Stored procedure return into DataSet in C# .Net
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); }
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.
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>