这节讲解EF Core中使用FromSqlRaw()& ExecuteCommand() 反复执行存储过程
1 存储过程
下面存储过程返回指定 name和standard 所有学生，name&standard值通过存储过程的输入参数提供
CREATE PROCEDURE [dbo].[sp_GetStudents]@Name VARCHAR(50),@Standard INTASBEGINSELECT * FROM Student Where Name=@Name AND Standard=@StandardEND
Entity Framework Core 使用FromSqlRaw() 方法执行存储过，使用FromSqlRaw()方法时必须为存储过程提供两个参数，因此我们必须使用SqlParameter 数组设置参数
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 INTASBEGINSELECT * FROM Student Where Id=@IdStuSELECT * FROM StudentAddress WHERE Id=@IdAddEND
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 tablestring studentName= Convert.ToString(reader["Name"]);}reader.NextResult(); //move the next record setwhile (reader.Read()){// city from student address tablestring city = Convert.ToString(reader["City"]);}}
EF Core中三个最重要的概念：
1 DbContext类
2 Migrations
3 Fluent APIs
3 使用ExecuteSqlRawAsync()执行存储过程和原生SQL
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 OUTPUTASBEGINSELECT * FROM Student Where Name=@Name AND Standard=@StandardSET @TotalStudents= (SELECT COUNT(*) FROM Student)END
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));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);
本文暂时没有评论，来添加一个吧(●'◡'●)