Stored procedure with input & output parameters and 2 recordsets

Question

I try to extract the results in c# asp.net from my stored procedure but it has 2 recordsets. the first with 1 row and the second with many rows as dates.

The code

public string penth_slqDAYS(string connectionString)
{
    string sMonth = DateTime.Now.Month.ToString();
    string syear = DateTime.Now.Year.ToString();

    try
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command1 = new SqlCommand("penthhmera_proc", connection);                
            /////////////////////////////
            SqlParameter param1;
            param1 = command1.Parameters.Add("@prs_nmb_pen", SqlDbType.VarChar, 7);
            param1.Value = prs_nmb_lb1.Text.Trim();
            SqlParameter param2;
            param2 = command1.Parameters.Add("@month_pen", SqlDbType.Int);
            param2.Value = sMonth;
            SqlParameter param3;
            param3 = command1.Parameters.Add("@year_int", SqlDbType.Int);
            param3.Value = syear;
            /////////////////////////
            command1.Parameters.Add("@days_out", SqlDbType.Int);
            command1.Parameters["@days_out"].Direction = ParameterDirection.Output;
            command1.Parameters.Add("@message_out", SqlDbType.VarChar,50);
            command1.Parameters ["@message_out"].Direction = ParameterDirection.Output;
            command1.Parameters.Add("@dateess_out", SqlDbType.Date);
            command1.Parameters["@dateess_out"].Direction = ParameterDirection.Output;
            ///////////////////////////   
            connection.Open();
            command1.CommandType = CommandType.StoredProcedure;
            command1.ExecuteNonQuery();               
            days_penthwork_tx.Text = Convert.ToString(command1.Parameters["@days_out"].Value);
            message_tx.Text = Convert.ToString(command1.Parameters["@message_out"].Value);
            ///the above parameter contains rows with dates
            Label12.Text = Label12.Text + Convert.ToString(command1.Parameters["@dateess_out"].Value);          

            connection.Close();//close connection 
        }
        
        return "success";
    }
    catch (Exception e)
    {
        return e.ToString();
    }
}

My SQL Server stored procedure:

the results

and the query when c# run the code

declare @p4 int
set @p4 = 3

declare @p5 varchar(50)
set @p5 = 'some text'

declare @p6 date
set @p6 = NULL

exec penthhmera_proc @prs_nmb_pen = '274484',
                     @month_pen = 1,
                     @year_int = 2021,
                     @days_out = @p4 output,
                     @message_out = @p5 output,
                     @dateess_out = @p6 output

select @p4, @p5, @p6

I think that with that way @p6 is always null.

Finally I want to load all the values from the second recordset to a Gridview or something like a table in order to show it in my webform.

Any idea?

Thanks in advance

you should not use output parameter. see this [link](https://stackoverflow.com/questions/12715620/how-do-i-return-multiple-result-sets-with-sqlcommand) — Mahdi Rahimi, Jan 09 '21 at 13:45
this question is about c# and ADO.net or Entity framework or asp please don't use sql server tag — Mahdi Rahimi, Jan 09 '21 at 13:47
You have to publish your stored procedure script. This is the most important. — Serge, Jan 09 '21 at 14:03
What is the problem with the output parameters? Are they definitely returning the wrong result? @MahdiRahimi This is if anything a C# with SQL Server question, and ASP has nothing to do with it. Why shouldn't he use output parameters here? For single values it's quite standard — Charlieface, Jan 09 '21 at 19:18
The problem is that the second result is with multiple rows and I don't know how to catch it. See the picture — David Dan, Jan 09 '21 at 21:26
`ExecuteNonQuery` means no results will be returned, only output parameters. If you want results, use `ExecuteReader`. Get multiple resultsets (`SELECTs`) with `NextResult` — Charlieface, Jan 09 '21 at 21:47
@Charlieface all of ORM have some solutions for this problem but all of them use ResultSet instead output parameter its my point for this question. SQL server return query results but in the c# he have wrong way to give result then the question have no relation to sql server its about c# and DataBase providers — Mahdi Rahimi, Jan 10 '21 at 05:54
@MahdiRahimi See [here](https://stackoverflow.com/questions/22353881/) how to use output params in Dapper, and [here](https://stackoverflow.com/questions/6192991/) for EF. I don't follow you: the question relates to connecting *specifically* to SQL Server from C# and getting output params, this uses `SqlConnection` not `DbConnection` etc which is specific to SQL Server and an ORM is not being used, and the way to pass parameters back and forth does vary between DBs, so [sql-server] tag is legit here, [asp] and [entity-framwork] is not. — Charlieface, Jan 10 '21 at 06:02
@Charlieface both of links have some change in c# code and solved his problem !!!!! its mean these questions related to c# — Mahdi Rahimi, Jan 10 '21 at 06:13

score 0 · Answer 1 · answered Jan 09 '21 at 23:52

ExecuteReader was the answer. thnx Charlieface.

connection.Open();
            command1.CommandType = CommandType.StoredProcedure;

            SqlDataReader dr = command1.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    //some code
                }
                dr.NextResult();
                while (dr.Read())
                {
                   //some code
                }
            }
            else
            {
                Console.WriteLine("No data found.");
            }

Stored procedure with input & output parameters and 2 recordsets

1 Answers1