【手撸一个ORM】第十步、数据操作工具类 MyDb
说明
其实就是数据库操作的一些封装,很久不用SqlCommand操作数据库了,看了点园子里的文章就直接上手写了,功能上没问题,但写法上是否完美高效无法保证,建议有需要的朋友自己重写,当然如果能把最佳实践方式告知一下,不胜感激!!
因为文件比较大,所以将此类分成了四部分。
MyDb主体
using MyOrm.Commons; using MyOrm.DbParameters; using MyOrm.Expressions; using MyOrm.Queryable; using MyOrm.Reflections; using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq.Expressions; namespace MyOrm { public partial class MyDb { private readonly string _connectionString; private readonly string _prefix; public MyDb(string connectionString, string prefix = "@") { _connectionString = connectionString; _prefix = prefix; } public MyDb() { if (string.IsNullOrWhiteSpace(MyDbConfiguration.GetConnectionString())) { throw new Exception("MyOrm尚未初始化"); } _connectionString = MyDbConfiguration.GetConnectionString(); _prefix = MyDbConfiguration.GetPrefix(); } /// <summary> /// 使用默认配置,返回新MyDb实例 /// </summary> /// <returns></returns> public static MyDb New() { return new MyDb(); } /// <summary> /// 返回新MyDb实例 /// </summary> /// <param name="connectionString"></param> /// <param name="prefix"></param> /// <returns></returns> public static MyDb New(string connectionString, string prefix = "@") { return new MyDb(connectionString, prefix); } #region 查询 /// <summary> /// 返回MyQueryable实例 /// </summary> /// <typeparam name="T"></typeparam> /// <returns>实体,若记录为空,返回default(T)</returns> public MyQueryable<T> Query<T>() where T : class, IEntity, new() { return new MyQueryable<T>(_connectionString); } /// <summary> /// 根据ID加载一个实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="id">实体ID</param> /// <returns>实体,若记录为空,返回default(T)</returns> public T Load<T>(int id) where T : class, IEntity, new() { return new MyQueryable<T>(_connectionString).Where(t => t.Id == id).FirstOrDefault(); } /// <summary> /// 根据条件加载一个实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where">查询条件</param> /// <param name="orderBy">排序字段</param> /// <param name="dbSort">正序或倒序</param> /// <returns>实体,若记录为空,返回default(T)</returns> public T Load<T>(Expression<Func<T, bool>> where = null, Expression<Func<T, object>> orderBy = null, MyDbOrderBy dbSort = MyDbOrderBy.Asc) where T : class, new() { var query = new MyQueryable<T>(_connectionString); if (where != null) { query.Where(where); } if (orderBy != null) { query.OrderBy(orderBy, dbSort); } return query.FirstOrDefault(); } /// <summary> /// 根据条件加载所有实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where">查询条件</param> /// <param name="orderBy">排序字段</param> /// <param name="dbSort">正序或倒序</param> /// <returns>实体列表</returns> public List<T> Fetch<T>(Expression<Func<T, bool>> where = null, Expression<Func<T, object>> orderBy = null, MyDbOrderBy dbSort = MyDbOrderBy.Asc) where T : class, new() { var query = new MyQueryable<T>(_connectionString); if (where != null) { query.Where(where); } if (orderBy != null) { query.OrderBy(orderBy, dbSort); } return query.ToList(); } /// <summary> /// 加载分页列表 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">每页条数</param> /// <param name="recordCount">记录总数</param> /// <param name="where">查询条件</param> /// <param name="orderBy">排序字段</param> /// <param name="dbSort">正序或倒序</param> /// <returns>实体列表,输出记录总数</returns> public List<T> PageList<T>(int pageIndex, int pageSize, out int recordCount, Expression<Func<T, bool>> where = null, Expression<Func<T, object>> orderBy = null, MyDbOrderBy dbSort = MyDbOrderBy.Asc) where T : class, new() { var query = new MyQueryable<T>(_connectionString); if (where != null) { query.Where(where); } if (orderBy != null) { query.OrderBy(orderBy, dbSort); } return query.ToPageList(pageIndex, pageSize, out recordCount); } #endregion #region 获取数量 public int GetCount<T>(Expression<Func<T, bool>> expression = null) where T : class, IEntity, new() { var entityInfo = MyEntityContainer.Get(typeof(T)); if (expression == null) { var sql = $"SELECT COUNT(0) FROM [{entityInfo.TableName}]"; using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); return (int)command.ExecuteScalar(); } } else { var resolver = new EditConditionResolver<T>(entityInfo); var result = resolver.Resolve(expression.Body); var condition = result.Condition; var parameters = result.Parameters; condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition; var sql = $"SELECT COUNT(0) FROM [{entityInfo.TableName}] WHERE [{condition}]"; using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddRange(parameters.Parameters); return (int)command.ExecuteScalar(); } } } #endregion #region 执行SQL语句 //public List<T> Fetch<T>(string sql, MyDbParameters parameters = null) //{ //} #endregion } }
MyDbUpdate 更新的相关操作
using MyOrm.Commons; using MyOrm.DbParameters; using MyOrm.Expressions; using MyOrm.Reflections; using MyOrm.SqlBuilder; using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Linq.Expressions; namespace MyOrm { public partial class MyDb { /// <summary> /// 更新一个实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity">要更新的实体</param> /// <returns>受影响的记录数</returns> public int Update<T>(T entity) where T : class, IEntity, new() { var entityInfo = MyEntityContainer.Get(typeof(T)); var sqlBuilder = new SqlServerBuilder(); var sql = sqlBuilder.Update(entityInfo, ""); var parameters = new MyDbParameters(); parameters.Add(entity); var command = new SqlCommand(sql); command.Parameters.AddRange(parameters.Parameters); using (var conn = new SqlConnection(_connectionString)) { conn.Open(); command.Connection = conn; return command.ExecuteNonQuery(); } } /// <summary> /// 更新多个实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entityList">要更新的实体列表</param> /// <returns>受影响的记录数</returns> public int Update<T>(List<T> entityList) where T : class, IEntity, new() { var entityInfo = MyEntityContainer.Get(typeof(T)); var sqlBuilder = new SqlServerBuilder(); var sql = sqlBuilder.Update(entityInfo, ""); var count = 0; using (var conn = new SqlConnection(_connectionString)) { conn.Open(); using (var trans = conn.BeginTransaction()) { try { foreach (var entity in entityList) { using (var command = new SqlCommand(sql, conn, trans)) { var param = new MyDbParameters(); param.Add(entity); command.Parameters.AddRange(param.Parameters); count += command.ExecuteNonQuery(); } } trans.Commit(); } catch (Exception) { trans.Rollback(); count = 0; } } } return count; } /// <summary> /// 如果不存在,则更新 /// 如:UpdateIfNotExists(user, u=>u.Name == user.Name && u.Id != user.Id) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <param name="where"></param> /// <returns>受影响的记录数</returns> public int UpdateIfNotExits<T>(T entity, Expression<Func<T, bool>> where) { var entityInfo = MyEntityContainer.Get(typeof(T)); var resolver = new EditConditionResolver<T>(entityInfo); var result = resolver.Resolve(where.Body); var condition = result.Condition; var parameters = result.Parameters; parameters.Add(entity); condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition; var sqlBuilder = new SqlServerBuilder(); var sql = sqlBuilder.Update(entityInfo, ""); sql += $" AND NOT EXISTS (SELECT 1 FROM [{entityInfo.TableName}] WHERE {condition})"; var command = new SqlCommand(sql); command.Parameters.AddRange(parameters.Parameters); using (var conn = new SqlConnection(_connectionString)) { conn.Open(); command.Connection = conn; return command.ExecuteNonQuery(); } } #region 扩展 /// <summary> /// 通过Id修改指定列 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="id">实体ID</param> /// <param name="kvs">属性和值的键值对。用法 DbKvs.New().Add("属性名", 值)</param> /// <returns>受影响的记录数</returns> public int Update<T>(int id, DbKvs kvs) { var entityInfo = MyEntityContainer.Get(typeof(T)); var setProperties = kvs.Where(kv => kv.Key != "Id").Select(kv => kv.Key); var includeProperties = entityInfo.Properties.Where(p => setProperties.Contains(p.Name)).ToList(); if (includeProperties.Count == 0) { return 0; } var sql = $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE Id=@Id"; var parameters = kvs.ToSqlParameters(); parameters.Add(new SqlParameter("@Id", id)); using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddRange(parameters.ToArray()); return command.ExecuteNonQuery(); } } /// <summary> /// 通过查询条件修改指定列 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="kvs">属性和值的键值对。用法 DbKvs.New().Add("属性名", 值)</param> /// <param name="expression">查询条件,注意:不支持导航属性,如 "student => student.School.Id > 0" 将无法解析</param> /// <returns>受影响的记录数</returns> public int Update<T>(Expression<Func<T, bool>> expression, DbKvs kvs) { var entityInfo = MyEntityContainer.Get(typeof(T)); var setProperties = kvs.Where(kv => kv.Key != "Id").Select(kv => kv.Key); var includeProperties = entityInfo.Properties.Where(p => setProperties.Contains(p.Name)).ToList(); if (includeProperties.Count == 0) { return 0; } string sql; List<SqlParameter> parameters; if (expression == null) { sql = $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE Id=@Id"; parameters = kvs.ToSqlParameters(); } else { var resolver = new EditConditionResolver<T>(entityInfo); var result = resolver.Resolve(expression.Body); var where = result.Condition; var whereParameters = result.Parameters; parameters = kvs.ToSqlParameters(); parameters.AddRange(whereParameters.Parameters); where = string.IsNullOrWhiteSpace(where) ? "1=1" : where; sql = $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE {where}"; } using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddRange(parameters.ToArray()); return command.ExecuteNonQuery(); } } /// <summary> /// 修改实体的指定属性 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity">要修改的实体</param> /// <param name="includes">要修改的属性名称,注意:是实体的属性名而不是数据库字段名</param> /// <param name="ignoreAttribute">是否忽略实体的UpdateIgnore描述。默认为true,既includes中包含的所有属性都会被修改</param> /// <returns>受影响的记录数</returns> public int UpdateInclude<T>(T entity, IEnumerable<string> includes, bool ignoreAttribute = true) where T : IEntity { var entityInfo = MyEntityContainer.Get(typeof(T)); var includeProperties = entityInfo.Properties.Where(p => includes.Contains(p.Name) && p.Name != "Id").ToList(); if (!ignoreAttribute) { includeProperties = includeProperties.Where(p => !p.UpdateIgnore).ToList(); } if (includeProperties.Count == 0) { return 0; } var sql = $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE Id=@Id"; var parameters = new List<SqlParameter> { new SqlParameter("@Id", entity.Id) }; foreach (var property in includeProperties) { parameters.Add(new SqlParameter($"@{property.Name}", ResolveParameterValue(property.PropertyInfo.GetValue(entity)))); } using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddRange(parameters.ToArray()); return command.ExecuteNonQuery(); } } /// <summary> /// 修改实体的指定属性 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity">要修改的实体</param> /// <param name="expression">要修改的属性,注意不支持导航属性及其子属性</param> /// <param name="ignoreAttribute">是否忽略实体的UpdateIgnore描述。默认为true,既includes中包含的所有属性都会被修改</param> /// <returns>受影响的记录数</returns> public int UpdateInclude<T>(T entity, Expression<Func<T, object>> expression, bool ignoreAttribute = true) where T : IEntity { var entityInfo = MyEntityContainer.Get(typeof(T)); var visitor = new ObjectMemberVisitor(); visitor.Visit(expression); var include = visitor.GetPropertyList(); return UpdateInclude(entity, include, ignoreAttribute); } /// <summary> /// 修改实体除指定属性外的其他属性 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity">要修改的实体</param> /// <param name="ignore">要忽略的属性,注意:是实体的属性名而不是数据表的列名</param> /// <param name="ignoreAttribute">是否忽略实体的UpdateIgnore描述。默认为true,既includes中包含的所有属性都会被修改</param> /// <returns>受影响的记录数</returns> public int UpdateIgnore<T>(T entity, IEnumerable<string> ignore, bool ignoreAttribute = true) where T : IEntity { var entityInfo = MyEntityContainer.Get(typeof(T)); var includeProperties = entityInfo.Properties.Where(p => !ignore.Contains(p.Name) && p.Name != "Id").ToList(); if (!ignoreAttribute) { includeProperties = includeProperties.Where(p => !p.UpdateIgnore).ToList(); } if (includeProperties.Count() == 0) { return 0; } var sql = $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE Id=@Id"; var parameters = new List<SqlParameter> { new SqlParameter("@Id", entity.Id) }; foreach (var property in includeProperties) { parameters.Add(new SqlParameter($"@{property.Name}", ResolveParameterValue(property.PropertyInfo.GetValue(entity)))); } using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddRange(parameters.ToArray()); return command.ExecuteNonQuery(); } } /// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity">要修改的实体</param> /// <param name="expression">要修改的属性,注意不支持导航属性及其子属性</param> /// <param name="ignoreAttribute">是否忽略实体的UpdateIgnore描述。默认为true,既includes中包含的所有属性都会被修改</param> /// <returns>受影响的记录数</returns> public int UpdateIgnore<T>(T entity, Expression<Func<T, object>> expression, bool ignoreAttribute = true) where T : IEntity { var entityInfo = MyEntityContainer.Get(typeof(T)); var visitor = new ObjectMemberVisitor(); visitor.Visit(expression); var include = visitor.GetPropertyList(); return UpdateIgnore(entity, include, ignoreAttribute); } #endregion private object ResolveParameterValue(object val) { if (val is null) { val = DBNull.Value; } return val; } } }
MyDbInsert 插入相关的操作
using MyOrm.Commons; using MyOrm.DbParameters; using MyOrm.Expressions; using MyOrm.Reflections; using MyOrm.SqlBuilder; using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Linq.Expressions; namespace MyOrm { public partial class MyDb { /// <summary> /// 创建一个实体,新的记录Id将绑定到entity的Id属性 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity">要创建的实体</param> /// <returns>新生成记录的ID,若失败返回0</returns> public int Insert<T>(T entity) where T : class, IEntity, new() { var entityInfo = MyEntityContainer.Get(typeof(T)); var sqlBuilder = new SqlServerBuilder(); var sql = sqlBuilder.Insert(entityInfo); var parameters = new MyDbParameters(); parameters.Add(entity); var command = new SqlCommand(sql); command.Parameters.AddRange(parameters.Parameters); using (var conn = new SqlConnection(_connectionString)) { conn.Open(); command.Connection = conn; var result = command.ExecuteScalar().ToString(); entity.Id = Convert.ToInt32(string.IsNullOrWhiteSpace(result) ? "0" : result); return entity.Id; } } /// <summary> /// 如果不满足条件则创建一个实体, /// 如限制用户名不能重复 InsertIfNotExist(user, u => u.Name == user.Name) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity">要创建的实体</param> /// <param name="where">条件</param> /// <returns>新生成记录的ID,若失败返回0</returns> public int InsertIfNotExists<T>(T entity, Expression<Func<T, bool>> where) where T : class, IEntity, new() { if (where == null) { return Insert(entity); } else { var entityInfo = MyEntityContainer.Get(typeof(T)); var resolver = new EditConditionResolver<T>(entityInfo); var result = resolver.Resolve(where.Body); var condition = result.Condition; var parameters = result.Parameters; parameters.Add(entity); condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition; var sqlBuilder = new SqlServerBuilder(); var sql = sqlBuilder.InsertIfNotExists(entityInfo, condition); var command = new SqlCommand(sql); command.Parameters.AddRange(parameters.Parameters); using (var conn = new SqlConnection(_connectionString)) { conn.Open(); command.Connection = conn; var idString = command.ExecuteScalar().ToString(); entity.Id = Convert.ToInt32(string.IsNullOrWhiteSpace(idString) ? "0" : idString); return entity.Id; } } } /// <summary> /// 批量创建实体,注意此方法效率不高 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entityList">实体列表</param> /// <returns>受影响的记录数</returns> public int Insert<T>(List<T> entityList) where T : class, IEntity, new() { var entityInfo = MyEntityContainer.Get(typeof(T)); var sqlBuilder = new SqlServerBuilder(); var sql = sqlBuilder.Insert(entityInfo); var count = 0; using (var conn = new SqlConnection(_connectionString)) { conn.Open(); using (var trans = conn.BeginTransaction()) { try { foreach (var entity in entityList) { using (var command = new SqlCommand(sql, conn, trans)) { var parameters = new MyDbParameters(); parameters.Add(entity); command.Parameters.AddRange(parameters.Parameters); var result = command.ExecuteScalar().ToString(); entity.Id = Convert.ToInt32(string.IsNullOrWhiteSpace(result) ? "0" : result); count++; } } trans.Commit(); } catch { trans.Rollback(); count = 0; } } } return count; } } }
MyDbDelete 删除相关的操作
using MyOrm.Commons; using MyOrm.Expressions; using MyOrm.Reflections; using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq.Expressions; namespace MyOrm { public partial class MyDb { #region 删除 /// <summary> /// 根据ID删除记录,如果支持软删除并且非强制删除,则更新IsDel字段为true,否则,删除记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="id">要删除的实体ID</param> /// <param name="isForce">是否强制删除,默认为false</param> /// <returns>受影响的记录数</returns> public int Delete<T>(int id, bool isForce = false) where T : class, IEntity, new() { var entityInfo = MyEntityContainer.Get(typeof(T)); if (isForce || !entityInfo.IsSoftDelete) { var sql = $"DELETE [{entityInfo.TableName}] WHERE [{entityInfo.KeyColumn}]={_prefix}Id"; using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddWithValue($"{_prefix}Id", id); return command.ExecuteNonQuery(); } } else { var sql = $"UPDATE [{entityInfo.TableName}] SET IsDel=1 WHERE [{entityInfo.KeyColumn}]={_prefix}Id"; using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddWithValue($"{_prefix}Id", id); return command.ExecuteNonQuery(); } } } /// <summary> /// 根据ID批量删除记录,如果支持软删除并且非强制删除,则更新IsDel字段为true,否则,删除记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="idList">要删除的ID列表</param> /// <param name="isForce">是否强制删除,默认为false</param> /// <returns>受影响的记录数</returns> public int Delete<T>(IEnumerable<int> idList, bool isForce = false) where T : class, IEntity, new() { var entityInfo = MyEntityContainer.Get(typeof(T)); if (isForce || !entityInfo.IsSoftDelete) { var sql = $"EXEC('DELETE [{entityInfo.TableName}] WHERE [{entityInfo.KeyColumn}] in ('+{_prefix}Ids+')')"; using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddWithValue($"{_prefix}Ids", string.Join(",", idList)); return command.ExecuteNonQuery(); } } else { var sql = $"EXEC('UPDATE [{entityInfo.TableName}] SET IsDel=1 WHERE [{entityInfo.KeyColumn}] in ('+{_prefix}Ids+')')"; using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddWithValue($"{_prefix}Id", idList); return command.ExecuteNonQuery(); } } } /// <summary> /// 根据条件删除记录,如果支持软删除并且非强制删除,则更新IsDel字段为true,否则,删除记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="expression">条件,注意不支持导航属性及其子属性</param> /// <param name="isForce">是否强制删除</param> /// <returns>受影响的记录数</returns> public int Delete<T>(Expression<Func<T, bool>> expression, bool isForce) where T : IEntity { var entityInfo = MyEntityContainer.Get(typeof(T)); var resolver = new EditConditionResolver<T>(entityInfo); var result = resolver.Resolve(expression.Body); var condition = result.Condition; var parameters = result.Parameters; condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition; string sql; if (isForce || !entityInfo.IsSoftDelete) { sql = $"DELETE [{entityInfo.TableName}] WHERE {condition}"; } else { sql = $"UPDATE [{entityInfo.TableName}] SET IsDel=1 WHERE {condition}"; } using (var conn = new SqlConnection(_connectionString)) { conn.Open(); var command = new SqlCommand(sql, conn); command.Parameters.AddRange(parameters.Parameters); return command.ExecuteNonQuery(); } } #endregion } }