ASP.NET Web API (.NET Framework) · Data

Stored Procedures

Many enterprise databases expose their logic as stored procedures. Your Web API 2 code calls them, passes parameters, reads results — and turns SQL errors into proper HTTP responses.

Intermediate 6 min Lesson 15 of 36Covers ASP.NET Web API 2.2 on .NET Framework 4.8.

Before you start:SQL Server SQL Server: Stored Procedures

0/36 in ASP.NET Web API

On this page · 6 min · Intermediate

Interview questions

Learning objectives#

Call a stored procedure with ADO.NET and CommandType.StoredProcedure
Use input and output parameters
Map a result set to a class with EF6 Database.SqlQuery<T>
Translate a THROW from SQL into a 409 response

Explanation#

Set command.CommandType = CommandType.StoredProcedure and use the procedure name as the command text. Add parameters exactly as for normal SQL.

Output parameters need Direction = ParameterDirection.Output; read their Value after the command runs.

EF6 can map procedure results to any class whose property names match the columns: db.Database.SqlQuery<OrderSummary>("EXEC dbo.usp_CustomerSummary @CustomerId", new SqlParameter(...)).

Procedures report business errors with THROW 50001, 'message', 1. In C# that arrives as a SqlException with Number 50001 — catch it and return 409 or 400.

Procedures keep data logic close to the data and let DBAs tune it. The trade-off: logic is split between C# and SQL, and is harder to unit test. Many teams use procedures for complex reports and transactional operations, and EF6 for simple CRUD.

Syntax#

SQL Illustrative

1CREATE OR ALTER PROCEDURE dbo.usp_PlaceOrder
2    @ProductId INT, @Quantity INT, @OrderId INT OUTPUT
3AS
4BEGIN
5    SET NOCOUNT ON;
6    BEGIN TRANSACTION;
7    UPDATE dbo.Products SET Stock = Stock - @Quantity
8    WHERE Id = @ProductId AND Stock >= @Quantity;
9    IF @@ROWCOUNT = 0
10    BEGIN
11        ROLLBACK;
12        THROW 50001, 'Not enough stock.', 1;
13    END
14    INSERT INTO dbo.Orders (ProductId, Quantity) VALUES (@ProductId, @Quantity);
15    SET @OrderId = SCOPE_IDENTITY();
16    COMMIT;
17END

Code example: Calling procedures from a repository and a controller#

C#OrderProcedures.cs Illustrative

1using System.Configuration;
2using System.Data;
3using System.Data.Entity;
4using System.Data.SqlClient;
5using System.Net;
6using System.Threading.Tasks;
7using System.Web.Http;
8using ShopApi.Data;      // ShopDbContext
9

10public class OrderSummary
11{
12    public int OrderCount { get; set; }
13    public decimal TotalSpent { get; set; }
14}
15

16public class OrderProcedures
17{
18    private readonly string _cs = ConfigurationManager.ConnectionStrings["ShopDb"].ConnectionString;
19

20    // ADO.NET: input + output parameters
21    public async Task<int> PlaceOrderAsync(int productId, int quantity)
22    {
23        using (var connection = new SqlConnection(_cs))
24        using (var command = new SqlCommand("dbo.usp_PlaceOrder", connection))
25        {
26            command.CommandType = CommandType.StoredProcedure;
27            command.Parameters.Add("@ProductId", SqlDbType.Int).Value = productId;
28            command.Parameters.Add("@Quantity", SqlDbType.Int).Value = quantity;
29            var orderId = command.Parameters.Add("@OrderId", SqlDbType.Int);
30            orderId.Direction = ParameterDirection.Output;
31

32            await connection.OpenAsync();
33            await command.ExecuteNonQueryAsync();
34            return (int)orderId.Value;
35        }
36    }
37

38    // EF6: map a result set to a class
39    public async Task<OrderSummary> GetCustomerSummaryAsync(int customerId)
40    {
41        using (var db = new ShopDbContext())
42        {
43            return await db.Database
44                .SqlQuery<OrderSummary>("EXEC dbo.usp_CustomerSummary @CustomerId",
45                    new SqlParameter("@CustomerId", customerId))
46                .SingleAsync();
47        }
48    }
49}
50

51public class PlaceOrderRequest
52{
53    public int ProductId { get; set; }
54    public int Quantity { get; set; }
55}
56

