Calling a Stored Procedure from C#

After this lesson you can

• Call a stored procedure from C# with SqlCommand, CommandType.StoredProcedure and parameters

Key takeaways

• To call a procedure, put its name in the SqlCommand and set CommandType to CommandType.StoredProcedure.
• Add each parameter by its name, @ included: command.Parameters.AddWithValue("@CategoryName", "Produce").
• Read the rows a procedure returns with ExecuteReader, and a single value with ExecuteScalar, as with any query.
• Leave out a parameter that has a default, and the procedure uses the default.
• Without CommandType.StoredProcedure, the procedure runs with no parameters, and SQL Server says one was not supplied.

Calling a stored procedure from C# is almost the same as running a query with ADO.NET, from Connecting Apps to SQL Server. Three things change:

1. The command's text is the procedure's name, not a SELECT.
2. The command's CommandType is CommandType.StoredProcedure.
3. Each parameter is added by its name, @ included, as in the procedure.

Reading rows

This program calls GetProductsByCategory, from the last lesson, and prints what it returns:

using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

using SqlCommand command = new SqlCommand("GetProductsByCategory", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CategoryName", "Produce");

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    string name = (string)reader["ProductName"];
    decimal price = (decimal)reader["UnitPrice"];
    Console.WriteLine(quot;{name}: {price:0.00}");
}

Longlife Tofu: 10.00
Manjimup Dried Apples: 53.00
Rössle Sauerkraut: 45.60
Tofu: 23.25
Uncle Bob's Organic Dried Pears: 30.00

CommandType lives in the System.Data namespace, hence the extra using. The reader reads the procedure's columns by name, exactly as it would a query's.

Reading one value

CountProductsInCategory returns a single value, so call it with ExecuteScalar:

using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

foreach (string category in new[] { "Seafood", "Produce", "Beverages" })
{
    using SqlCommand command = new SqlCommand("CountProductsInCategory", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@CategoryName", category);

    int count = (int)command.ExecuteScalar();
    Console.WriteLine(quot;{category}: {count} products");
}

Seafood: 12 products
Produce: 5 products
Beverages: 12 products

Leaving out a parameter with a default

SearchProducts has a default for @MaxPrice. Leave that parameter out in C#, and the default is used, just as in EXEC:

using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

using SqlCommand command = new SqlCommand("SearchProducts", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Text", "Sauce");

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader["ProductName"]);
}

Northwoods Cranberry Sauce
Louisiana Fiery Hot Pepper Sauce

Common problems

Check yourself

1 question to try on the lesson page.

So what

A procedure's name, CommandType.StoredProcedure and named parameters: that's all it takes for your C# to use logic stored in the database. The DAOs you write later can call procedures and queries side by side.

Key terms

• Stored Procedure
• CommandType

Cheat sheets

• Calling a Stored Procedure from C#