using Newtonsoft.Json;
|
using NongFuWebApi.Models;
|
using SqlSugar;
|
using System;
|
using System.Collections.Generic;
|
using System.Configuration;
|
using System.Linq;
|
using System.Linq.Expressions;
|
|
namespace NongFuWebApi.Services
|
{
|
//https://www.donet5.com/Home/Doc
|
public class SqlHelper<T> where T : class, new()
|
{
|
public bool ExecuteSql(string sql) {
|
try {
|
var code = GetInstance().Ado.ExecuteCommand(sql);
|
return code > 1;
|
}
|
catch (Exception ex) {
|
LogHelper.Error(ex.Message,ex);
|
return false;
|
}
|
}
|
|
public bool Update(T model, string[] cols) {
|
var db = GetInstance();
|
return db.Updateable<T>(model).UpdateColumns(cols).ExecuteCommand() > 0;
|
}
|
public List<T> GetList(Expression<Func<T, bool>> where = null) {
|
var db = GetInstance();
|
if (where == null) {
|
return db.Queryable<T>().ToList();
|
}
|
else {
|
return db.Queryable<T>().Where(where).ToList();
|
}
|
}
|
public T Get(Expression<Func<T, bool>> where, Expression<Func<T, object>> order, bool asc = false) {
|
SqlSugarClient db = GetInstance();
|
if (order == null) {
|
return db.Queryable<T>().Where(where).Single();
|
}
|
else {
|
return db.Queryable<T>().Where(where).OrderBy(order, asc ? OrderByType.Asc : OrderByType.Desc).First();
|
}
|
}
|
public T Get(Expression<Func<T, bool>> where, Expression<Func<T, object>> orderBy = null) {
|
try {
|
var db = GetInstance();
|
T model = null;
|
if (orderBy != null) {
|
model = db.Queryable<T>().Where(where).OrderBy(orderBy, OrderByType.Desc).First();//查询表的所有
|
}
|
else {
|
model = db.Queryable<T>().Where(where).First();
|
}
|
return model;
|
}
|
catch (Exception ex) {
|
Console.WriteLine(ex.Message);
|
return default(T);
|
}
|
}
|
public bool Update(T model) {
|
var db = GetInstance();
|
return db.Updateable<T>(model).ExecuteCommand() > 0;
|
}
|
public bool Insert(T model) {
|
try {
|
var db = GetInstance();
|
return db.Insertable<T>(model).ExecuteCommand() > 0;
|
}
|
catch (Exception ex) {
|
LogHelper.Error(ex.Message,ex);
|
return false;
|
}
|
}
|
|
//删除指定条件数据
|
public bool Deleteable(Expression<Func<T, bool>> where) {
|
try {
|
var db = GetInstance();
|
return db.Deleteable<T>().Where(where).ExecuteCommand() > 0;
|
}
|
catch (Exception ex) {
|
LogHelper.Error(ex.Message,ex);
|
return false;
|
}
|
}
|
|
public bool CreateTable()
|
{
|
bool result = false;
|
var db = GetInstance();
|
//定义SQL语句
|
//string sql = "create table student" + "(num varchar(50),name varchar(255),age int)";
|
try
|
{
|
if (db != null)
|
{
|
// 创建数据库
|
// db.Context.DbMaintenance.CreateDatabase();
|
// 创建表
|
db.Context.CodeFirst.InitTables(
|
typeof(T)
|
);
|
//Console.WriteLine("数据表创建成功。");
|
}
|
else LogHelper.Info($"数据表创建失败:未连接上指定数据库。", "DataBaseLog");
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Info($"数据表创建失败:{ex.Message.ToString()}", "DataBaseLog");
|
}
|
//finally
|
//{
|
// //db.Close();//对数据库操作完成后，需要关闭数据库，释放内存
|
//}
|
return result;
|
}
|
|
//创建SqlSugarClient
|
//DBName--指定数据库名 FuncCode--指定功能名 两者同时只允许一个有值
|
public SqlSugarClient GetInstance(string DBName = "",string FuncCode = "")
|
{
|
LogHelper.Info($"数据库连接开始:DBName:{DBName},FuncCode:{FuncCode}", "DataBaseLog");
|
//创建数据库对象taskType
|
string connectionInfo = ConfigurationManager.AppSettings["MSSQL"].ToString();
|
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() {
|
ConnectionString = connectionInfo,
|
DbType = DbType.SqlServer,
|
IsAutoCloseConnection = true,
|
InitKeyType = InitKeyType.Attribute//从特性读取主键自增信息
|
});
|
//db.MappingTables.Add("","");
|
|
//监控所有超过1秒的Sql
|
db.Aop.OnLogExecuted = (sql, p) => {
|
//Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
|
//执行时间超过1秒
|
if (db.Ado.SqlExecutionTime.TotalSeconds > 1) {
|
//代码CS文件名
|
var fileName = db.Ado.SqlStackTrace.FirstFileName;
|
//代码行数
|
var fileLine = db.Ado.SqlStackTrace.FirstLine;
|
//方法名
|
var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
|
//db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息
|
}
|
//相当于EF的 PrintToMiniProfiler
|
};
|
|
db.Aop.DataExecuted = (value, entity) =>
|
{
|
//只有行级事件
|
entity.EntityColumnInfos.ToList().ForEach(a =>
|
{
|
var pvalue = entity.GetValue(a.PropertyName);
|
if (pvalue != null && pvalue.GetType() == typeof(String))
|
{
|
entity.SetValue(a.PropertyName, pvalue.ToString().Trim());
|
}
|
});
|
};
|
LogHelper.Info($"数据库连接结束:DBName:{DBName},FuncCode:{FuncCode}", "DataBaseLog");
|
|
//据转换 (ExecuteCommand才会拦截，查询不行)
|
//db.Aop.DataExecuting = (value, entity) => {
|
// //var val=entity.EntityColumnInfo
|
// Console.WriteLine(entity.EntityName);
|
//};
|
|
return db;
|
}
|
|
#region Sqlserver连接异常总结
|
|
//异常1：Could not load file or assembly 'System.Data.SqlClient, Version=4.6.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. 系统找不到指定的文件。
|
//异常1总结：因为本框架使用 .net Core 3.1版本，不支持 System.Data.SqlClient 4.6.1 版本，因此调用方法时出现异常
|
//异常1解决方案：使用 Nuget 添加 System.Data.SqlClient 4.8.0 版本即可
|
|
//异常2：中文提示 : 连接数据库过程中发生错误，检查服务器是否正常连接字符串是否正确，错误信息：用户 'sa' 登录失败。DbType="SqlServer";ConfigId="".
|
// English Message : Connection open error.用户 'sa' 登录失败。DbType="SqlServer";ConfigId=""
|
//异常2总结：获取配置文件数据库连接信息BUG，已修复
|
|
#endregion
|
}
|
|
}
|
