Critical SQL statement

protected void BuildWhereClause()
        {
            string skillresult = "";
            string qualificationresult = "";
            string cityresult = "";
            string name="";
            string experincefrom = "";
            string experinceto = "";
            string fresher = "";
            string interships = "";
            string lacsfrom = "";
            string lacsto = "";
            string companyresult = "";
            string activedays = "";
            string skilljoin = " inner join tblCandidateSkillSets ss on cp.ID = ss.CandidateID inner join   tblSkillSetLookup lp on lp.ID = ss.SkillID";
            string skillwhere = " where lp.Skill in";
            string qualificationjoin = " inner join tblCandidateQualification qf on cp.ID=qf.CandidateID inner join tblQualificationLookup ql on ql.ID=qf.QualificationID";
            string qualificationwhere = " ql.Qualification in";
//skills loop
            int count = chkSkills.Items.Count;
            for (int i = 0; i < count; i++)
            {
                if (chkSkills.Items[i].Selected == true)
                {
                    skillresult += "," + "'" + (chkSkills.Items[i].Value) + "'";
                }
            }
            if (skillresult.Length > 0)
            {
                skillresult = skillresult.Substring(1);
                sqljoin = skilljoin + skillwhere + "(" + skillresult + ")";
            }

            //Qualification loop
            int z = chkQualification.Items.Count;
            for (int k = 0; k < z; k++)
            {
                if (chkQualification.Items[k].Selected == true)
                {
                    qualificationresult += "," + "'" + (chkQualification.Items[k].Value) + "'";
                }
            }
            if (qualificationresult.Length > 0)
            {
                qualificationresult = qualificationresult.Substring(1);
                if (sqljoin.Length > 0)
                {
                    sqljoin = skilljoin + qualificationjoin + skillwhere + "(" + skillresult + ")" + "and" + qualificationwhere + "(" + qualificationresult + ")";
                }
                else
                {
                    sqljoin = qualificationjoin + " where " + qualificationwhere + "(" + qualificationresult + ")";
                }
            }

            //location loop
            int y = chkCity.Items.Count;
            for (int j = 0; j < y; j++)
            {
                if (chkCity.Items[j].Selected == true)
                {
                    cityresult += "," + "'" + (chkCity.Items[j].Value + "'");
                }
            }
            if (cityresult.Length > 0)
            {
                cityresult = cityresult.Substring(1);
                if (sqljoin.Length > 0)
                {
                    sqljoin = sqljoin + " and CurrentLocation in(" + cityresult + ")";
                }
                else
                {
                    sqljoin = " where CurrentLocation in(" + cityresult + ")";
                }
            }
//If name is given to search name loop
            if(txtName.Text.Length>0)
            {                         
                name=txtName.Text;
                if (sqljoin.Length > 0)
                {
                    sqljoin = sqljoin + " and Name=" + "'" + name + "'";
                }
                else
                {
                    sqljoin = " where Name=" + "'" + name + "'";
                }
            }


            //Experince
            if ((ddlYearFrom.SelectedIndex > 0) && (ddlYearTo.SelectedIndex > 0))
            {
                experincefrom = ddlYearFrom.SelectedValue;
                experinceto = ddlYearTo.SelectedValue;
                if (sqljoin.Length > 0)
                {
                    sqljoin = sqljoin + " and ExpInYrs between " + experincefrom + " and " + experinceto;
                }
                else
                {
                    sqljoin = " where ExpInYrs between " + experincefrom + " and " + experinceto;
                }
            }
            else
            {
                if ((ddlYearFrom.SelectedIndex > 0) || (ddlYearTo.SelectedIndex > 0))
                {
                    experincefrom = ddlYearFrom.SelectedValue;
                    experinceto = ddlYearTo.SelectedValue;
                    if (skillresult.Length > 0)
                    {
                        if (ddlYearFrom.SelectedIndex > 0)
                        {
                            sqljoin = sqljoin + " and ExpInYrs=" + experincefrom;
                        }
                        else
                        {
                            sqljoin = sqljoin + " and ExpInYrs=" + experinceto;
                        }
                    }
                    else
                    {
                        if (ddlYearTo.SelectedIndex > 0)
                        {
                            sqljoin = sqljoin + " where ExpInYrs=" + experinceto;
                        }
                        else
                        {
                            sqljoin = sqljoin + " where ExpInYrs=" + experincefrom;
                        }
                    }
                }
            }

            //company
            if (txtCompany.Text.Length > 0)
            {
                companyresult = txtCompany.Text;
                if (sqljoin.Length > 0)
                {
                    sqljoin = sqljoin + " and CurrentCompany=" + "'" + companyresult + "'";
                }
                else
                {
                    sqljoin = " where CurrentCompany=" + "'" + companyresult + "'";
                }
            }

            //If Fresher
            if (chkFreshers.Checked == true)
            {
                fresher = "1";
                if (sqljoin.Length > 0)
                {
                    sqljoin = sqljoin + " and Fresher =" + fresher;
                }
                else
                {
                    sqljoin = " where Fresher =" + fresher;
                }
            }
//If internship
            if (chkInternships.Checked == true)
            {
                interships = "1";
                if (sqljoin.Length > 0)
                {
                    sqljoin = sqljoin + " and Internship =" + interships;
                }
                else
                {
                    sqljoin = " where Internship =" + interships;
                }
            }

            //salary in lacs
            if ((ddlLacsFrom.SelectedIndex > 0) && (ddlLacsTo.SelectedIndex > 0))
            {
                lacsfrom = ddlLacsFrom.SelectedValue;
                lacsto = ddlLacsTo.SelectedValue;
                if (sqljoin.Length > 0)
                {
                    sqljoin = sqljoin + " and CurrentSalaryInLacs between " + lacsfrom + " and " + lacsto;
                }
                else
                {
                    sqljoin = " where CurrentSalaryInLacs between " + lacsfrom + " and " + lacsto;
                }
            }
            else
            {
                if ((ddlLacsFrom.SelectedIndex > 0) || (ddlLacsTo.SelectedIndex > 0))
                {
                    lacsfrom = ddlLacsFrom.SelectedValue;
                    lacsto = ddlLacsTo.SelectedValue;
                    if (sqljoin.Length > 0)
                    {
                        if (ddlLacsFrom.SelectedIndex > 0)
                        {
                            sqljoin = sqljoin + " and CurrentSalaryInLacs=" + lacsfrom;
                        }
                        else
                        {
                            sqljoin = sqljoin + " and CurrentSalaryInLacs=" + lacsto;
                        }
                    }
                    else
                    {
                        if (ddlLacsTo.SelectedIndex > 0)
                        {
                            sqljoin = sqljoin + " where CurrentSalaryInLacs=" + lacsto;
                        }
                        else
                        {
                            sqljoin = sqljoin + " where CurrentSalaryInLacs=" + lacsfrom;
                        }
                    }
                }
            }

            // If company name is given to search company
            if (txtCompany.Text.Length > 0)
            {
                companyresult = txtCompany.Text;
                if (sqljoin.Length > 0)
                {
                    sqljoin = sqljoin + " and CurrentCompany=" + "'" + companyresult + "'";
                }
                else
                {
                    sqljoin = " where CurrentCompany=" + "'" + companyresult + "'";
                }
            }

            //If active days is selected
            if (ddlActive.SelectedIndex > 0)
            {
                activedays = ddlActive.SelectedValue;
                if (ddlActive.SelectedValue == "1 Week")
                {
                    if (sqljoin.Length > 0)
                    {
                        sqljoin = sqljoin + " and UpdatedOn<=dateadd(day," + 7 + ",getdate())";
                    }
                    else
                    {
                        sqljoin = sqljoin + " where UpdatedOn<=dateadd(day," + 7 + ",getdate())";
                    }
                }
                if (ddlActive.SelectedValue == "1 Month")
                {
                    if (sqljoin.Length > 0)
                    {
                        sqljoin = sqljoin + " and UpdatedOn<=dateadd(day," + 30 + ",getdate())";
                    }
                    else
                    {
                        sqljoin = sqljoin + " where UpdatedOn<=dateadd(day," + 30 + ",getdate())";
                    }
                }
                if (ddlActive.SelectedValue == "3 Months")
                {
                    if (sqljoin.Length > 0)
                    {
                        sqljoin = sqljoin + " and UpdatedOn<=dateadd(day," + 90 + ",getdate())";
                    }
                    else
                    {
                        sqljoin = sqljoin + " where UpdatedOn<=dateadd(day," + 90 + ",getdate())";
                    }
                }
                if (ddlActive.SelectedValue == "6 Months")
                {
                    if (sqljoin.Length > 0)
                    {
                        sqljoin = sqljoin + " and UpdatedOn<=dateadd(day," + 180 + ",getdate())";
                    }
                    else
                    {
                        sqljoin = sqljoin + " where UpdatedOn<=dateadd(day," + 180 + ",getdate())";
                    }
                }

            }

            //To exclude already viewed profiles
            if (chkExclude.Checked == true)
            {
                GetUserName();
                if (sqljoin.Length > 0)
                {
                    sqljoin = sqljoin + " and ViewedUsers like'; " + users + ";'";
                }
                else
                {
                    sqljoin = " where ViewedUsers like'; " + users + ";'";
                }
            }
        }

