EF Core - Làm việc với Stored Procedure EF Core

Làm việc với Stored Procedure EF Core

Trong phần này, bạn sẽ học cách thực thi thủ tục lưu trữ (Stored Procedure ) cơ sở dữ liệu trong Entity Framework Core.

EF Core cung cấp các phương pháp sau để thực hiện một thủ tục được lưu trữ(Stored Procedure):

  1. DbSet<TEntity>.FromSql()
  2. DbContext.Database.ExecuteSqlCommand()

 Có một số hạn chế về việc thực thi các thủ tục được lưu trữ trong cơ sở dữ liệu bằng phương pháp FromSql hoặc ExecuteSqlCommand trong EF Core2:

  1. Kết quả phải là một loại thực thể. Điều này có nghĩa là một thủ tục được lưu trữ phải trả về tất cả các cột của bảng tương ứng của một thực thể.
  2. Kết quả không thể chứa dữ liệu liên quan. Điều này có nghĩa là một thủ tục được lưu trữ không thể thực hiện các JOIN để hình thành kết quả.
  3. Các thủ tục Thêm, Cập nhật và Xóa không thể được ánh xạ với thực thể, vì vậy phương thức SaveChanges không thể gọi các thủ tục được lưu trữ cho các hoạt động CUD.

Chúng ta hãy tạo thủ tục được lưu trữ của chúng tôi trong MS SQL Server trước khi chúng tôi thực thi nó trong EF Core.

Nếu bạn làm theo cách tiếp cận cơ sở dữ liệu đầu tiên, thì hãy thực thi tập lệnh sau trong cơ sở dữ liệu SQL Server cục bộ của bạn:

SE [SchoolDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetStudents]
            @FirstName varchar(50)
        AS
        BEGIN
            SET NOCOUNT ON;
            select * from Students where FirstName like @FirstName +'%'
        END
GO

 Nếu bạn đang làm theo cách tiếp cận mã đầu tiên, hãy làm theo các bước sau:

  1.  Thêm migration bằng cách thực hiện lệnh sau trong NPM (NuGet Package Manager):
PM> Add-migration sp-GetStudents
  1. Viết mã sau trong phương thức Up của lớp migration trống trong <DateTime>_sp-GetStudents.cs:
public partial class spGetStudents : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sp = @"CREATE PROCEDURE [dbo].[GetStudents]
                    @FirstName varchar(50)
                AS
                BEGIN
                    SET NOCOUNT ON;
                    select * from Students where FirstName like @FirstName +'%'
                END";

        migrationBuilder.Sql(sp);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}
  1. Bây giờ, hãy tạo quy trình được lưu trữ ở trên trong cơ sở dữ liệu bằng cách thực hiện lệnh sau trong NPM:
PM> Update-database

Thao tác này sẽ tạo thủ tục được lưu trữ GetStudents trong cơ sở dữ liệu SQL Server.

 


Thực thi các thủ tục được lưu trữ bằng FromSql

Như đã đề cập trong phần trước, phương thức FromSql của DbSet có thể được sử dụng để thực thi các truy vấn SQL đến cơ sở dữ liệu bên dưới. Theo cách tương tự, nó có thể được sử dụng để thực hiện thủ tục được lưu trữ trả về dữ liệu thực thể, nhưng có một số hạn chế.

Trong cơ sở dữ liệu, chúng ta có thể thực thi thủ tục được lưu trữ GetStudents với giá trị tham số INPUT như bên dưới:

GetStudents "Bill"
-- or
exec GetStudents "Bill"

Chúng ta có thể thực thi SP bằng phương thức FromSql trong EF Core theo cách tương tự như trên, như hình dưới đây.

var context = new SchoolContext(); 

var students = context.Students.FromSql("GetStudents 'Bill'").ToList();

Chúng ta cũng có thể truyền một giá trị tham số bằng cú pháp nội suy chuỗi C #, như được hiển thị bên dưới.

var name = "Bill";

var context = new SchoolContext(); 
var students = context.Students
                      .FromSql($"GetStudents {name}")
                      .ToList();

//or
//var students = context.Students.FromSql($"exec GetStudents {name}").ToList();

Sử dụng đối tượng SqlParameter để chỉ định giá trị của các tham số IN hoặc OUT như bên dưới:

var context = new SchoolContext(); 
var param = new SqlParameter("@FirstName", "Bill");
//or
/*var param = new SqlParameter() {
                    ParameterName = "@FirstName",
                    SqlDbType =  System.Data.SqlDbType.VarChar,
                    Direction = System.Data.ParameterDirection.Input,
                    Size = 50,
                    Value = "Bill"
};*/

var students = context.Students.FromSql("GetStudents @FirstName", param).ToList();

Chúng ta cũng có thể khai báo @ p0 cho tham số đầu tiên, @ p1 cho tham số thứ hai, v.v.

var context = new SchoolContext(); 

var students = context.Students.FromSql("GetStudents @p0","Bill").ToList();

Trong ví dụ trên, @ p0 được sử dụng cho tham số đầu tiên vì các tham số được đặt tên chưa được hỗ trợ trong EF Core.

Lưu ý: Tất cả các thực thể trong kết quả sẽ được DbContext theo dõi mặc định. Nếu chúng ta thực thi cùng một thủ tục được lưu trữ với các tham số giống nhau nhiều lần, thì nó sẽ thực thi cùng một câu lệnh SQL mỗi lần, nhưng nó sẽ chỉ theo dõi một tập kết quả. Ví dụ, ví dụ sau sẽ thực thi thủ tục được lưu trữ GetStudents ba lần, nhưng nó sẽ lưu vào bộ nhớ cache và chỉ theo dõi một bản sao của kết quả.

var context = new SchoolContext(); 

var list1 = context.Students.FromSql("GetStudents 'Bill'").ToList();
var list2 = context.Students.FromSql("GetStudents 'Bill'").ToList();
var list3 = context.Students.FromSql("GetStudents 'Bill'").ToList();

Thực thi thủ tục được lưu trữ bằng ExecuteSqlCommand ()

Phương thức ExecuteSqlCommand () được sử dụng để thực thi các lệnh cơ sở dữ liệu dưới dạng một chuỗi. Nó trả về một số nguyên cho số dòng bị tác động thông qua lệnh được chỉ định.

var context = new SchoolContext(); 

var rowsAffected = context.Database.ExecuteSqlCommand("Update Students set FirstName = 'Bill' where StudentId = 1;");

Trong ví dụ trên, lệnh cập nhật được truyền trong phương thức ExecuteSqlCommand. Giá trị của rowAffected sẽ là 1 vì chỉ có 1 hàng bị ảnh hưởng với lệnh cập nhật được chỉ định.

Theo cách tương tự, chúng ta có thể thực hiện các thủ tục được lưu trữ để tạo, cập nhật và xóa các lệnh. Hãy xem xét thủ tục được lưu trữ sau để chèn một bản ghi trong bảng Student trong cơ sở dữ liệu:

CREATE PROCEDURE CreateStudent
    @FirstName Varchar(50),
    @LastName Varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    Insert into Students(
           [FirstName]
           ,[LastName]
           )
 Values (@FirstName, @LastName)
END
GO

Bây giờ, bạn có thể thực hiện SP trên như bên dưới.

var context = new SchoolContext(); 

context.Database.ExecuteSqlCommand("CreateStudents @p0, @p1", parameters: new[] { "Bill", "Gates" });

Theo cách tương tự, bạn có thể thực hiện các thủ tục được lưu trữ cho các lệnh Update và Delete .