public ExeResEdm ExecuteTransaction(List <SqlContianer> ltSqls, DBOperUser dbLogMsg = null) { var res = ExecuteNonQueryFromSqlContianer(ltSqls); WriteLogMsg(dbLogMsg, LogType.事务, "根据" + GetRealSql(ltSqls) + "执行事务,受影响行数为" + res.ExeNum, "ExecuteTransaction方法"); return(res); }
public ExeResEdm GetListByPage(string tableName, PageSerach <T> para, DBOperUser dbLogMsg = null) { var orderByStr = LambdaToSqlHelper <T> .GetSqlFromLambda(para.OrderBy).OrderbySql; string whereSql = !string.IsNullOrEmpty(para.StrWhere) ? para.StrWhere : LambdaToSqlHelper <T> .GetWhereFromLambda(para.Filter, DBStoreType.NoSelect); SearchParam searchParam = new SearchParam() { Orderby = orderByStr, PageIndex = para.PageIndex, PageSize = para.PageSize, TableName = tableName, StrWhere = whereSql, }; ExeResEdm res = GetDTByPage(searchParam); int curNum = 0; if (res.ErrCode == 0) { List <T> list = DtModelConvert <T> .DatatableToList((res.ExeModel as DataTable)); res.ExeModel = list.AsQueryable(); curNum = list.Count(); res.ExeNum = searchParam.TotalCount; } WriteLogMsg(dbLogMsg, LogType.查询, "根据[" + DtModelConvert <T> .SerializeToString(searchParam) + "]获取了分页数据,返回了" + curNum + "/" + searchParam.TotalCount + "条记录", tableName); return(res); }
public ExeResEdm Update(string tableName, T model, List <string> whereParas, DBOperUser dbLogMsg = null, params string[] skipCols) { ComDBFun ComDBFun = new ComDBFun(DBBaseAttr); Dictionary <string, object> dic = DtModelConvert <T> .GetPropertity(model); // object[] values = dic.Values.ToArray(); string idVal = dic.Values.ToArray()[0].ToString(); for (int i = 0; i < skipCols.Length; i++)//自动增长的列要忽略 { dic.Remove(skipCols[i]); } for (int i = 0; i < whereParas.Count; i++) { try { var curKey = dic.Where(a => a.Key.Equals(whereParas[i], StringComparison.OrdinalIgnoreCase)).FirstOrDefault().Key; dic.Remove(curKey); } catch { } } for (int i = dic.Values.Count - 1; i >= 0; i--)//比较值为空的不参与比较 { if (dic.Values.ToList()[i] == null) { dic.Remove(dic.Keys.ToList()[i]); } } return(Update(tableName, dic, null, dbLogMsg)); }
public ExeResEdm ExecuteStoredProcedure(string storedProcedureName, DBOperUser dbLogMsg = null, params DbParameter[] parameters) { #region 存储过程例子,建议将输出参数以select形式输出 //CREATE PROCEDURE[dbo].[getInsertLog] @userid nvarchar(100), @bok INT OUTPUT //AS //BEGIN //SET NOCOUNT ON; //INSERT INTO Log_OperateTrace // ([LogTime], [UserID], [UserName] , [LogType] , [SystemID] , [ServerHost] // , [ServerIP], [ClientHost], [ClientIP] , [TabOrModu], [Detail], [Remark]) //VALUES // (CONVERT(varchar, GETDATE(),120), @userid, @userid ,3 ,10 ,'ServerHost' // ,'ServerIP' ,'ClientHost' ,'ClientIP' ,'TabOrModu','<Detail' ,'<Remark') //set @bok = 1; //SELECT @bok; //SELECT[ID] FROM Log_OperateTrace WHERE[ID] = SCOPE_IDENTITY(); //SELECT* FROM Log_OperateTrace //END #endregion 存储过程例子,建议将输出参数以select形式输出 DataSet ds = new DataSet(); parameters = ParameterPrepare(parameters); var res = SqlCMD_DT(storedProcedureName, CommandType.StoredProcedure, adt => adt.Fill(ds), parameters); res.ExeModel = ds; try { WriteLogMsg(dbLogMsg, LogType.存储过程, "名为" + storedProcedureName + ",得到" + ds.Tables.Count + "张表", "ExecuteStoredProcedure方法"); } catch { } return(res); }
public ExeResEdm Exist(string tableName, T model, List <string> whereParas, DBOperUser dbLogMsg = null) { whereParas = whereParas.Where(a => !string.IsNullOrEmpty(a)).Select(a => a.ToLower()).Distinct().ToList(); Dictionary <string, object> whereDic = DtModelConvert <T> .GetPropertity(model); whereDic = whereDic.Where(a => whereParas.Contains(a.Key.ToLower())).ToDictionary(k => k.Key, v => v.Value); return(Exist(tableName, whereDic, dbLogMsg)); }
public ExeResEdm SelectDBTableFormat(string tableName, DBOperUser dbLogMsg = null, string strField = "*") { string strSqlTxt = GetColumnsNameSql(tableName, strField); ExeResEdm dtFb = GetDataTable(strSqlTxt); WriteLogMsg(dbLogMsg, LogType.查询, "获取了表结构", tableName); return(dtFb); }
//批量进行添加/更新/删除 public ExeResEdm AddUpdateDelete(DBOperUser dbLogMsg = null, params AddUpdateDelEdm[] models) { if (models != null && models.Length > 0) { models = models.Select(a => { a.TableName = !string.IsNullOrEmpty(a.TableName) ? a.TableName : tableName; return(a); }).ToArray(); } return(baseDB.AddUpdateDelete(dbLogMsg, models)); }
public ExeResEdm ExecuteScalar(string cmdText, DBOperUser dbLogMsg = null, params DbParameter[] parameters) { ExeResEdm dBResEdm = SqlCMD(cmdText, cmd => cmd.ExecuteScalar(), parameters); dBResEdm.ExeNum = 1; WriteLogMsg(dbLogMsg, LogType.ExecuteScalar, "根据" + GetRealSql(cmdText, parameters) + "执行,结果为" + dBResEdm.ExeModel, "ExecuteScalar方法"); return(dBResEdm); }
public ExeResEdm Add(string tableName, List <T> list, DBOperUser dbLogMsg = null, string strComFields = "*") { var dt = GetDataTable(tableName, null, list, null); var res = UpdateDtToDB(dt, strComFields); WriteLogMsg(dbLogMsg, LogType.批量插入, "参数为:" + DtModelConvert <List <T> > .SerializeToString(list) + ",受影响的行数为" + res.ExeNum, tableName); return(res); }
//获取DataSet数据 public ExeResEdm GetDataSet(List <SqlContianer> ltSqls, DBOperUser dbLogMsg = null) { ltSqls = ltSqls ?? new List <SqlContianer>(); ltSqls = ltSqls.Select(a => { var tb = ComDBFun.GetTableNameFromSelectSql(a.strSqlTxt); if (string.IsNullOrEmpty(tb)) { a.strSqlTxt = "select * from " + tableName + " where " + a.strSqlTxt; } return(a); }).ToList(); return(baseDB.GetDataSet(ltSqls, dbLogMsg)); }
public ExeResEdm GetDataSet(string cmdText, DBOperUser dbLogMsg = null, params DbParameter[] parameters) { DataSet ds = new DataSet(); parameters = ParameterPrepare(parameters); var res = SqlCMD_DT(cmdText, CommandType.Text, adt => adt.Fill(ds), parameters); res.ExeModel = ds.Copy(); res.ExeNum = ds.Tables.Count; WriteLogMsg(dbLogMsg, LogType.查询, "根据" + GetRealSql(cmdText, parameters) + "获取了" + ds.Tables.Count + "张表", "GetDataSet方法"); return(res); }
public ExeResEdm ExecuteNonQuery(string cmdText, DBOperUser dbOperUser = null, params DbParameter[] parameters) { LogTraceEdm logMsg = null; if (dbOperUser != null) { logMsg = new LogTraceEdm() { LogType = LogType.ExecuteNonQuery, UserId = dbOperUser.UserId, UserName = dbOperUser.UserName, TabOrModu = "ExecuteNonQuery方法", }; } var n = ExecuteNonQuery(cmdText, logMsg, parameters); return(n); }
//获取DataSet数据 public ExeResEdm GetDataSet(string cmdText, DBOperUser dbLogMsg = null, params DbParameter[] parameters) { string sql = ""; cmdText = string.IsNullOrEmpty(cmdText) ? "" : cmdText.Trim(); if (cmdText.StartsWith("select ", StringComparison.OrdinalIgnoreCase) || cmdText.StartsWith("with ", StringComparison.OrdinalIgnoreCase)) { sql = cmdText; } else { sql += " where " + cmdText; } return(baseDB.GetDataSet(cmdText, dbLogMsg, parameters)); }
void WriteLogMsg(DBOperUser logUser, LogType logType, string detail, string module) { if (logUser != null && !string.IsNullOrEmpty(detail)) { LogTraceEdm logTraceEdm = new LogTraceEdm() { Detail = detail, TabOrModu = module, LogType = logType }; if (logUser != null) { logTraceEdm.UserId = logUser.UserId; logTraceEdm.UserName = logUser.UserName; } WriteLogMsg(logTraceEdm, null, null); } }
//根据id软删除 public ExeResEdm SoftDelete(object id, DBOperUser dbLogMsg = null) { if (deleteKeys != null || deleteKeys.Count == 1) { Dictionary <string, object> whereParas = new Dictionary <string, object>() { { deleteKeys[0], id } }; return(SoftDelete(whereParas, dbLogMsg)); } else { return(new ExeResEdm() { ErrCode = 1, ErrMsg = "必须是唯一主键才能根据ID删除", Module = tableName }); } }
public ExeResEdm GetDataSet(List <SqlContianer> ltSqls, DBOperUser dbLogMsg = null) { ExeResEdm exeRes = new ExeResEdm(); int n = 0; try { exeRes = GetDataSets(ltSqls); n = (exeRes.ExeModel as DataSet).Tables.Count; } catch (Exception ex) { exeRes.ExBody = ex; exeRes.ErrCode = 1; exeRes.Module = "GetDataSet方法"; } WriteLogMsg(dbLogMsg, LogType.查询, "根据" + GetRealSql(ltSqls) + "获取了" + n + "张表", "GetDataSet方法"); return(exeRes); }
public ExeResEdm Add(string tableName, T model, DBOperUser dbLogMsg = null, params string[] skipCols) { Dictionary <string, object> dic = DtModelConvert <T> .GetPropertity(model); object[] values = dic.Values.ToArray(); // string idVal = dic.Values.ToArray()[0].ToString(); //SqlParameter[] pms = GetOleDbParameters(dic.Keys.ToList(), dic.Values.ToList());//参数过多,不会影响程序执行的正确性。 for (int i = 0; i < skipCols.Length; i++)//自动增长的列要忽略 { dic.Remove(skipCols[i]); } for (int i = dic.Values.Count - 1; i >= 0; i--)//值为空的不参与 { if (dic.Values.ToList()[i] == null) { dic.Remove(dic.Keys.ToList()[i]); } } ComDBFun ComDBFun = new ComDBFun(DBBaseAttr); string textParas = ComDBFun.GetSQLText(dic.Keys.ToList(), null); string sql = "insert into " + tableName + textParas; DbParameter[] pms = GetDbParametersFromDic(dic); LogTraceEdm logMsg = null; if (dbLogMsg != null) { logMsg = new LogTraceEdm() { LogType = LogType.添加, UserId = dbLogMsg.UserId, UserName = dbLogMsg.UserName, TabOrModu = tableName, }; } var n = ExecuteNonQuery(sql, logMsg, pms); return(n); }
//获取分页数据 public ExeResEdm GetAll(PageSerach <T> para, DBOperUser dbLogMsg = null) { var data = baseDB.GetListByPage(tableName, para, dbLogMsg); return(data); }
//执行事务 public ExeResEdm ExecuteTransaction(List <SqlContianer> ltSqls, DBOperUser dbLogMsg = null) { return(baseDB.ExecuteTransaction(ltSqls, dbLogMsg)); }
//执行存储过程 public ExeResEdm ExecuteStoredProcedure(string storedProcedureName, bool bOutputDT = true, DBOperUser dbLogMsg = null, params DbParameter[] parameters) { return(baseDB.ExecuteStoredProcedure(storedProcedureName, bOutputDT, dbLogMsg, parameters)); }
//执行ExecuteScalar语句 public ExeResEdm ExecuteScalar(string cmdText, DBOperUser dbLogMsg = null, params DbParameter[] parameters) { return(baseDB.ExecuteScalar(cmdText, dbLogMsg, parameters)); }
//根据条件软删除 public ExeResEdm SoftDelete(Dictionary <string, object> whereParas, DBOperUser dbLogMsg = null) { return(baseDB.SoftDelete(tableName, whereParas, softDelFalg, dbLogMsg)); }
//软删除某个实体 public ExeResEdm SoftDelete(T model, DBOperUser dbLogMsg = null) { return(baseDB.SoftDelete(tableName, model, deleteKeys, softDelFalg, dbLogMsg)); }
//批量更新 public ExeResEdm Update(List <T> list, List <Dictionary <string, string> > updateFDList, DBOperUser dbLogMsg = null, string strComFields = "*") { return(baseDB.Update(tableName, list, updateFDList, dbLogMsg, strComFields)); }
//根据字段更新实体 public ExeResEdm Update(T model, List <string> whereParas, DBOperUser dbLogMsg = null) { return(baseDB.Update(tableName, model, updateKeys, dbLogMsg, skipCols)); }
//检查指定条件的数据是否存在 public ExeResEdm Exist(Dictionary <string, object> whereParas, DBOperUser dbLogMsg = null) { return(baseDB.Exist(tableName, whereParas, dbLogMsg)); }
//获取一个数据表的表结构 public ExeResEdm SelectDBTableFormat(DBOperUser dbLogMsg = null, string strField = "*") { return(baseDB.SelectDBTableFormat(tableName, dbLogMsg, strField)); }
//批量添加实体 public ExeResEdm Add(List <T> list, DBOperUser dbLogMsg = null) { return(baseDB.Add(tableName, list, dbLogMsg)); }
//检查某个实体是否存在 public ExeResEdm Exist(T model, DBOperUser dbLogMsg = null) { return(baseDB.Exist(tableName, model, updateKeys, dbLogMsg)); }
//根据字段更新 public ExeResEdm Update(Dictionary <string, object> whereParas, Dictionary <string, object> updateFDList, DBOperUser dbLogMsg = null) { return(baseDB.Update(tableName, whereParas, updateFDList)); }