Sending output parameters and getting multiple datasets from Stored procedure C#

Question

My stored procedure :-

DECLARE @return_value int,
        @Sum int,
        @Total float

EXEC    @return_value = [dbo].[mysamplesp]
        @sd = '2014-09-23 00:00:00.000',
        @ed = '2014-09-30 00:00:00.000',
        @ID = '40494',
        @sp = NULL,
        @Ct = NULL,
        @GD = NULL,
        @Sum = @Sum OUTPUT,
        @Total = @Total OUTPUT

SELECT  @Sum as N'@Sum',
        @Total as N'@Total'

SELECT  'Return Value' = @return_value

Execution result;-

dataset1-

col1    col2    col3    col4    col5

datase2-

@Sum    @Total

dataset 3-

return_value

My C# implementation:-

Dictionary<string, object> storage = new Dictionary<string, object>();
                storage.Add("sd", "2014-09-23 00:00:00.000");
                storage.Add("ed", "2014-09-30 00:00:00.000");
                storage.Add("ID", "40494");
                storage.Add("Sp", null);
                storage.Add("Ct", null);
                storage.Add("GD", null);
                storage.Add("Sum", null);
                storage.Add("Total", null );

 var spReturn = new Data(Data.DBKEY).ExecuteSotredProcedure("mysamplesp", storage);

. . .

 public int ExecuteSotredProcedure(string qryName, Dictionary<string, object> parameters)
            {
                using (SqlConnection con = new SqlConnection(_conStr))
                {
                    con.Open();
                    using (SqlCommand cmd = new SqlCommand(qryName, con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        foreach (var key in parameters.Keys)
                        {
                            cmd.Parameters.AddWithValue(key, parameters[key] ?? DBNull.Value);
                        }
                        return cmd.ExecuteNonQuery();
                    }
                }
            }

Q1: How to send output paramenters to Sp using a dictionary in C#? possible ?

Q2: How get multiple datasets as result out of SP ?

MantasG · Accepted Answer · 2014-11-17T08:59:14.257

2

For Q1, you should specify sql parameter direction to ParameterDirection.Output

For Q2, use SqlDataReader

var cmd = sqlConnection.CreateCommand();
cmd.CommandText = "[dbo].[mysamplesp]";
cmd.CommandType = CommandType.StoredProcedure;
sqlConnection.Open();
var reader = cmd.ExecuteReader();
// process first dataset
reader.NextResult();
// process second dataset
// etc

edited Nov 17 '14 at 08:59

answered Nov 17 '14 at 07:44

MantasG

111
6

I added the code like below - `cmd.Parameters["Sum"].Direction = ParameterDirection.Output; cmd.Parameters["Total"].Direction = ParameterDirection.Output;` But getting `String[6]: the Size property has an invalid size of 0.` Error on ` var reader = cmd.ExecuteReader();` – Murali Uppangala Nov 17 '14 at 08:50
1

Try to set your the `Size` property of your output parameters, like: `cmd.Parameters["Sum"].Size = 5`. Refer to [this link](http://stackoverflow.com/questions/17494990/the-size-property-has-an-invalid-size-of-0) – MantasG Nov 17 '14 at 09:02
But am not able to get 2nd dataset from the SP.The reader shows only one(one set with 4 columns) .even reader.NextResult() couldnt find it.. – Murali Uppangala Nov 17 '14 at 10:14
Can you provide full code of how you are executing the procedure? – MantasG Nov 17 '14 at 10:30

Sending output parameters and getting multiple datasets from Stored procedure C#

1 Answers1