binding data from DB to Gridview

//postback for button event in page load
btnSaveClose.OnClientClick = String.Format("fnClickSave('{0}','{1}')", btnSaveClose.UniqueID, "");

//sample data bind from DB to checkbox
SqlConnection
string myquery = "select Qualification from tblQualificationLookup";
SqlCommand mycmd = new SqlCommand(myquery);
string mycmd.Connection = conn;
dt = ad.Fill(dt);
chkQualification.DataSource = dt;
chkQualification.DataTextField = "Qualification";chkQualification.DataValueField = "Qualification";chkQualification.DataBind();
conn = new SqlConnection(myConnectionString);

web.config to give connectionstring

<connectionStrings>add name="CMI_CVBank" connectionString="Initial Catalog=CMI_CVBank;Data Source=CAMJ82R4BS\SQLEXPRESS;User Id=CVBankUser;Password=CVBankUser" providerName="System.Data.SqlClient"/></connectionStrings>

Auto incrementing gridview columns and hyperlink to the particular file

<asp:TemplateField HeaderText="S.No"><ItemTemplate><%#Container.DataItemIndex+1%>
</ItemTemplate></asp:TemplateField>

//Hyperlink Field in Gridview to direct to the file in the folder named resumes
<asp:HyperLinkField DataTextField="FileName" DataNavigateUrlFields="FileName" DataNavigateUrlFormatString="/resumes/{0}" HeaderText="Download full profile" />//Incrementing Serial Number in gridview

