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  lt   Finish     select StartTime  EndTime  Count  Test ScenarioID   as TotalPeaks     from TimeRanges as TR left outer join       dbo Test as Test on TR StartTime  lt   Test SessionStartTime and Test SessionCloseTime  lt  TR EndTime     group by TR StartTime  TR EndTime    END

User · Answer

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.

User · Answer

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

User · Answer

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   lt connectionStrings gt       lt add name  SqlConnector           connectionString  data source   SQLEXPRESS Integrated Security SSPI Initial Catalog YourDatabaseName User id YourUserName Password YourPassword           providerName  System Data SqlClient    gt   lt  connectionStrings gt

[c#] Stored procedure return into DataSet in C# .Net

Examples related to c#

Examples related to asp.net

Examples related to stored-procedures

Examples related to dataset