Stored procedure return into DataSet in C# .Net

Question

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 

Answer 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);      } 

Answer 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.