protected override ExeResEdm WriteLog(Log_SystemMonitorMQ model)
{
if (model == null)
{
return(new ExeResEdm());
}
// AdoSQLHelper<Log_SystemMonitor> sqlHelper = new AdoSQLHelper<Log_SystemMonitor>();
var dbAccessFac = new Log_SystemMonitorDBAccessFac().DBAccessFactory();
Log_SystemMonitor dbModel = AutoMapperConfig.GetLog_SystemMonitorModel(model);
AddDBPara <Log_SystemMonitor> addDBPara = new AddDBPara <Log_SystemMonitor>()
{
DBType = DBType.LogMonitor,
Model = dbModel,
SkipCols = new string[] { "Id" },
TableName = "Log_SystemMonitor",
};
try
{
var n = dbAccessFac.Add(addDBPara);
return(n);
}
catch (Exception ex)
{
ExeResEdm dbResEdm = new ExeResEdm()
{
ErrCode = 1, ExBody = ex, Module = "WriteLog方法"
};
return(dbResEdm);
}
}
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);
}
}
protected override ExeResEdm SqlCMD_DT(string cmdText, CommandType commandType, Func <DbDataAdapter, int> fun, params DbParameter[] parameters)
{
ExeResEdm dBResEdm = new ExeResEdm();
try
{
parameters = ParameterPrepare(parameters);
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdText;
cmd.CommandType = commandType;
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange((parameters));
}
var da = new SqlDataAdapter(cmd);
var res = fun(da);
dBResEdm.ExeNum = res;
}
}
catch (Exception ex)
{
dBResEdm.Module = "SqlCMD_DT 方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
protected override ExeResEdm WriteLog(Log_OperateTrace model)
{
if (model == null)
{
return(new ExeResEdm());
}
var item = model;
var dbAccessFac = new Log_OperateTraceDBAccessFac().DBAccessFactory();
AddDBPara <Log_OperateTrace> addDBPara = new AddDBPara <Log_OperateTrace>()
{
DBType = DBType.LogTrace,
Model = model,
SkipCols = new string[] { "Id" },
TableName = "Log_OperateTrace",
};
try
{
var n = dbAccessFac.Add(addDBPara);
return(n);
}
catch (Exception ex)
{
ExeResEdm dbResEdm = new ExeResEdm()
{
ErrCode = 1, ExBody = ex, Module = "WriteLog方法"
};
return(dbResEdm);
}
}
protected override ExeResEdm SqlCMD_DT(string cmdText, Func <DbDataAdapter, int> fun, params DbParameter[] parameters)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
ExeResEdm dBResEdm = new ExeResEdm();
try
{
parameters = ParameterPrepare(parameters);
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdText;
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange((parameters));
}
var da = new System.Data.OracleClient.OracleDataAdapter(cmd);
var n = fun(da);
dBResEdm.ExeNum = n;
}
}
catch (Exception ex)
{
dBResEdm.Module = "SqlCMD_DT 方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
protected override ExeResEdm SqlCMD(string sql, CommandType commandType, Func <DbCommand, object> fun, params DbParameter[] pms)
{
ExeResEdm dBResEdm = new ExeResEdm();
try
{
pms = ParameterPrepare(pms);
using (SqlConnection con = new SqlConnection(connstr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
cmd.CommandType = commandType;
if (pms != null && pms.Length > 0)
{
cmd.Parameters.AddRange((pms));
}
var res = fun(cmd);
dBResEdm.ExeModel = res;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "SqlCMD方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
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);
}
protected override ExeResEdm UpdateDtToDB(DataTable dtInfos, string strComFields = "*")
{
ExeResEdm dBResEdm = new ExeResEdm();
string strTableName = dtInfos.TableName;
try
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = GetColumnsNameSql(strTableName, strComFields);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.UpdateCommand = new SqlCommandBuilder(adapter).GetUpdateCommand();
adapter.Update(dtInfos.GetChanges());
dtInfos.AcceptChanges();
}
}
catch (Exception ex)
{
dBResEdm.Module = "UpdateDtToDB方法" + AddTableNameOrSqlText(strTableName);
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
protected override ExeResEdm ExecuteNonQueryFromSqlContianer(List <SqlContianer> ltSqls)
{
ExeResEdm dBResEdm = new ExeResEdm();
string curSQL = "";
try
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
SqlTransaction oraOprate = conn.BeginTransaction();
try
{
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = oraOprate;
foreach (SqlContianer objOraSqlCon in ltSqls)
{
cmd.CommandText = objOraSqlCon.strSqlTxt;
curSQL = objOraSqlCon.strSqlTxt;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(objOraSqlCon.ltOraParams.ToArray());
int intRes = cmd.ExecuteNonQuery();
dBResEdm.ExeNum += intRes;
if (objOraSqlCon.intExpectNums > 0)
{
if (intRes != objOraSqlCon.intExpectNums)
{
throw new Exception("Update records[" + intRes + "] not match the expect nums[" + objOraSqlCon.intExpectNums + "]");
}
}
else if (objOraSqlCon.intExpectNums < 0)
{
if (intRes != 0 && intRes != objOraSqlCon.intExpectNums * -1)
{
throw new Exception("Update records[" + intRes + "] not match the expect nums[" + objOraSqlCon.intExpectNums + "]");
}
}
}
oraOprate.Commit();
}
catch (Exception ex)
{
oraOprate.Rollback();
dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
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 SelectDBTableFormat(string tableName, DBOperUser dbLogMsg = null, string strField = "*")
{
string strSqlTxt = GetColumnsNameSql(tableName, strField);
ExeResEdm dtFb = GetDataTable(strSqlTxt);
WriteLogMsg(dbLogMsg, LogType.查询, "获取了表结构", tableName);
return(dtFb);
}
//select SQL_CALC_FOUND_ROWS * from log_systemmonitor where OnlineCnt >1 order by time DESC limit 0,5;
//SELECT FOUND_ROWS();
//SELECT SQL_CALC_FOUND_ROWS @rowno:=@rowno+1 as rowno,r.* from log_systemmonitor r,(select @rowno:= 0) t where OnlineCnt >1 order by time DESC;
//SELECT FOUND_ROWS();
protected override ExeResEdm GetDataByPage(string tableName, string strWhere, string orderby, int pageIndex, int pageSize, out int totalCnt)
{
totalCnt = 0;
StringBuilder strSql = new StringBuilder();
string columns = "";//为空，则获取全部列
if (string.IsNullOrEmpty(orderby) || string.IsNullOrEmpty(orderby.Trim()))
{
return(null);
}
if (string.IsNullOrEmpty(columns))
{
columns = "*";
}
columns = "SQL_CALC_FOUND_ROWS " + columns;
strSql.Append("SELECT " + columns + " FROM " + tableName);
if (!string.IsNullOrEmpty(strWhere) && !string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" order by " + orderby + " limit " + DBBaseAttr.ParaPreChar + "startIx," + DBBaseAttr.ParaPreChar + "endIx;");
strSql.Append("SELECT FOUND_ROWS();");
Dictionary <string, object> pageDic = new Dictionary <string, object>()
{
{ "startIx", pageSize *(pageIndex - 1) },
{ "endIx", pageSize }
};
DbParameter[] pms = GetDbParametersFromDic(pageDic);
try
{
string text = strSql.ToString();
ExeResEdm ds = GetDataSet(strSql.ToString(), null, pms);
if (ds != null && ds.ErrCode == 0 && (ds.ExeModel as DataSet).Tables.Count > 1)
{
totalCnt = Convert.ToInt32((ds.ExeModel as DataSet).Tables[1].Rows[0][0].ToString());
return(new ExeResEdm()
{
ExeModel = (ds.ExeModel as DataSet).Tables[0]
});
}
else
{
return(ds);
}
}
catch (Exception ex)
{
return(new ExeResEdm()
{
ErrCode = 1, ExBody = ex, Module = "GetDataByPage-MySql"
});
}
}
ExeResEdm ExecuteNonQuery(string cmdText, params DbParameter[] parameters)
{
ExeResEdm dBResEdm = SqlCMD(cmdText, cmd => cmd.ExecuteNonQuery(), parameters);
if (dBResEdm.ErrCode == 0)
{
dBResEdm.ExeNum = Convert.ToInt32(dBResEdm.ExeModel);
}
return(dBResEdm);
}
protected ExeResEdm ExecuteNonQuery(string cmdText, LogTraceEdm logMsg, params DbParameter[] parameters)
{
ExeResEdm dBResEdm = SqlCMD(cmdText, cmd => cmd.ExecuteNonQuery(), parameters);
if (dBResEdm.ErrCode == 0)
{
dBResEdm.ExeNum = Convert.ToInt32(dBResEdm.ExeModel);
}
WriteLogMsg(logMsg, dBResEdm.ExeNum, GetRealSql(cmdText, parameters));
return(dBResEdm);
}
//将日志发送到队列中
protected override ExeResEdm SendLogToQueue(object model, MQType mqType)
{
if (mqType == MQType.TraceLog)//是操作轨迹类数据
{
var item = model as Log_OperateTrace;
ExeResEdm bOK = RabbitMQManager.Send(item, mqType);
return(bOK);
}
else //是监控类数据
{
var item = model as Log_SystemMonitorMQ;
ExeResEdm bOK = RabbitMQManager.Send(item, mqType);
return(bOK);
}
}
//将数据记录到文件中，
//若是本地调试文件和日志，则文件名为日期，每行数据加时间；
//若是备份日志，每条记录一个文件，每行数据为Json;
internal static ExeResEdm WriteModelToFile <T>(T model, string typeName, FileType fileType)
{
ExeResEdm exeResEdm = new ExeResEdm()
{
};
string msg = SerializerHelper.SerializeToString(model);
try
{
string path = GetWholeFolderPath(fileType);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string txtFile = DateTime.Now.ToString(dateTimeFileFormat) + /* Guid.NewGuid().ToString("N") + "_" */ typeName + ".log";
if (txtFile.IndexOfAny(Path.GetInvalidFileNameChars()) >= 0)
{
typeName = "typeNameValid";
txtFile = DateTime.Now.ToString(dateFileFormat) + /* Guid.NewGuid().ToString("N") + "_" */ "typeNameValid.log";
msg = msg + "---" + typeName;
}
if (fileType != FileType.Backup /* fileType == FileType.Debug || fileType == FileType.File || fileType == FileType.Info*/)
{
txtFile = DateTime.Now.ToString(dateFileFormat) + typeName + ".log";
msg = DateTime.Now.ToString("HH:mm:ss.fff：") + msg;
}
txtFile = Path.Combine(path, txtFile);
if (fileType == FileType.Backup)
{
if (File.Exists(txtFile))
{
txtFile = DateTime.Now.ToString(dateTimeFileFormat) + DateTime.Now.Ticks + /* Guid.NewGuid().ToString("N") + "_" */ typeName + ".log";
txtFile = Path.Combine(path, txtFile);
}
}
System.IO.File.AppendAllLines(txtFile, new string[] { msg });
return(exeResEdm);
}
catch (Exception ex)
{
exeResEdm.ErrCode = 1;
exeResEdm.Module = "WriteModelToFile方法";
exeResEdm.ExBody = ex;
return(exeResEdm);
}
}
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);
}
//将日志发送到队列中
protected override ExeResEdm SendLogToQueue(object model, MQType mqType)
{
ExeResEdm exeResEdm = new ExeResEdm()
{
};
if (mqType == MQType.TraceLog)//是操作轨迹类数据
{
var item = model as Log_OperateTrace;
OperateLogQueue.Enqueue(item);
return(exeResEdm);
}
else //是监控类数据
{
var item = model as Log_SystemMonitorMQ;
MonitorLogQueue.Enqueue(item);
return(exeResEdm);
}
}
ExeResEdm GetDTByPage(SearchParam searchParam)
{
string strWhere = searchParam.StrWhere;
string orderby = searchParam.Orderby;
int pageSize = searchParam.PageSize;
int pageIndex = searchParam.PageIndex;
int totalCnt = 0;
if (string.IsNullOrEmpty(orderby) || string.IsNullOrEmpty(orderby.Trim()))
{
return(null);
}
ExeResEdm dtRes = GetDataByPage(searchParam.TableName, strWhere, orderby, pageIndex, pageSize, out totalCnt);
searchParam.TotalCount = totalCnt;
return(dtRes);
}
public ExeResEdm Add(AddDBPara <T> dBPara)
{
ExeResEdm dBResEdm = new ExeResEdm()
{
};
try
{
dBResEdm = Insert(dBPara.Model);
return(dBResEdm);
}
catch (Exception ex)
{
dBResEdm.Module = "Add 方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
ExeResEdm Insert(T entity)
{
ExeResEdm dBResEdm = new ExeResEdm()
{
};
try
{
var t = DbSet.Add(entity);
this.Context.SaveChanges();
dBResEdm.ExeModel = t;
dBResEdm.ExeNum = 1;
return(dBResEdm);
}
catch (Exception ex)
{
dBResEdm.Module = "Add 方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
//SQL Server 和 oracle 可以使用此方法，MySQL不行
protected virtual ExeResEdm GetDataByPage(string tableName, string strWhere, string orderby, int pageIndex, int pageSize, out int totalCnt)
{
totalCnt = 0;
StringBuilder strSql = new StringBuilder();
string columns = "";//为空，则获取全部列
if (string.IsNullOrEmpty(orderby) || string.IsNullOrEmpty(orderby.Trim()))
{
return(new ExeResEdm()
{
ErrCode = 1, ErrMsg = "orderby 参数不能为空", Module = "GetDataByPage"
});
}
if (string.IsNullOrEmpty(columns))
{
columns = "*";
}
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageSize * pageIndex;
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
strSql.Append("order by T." + orderby);
strSql.Append(")AS RowIx, T.* ,COUNT(*) OVER() AS dbtotal from " + tableName + " T ");
if (!string.IsNullOrEmpty(strWhere) && !string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.RowIx between " + DBBaseAttr.ParaPreChar + "startIx and " + DBBaseAttr.ParaPreChar + "endIx "); //ComDBFun.paraChar
Dictionary <string, object> pageDic = new Dictionary <string, object>()
{
{ "startIx", pageSize *(pageIndex - 1) + 1 },
{ "endIx", pageSize *pageIndex }
};
DbParameter[] pms = GetDbParametersFromDic(pageDic);
try
{
string text = strSql.ToString();
ExeResEdm res = GetDataTable(strSql.ToString(), pms);
if (res.ErrCode == 0 && res.ExeNum > 0)
{
totalCnt = Convert.ToInt32((res.ExeModel as DataTable).Rows[0]["dbtotal"].ToString());
}
return(res);
}
catch (Exception ex)
{
return(new ExeResEdm()
{
ErrCode = 1, ExBody = ex, Module = "GetDataByPage"
});
}
}
public ExeResEdm ExecuteStoredProcedure(string storedProcedureName, bool bOutputDT = true, 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);
ExeResEdm res = new ExeResEdm();
if (bOutputDT) //update insert 操作的存储过程，也可以使用 SqlCMD_DT 方法
{
res = SqlCMD_DT(storedProcedureName, CommandType.StoredProcedure, adt => adt.Fill(ds), parameters);
res.ExeModel = ds;
}
else
{
res = SqlCMD(storedProcedureName, CommandType.StoredProcedure, cmd => cmd.ExecuteNonQuery(), parameters);
}
List <string> resultList = new List <string>();
if (ds != null && ds.Tables.Count > 0)
{
int rowCnt = 0;
foreach (DataTable item in ds.Tables)
{
if (item != null)
{
rowCnt += item.Rows.Count;
}
}
resultList.Add("得到" + ds.Tables.Count + "张表" + rowCnt + "条数据");
}
else
{
resultList.Add("影响了" + res.ExeModel + "条记录");
}
if (parameters != null && parameters.Length > 0)
{
var outParas = parameters.Where(a => a.Direction != ParameterDirection.Input).ToList();
if (outParas.Count > 0)
{
resultList.Add("返回值为[" + string.Join(" and ", outParas.Select(a => a.ParameterName + " = " + a.Value)) + "]");
}
}
try
{
string detalMsg = ("执行存储过程" + storedProcedureName + "，" + string.Join("，", resultList)).Trim('，');
WriteLogMsg(dbLogMsg, LogType.存储过程, detalMsg, "ExecuteStoredProcedure方法");
}
catch
{
}
return(res);
}
protected override ExeResEdm GetDataSets(List <SqlContianer> ltSqls)
{
ExeResEdm dBResEdm = new ExeResEdm();
DataSet ds = new DataSet();
string curTableName = "";
try
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
SqlTransaction tsOprate = conn.BeginTransaction();
try
{
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = tsOprate;
List <string> tbNames = new List <string>();
foreach (var objOraSqlCon in ltSqls)
{
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(objOraSqlCon.tableName))
{
dt.TableName = objOraSqlCon.tableName;
}
else
{
string tb = GetTableNameFromSelectSql(objOraSqlCon.strSqlTxt);
if (!string.IsNullOrEmpty(tb))
{
dt.TableName = tb;
}
}
if (tbNames.Contains(dt.TableName))
{
dt.TableName = dt.TableName + "_" + (tbNames.Count() + 1);
}
tbNames.Add(dt.TableName);
curTableName = dt.TableName;
cmd.CommandText = objOraSqlCon.strSqlTxt;
cmd.Parameters.Clear();
if (objOraSqlCon.ltOraParams != null && objOraSqlCon.ltOraParams.Count > 0)
{
cmd.Parameters.AddRange(objOraSqlCon.ltOraParams.ToArray());
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
ds.Tables.Add(dt);
}
tsOprate.Commit();
}
catch (Exception ex)
{
tsOprate.Rollback();
dBResEdm.Module = "GetDataSets方法" + AddTableNameOrSqlText(curTableName);
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "GetDataSets方法" + AddTableNameOrSqlText(curTableName);
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
dBResEdm.ExeModel = ds;
return(dBResEdm);
}
/// <summary>
/// 发送消息给队列服务器
/// </summary>
/// <returns></returns>
public ExeResEdm Send(string message)
{
ExeResEdm exeResEdm = new ExeResEdm()
{
ErrCode = 1
};
if (string.IsNullOrWhiteSpace(message) || bErrorMQSetting)
{
exeResEdm.ErrMsg = bErrorMQSetting ? "消息队列设置错误" : "消息为空";
return(exeResEdm);
}
try
{
using (var channel = this.GetConnection().CreateModel())
{
//推送消息
byte[] bytes = Encoding.UTF8.GetBytes(message);
////声明一个交换机和队列，然后绑定在一起。
//if (!string.IsNullOrWhiteSpace(this.RabbitConfig.Exchange))
// //使用自定义的路由
// channel.ExchangeDeclare(this.RabbitConfig.Exchange, this.RabbitConfig.ExchangeType.ToString(), this.RabbitConfig.DurableQueue, false, null);
////channel.ExchangeDeclare(this.RabbitConfig.Exchange, this.RabbitConfig.ExchangeType);
//else
// //声明消息队列，且为可持久化的 ，如果队列的名称不存在，系统会自动创建，有的话不会覆盖
// channel.QueueDeclare(this.RabbitConfig.QueueName, this.RabbitConfig.DurableQueue, false, false, null);
channel.ExchangeDeclare(this.RabbitConfig.Exchange, this.RabbitConfig.ExchangeType.ToString(), this.RabbitConfig.DurableQueue, false, null);
channel.QueueDeclare(this.RabbitConfig.QueueName, this.RabbitConfig.DurableQueue, false, false, null);
IBasicProperties properties = channel.CreateBasicProperties();
properties.DeliveryMode = Convert.ToByte(this.RabbitConfig.DurableMessage ? 2 : 1); //支持持久化数据
channel.QueueBind(this.RabbitConfig.QueueName, RabbitConfig.Exchange, RabbitConfig.RoutingKey);
//将详细写入队列
if (string.IsNullOrEmpty(this.RabbitConfig.Exchange))
{
//没有配置路由，使用系统默认的路由
//推送消息
channel.BasicPublish("", this.RabbitConfig.QueueName, properties, bytes);
}
else
{
//推送消息
channel.BasicPublish(this.RabbitConfig.Exchange, this.RabbitConfig.RoutingKey, properties, bytes);
}
exeResEdm.ErrCode = 0;
return(exeResEdm);
}
}
catch (Exception ex)
{
// mqErrCnt++;
if (ex.Message == "None of the specified endpoints were reachable")
{
bErrorMQSetting = true;
}
exeResEdm.ExBody = ex;
exeResEdm.Module = "RabbitMQHelper.Send<string>方法";
return(exeResEdm);
}
}
protected override ExeResEdm UpdateDsToDB(DataSet dsTables, Dictionary <string, string> dicDtMainFields = null)
{
ExeResEdm dBResEdm = new ExeResEdm();
int n = 0;
string strTableName = "";
try
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
SqlTransaction tsOprate = conn.BeginTransaction();
try
{
SqlCommand cmd = conn.CreateCommand();
foreach (DataTable dtTemp in dsTables.Tables)
{
string strComFields = "*";
if (dicDtMainFields != null && dicDtMainFields.Count > 0 && dicDtMainFields.ContainsKey(dtTemp.TableName))
{
strComFields = dicDtMainFields[dtTemp.TableName];
}
cmd.CommandText = GetColumnsNameSql(dtTemp.TableName, strComFields);
cmd.Transaction = tsOprate;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var dtChanges = dtTemp.GetChanges();
if (dtChanges != null) //是添加或更新
{
adapter.UpdateCommand = new SqlCommandBuilder(adapter).GetUpdateCommand();
n += adapter.Update(dtChanges);
dtTemp.AcceptChanges();
}
else //是删除
{
adapter.DeleteCommand = new SqlCommandBuilder(adapter).GetDeleteCommand();
for (int i = dtTemp.Rows.Count - 1; i >= 0; i--)
{
dtTemp.Rows[i].Delete();
}
n += adapter.Update(dtTemp);
}
}
dsTables.AcceptChanges();
tsOprate.Commit();
}
catch (Exception ex)
{
tsOprate.Rollback();
dBResEdm.Module = "UpdateDsToDB方法" + AddTableNameOrSqlText(strTableName);
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "UpdateDsToDB方法" + AddTableNameOrSqlText(strTableName);
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
dBResEdm.ExeNum = n;
return(dBResEdm);
}
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);
}
/// <summary>
/// 接受消息，使用委托进行处理，使用QueueingBasicConsumer方法（已过时）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="receiveMethod"></param>
public ExeResEdm Receive <T>(ReceiveMessageDelegate <T> receiveMethod)
{
ExeResEdm exeResEdm = new ExeResEdm()
{
};
try
{
using (var channel = this.GetConnection().CreateModel())
{
//是否使用路由
if (!string.IsNullOrWhiteSpace(this.RabbitConfig.Exchange))
{
//声明路由
// channel.ExchangeDeclare(this.RabbitConfig.Exchange, this.RabbitConfig.ExchangeType.ToString(), this.RabbitConfig.DurableQueue);
//声明队列且与交换机绑定
channel.QueueDeclare(this.RabbitConfig.QueueName, this.RabbitConfig.DurableQueue, false, false, null);
channel.QueueBind(this.RabbitConfig.QueueName, this.RabbitConfig.Exchange, this.RabbitConfig.RoutingKey);
}
else
{
channel.QueueDeclare(this.RabbitConfig.QueueName, this.RabbitConfig.DurableQueue, false, false, null);
}
//输入1，那如果接收一个消息，但是没有应答，则客户端不会收到下一个消息
channel.BasicQos(0, 1, false); ///告诉RabbitMQ同一时间给一个消息给消费者
//在队列上定义一个消费者
var consumer = new QueueingBasicConsumer(channel);
//消费队列，并设置应答模式为程序主动应答
channel.BasicConsume(this.RabbitConfig.QueueName, false, consumer);
while (true)
{
//阻塞函数，获取队列中的消息
ProcessingResultsEnum processingResult = ProcessingResultsEnum.Retry;
ulong deliveryTag = 0;
try
{
//获取信息
var ea = (BasicDeliverEventArgs)consumer.Queue.Dequeue();
deliveryTag = ea.DeliveryTag;
byte[] bytes = ea.Body;
string str = Encoding.UTF8.GetString(bytes);
T v = SerializerHelper.DeserializeToObject <T>(str);
receiveMethod(v);
processingResult = ProcessingResultsEnum.Accept; //处理成功
}
catch //(Exception ex)
{
processingResult = ProcessingResultsEnum.Reject; //系统无法处理的错误
}
finally
{
System.Threading.Thread.Sleep(100);//暂停0.1秒，防止CPU爆满的问题
switch (processingResult)
{
case ProcessingResultsEnum.Accept:
//回复确认处理成功
channel.BasicAck(deliveryTag, false);
break;
case ProcessingResultsEnum.Retry:
//发生错误了，但是还可以重新提交给队列重新分配
channel.BasicNack(deliveryTag, false, true);
break;
case ProcessingResultsEnum.Reject:
//发生严重错误，无法继续进行，这种情况应该写日志或者是发送消息通知管理员
channel.BasicNack(deliveryTag, false, false);
//写日志
break;
}
}
}
}
}
catch (Exception ex)
{
exeResEdm.ErrCode = 1;
exeResEdm.ExBody = ex;
exeResEdm.Module = "RabbitMQHelper.Receive方法";
LogCom.WriteExceptToFile(ex, "RabbitMQHelper.Receive");
}
return(exeResEdm);
}
