SqlSugar的一些使用方法
125
2022-01-28
在自己捣鼓了N年的自写ORM后，最后还是走向了SqlSugar。无他，这玩意，基本上实现了我所有对ORM的期望。这里总结下我所使用的一些方法。
运行环境是.Net6，因为安装的是Core版。
Install-Package SqlSugarCore使用SqlSugar有两种方法，一种是静态全局使用，一种是依赖注入。
1.静态全局配置
MySQL示例
/// <summary>
/// SqlSugarUtil
/// </summary>
public class SqlSugarUtil
{
/// <summary>
/// 默认静态实例
/// </summary>
private static SqlSugarScope _db;
/// <summary>
/// 日志模板
/// </summary>
public readonly static string LogTemplate = @"#Date {0}
#Params
{1}
#SQL
{2}";
/// <summary>
/// 初始化
/// </summary>
static SqlSugarUtil()
{
//配置
var configs = new List<ConnectionConfig>();
//构建所有数据库连接实例
var builder = new ConfigurationBuilder()
.SetBasePath(AppContext.BaseDirectory)
.AddJsonFile("appsettings.json")
.AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT")}.json", optional: true, reloadOnChange: true);
//获取配置项
var configurationRoot = builder.Build();
//构建所有数据库连接实例
var sections = configurationRoot.GetSection("connectionStrings").GetChildren();
foreach (var item in sections)
{
configs.Add(new ConnectionConfig
{
DbType = DbType.MySql,
ConnectionString = item.Value,
ConfigId = item.Key,
IsAutoCloseConnection = true
});
}
//写入DB
_db = new SqlSugarScope(configs);
//雪花标识ID
SnowFlakeSingle.WorkId = 1;
}
/// <summary>
/// 获取指定数据库连接
/// </summary>
/// <param name="databaseName"></param>
/// <returns></returns>
public static SqlSugarProvider DB(DatabaseName databaseName)
{
var connection = databaseName switch
{
DatabaseName.DB_User => _db.GetConnection(DatabaseName.DB_User.ToString()),
DatabaseName.DB_Admin => _db.GetConnection(DatabaseName.DB_Admin.ToString()),
DatabaseName.DB_Order => _db.GetConnection(DatabaseName.DB_Order.ToString()),
DatabaseName.DB_Model => _db.GetConnection(DatabaseName.DB_Model.ToString()),
_ => _db.GetConnection(DatabaseName.DB_User.ToString()),
};
connection.Aop.OnLogExecuted = (sql, pars) =>
{
LogManager.GetLogger("dblog").Info(string.Format(LogTemplate, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), string.Join("\r\n", pars.Select(a => $"SET {a.ParameterName.Replace("@", "@`")}`={a.Value};")), sql));
};
connection.Aop.OnError = ex =>
{
LogManager.GetLogger("dblog").Error($"SQL执行异常({ex.Message}{ex.InnerException?.Message})，{string.Format(LogTemplate, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "", ex.Sql)}");
};
return connection;
}
}
appsetting.json的数据库配置
...
"connectionStrings": {
"DB_Admin": "......",
"DB_User": "......",
"DB_Order": "......",
"DB_Model": "......"
},
...
使用方式
//获取通知缓存数
var notificationCount = await SqlSugarUtil.DB(DatabaseName.DB_User)
.Queryable<NotificationEntity>()
.InnerJoin<FSMUserNotificationEntity>((a, b) => a.Id == b.NotificationId && b.UserId == userId && b.IsRead == false)
.Where((a, b) => a.IsDelete == false)
.CountAsync();
2.依赖注入配置
MySQL示例，Autofac的DI注入
/// <summary>
/// 注册SqlSugarScope（线程安全，一定要用单例模式）
/// </summary>
/// <param name="builder">构建</param>
/// <param name="configSection">配置项</param>
public static void RegisterSqlSugarScope(this ContainerBuilder builder, IConfigurationSection configSection)
{
builder
.RegisterType<SqlSugarScope>()
.As<ISqlSugarClient>()
.WithParameter("configs", configSection.GetChildren().Select(item => new ConnectionConfig
{
DbType = DbType.MySql,
ConnectionString = item.Value,
ConfigId = item.Key,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuted = (sql, pars) =>
{
LogManager.GetLogger("dblog").Info(string.Format(SqlSugarUtil.LogTemplate, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), string.Join("\r\n", pars.Select(a => $"SET {a.ParameterName.Replace("@", "@`")}`={a.Value};")), sql));
},
OnError = ex =>
{
LogManager.GetLogger("dblog").Error($"SQL执行异常({ex.Message}{ex.InnerException?.Message})，{string.Format(SqlSugarUtil.LogTemplate, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "", ex.Sql)}");
}
}
}).ToList())
.SingleInstance();
}
/// <summary>
/// 获取连接
/// </summary>
/// <param name="sugarScope"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public static SqlSugarProvider GetConnection(this ISqlSugarClient sugarScope, DatabaseName dbName)
{
return ((SqlSugarScope)sugarScope).GetConnection(dbName.ToString());
}
在Program中的注入
//自动注册配置中的服务
builder.Host.ConfigureContainer<ContainerBuilder>(containerBuilder =>
{
containerBuilder.RegisterSqlSugarScope(builder.Configuration.GetSection("connectionStrings"));
...
});
使用方式（使用了Autofac.Annotation，参照以往的博客内容）
/// <summary>
/// 后台数据库连接
/// </summary>
[Autowired]
private ISqlSugarClient dbClient { get; set; }
...
//获取后台用户列表数据
var list = await dbClient.GetConnection(DatabaseName.DB_Admin)
.Queryable<UserEntity>()
.Where(a => a.IsDeleted == false)
.OrderByDescending(a => a.CreatedTime)
.ToListAsync();
对SqlSugar的一些扩展封装，使用到了AutoMapper和自定义的结果类
#region 查询扩展
/// <summary>
/// 单笔映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <returns>单笔数据</returns>
public static T2 FirstMap<T1, T2>(this ISugarQueryable<T1> queryable)
{
var mapper = GlobalHttpContext.GetService<IMapper>();
var entity = queryable.First();
return mapper.Map<T1, T2>(entity);
}
/// <summary>
/// 单笔映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <returns>单笔数据</returns>
public static async Task<T2> FirstMapAsync<T1, T2>(this ISugarQueryable<T1> queryable)
{
var mapper = GlobalHttpContext.GetService<IMapper>();
var entity = await queryable.FirstAsync();
return mapper.Map<T1, T2>(entity);
}
/// <summary>
/// 单笔映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <returns>单笔数据</returns>
public static ResultDto<T2> FirstMapResult<T1, T2>(this ISugarQueryable<T1> queryable)
{
var result = new ResultDto<T2>
{
Status = true
};
try
{
var mapper = GlobalHttpContext.GetService<IMapper>();
var entity = queryable.First();
if (entity == null)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"找不到数据";
}
else
result.Data = mapper.Map<T1, T2>(entity);
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
/// <summary>
/// 单笔映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <returns>单笔数据</returns>
public static async Task<ResultDto<T2>> FirstMapResultAsync<T1, T2>(this ISugarQueryable<T1> queryable)
{
var result = new ResultDto<T2>
{
Status = true
};
try
{
var mapper = GlobalHttpContext.GetService<IMapper>();
var entity = await queryable.FirstAsync();
if (entity == null)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"找不到数据";
}
else
result.Data = mapper.Map<T1, T2>(entity);
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
/// <summary>
/// 列表映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <returns>列表数据</returns>
public static List<T2> ToListMap<T1, T2>(this ISugarQueryable<T1> queryable)
{
var mapper = GlobalHttpContext.GetService<IMapper>();
var list = queryable.ToList();
return mapper.Map<List<T1>, List<T2>>(list);
}
/// <summary>
/// 列表映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <returns>列表数据</returns>
public static async Task<List<T2>> ToListMapAsync<T1, T2>(this ISugarQueryable<T1> queryable)
{
var mapper = GlobalHttpContext.GetService<IMapper>();
var list = await queryable.ToListAsync();
return mapper.Map<List<T1>, List<T2>>(list);
}
/// <summary>
/// 列表映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <returns>列表数据</returns>
public static ListResultDto<T2> ToListMapResult<T1, T2>(this ISugarQueryable<T1> queryable)
{
var listResult = new ListResultDto<T2>
{
Status = true
};
try
{
var mapper = GlobalHttpContext.GetService<IMapper>();
listResult.List.AddRange(mapper.Map<List<T1>, List<T2>>(queryable.ToList()));
}
catch (Exception ex)
{
listResult.Status = false;
listResult.Tag = 1;
listResult.Msg = $"数据库执行异常：{ex.Message}";
}
return listResult;
}
/// <summary>
/// 列表映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <returns>列表数据</returns>
public static async Task<ListResultDto<T2>> ToListMapResultAsync<T1, T2>(this ISugarQueryable<T1> queryable)
{
var listResult = new ListResultDto<T2>
{
Status = true
};
try
{
var mapper = GlobalHttpContext.GetService<IMapper>();
var list = await queryable.ToListAsync();
listResult.List.AddRange(mapper.Map<List<T1>, List<T2>>(list));
}
catch (Exception ex)
{
listResult.Status = false;
listResult.Tag = 1;
listResult.Msg = $"数据库执行异常：{ex.Message}";
}
return listResult;
}
/// <summary>
/// 分页映射
/// </summary>
/// <typeparam name="T">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <param name="pageNum">页码</param>
/// <param name="pageSize">页长</param>
/// <returns>分页数据</returns>
public static PageResultDto<T> ToPageListResult<T>(this ISugarQueryable<T> queryable, int pageNum, int pageSize)
{
var pageResult = new PageResultDto<T>
{
Status = true,
Page = pageNum,
Limit = pageSize
};
try
{
var mapper = GlobalHttpContext.GetService<IMapper>();
int total = 0;
pageResult.List.AddRange(queryable.ToPageList(pageNum, pageSize, ref total));
pageResult.Total = total;
}
catch (Exception ex)
{
pageResult.Status = false;
pageResult.Tag = 1;
pageResult.Msg = $"数据库执行异常：{ex.Message}";
}
return pageResult;
}
/// <summary>
/// 分页映射
/// </summary>
/// <typeparam name="T">源实体</typeparam>
/// <param name="queryable">查询</param>
/// <param name="pageNum">页码</param>
/// <param name="pageSize">页长</param>
/// <returns>分页数据</returns>
public static async Task<PageResultDto<T>> ToPageListResultAsync<T>(this ISugarQueryable<T> queryable, int pageNum, int pageSize)
{
var pageResult = new PageResultDto<T>
{
Status = true,
Page = pageNum,
Limit = pageSize
};
try
{
var mapper = GlobalHttpContext.GetService<IMapper>();
RefAsync<int> total = 0;
var list = await queryable.ToPageListAsync(pageNum, pageSize, total);
pageResult.List.AddRange(list);
pageResult.Total = total.Value;
}
catch (Exception ex)
{
pageResult.Status = false;
pageResult.Tag = 1;
pageResult.Msg = $"数据库执行异常：{ex.Message}";
}
return pageResult;
}
/// <summary>
/// 分页映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <param name="pageNum">页码</param>
/// <param name="pageSize">页长</param>
/// <returns>分页数据</returns>
public static PageResultDto<T2> ToPageListMapResult<T1, T2>(this ISugarQueryable<T1> queryable, int pageNum, int pageSize)
{
var pageResult = new PageResultDto<T2>
{
Status = true,
Page = pageNum,
Limit = pageSize
};
try
{
var mapper = GlobalHttpContext.GetService<IMapper>();
int total = 0;
pageResult.List.AddRange(mapper.Map<List<T1>, List<T2>>(queryable.ToPageList(pageNum, pageSize, ref total)));
pageResult.Total = total;
}
catch (Exception ex)
{
pageResult.Status = false;
pageResult.Tag = 1;
pageResult.Msg = $"数据库执行异常：{ex.Message}";
}
return pageResult;
}
/// <summary>
/// 分页映射
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标DTO</typeparam>
/// <param name="queryable">查询</param>
/// <param name="pageNum">页码</param>
/// <param name="pageSize">页长</param>
/// <returns>分页数据</returns>
public static async Task<PageResultDto<T2>> ToPageListMapResultAsync<T1, T2>(this ISugarQueryable<T1> queryable, int pageNum, int pageSize)
{
var pageResult = new PageResultDto<T2>
{
Status = true,
Page = pageNum,
Limit = pageSize
};
try
{
var mapper = GlobalHttpContext.GetService<IMapper>();
RefAsync<int> total = 0;
var list = await queryable.ToPageListAsync(pageNum, pageSize, total);
pageResult.List.AddRange(mapper.Map<List<T1>, List<T2>>(list));
pageResult.Total = total.Value;
}
catch (Exception ex)
{
pageResult.Status = false;
pageResult.Tag = 1;
pageResult.Msg = $"数据库执行异常：{ex.Message}";
}
return pageResult;
}
#endregion
#region 更新扩展
/// <summary>
/// 更新并返回执行结果
/// </summary>
/// <typeparam name="T">源实体</typeparam>
/// <param name="updateable">更新</param>
/// <returns>结果</returns>
public static ResultDto ExecuteResult<T>(this IUpdateable<T> updateable) where T : class, new()
{
var result = new ResultDto
{
Status = true
};
try
{
if (updateable.ExecuteCommandHasChange())
return result;
result.Status = false;
result.Tag = 1;
result.Msg = "执行【更新】后数据无变化";
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
/// <summary>
/// 更新并返回执行结果
/// </summary>
/// <typeparam name="T">源实体</typeparam>
/// <param name="updateable">更新</param>
/// <returns>结果</returns>
public static async Task<ResultDto> ExecuteResultAsync<T>(this IUpdateable<T> updateable) where T : class, new()
{
var result = new ResultDto
{
Status = true
};
try
{
if (await updateable.ExecuteCommandHasChangeAsync())
return result;
result.Status = false;
result.Tag = 1;
result.Msg = "执行【更新】后数据无变化";
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
#endregion
#region 插入扩展
/// <summary>
/// 插入并返回执行结果
/// </summary>
/// <typeparam name="T">源实体</typeparam>
/// <param name="insertable">插入</param>
/// <returns>结果</returns>
public static ResultDto ExecuteResult<T>(this IInsertable<T> insertable) where T : class, new()
{
var result = new ResultDto
{
Status = true
};
try
{
if (insertable.ExecuteCommand() > 0)
return result;
result.Status = false;
result.Tag = 1;
result.Msg = "执行【插入】后数据无变化";
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
/// <summary>
/// 插入并返回执行结果
/// </summary>
/// <typeparam name="T">源实体</typeparam>
/// <param name="insertable">插入</param>
/// <returns>结果</returns>
public static async Task<ResultDto> ExecuteResultAsync<T>(this IInsertable<T> insertable) where T : class, new()
{
var result = new ResultDto
{
Status = true
};
try
{
if (await insertable.ExecuteCommandAsync() > 0)
return result;
result.Status = false;
result.Tag = 1;
result.Msg = "执行【插入】后数据无变化";
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
/// <summary>
/// 插入并返回携带实体的执行结果
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标实体</typeparam>
/// <param name="insertable">插入</param>
/// <returns>结果</returns>
public static ResultDto<T2> ExecuteResultEntity<T1, T2>(this IInsertable<T1> insertable) where T1 : class, new()
{
var result = new ResultDto<T2>
{
Status = true
};
try
{
var entity = insertable.ExecuteReturnEntity();
if (entity == null)
{
result.Status = false;
result.Tag = 1;
result.Msg = "执行【插入】后无返回";
return result;
}
var mapper = GlobalHttpContext.GetService<IMapper>();
result.Data = mapper.Map<T1, T2>(entity);
return result;
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
/// <summary>
/// 插入并返回携带实体的执行结果
/// </summary>
/// <typeparam name="T1">源实体</typeparam>
/// <typeparam name="T2">目标实体</typeparam>
/// <param name="insertable">插入</param>
/// <returns>结果</returns>
public static async Task<ResultDto<T2>> ExecuteResultEntityAsync<T1, T2>(this IInsertable<T1> insertable) where T1 : class, new()
{
var result = new ResultDto<T2>
{
Status = true
};
try
{
var entity = await insertable.ExecuteReturnEntityAsync();
if (entity == null)
{
result.Status = false;
result.Tag = 1;
result.Msg = "执行【插入】后无返回";
return result;
}
var mapper = GlobalHttpContext.GetService<IMapper>();
result.Data = mapper.Map<T1, T2>(entity);
return result;
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
#endregion
#region 删除扩展
/// <summary>
/// 删除并返回执行结果
/// </summary>
/// <typeparam name="T">源实体</typeparam>
/// <param name="deleteable">删除</param>
/// <returns>结果</returns>
public static ResultDto ExecuteResult<T>(this IDeleteable<T> deleteable) where T : class, new()
{
var result = new ResultDto
{
Status = true
};
try
{
if (deleteable.ExecuteCommand() > 0)
return result;
result.Status = false;
result.Tag = 1;
result.Msg = "执行【删除】后数据无变化";
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
/// <summary>
/// 删除并返回执行结果
/// </summary>
/// <typeparam name="T">源实体</typeparam>
/// <param name="deleteable">删除</param>
/// <returns>结果</returns>
public static async Task<ResultDto> ExecuteResultAsync<T>(this IDeleteable<T> deleteable) where T : class, new()
{
var result = new ResultDto
{
Status = true
};
try
{
if (await deleteable.ExecuteCommandAsync() > 0)
return result;
result.Status = false;
result.Tag = 1;
result.Msg = "执行【删除】后数据无变化";
}
catch (Exception ex)
{
result.Status = false;
result.Tag = 1;
result.Msg = $"数据库执行异常：{ex.Message}";
}
return result;
}
#endregion
#region 备份扩展
/// <summary>
/// 获取插入SQL
/// </summary>
/// <param name="dr">数据行</param>
/// <param name="columns">列</param>
/// <returns></returns>
private static string GetInsertValueSql(this System.Data.DataRow dr, System.Data.DataColumnCollection columns)
{
var values = new List<string>();
foreach (System.Data.DataColumn dc in columns)
{
if (dr[dc.ColumnName] == DBNull.Value)
{
values.Add("null");
continue;
}
if (dc.DataType == typeof(string))
{
values.Add($"'{dr[dc.ColumnName].ToString().Replace("'", "\\'")}'");
continue;
}
if (dc.DataType == typeof(DateTime))
{
values.Add($"'{dr[dc.ColumnName]:yyyy-MM-dd HH:mm:ss}'");
continue;
}
if (dc.DataType == typeof(bool))
{
values.Add($"{dr[dc.ColumnName].ToStringEx().ToBoolInt()}");
continue;
}
if (dc.DataType.IsEnum)
{
values.Add($"{(int)dr[dc.ColumnName]}");
continue;
}
values.Add($"{dr[dc.ColumnName].ToString().Replace("'", "\\'")}");
}
return $"({values.ToJoinString(",")})";
}
/// <summary>
/// 备份指定数据
/// </summary>
/// <param name="provider">查询</param>
/// <param name="backupFileName">备份文件</param>
/// <param name="dbTableNames">数据表名</param>
/// <returns></returns>
public static async Task<bool> BackupAsync(this SqlSugarProvider provider, string backupFileName, params string[] dbTableNames)
{
if (dbTableNames == null)
return false;
var builder = new StringBuilder();
builder.AppendLine("/*");
builder.AppendLine($"环境：{GlobalHttpContext.CurrentEnvironment.GetComment()}");
builder.AppendLine($"库表：{provider.Ado.Connection.Database}");
builder.AppendLine($"时间：{DateTime.Now:yyyy-MM-dd HH:mm:ss}");
builder.AppendLine("*/");
builder.AppendLine();
foreach (var item in dbTableNames)
{
var table = await provider.Ado.GetDataTableAsync($"SELECT * FROM `{item}`;");
if (table == null)
continue;
builder.AppendLine($"-- -----------------------------------");
builder.AppendLine($"-- {item} 共 {table.Rows.Count} 条数据 ");
builder.AppendLine($"-- -----------------------------------");
if (table.Rows.Count == 0)
continue;
var columns = new List<string>();
foreach (System.Data.DataColumn dc in table.Columns)
columns.Add(dc.ColumnName);
var values = new List<string>();
var index = 0;
foreach (System.Data.DataRow dr in table.Rows)
{
if (index % 1000 == 0)
{
if (values.Count > 0)
{
builder.AppendLine($"{values.ToJoinString(",\n")};");
values.Clear();
}
if (table.Rows.Count > index)
builder.AppendLine($"INSERT INTO `{item}`(`{columns.ToJoinString("`,`")}`) VALUES");
}
index++;
values.Add(dr.GetInsertValueSql(table.Columns));
}
builder.AppendLine($"{values.ToJoinString(",\n")};");
builder.AppendLine();
}
await File.WriteAllTextAsync(backupFileName, builder.ToString());
return true;
}
/// <summary>
/// 备份所有数据
/// </summary>
/// <param name="provider">查询</param>
/// <param name="backupFileName">备份文件</param>
/// <param name="ignoreTables">或略表</param>
/// <returns></returns>
public static async Task<bool> BackupAllAsync(this SqlSugarProvider provider, string backupFileName, params string[] ignoreTables)
{
var tables = await provider.Ado.SqlQueryAsync<string>($"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{provider.Ado.Connection.Database}';");
if (tables == null || tables.Count == 0)
return false;
var builder = new StringBuilder();
builder.AppendLine("/*");
builder.AppendLine($"环境：{GlobalHttpContext.CurrentEnvironment.GetComment()}");
builder.AppendLine($"库表：{provider.Ado.Connection.Database}");
builder.AppendLine($"时间：{DateTime.Now:yyyy-MM-dd HH:mm:ss}");
builder.AppendLine("*/");
builder.AppendLine();
foreach (var item in tables)
{
if (ignoreTables != null && ignoreTables.Any(a => a.ToLower() == item.ToLower()))
continue;
var table = await provider.Ado.GetDataTableAsync($"SELECT * FROM `{item}`;");
if (table == null)
continue;
builder.AppendLine($"-- -----------------------------------");
builder.AppendLine($"-- {item} 共 {table.Rows.Count} 条数据 ");
builder.AppendLine($"-- -----------------------------------");
if (table.Rows.Count == 0)
continue;
var columns = new List<string>();
foreach (System.Data.DataColumn dc in table.Columns)
columns.Add(dc.ColumnName);
var values = new List<string>();
var index = 0;
foreach (System.Data.DataRow dr in table.Rows)
{
if (index % 100 == 0)
{
if (values.Count > 0)
{
builder.AppendLine($"{values.ToJoinString(",\n")};");
values.Clear();
}
if (table.Rows.Count > index)
builder.AppendLine($"INSERT INTO `{item}`(`{columns.ToJoinString("`,`")}`) VALUES");
}
index++;
values.Add(dr.GetInsertValueSql(table.Columns));
}
builder.AppendLine($"{values.ToJoinString(",\n")};");
builder.AppendLine();
}
await File.WriteAllTextAsync(backupFileName, builder.ToString());
return true;
}
#endregion
更多SqlSugar的使用方式，请参SqlSugar官网文档
- 0
- 0
-
分享