/// <summary> /// 将实体插入到指定的数据库中 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="model">实体对象</param> /// <param name="DBName">数据库名称</param> /// <param name="tableName">表名,可空,空时为类型名</param> /// <param name="isSkip">是否跳过指定列,默认跳过</param> /// <param name="skipColumns">跳过的列名,默认为配置的列</param> /// <returns></returns> public static int InsertSql <T>(T model, DBConfigEnum DBName, string tableName = "", bool?isSkip = null, string[] skipColumns = null) { StringBuilder sql = new StringBuilder(); string columns = ""; string values = ""; DynamicParameters param = new DynamicParameters(); if (isSkip == null) { isSkip = _isSkip; } if (skipColumns == null) { skipColumns = _InsertSkipColumns; } if (string.IsNullOrWhiteSpace(tableName)) { tableName = typeof(T).Name;//model.GetType().Name; } foreach (var a in model.GetType().GetProperties()) { if (isSkip == true) { bool isContinue = false; foreach (var c in skipColumns) { if (a.Name.ToLower() == c.ToLower()) { isContinue = true; break; } } if (isContinue) { continue; } } columns += a.Name + ","; values += "@" + a.Name + ","; if (string.IsNullOrWhiteSpace(Convert.ToString(a.GetValue(model)))) { param.Add("@" + a.Name, null); } else { param.Add(a.Name, a.GetValue(model).ToString()); } } if (!string.IsNullOrWhiteSpace(columns)) { columns = columns.Substring(0, columns.Length - 1); values = values.Substring(0, values.Length - 1); } sql.Append($" insert into {tableName}({columns}) values({values}) "); using (var conn = new DBConfig().InitConn(DBName.ToString())) { return(conn.Execute(sql.ToString(), param)); } }
/// <summary> /// 执行SQL返回DataSet /// </summary> /// <param name="sql">SQL语句</param> /// <param name="DBName">数据库名称</param> /// <returns></returns> public static DataSet ExecuteSql(string sql, DBConfigEnum DBName) { using (var conn = new DBConfig().InitConn(DBName.ToString())) { SqlCommand comm = new SqlCommand(sql, conn); SqlDataAdapter sda = new SqlDataAdapter(comm); DataSet ds = new DataSet(); sda.Fill(ds); return(ds); } }
/// <summary> /// 按数据库更新实体数据 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="model">实体</param> /// <param name="DBName">数据库名称</param> /// <param name="tableName">表名,可空,默认为实体名称</param> /// <param name="mainKey">主键列名,默认id</param> /// <returns></returns> public static int Update <T>(T model, DBConfigEnum DBName, string tableName = "", string mainKey = "id") { StringBuilder sql = new StringBuilder(); string setStr = ""; DynamicParameters param = new DynamicParameters(); if (string.IsNullOrWhiteSpace(tableName)) { tableName = typeof(T).Name; } sql.Append($"update {tableName} set "); foreach (var a in model.GetType().GetProperties()) { if (string.IsNullOrWhiteSpace(Convert.ToString(a.GetValue(model)))) { continue; } if (a.Name.ToLower() == mainKey.ToLower()) { if (string.IsNullOrWhiteSpace(Convert.ToString(a.GetValue(model)))) { return(0); } param.Add("@" + mainKey, a.GetValue(model).ToString()); continue; } setStr += $"{a.Name} = @{a.Name},"; param.Add("@" + a.Name, a.GetValue(model).ToString()); } if (!string.IsNullOrWhiteSpace(setStr)) { setStr = setStr.Substring(0, setStr.Length - 1); sql.Append(setStr); sql.Append($" where {mainKey} = @{mainKey} "); using (var conn = new DBConfig().InitConn(DBName.ToString())) { return(conn.Execute(sql.ToString(), param)); } } else { return(0); } }
/// <summary> /// 按数据库根据键值删除数据 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="id">键值</param> /// <param name="DBName">数据库名称</param> /// <param name="tableName">表名,默认为实体类型名称</param> /// <param name="mainKey">列名,默认id</param> /// <returns></returns> public static int Delete <T>(string id, DBConfigEnum DBName, string tableName = "", string mainKey = "id") { if (string.IsNullOrWhiteSpace(tableName)) { tableName = typeof(T).Name; } if (string.IsNullOrWhiteSpace(id) || string.IsNullOrWhiteSpace(mainKey)) { return(0); } string sql = $" Delete {tableName} where {mainKey} = @{mainKey}"; DynamicParameters param = new DynamicParameters(); param.Add("@" + mainKey, id); using (var conn = new DBConfig().InitConn(DBName.ToString())) { return(conn.Execute(sql, param)); } }
/// <summary> /// 按数据库使用事务删除多个表的数据 /// </summary> /// <param name="list"> /// Hashtable格式: /// hs["id"] 键值 /// hs["table"] 表名 /// hs["mainKey"] 列名 /// </param> /// <param name="DBName">数据库名称</param> /// <param name="mainKey">默认列名</param> /// <param name="ErrorIsRollBack">是否需要匹配传入的列表行数,当行数与变更行数不等时回滚,默认不需要</param> /// <returns></returns> public static int Delete(List <Hashtable> list, DBConfigEnum DBName, string mainKey = "id", bool ErrorIsRollBack = false) { int res = 0; using (var conn = new DBConfig().InitConn(DBName.ToString())) { IDbTransaction tran = conn.BeginTransaction(); try { foreach (var hs in list) { if (string.IsNullOrWhiteSpace(Convert.ToString(hs["id"])) || string.IsNullOrWhiteSpace(Convert.ToString(hs["table"]))) { tran.Rollback(); return(0); } if (string.IsNullOrWhiteSpace(Convert.ToString(hs["mainKey"]))) { hs["mainKey"] = mainKey; } string sql = $" Delete {Convert.ToString(hs["table"])} where {Convert.ToString(hs["mainKey"])} = @{Convert.ToString(hs["mainKey"])}"; DynamicParameters param = new DynamicParameters(); param.Add("@" + Convert.ToString(hs["mainKey"]), Convert.ToString(hs["id"])); res += conn.Execute(sql, param, tran); } if (ErrorIsRollBack) { if (res < list.Count || res > list.Count) { tran.Rollback(); return(0); } } tran.Commit(); return(res); } catch { tran.Rollback(); return(0); } } }
/// <summary> /// 按数据库使用事务删除多条数据 /// </summary> /// <param name="ids">键值</param> /// <param name="DBName">数据库名称</param> /// <param name="tableName">表名</param> /// <param name="mainKey">主键</param> /// <param name="ErrorIsRollBack">是否需要匹配传入的列表行数,当行数与变更行数不等时回滚,默认不需要</param> /// <returns></returns> public static int Delete(List <string> ids, DBConfigEnum DBName, string tableName, string mainKey = "id", bool ErrorIsRollBack = false) { if (string.IsNullOrWhiteSpace(tableName)) { throw new Exception("无效的表名"); } int res = 0; using (var conn = new DBConfig().InitConn(DBName.ToString())) { IDbTransaction tran = conn.BeginTransaction(); try { foreach (var id in ids) { if (string.IsNullOrWhiteSpace(id) || string.IsNullOrWhiteSpace(mainKey)) { tran.Rollback(); return(0); } string sql = $" Delete {tableName} where {mainKey} = @{mainKey}"; DynamicParameters param = new DynamicParameters(); param.Add("@" + mainKey, id); res += conn.Execute(sql, param, tran); } if (ErrorIsRollBack) { if (res < ids.Count || res > ids.Count) { tran.Rollback(); return(0); } } tran.Commit(); return(res); } catch { tran.Rollback(); return(0); } } }
/// <summary> /// 按数据库根据键值取得一个实体 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="cond">条件,请以and|or等连接字开头</param> /// <param name="DBName">数据库名称</param> /// <param name="tableName">表名,默认为实体类型名</param> /// <param name="sortStr">排序方式,默认为系统设置的默认值</param> public static List <T> GetModels <T>(string cond, DBConfigEnum DBName, string tableName = "", string sortStr = "") { if (string.IsNullOrWhiteSpace(tableName)) { tableName = typeof(T).Name; } if (string.IsNullOrWhiteSpace(sortStr)) { sortStr = DefaultSortStr; } if (string.IsNullOrWhiteSpace(tableName) || string.IsNullOrWhiteSpace(sortStr)) { throw new Exception("空值的参数设定"); } string sql = $"select * from {tableName} where 1=1 {cond} order by {sortStr} "; using (var conn = new DBConfig().InitConn(DBName.ToString())) { return(conn.Query <T>(sql).ToList()); } }
/// <summary> /// 按数据库根据键值取得一个实体 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="id">键值</param> /// <param name="DBName">数据库名称</param> /// <param name="tableName">表名,默认为实体类型名</param> /// <param name="mainKey">键名,默认id</param> /// <param name="sortStr">排序方式,默认为系统设置的默认值</param> /// <returns></returns> public static T GetModel <T>(string id, DBConfigEnum DBName, string tableName = "", string mainKey = "id", string sortStr = "") { if (string.IsNullOrWhiteSpace(tableName)) { tableName = typeof(T).Name; } if (string.IsNullOrWhiteSpace(sortStr)) { sortStr = DefaultSortStr; } if (string.IsNullOrWhiteSpace(id) || string.IsNullOrWhiteSpace(tableName) || string.IsNullOrWhiteSpace(mainKey) || string.IsNullOrWhiteSpace(sortStr)) { throw new Exception("空值的参数设定"); } string sql = $"select top 1 * from {tableName} where {mainKey} = @{mainKey} order by {sortStr} "; DynamicParameters param = new DynamicParameters(); param.Add("@" + mainKey, id); using (var conn = new DBConfig().InitConn(DBName.ToString())) { return(conn.Query <T>(sql, param).SingleOrDefault()); } }
/// <summary> /// 按数据库取得分页取得数据 /// </summary> /// <param name="page">分页实体</param> /// <param name="columns">查询的列</param> /// <param name="tableSql">查询的表体</param> /// <param name="cond">查询的条件</param> /// <param name="DBName">数据库名称</param> /// <param name="totalRows">OUT->总数据行数</param> /// <returns>返回查询出来的数据以及输入总数据行数</returns> public static DataSet GetTableForPagination(Pagination page, string columns, string tableSql, string cond, DBConfigEnum DBName, out int totalRows) { int pageIndex = page.NowPage; int pageSize = page.PageSize; totalRows = 0; if (string.IsNullOrWhiteSpace(page.Sort)) { page.Sort = DefaultSortStr; page.SortType = ""; } else if (string.IsNullOrWhiteSpace(page.SortType)) { page.SortType = " asc "; } if (string.IsNullOrWhiteSpace(columns) || string.IsNullOrWhiteSpace(tableSql)) { throw new Exception("传入的列名与表名均不能为空"); } string sql = $@"select * from ( select ROW_NUMBER() OVER(order by {page.Sort} {page.SortType} ) rowid,{columns} from {tableSql} where 1=1 {cond} ) t where 1=1 and rowid between {((pageIndex - 1) * pageSize) + 1} and {pageIndex * pageSize}"; string countSql = $@"select Count(1) counts from ( select {columns} from {tableSql} where 1=1 {cond} ) t "; DataSet ds = ExecuteSql(sql, DBName); using (var conn = new DBConfig().InitConn(DBName.ToString())) { totalRows = conn.ExecuteScalar <int>(countSql); } return(ds); }
/// <summary> /// 按数据库名称并根据传入的参数进行查询 /// </summary> /// <param name="columns">查询的列名</param> /// <param name="tableSql">查询的表体</param> /// <param name="cond">条件,以and|or连接字开始</param> /// <param name="sort">排序字段</param> /// <param name="DBName">数据库名称</param> /// <param name="rowCount">查询结果的行数</param> /// <returns>返回一个DataSet并带出查询的行数</returns> public static DataSet GetTable(string columns, string tableSql, string cond, string sort, DBConfigEnum DBName, out int rowCount) { if (string.IsNullOrWhiteSpace(sort)) { sort = DefaultSortStr; } if (string.IsNullOrWhiteSpace(columns) || string.IsNullOrWhiteSpace(tableSql) || string.IsNullOrWhiteSpace(sort)) { throw new Exception("空值的参数设定"); } string sql = $@"select * from ( select ROW_NUMBER() over( order by {sort}) rowid, {columns} from {tableSql} where 1=1 {cond} ) t "; string countSql = $@"select Count(1) from ( select ROW_NUMBER() over( order by {sort}) rowid, {columns} from {tableSql} where 1=1 {cond} ) t "; DataSet ds = new DataSet(); ds = ExecuteSql(sql, DBName); using (var conn = new DBConfig().InitConn(DBName.ToString())) { rowCount = conn.ExecuteScalar <int>(countSql); } return(ds); }
/// <summary> /// 按数据库名称并根据传入的参数进行查询 /// </summary> /// <param name="columns">查询的列名</param> /// <param name="tableSql">查询的表体</param> /// <param name="cond">条件,以and|or连接字开始</param> /// <param name="sort">排序字段</param> /// <param name="DBName">数据库名称</param> /// <returns>返回一个DataSet</returns> public static DataSet GetTable(string columns, string tableSql, string cond, string sort, DBConfigEnum DBName) { if (string.IsNullOrWhiteSpace(sort)) { sort = DefaultSortStr; } if (string.IsNullOrWhiteSpace(columns) || string.IsNullOrWhiteSpace(tableSql) || string.IsNullOrWhiteSpace(sort)) { throw new Exception("空值的参数设定"); } string sql = $@"select * from ( select ROW_NUMBER() over( order by {sort}) rowid, {columns} from {tableSql} where 1=1 {cond} ) t "; DataSet ds = new DataSet(); ds = ExecuteSql(sql, DBName); return(ds); }
/// <summary> /// 按数据库更新实体列表 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="models">实体列表</param> /// <param name="DBName">数据库名称</param> /// <param name="tableName">表名,可空,默认为实体名称</param> /// <param name="mainKey">主键列名,默认id</param> /// <param name="ErrorIsRollBack">错误是否回滚</param> /// <returns></returns> public static int Update <T>(List <T> models, DBConfigEnum DBName, string tableName = "", string mainKey = "id", bool ErrorIsRollBack = false) { int res = 0; if (string.IsNullOrWhiteSpace(tableName)) { tableName = typeof(T).Name; } List <StringBuilder> sqls = new List <StringBuilder>(); List <DynamicParameters> paramList = new List <DynamicParameters>(); foreach (var model in models) { StringBuilder sql = new StringBuilder(); string setStr = ""; DynamicParameters param = new DynamicParameters(); sql.Append($"update {tableName} set "); foreach (var a in model.GetType().GetProperties()) { if (string.IsNullOrWhiteSpace(Convert.ToString(a.GetValue(model)))) { continue; } if (a.Name.ToLower() == mainKey.ToLower()) { if (string.IsNullOrWhiteSpace(Convert.ToString(a.GetValue(model)))) { return(0); } param.Add("@" + mainKey, a.GetValue(model).ToString()); continue; } setStr += $"{a.Name} = @{a.Name},"; param.Add("@" + a.Name, a.GetValue(model).ToString()); } if (!string.IsNullOrWhiteSpace(setStr)) { setStr = setStr.Substring(0, setStr.Length - 1); sql.Append(setStr); sql.Append($" where {mainKey} = @{mainKey} "); sqls.Add(sql); paramList.Add(param); } } using (var conn = new DBConfig().InitConn(DBName.ToString())) { IDbTransaction tran = conn.BeginTransaction(); for (int i = 0; i < sqls.Count; i++) { res += conn.Execute(sqls[i].ToString(), paramList[i], tran); } if (ErrorIsRollBack) { if (res < models.Count || res > models.Count) { tran.Rollback(); return(0); } } tran.Commit(); return(res); } }
/// <summary> /// 将实体插入到指定的数据库中 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="models">实体对象列表</param> /// <param name="DBName">数据库名称</param> /// <param name="tableName">表名,可空,空时为类型名</param> /// <param name="isSkip">是否跳过指定列,默认跳过</param> /// <param name="skipColumns">跳过的列名,默认为配置的列</param> /// <returns></returns> public static int InsertSql <T>(List <T> models, DBConfigEnum DBName, string tableName = "", bool?isSkip = null, string[] skipColumns = null) { StringBuilder sql = new StringBuilder(); string columns = ""; string values = ""; string valueStr = ""; if (models == null || models.Count == 0) { return(0); } if (isSkip == null) { isSkip = _isSkip; } if (skipColumns == null) { skipColumns = _InsertSkipColumns; } if (string.IsNullOrWhiteSpace(tableName)) { tableName = models[0].GetType().Name; } foreach (var a in models[0].GetType().GetProperties()) { if (isSkip == true) { bool isContinue = false; foreach (var c in skipColumns) { if (a.Name.ToLower() == c.ToLower()) { isContinue = true; break; } } if (isContinue) { continue; } } columns += a.Name + ","; } foreach (var model in models) { values = ""; foreach (var a in model.GetType().GetProperties()) { if (isSkip == true) { bool isContinue = false; foreach (var c in skipColumns) { if (a.Name.ToLower() == c.ToLower()) { isContinue = true; break; } } if (isContinue) { continue; } } if (string.IsNullOrWhiteSpace(Convert.ToString(a.GetValue(model)))) { values += "NULL,"; } else { values += $"'{a.GetValue(model).ToString()}',"; } } if (!string.IsNullOrWhiteSpace(values)) { values = values.Substring(0, values.Length - 1); valueStr += "\r\n select " + values + " union all "; } } if (!string.IsNullOrWhiteSpace(valueStr)) { valueStr = valueStr.Substring(0, valueStr.Length - (valueStr.Length - valueStr.LastIndexOf(" union all"))); } if (!string.IsNullOrWhiteSpace(columns)) { columns = columns.Substring(0, columns.Length - 1); } sql.Append($" insert into {tableName}({columns}) {valueStr} "); using (var conn = new DBConfig().InitConn(DBName.ToString())) { return(conn.Execute(sql.ToString())); } }