Converting multiple query to use parameters to avoid SQL injection

The recommended way to avoid SQL injection attacks is to use parameters. Also I can recommend that you create a stored procedure instead of using dynamic SQL.

You can pass your dropdown indexes as parameters to your SP and use the old trick of using it as conditional on the where clause.

Your select can end up like this:

select CT.columnA, CT.columnB
from tableA CT
join tableB SE on SE.idA = CT.id
where
      (@ddlTaskName_SelectedIndex > 0 and (CT.ATTR2739 LIKE '%' + @ddlTaskName_SelectedItemText))
-- or (@ddlTaskName_SelectedIndex = 0 and (CT.ATTR2739 LIKE '%' OR CT.ATTR2739 IS NULL))     -- if you think enough ill see this line is unnecessary
and 
      (@ddlService_SelectedIndex > 0 and (SE.ATTR2821 LIKE '%' + @ddlService_SelectedItemText + '%')
-- This "else" line is unnecessary too, since we don't really filter it because eveything is "like %" or "null"

And so on ...

Those dropdown texts are still prone to SQL injection since a smart hacker may be able to change its value depending on your system. You can fix it to just set variables inside the SP, this mean you will not pass the drop down text, just the indexes and do some swith case to set that varchar variables before the select.

That can add some maintenance issue, since your dropdown texts must match the same texts hardcoded on your SP. A better approach can be to create a domain table for each dropdown:

Create table DropDownClientValues
(
 index int
,text varchar(50)
)

And list your dropdown values directly from those domain tables; that way all your selects/SP can refer the exact same values.

This has the advantage of making adding/removing options from your drop downs very easy and with no impact in your code. Sadly this can be a bit onerous to refactor a big app that way.

You could also use another using statement here

using (SqlConnection conn = new SqlConnection(connString))
{
    try
    {
        SqlCommand cmd = new SqlCommand(query, conn);

        SqlDataAdapter da = new SqlDataAdapter(query, conn);

        myDataSet = new DataSet();
        da.Fill(myDataSet);

        myDataView = new DataView();
        myDataView = myDataSet.Tables[0].DefaultView;

        yourTasksGV.DataSource = myDataView;
        yourTasksGV.DataBind();
    }
}

and make it

using (SqlConnection conn = new SqlConnection(connString))
using (SqlCommand cmd = new SqlCommand(query, conn))
using (SqlDataAdapter da = new SqlDataAdapter(query, conn))
{
        myDataSet = new DataSet();
        da.Fill(myDataSet);

        myDataView = new DataView();
        myDataView = myDataSet.Tables[0].DefaultView;

        yourTasksGV.DataSource = myDataView;
        yourTasksGV.DataBind();
    }
}

I didn't see any declaration of myDataSet or myDataView so I var'd them.

You don't use the SQLCommand so you could get rid of that altogether, so it becomes

using (SqlConnection conn = new SqlConnection(connString))
using (SqlDataAdapter da = new SqlDataAdapter(query, conn))
{
        myDataSet = new DataSet();
        da.Fill(myDataSet);

        myDataView = new DataView();
        myDataView = myDataSet.Tables[0].DefaultView;

        yourTasksGV.DataSource = myDataView;
        yourTasksGV.DataBind();
    }
}

This is what you have:

using (SqlConnection conn = new SqlConnection(connString))
{
    try
    {
        SqlCommand cmd = new SqlCommand(query, conn);

        SqlDataAdapter da = new SqlDataAdapter(query, conn);

        myDataSet = new DataSet();
        da.Fill(myDataSet);

        myDataView = new DataView();
        myDataView = myDataSet.Tables[0].DefaultView;

        yourTasksGV.DataSource = myDataView;
        yourTasksGV.DataBind();
    }
}

This is what I would change it to

using (SqlConnection conn = new SqlConnection(connString))
using (SqlDataAdapter da = new SqlDataAdapter(query, conn))
{
        myDataSet = new DataSet();
        da.Fill(myDataSet);

        myDataView = new DataView();
        myDataView = myDataSet.Tables[0].DefaultView;

        yourTasksGV.DataSource = myDataView;
        yourTasksGV.DataBind();
    }
}

The explanation is above, you should make sure that you know how this works before implementing to be certain that it works for your code.

I would go away with some utility class for building the command. This class should store:

• sql parameter values
• sql command text
• be able to dump resulting sql command

Naive implementation:

public class GenericCommandBuilder
{
  private ArrayList m_ParameterValues;

  public StringBuilder Builder { get; private set; }

  public GenericCommandBuilder()
  {
    Builder = new StringBuilder(50);
    m_ParameterValues = new ArrayList();
  }

  public string GetParameter()
  {
    return string.Format(CultureInfo.InvariantCulture, "@ParameterID{0}", m_ParameterValues.Count);
  }

  public void AddParameter(object value)
  {
    m_ParameterValues.Add(value);
  }

  public SqlCommand ToCommand()
  {
    var cmd = new SqlCommand(Builder.ToString());
    for (var i = 0; i < m_ParameterValues.Count; i++)
    {
      cmd.Parameters.AddWithValue(string.Format(CultureInfo.InvariantCulture, "@ParameterID{0}", i), m_ParameterValues[i]);
    }
    return cmd;
  }
}

Sample usage:

  var table = new DataTable();
  using(var conn = new SqlConnection(@"Server=.\SQL2008;Database=d20;Integrated Security=true")) {
    conn.Open();
    var builder = new GenericCommandBuilder();
    builder.Builder.Append("select * from Alignments where ");

    builder.Builder.AppendFormat("LawfulValue={0}", builder.GetParameter());
    builder.AddParameter(1);

    builder.Builder.AppendFormat(" AND GoodValue={0}", builder.GetParameter());
    builder.AddParameter(2);

    builder.Builder.AppendFormat(" AND AlignmentName like {0}", builder.GetParameter());
    builder.AddParameter("%good%");

    var cmd = builder.ToCommand();
    cmd.Connection = conn;
    var adapter = new SqlDataAdapter(cmd);
    adapter.Fill(table);
  }