how to execute multiple sql command in asp.net

Question

I have:

 string commandText = @"SELECT cn.companyName from Companies cn 
               INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
               WHERE uc.description like '%" + ProcessInputClause + "%';";

 string addr = @"SELECT address FROM Companies where companyName = @companyName";

To execute that I tried:

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = commandText;
sqlCmd.Parameters.Clear();
string connectionString = ConfigurationSettings.AppSettings["connectionString"];
using (SqlConnection connection = new SqlConnection(connectionString))
{
   SqlCommand command = new SqlCommand(commandText, connection);
   try
   {
       connection.Open();
       sqlCmd = new SqlCommand(sqlCmd.CommandText, connection);
       SqlDataReader sqlReader = sqlCmd.ExecuteReader();
       DataTable dt = new DataTable();

       sqlReader.Read();
       dt.Load(sqlReader);
       Label1.Text = dt.Rows[0][0].ToString();
       sqlCmd.CommandText = addr;
       SqlCmd.Parameters.AddWithValue("@companyName", "namehere");
       SqlDataReader addressReader = sqlCmd.ExecuteReader();
       addressReader.Read();
       Label1.Text = Label1.Text + addressReader["address"].ToString() + addressReader.GetValue(1).ToString() + addressReader.GetString(0) + addressReader.GetString(1);

I can get only the first sql to execute, and get the companyName to Label1.text. But it looks like the second executeReade() gives me no result, although I tried the query alone successfully! I also tried the combine commandtext and use nextresult but no luck. Can you help me in how can I execute both command successfully ? Ps: added calling addressReader.Read(); I forget to copy this, tried with this but no result !

Answer 1

The first thing to do is to parameterize that first query ;p

After that, all you need to change here is to actually consume the second reader:

using(var addressReader = sqlCmd.ExecuteReader()) {
    if(addressReader.Read()) {
        Label1.Text = Label1.Text + addressReader["address"].ToString()
         + addressReader.GetValue(1).ToString() + addressReader.GetString(0)
         + addressReader.GetString(1);
    }
}

EDIT: removing this, as it doesn't apply since the two queries are hierarchical

You could perform both selects in a single sql operation (NextResult(), etc), but I'm not sure that dt.Load will work well with that; worth a try, though:

sqlCommand.CommandText = @"
    SELECT cn.companyName from Companies cn 
    INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
    WHERE uc.description like '%' + @description+ '%';

    SELECT address FROM Companies where companyName = @companyName;";

sqlCommand.Parameters.AddWithValue("description", ProcessInputClause);
sqlCommand.Parameters.AddWithValue("companyName", "namehere");
using(var reader = sqlCommand.ExecuteReader()) {
    dt.Load(reader);
    if(reader.NextResult() && reader.Read()) {
        Label1.Text = Label1.Text + reader["address"].ToString()
         + reader.GetValue(1).ToString() + reader.GetString(0)
         + reader.GetString(1);
    }
}

With a dependency between the queries, and the fact that the address is part of the company record, I would just do this:

@"SELECT cn.companyName, cn.Address from Companies cn 
           INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
           WHERE uc.description like '%" + @description + "%';";

a single query that fetches both values. You could use your existing dt.Load to access that, getting the name from the first column and the address from the second - but frankly I'm a huge fan of "dapper", so I'd do it this way:

class Company {
    public string CompanyName {get;set;}
    public string Address {get;set;}
}
...
var rows = conn.Query<Company>(
    @"SELECT cn.companyName, cn.Address from Companies cn 
      INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
      WHERE uc.description like '%" + @description + "%';",
    new { description = ProcessInputClause }).ToList();

then just iterate over rows:

foreach(var row in rows) {
    string name = row.CompanyName;
    string address = row.Address;
    // ...
}

Or if you don't want to declare the type:

var rows = conn.Query(
    @"SELECT cn.companyName, cn.Address from Companies cn 
      INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
      WHERE uc.description like '%" + @description + "%';",
    new { description = ProcessInputClause }).ToList();
foreach(var row in rows) {
    string name = row.companyName; // yes, this works
    string address = row.Address;
    // ...
}

---

In the scenario where multiple tables are involved, you can use a table-variable as the base for joins:

declare @ids table (CompanyId int not null)
insert @ids (CompanyId)
select companyId from Companies cn
INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
WHERE uc.description like '%" + @description + "%';

select cn.CompanyName, cn.Address
from @ids #i
inner join Companies cn on cn.CompanyId = #i.CompanyId

select /* other stuff */
from @ids #i
inner join /* other tables */

select /* yet more other stuff */
from @ids #i
inner join /* yet more other tables */

Answer 2

Use DataSet instead of Datatable

DataSet ds = new DataSet();

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);


SqlDataAdapter da = new SqlDataAdapter(qry, connection);

da.Fill(ds);

Answer 3

You can put both commands on an string array and do foreach statement command in commands.

Like this:

string command1 = @"SELECT cn.companyName from Companies cn 
               INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
               WHERE uc.description like '%" + ProcessInputClause + "%';";
string command2 = @"SELECT address FROM Companies where companyName = @companyName";

string[] commands  = new string[2];
        commands.SetValue(command1 , 0);
        commands.SetValue(command2 , 1);

Then:

Foreach(string command in commands)
{
    SqlCommand c= new SqlCommand(command , connection);
    //do the rest
}