Return 2 Results From SQL

Question

If I have a stored procedure that is

Alter dbo.Testing
    Select userid from masterdb
    where employed = 'Yes'

    Select Count(*) from leftfield
    where pin is not null

How can I store the returned results from each query in a separate data set?

Pseudo code:

firstdataset = Select userid from masterdb where employed = 'Yes'

seconddataset = select count(*) from leftfield where pin is not null

EDIT

I use this to return 1 result set from a stored procedure to a C# dataset. Is it possible to return more than 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;
}

EDIT # 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;
}

Error # 1
Cannot implicitly convert type 'System.Collections.Generic.List' to 'System.Data.DataSet'

Are you asking about a temporary table? See http://stackoverflow.com/questions/20107827/insert-data-into-temp-table-with-query or http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table — AJ X., Dec 10 '15 at 20:09
@axlj - No, My stored procedure, returns 2 totally seperate result sets. I want to in my C# get the results of each result set and store it in 2 seperate datasets. — FartStopper, Dec 10 '15 at 20:12

Thomas · Answer 1 · 2015-12-11T01:04:57.187

You can do it like that :

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 Add function to connect to the database **

A sample function to get your values:

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

How would I assign a C# dataset to each returned SQL Variable? — FartStopper, Dec 10 '15 at 20:18

Mirko · Answer 2 · 2015-12-11T03:25:38.610

2

You are looking for the SqlReader.NextResult() method, so:

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: Edited to change to DataTable from DataSet to make the Load function work the way I had indicated (unfortunately from memory). This will return a list of DataTables now (probably more appropriately indicating what is being returned anyway). Frankly I would not use this and prefer EntityFramework and strongly typed results (but then I of course do not know the entire use case, performance requirements, etc.).

edited Dec 11 '15 at 03:25

answered Dec 10 '15 at 20:32

Mirko

4,284
1
22
19

Yes, that is the idea. As long as the sproc returns both results at the end of execution. Let me know if that works out for you – Mirko Dec 10 '15 at 20:42
I get 2 compile error 1) 'No overload for method 'Load' takes 1 argument' & 2) Can not implicitly convert type 'List' to 'System.Data.DataSet' – FartStopper Dec 10 '15 at 20:45
Hi, sorry about that. It is proving that coding from memory is always bad :) Do you really need datasets or would datatables do in this case? I am asking as the DataTable.Load method actually just takes a DataReader and in your case you are really just loading two DataTables really. I will edit my answer for now (and you will likely need to change how you are calling the method which is probably one of the error causes). Do show how you are invoking it in case you need help with that also. – Mirko Dec 11 '15 at 03:21
on the var dt1 & var dt2 lines I get an error of Can not assign void to an implicitly-typed local variable. I am going to edit my original post with your syntax and how I am calling as my call is throwing an error as well. – FartStopper Dec 11 '15 at 13:35

score 0 · Answer 3 · answered Dec 10 '15 at 20:06

0

You need a function to return results, not a stored procedure. The stored procedure is designed to return a status value only. Write two functions and have each one return a non-scalar value, which is essentially a dataset.

answered Dec 10 '15 at 20:06

nicomp

4,344
4
27
60

score 0 · Accepted Answer · answered Dec 16 '15 at 21:50

I ended up using the below solution to solve my issue. My SQL Stored Procedure looks like so

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

And this is the C# I used (sans the html to create the gridview)

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

Return 2 Results From SQL

4 Answers4