locked
Select Statement with in RRS feed

  • 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

    Tuesday, October 16, 2018 6:25 AM

All replies

  • 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

    Wednesday, October 17, 2018 5:14 AM