SqlSugar-C#版（一）_安装与基础使用
一、安装：
1、引用NuGet包：
2、SqlSugar数据库连接类-非单例（看完这个后可看下一章 单例）
SqlSugar数据库连接类-非单例
/**
*┌──────────────────────────────────────────────────────────────┐
*│ 描 述：SqlSugar数据库连接类-非单例
*│ 作 者：执笔小白
*│ 版 本：1.3
*│ 创建时间：2022-12-2 15:40:56
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│ 命名空间: Repository._Base
*│ 类 名：PracticeContext
*└──────────────────────────────────────────────────────────────┘
*/
//using IRepository._Base;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
namespace Repository._Base
{
/// <summary>
/// 操作SqlSugarClient实例
/// 这里用的执行一次创建一个SqlSugarClient，因为有人说SqlSugarClient是非线程安全的，所以未做成单例或静态变量
/// </summary>
public class PracticeContext // : IPracticeContext
{
/// <summary>
/// 数据库连接字符串
/// ConfigurationManager.AppSettings["DefaultConnection"];
/// ConfigurationManager.ConnectionStrings["MSSqlConStr"].ConnectionString;
/// </summary>
private static readonly string _connectionString = ConfigurationManager.AppSettings["DefaultConnection"];
/// <summary>
/// SqlSugarClient对象
/// </summary>
public SqlSugarClient Db;
/// <summary>
/// 创建一个SqlSugarClient对象
/// </summary>
/// <param name="connName">连接字符串名称</param>
public PracticeContext()
{
// 创建SqlSugarClient对象
Db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = _connectionString, // 连接字符串
DbType = DbType.MySql, // 数据库类型
InitKeyType = InitKeyType.Attribute, // 从特性读取主键和自增列信息
IsAutoCloseConnection = true, // 开启自动释放模式,和EF原理一样
});
Db.Ado.CommandTimeOut = 720; // 12分钟
// 每次Sql执行前事件，记录进行的操作
Db.Aop.OnLogExecuting = (sql, pars) =>
{
StringBuilder sqlStr = new();
if (sql.StartsWith("UPDATE") || sql.StartsWith("INSERT"))
{
Console.ForegroundColor = ConsoleColor.Blue;
sqlStr.AppendLine($"==============将要执行新增/修改操作==============");
}
if (sql.StartsWith("DELETE"))
{
Console.ForegroundColor = ConsoleColor.Red;
sqlStr.AppendLine($"==============将要执行删除操作==============");
}
if (sql.StartsWith("SELECT"))
{
Console.ForegroundColor = ConsoleColor.Green;
sqlStr.AppendLine($"==============将要执行查询操作==============");
}
sqlStr.AppendLine("预SQL:");
sqlStr.AppendLine(" " + sql);
string sqlPars = Db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value));
sqlStr.AppendLine("SQL预传参：");
sqlStr.AppendLine(" " + sqlPars);
Console.WriteLine(sqlStr.ToString()); // 打印
Console.ForegroundColor = ConsoleColor.White;
// 记录执行的信息
};
//每次Sql执行后事件，记录SQL执行完的信息
Db.Aop.OnLogExecuted = (sql, pars) =>
{
// 执行时间超过1秒
if (Db.Ado.SqlExecutionTime.TotalSeconds > 1)
{
StringBuilder sqlPStr = new();
sqlPStr.AppendLine($"==============执行了下面的操作==============");
var fileName = Db.Ado.SqlStackTrace.FirstFileName; // 代码CS文件名
sqlPStr.AppendLine("代码CS文件名:"+ fileName);
var fileLine = Db.Ado.SqlStackTrace.FirstLine; // 代码行数
sqlPStr.AppendLine("代码行数:"+ fileLine);
var FirstMethodName = Db.Ado.SqlStackTrace.FirstMethodName; // 方法名
sqlPStr.AppendLine("方法名:" + FirstMethodName);
sqlPStr.AppendLine("SQL:");
sqlPStr.AppendLine(" "+ sql);
var sqlPars = Db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value)); // 参数
sqlPStr.AppendLine("SQL传参：");
sqlPStr.AppendLine(" " + sqlPars);
// 打印
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine(sqlPStr);
Console.ForegroundColor = ConsoleColor.White;
// 记录执行的信息
}
};
// 记录SQL报错
Db.Aop.OnError = (exp) =>
{
StringBuilder sqlStr = new();
sqlStr.AppendLine($"==============数据库执行报错==============");
sqlStr.AppendLine("SQL: ");
sqlStr.AppendLine(" " + exp.Sql);
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(sqlStr); // 打印
Console.ForegroundColor = ConsoleColor.White;
// 记录执行的信息
};
}
/// <summary>
/// 验证是否连接成功
/// 注：一般长连接使用，即IsAutoCloseConnection=true时；=false时使用较少
/// </summary>
/// <returns></returns>
public bool IsValidConnection()
{
return Db.Ado.IsValidConnection();
}
/// <summary>
/// 根据数据表生成Entity（实体）,
/// 带有特征
/// 带有默认值
/// </summary>
/// <param name="classNameSpace">指定类的包名</param>
/// <param name="tableName">指定表名；不指定时生成数据库中所有表的实体</param>
/// <param name="isStartsWith_TableName">生成表名以指定关键字为开头的表</param>
public void DBFirst(string classNameSpace, string tableName = null,bool isStartsWith_TableName = false)
{
// 生成的目录
string filePath = System.Environment.CurrentDirectory.ToString() + "/EntityFile";
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
if (string.IsNullOrEmpty(tableName)) // 生成数据库中所有表的实体
{
Db.DbFirst.IsCreateAttribute().IsCreateDefaultValue().CreateClassFile(filePath, classNameSpace);
}
else if(isStartsWith_TableName) // 生成数据库中生成表名以指定关键字为开头的表
{
Db.DbFirst.Where(it => it.StartsWith(tableName)).IsCreateAttribute().IsCreateDefaultValue().CreateClassFile(filePath, classNameSpace);
}
else // 生成数据库中指定表名的实体
{
Db.DbFirst.Where("tableName").IsCreateAttribute().IsCreateDefaultValue().CreateClassFile(filePath, classNameSpace);
}
Console.WriteLine("输出目录："+ filePath);
}
/// <summary>
/// 根据Entity（实体）生成数据库中的表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity">指定Entity名；不指定时生成指定作用域中/默认作用域（一般指文件夹名）下所有Entity的表结构</param>
/// <param name="dllName">指定类的包名</param>
/// <param name="classNameSpaces">指定类的包名</param>
public void CodeFirst<T>(T entity = null, string dllName= "BOZHON.Repository.dll", string[] classNameSpaces =null) where T : class, new()
{
classNameSpaces ??= new string[] { "Entity" };
if (entity is null)
{
var path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + @"/";
List<Type> entitylist = new();
if (!string.IsNullOrWhiteSpace(dllName))
{
dllName = path + dllName;
Assembly assembly = Assembly.LoadFrom(dllName);
Type[] ts = assembly.GetTypes();
foreach (string classNameSpace in classNameSpaces)
{
foreach (Type t in ts)
{
if (t.FullName.Contains(classNameSpace))
{
entitylist.Add(t);
}
}
}
}
Db.CodeFirst.SetStringDefaultLength(255).InitTables(entitylist.ToArray());
}
else
{
Db.CodeFirst.SetStringDefaultLength(255).InitTables(typeof(T));
}
}
/// <summary>
/// 导入种子数据
/// 注：批量不可用（指定Entity名时功能可用，通过“classNameSpaces”批量导入时功能不可用）
/// ① DBSeed文件使用json文件保存；
/// ② 一张表一个DBSeed文件；
/// ③ 文件名字与表名保持一致；
/// </summary>
/// <param name="dbSeedFileDirec">DB种子数据所在的的文件夹（放在程序目录下）</param>
/// <typeparam name="T"></typeparam>
/// <param name="entity">指定Entity名；不指定时生成指定作用域中/默认作用域（一般指文件夹名）下所有Entity的表的数据</param>
/// <param name="dllName">指定实体类的包名</param>
/// <param name="classNameSpaces">指定实体类的包名</param>
/// <exception cref="NotImplementedException"></exception>
public void ImportDBSeed<T>(string dbSeedFileDirec, T entity = null, string dllName = "BOZHON.Repository.dll", string[] classNameSpaces = null) where T : class, new()
{
classNameSpaces = classNameSpaces == null ? new string[] { "Entity" } : classNameSpaces;
var path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
string dbSeedFileDirecPath = path + dbSeedFileDirec + @"/";
if (!Directory.Exists(dbSeedFileDirecPath))
{
throw new Exception("DB数据初始化失败！在程序目录下找不到DB种子数据文件夹！");
}
if (entity is null)
{
List<Type> entitylist = new List<Type>();
if (!string.IsNullOrWhiteSpace(dllName))
{
dllName = path + dllName;
Assembly assembly = Assembly.LoadFrom(dllName);
Type[] ts = assembly.GetTypes();
foreach (string classNameSpace in classNameSpaces)
{
foreach (Type t in ts)
{
if (t.FullName.Contains(classNameSpace))
{
entitylist.Add(t);
}
}
}
}
foreach (Type type in entitylist)
{
string dbSeedFilePath = dbSeedFileDirecPath + type.Name + ".json";
if (File.Exists(dbSeedFilePath))
{
Type typeList = typeof(List<>);
Type actualType = typeList.MakeGenericType(type);
dynamic obj = Activator.CreateInstance(actualType);
obj = JsonFileHelper.ReadjsonT<object>(dbSeedFilePath); // 加载数据
//Db.Insertable(obj).ExecuteCommand(); // 未找到合适的无实体插入方法
throw new Exception("批量插入请使用方法ImportDBSeed2！");
}
}
}
else
{
string dbSeedFilePath = dbSeedFileDirecPath + entity.GetType().Name + ".json";
if (File.Exists(dbSeedFilePath))
{
T obj = JsonFileHelper.ReadjsonT<T>(dbSeedFilePath); // 加载数据
Db.Insertable(obj);
}
}
}
/// <summary>
/// 导入种子数据-批量
/// ① DBSeed文件使用json文件保存；
/// ② 一张表一个DBSeed文件；
/// ③ 文件名字与表名保持一致；
/// </summary>
/// <param name="dbSeedFileDirec">DB种子数据所在的的文件夹（放在程序目录下）</param>
/// <typeparam name="T"></typeparam>
/// <param name="entity">指定Entity名；不指定时生成指定作用域中/默认作用域（一般指文件夹名）下所有Entity的表的数据</param>
/// <param name="dllName">指定实体类的包名</param>
/// <param name="classNameSpaces">指定实体类的包名</param>
/// <exception cref="NotImplementedException"></exception>
public void ImportDBSeed2(string dbSeedFileDirec)
{
var path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
string dbSeedFileDirecPath = path + dbSeedFileDirec + @"/";
if (!Directory.Exists(dbSeedFileDirecPath))
{
throw new Exception("DB数据初始化失败！在程序目录下找不到DB种子数据文件夹！");
}
#region 设置DBSeed
ImportDBSeed2<TestTable1>(dbSeedFileDirecPath);
ImportDBSeed2<TestTable2>(dbSeedFileDirecPath);
ImportDBSeed2<TestTable3>(dbSeedFileDirecPath);
#endregion 设置DBSeed
}
/// <summary>
/// ImportDBSeed2-导入种子数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dbSeedFileDirecPath">文件夹路径</param>
private void ImportDBSeed2<T>(string dbSeedFileDirecPath) where T : class, new()
{
string dbSeedFilePath = dbSeedFileDirecPath + new T().GetType().Name + ".json";
if (File.Exists(dbSeedFilePath))
{
var objs = JsonFileHelper.ReadjsonT<List<T>>(dbSeedFilePath); // 加载数据
Db.Insertable<T>(objs).ExecuteCommand();
}
}
/// <summary>
/// 导出种子数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dbSeedFileDirec">DB种子数据导出的文件夹（生成在程序目录下）</param>
/// <param name="entity">指定Entity名；不指定时生成指定作用域中/默认作用域（一般指文件夹名）下所有Entity的表的数据</param>
/// <param name="dllName">指定实体类的包名</param>
/// <param name="classNameSpaces">指定实体类的包名</param>
public void ExportDBSeed<T>(string dbSeedFileDirec, T entity = null, string dllName = "BOZHON.Repository.dll", string[] classNameSpaces = null) where T : class, new()
{
classNameSpaces = classNameSpaces == null ? new string[] { "Entity" } : classNameSpaces;
var path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
string dbSeedFileDirecPath = path + dbSeedFileDirec + @"/";
if (!Directory.Exists(dbSeedFileDirecPath))
{
Directory.CreateDirectory(dbSeedFileDirecPath); // 生成目录
}
if (entity is null)
{
List<Type> entitylist = new List<Type>();
if (!string.IsNullOrWhiteSpace(dllName))
{
dllName = path + dllName;
Assembly assembly = Assembly.LoadFrom(dllName);
Type[] ts = assembly.GetTypes();
foreach (string classNameSpace in classNameSpaces)
{
foreach (Type t in ts)
{
if (t.FullName.Contains(classNameSpace))
{
entitylist.Add(t);
}
}
}
}
foreach (Type type in entitylist)
{
string dbSeedFilePath = dbSeedFileDirecPath + type.Name + ".json";
var seedDatas = Db.Queryable(type.Name, type.Name).ToList();
JsonFileHelper.WritejsonT(dbSeedFilePath, seedDatas);
}
}
else
{
string dbSeedFilePath = dbSeedFileDirecPath + entity.GetType().Name + ".json";
var seedDatas = Db.Queryable<T>().ToList();
JsonFileHelper.WritejsonT(dbSeedFilePath, seedDatas);
}
}
}
}二、SqlSugar使用教程：
1、SqlSugar特征讲解
实体示例-SqlSugar特征讲解
/**
*┌──────────────────────────────────────────────────────────────┐
*│ 描 述：示例表
*│ 作 者：执笔小白
*│ 版 本：1.0
*│ 创建时间：2022-12-2 15:40:56
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│ 命名空间: Repository._Base
*│ 类 名：TestTable
*└──────────────────────────────────────────────────────────────┘
*/
/// <summary>
/// 示例表
/// SugarTable(表名，表描述，isDisabledDelete:禁止删除列,isCreateTableFieldSort:创建表时对字段进行排序)
/// </summary>
[SugarTable("testtable", "示例表", false, false)]
public class TestTable
{
/// <summary>
/// 主键ID
/// 不空、主键、自增、列名、说明
/// </summary>
[SugarColumn(IsNullable = false, IsPrimaryKey = true,IsIdentity=true,ColumnName = "Id",ColumnDescription = "主键ID")]
public int Id { get; set; }
/// <summary>
///
/// OldColumnName：将Uname列名改为Name
/// IndexGroupNameList：索引
/// </summary>
[SugarColumn(OldColumnName = "UName", ColumnDataType = "nvarchar", Length = 32, IsNullable = false, IndexGroupNameList = new string[] { "index_UName" })]
public string Name { get; set; } = string.Empty;
/// <summary>
///
/// </summary>
[SugarColumn(ColumnDataType = "varchar", Length = 32, IsNullable = false)]
public string Pwd { get; set; } = string.Empty;
/// <summary>
///
/// UniqueGroupNameList：唯一索引
/// </summary>
[SugarColumn(IsNullable = false,UniqueGroupNameList =new string[] { "index_FId" })]
public int FId { get; set; }
/// <summary>
///
/// </summary>
public bool IsDelete { get; set; }
/// <summary>
/// IsIgnore ：ORM不处理该列
/// IsOnlyIgnoreInsert ：插入操作时不处理该列
/// length=5 长度5 decimal(5,2)
/// DecimalDigits=2 ：精度2 decimal(5,2)
/// </summary>ColumnDescription 备注
[SugarColumn(IsIgnore = true, IsOnlyIgnoreInsert=true, Length = 5, DecimalDigits = 2)]
public float dd { get; set; }
}
// SugarColumn特性
// public class SugarColumn : Attribute
// {
// public SugarColumn();
//
// public string ColumnName { get; set; }//列名
// public bool IsIgnore { get; set; }//是否忽略
// public bool IsPrimaryKey { get; set; }//是否是主键
// public bool IsIdentity { get; set; }//是否自增
// public string MappingKeys { get; set; }//映射key
// public string ColumnDescription { get; set; }//列描述
// public int Length { get; set; }//长度
// public bool IsNullable { get; set; }//是否为空
// public string OldColumnName { get; set; }//旧的列名
// public string ColumnDataType { get; set; }//列类型，自定义
// public int DecimalDigits { get; set; }//dicimal精度
// public string OracleSequenceName { get; set; }//Oracle序列名
// public bool IsOnlyIgnoreInsert { get; set; }//是否仅对添加忽略
// public bool IsEnableUpdateVersionValidation { get; set; }
// }2、增删改查等常见示例
/**
*┌──────────────────────────────────────────────────────────────┐
*│ 描 述：SqlSugar使用示例
*│ 作 者：执笔小白
*│ 版 本：1.0
*│ 创建时间：2022-12-2 15:40:56
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│ 命名空间: Repository._Base
*│ 类 名：UseSqlSugarDemo
*└──────────────────────────────────────────────────────────────┘
*/
using Repository._Base;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
namespace Repository
{
/// <summary>
/// SqlSugar使用Demo
/// </summary>
public class UseSqlSugarDemo : PracticeContext
{
#region sql
/// <summary>
/// 执行sql（增删改）
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool SqlExecuteCommand(string sql)
{
if (string.IsNullOrEmpty(sql)) return true;
int count = Db.Ado.ExecuteCommand(sql);
return count > 0;
}
#endregion sql
#region 查询
/// <summary>
/// 查询所有实体-返回List示例
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <returns></returns>
public List<T> Queryable<T>(string tableName = "") where T : class, new()
{
var sugar = Db.Queryable<T>();
return string.IsNullOrEmpty(tableName) ? sugar.ToList() : sugar.AS(tableName).ToList();
}
/// <summary>
/// 查询所有实体-返回DataTable示例
/// 有报错 DataTable already belongs to another DataSet，已重新声明
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <returns></returns>
public DataTable QueryableToDataTable<T>(string tableName = "")
{
var sugar = Db.Queryable<T>();
return string.IsNullOrEmpty(tableName) ? sugar.ToDataTable().Copy() : sugar.AS(tableName).ToDataTable().Copy(); // 原datatable有自己的数据集，需要重新声明一个
}
/// <summary>
/// 根据表达式查询-List
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="expression">Where条件</param>
/// <param name="tableName">别名表</param>
/// <returns></returns>
public List<T> Queryable<T>(Expression<Func<T, bool>> expression, string tableName = "") where T : class, new()
{
var sugar = Db.Queryable<T>().Where(expression);
return string.IsNullOrEmpty(tableName) ? sugar.ToList() : sugar.AS(tableName).ToList();
}
/// <summary>
/// 根据表达式查询-DataTable
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="expression">Where条件</param>
/// <param name="tableName">别名表</param>
/// <returns></returns>
public DataTable Queryable1<T>(Expression<Func<T, bool>> expression, string tableName = "")
{
var sugar = Db.Queryable<T>().Where(expression);
return string.IsNullOrEmpty(tableName) ? sugar.ToDataTable().Copy() : sugar.AS(tableName).ToDataTable().Copy(); // 原datatable有自己的数据集，需要重新声明一个
}
/// <summary>
/// SQL语句查询-List示例
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="sql">SQL执行语句</param>
/// <returns></returns>
public List<T> SqlQueryable<T>(string sql) where T : class, new()
{
return Db.SqlQueryable<T>(sql).ToList();
}
/// <summary>
/// SQL语句查询-DataTable示例
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable SqlQueryable(string sql)
{
return Db.SqlQueryable<DataTable>(sql).ToDataTable().Copy();
}
#endregion
#region 新增
/// <summary>
/// 新增示例
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="insertObj">数据</param>
/// <param name="tableName">别名表</param>
/// <returns>状态</returns>
public bool Insert<T>(T insertObj, string tableName = "") where T : class, new()
{
if (insertObj == null) return true;
IInsertable<T> sugar = Db.Insertable(insertObj);
return string.IsNullOrEmpty(tableName) ? sugar.ExecuteCommandIdentityIntoEntity() : sugar.AS(tableName).ExecuteCommandIdentityIntoEntity();
}
/// <summary>
/// 批量新增示例
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="insertObjs">数据</param>
/// <param name="tableName">别名表</param>
/// <returns>状态</returns>
public bool Insert<T>(List<T> insertObjs, string tableName = "") where T : class, new()
{
if (!insertObjs.Any()) return true;
do
{
List<T> tempParam = insertObjs.Take(500).ToList(); // 限制最多500条执行
IInsertable<T> sugar = Db.Insertable(tempParam);
bool isSuc = string.IsNullOrEmpty(tableName) ? sugar.ExecuteCommandIdentityIntoEntity() : sugar.AS(tableName).ExecuteCommandIdentityIntoEntity();
insertObjs.RemoveRange(0, tempParam.Count);
}
while (insertObjs.Count > 0);
return true;
}
#endregion 新增
#region 修改
/// <summary>
/// 根据主键修改示例
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="UpdateObj">数据</param>
/// <param name="tableName">别名表</param>
/// <returns>状态</returns>
public bool Update<T>(T UpdateObj, string tableName = "") where T : class, new()
{
if (UpdateObj == null) return true;
IUpdateable<T> sugar = Db.Updateable(UpdateObj);
return string.IsNullOrEmpty(tableName) ? sugar.ExecuteCommandHasChange() : sugar.AS(tableName).ExecuteCommandHasChange();
}
/// <summary>
/// 根据主键修改_实体指定列示例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="UpdateObj">数据</param>
/// <param name="columns">需要更新的实体列</param>
/// <param name="tableName">别名表</param>
/// <returns></returns>
public bool Update<T>(T UpdateObj, Expression<Func<T, object>> columns, string tableName = "") where T : class, new()
{
if (UpdateObj == null) return true;
IUpdateable<T> sugar = Db.Updateable(UpdateObj).UpdateColumns(columns);
return string.IsNullOrEmpty(tableName) ? sugar.ExecuteCommandHasChange() : sugar.AS(tableName).ExecuteCommandHasChange();
}
/// <summary>
/// 根据主键批量修改示例
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="UpdateObjs">数据</param>
/// <param name="tableName">别名表</param>
/// <returns>状态</returns>
public bool Update<T>(List<T> UpdateObjs, string tableName = "") where T : class, new()
{
if (!UpdateObjs.Any()) return true;
do
{
List<T> tempParam = UpdateObjs.Take(500).ToList(); // 限制每次最多修改500条
IUpdateable<T> sugar = Db.Updateable(tempParam);
bool isSuc = string.IsNullOrEmpty(tableName) ? sugar.ExecuteCommandHasChange() : sugar.AS(tableName).ExecuteCommandHasChange();
UpdateObjs.RemoveRange(0, tempParam.Count);
}
while (UpdateObjs.Count > 0);
return true;
}
#endregion
#region 删除
/// <summary>
/// 根据主键删除示例
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <typeparam name="PkType">主键类型</typeparam>
/// <param name="primaryKeyValue">主键</param>
/// <param name="tableName">别名表</param>
/// <returns>状态</returns>
public bool Deleteable<T, PkType>(PkType primaryKeyValue, string tableName = "") where T : class, new()
{
IDeleteable<T> sugar = Db.Deleteable<T>().In(primaryKeyValue);
return string.IsNullOrEmpty(tableName) ? sugar.ExecuteCommandHasChange() : sugar.AS(tableName).ExecuteCommandHasChange();
}
/// <summary>
/// 根据表达式删除示例
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="expression">Where条件</param>
/// <param name="tableName">别名表</param>
/// <returns>状态</returns>
public bool Deleteable<T>(Expression<Func<T, bool>> expression, string tableName = "") where T : class, new()
{
IDeleteable<T> sugar = Db.Deleteable<T>().Where(expression);
return string.IsNullOrEmpty(tableName) ? sugar.ExecuteCommandHasChange() : sugar.AS(tableName).ExecuteCommandHasChange();
}
#endregion
#region 事务
/// <summary>
/// 运行事务示例
/// </summary>
/// <returns></returns>
public bool RunTransactionDemo(string sql)
{
try
{
TestTable testTable = new TestTable();
Db.Ado.BeginTran(); // 事务开始
if (!string.IsNullOrEmpty(sql))
{
Db.Ado.ExecuteCommand(sql); // 执行sql
}
Db.Insertable<TestTable>(testTable).ExecuteCommand(); // 增
Db.Deleteable<TestTable>(testTable).ExecuteCommand(); // 删
Db.Updateable<TestTable>(testTable).ExecuteCommand(); // 改
Db.Ado.CommitTran(); // 事务提交
return true;
}
catch (Exception ex)
{
Db.Ado.RollbackTran(); // 事务回滚
// 记录日志
string str = ex.StackTrace ?? string.Empty;
Debug.WriteLine(string.Concat("UI线程异常;异常位置：", str.AsSpan(str.LastIndexOf("\\") + 1, str.Length - str.LastIndexOf("\\") - 1), "；异常信息：", ex.Message));
return false;
}
}
#endregion 事务
#region 表处理
public void TableHandleDome()
{
//Db.DbMaintenance.GetDataBaseList(); // 获取所有库
Db.DbMaintenance.GetTableInfoList(false); // 获取库中的所有表;true为走缓存，false为不走缓存
Db.DbMaintenance.GetViewInfoList(false); // 获取库中的所有视图;true为走缓存，false为不走缓存
Db.DbMaintenance.IsAnyTable("tableName", false); // 判断表是否存在 （IsAny(表名，是否缓存)）
Db.CodeFirst.InitTables(typeof(TestTable)); // 创建表（有实体建表）
Db.DbMaintenance.RenameTable("oldTableName", "newTableName"); // 修改表名
Db.DbMaintenance.DropTable("tableName"); // 删除表
}
#endregion 表处理
}
/// <summary>
/// 示例表
/// SugarTable(表名，表描述，isDisabledDelete:禁止删除列,isCreateTableFieldSort:创建表时对字段进行排序)
/// </summary>
[SugarTable("testtable", "示例表", false, false)]
public class TestTable
{
/// <summary>
/// 主键ID
/// 不空、主键、自增、列名、说明
/// </summary>
[SugarColumn(IsNullable = false, IsPrimaryKey = true,IsIdentity=true,ColumnName = "Id",ColumnDescription = "主键ID")]
public int Id { get; set; }
/// <summary>
///
/// OldColumnName：将Uname列名改为Name
/// IndexGroupNameList：索引
/// </summary>
[SugarColumn(OldColumnName = "UName", ColumnDataType = "nvarchar", Length = 32, IsNullable = false, IndexGroupNameList = new string[] { "index_UName" })]
public string Name { get; set; } = string.Empty;
/// <summary>
///
/// </summary>
[SugarColumn(ColumnDataType = "varchar", Length = 32, IsNullable = false)]
public string Pwd { get; set; } = string.Empty;
/// <summary>
///
/// UniqueGroupNameList：唯一索引
/// </summary>
[SugarColumn(IsNullable = false,UniqueGroupNameList =new string[] { "index_FId" })]
public int FId { get; set; }
/// <summary>
///
/// </summary>
public bool IsDelete { get; set; }
/// <summary>
/// IsIgnore ：ORM不处理该列
/// IsOnlyIgnoreInsert ：插入操作时不处理该列
/// length=5 长度5 decimal(5,2)
/// DecimalDigits=2 ：精度2 decimal(5,2)
/// </summary>ColumnDescription 备注
[SugarColumn(IsIgnore = true, IsOnlyIgnoreInsert=true, Length = 5, DecimalDigits = 2)]
public float dd { get; set; }
}
// SugarColumn特性
// public class SugarColumn : Attribute
// {
// public SugarColumn();
//
// public string ColumnName { get; set; }//列名
// public bool IsIgnore { get; set; }//是否忽略
// public bool IsPrimaryKey { get; set; }//是否是主键
// public bool IsIdentity { get; set; }//是否自增
// public string MappingKeys { get; set; }//映射key
// public string ColumnDescription { get; set; }//列描述
// public int Length { get; set; }//长度
// public bool IsNullable { get; set; }//是否为空
// public string OldColumnName { get; set; }//旧的列名
// public string ColumnDataType { get; set; }//列类型，自定义
// public int DecimalDigits { get; set; }//dicimal精度
// public string OracleSequenceName { get; set; }//Oracle序列名
// public bool IsOnlyIgnoreInsert { get; set; }//是否仅对添加忽略
// public bool IsEnableUpdateVersionValidation { get; set; }
// }
}
本文来自博客园，作者：꧁执笔小白꧂，转载请注明原文链接：https://www.cnblogs.com/qq2806933146xiaobai/p/16945008.html
