前言
操作数据库时手动拼装sql语句相当麻烦，而且容易拼错。
SQL构建器实际上就是封装的一个可以拼接sql语句的类，使用SQL构建器，可以通过创建一个实例来调用方法生成SQL语句。
本人用过Laravel框架（一个PHP框架），感觉它的sql构建器用着比较舒服，所以就仿着Laravel中sql构建器的书写方式，实现了一个C#版本。
封装的sql构建器类实现了对数据库的增删改查、事务，用到了泛型类、泛型方法、反射、委托、Lambda表达式、泛型类的静态构造方法进行缓存等知识点。
使用方法
插入一条记录
bool b = DB<Company>.CreateInstance.Insert(new Dictionary<string, object>()
{
{ "Name", "sss" },
{ "CreateTime", null },
{ "LastModifierId",null }
});
插入一条记录，并返回新插入记录的ID
long l = DB<Company>.CreateInstance.InsertGetId(new Dictionary<string, object>()
{
{ "Name", "xxxxxx" },
{ "CreateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") },
{ "LastModifierId",66 }
});
根据ID删除一条记录
bool b = DB<Company>.CreateInstance.Delete(75);
删除记录
//指定一个where条件
bool b = DB<Company>.CreateInstance.Where("Id", ">", "2").Delete();
//指定多个where条件，条件之间用AND连接
bool b = DB<Company>.CreateInstance.AndWhere(new string[,]{
{"Id", ">", "5"},
{"Id", "<", "10"}
}).Delete();
//指定多个where条件，条件之间用OR连接
bool b = DB<Company>.CreateInstance.OrWhere(new string[,]{
{"Id", "=", "5"},
{"Name", "=", "aaa"}
}).Delete();
修改记录
bool b = DB<Company>.CreateInstance.Where("id", ">", "14").Update(new Dictionary<string, object>()
{
{ "Name", "汇邦123" },
{ "CreateTime", "2022-02-22" },
{ "LastModifierId","21" }
});
查询多条记录
List<Company> l1 = DB<Company>.Create().Field("Id", "Name").Where("Id", ">", "1").OrderBy("Id desc").Limit(5).Get();
List<Company> l2 = DB<Company>.Create().Field("Id", "Name").AndWhere(new string[,]{
{"Id", ">", "5"},
{"Id", "<", "10"}
}).OrderBy("Id desc").Limit(5).Get();
根据id，查询一条记录
Company company = DB<Company>.CreateInstance.Find(13);
查询一条记录
Company company = DB<Company>.Create().Field("Id", "Name").Where("Id", ">", "1").OrderBy("Id desc").First();
Company company = DB<Company>.Create().Field("Id", "Name").AndWhere(new string[,]{
{"Id", ">", "5"},
{"Id", "<", "10"}
}).OrderBy("Id desc").First();
查询一条记录的单个字段值
var id = DB<Company>.CreateInstance.Where("Id", ">", "1").OrderBy("Id desc").Value<int?>("LastModifierId");
var id = DB<Company>.CreateInstance.AndWhere(new string[,]{
{"Id", ">", "5"},
{"Id", "<", "10"}
}).OrderBy("Id desc").Value<int?>("LastModifierId");
查询符合条件的记录数
long l = DB<Company>.CreateInstance.Where("Id", ">", "1").Count();
long l = DB<Company>.CreateInstance.AndWhere(new string[,]{
{"Id", ">", "5"},
{"Id", "<", "10"}
}).Count();
根据传入的sql语句查询（无参数）
DataTable dt = DB_STATIC.Select("select * from company");
根据传入的sql语句查询（有参数）
string sql = "select * from company where id=@Id and name=@Name";
DataTable dt = DB_STATIC.Select(sql, new Dictionary<string, object>()
{
{ "Id", 1 },
{ "Name", "汇邦123" }
});
事务
bool b = DB_STATIC.Transaction(() =>
{
bool b1 = DB<Company>.CreateInstance.Where("Id", "=", "1").Update(new Dictionary<string, object>()
{
{ "Name", "ff" },
{ "CreateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") },
{ "LastModifierId",66 }
});
long l = DB<Company>.CreateInstance.InsertGetId(new Dictionary<string, object>()
{
{ "Name", "xxxxxx" },
{ "CreateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") },
{ "LastModifierId",66 }
});
bool b2 = DB<Company>.CreateInstance.Delete(75);
});
DAL层
DB.cs
using DAL.Mapping;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
namespace DAL
{
public class DB<T> where T : BaseModel, new()
{
// 非静态字段
private string field = "";
private string where = "";
private string orderBy = "";
private string limit = "";
private string[] fieldArr;
private List<SQLiteParameter> sqlParameterList = new List<SQLiteParameter>();
// 静态字段
private static string tableName = null;
public static DB<T> CreateInstance
{
get
{
return new DB<T>();
}
}
public static DB<T> Create()
{
return new DB<T>();
}
//public DB<T> Table(string tableName)
//{
// this.tableName = tableName;
// return this;
//}
static DB()
{
Type type = typeof(T);
DB<T>.tableName = type.GetMappingName<Type>();
}
private DB()
{
}
public DB<T> AndWhere(string[,] whereArr)
{
this.WhereAndOr("AND", whereArr);
return this;
}
public DB<T> OrWhere(string[,] whereArr)
{
this.WhereAndOr("OR", whereArr);
return this;
}
private void WhereAndOr(string strAndOr, string[,] whereArr)
{
if (!string.IsNullOrEmpty(this.where))
{
throw new Exception("Where()、AndWhere()、OrWhere()方法只能调用其中一个，且只能调用一次");
}
List<string> listWhere = new List<string>();
for (int i = 0; i < whereArr.GetLength(0); i++)
{
listWhere.Add($"\"{whereArr[i, 0]}\"{whereArr[i, 1]}@{whereArr[i, 0]}_Y{i}");
sqlParameterList.Add(new SQLiteParameter($"@{whereArr[i, 0]}_Y{i}", (object)whereArr[i, 2] ?? DBNull.Value));
}
this.where = string.Join($" {strAndOr} ", listWhere);
}
public DB<T> Where(params string[] whereArr)
{
if (!string.IsNullOrEmpty(this.where))
{
throw new Exception("Where()、AndWhere()、OrWhere()方法只能调用其中一个，且只能调用一次");
}
this.where = $"\"{whereArr[0]}\"{whereArr[1]}@{whereArr[0]}_Y";
sqlParameterList.Add(new SQLiteParameter($"@{whereArr[0]}_Y", (object)whereArr[2] ?? DBNull.Value));
return this;
}
public DB<T> OrderBy(string orderBy)
{
this.orderBy = orderBy;
return this;
}
public DB<T> Limit(object limit)
{
this.limit = limit.ToString();
return this;
}
public DB<T> Field(params string[] fieldArr)
{
this.fieldArr = fieldArr;
this.field = string.Join(",", fieldArr.Select(item => $"\"{item}\""));
return this;
}
/// <summary>
/// 插入一条数据
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public bool Insert(Dictionary<string, object> dic)
{
List<SQLiteParameter> sqlParameterList;
string sql;
this.InsertBuildSql(dic, out sqlParameterList, out sql);
if (DB_STATIC.cmd == null)
{
return ExecuteSql<bool>(sql, sqlParameterList.ToArray(), command =>
{
int iResult = command.ExecuteNonQuery();
return iResult == 1;
});
}
else
{
DB_STATIC.cmd.CommandText = sql;
DB_STATIC.cmd.Parameters.Clear();
DB_STATIC.cmd.Parameters.AddRange(sqlParameterList.ToArray());
int iResult = DB_STATIC.cmd.ExecuteNonQuery();
return iResult == 1;
}
}
private void InsertBuildSql(Dictionary<string, object> dic, out List<SQLiteParameter> sqlParameterList, out string sql)
{
sqlParameterList = new List<SQLiteParameter>();
foreach (var item in dic)
{
// b ? x : y
//如果 X 和 Y 的类型相同，则此类型为该条件表达式的类型。
//否则，如果存在从 X 到 Y 的隐式转换，但不存在从 Y 到 X 的隐式转换，则 Y 为条件表达式的类型。
//否则，如果存在从 Y 到 X 的隐式转换，但不存在从 X 到 Y 的隐式转换，则 X 为条件表达式的类型。
//否则，无法确定条件表达式的类型
sqlParameterList.Add(new SQLiteParameter($"@{item.Key}", item.Value ?? DBNull.Value));
}
string fieldString = string.Join(",", dic.Keys.Select(p => $"\"{p}\""));
string valueString = string.Join(",", dic.Keys.Select(p => $"@{p}"));
sql = $"INSERT INTO \"{DB<T>.tableName}\" ({fieldString}) VALUES ({valueString})";
}
/// <summary>
/// 插入一条数据并返回id
/// </summary>
/// <param name="dic"></param>
/// <returns>新插入数据的id</returns>
public long InsertGetId(Dictionary<string, object> dic)
{
List<SQLiteParameter> sqlParameterList;
string sql;
this.InsertBuildSql(dic, out sqlParameterList, out sql);
sql += $";SELECT MAX(ROWID) FROM \"{DB<T>.tableName}\"";
if (DB_STATIC.cmd == null)
{
return ExecuteSql<long>(sql, sqlParameterList.ToArray(), command =>
{
long iResult = (long)command.ExecuteScalar();
return iResult;
});
}
else
{
DB_STATIC.cmd.CommandText = sql;
DB_STATIC.cmd.Parameters.Clear();
DB_STATIC.cmd.Parameters.AddRange(sqlParameterList.ToArray());
long iResult = (long)DB_STATIC.cmd.ExecuteScalar();
return iResult;
}
}
/// <summary>
/// 删除一条数据（根据id）
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool Delete<X>(X id)
{
if (!string.IsNullOrEmpty(this.where))
{
throw new Exception("根据Id删除时，不需要指定Where条件");
}
this.Where("Id", "=", Convert.ToInt64(id).ToString());
return Delete();
}
/// <summary>
/// 删除数据
/// </summary>
/// <returns></returns>
public bool Delete()
{
if (string.IsNullOrEmpty(this.where))
{
throw new Exception("执行Delete操作必须有Where条件");
}
string sql = $"DELETE FROM \"{DB<T>.tableName}\"";
sql += $" WHERE {this.where}";
if (DB_STATIC.cmd == null)
{
return ExecuteSql<bool>(sql, sqlParameterList.ToArray(), command =>
{
int iResult = command.ExecuteNonQuery();
return iResult > 0;
});
}
else
{
DB_STATIC.cmd.CommandText = sql;
DB_STATIC.cmd.Parameters.Clear();
DB_STATIC.cmd.Parameters.AddRange(sqlParameterList.ToArray());
int iResult = DB_STATIC.cmd.ExecuteNonQuery();
return iResult > 0;
}
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public bool Update(Dictionary<string, object> dic)
{
if (string.IsNullOrEmpty(this.where))
{
throw new Exception("执行Update操作必须有Where条件");
}
foreach (var item in dic)
{
sqlParameterList.Add(new SQLiteParameter($"@{item.Key}_X", item.Value ?? DBNull.Value));
}
string keyValString = string.Join(",", dic.Keys.Select(p => $"\"{p}\"=@{p}_X"));
string sql = $"UPDATE \"{DB<T>.tableName}\" SET {keyValString}";
sql += $" WHERE {this.where}";
if (DB_STATIC.cmd == null)
{
return ExecuteSql<bool>(sql, sqlParameterList.ToArray(), command =>
{
//经测试发现：在sqlite中update操作就算没做任何修改也会返回受影响的行数（不会返回0）;mysql则不是
int iResult = command.ExecuteNonQuery();
return iResult > 0;
});
}
else
{
DB_STATIC.cmd.CommandText = sql;
DB_STATIC.cmd.Parameters.Clear();
DB_STATIC.cmd.Parameters.AddRange(sqlParameterList.ToArray());
int iResult = DB_STATIC.cmd.ExecuteNonQuery();
return iResult > 0;
}
}
/// <summary>
/// 查询多条数据
/// </summary>
/// <returns></returns>
public List<T> Get()
{
Type type = typeof(T);
if (string.IsNullOrEmpty(this.field))
{
this.field = string.Join(",", type.GetProperties().Select(prop => $"\"{prop.Name}\""));
this.fieldArr = type.GetProperties().Select(prop => prop.Name).ToArray();
}
string sql = $"SELECT {this.field} FROM \"{DB<T>.tableName}\"";
if (!string.IsNullOrEmpty(this.where))
{
sql += $" WHERE {this.where}";
}
if (!string.IsNullOrEmpty(this.orderBy))
{
sql += $" ORDER BY {this.orderBy}";
}
if (!string.IsNullOrEmpty(this.limit))
{
sql += $" LIMIT {this.limit}";
}
return ExecuteSql<List<T>>(sql, sqlParameterList.ToArray(), command =>
{
SQLiteDataReader reader = command.ExecuteReader();
List<T> list = new List<T>();
//T t = Activator.CreateInstance<T>();
while (reader.Read())
{
T t = new T();
foreach (string propName in this.fieldArr)
{
PropertyInfo prop = type.GetProperty(propName);
prop.SetValue(t, reader[propName] is DBNull ? null : reader[propName]);
}
list.Add(t);
}
reader.Close();
return list;
});
}
/// <summary>
/// 查询一条数据（根据id）
/// </summary>
/// <param name="id">主键Id</param>
/// <returns>实体对象 或 null</returns>
public T Find<X>(X id)
{
Type type = typeof(T);
if (string.IsNullOrEmpty(this.field))
{
this.field = string.Join(",", type.GetProperties().Select(prop => $"\"{prop.Name}\""));
this.fieldArr = type.GetProperties().Select(prop => prop.Name).ToArray();
}
string sql = $"SELECT {this.field} FROM \"{DB<T>.tableName}\" WHERE \"Id\"=@Id";
SQLiteParameter[] sqlParameter = { new SQLiteParameter($"@Id", Convert.ToInt64(id)) };
return ExecuteSql<T>(sql, sqlParameter, command =>
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
//SQLiteDataReader 在使用完成后必须手动释放（using或close），否则会出问题;
if (reader.Read())
{
//T t = Activator.CreateInstance<T>();
T t = new T();
foreach (string propName in this.fieldArr)
{
PropertyInfo prop = type.GetProperty(propName);
prop.SetValue(t, reader[propName] is DBNull ? null : reader[propName]);
}
return t;
}
else
{
return default(T);
}
}
});
}
/// <summary>
/// 查询一条数据
/// </summary>
/// <param name="id">主键Id</param>
/// <returns>实体对象 或 null</returns>
public T First()
{
Type type = typeof(T);
if (string.IsNullOrEmpty(this.field))
{
this.field = string.Join(",", type.GetProperties().Select(prop => $"\"{prop.Name}\""));
this.fieldArr = type.GetProperties().Select(prop => prop.Name).ToArray();
}
string sql = $"SELECT {this.field} FROM \"{DB<T>.tableName}\"";
if (!string.IsNullOrEmpty(this.where))
{
sql += $" WHERE {this.where}";
}
if (!string.IsNullOrEmpty(this.orderBy))
{
sql += $" ORDER BY {this.orderBy}";
}
sql += $" LIMIT 1";
return ExecuteSql<T>(sql, sqlParameterList.ToArray(), command =>
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
//T t = Activator.CreateInstance<T>();
T t = new T();
foreach (string propName in this.fieldArr)
{
PropertyInfo prop = type.GetProperty(propName);
prop.SetValue(t, reader[propName] is DBNull ? null : reader[propName]);
}
return t;
}
else
{
return default(T);
}
}
});
}
/// <summary>
/// 从结果中获取单个值
/// </summary>
/// <typeparam name="S"></typeparam>
/// <param name="oneFieldName"></param>
/// <returns></returns>
public S Value<S>(string oneFieldName)
{
string sql = $"SELECT \"{oneFieldName}\" FROM \"{DB<T>.tableName}\"";
if (!string.IsNullOrEmpty(this.where))
{
sql += $" WHERE {this.where}";
}
if (!string.IsNullOrEmpty(this.orderBy))
{
sql += $" ORDER BY {this.orderBy}";
}
sql += $" LIMIT 1";
return ExecuteSql<S>(sql, sqlParameterList.ToArray(), command =>
{
object obj = command.ExecuteScalar();
if (obj is DBNull || obj == null)
{
return default(S);
}
else
{
return (S)obj;
}
});
}
public long Count()
{
string sql = $"SELECT COUNT(1) FROM \"{DB<T>.tableName}\"";
if (!string.IsNullOrEmpty(this.where))
{
sql += $" WHERE {this.where}";
}
return ExecuteSql<long>(sql, sqlParameterList.ToArray(), command =>
{
return (long)command.ExecuteScalar();
});
}
private H ExecuteSql<H>(string sql, SQLiteParameter[] sqlParameter, Func<SQLiteCommand, H> func)
{
using (SQLiteConnection conn = new SQLiteConnection(DB_STATIC.ConnectionString))
{
SQLiteCommand command = new SQLiteCommand(sql, conn);
command.Parameters.AddRange(sqlParameter);
conn.Open();
return func.Invoke(command);
}
}
}
}
DB_STATIC.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
namespace DAL
{
public static class DB_STATIC
{
// 静态字段
public static SQLiteCommand cmd = null;
public static readonly string ConnectionString = "data source=./DB_test.db;version=3;";
/// <summary>
/// 根据传入的sql语句查询（无参数）
/// </summary>
/// <param name="sqlStr">sql字符串</param>
/// <returns>DataTable类型</returns>
public static DataTable Select(string sqlStr)
{
Dictionary<string, object> dic = new Dictionary<string, object>();
return Select(sqlStr, dic);
}
/// <summary>
/// 根据传入的sql语句查询（有参数）
/// </summary>
/// <param name="sqlStr">sql字符串</param>
/// <param name="dic"></param>
/// <returns>DataTable类型</returns>
public static DataTable Select(string sqlStr, Dictionary<string, object> dic)
{
List<SQLiteParameter> sqlParameterList = new List<SQLiteParameter>();
foreach (var item in dic)
{
sqlParameterList.Add(new SQLiteParameter($"@{item.Key}", item.Value ?? DBNull.Value));
}
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteDataAdapter da = new SQLiteDataAdapter(sqlStr, conn))
{
da.SelectCommand.Parameters.AddRange(sqlParameterList.ToArray());
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
/// <summary>
/// 事务
/// </summary>
/// <returns></returns>
public static bool Transaction(Action action)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
SQLiteTransaction tran = conn.BeginTransaction();//SQLite中，此行执行完会锁库
DB_STATIC.cmd = new SQLiteCommand();
DB_STATIC.cmd.Transaction = tran;
try
{
action.Invoke();
tran.Commit();
return true;
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message);
//throw ex;
tran.Rollback();
return false;
}
finally
{
DB_STATIC.cmd.Dispose();
DB_STATIC.cmd = null;
}
}
}
}
}
BaseModel.cs
using DAL.Mapping;
namespace DAL
{
public class BaseModel
{
[KeyAttribute]
public long Id { get; set; }
}
}
MappingAttributeExtend.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
namespace DAL.Mapping
{
public static class MappingAttributeExtend
{
/// <summary>
/// 把下面两个方法整合为一个泛型方法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="type"></param>
/// <returns></returns>
public static string GetMappingName<T>(this T member) where T : MemberInfo
{
if (member.IsDefined(typeof(AbstractMappingAttribute), true))
{
AbstractMappingAttribute attribute = member.GetCustomAttribute<AbstractMappingAttribute>();
return attribute.GetMappingName();
}
else
{
return member.Name;
}
}
public static string GetTableName(this Type type)
{
if (type.IsDefined(typeof(TableMappingAttribute), true))
{
TableMappingAttribute attribute = type.GetCustomAttribute<TableMappingAttribute>();
return attribute.GetMappingName();
}
else
{
return type.Name;
}
}
public static string GetColumnName(this PropertyInfo prop)
{
if (prop.IsDefined(typeof(ColumnMappingAttribute), true))
{
ColumnMappingAttribute attribute = prop.GetCustomAttribute<ColumnMappingAttribute>();
return attribute.GetMappingName();
}
else
{
return prop.Name;
}
}
public static IEnumerable<PropertyInfo> NotKey(this IEnumerable<PropertyInfo> propertyInfos)
{
return propertyInfos.Where(p => !p.IsDefined(typeof(KeyAttribute), true));
}
}
}
AbstractMappingAttribute.cs
using System;
namespace DAL.Mapping
{
public abstract class AbstractMappingAttribute : Attribute
{
private string _MappingName = null;
public AbstractMappingAttribute(string name)
{
this._MappingName = name;
}
public string GetMappingName()
{
return this._MappingName;
}
}
}
ColumnMappingAttribute.cs
using System;
namespace DAL.Mapping
{
[AttributeUsage(AttributeTargets.Property)]
public class ColumnMappingAttribute : AbstractMappingAttribute
{
public ColumnMappingAttribute(string name) : base(name)
{
}
}
}
TableMappingAttribute.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAL.Mapping
{
[AttributeUsage(AttributeTargets.Class)]
public class TableMappingAttribute : AbstractMappingAttribute
{
public TableMappingAttribute(string name) : base(name)
{
}
}
}
KeyAttribute.cs
using System;
namespace DAL.Mapping
{
[AttributeUsage(AttributeTargets.Property)]
public class KeyAttribute : Attribute
{
}
}
Model层
Company.cs
using DAL;
using DAL.Mapping;
using System;
namespace Model
{
[TableMappingAttribute("Company")]
public class Company : BaseModel
{
//[ElevenColumnMappingAttribute("Name")]
public string Name { get; set; }
public DateTime? CreateTime { get; set; }
//public string CreateTime { get; set; }
public Nullable<int> LastModifierId { get; set; }//Nullable<int> 与 int? 等价
}
}
1412
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
