如何执行在asp.net多个SQL命令

问题描述：

我有：

 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";

要执行，我试过：

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);

我只能得到第一个SQL执行，并得到公司名称为Label1.text。但它看起来像第二executeReade（）让我没有结果，但我试过单独查询成功！我也试过结合的CommandText，并使用nextresult但没有运气。你能帮我我怎么能成功地执行这两个命令？
PS：新增调用addressReader.Read（）;我忘了复制此，尝试了这一点，但没有结果！

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 !

答

做的第一件事就是参数，首先查询; P

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);
    }
}

编辑：删除这一点，因为它并不适用，因为这两个查询是分层

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

您的在一个单独的SQL操作可能的执行两个选择（ NextResult（）等），但我不知道， dt.Load 将与正常工作;值得一试，虽然：

~~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 + "%';";

这取两个值一个查询。你可以使用现有的 dt.Load 来访问，从第一栏和第二个地址获取的名字 - 但坦率地说，我的短小精悍一个巨大的风扇所以我会做这种方式：

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();

然后就遍历行：

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 */

如何执行在asp.net多个SQL命令

相关推荐