Calling a Stored Procedure from C#
In ADO.NET, put the procedure's name in a SqlCommand, set its CommandType to StoredProcedure, and add each parameter by name. Then read the results as from any query.
Journeyman · C# · T-SQL · Updated
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
SqlCommandand setCommandTypetoCommandType.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 withExecuteScalar, 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:
- The command's text is the procedure's name, not a
SELECT. - The command's
CommandTypeisCommandType.StoredProcedure. - 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
| What you see | What to do |
|---|---|
Could not find stored procedure 'GetProductsByCategory' |
The procedure doesn't exist in the database you connected to. Create it in SSMS, in the right database. |
Procedure or function 'GetProductsByCategory' expects parameter '@CategoryName', which was not supplied |
A parameter without a default is missing or misspelled, or CommandType wasn't set to StoredProcedure: without it, the procedure runs with no parameters at all. |
Do it: set up the command that counts a category's products
The store's stock page shows how many products each category has. It gets each number from the stored procedure CountProductsInCategory, from the last lesson, which takes one parameter, @CategoryName. Write the method that sets up the command for it.
The practice box can't reach SQL Server, so the bottom of Program.cs has a stand-in for SqlCommand, named StandInCommand, and for the CommandType values. Leave them as they are. A StandInCommand has what a SqlCommand has for this job:
CommandText: the text the command runs, given when it's made:new StandInCommand("...").CommandType:CommandType.Text(a query, the default) orCommandType.StoredProcedure.Parameters: the parameters, each added withParameters.AddWithValue(name, value).
The method gets one value:
categoryName: the category whose products to count, astring("Seafood").
For example, CountCommand("Seafood") returns a command set up like this:
| Part | Value |
|---|---|
CommandText |
"CountProductsInCategory" |
CommandType |
CommandType.StoredProcedure |
Parameters |
one: @CategoryName, holding "Seafood" |
The tests read the parameters as a list of lines, one per parameter, written name = value. For this example, that list is ["@CategoryName = Seafood"].
Your job: write the method StandInCommand CountCommand(string categoryName).
- Make the command with the procedure's name,
"CountProductsInCategory", as its text. - Set its
CommandTypetoCommandType.StoredProcedure. - Add one parameter, named
"@CategoryName"(the@included), holdingcategoryName. - Return the command.
Practiced live on the lesson page: write the C# right there and check it.
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