57public class OrdersController : ApiController
58{
59    private readonly OrderProcedures _procedures = new OrderProcedures();
60

61    [HttpPost, Route("api/orders")]
62    public async Task<IHttpActionResult> Place(PlaceOrderRequest request)
63    {
64        try
65        {
66            int orderId = await _procedures.PlaceOrderAsync(request.ProductId, request.Quantity);
67            return Created("api/orders/" + orderId, new { orderId });
68        }
69        catch (SqlException ex) when (ex.Number == 50001)
70        {
71            return Content(HttpStatusCode.Conflict, new { message = ex.Message });
72        }
73    }
74

75    [HttpGet, Route("api/customers/{id:int}/summary")]
76    public async Task<IHttpActionResult> Summary(int id)
77    {
78        return Ok(await _procedures.GetCustomerSummaryAsync(id));
79    }
80}

Expected output

POST /api/orders {"ProductId":3,"Quantity":2}   → 201 {"orderId":17}
POST /api/orders {"ProductId":3,"Quantity":99}  → 409 {"message":"Not enough stock."}
GET  /api/customers/5/summary                   → 200 {"OrderCount":4,"TotalSpent":2196.00}

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

The procedure does the stock check, update and insert in one transaction on the server.
In C#, CommandType.StoredProcedure plus the procedure name replaces the SQL text.
@OrderId is an output parameter; its Value is read after ExecuteNonQueryAsync.
THROW 50001 becomes a SqlException with Number 50001; the exception filter (when) catches only that one and returns 409.
Database.SqlQuery<OrderSummary> maps the procedure’s columns OrderCount and TotalSpent to the class by name.

Real-world use case#

A bank’s core system exposes transfers only through audited stored procedures. The Web API 2 layer validates input, calls usp_Transfer with parameters, and maps each THROW number to a clear HTTP error.

Common mistakes#

Building EXEC strings with concatenation

"EXEC usp_Find '" + name + "'" is just as injectable as any other SQL. Use parameters.

Forgetting SET NOCOUNT ON

Without it, “rows affected” messages can confuse some clients and add network chatter. Start procedures with SET NOCOUNT ON.

Catching every SqlException as 409

Timeouts and deadlocks are server problems, not conflicts. Filter on specific error numbers.

Best practices#

Use CommandType.StoredProcedure, not "EXEC ..." text, in ADO.NET
Reserve custom error numbers (50001+) for business errors
Keep procedures focused and versioned in source control
Wrap multi-step changes in a transaction inside the procedure

Remember this#

CommandType.StoredProcedure + procedure name.

Output parameter: Direction = Output, read Value after executing.

THROW 50001 in SQL → SqlException.Number 50001 in C#.

Practice exercise#

Your task: Call a procedure dbo.usp_SearchProducts @Term NVARCHAR(50) that returns Id, Name, Price, using EF6 Database.SqlQuery into a ProductRow class, and return the list from GET api/products/proc-search?term=pen.

Need a hint?

SqlQuery<ProductRow>("EXEC dbo.usp_SearchProducts @Term", new SqlParameter("@Term", term)).ToListAsync().

Show solution

C# Illustrative

1using System.Data.Entity;
2using System.Data.SqlClient;
3using System.Threading.Tasks;
4using System.Web.Http;
5using ShopApi.Data;
6

7public class ProductRow
8{
9    public int Id { get; set; }
10    public string Name { get; set; }
11    public decimal Price { get; set; }
12}
13

14public class ProcSearchController : ApiController
15{
16    [HttpGet, Route("api/products/proc-search")]
17    public async Task<IHttpActionResult> Search(string term = "")
18    {
19        using (var db = new ShopDbContext())
20        {
21            var rows = await db.Database
22                .SqlQuery<ProductRow>("EXEC dbo.usp_SearchProducts @Term", new SqlParameter("@Term", term))
23                .ToListAsync();
24            return Ok(rows);
25        }
26    }
27}

Mini quiz#

Stored Procedures quizMultiple choice Question 1 of 3

How do you mark a parameter as an output parameter in ADO.NET?

Score so far: 0/0

Want more? Practise all ASP.NET Web API MCQs · ASP.NET Web API interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.

On this page

Related tutorials

Interview questions

Learning objectives#

Explanation#

Syntax#

Code example: Calling procedures from a repository and a controller#

Step-by-step explanation#

Real-world use case#

Common mistakes#

Best practices#

Remember this#

Practice exercise#

Mini quiz#

Related topics#

My notes#