Confirm box for popup button

<asp:ConfirmButtonExtender ID="CBE" runat="server" TargetControlID="btnDelete" ConfirmText="Are you sure you want to delete?"></asp:ConfirmButtonExtender>

//In this button we can define functions for confirm OK button click. Cancel will done automatically.
<asp:Button ID="btnDelete" Text="Delete" runat="server" Width="70px" OnClick="btnDelete_Click" />

Modal popup using ajax for gridview

<script runat="server">protected void btndetails_Click(object sender, EventArgs e)
{
Button btnviewdetails = sender as Button;
GridViewRow row = (GridViewRow)btnviewdetails.NamingContainer;
this.sqldsprofiledetails.SelectParameters.Clear();
string ID =Convert.ToString(this.GVprofiledetails.DataKeys[row.RowIndex].Value);
this.sqldsprofiledetails.SelectParameters.Add("ID", ID);
this.dvProfileDetails.DataSource = this.sqldsprofiledetails;
this.dvProfileDetails.DataBind();
this.dvProfileDetails.Fields[0].Visible = false;

this.MPE.Show();
}

</script>

//Toolscript manager and sql datasource for popup
<asp:ToolkitScriptManager ID="TSM" runat="server"></asp:ToolkitScriptManager><asp:SqlDataSource ID="sqldsgriddetails" runat="server" SelectCommand="select ID,Name,ExpInYrs,CurrentCompany,CurrentSalaryInLacs,CurrentDesignation,CurrentLocation from dbo.tblCandidateProfile"SelectCommandType="Text" ConnectionString="server=CAMJ82R4BS\SQLEXPRESS;database=CMI_CVBank;User ID=CVBankUser;password=CVBankUser;"></asp:SqlDataSource><asp:SqlDataSource ID="sqldsprofiledetails" runat="server" SelectCommand="select ID,Name,Source,ReferredBy,Description,ExpInYrs,CurrentSalaryInLacs,CurrentLocation,WillingToRelocate,FileName from dbo.tblCandidateProfile where ID=@ID"SelectCommandType="Text" CancelSelectOnNullParameter="true" ConnectionString="server=CAMJ82R4BS\SQLEXPRESS;database=CMI_CVBank;User ID=CVBankUser;password=CVBankUser;"></asp:SqlDataSource>

