CoreDBHelper 1.0.8

.NET 使用正则表达式，xml解析，封装 Dapper 让SQL从代码迁移到xml文件上，并实现简单的逻辑处理

使用方法

1.配置连接字符串

.NET 5 引用 CoreDBHelper.dll 在appsettings.json中增加 "ConnectionStrings": { "test_mysql": "server=127.0.0.1;userid=;pwd=;port=3306;database=****;sslmode=none;" } 然后在Startup.cs的ConfigureServices中通过代码附加连接字符串 SQLHelperFactory.Instance.ConnectionStringsDic["test_mysql"] = Configuration.GetConnectionString("test_mysql");

2.配置SQL语句 在项目上增加文件夹SqlConfig,增加DemoSql.xml文件

xml内容如下：

<?xml version="1.0" encoding="utf-8" ?> <SqlSetting> <Data name="SelectTable"> <SqlDefinition type="MySql" ConnStringName="test_mysql"> <SqlCommand> <![CDATA[ SELECT * FROM table where age = @@age@@ <%= name like CONCAT('%',@@name@@,'%') %> <R%= @@startIndex@@, @@selectCount@@ %R> ]]> </SqlCommand> </SqlDefinition> </Data> </SqlSetting>

using DBHelper.SQLHelper; using System; using System.Collections.Generic;

namespace CoreDBDemo { class Program { static void Main(string[] args) { Dictionary<string, object> paramDic = new Dictionary<string, object>(); paramDic["age"] = 18; paramDic["name"] = "admin"; paramDic["startIndex"] = 0; paramDic["selectCount"] = 10; var list = SQLHelperFactory.Instance.QueryForList("SelectTable", paramDic); Console.Read(); } } } // 此时生成的SQL为： SELECT * FROM table where age = ?age name like CONCAT('%',?name,'%') 0, 10

using DBHelper.SQLHelper; using System; using System.Collections.Generic;

namespace CoreDBDemo { class Program { static void Main(string[] args) { Dictionary<string, object> paramDic = new Dictionary<string, object>(); paramDic["age"] = 18; paramDic["name"] = "admin"; paramDic["startIndex"] = 0; //paramDic["selectCount"] = 10; var list = SQLHelperFactory.Instance.QueryForList("SelectTable", paramDic); Console.Read(); } } } // 此时生成的SQL为： SELECT * FROM table where age = ?age name like CONCAT('%',?name,'%')

using DBHelper.SQLHelper; using System; using System.Collections.Generic;

namespace CoreDBDemo { class Program { static void Main(string[] args) { Dictionary<string, object> paramDic = new Dictionary<string, object>(); paramDic["age"] = 18; //paramDic["name"] = "admin"; paramDic["startIndex"] = 0; //paramDic["selectCount"] = 10; var list = SQLHelperFactory.Instance.QueryForList("SelectTable", paramDic); Console.Read(); } } } // 此时生成的SQL为： SELECT * FROM table where age = ?age

4.接口说明

// 返回影响行数 public int ExecuteNonQuery(string sqlKey, Dictionary<string, object> paramDic, bool isUseTrans = false, int maxretry = MaxRetry)；

// 返回一条数据 public IDataReader ExecuteReader(string sqlKey, Dictionary<string, object> paramDic, bool isUseTrans = false, int maxretry = MaxRetry)

// 返回第一行第一列 public object ExecuteScalar(string sqlKey, Dictionary<string, object> paramDic, bool isUseTrans = false, int maxretry = MaxRetry)

// 返回第一行第一列 public T ExecuteScalarByT(string sqlKey, Dictionary<string, object> paramDic, bool isUseTrans = false, int maxretry = MaxRetry)

// 返回IEnumerable public List QueryForList(string sqlKey, Dictionary<string, object> paramDic, bool isUseTrans = false, int maxretry = MaxRetry)

// 返回IEnumerable public List QueryForListByT(string sqlKey, Dictionary<string, object> paramDic, bool isUseTrans = false, int maxretry = MaxRetry)

// 通过sql配置控制返回的类型，调用 QueryForListByT public List QueryForLisByAssembly(string sqlKey, Dictionary<string, object> paramDic, bool isUseTrans = false, int maxretry = MaxRetry)

// 返回dynamic public dynamic QueryForObject(string sqlKey, Dictionary<string, object> paramDic, bool isUseTrans = false, int maxretry = MaxRetry)

// 返回T public T QueryForObjectByT(string sqlKey, Dictionary<string, object> paramDic, bool isUseTrans = false, int maxretry = MaxRetry)

// 返回结果集和数量 专为分页功能而准备 数据集的sql在前面，返回数量的在后面 public IEnumerable QueryMultipleByPage(string sqlKey, Dictionary<string, object> paramDic, out int total, bool isUseTrans = false, int maxretry = MaxRetry)

// 返回多个结果集 public IEnumerable QueryMultiple<TFirst, TSecond, TReturn>(string sqlKey, Dictionary<string, object> paramDic, Func<IEnumerable, IEnumerable, IEnumerable> func, bool isUseTrans = false, int maxretry = MaxRetry)

具体参考 CoreDBDemo