• ASP.NET Core 3.1系列（18）——EFCore中执行原生SQL语句

    1、前言

    前一篇博客介绍了EFCore中常见的一些查询操作，使用Linq或Lambda结合实体类的操作相当方便。但在某些特殊情况下，我们仍旧需要使用原生SQL来获取数据。好在EFCore中提供了完整的方法支持原生SQL，下面开始介绍。

    2、构建测试数据库

    与之前一样，还是使用Author和Book数据表，它们是一对多的关系，AuthorId为Book表中的外键。

    Author表数据如下所示：

    IdNameGenderAgeEmail
    1张三男3511111111@qq.com
    2李四女4022222222@qq.com
    3王五男3733333333@qq.com

    Book表数据如下所示：

    IdTitlePressPublicationTimePriceAuthorId
    1《C程序设计》A出版社2022-01-01301
    2《C++程序设计》B出版社2022-02-02451
    3《Java程序设计》C出版社2022-03-03602
    4《C#程序设计》D出版社2022-04-04552

    Author代码如下：

    using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.
// If you have enabled NRTs for your project, then un-comment the following line:
// #nullable disable

namespace App.Models
{
    public partial class Author
    {
        public Author()
        {
            Book = new HashSet<Book>();
        }

        /// 
        /// 主键
        /// 
        [Key]
        public int Id { get; set; }

        /// 
        /// 姓名
        /// 
        [StringLength(20)]
        public string Name { get; set; }

        /// 
        /// 性别
        /// 
        [StringLength(2)]
        public string Gender { get; set; }

        /// 
        /// 年龄
        /// 
        public int? Age { get; set; }

        /// 
        /// 邮箱
        /// 
        [StringLength(30)]
        public string Email { get; set; }

        /// 
        /// 导航属性
        /// 
        [InverseProperty("Author")]
        public virtual ICollection<Book> Book { get; set; }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53

    Book代码如下：

    using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.
// If you have enabled NRTs for your project, then un-comment the following line:
// #nullable disable

namespace App.Models
{
    public partial class Book
    {
        /// 
        /// 主键
        /// 
        [Key]
        public int Id { get; set; }

        /// 
        /// 书名
        /// 
        [StringLength(20)]
        public string Title { get; set; }

        /// 
        /// 出版社
        /// 
        [StringLength(20)]
        public string Press { get; set; }

        /// 
        /// 出版时间
        /// 
        [Column(TypeName = "datetime")]
        public DateTime? PublicationTime { get; set; }

        /// 
        /// 价格
        /// 
        [Column(TypeName = "money")]
        public decimal? Price { get; set; }

        /// 
        /// 外键：AuthorId
        /// 
        public int? AuthorId { get; set; }

        /// 
        /// 导航属性
        /// 
        [ForeignKey(nameof(AuthorId))]
        [InverseProperty("Book")]
        public virtual Author Author { get; set; }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55

    DaoDbContext代码如下：

    using App.Models;
using Microsoft.EntityFrameworkCore;

// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.
// If you have enabled NRTs for your project, then un-comment the following line:
// #nullable disable

namespace App.Context
{
    public partial class DaoDbContext : DbContext
    {
        public DaoDbContext()
        {
        }

