public ExeResEdm Exist(string tableName, Dictionary <string, object> whereParas, DBOperUser dbLogMsg = null)
{
ComDBFun ComDBFun = new ComDBFun(DBBaseAttr);
string whereSql = ComDBFun.GetWhereCondition(whereParas.Keys.ToList(), "and");
DbParameter[] pms = GetDbParametersFromDic(whereParas);
string sql = "select count(0) from " + tableName + whereSql;
var res = ExecuteScalar(sql, dbLogMsg, pms);
if (res.ErrCode == 0)
{
try
{
int n = (int)res.ExeModel;
res.ExeNum = n;
res.ExeModel = n > 0;
return(res);
}
catch (Exception ex)
{
res.ErrCode = 1; res.ExBody = ex;
res.Module = "Exist方法";
}
}
return(res);
}
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 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 Update(string tableName, Dictionary <string, object> whereParas, Dictionary <string, object> updateFDList, DBOperUser dbLogMsg = null)
{
ComDBFun ComDBFun = new ComDBFun(DBBaseAttr);
updateFDList = updateFDList ?? new Dictionary <string, object>();
var paras = whereParas.Union(updateFDList).ToDictionary(k => k.Key, v => v.Value);
DbParameter[] pms = GetDbParametersFromDic(paras);
string textParas = ComDBFun.GetUpdateSQLText(updateFDList.Keys.ToList());
string whereSql = ComDBFun.GetWhereCondition(whereParas.Keys.ToList(), "and");
textParas += whereSql; // " where " + dic.Keys.ToArray()[skipIndex] + "=@" + dic.Keys.ToArray()[skipIndex];
string sql = "update " + tableName + " set " + textParas;
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 AdoNetBaseDal()
{
tableName = CurDalParas.TableName;
primaryKey = CurDalParas.PrimaryKey;
skipCols = CurDalParas.SkipCols;
updateKeys = CurDalParas.UpdateKeys;
deleteKeys = CurDalParas.DeleteKeys;
orderby = CurDalParas.Orderby;
conStr = ComDBFun.GetConnectionString(CurDalParas.DBType);
GetBaseDBByDBType();
}
protected DbParameter[] GetDbParametersFromDic(Dictionary <string, object> dic)
{
List <DbParameter> list = new List <DbParameter>();
if (dic == null || dic.Count <= 0)
{
return(list.ToArray());
}
List <string> colNames = dic.Keys.ToList(); List <object> colValues = dic.Values.ToList();
for (int i = 0; i < dic.Count; i++)
{
DbParameter cur = GetOneDbParameter(DBBaseAttr.ParaPreChar + ComDBFun.RemoveSpecialChar(colNames[i]), GetValue(colValues[i]));
list.Add(cur);
}
return(list.ToArray());
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
var dbGeneral = ComDBFun.GetDBGeneralInfo(DBType.LogTrace);
// if (dbGeneral.DataBaseType != DataBaseType.Oracle)
{
Database.SetInitializer(new Log_OperateTraceInitializer());//oracle 不建议使用EF，会导致字段名和数据库名必须加引号
}
modelBuilder.Configurations.Add(new Log_OperateTraceMap());
// modelBuilder.Entity<Log_OperateTrace>().HasKey(s => s.Id);
if (!string.IsNullOrEmpty(dbGeneral.SchemaName))
{
modelBuilder.HasDefaultSchema(dbGeneral.SchemaName.ToUpper());
}
base.OnModelCreating(modelBuilder);
}
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);
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
var dbGeneral = ComDBFun.GetDBGeneralInfo(DBType.LogMonitor);
// if (dbGeneral.DataBaseType != DataBaseType.Oracle)
{
Database.SetInitializer(new Log_SystemMonitorInitializer());
}
modelBuilder.Configurations.Add(new Log_SystemMonitorMap());
// modelBuilder.Entity<Log_SystemMonitor>().Property(a => a.DiskSpace).HasColumnType("xml"); //非空类型才能用作泛型参数
if (!string.IsNullOrEmpty(dbGeneral.SchemaName))
{
modelBuilder.HasDefaultSchema(dbGeneral.SchemaName.ToUpper());
}
base.OnModelCreating(modelBuilder);
}
public ExeResEdm Delete(string tableName, Dictionary <string, object> whereParas, DBOperUser dbLogMsg = null)
{
ComDBFun ComDBFun = new ComDBFun(DBBaseAttr);
string whereSql = ComDBFun.GetWhereCondition(whereParas.Keys.ToList(), "and", whereParas);
string sql = "delete " + tableName + whereSql;
DbParameter[] pms = GetDbParametersFromDic(whereParas);
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 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);
}
protected string GetTableNameFromSelectSql(string selectSql)
{
return(ComDBFun.GetTableNameFromSelectSql(selectSql));
}
/// <summary>
/// 注册日志组件到本系统，为日志组件准备基础信息：服务器IP、服务器主机名，系统名称等
/// </summary>
/// <param name="sys">业务系统类型</param>
/// <param name="application">应用程序的Application对象</param>
/// <param name="bWriteStartLog">是否是启动日志</param>
/// <param name="bLogMonitor">是否写定时监控日志</param>
public static void RegisterLogInitMsg(SysCategory sys, object applicationObj = null, UserCfg userConfig = null, Dictionary <SysCategory, string> webAppName = null, bool bWriteStartLog = true, bool bLogMonitor = true)
{
webAppNames = webAppName ?? new Dictionary <SysCategory, string>();
new HttpCacheHelper().SetCache(AppConfig.GetCacheKey(CacheConst.userCfgInCode), userConfig, expireType: Expire.Month);
userCfg = GetUserConfigItem();
userCfg = userCfg ?? new UserCfg();
dataCache = CacheFac.CacheFactory();
appender = AppenderFac.AppenderFactory();
logLevelCfg = AppConfig.GetFinalConfig("log2NetLevel", LogLevel.Debug, GetLog2NetLevel());
if (logLevelCfg == LogLevel.Off)
{
return;
}
// AutoMapperConfig.Configure();//注册AutoMapper
#if NET
bRegister = true;
ApplicationVisitCount.ApplicationObj = applicationObj;
#endif
//var machineName = System.Web.HttpContext.Current.Server.MachineName;//服务器计算机名
var machineName = Environment.MachineName;//服务器计算机名
var server = ClientServerInfo.ClientInfo.GetIPAccordingHost(machineName);
string serverIP = server.IP;
dataCache.SetCache(AppConfig.GetCacheKey(CacheConst.serverIP), serverIP, expireType: Expire.Month);
dataCache.SetCache(AppConfig.GetCacheKey(CacheConst.serverHost), machineName, expireType: Expire.Month);
dataCache.SetCache(AppConfig.GetCacheKey(CacheConst.systemName), sys, expireType: Expire.Month);
#region 使用EF自动创建数据库
try
{
var initTraceDBWhenOracle = AppConfig.GetFinalConfig("initTraceDBWhenOracle", false, LogApi.IsInitTraceDBWhenOracle());
// initTraceDBWhenOracle = "1";
var traceDBType = ComDBFun.GetDBGeneralInfo(DBType.LogTrace).DataBaseType;
if (traceDBType != DataBaseType.Oracle || (traceDBType == DataBaseType.Oracle && initTraceDBWhenOracle))
{
using (var context = new Log_OperateTraceContext()) //oracle 不建议使用EF，会导致字段名和数据库名必须加引号
{
#if NET
context.Database.Initialize(true); //EF6
#else
context.Database.EnsureCreated(); //EFCore
#endif
}
}
var initMonitorDBWhenOracle = AppConfig.GetFinalConfig("initMonitorDBWhenOracle", false, LogApi.IsInitMonitorDBWhenOracle());
// initMonitorDBWhenOracle = "1";
var monitorDBType = ComDBFun.GetDBGeneralInfo(DBType.LogMonitor).DataBaseType;
if (monitorDBType != DataBaseType.Oracle || (monitorDBType == DataBaseType.Oracle && initMonitorDBWhenOracle))
{
using (var context = new Log_SystemMonitorContext())
{
#if NET
context.Database.Initialize(true); //EF6
#else
context.Database.EnsureCreated(); //EFCore
#endif
}
}
}
catch (Exception ex)
{
WriteExceptToFile(ex);
}
#endregion 使用EF自动创建数据库
if (bWriteStartLog)
{
WriteServerStartupLog();//系统启动的日志
}
StartThreadToWriteFileToAppender(); //开启线程，将备份日志写到Appender中
//在线人数和访客人数的初始化
VisitOnline.VisitCountFactory.GetInstance().SetVisitNumWhenInit();
if (bLogMonitor)
{
WriteMonitorLogThread();
}
}
public Log_SystemMonitorContext() : base("name=" + ComDBFun.GetConnectionStringKey(DBType.LogMonitor))
{
}
public Log_OperateTraceContext() : base("name=" + ComDBFun.GetConnectionStringKey(DBType.LogTrace))
{
}
