Select Statement with in

Question

User-1499457942 posted

Hi

  I have employee id in variables employee1,employee2,employee3 . I want to make select statement with in option . Is it possible instead of 3 variables in condition can be created with 1 variable. I am trying like below and it is working but i want to convert into few lines

SqlCommand cmdSale = new SqlCommand();
                                    strQuery = "Select * from [Data] where Location = @Location and ";
                                    cmdSale.Parameters.AddWithValue("@Location", dt.Rows[i]["Location"]);
                                    //if (!string.IsNullOrWhiteSpace(Convert.ToString(dt.Rows[i]["Employee1"])))
                                    if (dt.Rows[i]["Employee1"] != DBNull.Value && string.IsNullOrEmpty(dt.Rows[i]["Employee1"].ToString()) == false)
                                    {
                                        strQuery = strQuery + "( EmployeeId = @EmpId1 or ";
                                        cmdSale.Parameters.AddWithValue("@EmpId1", dt.Rows[i]["Employee1"]);
                                    }
                                    if (dt.Rows[i]["Employee2"] != DBNull.Value && string.IsNullOrEmpty(dt.Rows[i]["Employee2"].ToString()) == false)
                                    {
                                        strQuery = strQuery + "EmployeeId = @EmpId2 or ";
                                        cmdSale.Parameters.AddWithValue("@EmpId2", dt.Rows[i]["Employee2"]);
                                    }
                                    if (dt.Rows[i]["Employee3"] != DBNull.Value && string.IsNullOrEmpty(dt.Rows[i]["Employee3"].ToString()) == false)
                                    {
                                        strQuery = strQuery + "EmployeeId = @EmpId3)";
                                        cmdSale.Parameters.AddWithValue("@EmpId3", dt.Rows[i]["Employee3"]);
                                    }

Thanks

Answer 1

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