ASP.NET Core 3.1系列18——EFCore执行原生SQL语句
herrydong
帮助1人
1、前言
前一篇博客介绍了EFCore中常见的一些查询操作，使用Linq或Lambda结合实体类的操作相当方便。但在某些特殊情况下，我们仍旧需要使用原生SQL来获取数据。好在EFCore中提供了完整的方法支持原生SQL，下面开始介绍。
2、构建测试数据库
与之前一样，还是使用Author和Book数据表，它们是一对多的关系，AuthorId为Book表中的外键。
Author表数据如下所示：
|
Id |
Name |
Gender |
Age |
|
|
1 |
张三 |
男 |
35 |
11111111@qq.com |
|
2 |
李四 |
女 |
40 |
22222222@qq.com |
|
3 |
王五 |
男 |
37 |
33333333@qq.com |
Book表数据如下所示：
|
Id |
Title |
Press |
PublicationTime |
Price |
AuthorId |
|
1 |
《C程序设计》 |
A出版社 |
2022-01-01 |
30 |
1 |
|
2 |
《C 程序设计》 |
B出版社 |
2022-02-02 |
45 |
1 |
|
3 |
《Java程序设计》 |
C出版社 |
2022-03-03 |
60 |
2 |
|
4 |
《C#程序设计》 |
D出版社 |
2022-04-04 |
55 |
2 |
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>();
}
/// <summary>
/// 主键
/// </summary>
[Key]
public int Id { get; set; }
/// <summary>
/// 姓名
/// </summary>
[StringLength(20)]
public string Name { get; set; }
/// <summary>
/// 性别
/// </summary>
[StringLength(2)]
public string Gender { get; set; }
/// <summary>
/// 年龄
/// </summary>
public int? Age { get; set; }
/// <summary>
/// 邮箱
/// </summary>
[StringLength(30)]
public string Email { get; set; }
/// <summary>
/// 导航属性
/// </summary>
[InverseProperty("Author")]
public virtual ICollection<Book> Book { get; set; }
}
}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
{
/// <summary>
/// 主键
/// </summary>
[Key]
public int Id { get; set; }
/// <summary>
/// 书名
/// </summary>
[StringLength(20)]
public string Title { get; set; }
/// <summary>
/// 出版社
/// </summary>
[StringLength(20)]
public string Press { get; set; }
/// <summary>
/// 出版时间
/// </summary>
[Column(TypeName = "datetime")]
public DateTime? PublicationTime { get; set; }
/// <summary>
/// 价格
/// </summary>
[Column(TypeName = "money")]
public decimal? Price { get; set; }
/// <summary>
/// 外键：AuthorId
/// </summary>
public int? AuthorId { get; set; }
/// <summary>
/// 导航属性
/// </summary>
[ForeignKey(nameof(AuthorId))]
[InverseProperty("Book")]
public virtual Author Author { get; set; }
}
}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);
}
}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();
}
}
}运行结果如下所示：
[{"id":1,"name":"张三","gender":"男","age":35,"email":"11111111@qq.com","book":[]}]如果希望查询出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();
}
}
}运行结果如下所示：
[
{
"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
}
]
}
]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();
}
}
}运行结果如下所示：
[{"id":1,"name":"张三","gender":"男","age":35,"email":"11111111@qq.com","book":[]}]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();
}
}
}运行结果如下所示：
[
{
"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": []
}
]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})");
}
}
}运行结果如下所示：
添加数据成功
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);
}
}
}运行结果如下所示：
添加数据成功
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 "执行事务失败";
}
}
}
}
}运行结果如下所示：
执行事务成功
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
{
/// <summary>
/// 执行查询操作，返回DataTable
/// </summary>
/// <param name="dbContext">数据库上下文</param>
/// <param name="commandText">命令语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">格式化参数集合</param>
/// <returns>DataTable</returns>
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();
}
}
/// <summary>
/// 执行查询操作，返回DbDataReader
/// </summary>
/// <param name="dbContext">数据库上下文</param>
/// <param name="commandText">命令语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">格式化参数集合</param>
/// <returns>DbDataReader</returns>
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();
}
}
/// <summary>
/// 执行查询操作，返回第一行第一列
/// </summary>
/// <param name="dbContext">数据库上下文</param>
/// <param name="commandText">命令语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">格式化参数集合</param>
/// <returns>第一行第一列</returns>
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();
}
}
/// <summary>
/// 执行非查询操作，返回受影响的行数
/// </summary>
/// <param name="dbContext">数据库上下文</param>
/// <param name="commandText">命令语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">格式化参数集合</param>
/// <returns>受影响的行数</returns>
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();
}
}
/// <summary>
/// 执行数据库事务，返回受影响的行数
/// </summary>
/// <param name="dbContext">数据库上下文</param>
/// <param name="commands">命令集合</param>
/// <returns>受影响的行数</returns>
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;
}
}
}
}SingleCommand.cs代码：
using Microsoft.Data.SqlClient;
using System.Collections.Generic;
namespace App
{
public class SingleCommand
{
/// <summary>
/// 命令语句
/// </summary>
public string CommandText { get; set; }
/// <summary>
/// 格式化参数集合
/// </summary>
public List<SqlParameter> Parameters { get; set; }
}
}最后在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 ? "事务执行成功" : "事务执行失败";
}
}
}GetAuthorsById结果如下：
[
{"id":2,"name":"李四","age":40},
{"id":3,"name":"王五","age":37}
]GetAuthorsByAge结果如下：
[
{"id":2,"name":"李四","gender":null,"age":40,"email":null,"book":[]},
{"id":3,"name":"王五","gender":null,"age":37,"email":null,"book":[]}
]GetAuthorsCount结果如下：
3UpdateAuthorById结果如下：
修改邮箱成功
GetTransactionResult结果如下：
事务执行成功
7、结语
本文主要介绍了如何在EFCore中执行原生SQL的方法。在某些特殊情况下，直接执行原生语句往往会更方便且更高效，因此EFCore虽好，也别忘了SQL。
这篇好文章是转载于：学新通技术网
- 版权申明： 本站部分内容来自互联网，仅供学习及演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，请提供相关证据及您的身份证明，我们将在收到邮件后48小时内删除。
- 本站站名： 学新通技术网
- 本文地址： /boutique/detail/tanhibjjgg
系列文章
更多
同类精品
更多
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01 -
Android 11 保存文件到外部存储，并分享文件
Luke 10-12 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
微信运动停用后别人还能看到步数吗
PHP中文网 07-22 -
excel打印预览压线压字怎么办
PHP中文网 06-22
