Entity Framework Core 5 Stored Procedures .NET 5 (C#)
Introduction
Although EF Core (Entity Framework Core) 5 is commonly used with a DbContext along with one or more models to interact with data there may be times when developers are required to interact with data using stored procedures.
- Follow through the contents below to learn the very basics for working with stored procedures, in this case the database used is Microsoft SQL-Server. what should not be done is to use stored procedures just to use them as with any direction for coding there are advantages and disadvantages.
- In most code samples to follow naming of classes are lengthy which self describes the class is for.
Requirements
- Microsoft Visual Studio 2019
- Microsoft SQL-Server, express edition or better.
- Optional but recommended, SSMS which is better than writing SQL in Visual Studio.
- Familiar with C#
- Understand basic SQL
- Familiar with basic Entity Framework Core
Definition of Stored Procedures
A stored procedure is a prepared SQL code that you can save, so the code can be reused repeatedly which are stored in a database. Basic usage, common SQL statements with or without parameters while other uses include data-validation or access-control mechanisms. Stored procedures may return result sets (a DataTable for instance), i.e., the results of a SELECT statement. Such result sets can be processed using cursors, by other stored procedures, by associating a result-set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. Stored-procedure flow-control statements typically include IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared.
Stored procedures have been viewed as the de facto standard for applications to access and manipulate database information using codified methods, or “procedures.” This is largely due to what they offer developers: the opportunity to couple the set-based power of SQL with the iterative and conditional processing control of code development. Instead of writing inline SQL and then attempting to manipulate the data from within the code.
Note that all example which follow are starter code samples, as a stored procedure becomes complex the same basics apply.
Advantages of Using Stored Procedures
The following are not all inclusive reasons in regards to advantages of using Stored Procedures.
These advantages are subjective to developers, database administrators, business and/or security requirements.
- Maintainability: Because scripts are in a single location updates and tracking of dependencies based on schema changes become easier.
- Testing: Can be tested independent of an application.
- Isolation of Business Rules: Having Stored Procedures in one location means that there’s no confusion of having business rules spread over potentially disparate code files in the application.
- Speed/Optimization: Stored Procedures are cached on the server. The first execution may take longer while later calls will be shorter. This means the query is optimized along with proper indexing server side.
- Security:
- Limit direct access to tables via defined roles in the database
- Securing just the data and the code that accesses it is easier than applying that security within the application code itself
Disadvantages of Using Stored Procedures
- Someone must keep track of changes between database environments, make a test changes in development environment and promote, then test in both test and production environments.
- Testing:
- Stored Procedures tend to be utilized on more than one application. This means there are chances all affected applications do not get tested which may lead to unexpected results.
- Any data errors in handling Stored Procedures are not generated until runtime
- Security: When done for the wrong reasons can lead to longer time to work with a specific Stored Procedure where time will vary dependent on the process to gain access to a Stored Procedure followed by time to work on the SQL.
- Stored procedure code is not as robust as application code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive)
Basic example no parameters
A requirement is to select all customers from a customer table joined with a contacts and contacts type table using the following stored procedure.
CREATE PROCEDURE dbo.uspGetCustomers1
AS
BEGIN
SET NOCOUNT ON;
SELECT Cust.CustomerIdentifier,
Cust.CompanyName,
Cust.ContactId,
CT.ContactTitle,
C.FirstName,
C.LastName,
Cust.Street,
Cust.City,
Cust.Region,
Cust.PostalCode,
Cust.Phone,
Cust.ContactTypeIdentifier,
Cust.ModifiedDate
FROM Customers AS Cust
INNER JOIN Contacts AS C ON Cust.ContactId = C.ContactId
INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier;
END;
The first task is to have a class which represents data being returned from the above stored procedures to match columns.
public partial class uspGetCustomers1Result
{
public int CustomerIdentifier { get; set; }
public string CompanyName { get; set; }
public int? ContactId { get; set; }
public string ContactTitle { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Street { get; set; }
public string City { get; set; }
public string Region { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public int? ContactTypeIdentifier { get; set; }
public DateTime? ModifiedDate { get; set; }
}
This is followed by a class to run the stored procedure.
public partial class StoredProcedures
{
private readonly NorthwindContext _context;
public StoredProcedures(NorthwindContext context)
{
_context = context;
}
public async Task<uspGetCustomers1Result[]> UspGetCustomers1()
{
return await Task.Run(async () =>
{
var result = await _context.SqlQuery<uspGetCustomers1Result>("EXEC [dbo].[uspGetCustomers1]");
return result;
});
}
}
- Although the method UspGetCustomers1 is asynchronous it's not a requirement, the decision to use asynchronous or not is dependent on responsiveness of the task as it relates to the user interface.
- SqlQuery extension requires a type which in this case is uspGetCustomers1Result.
Basic example with parameters
This example uses a stored procedures like above while in this case there is a where condition, get customers by country identifier. The likelihood there will more than one record matching the where an array will be returned.
CREATE PROCEDURE dbo.uspCustomersByCountryIdentifier(@CountryIdentifier INT)
AS
BEGIN
SELECT Cust.CustomerIdentifier,
Cust.CompanyName,
Cust.ContactId,
CT.ContactTitle,
C.FirstName,
C.LastName,
Cust.Street,
Cust.City,
Cust.Region,
Cust.PostalCode,
Cust.Phone,
Cust.ContactTypeIdentifier,
Cust.ModifiedDate,
Cust.CountryIdentifier,
CO.Name
FROM Customers AS Cust
INNER JOIN Contacts AS C ON Cust.ContactId = C.ContactId
INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier
INNER JOIN Countries AS CO ON Cust.CountryIdentifier = CO.CountryIdentifier
WHERE Cust.CountryIdentifier = @CountryIdentifier;
END;
This class represents data returned from the stored procedure above.
public partial class CustomersByCountryIdentifierStoredProcedure
{
public int CustomerIdentifier { get; set; }
public string CompanyName { get; set; }
public int? ContactId { get; set; }
public string ContactTitle { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Street { get; set; }
public string City { get; set; }
public string Region { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public int? ContactTypeIdentifier { get; set; }
public DateTime? ModifiedDate { get; set; }
public int? CountryIdentifier { get; set; }
public string Name { get; set; }
}
Next a method is needed to return data which accepts a country identifier and returns an array of CustomersByCountryIdentiferStoredProcedure.
public async Task<CustomersByCountryIdentifierStoredProcedure[]>
UspCustomersByCountryIdentifier(int? countryIdentifier)
{
var parameterCountryIdentifier = new SqlParameter
{
ParameterName = "CountryIdentifier",
Precision = 10,
Size = 4,
SqlDbType = System.Data.SqlDbType.Int,
Value = countryIdentifier,
};
CustomersByCountryIdentifierStoredProcedure[] result = await _context
.SqlQuery<CustomersByCountryIdentifierStoredProcedure>
("EXEC [dbo].[uspCustomersByCountryIdentifier] @CountryIdentifier ",
parameterCountryIdentifier);
return result;
}
- EXEC [dbo].[uspCustomersByCountryIdentifier] @CountryIdentifier indicates the stored procedure name followed by the parameter name in the stored procedure. Note the space between the stored procedure name and the parameter. If there are multiple parameters they are to be separated by a comma.
- parameterCountryIdentifier is the parameter sent to SqlQuery which defines what the stored procedures needs to execute. If not configured properly SQL-Server will thrown a runtime exception.
Repurposing
Suppose a requirement is to get only a single column, contact first name or perhaps two columns, contact first and last name. The same stored procedure may be used by writing a method which calls the stored procedure above.
Here one column is returned, first name of a contact. If two or more columns are needed a select can be performed to a class with properties for contact first and last name.
public async Task<List<string>> CustomersByCountryIdentifier(int? countryIdentifier)
{
return await Task.Run(async () =>
{
var customerResults = await UspCustomersByCountryIdentifier(countryIdentifier);
return customerResults.Select(cust => cust.FirstName).ToList();
});
}
In both cases the important aspect is to have a class which represents returning data.
Basic insert new record
To insert records the same logic applies as in the last select example with parameters other than to return a new primary key. A basic insert stored procedures to add a record then pass back the new primary key.
CREATE PROCEDURE [dbo].[uspInsertCategory]
@CategoryName NVARCHAR(15),
@Description ntext,
@Identity INT OUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO dbo.Categories (CategoryName, Description) VALUES (@CategoryName, @Description);
SET @Identity = SCOPE_IDENTITY()
END
- Parameters 1 and two are used to set column values
- Parameter 3 is used to return the new primary key. Note after the type OUT is used to designate the parameter is for returning a value.
- SET @Identity = SCOPE_IDENTITY() retrieves the new primary key on a successful insert.
C# code for performing the insert.
public class Operations
{
/// <summary>
/// Insert new category
/// </summary>
/// <param name="categoryName">Category name required</param>
/// <param name="description">Category description</param>
public static void InsertCategory(string categoryName, string description)
{
using var context = new NorthWindContext();
try
{
var parameters = new[] {
new SqlParameter("@CategoryName", SqlDbType.NVarChar)
{
Direction = ParameterDirection.Input,
Value = categoryName
},
new SqlParameter("@Description", SqlDbType.NText)
{
Direction = ParameterDirection.Input,
Value = description
},
new SqlParameter("@Identity", SqlDbType.Int)
{
Direction = ParameterDirection.Output,
Value = 0
}
};
context.Database.ExecuteSqlRaw(
"exec uspInsertCategory @CategoryName,@Description,@Identity out", parameters:
parameters);
var newPrimaryKey = Convert.ToInt32(parameters[2].Value);
Debug.WriteLine(newPrimaryKey.ToString());
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
}
- parameters array of SqlParamter defines each parameter sent to the stored procedure
- Note the third parameter Direction is OUT which matches the parameter definition in the stored procedure while the first two direction are set to input.
- ExecuteSqlRaw is perfect as there is only one value returned.
- The first parameter sent to ExecuteSqlRaw is the stored procedure name followed by a comma delimited names of parameters expected by the stored procedure.
- To get the new primary key cast the third paramter to an int.
Deleting records
Deleting records follows the same logic as presented other than there should be assertions performed to see if the record could be removed which may not be possible because of a rule or a rule not set such as cascading to child records which still can pose issues. Note as with the insert this stored procedure has an out parameter which can be handled exactly the same as the insert.
CREATE PROCEDURE [dbo].[DeleteCustomer]
@flag bit output,-- return 0 for fail,1 for success
@Identity int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
DELETE FROM Customer WHERE Identifier = @Identity set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
Using a tool to assist with writing code
A good deal of the code required is easy enough to write out while a Visual Studio extension, EF Power Tools (free) will do just about all the work for a developer. To get a tasted of EF Power Tools.
Summary
Basics have been presented to get started working with stored procedures using Entity Framework Core 5 which can provide a head start to working into intermediate like shown below or complex stored procedures with business logic and error handling.
CREATE PROCEDURE [dbo].[CustomerInsertOrUpdate]
@Identifier int = NULL OUTPUT,
@CompanyName nvarchar(255),
@ContactName nvarchar(255),
@ContactTitle nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;
IF @Identifier IS NULL
BEGIN
INSERT INTO Customer
(CompanyName,ContactName,ContactTitle)
VALUES
(@CompanyName, @ContactName, @ContactTitle)
SET @Identifier = SCOPE_IDENTITY()
END
ELSE
BEGIN
UPDATE Customer
SET CompanyName = @CompanyName,
ContactName = @ContactName,
ContactTitle = @ContactTitle
END
END
Working with error handling, a little more logic but the basics still apply.
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
@BusinessEntityID [int],
@JobTitle [nvarchar](50),
@HireDate [datetime],
@RateChangeDate [datetime],
@Rate [money],
@PayFrequency [tinyint],
@CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE [HumanResources].[Employee]
SET [JobTitle] = @JobTitle
,[HireDate] = @HireDate
,[CurrentFlag] = @CurrentFlag
WHERE [BusinessEntityID] = @BusinessEntityID;
INSERT INTO [HumanResources].[EmployeePayHistory]
([BusinessEntityID]
,[RateChangeDate]
,[Rate]
,[PayFrequency])
VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
See also
- SQL Server Books Online
- Entity Framework Core shadow properties
- Entity Framework Core projections
- Entity Framework Core/Windows forms tips and tricks
- SQL-Server stored procedures for C# windows forms
- SSMS Generate Scripts for stored procedures without dynamic SQL
Source code
Resides in the following GitHub repository which has other projects besides what is needed to this article to be cloned. To download only the needed projects, three, two for stored procedures and one for reading appsettings.json to get connection strings do the following.
- Check that a current version of GIT is installed otherwise the script may fail.
- Create a temp folder e.g. C:\GitDownloads
- Create a batch file with the contents below.
- Run the batch file, wait for the download to complete
- Copy the projects to a Visual Studio solution and do a NuGet restore packages.
mkdir code
cd code
git init
git remote add -f origin https://github.com/karenpayneoregon/efcore5-getting-started
git sparse-checkout init --cone
git sparse-checkout add ConfigurationHelper
git sparse-checkout add EntityFrameworkCoreStoredProcedures
git sparse-checkout add StoredProcedureInsertNewCategory
git pull origin master
:clean-up
del .gitattributes
del .gitignore
del .yml
del .editorconfig
del *.md
del *.sln