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 ?

Answer 1

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