ABP学习实践(十四)--执行自定义SQL语句,创建动态表
在日常的开发工作中,有时我们需要在应用程序中直接执行SQL语句,比如动态创建数据库表、自定义查询等,利用ABP框架的仓储特性和EntityFramework Core的特性就能实现这个功能。
1.执行自定义SQL语句
网上可以查到不少通过EFCore执行SQL语句的文章,但有些是比较早的,相关方法可能已经废弃了,也有结合ABP仓储实现的,但有些地方感觉也没写明白。
1.1 创建自定义仓储
在领域层(一般是Core项目)中添加自定义仓储接口。
public interface ISqlRepository: IRepository
{
/// <summary>
/// 执行给定的命令
/// </summary>
/// <param name="sql">命令字符串</param>
/// <param name="parameters">要应用于命令字符串的参数</param>
/// <returns>执行命令后由数据库返回的结果</returns>
Task<int> Execute(string sql, params object[] parameters);
/// <summary>
/// 执行语句返回dataset,注意参数用@p0、@p1、@p3...以此类推,要按照顺序
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">sql参数</param>
/// <returns>dataset</returns>
Task<DataTable> ExecuteDataTable(string sql, params object[] parameters);
}
其中Execute方法一般用于新增、修改、删除操作,返回结果是受影响的结果条数;ExecuteDataTable方法一般用于查询操作,返回结果为查询结果集,格式为Datatable。
1.2 实现自定义仓储
在基础设施层(一般是EntityFrameworkCore项目)中实现接口。
public class SqlRepository : ISqlRepository, ITransientDependency
{
private readonly IDbContextProvider<DataStandardMakerDbContext> _dbContextProvider;
public SqlRepository(IDbContextProvider<DataStandardMakerDbContext> dbContextProvider)
{
_dbContextProvider = dbContextProvider;
}
/// <summary>
/// 执行给定的命令
/// </summary>
/// <param name="sql">命令字符串</param>
/// <param name="parameters">要应用于命令字符串的参数</param>
/// <returns>执行命令后由数据库返回的结果</returns>
public async Task<int> Execute(string sql, params object[] parameters)
{
return await _dbContextProvider.GetDbContext().Database.ExecuteSqlCommandAsync(sql, parameters);
}
/// <summary>
/// 执行命令返回集合
/// </summary>
/// <param name="sql">命令字符串</param>
/// <param name="parameters">参数</param>
/// <returns>结果集</returns>
public async Task<DataTable> ExecuteDataTable(string sql, params object[] parameters)
{
DataTable dt=_dbContextProvider.GetDbContext().Database.SqlQuery(sql, parameters);
return await Task.FromResult(dt);
}
}
其中_dbContextProvider.GetDbContext().Database.SqlQuery(sql, parameters)中的SqlQuery为扩展方法。EFCore的扩展方法类为:
public static class EntityFrameworkCoreExtension
{
private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection, params object[] parameters)
{
var conn = facade.GetDbConnection();
connection = conn;
conn.Open();
var cmd = conn.CreateCommand();
if (facade.IsSqlite()) //数据库类型的判断,可以根据需要改成SQL Server或MySQL
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
}
return cmd;
}
public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
{
var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
var reader = command.ExecuteReader();
var dt = new DataTable();
dt.Load(reader);
reader.Close();
conn.Close();
return dt;
}
public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
{
var dt = SqlQuery(facade, sql, parameters);
return dt.ToList<T>();
}
public static List<T> ToList<T>(this DataTable dt) where T : class, new()
{
var propertyInfos = typeof(T).GetProperties();
var list = new List<T>();
foreach (DataRow row in dt.Rows)
{
var t = new T();
foreach (PropertyInfo p in propertyInfos)
{
if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
p.SetValue(t, row[p.Name], null);
}
list.Add(t);
}
return list;
}
}
1.3 使用示例
在应用层(一般是Application项目)可以直接使用自定义仓储实现SQL语句操作。
public class DemoService : DemoAppServiceBase, IApplicationService
{
private readonly ISqlRepository _repository;
public DataService(ISqlRepository repository)
{
_repository = repository;
}
public async Task<int> UpdateTable()
{
string strSql="update tablename set colname1=value1 where id=xx";
var result = _repository.Execute(strSql).Result;
return await Task.FromResult(result);
}
public async Task<DataTable> QueryTable()
{
string strSql="select colname1,colname2 from tablename";
var result = _repository.ExecuteDataTable(strSql).Result;
return await Task.FromResult(result);
}
}
ABP学习实践(十四)--执行自定义SQL语句,创建动态表
在日常的开发工作中,有时我们需要在应用程序中直接执行SQL语句,比如动态创建数据库表、自定义查询等,利用ABP框架的仓储特性和EntityFramework Core的特性就能实现这个功能。
1.执行自定义SQL语句
网上可以查到不少通过EFCore执行SQL语句的文章,但有些是比较早的,相关方法可能已经废弃了,也有结合ABP仓储实现的,但有些地方感觉也没写明白。
1.1 创建自定义仓储
在领域层(一般是Core项目)中添加自定义仓储接口。
public interface ISqlRepository: IRepository
{
/// <summary>
/// 执行给定的命令
/// </summary>
/// <param name="sql">命令字符串</param>
/// <param name="parameters">要应用于命令字符串的参数</param>
/// <returns>执行命令后由数据库返回的结果</returns>
Task<int> Execute(string sql, params object[] parameters);
/// <summary>
/// 执行语句返回dataset,注意参数用@p0、@p1、@p3...以此类推,要按照顺序
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">sql参数</param>
/// <returns>dataset</returns>
Task<DataTable> ExecuteDataTable(string sql, params object[] parameters);
}
其中Execute方法一般用于新增、修改、删除操作,返回结果是受影响的结果条数;ExecuteDataTable方法一般用于查询操作,返回结果为查询结果集,格式为Datatable。
1.2 实现自定义仓储
在基础设施层(一般是EntityFrameworkCore项目)中实现接口。
public class SqlRepository : ISqlRepository, ITransientDependency
{
private readonly IDbContextProvider<DataStandardMakerDbContext> _dbContextProvider;
public SqlRepository(IDbContextProvider<DataStandardMakerDbContext> dbContextProvider)
{
_dbContextProvider = dbContextProvider;
}
/// <summary>
/// 执行给定的命令
/// </summary>
/// <param name="sql">命令字符串</param>
/// <param name="parameters">要应用于命令字符串的参数</param>
/// <returns>执行命令后由数据库返回的结果</returns>
public async Task<int> Execute(string sql, params object[] parameters)
{
return await _dbContextProvider.GetDbContext().Database.ExecuteSqlCommandAsync(sql, parameters);
}
/// <summary>
/// 执行命令返回集合
/// </summary>
/// <param name="sql">命令字符串</param>
/// <param name="parameters">参数</param>
/// <returns>结果集</returns>
public async Task<DataTable> ExecuteDataTable(string sql, params object[] parameters)
{
DataTable dt=_dbContextProvider.GetDbContext().Database.SqlQuery(sql, parameters);
return await Task.FromResult(dt);
}
}
其中_dbContextProvider.GetDbContext().Database.SqlQuery(sql, parameters)中的SqlQuery为扩展方法。EFCore的扩展方法类为:
public static class EntityFrameworkCoreExtension
{
private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection, params object[] parameters)
{
var conn = facade.GetDbConnection();
connection = conn;
conn.Open();
var cmd = conn.CreateCommand();
if (facade.IsSqlite()) //数据库类型的判断,可以根据需要改成SQL Server或MySQL
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
}
return cmd;
}
public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
{
var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
var reader = command.ExecuteReader();
var dt = new DataTable();
dt.Load(reader);
reader.Close();
conn.Close();
return dt;
}
public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
{
var dt = SqlQuery(facade, sql, parameters);
return dt.ToList<T>();
}
public static List<T> ToList<T>(this DataTable dt) where T : class, new()
{
var propertyInfos = typeof(T).GetProperties();
var list = new List<T>();
foreach (DataRow row in dt.Rows)
{
var t = new T();
foreach (PropertyInfo p in propertyInfos)
{
if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
p.SetValue(t, row[p.Name], null);
}
list.Add(t);
}
return list;
}
}
1.3 使用示例
在应用层(一般是Application项目)可以直接使用自定义仓储实现SQL语句操作。
public class DemoService : DemoAppServiceBase, IApplicationService
{
private readonly ISqlRepository _repository;
public DataService(ISqlRepository repository)
{
_repository = repository;
}
public async Task<int> UpdateTable()
{
string strSql="update tablename set colname1=value1 where id=xx";
var result = _repository.Execute(strSql).Result;
return await Task.FromResult(result);
}
public async Task<DataTable> QueryTable()
{
string strSql="select colname1,colname2 from tablename";
var result = _repository.ExecuteDataTable(strSql).Result;
return await Task.FromResult(result);
}
}