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

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Oct 19 '12 at 11:57

codingbiz · Accepted Answer · 2014-05-29T13:39:50.997

68

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

edited May 29 '14 at 13:39

answered Oct 19 '12 at 12:14

codingbiz

26,179
8
59
96

1

One thing that got me when trying to use this answer was that i had to set my command type to `CommandType.StoredProcedure`. so it would look like this `sqlComm.CommandType = CommandType.StoredProcedure`. – Pow-Ian Jul 18 '13 at 15:15
1

no problem. I sat and struggled with it for an hour or so before I googled it and found the [MS article that gave me the missing piece](http://support.microsoft.com/kb/310070). But this certainly got me 99.9% of the way there. Great answer. – Pow-Ian Jul 18 '13 at 15:29
1

@codingbiz How if t sql return output param beside datatable, too? Is it possible to get it too? How? sample? – Ahmad Ebrahimi Jul 30 '15 at 09:02
1

`ds.Tables[0].Rows[0][0]` to get first column of first row, since you only have one row and one column – codingbiz Jul 30 '15 at 10:13
Output parameter is used when inserting... this is a select query method – codingbiz Jul 30 '15 at 10:14

muhammad kashif · Answer 2 · 2012-10-19T12:58:56.303

8

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.

edited Oct 19 '12 at 12:58

answered Oct 19 '12 at 12:17

muhammad kashif

2,566
3
26
49

as answers go I don't think this was very helpful. Unless otherwise stated I feel that people want specifics rather than generics. – M_Griffiths Jan 15 '16 at 17:05

score 3 · Answer 3 · answered Apr 12 '15 at 08:33

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>

No, you should always create SqlCommand and SqlConnection objects just as you use them, inside `using` statements so they’re disposed afterwards and to ensure they’re not used by multiple threads. — Rory, Apr 08 '21 at 21:29
Since SqlCommand and SqlConnection are used in global level for multiple methods as well (for reuse purpose), I have neither used `using` nor any command to dispose. Ideally, I used to dispose this wherever necessary/application ends. Also, please be noted that I have not mentioned the complete code from the program as my intention was to answer for what OP asked. That's the reason you are not seeing any code for disposal @Rory — Sarath Subramanian, Apr 09 '21 at 14:54
What I'm saying is that it's best practice to NOT do what you're suggesting. Your example should not be followed. There's no good reason to keep a SqlCommand or SqlConnection object beyond when you're using it directly, and there's lots of downsides (e.g. use by multiple threads, not disposing properly, preventing efficient connection pooling, keeping more connections open than needed). — Rory, Apr 10 '21 at 22:41
Also it looks like there's a few bugs in `GetDataSet()`, e.g. it always returns a new empty `DataSet` object, it disposes the `sqlconn` variable after it executes the first time. Here's a good example of filling a DataSet from a proc https://stackoverflow.com/a/12974098/8479 — Rory, Apr 10 '21 at 22:49

Stored procedure return into DataSet in C# .Net

3 Answers3

Linked

Related