Dynamic Parameter SQL Query.

If in case you have multiple search boxes in which query can be made on any text box entry (as shown in above screenshot). Code will be as follows, in which Dynamic query will be generated (Search Button Click Event Code).

[Code]


        StringBuilder query = new StringBuilder();

        string name, cnic, email, empno;
        name = txtName.Text.Trim();
        cnic = txtCNIC.Text.Trim();
        email = txtEmailAssigned.Text.Trim();
        empno = txtEmployeeNo.Text.Trim();

        List<TextBox> txtBoxes = new List<TextBox>();

        txtBoxes.Add(txtName);
        txtBoxes.Add(txtEmailAssigned);
        txtBoxes.Add(txtCNIC);
        txtBoxes.Add(txtEmployeeNo);

        query.Append("SELECT * FROM EmailTable");
        bool containstext = false;

        //Following foreach will check whether any text box contains some text or not

        foreach (TextBox tb1 in txtBoxes)
        {
            if (tb1.Text.Length != 0)
            {
                containstext = true;
            }
        }

        if (containstext)
        {
            int counter = 0;
            query.Append(" WHERE");
            string condition = "";

            foreach (TextBox tb in txtBoxes)
            {
                condition = string.Empty;
                condition = tb.Text;

                if (!string.IsNullOrEmpty(condition))
                {
                    switch (tb.ID)
                    {
                        case "txtName":
                            condition = " fname LIKE '%" + condition + "%'";
                            counter++;
                            break;
                        case "txtEmailAssigned":
                            condition = " email_assigned LIKE '%" + condition + "%'";
                            counter++;
                            break;
                        case "txtCNIC":
                            condition = " cnic LIKE '%" + condition + "%'";
                            counter++;
                            break;
                        case "txtEmployeeNo":
                            condition = " empno LIKE '%" + empno + "%'";
                            counter++;
                            break;
                    }

                    if (counter > 1)
                    {
                        condition = " AND " + condition;
                    }
                }

                query.Append(condition);
            }

            query.Append("AND disable IS NULL AND deleted IS NULL");
            //Response.Write(query);
           
            DataSet ds = dh.GetEmployeeDetail(query.ToString());

            GridView1.DataSource = ds;
            GridView1.DataBind();

[/Code]