一、安装:
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);
}
}
}
}
- 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.
- 218.
- 219.
- 220.
- 221.
- 222.
- 223.
- 224.
- 225.
- 226.
- 227.
- 228.
- 229.
- 230.
- 231.
- 232.
- 233.
- 234.
- 235.
- 236.
- 237.
- 238.
- 239.
- 240.
- 241.
- 242.
- 243.
- 244.
- 245.
- 246.
- 247.
- 248.
- 249.
- 250.
- 251.
- 252.
- 253.
- 254.
- 255.
- 256.
- 257.
- 258.
- 259.
- 260.
- 261.
- 262.
- 263.
- 264.
- 265.
- 266.
- 267.
- 268.
- 269.
- 270.
- 271.
- 272.
- 273.
- 274.
- 275.
- 276.
- 277.
- 278.
- 279.
- 280.
- 281.
- 282.
- 283.
- 284.
- 285.
- 286.
- 287.
- 288.
- 289.
- 290.
- 291.
- 292.
- 293.
- 294.
- 295.
- 296.
- 297.
- 298.
- 299.
- 300.
- 301.
- 302.
- 303.
- 304.
- 305.
- 306.
- 307.
- 308.
- 309.
- 310.
- 311.
- 312.
- 313.
- 314.
- 315.
- 316.
- 317.
- 318.
- 319.
- 320.
- 321.
- 322.
- 323.
- 324.
- 325.
- 326.
- 327.
- 328.
- 329.
- 330.
- 331.
- 332.
- 333.
- 334.
- 335.
- 336.
- 337.
- 338.
- 339.
- 340.
- 341.
- 342.
- 343.
- 344.
- 345.
- 346.
- 347.
- 348.
- 349.
- 350.
- 351.
- 352.
- 353.
- 354.
- 355.
- 356.
- 357.
- 358.
- 359.
- 360.
- 361.
- 362.
- 363.
- 364.
- 365.
- 366.
- 367.
- 368.
- 369.
- 370.
- 371.
- 372.
- 373.
- 374.
- 375.
- 376.
- 377.
- 378.
- 379.
- 380.
- 381.
- 382.
- 383.
- 384.
- 385.
- 386.
- 387.
- 388.
- 389.
- 390.
- 391.
二、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; }
// }
- 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.
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; }
// }
}
- 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.
- 218.
- 219.
- 220.
- 221.
- 222.
- 223.
- 224.
- 225.
- 226.
- 227.
- 228.
- 229.
- 230.
- 231.
- 232.
- 233.
- 234.
- 235.
- 236.
- 237.
- 238.
- 239.
- 240.
- 241.
- 242.
- 243.
- 244.
- 245.
- 246.
- 247.
- 248.
- 249.
- 250.
- 251.
- 252.
- 253.
- 254.
- 255.
- 256.
- 257.
- 258.
- 259.
- 260.
- 261.
- 262.
- 263.
- 264.
- 265.
- 266.
- 267.
- 268.
- 269.
- 270.
- 271.
- 272.
- 273.
- 274.
- 275.
- 276.
- 277.
- 278.
- 279.
- 280.
- 281.
- 282.
- 283.
- 284.
- 285.
- 286.
- 287.
- 288.
- 289.
- 290.
- 291.
- 292.
- 293.
- 294.
- 295.
- 296.
- 297.
- 298.
- 299.
- 300.
- 301.
- 302.
- 303.
- 304.
- 305.
- 306.
- 307.
- 308.
- 309.
- 310.
- 311.
- 312.
- 313.
- 314.
- 315.
- 316.
- 317.
- 318.
- 319.
- 320.
- 321.
- 322.
- 323.
- 324.
- 325.
- 326.
- 327.
- 328.
- 329.
- 330.
- 331.
- 332.
- 333.
- 334.
- 335.
- 336.
- 337.
- 338.
- 339.
- 340.
- 341.
- 342.
- 343.
- 344.
- 345.
- 346.
- 347.
- 348.
- 349.
- 350.
- 351.
- 352.
- 353.
- 354.
- 355.
- 356.
- 357.
- 358.
- 359.
- 360.
作者:꧁执笔小白꧂