Use Microsoft.Data.SqlClient classes with Entity Framework Core
In the previous article we learned to execute stored procedures using SqlCommand and SqlParameter classes. In the examples we developed so far, we used data provider classes directly for all the operations. We didn't use Entity Framework Core at all. What if your application wants to use EF Core? Can we use EF Core and plain data provider objects hand-in-hand? That's what we are going to discuss in this article.
In order to illustrate a few cases where EF Core and plain data provider objects can be used together, we are going to need an EF Core DbContext. First, add Microsoft.EntityFrameworkCore.SqlServer NuGet package to the project. Then add a new class to the DataAccess folder named AppDbContext and write the following code in it:
public class AppDbContext:DbContext
{
public AppDbContext
(DbContextOptions<AppDbContext> options)
:base(options)
{
}
}
You might wonder why our AppDbContext class doesn't have any DbSet properties. That's because in our example Customer is a record type and as per Microsoft's official documentation record types are not well suited for EF Core entities. For the purpose of this article the above basic AppDbContext is sufficient.
Now copy the CustomerStProcRepository.cs file and save it as CustomerEFRepository.cs. Instead of tampering with the previous code we will create a new repository with the desired modifications.
We need to inject the AppDbContext into the newly created repository class as shown below:
public class CustomerEFRepository
: ICustomerRepository
{
private readonly string connectionString;
private readonly AppDbContext db;
public CustomerEFRepository
(IConfiguration config,
AppDbContext db)
{
this.connectionString =
config.GetConnectionString("AppDb");
this.db = db;
}
Notice the code shown in bold letters. The constructor now takes two parameters. The additional AppDbContext parameter injects the DbContext and we store it in db variable for later use.
So far we have been creating SqlConnection and SqlCommand objects ourselves. We will change that in this example. We will use EF Core to get a connection object. Using that connection object we will create a command object.
The following code shows the modified SelectAll() method of the CustomerEFRepository class.
public async Task<List<Customer>> SelectAll()
{
var connection = db.Database.GetDbConnection();
var query = "Customers_SelectAll";
using var command = connection.CreateCommand();
command.CommandText = query;
command.CommandType = CommandType.StoredProcedure;
try
{
await connection.OpenAsync();
using var reader = await
command.ExecuteReaderAsync();
List<Customer> items = null;
if (reader.HasRows)
{
items = new List<Customer>();
while (await reader.ReadAsync())
{
var item = new Customer(
CustomerID: reader.GetString(0),
CompanyName: reader.GetString(1),
ContactName: reader.GetString(2),
Country: reader.GetString(3));
items.Add(item);
}
}
await reader.CloseAsync();
await connection.CloseAsync();
return items;
}
catch
{
if (connection.State != ConnectionState.Closed)
{
await connection.CloseAsync();
}
return null;
}
}
Notice the code shown in bold letters. We use GetDbConnection() method of Database to get a connection object. The data provider classes such as SqlConnection and SqlCommand inherit from DbConnection and DbCommand base classes respectively. The GetDbConnection() method returns a DbConnection object (and not a SqlConnection object).
We then get a DbCommand object from this DbConnection using its CreateCommand() method. So, here we use base classes (DbConnection and DbCommand) instead of derived classes (SqlConnection and SqlCommand). The remaining code of SelectAll() is unchanged.
What if you want to use SqlCommand instead of DbCommand? Of course, you can resort to type casting as shown in the SelectByID() method below:
public async Task<Customer> SelectByID(string id)
{
var connection = db.Database.GetDbConnection();
var query = "Customers_SelectByID";
using var command = new SqlCommand
(query, connection as SqlConnection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CustomerID", id);
try
{
await connection.OpenAsync();
using var reader = await
command.ExecuteReaderAsync();
Customer item = null;
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
item = new Customer(
CustomerID: reader.GetString
(reader.GetOrdinal("CustomerID")),
CompanyName: reader.GetString
(reader.GetOrdinal("CompanyName")),
ContactName: reader.GetString
(reader.GetOrdinal("ContactName")),
Country: reader.GetString
(reader.GetOrdinal("Country")));
}
}
await reader.CloseAsync();
await connection.CloseAsync();
return item;
}
catch
{
if (connection.State != ConnectionState.Closed)
{
await connection.CloseAsync();
}
return null;
}
}
Notice the code shown in bold letters. Here, we create SqlCommand object and while passing connection in the constructor type convert from DbConnection to SqlConnection. The remainder of the code is unchanged.
The Insert(), Update(), and Delete() methods of our repositories are executing action queries such as INSERT, UPDATE, or DELETE. So far we used SqlCommand object to execute them. In the example that follows we will use an EF Core alternative.
public async Task<int> Insert(Customer customer)
{
SqlParameter[] p = new SqlParameter[5];
p[0] = new SqlParameter
("@CustomerID", customer.CustomerID);
p[1] = new SqlParameter
("@CompanyName", customer.CompanyName);
p[2] = new SqlParameter
("@ContactName", customer.ContactName);
p[3] = new SqlParameter
("@Country", customer.Country);
p[4] = new SqlParameter
("@RecCount", SqlDbType.Int);
p[4].Direction = ParameterDirection.Output;
var query = "EXEC Customers_Insert @CustomerID,
@CompanyName, @ContactName, @Country,
@RecCount OUT";
var rowsAffected = await db.Database.
ExecuteSqlRawAsync(query, p);
var count = p[4].Value;
return rowsAffected;
}
Observe this code carefully. We create an array of SqlParameter objects to represent the stored procedure parameters. The RecCount parameter is an output parameter.
We then specify the stored procedure along with its parameters in the query. To execute this stored procedure we use EF Core's ExecuteSqlRawAsync() method. The ExecuteSqlRawAsync() method takes an action query and it's parameters and execute that against the underlying database. The ExecuteSqlRawAsync() returns an integer indicating the number of rows affected.
Although we aren't using the RecCount output parameter value, you can access it using the SqlParameter's Value property.
You can change the Update() and Delete() method on similar lines.
Let's see the final example where data provider objects and EF Core can be used together.
Open Program.cs file and modify the following code:
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddRazorPages();
SqlConnection connection = new SqlConnection
(builder.Configuration.GetConnectionString("AppDb"));
builder.Services.AddDbContext<AppDbContext>
(o => o.UseSqlServer(connection));
builder.Services.AddScoped
<ICustomerRepository, CustomerEFRepository>();
var app = builder.Build();
app.MapRazorPages();
app.Run();
Notice the code shown in bold letters. So far we used to pass a database connection string to the UseSqlServer() method. In this example, we create an SqlConnection object ourselves and pass this SqlConnection to the UseSqlServer() method. Now, EF Core will use our SqlConnection instead of creating itw own. If the SqlConnection is closed, EF Core will open and close it as required. If the SqlConnection is open, EF Core won't close it.
In this article we learned about a few situations where plain data provider objects such as SqlConnection, SqlCommand, and SqlParameter can be used with EF Core. Of course, it's recommended to stick to a consistent data access style in your app but if at all needed you can use them together.
That's it for now! Keep coding!!