SqlSugarDbContext.cs
6.16 KB
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
using Hh.Mes.Common.log;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using System.Configuration;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Hh.Mes.POJO.EnumEntitys;
using NPOI.SS.Formula.Functions;
namespace HH.Data.Excel.SqlHelp
{
public class SqlSugarDbContext
{
private readonly ConnectionConfig _connectionConfig;
public readonly SqlSugarClient _db;
public SqlSugarDbContext(string connectionString)
{
_connectionConfig = new ConnectionConfig()
{
//数据库类型
DbType = DbType.SqlServer,
//从特性读取主键自增信息
InitKeyType = InitKeyType.Attribute,
//自动释放和关闭数据库连接，如果有事务事务结束时关闭，否则每次操作后关闭
IsAutoCloseConnection = true,
//连接符字串
ConnectionString = connectionString,
//增加对C#自带的实体特性支持，比如实体的[Table]特性，字段的[Key]特性
ConfigureExternalServices = SetExternalServices(),
MoreSettings = new ConnMoreSettings()
{
IsWithNoLockQuery = true//全局 With(nolock)
},
};
_db = new SqlSugarClient(_connectionConfig);
// 启用日志记录
_db.Aop.OnError = (exp) =>
{
if (exp.Message.IndexOf("连接数据库过程中发生错误") > -1) return;
StringBuilder sqlString = new StringBuilder();
StringBuilder parameterString = new StringBuilder();
sqlString.Append(exp.Sql);
SugarParameter[]? sugarParameters = exp.Parametres as SugarParameter[];
if (sugarParameters != null && sugarParameters.Length > 0)
{
//参数从长到短来循环，这样替换sql中的参数的时候，避免@Parame11被@Parame给替换的情况
foreach (var item in sugarParameters.OrderByDescending(t => t.ParameterName.Length))
{
if (item.DbType.ToString().Contains("String") || item.DbType.ToString().Contains("Date") ||
item.DbType == System.Data.DbType.Guid || item.DbType == System.Data.DbType.Time ||
item.DbType == System.Data.DbType.Object || item.DbType == System.Data.DbType.Xml)
{
sqlString.Replace(item.ParameterName, "'" + item.Value.ToString() + "'");
parameterString.Append(item.ParameterName + " = '" + item.Value + "',");
}
else
{
sqlString.Replace(item.ParameterName, item.Value.ToString());
parameterString.Append(item.ParameterName + " = " + item.Value + ",");
}
}
//删除逗号
parameterString.Remove(parameterString.Length - 1, 1);
}
var errorMsg = $"【SqlSugar】执行SQL语句【{sqlString}】，参数【{parameterString}】,发生错误【{exp.Message}】";
Log4NetHelper.Instance.Error(errorMsg);
};
}
/// <summary>
/// 设置自定义特性（增加对C#自带的实体特性支持，比如实体的[Table]特性，字段的[Key]特性等）
/// </summary>
/// <returns></returns>
private static ConfigureExternalServices SetExternalServices()
{
return new ConfigureExternalServices()
{
//设置实体的SqlSugar特性，从而把C#自带的[Table]特性变成SqlSugar特性
EntityNameService = (type, entity) =>
{
var attributes = type.GetCustomAttributes(true);
if (attributes.Any(it => it is TableAttribute))
{
entity.DbTableName = ((TableAttribute)attributes.First(it => it is TableAttribute)).Name;
}
},
//设置字段的SqlSugar特性，从而把C#自带的字段特性变成SqlSugar特性
EntityService = (property, column) =>
{
//获取所有特性
var attributes = property.GetCustomAttributes(true);//get all attributes
// 如果特性含有字段的[Key]特性，就设置字段SqlSugar的IsPrimaryKey特性
if (attributes.Any(it => it is KeyAttribute))
{
column.IsPrimarykey = true;
}
// 如果特性含有字段的[NotMapped]特性，就设置字段SqlSugar的IsIgnore特性
if (attributes.Any(it => it is NotMappedAttribute))
{
column.IsIgnore = true;
}
// 如果特性含有字段的[Column]特性，就设置字段SqlSugar的DbColumnName特性
if (attributes.Any(it => it is ColumnAttribute))
{
column.DbColumnName = ((ColumnAttribute)attributes.First(it => it is ColumnAttribute)).Name;
}
}
};
}
public List<T> QueryableList<T>(Expression<Func<T, bool>>? predicate = null) where T : class, new()
{
try
{
if (predicate == null)
{
return _db.Queryable<T>().ToList();
}
else
{
return _db.Queryable<T>().Where(predicate).ToList();
}
}
catch (Exception ex)
{
Log4NetHelper.Instance.Error("SqlSugarDBContext QueryableList: " + ex.ToString());
throw;
}
}
}
}