//Proxy button and detailsview inside the popup panel
<asp:Button ID="btnshowpopup" runat="server" CssClass="buttonpopup"/><asp:ModalPopupExtender ID="MPE" runat="server" TargetControlID="btnshowpopup" PopupControlID="pnlpopupcontent"CancelControlID="btnCancel" BackgroundCssClass="modalBackground" ></asp:ModalPopupExtender>
<asp:Panel ID="pnlpopupcontent" runat="server" Width="100%"><asp:UpdatePanel ID="updpnlprofiledetails" runat="server" UpdateMode="Conditional"><ContentTemplate><div align="center" style="width: 200"><asp:Label ID="lblCandidateDetail" runat="server" Text="Candidate Details" Font-Bold="True" Font-Size="XX-Large" ForeColor="Black"></asp:Label></div><asp:DetailsView ID="dvProfileDetails" runat="server" DefaultMode="ReadOnly" Width="561px"Height="193px" BackColor="white" HorizontalAlign="Center"
BorderColor="Black" BorderStyle="Solid"
CellPadding="4" CellSpacing="4" Font-Bold="True" OnDataBound="dvProfileDetails_DataBound"
GridLines="None" DataKeyNames="ID" AutoGenerateRows="false"
HeaderStyle-Font-Bold="true" ><Fields>
<asp:BoundField DataField="ID" HeaderText="ID" /><asp:BoundField DataField="Name" HeaderText="Name" /><asp:BoundField DataField="Source" HeaderText="Source" /><asp:BoundField DataField="ReferredBy" HeaderText="Referred By" /><asp:BoundField DataField="Description" HeaderText="Description" /><asp:TemplateField HeaderText="Skill Sets"><ItemTemplate><asp:Label ID="lblSkills" runat="server" ></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="Qualification"><ItemTemplate ><asp:Label ID="blQualification" runat="server"></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundField DataField="ExpInYrs" HeaderText="Experince in years" /><asp:BoundField DataField="CurrentSalaryInLacs" HeaderText="Current Annual salary in lacs" /><asp:BoundField DataField="CurrentLocation" HeaderText="Current Location" /><asp:TemplateField HeaderText="Willing To Relocate"><ItemTemplate ><asp:Label ID="lblRelocate" runat="server" ></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="HR Remarks">
<ItemTemplate>
<asp:TextBox ID="txtoldhrremarks" runat="server" TextMode="MultiLine" ReadOnly="true"></asp:TextBox></ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="HR Remarks (if any new remarks)">
<ItemTemplate>
<asp:TextBox ID="txthrremarks" Text="" runat="server" TextMode="MultiLine"></asp:TextBox></ItemTemplate>
</asp:TemplateField>
<asp:HyperLinkField DataTextField="FileName" DataNavigateUrlFields="FileName" DataNavigateUrlFormatString="/resumes/{0}" HeaderText="Download full profile" />
<asp:TemplateField></asp:TemplateField></Fields>
<HeaderStyle Font-Bold="True" /></asp:DetailsView>

gridview databound event

GridViewRow

{
//ID is field name in grd and searchdetails is the grid id


{
//chkselect is the checkbox in our grid

ckselected.Checked =
}
row = e.Row;if (row.RowIndex >= 0)string ID = Convert.ToString(searchdetails.DataKeys[row.RowIndex].Value);if (selected.Contains(ID))CheckBox ckselected = (CheckBox)row.FindControl("chkSelect");true;else{

ckselected.Checked =
}
}CheckBox ckselected = (CheckBox)row.FindControl("chkSelect");false;