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

this question is about c# and ADO.net or Entity framework or asp please don't use sql server tag — Mahdi Rahimi
– Mahdi Rahimi, Commented Jan 9, 2021 at 13:47
You have to publish your stored procedure script. This is the most important. — Serge
– Serge, Commented Jan 9, 2021 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
– Charlieface, Commented Jan 9, 2021 at 19:18

David Dan · Accepted Answer · 2021-01-09 23:52:00Z

0

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

answered Jan 9, 2021 at 23:52

David Dan

35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored procedure with input & output parameters and 2 recordsets

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related