        public DaoDbContext(DbContextOptions<DaoDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Author> Author { get; set; }
        public virtual DbSet<Book> Book { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Data Source=DSF-PC;Initial Catalog=Dao;User ID=sa;Password=123456;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Book>(entity =>
            {
                entity.HasOne(d => d.Author)
                    .WithMany(p => p.Book)
                    .HasForeignKey(d => d.AuthorId)
                    .OnDelete(DeleteBehavior.Cascade)
                    .HasConstraintName("FK_Book_Author");
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48

    3、执行原生SQL查询操作

    3.1、FromSqlInterpolated

    如果是针对单表的查询操作，可以使用FromSqlInterpolated方法，但是该方法有以下局限性：

    • 只支持单表查询，不支持Join
    • 必须返回全部列
    • 结果集中的列名必须与数据库中的列名对应

    下面将查询查询Author表中Name='张三'且Age>30的记录，代码如下：

    using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return GetAuthors("张三", 30);
        }

        private List<Author> GetAuthors(string name, int age)
        {
            return _dbContext.Set<Author>()
                             .FromSqlInterpolated(@$"select * from Author where Name={name} and Age>{age}")
                             .ToList();
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    运行结果如下所示：

    [{"id":1,"name":"张三","gender":"男","age":35,"email":"11111111@qq.com","book":[]}]

    • 1

    如果希望查询出Author对应的Book，也可以通过Include实现，EFCore支持FromSqlInterpolated与Lambda一起使用，代码如下：

    using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return GetAuthors("张三", 30);
        }

        private List<Author> GetAuthors(string name, int age)
        {
            return _dbContext.Set<Author>()
                             .FromSqlInterpolated(@$"select * from Author where Name={name} and Age>{age}")
                             .Include(p => p.Book)
                             .ToList();
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    运行结果如下所示：

    [
    {
        "id": 1,
        "name": "张三",
        "gender": "男",
        "age": 35,
        "email": "11111111@qq.com",
        "book": [
            {
                "id": 1,
                "title": "《C程序设计》",
                "press": "A出版社",
                "publicationTime": "2021-01-01T00:00:00",
                "price": 30.0000,
                "authorId": 1
            },
            {
                "id": 2,
                "title": "《C++程序设计》",
                "press": "B出版社",
                "publicationTime": "2021-02-02T00:00:00",
                "price": 45.0000,
                "authorId": 1
            }
        ]
    }
]

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    3.2、FromSqlRaw

    针对单表的查询也可以使用FromSqlRaw方法，与FromSqlInterpolated类似，该方法也必须返回全部列，代码如下：

    using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return GetAuthors("张三", 30);
        }

        private List<Author> GetAuthors(string name, int age)
        {
            SqlParameter[] parameters =
            {
                new SqlParameter(@"Name", name),
                new SqlParameter(@"Age", age)
            };
            return _dbContext.Set<Author>()
                             .FromSqlRaw("select * from Author where Name=@Name and Age>@Age", parameters)
                             .ToList();
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    运行结果如下所示：

    [{"id":1,"name":"张三","gender":"男","age":35,"email":"11111111@qq.com","book":[]}]

    • 1

    FromSqlRaw方法也可以与Lambda一起使用，代码如下：

    using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<List<Author>> Get()
        {
            return GetAuthors(1);
        }

        private List<Author> GetAuthors(int id)
        {
            SqlParameter[] parameters =
            {
                new SqlParameter(@"Id", id),
            };
            return _dbContext.Set<Author>()
                             .FromSqlRaw("select * from Author where Id>@Id", parameters)
                             .OrderByDescending(p => p.Age)
                             .Include(p => p.Book)
                             .ToList();
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    运行结果如下所示：

    [
    {
        "id": 2,
        "name": "李四",
        "gender": "女",
        "age": 40,
        "email": "22222222@qq.com",
        "book": [
            {
                "id": 3,
                "title": "《Java程序设计》",
                "press": "C出版社",
                "publicationTime": "2021-03-03T00:00:00",
                "price": 60.0000,
                "authorId": 2
            },
            {
                "id": 4,
                "title": "《C#程序设计》",
                "press": "D出版社",
                "publicationTime": "2021-04-04T00:00:00",
                "price": 55.0000,
                "authorId": 2
            }
        ]
    },
    {
        "id": 3,
        "name": "王五",
        "gender": "男",
        "age": 37,
        "email": "33333333@qq.com",
        "book": []
    }
]

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    4、执行原生SQL非查询操作

    4.1、ExecuteSqlInterpolated

    如果希望执行增删改等非查询操作，可以使用ExecuteSqlInterpolated方法，下面给Author表添加一条记录，代码如下：

    using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<string> Get()
        {
            int result = AddAuthor(new Author
            {
                Name = "AAA",
                Gender = "男",
                Age = 33,
                Email = "44444444@qq.com"
            });
            return result > 0 ? "添加数据成功" : "添加数据失败";
        }

        private int AddAuthor(Author author)
        {
            string name = author.Name;
            string gender = author.Gender;
            int age = author.Age.HasValue ? author.Age.Value : 0;
            string email = author.Email;
            return _dbContext.Database.ExecuteSqlInterpolated(@$"insert into Author(Name,Gender,Age,Email) values({name},{gender},{age},{email})");
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    运行结果如下所示：

    添加数据成功

    • 1

    在这里插入图片描述

    4.2、ExecuteSqlRaw

    ExecuteSqlRaw方法也可以执行增删改等非查询操作，代码如下：

    using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<string> Get()
        {
            int result = AddAuthor(new Author
            {
                Name = "BBB",
                Gender = "女",
                Age = 42,
                Email = "55555555@qq.com"
            });
            return result > 0 ? "添加数据成功" : "添加数据失败";
        }

        private int AddAuthor(Author author)
        {
            SqlParameter[] parameters =
            {
                new SqlParameter(@"Name", author.Name),
                new SqlParameter(@"Gender", author.Gender),
                new SqlParameter(@"Age", author.Age),
                new SqlParameter(@"Email", author.Email)
            };
            return _dbContext.Database.ExecuteSqlRaw("insert into Author(Name,Gender,Age,Email) values(@Name,@Gender,@Age,@Email)", parameters);
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45

    运行结果如下所示：

    添加数据成功

    • 1

    在这里插入图片描述

    5、执行数据库事务

    如果希望执行数据库事务，可以使用BeginTransaction方法，下面代码执行了一个包含Insert和Update的事务：

    using App.Context;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<string> Get()
        {
            using (var transaction = _dbContext.Database.BeginTransaction())
            {
                try
                {
                    _dbContext.Database.ExecuteSqlRaw("insert into Author(Name,Gender,Age,Email) values('CCC','男',45,'66666666@qq.com')");
                    _dbContext.Database.ExecuteSqlRaw("update Author set Name='张三三' where Name='张三'");
                    transaction.Commit();
                    return "执行事务成功";
                }
                catch
                {
                    transaction.Rollback();
                    return "执行事务失败";
                }
            }
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38

    运行结果如下所示：

    执行事务成功

    • 1

    在这里插入图片描述

    6、封装SqlHelper

    上面的查询方法都必须返回全部列，而在实际开发过程中往往是按需查询列，因此我们还是需要自行封装一个SqlHelper，代码如下：

    SqlHelper.cs代码：

    using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace App
{
    public class SqlHelper
    {
        /// 
        /// 执行查询操作，返回DataTable
        /// 
        /// 数据库上下文
        /// 命令语句
        /// 命令类型
        /// 格式化参数集合
        /// DataTable
        public static DataTable ExecuteQuery(DbContext dbContext, string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            DbConnection connection = dbContext.Database.GetDbConnection();
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            // 设置Command
            using DbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (parameters != null && parameters.Length > 0)
            {
                command.Parameters.AddRange(parameters);
            }

            // 查询数据
            using SqlDataAdapter adapter = new SqlDataAdapter(command as SqlCommand);
            try
            {
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                return dataTable;
            }
            catch
            {
                return null;
            }
            finally
            {
                command.Parameters.Clear();
            }
        }

        /// 
        /// 执行查询操作，返回DbDataReader
        /// 
        /// 数据库上下文
        /// 命令语句
        /// 命令类型
        /// 格式化参数集合
        /// DbDataReader
        public static DbDataReader ExecuteReader(DbContext dbContext, string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            DbConnection connection = dbContext.Database.GetDbConnection();
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            // 设置Command
            using DbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (parameters != null && parameters.Length > 0)
            {
                command.Parameters.AddRange(parameters);
            }

            // 返回DataReader
            try
            {
                return command.ExecuteReader();
            }
            catch
            {
                return null;
            }
            finally
            {
                command.Parameters.Clear();
            }
        }

        /// 
        /// 执行查询操作，返回第一行第一列
        /// 
        /// 数据库上下文
        /// 命令语句
        /// 命令类型
        /// 格式化参数集合
        /// 第一行第一列
        public static object ExecuteScalar(DbContext dbContext, string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            DbConnection connection = dbContext.Database.GetDbConnection();
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            // 设置Command
            using DbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (parameters != null && parameters.Length > 0)
            {
                command.Parameters.AddRange(parameters);
            }

            // 返回第一行第一列
            try
            {
                return command.ExecuteScalar();
            }
            catch
            {
                return null;
            }
            finally
            {
                command.Parameters.Clear();
            }
        }

        /// 
        /// 执行非查询操作，返回受影响的行数
        /// 
        /// 数据库上下文
        /// 命令语句
        /// 命令类型
        /// 格式化参数集合
        /// 受影响的行数
        public static int ExecuteNonQuery(DbContext dbContext, string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            DbConnection connection = dbContext.Database.GetDbConnection();
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            // 设置Command
            using DbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (parameters != null && parameters.Length > 0)
            {
                command.Parameters.AddRange(parameters);
            }

            // 返回受影响的行数
            try
            {
                return command.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            finally
            {
                command.Parameters.Clear();
            }
        }

        /// 
        /// 执行数据库事务，返回受影响的行数
        /// 
        /// 数据库上下文
        /// 命令集合
        /// 受影响的行数
        public static int ExecuteTransaction(DbContext dbContext, List<SingleCommand> commands)
        {
            DbConnection connection = dbContext.Database.GetDbConnection();
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            // 开启事务
            using DbTransaction transaction = connection.BeginTransaction();
            try
            {
                foreach (var item in commands)
                {
                    DbCommand command = connection.CreateCommand();
                    command.CommandText = item.CommandText;
                    command.CommandType = CommandType.Text;
                    command.Transaction = transaction;
                    if (item.Parameters.Count > 0)
                    {
                        command.Parameters.AddRange(item.Parameters.ToArray());
                    }
                    command.ExecuteNonQuery();
                }

                // 提交事务
                transaction.Commit();
                return 1;
            }
            catch
            {
                // 回滚事务
                transaction.Rollback();
                return 0;
            }
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211
    • 212
    • 213
    • 214
    • 215
    • 216
    • 217

    SingleCommand.cs代码：

    using Microsoft.Data.SqlClient;
using System.Collections.Generic;

namespace App
{
    public class SingleCommand
    {
        /// 
        /// 命令语句
        /// 
        public string CommandText { get; set; }

        /// 
        /// 格式化参数集合
        /// 
        public List<SqlParameter> Parameters { get; set; }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    最后在Controller调用即可，代码如下：

    using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<DataTable> GetAuthorsById()
        {
            SqlParameter parameter = new SqlParameter(@"Id", 1);
            return SqlHelper.ExecuteQuery(_dbContext, "select Id,Name,Age from Author where Id>@Id", CommandType.Text, parameter);
        }

        [HttpGet]
        public ActionResult<List<Author>> GetAuthorsByAge()
        {
            List<Author> list = new List<Author>();
            SqlParameter parameter = new SqlParameter(@"Age", 35);
            DbDataReader reader = SqlHelper.ExecuteReader(_dbContext, "select Id,Name,Age from Author where Age>@Age", CommandType.Text, parameter);
            while (reader.Read())
            {
                list.Add(new Author
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"] == DBNull.Value ? null : Convert.ToString(reader["Name"]),
                    Age = reader["Id"] == DBNull.Value ? new Nullable<int>() : Convert.ToInt32(reader["Age"])
                });
            }
            return list;
        }

        [HttpGet]
        public ActionResult<int> GetAuthorsCount()
        {
            object obj = SqlHelper.ExecuteScalar(_dbContext, "select count(*) from Author", CommandType.Text);
            return Convert.ToInt32(obj);
        }

        [HttpGet]
        public ActionResult<string> UpdateAuthorById()
        {
            SqlParameter[] parameters =
            {
                new SqlParameter(@"Id", 1),
                new SqlParameter(@"Email", "12345678@163.com")
            };
            int result = SqlHelper.ExecuteNonQuery(_dbContext, "update Author set Email=@Email where Id=@Id", CommandType.Text, parameters);
            return result > 0 ? "修改邮箱成功" : "修改邮箱失败";
        }

        [HttpGet]
        public ActionResult<string> GetTransactionResult()
        {
            List<SingleCommand> commands = new List<SingleCommand>
            {
                new SingleCommand()
                {
                    CommandText = "insert into Author values(@Name,@Gender,@Age,@Email)",
                    Parameters = new List<SqlParameter>
                    {
                        new SqlParameter(@"Name", "赵六"),
                        new SqlParameter(@"Gender", "女"),
                        new SqlParameter(@"Age", 39),
                        new SqlParameter(@"Email", "12345678@163.com")
                    }
                },
                new SingleCommand()
                {
                    CommandText = "update Author set Age=@Age where Name=@Name",
                    Parameters = new List<SqlParameter>
                    {
                        new SqlParameter(@"Name", "张三"),
                        new SqlParameter(@"Age", 59)
                    }
                },
            };
            int result = SqlHelper.ExecuteTransaction(_dbContext, commands);
            return result > 0 ? "事务执行成功" : "事务执行失败";
        }
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97

    GetAuthorsById结果如下：

    [
 {"id":2,"name":"李四","age":40},
 {"id":3,"name":"王五","age":37}
]

    • 1
    • 2
    • 3
    • 4

    GetAuthorsByAge结果如下：

    [
 {"id":2,"name":"李四","gender":null,"age":40,"email":null,"book":[]},
 {"id":3,"name":"王五","gender":null,"age":37,"email":null,"book":[]}
]

    • 1
    • 2
    • 3
    • 4

    GetAuthorsCount结果如下：

    3

    • 1

    UpdateAuthorById结果如下：

    修改邮箱成功

    • 1

    在这里插入图片描述
    GetTransactionResult结果如下：

    事务执行成功

    • 1

    在这里插入图片描述

    7、结语

    本文主要介绍了如何在EFCore中执行原生SQL的方法。在某些特殊情况下，直接执行原生语句往往会更方便且更高效，因此EFCore虽好，也别忘了SQL。

  • 相关阅读:
    Java基础面试，重载和重写的区别
    Zebec Protocol 成非洲利比亚展会合作伙伴，并将向第三世界国家布局
    MATLAB数据导出
    项目经理每天，每周，每月的工作清单
    兄弟DCP-7080激光打印机硒鼓清零方法
    .NET Core 允许跨域的两种方式实现（IIS 配置、C# 代码实现）
    python中的一个实用的库imghdr,用于探测图片格式
    YOLOv7独家改进：Multi-Dconv Head Transposed Attention注意力，效果优于MHSA| CVPR2022
    Vue第七讲
    数据库恢复
  • 原文地址：https://blog.csdn.net/HerryDong/article/details/128197900