User-893317190 posted
Hi JagjitSingh,
You could define a list of employee columns and loop through the list.
Below is my sample.
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("Employee1",typeof(Int32)));
table.Columns.Add(new DataColumn("Employee2", typeof(Int32)));
table.Columns.Add(new DataColumn("Employee3", typeof(Int32)));
DataRow newrow= table.NewRow();
newrow[0] = 2;
newrow[1] =DBNull.Value; //the second value is null
newrow[2] = 4;
table.Rows.Add(newrow);
string sql = "select * from suppliers where sid in ( ";
//definde a list of employee columns
List<String> employees = new List<string> { "Employee1", "Employee2", "Employee3" };
SqlCommand command = new SqlCommand();
command.Connection = new SqlConnection(constr);
//loop through the list so that you only need to deal with sqlcommand for one time
foreach (var employee in employees)
{
if (!string.IsNullOrEmpty(Convert.ToString(table.Rows[0][employee])))
{
sql += " @" + employee +",";
command.Parameters.AddWithValue(employee, table.Rows[0][employee]);
}
}
sql = sql.TrimEnd(',') + ")"; // remove the last "," and add ")"
command.CommandText = sql;
Response.Write(sql);
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
adapter.SelectCommand = command;
DataTable table2 = new DataTable();
adapter.Fill(table2);
GridView1.DataSource = table2;
GridView1.DataBind();
}
The result.

Best regards,
Ackerly Xu