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