/// <summary> /// 修改数据(根据某几个字段进行修改) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="t">如果sql为null,则根据t的主键进行修改</param> /// <param name="sql">按条件修改</param> /// <returns></returns> public static bool Update <T>(this DbBase dbs, T t, IDbTransaction transaction = null, SqlQuery sql = null, int?commandTimeout = null) where T : class { try { #region 验证 //DBValidateCommon.ValidateTargetModel(t); #endregion var db = dbs.DbConnecttion; if (sql == null) { sql = SqlQuery <T> .Builder(dbs); } //获取查询内容 if (t is ModelBase) { ModelBase store = t as ModelBase; sql = sql.AppendParam <T>(t).SetExcProperties <T>(store.Fields); } else { sql = sql.AppendParam <T>(t); } var f = db.Execute(sql.UpdateSql, sql.Param, transaction, commandTimeout, null); return(f > 0); } catch (Exception ex) { throw ex; } }
/// <summary> /// 执行存储过程 /// 具体二次分装需要参考:http://blog.csdn.net/wang463584281/article/details/21244933 /// </summary> /// <param name="dbs"></param> /// <param name="sotreName"></param> /// <param name="inParems"></param> /// <param name="outResult"></param> /// <returns></returns> public static int StoreProcedure(this DbBase dbs, string sotreName, DynamicParameters inParems, string outResult) { var db = dbs.DbConnecttion; db.Execute(sotreName, inParems, null, null, CommandType.StoredProcedure); return(inParems.Get <int>(outResult)); }
/// <summary> /// 获得SqlQuery查询条件 /// </summary> /// <param name="dbBase"></param> /// <returns></returns> public SqlQuery GetSqlQuery(DbBase dbBase) { SqlQuery <T> query = SqlQuery <T> .Builder(dbBase); query.SetWhere(experList); return(query); }
/// <summary> /// 数据数量 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="sql"></param> /// <returns></returns> public static long Count <T>(this DbBase dbs, SqlQuery sql = null) where T : class { var db = dbs.DbConnecttion; if (sql == null) { sql = SqlQuery <T> .Builder(dbs); } var cr = db.Query(sql.CountSql, sql.Param, null, false, null, null).SingleOrDefault(); return((long)cr.DataCount); }
/// <summary> /// 查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="sql"></param> /// <returns></returns> public static List <T> QueryData <T>(this DbBase dbs, SqlQuery sql = null) where T : class { var db = dbs.DbConnecttion; if (sql == null) { sql = SqlQuery <T> .Builder(dbs); } var result = db.Query <T>(sql.QuerySql, sql.Param, null, false, null, null); return(result.ToList()); }
/// <summary> /// 存储过程无返回(可获取 OutPut,ReturnValue) /// </summary> /// <param name="dbs"></param> /// <param name="storeName">存储过程名称</param> /// <param name="param">输入参数</param> public static void ExecuteStoredProcedure(this DbBase dbs, string storeName, object param = null) { try { var db = dbs.DbConnecttion; db.Execute(storeName, param, null, null, CommandType.StoredProcedure); } catch (Exception ex) { throw ex; } }
/// <summary> /// 获取默认一条数据,没有则为NULL /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="sql"></param> /// <returns></returns> public static T SingleOrDefault <T>(this DbBase dbs, SqlQuery sql) where T : class { var db = dbs.DbConnecttion; if (sql == null) { sql = SqlQuery <T> .Builder(dbs); } sql = sql.Top(1); var result = db.Query <T>(sql.QuerySql, sql.Param, null, false, null, null); return(result.FirstOrDefault()); }
/// <summary> /// 存储过程返回集合(待优化) /// 参考:https://github.com/StackExchange/dapper-dot-net /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="storeName">存储过程名称</param> /// <param name="param">输入参数</param> /// <returns></returns> public static List <T> ExecuteStoredProcedureWithParms <T>(this DbBase dbs, string storeName, object param = null) where T : class { try { var db = dbs.DbConnecttion; var result = db.Query <T>(storeName, param, null, false, null, CommandType.StoredProcedure); return(result.ToList()); } catch (Exception ex) { throw ex; } }
/// <summary> /// 批量删除实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="lt"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> public static bool DeleteBatch <T>(this DbBase dbs, IList <T> lt, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { try { var db = dbs.DbConnecttion; var sql = SqlQuery <T> .Builder(dbs); var flag = db.Execute(sql.DeleteSql, lt, transaction, commandTimeout, null); return(flag == lt.Count); } catch (Exception ex) { throw ex; } }
/// <summary> /// 按条件删除 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="sql"></param> /// <returns></returns> public static bool Delete <T>(this DbBase dbs, SqlQuery sql = null, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { try { var db = dbs.DbConnecttion; if (sql == null) { sql = SqlQuery <T> .Builder(dbs); } var f = db.Execute(sql.DeleteSql, sql.Param, transaction, commandTimeout, null); return(f > 0); } catch (Exception ex) { throw ex; } }
/// <summary> /// 分页查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="dataCount"></param> /// <param name="sqlQuery"></param> /// <returns></returns> public static List <T> PageDataByRowNumber <T>(this DbBase dbs, int startRow, int endRow, out long dataCount, SqlQuery sqlQuery = null) where T : class { var db = dbs.DbConnecttion; var result = new List <T>(); dataCount = 0; if (sqlQuery == null) { sqlQuery = SqlQuery <T> .Builder(dbs); } sqlQuery = sqlQuery.PageRowNumber(startRow, endRow); var para = sqlQuery.Param; var cr = db.Query(sqlQuery.CountSql, para, null, false, null, null).SingleOrDefault(); dataCount = (long)cr.DataCount; //pageCount = cr.DataCount % pageSize == 0 ? (int)cr.DataCount / pageSize : (int)cr.DataCount / pageSize + 1;//计算页数 result = db.Query <T>(sqlQuery.PageSql, para, null, false, null, null).ToList(); return(result); }
/// <summary> /// 批量插入数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="lt"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> public static bool InsertBatch <T>(this DbBase dbs, IList <T> lt, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { try { #region 验证 //foreach (var t in lt) //{ // DBValidateCommon.ValidateTargetModel(t); //} #endregion var db = dbs.DbConnecttion; var sql = SqlQuery <T> .Builder(dbs); var flag = db.Execute(sql.InsertSql, lt, transaction, commandTimeout, null); return(flag == lt.Count); } catch (Exception ex) { throw ex; } }
/// <summary> /// 插入数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dbs"></param> /// <param name="t"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> public static bool Insert <T>(this DbBase dbs, T t, IDbTransaction transaction = null, int?commandTimeout = null) where T : class { try { #region 验证 //DBValidateCommon.ValidateTargetModel(t); #endregion var db = dbs.DbConnecttion; SqlQuery sql = SqlQuery <T> .Builder(dbs); bool isAutoId = false; string insertSql = sql.InsertSql; ModelDes modelDes = Common.GetModelDes <T>(); if (modelDes != null && modelDes.Properties != null) { foreach (var item in modelDes.Properties) { if ((item.CusAttribute is IdAttribute) && ((item.CusAttribute as IdAttribute).CheckAutoId)) { isAutoId = true; break; } } } int flag = 0; if (t is ModelBase) { ModelBase store = t as ModelBase; sql = sql.AppendParam <T>(t).SetExcProperties <T>(store.Fields); } else { sql = sql.AppendParam <T>(t); } if (isAutoId) { insertSql += ";SELECT @@IDENTITY;"; var recordId = db.ExecuteScalar(insertSql, sql.Param, transaction, commandTimeout, null); //flag = recordId.MConvertTo<int>(); flag = Convert.ToInt32(recordId); } else { flag = db.Execute(insertSql, sql.Param, transaction, commandTimeout, null); } if (isAutoId) { #region 如果主键是自增Id,则将生成的id赋值到实体类中 if (modelDes != null && modelDes.Properties != null) { foreach (var item in modelDes.Properties) { if ((item.CusAttribute is IdAttribute) && ((item.CusAttribute as IdAttribute).CheckAutoId)) { var filed = t.GetType().GetProperty(item.Field); filed.SetValue(t, Convert.ChangeType(flag, filed.PropertyType), null); break; } } } #endregion return(true); } return(flag > 0); } catch (Exception ex) { throw ex; } finally { } }