Stored procedure return into DataSet in C# .Net

I want to return virtual table from stored procedure and I want to use it in dataset in c# .net. My procedure is a little complex and can’t find how to return a table and set it in a dataset

Here is my procedure to modify:

ALTER PROCEDURE [dbo].[Procedure1] 

    @Start datetime, 
    @Finish datetime,
    @TimeRange time
AS
BEGIN

    SET NOCOUNT ON;

    declare @TimeRanges as TABLE (SessionStart datetime, SessionEnd datetime);

     with TimeRanges as (
  select @Start as StartTime, @Start + @TimeRange as EndTime
  union all
  select StartTime + @TimeRange, EndTime + @TimeRange
    from TimeRanges
    where StartTime < @Finish )
  select StartTime, EndTime, Count( Test.ScenarioID ) as TotalPeaks
    from TimeRanges as TR left outer join
      dbo.Test as Test on TR.StartTime <= Test.SessionStartTime and Test.SessionCloseTime < TR.EndTime
    group by TR.StartTime, TR.EndTime   
END

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Try this

    DataSet ds = new DataSet("TimeRanges");
    using(SqlConnection conn = new SqlConnection("ConnectionString"))
    {               
            SqlCommand sqlComm = new SqlCommand("Procedure1", conn);               
            sqlComm.Parameters.AddWithValue("@Start", StartTime);
            sqlComm.Parameters.AddWithValue("@Finish", FinishTime);
            sqlComm.Parameters.AddWithValue("@TimeRange", TimeRange);

            sqlComm.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = sqlComm;

            da.Fill(ds);
     }

Method 2

I should tell you the basic steps and rest depends upon your own effort. You need to perform following steps.

• Create a connection string.
• Create a SQL connection
• Create SQL command
• Create SQL data adapter
• fill your dataset.

Do not forget to open and close connection. follow this link for more under standing.

Method 3

You can declare SqlConnection and SqlCommand instances at global level so that you can use it through out the class. Connection string is in Web.Config.

SqlConnection sqlConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SqlConnector"].ConnectionString);
SqlCommand sqlcomm = new SqlCommand();

Now you can use the below method to pass values to Stored Procedure and get the DataSet.

public DataSet GetDataSet(string paramValue)
{
    sqlcomm.Connection = sqlConn;
    using (sqlConn)
    {
        try
        {
            using (SqlDataAdapter da = new SqlDataAdapter())
            {  
                // This will be your input parameter and its value
                sqlcomm.Parameters.AddWithValue("@ParameterName", paramValue);

                // You can retrieve values of `output` variables
                var returnParam = new SqlParameter
                {
                    ParameterName = "@Error",
                    Direction = ParameterDirection.Output,
                    Size = 1000
                };
                sqlcomm.Parameters.Add(returnParam);
                // Name of stored procedure
                sqlcomm.CommandText = "StoredProcedureName";
                da.SelectCommand = sqlcomm;
                da.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet ds = new DataSet();
                da.Fill(ds);                            
            }
        }
        catch (SQLException ex)
        {
            Console.WriteLine("SQL Error: " + ex.Message);
        }
        catch (Exception e)
        {
            Console.WriteLine("Error: " + e.Message);
        }
    }
    return new DataSet();
}

The following is the sample of connection string in config file

<connectionStrings>
    <add name="SqlConnector"
         connectionString="data source=.SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=YourDatabaseName;User id=YourUserName;Password=YourPassword"
         providerName="System.Data.SqlClient" />
</connectionStrings>

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0