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());
}
