Entity Framework Core執行存儲過程

這節講解EF Core中使用FromSqlRaw()& ExecuteCommand() 反複執行存儲過程

1 存儲過程

下面存儲過程傳回指定 name和standard 所有學生，name&standard值通過存儲過程的輸入參數提供

CREATE PROCEDURE [dbo].[sp_GetStudents]              @Name VARCHAR(50),              @Standard INT              AS              BEGIN              SELECT * FROM Student Where Name=@Name AND Standard=@Standard              END           

Entity Framework Core 使用FromSqlRaw() 方法執行存儲過，使用FromSqlRaw()方法時必須為存儲過程提供兩個參數，是以我們必須使用SqlParameter 數組設定參數

SqlParameter類位于Microsoft.Data.SqlClient命名空間内，下面代碼執行存儲過程

var param = new SqlParameter[] {              new SqlParameter() {              ParameterName = "@Name",              SqlDbType = System.Data.SqlDbType.VarChar,              Size = 100,              Direction = System.Data.ParameterDirection.Input,              Value = "Tony"              },              new SqlParameter() {              ParameterName = "@Standard",              SqlDbType = System.Data.SqlDbType.Int,              Direction = System.Data.ParameterDirection.Input,              Value = 10              }};              List<Student> studentList = context.Student.FromSqlRaw("[dbo].[sp_GetStudents] @Name, @Standard", param).ToList();           

2 存儲過程傳回多個資料集

.FromSqlRaw()方法将從存儲過程中擷取一條記錄，如果存儲過程傳回多個資料集，我們使用 ExecuteReader() 方法

下面存儲過程傳回兩組資料集，一個是Student表，另一個是StudentAddress表

CREATE PROCEDURE [dbo].[sp_MultiRecordSets]              @IdStu INT,              @IdAdd INT               AS              BEGIN              SELECT * FROM Student Where Id=@IdStu               SELECT * FROM StudentAddress WHERE Id=@IdAdd              END           

下面代碼顯示了如何使用ExecuteReader() 方法從這兩組資料中擷取值

var param = new SqlParameter[] {              new SqlParameter() {              ParameterName = "@IdStu",              SqlDbType = System.Data.SqlDbType.Int,              Direction = System.Data.ParameterDirection.Input,              Value = 5              },              new SqlParameter() {              ParameterName = "@IdAdd",              SqlDbType = System.Data.SqlDbType.Int,              Direction = System.Data.ParameterDirection.Input,              Value = 10              }};              var context = new SchoolContext();              using (var cnn = context.Database.GetDbConnection())              {              var cmm = cnn.CreateCommand();              cmm.CommandType = System.Data.CommandType.StoredProcedure;              cmm.CommandText = "[dbo].[sp_MultiRecordSets]";              cmm.Parameters.AddRange(param);              cmm.Connection = cnn;              cnn.Open();              var reader = cmm.ExecuteReader();              while (reader.Read())              {              // name from student table               string studentName= Convert.ToString(reader["Name"]);               }              reader.NextResult(); //move the next record set              while (reader.Read())              {              // city from student address table              string city = Convert.ToString(reader["City"]);               }              }           

注意使用NextResult()移動到下一組資料集

EF Core中三個最重要的概念：

1 DbContext類

2 Migrations

3 Fluent APIs

3 使用ExecuteSqlRawAsync()執行存儲過程和原生SQL

EF Core使用ExecuteSqlRawAsync() 方法執行存儲過程和原生SQL，但是不像FromSqlRaw()方法，它傳回受影響的行數，注意也有一個ExecuteSqlRaw() 同步版本類型的方法

var rowsAffected = await context.Database.ExecuteSqlRawAsync("Update Students set Name = 'Donald Trump' where Id = 5");           

4 使用ExecuteSqlRawAsync()輸出存儲過程參數

我們使用存儲過程傳回生總個數(@TotalStudents)

CREATE PROCEDURE [dbo].[sp_GetStudentsNew]              @Name VARCHAR(50),              @Standard INT,              @TotalStudents INT OUTPUT              AS              BEGIN              SELECT * FROM Student Where Name=@Name AND Standard=@Standard              SET @TotalStudents= (SELECT COUNT(*) FROM Student)              END           

為了執行存儲過程在EF Core，我們需要指定SqlParameter參數的輸出方向

new SqlParameter() {              ParameterName = "@TotalStudents",              SqlDbType = System.Data.SqlDbType.Int,              Direction = System.Data.ParameterDirection.Output, // direction output              }           

每次執行存儲過程時，我們能通過輸出參數擷取到傳回的值：

int totalStudents = Convert.ToInt32(Convert.ToString(param[2].Value));           

注意：參數的開始索引是0，總共有三個參數，最後一個參數是輸出參數

var context = new SchoolContext();              var param = new SqlParameter[] {              new SqlParameter() {              ParameterName = "@Name",              SqlDbType = System.Data.SqlDbType.VarChar,              Size = 100,              Direction = System.Data.ParameterDirection.Input,              Value = "Tony"              },              new SqlParameter() {              ParameterName = "@Standard",              SqlDbType = System.Data.SqlDbType.Int,              Direction = System.Data.ParameterDirection.Input,              Value = 10              },              new SqlParameter() {              ParameterName = "@TotalStudents",              SqlDbType = System.Data.SqlDbType.Int,              Direction = System.Data.ParameterDirection.Output,              }};              int affectedRows = await context.Database.ExecuteSqlRawAsync("[dbo].[sp_GetStudentsNew] @Name, @Standard, @TotalStudents out", param);              int totalStudents = Convert.ToInt32(param[2].Value);