从SQL返回2个结果

本文关键字：结果 2个 返回 SQL | 更新日期: 2023-09-27 18:26:29

如果我有一个存储过程

Alter dbo.Testing
    Select userid from masterdb
    where employed = 'Yes'
    Select Count(*) from leftfield
    where pin is not null

如何将每个查询返回的结果存储在单独的数据集中？

伪代码：

firstdataset = Select userid from masterdb where employed = 'Yes'
seconddataset = select count(*) from leftfield where pin is not null

编辑

我使用它将存储过程中的1个结果集返回到C#数据集。是否可以返回1以上的值？

public DataSet RunStoredProc(string databaseConnection)
{
    ds = new DataSet();  
    DSqlQueryBuilder = new StringBuilder();
    SqlQueryBuilder.Append("exec dbo.StoredProc "); 
    SqlConnection = new SqlConnection(connectionString);
    SqlCommand = new SqlCommand(sqlQuery, SqlConnection);
    SqlConnection.Open();
    SqlCommand.CommandTimeout = 0;  
    ds = new DataSet();
    SqlDataAdapter = new SqlDataAdapter(SqlCommand);            
    SqlDataAdapter.Fill(ds, "Data");
    return ds;
}



编辑#2

public DataSet RunStoredProc(string databaseConnection)
{
    ds = new DataSet();  
    DSqlQueryBuilder = new StringBuilder();
    SqlQueryBuilder.Append("exec dbo.StoredProc "); 
    //Error 1 On Line Below
    ds = ExecuteSqlQuery(databaseConnection, SqlQueryBuilder.ToString());
    return ds;
}
public List<DataTable> RunStoredProc(string databaseConnection)
{
    var dataTables = new List<DataTable>();  
    DSqlQueryBuilder = new StringBuilder();
    SqlQueryBuilder.Append("exec dbo.StoredProc "); 
    SqlConnection = new SqlConnection(connectionString);
    SqlCommand = new SqlCommand(sqlQuery, SqlConnection);
    var reader = SqlCommand.ExecuteReader();
    DataTable dt1 = new DataTable();
    dt1.Load(reader);
    dataTables.Add(dt1);
    DataTable dt2 = new DataTable();
    dt2.Load(reader);
    dataTables.Add(dt2);
    return dataTables;
}

错误#1
无法将类型"System.Collections.Generic.List"隐式转换为"System.Data.Datat"

从SQL返回2个结果

你可以这样做：

Alter dbo.Testing
-- Declare your variables
Declare @userId AS INT;
Declare @count AS INT;
-- Set the value
Select @userId = userid from masterdb where employed = 'Yes';
Select @count = Count(1) from leftfield where pin is not null;
-- Return the values
Select @userId, @count;

**EDIT添加连接到数据库的功能**

获取值的示例函数：

static DataSet sqlTest(string connectionString)
{
    using (var sqlConnection = new SqlConnection(connectionString))
    {
        sqlConnection.Open();
        var sqlCommand = new SqlCommand("exec dbo.StoredProc", sqlConnection);
        var dataSet = new DataSet();
        var sqlDataAdapter = new SqlDataAdapter(sqlCommand);
        sqlDataAdapter.Fill(dataSet, "Data");
        // you can access your values like that:
        var userId = dataSet.Tables["Data"].Rows[0][0];
        var count = dataSet.Tables["Data"].Rows[0][1];
        return dataSet;
    }
}

您正在寻找SqlReader.NextResult（）方法，因此：

public List<DataTable> RunStoredProc(string databaseConnection)
{
    var dataTables = new List<DataTable>();  
    DSqlQueryBuilder = new StringBuilder();
    SqlQueryBuilder.Append("exec dbo.StoredProc "); 
    SqlConnection = new SqlConnection(connectionString);
    SqlCommand = new SqlCommand(sqlQuery, SqlConnection);
    var reader = SqlCommand.ExecuteReader();
    var dt1 = new DataTable().Load(reader);
    dataTables.Add(dt1);
    reader.NextResult();
    var dt2 = new DataTable().Load(reader);
    dataTables.Add(dt1);
    // Make sure to really use usings here to ensure all resources are being 
closed
    return dataTables;
}

Edit：经过编辑，从DataSet更改为DataTable，以使Load函数按照我所指示的方式工作（不幸的是，从内存中）。这将立即返回一个DataTables列表（可能更适合指示返回的内容）。坦率地说，我不会使用它，更喜欢EntityFramework和强类型结果（但我当然不知道整个用例、性能要求等）。

您需要一个函数来返回结果，而不是一个存储过程。存储过程设计为仅返回状态值。编写两个函数，并让每个函数返回一个非标量值，该值本质上是一个数据集。

我最终使用以下解决方案来解决我的问题。我的SQL存储过程看起来像

Select Top 1 field1 from table1
Select Top 1 field1 from table1

这是我使用的C#（没有html来创建网格视图）

protected void PopulateTestGrids()
{
    DataSet ds = new DataSet();
    ds = RunStoredProc();
    DataTable tableA = ds.Tables[0];
    DataTable tableB = ds.Tables[1];
    GridView21.DataSource = tableA;
    GridView21.DataBind();
    GridView31.DataSource = tableB;
    GridView31.DataBind();
}
public DataSet RunStoredProc(string databaseConnection)
{
    ds = new DataSet();  
    DSqlQueryBuilder = new StringBuilder();
    SqlQueryBuilder.Append("exec dbo.StoredProc "); 
    ds = ExecuteSqlQuery(databaseConnection, SqlQueryBuilder.ToString());
    return ds;
}
public DataSet ExecuteSqlQuery(string databaseConnection, string sql)
{
    try
    {
        System.Configuration.ConnectionStringSettings connstring = System.Configuration.ConfigurationManager.ConnectionStrings["abcddb"];
        using (SqlConnection conn = new SqlConnection(connstring.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = sqlQuery;
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);                    
                adapter.Fill(_dataSet);
                conn.Close();
            }                
        }
        return _dataSet;
    }
    catch (Exception exception) { throw exception; }
}
相关文章：