ADO - DataSet with Stored Procedure

Using ADO.NET DataSet with Stored Procedure in C# allows you to populate a DataSet object with the results returned from a stored procedure. Here's an example of how to call a stored procedure with input parameters and populate a DataSet using ADO.NET in C#:

Create SQL Server Stored Procedure with Input Parameters:

Let's consider a stored procedure that accepts a category ID as input and returns the products belonging to that category:

CREATE PROCEDURE GetProductsByCategory
    @CategoryID int
AS
BEGIN
    SELECT * FROM Products WHERE CategoryID = @CategoryID;
END

Example to Understand How to Call a Stored Procedure with Input Parameter in C# using ADO.NET DataSet:

string connectionString = "YourConnectionString";
string storedProcedureName = "GetProductsByCategory";
int categoryID = 1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(storedProcedureName, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@CategoryID", categoryID);
    SqlDataAdapter adapter = new SqlDataAdapter(command);
    DataSet dataSet = new DataSet();
    connection.Open();
    adapter.Fill(dataSet);
    connection.Close();
}

Explanation:

First, we define the connection string to the SQL Server database.

Then, we create a new SqlCommand object with the stored procedure name and connection.

We set the CommandType property of the command to CommandType.StoredProcedure.

After that, we add the input parameter to the command using the Parameters.AddWithValue() method.

Next, we create a new SqlDataAdapter object and pass the command to it.

We also create a new DataSet object to store the data retrieved from the stored procedure.

After opening the connection, we use the Fill() method of the data adapter to populate the DataSet with the results of the stored procedure.

Finally, we close the connection.

By using ADO.NET DataSet with stored procedures, you can retrieve and manipulate data from the database and store it in a disconnected and independent representation. This provides flexibility in working with the data and allows for offline operations without the need for a continuous database connection.