public CRUDSql GetSelectSql(T searchPara, string tableName, string orderBy, List <string> selectFields = null) { // ComDBFun ComDBFun = new ComDBFun(bOrcl); Dictionary <string, object> dic = DtModelConvert <T> .GetPropertity(searchPara); List <string> whereParas = dic.Keys.ToList(); object[] values = dic.Values.ToArray(); for (int i = dic.Values.Count - 1; i >= 0; i--)//比较值为空的不参与比较 { if (dic.Values.ToList()[i] == null || string.IsNullOrEmpty(dic.Values.ToList()[i].ToString())) { whereParas.Remove(dic.Keys.ToList()[i]); dic.Remove(dic.Keys.ToList()[i]); } } string whereSql = new ComDBFun(DBBaseAttr).GetWhereCondition(whereParas, "and"); string fds = (selectFields == null || selectFields.Count <= 0) ? "*" : string.Join(",", selectFields); orderBy = string.IsNullOrEmpty(orderBy) ? "" : "order by " + orderBy; string sql = string.Format("select {0} from {1} {2} {3}", fds, tableName, whereSql, orderBy); CRUDSql res = new CRUDSql() { Sql = sql }; res.PMS = GetDbParametersFromDic(dic); 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 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 GetListByPage(string tableName, PageSerach <T> para) { var orderByStr = LambdaToSqlHelper <T> .GetSqlFromLambda(para.OrderBy).OrderbySql; string whereSql = !string.IsNullOrEmpty(para.StrWhere) ? para.StrWhere : LambdaToSqlHelper <T> .GetWhereFromLambda(para.Filter, DataBaseType.NoSelect); SearchParam searchParam = new SearchParam() { Orderby = orderByStr, PageIndex = para.PageIndex, PageSize = para.PageSize, TableName = tableName, StrWhere = whereSql, }; ExeResEdm res = GetDTByPage(searchParam); para.StrWhere = whereSql; if (res.ErrCode == 0) { List <T> list = DtModelConvert <T> .DatatableToList((res.ExeModel as DataTable)); res.ExeModel = list.AsQueryable(); return(res); } else { return(res); } }
public ExeResEdm Add(string tableName, T model, 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()); string sql = "insert into " + tableName + textParas; // SqlParameter[] pms = ComDBFun.GetMSOleDbParameters(dic.Keys.ToList(), dic.Values.ToList()); DbParameter[] //pms = ComDBFun.GetOrclOleDbParameters(dic.Keys.ToList(), dic.Values.ToList()); pms = GetDbParametersFromDic(dic); var n = ExecuteNonQuery(sql, pms); return(n); }
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 Update(string tableName, List <T> list, List <Dictionary <string, string> > updateFDList, DBOperUser dbLogMsg = null, string strComFields = "*") { var dt = GetDataTableComplex(tableName, null, list, updateFDList); var res = UpdateDtToDB(dt, strComFields); WriteLogMsg(dbLogMsg, LogType.批量修改, "参数为:" + DtModelConvert <object> .SerializeToString(new { Data = list, UpdateFDs = updateFDList }) + ",受影响的行数为" + res.ExeNum, tableName); return(res); }
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); }
//批量添加/更新/删除数据时所使用的的 DataTable,更新使用的是不同值值 DataTable GetDataTableComplex <V>(string tableName, DataTable dt, List <V> paras, List <Dictionary <string, string> > updateDicList = null) { //DtModelConvertHelper<T>.ModelListToDT(paras); //不行 if (dt == null || dt.Columns.Count <= 0) { dt = (SelectDBTableFormat(tableName).ExeModel) as DataTable; } else { dt = dt.Clone(); } dt.TableName = tableName; foreach (var item in paras) { DataRow dr = dt.NewRow(); dr = DtModelConvert <V> .ObjConvertToDr(item, dr); dt.Rows.Add(dr); } List <string> errList = new List <string>(); if (updateDicList != null && updateDicList.Count > 0)//是更新 { dt.AcceptChanges(); //检查和更新列名 //List<string> dtCols = new List<string>(); //foreach (DataColumn item in dt.Columns) //{ // dtCols.Add(item.ColumnName); //} if (errList.Count > 0) { throw new Exception("字段[" + string.Join(" , ", errList) + "]不是" + tableName + "表的字段,无法更新"); } for (int i = 0; i < dt.Rows.Count; i++) { DataRow item = dt.Rows[i]; var updateDic = updateDicList.Count == 1 ? updateDicList[0] : updateDicList[i]; if (updateDic == null || updateDic.Count <= 0) { continue; } foreach (var col in updateDic) { item[col.Key] = col.Value; } } } return(dt); }
public CRUDSql GetInsertSql <M>(M model, string tableName, bool bParameterizedQuery) { Dictionary <string, object> dic = DtModelConvert <T> .GetPropertity(model); ComDBFun ComDBFun = new ComDBFun(DBBaseAttr); string textParas = ComDBFun.GetSQLText(dic.Keys.ToList(), (bParameterizedQuery ? null : dic.Values.ToList())); string sql = "insert into " + tableName + textParas; CRUDSql insertSql = new CRUDSql() { Sql = sql }; if (bParameterizedQuery) { insertSql.PMS = GetDbParametersFromDic(dic); } return(insertSql); }
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 AddUpdateDelete(DBOperUser dbLogMsg = null, params AddUpdateDelEdm[] datas) { DataSet ds = new DataSet(); if (datas == null || datas.Length <= 0) { return(new ExeResEdm() { ErrCode = 1, Module = "AddUpdateDelete方法", ErrMsg = "没有有效的参数", }); } datas = datas.Where(a => a.Datas != null && a.Datas.Count > 0).ToArray(); if (datas.Length <= 0) { return(new ExeResEdm() { ErrCode = 1, Module = "AddUpdateDelete方法", ErrMsg = "没有有效的参数", }); } var models = datas.ToList(); //若TableName相同,则合并 models = models.Select(a => { a.TableName = a.TableName.Trim().ToLower().Trim(); return(a); }).ToList(); var groups = models.GroupBy(a => a.TableName).ToList(); List <AddUpdateDelEdm> realModels = new List <AddUpdateDelEdm>(); Dictionary <string, string> dtPKs = new Dictionary <string, string>(); models.Clear(); foreach (var item in groups) { AddUpdateDelEdm cur = new AddUpdateDelEdm() { TableName = item.Key, }; cur.Datas = new List <object>(); cur.UpdateFD = new List <Dictionary <string, string> >(); var itemList = item.ToList(); bool[] bAdd = new bool[itemList.Count]; for (int i = 0; i < itemList.Count; i++) { var m = itemList[i]; cur.MainFields = !string.IsNullOrEmpty(m.MainFields) ? m.MainFields : cur.MainFields; if (m.UpdateFD != null && m.UpdateFD.Count == m.Datas.Count) { cur.Datas.AddRange(m.Datas); cur.UpdateFD.AddRange(m.UpdateFD); bAdd[i] = false; } else { foreach (var d in m.Datas) { cur.Datas.Add(d); if (m.UpdateFD == null || m.UpdateFD.Count <= 0) { // cur.UpdateFD.Add(new Dictionary<string, string>() { }); bAdd[i] = true; } else if (m.UpdateFD.Count == 1) { cur.UpdateFD.Add(m.UpdateFD[0]); bAdd[i] = false; } } } dtPKs.Add(cur.TableName, cur.MainFields); } if (bAdd.Distinct().Count() != 1) { ExeResEdm exeResEdm = new ExeResEdm() { ErrCode = 1, ExBody = new Exception("同一张表[" + item.Key + "],不能同时有添加和更新操作"), Module = "AddUpdate 方法", }; return(exeResEdm); } models.Add(cur); } //获取所有的datatable模板 List <SqlContianer> ltSqls = models.Select(a => new SqlContianer() { tableName = a.TableName, strSqlTxt = GetColumnsNameSql(a.TableName) }).ToList(); var dsTemplate = GetDataSets(ltSqls).ExeModel as DataSet; if (dsTemplate == null || dsTemplate.Tables.Count <= 0) { return(new ExeResEdm() { ErrCode = 1, ErrMsg = "未获取到数据", Module = "AddUpdateDelete方法" }); } foreach (var item in models) { DataTable dt = new DataTable(); DataTable dtTemplate = dsTemplate.Tables[item.TableName]; if (item.UpdateFD == null || item.UpdateFD.Count == 0) { dt = GetDataTable(item.TableName, dtTemplate, item.Datas); } else if (item.UpdateFD.Count == 1) { dt = GetDataTable(item.TableName, dtTemplate, item.Datas, item.UpdateFD[0]); } else { dt = GetDataTableComplex(item.TableName, dtTemplate, item.Datas, item.UpdateFD); } ds.Tables.Add(dt); } var res = UpdateDsToDB(ds, dtPKs); WriteLogMsg(dbLogMsg, LogType.批量增删改, "参数为:" + DtModelConvert <List <AddUpdateDelEdm> > .SerializeToString(models) + ",受影响的行数为" + res.ExeNum, "AddUpdateDelete方法"); return(res); }
public IActionResult About() { ViewData["Message"] = "Your application description page."; var dic = Log2Net.LogApi.GetLogWebApplicationsName(); var curDal = new Log_OperateTraceAdoDal(); DBOperUser dbUser = new DBOperUser() { UserId = "CNNO2", UserName = "******" }; #region 测试获取数据 var dbData = curDal.GetAll(new LogORM.Models.PageSerach <Log_OperateTrace>() { Filter = a => a.Id > 0, OrderBy = a => a.OrderByDescending(m => m.LogTime), PageSize = 2 }); var dbModels = DtModelConvert <List <Log_OperateTrace> > .DeepClone(dbData.ExeModel); var selectModels = dbModels.ConvertAll(a => (object)a); #endregion 测试获取数据 #region 测试添加更新删除 LogORM.Models.AddDBPara <Log_OperateTrace> addDBPara = new LogORM.Models.AddDBPara <Log_OperateTrace>() { Model = new Log_OperateTrace() { ClientHost = "江南可采莲", Detail = "鱼戏莲叶间", ClientIP = "鱼戏莲叶东西南北中间", LogTime = System.DateTime.Now, LogType = LogType.添加, ServerHost = "鱼戏莲叶下上左右后前", ServerIP = "鱼莲玩嗨乐翻天", SystemID = SysCategory.SysA_02, TabOrModu = "莲叶变黄了_DNC", UserID = "鱼还没戏够", UserName = "******", } }; var resAdd = curDal.Add(addDBPara, new DBOperUser() { UserId = "CN666", UserName = "******" }); var resUpdate = curDal.Update(new Dictionary <string, object>() { { "id", 2 } }, new Dictionary <string, object>() { { "Detail", "后事如何,下回分解" } }); var delRes = curDal.Delete(new Dictionary <string, object>() { { "id", 2 } }, dbUser); var delRes2 = curDal.Delete(3, dbUser); #endregion 测试添加更新删除 #region 测试批量添加和更新 Log_OperateTrace curAddLog = new Log_OperateTrace() { ClientHost = "江南可采莲", Detail = "鱼戏莲叶间", ClientIP = "鱼戏莲叶东西南北中间", LogTime = System.DateTime.Now, LogType = LogType.添加, ServerHost = "鱼戏莲叶下上左右后前", ServerIP = "鱼莲玩嗨乐翻天", SystemID = SysCategory.SysA_02, TabOrModu = "莲叶变黄了_DNC", UserID = "鱼还没戏够", UserName = "******", }; List <AddUpdateDelEdm> AddUpdateDelEdms = new List <AddUpdateDelEdm>(); AddUpdateDelEdms.Add(new AddUpdateDelEdm() { TableName = "Log_OperateTrace", Datas = new List <object>() { curAddLog } }); var resBtAdd = curDal.AddUpdateDelete(new DBOperUser() { UserId = "CN1234", UserName = "******" }, AddUpdateDelEdms.ToArray()); //以下为更新 AddUpdateDelEdms.Add(new AddUpdateDelEdm() { TableName = "Log_OperateTrace", Datas = selectModels }); AddUpdateDelEdms[0].Datas = (selectModels); AddUpdateDelEdms[0].UpdateFD = new List <Dictionary <string, string> > { new Dictionary <string, string> { { "ServerIP", "1.1.1.1" } } }; var resBtUpdate = curDal.AddUpdateDelete(new DBOperUser() { UserId = "CN12348", UserName = "******" }, AddUpdateDelEdms.ToArray()); #endregion 测试批量添加和更新 #region 测试存储过程 DbParameter[] spParameters = new SqlParameter[] { new SqlParameter("@userid", "CN4096"), new SqlParameter("@bok", System.Data.SqlDbType.Int) }; spParameters[1].Direction = System.Data.ParameterDirection.Output; spParameters[1].Value = 0; var spRes = curDal.ExecuteStoredProcedure("getInsertLog", true, new DBOperUser() { UserId = "CN8192", UserName = "******" }, spParameters); #endregion 测试存储过程 #region 测试日志记录 LogTraceVM model = new LogTraceVM() { Detail = "所有的程序员都是天才编剧,所有的计算机都是烂演员", LogType = LogType.业务记录, Remark = "文学奖评选", TabOrModu = "计算机编程", }; var logRes = new ComClass().WriteLog(LogLevel.Info, model); #endregion 测试日志记录 //测试获取Sql var sql = curDal.CurSqlProvider.Select("username", "realname", "age").From("sys_user").Where <KeyValue>(a => a.Name == "username1").SqlString; return(View()); }