本文只做SqlSugar的配置，具体功能还要到实际使用。
1、Nuget安装SqlSugarCore到项目
2、假如我们有个表，TBAction
3、建立对应的类文件，SqlSugar支持DBFirst和CodeFirst方式从表生成类文件，或者从类文件生成表。得到TBAction.cs类文件
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Randy.Fang.Model
{
public class TBAction : IEquatable<TBAction>
{
public TBAction() { }
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public virtual Int32 ActionID { get; set; }
public virtual String ActionNameEN { get; set; }
public virtual String ActionNameCN { get; set; }
public virtual String ActionDesc { get; set; }
public virtual Int32? ActionType { get; set; }
public virtual Boolean? IsDeleted { get; set; }
public virtual DateTime? CreateTime { get; set; }
public virtual DateTime? LastModifyTime { get; set; }
public virtual Int32? Version { get; set; }
//实现比较的方法
public virtual bool Equals(TBAction other)
{
return ActionID.Equals(other.ActionID);
}
}
}
4、在appSettings.json文件中建立数据库配置链接
"ConnectionStrings": {
"Default": {
"ConnectionString": "Server=127.0.0.1;user=usrfordb;password=111111;Initial Catalog=fang;Encrypt=True;TrustServerCertificate=True;"
}
}
5、新建AppSettingsHelper.cs文件用于读取配置文件
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Randy.Fang.Common
{
public class AppSettingsHelper
{
public static IConfiguration Configuration { get; set; }
static AppSettingsHelper()
{
//ReloadOnChange = true 当appsettings.json被修改时重新加载
Configuration = new ConfigurationBuilder()
.Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true })
.Build();
}
}
}
6、建立DBService.cs用于SqlSuger读取数据库，并且会在控制台打印相关代码
DBService.cs
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Randy.Fang.Common
{
/// <summary>
/// 数据库服务类
/// </summary>
public class DBService
{
/// <summary>
/// 数据库连接字符串名称(默认是Default);
/// </summary>
public static string ConnectionStringsName = AppSettingsHelper.Configuration["ConnectionStrings:Default:ConnectionString"];
/// <summary>
/// 获取对象
/// </summary>
/// <returns></returns>
public static SqlSugarClient GetInstance() {
Console.WriteLine("连接字符串：" + ConnectionStringsName);
//创建数据库对象 SqlSugarClient
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = ConnectionStringsName,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true//自动释放
});
//每次Sql执行前事件
db.Aop.OnLogExecuting = (sql, pars) =>
{
var queryString = new KeyValuePair<string, SugarParameter[]>(sql, pars);
if (sql.StartsWith("UPDATE") || sql.StartsWith("INSERT"))
{
Console.ForegroundColor = ConsoleColor.Blue;
Console.WriteLine($"==============新增/修改操作==============");
}
if (sql.StartsWith("DELETE"))
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine($"==============删除操作==============");
}
if (sql.StartsWith("SELECT"))
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"==============查询操作==============");
}
//ToSqlExplain 在标题6中有完整代码
Console.WriteLine(ToSqlExplain.GetSql(queryString));//输出sql
Console.ForegroundColor = ConsoleColor.White;
};
//SQL执行完
db.Aop.OnLogExecuted = (sql, pars) =>
{
//执行时间超过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;
//sql
var exesql = sql;
//参数
var sqlpars = pars;
//db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息
}
};
//SQL报错
db.Aop.OnError = (exp) =>
{
Console.WriteLine(exp.Sql);
//exp.sql 这样可以拿到错误SQL
};
return db;
}
}
}
拼接Sql语句的代码文件ToSqlExplain.cs
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Randy.Fang.Common
{
/// <summary>
/// 拼接出完整的Sql，方便查看
/// </summary>
public class ToSqlExplain
{
public static string GetSql(KeyValuePair<string, SugarParameter[]> queryString)
{
var sql = queryString.Key;//sql语句
var par = queryString.Value;//参数
//字符串替换MethodConst1x会替换掉MethodConst1所有要从后往前替换,不能用foreach,后续可以优化
for (int i = par.Length - 1; i >= 0; i--)
{
if (par[i].ParameterName.StartsWith("@") && par[i].ParameterName.Contains("UnionAll"))
{
sql = sql.Replace(par[i].ParameterName, par[i].Value.ToString());
}
}
for (int i = par.Length - 1; i >= 0; i--)
{
if (par[i].ParameterName.StartsWith("@Method"))
{
sql = sql.Replace(par[i].ParameterName, "'" + par[i].Value.ToString() + "'");
}
}
for (int i = par.Length - 1; i >= 0; i--)
{
if (par[i].ParameterName.StartsWith("@Const"))
{
sql = sql.Replace(par[i].ParameterName, par[i].Value.ToString());
}
}
for (int i = par.Length - 1; i >= 0; i--)
{
if (par[i].ParameterName.StartsWith("@"))
{
//值拼接单引号 拿出来的sql不会报错
sql = sql.Replace(par[i].ParameterName, "'" + Convert.ToString(par[i].Value) + "'");
}
}
return sql;
}
}
}
7、我们在接口文件中建立一个测试接口
/// <summary>
/// 系统登录控制器
/// </summary>
[Route("api/v8/[controller]")]
[ApiController]
[ApiGroup(ApiGroup.Auth)]
public class IndexController
{
/// <summary>
/// 获取菜单信息
/// </summary>
/// <param name="category">菜单类型</param>
/// <returns></returns>
[HttpGet("Menu")]
public ApiResult Menu(string category)
{
var result = new ApiResult();
try
{
result.data = IndexService.GetMenuByCategory(category);
}
catch (Exception ex)
{
result.code = ApiConstant.QUERY_ERROR_CODE;
result.msg = ex.Message;
}
return result;
}
}
ApiResult是我自定义的返回类型。
相关的IndexService代码如下
/// <summary>
/// 首页服务类
/// </summary>
public static class IndexService
{
/// <summary>
/// 根据菜单分类返回菜单数据
/// </summary>
/// <param name="strCategory"></param>
/// <returns></returns>
public static List<TBAction> GetMenuByCategory(string strCategory)
{
return DBService.GetInstance()
.Queryable<TBAction>()
.Where(p => p.IsDeleted == false).ToList(); ;
}
}
8、我们使用swagger或者postman调用试一下
相应的console会打印出相关日志
（本文完）
标签：par,Console,System,管理软件,ORM,sql,using,public,SqlSugar From： https://www.cnblogs.com/randytech/p/16641919.html