private void ClearMemory() { adapter.Dispose(); builder.Dispose(); ds.Dispose(); ds.Clear(); }
public bool UpdateTable(DataTable table, string tableName, bool setInsertOn = true, bool setModifyOn = true, string additionalMessage = "") { try { TableHelper.SetDefaultColumnValues(table, setInsertOn, setModifyOn); var con = CONNECTION.OpenCon(); var adapter = new OracleDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con); var cmd = new OracleCommandBuilder(adapter); adapter.Update(table); cmd.Dispose(); adapter.Dispose(); CONNECTION.CloseCon(con); return(true); } catch (DBConcurrencyException cex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "UpdateTable DBConcurrencyError!", AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}", Ex = cex, }); if (Settings.ThrowExceptions) { throw new Exception("UpdateTable Error!", cex); } return(false); } catch (Exception ex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "UpdateTable Error!", AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}", Ex = ex, }); if (Settings.ThrowExceptions) { throw new Exception("UpdateTable Error!", ex); } return(false); } }
/// <summary> /// 写入操作日志 /// </summary> /// <param name="AListStrOperationInfo"> /// 0:客户端SessionID /// 1:模块ID 2:功能操作编号 3:租户Token(5位) 4:操作用户ID /// 5:当前操作角色 6:机器名 7:机器IP 8:操作时间 UTC /// 9:操作结果 10:操作内容对应的语言包ID 11:替换参数 12:异常错误 /// </param> /// <param name="AStrReturnCode"></param> /// <param name="AStrReturnMessage"></param> /// <returns></returns> public bool S01BOperation01(List <string> AListStrOperationInfo, ref string AStrReturnCode, ref string AStrReturnMessage) { bool LBoolReturn = true; string LStrVerificationCode004 = string.Empty; string LStrVerificationCode104 = string.Empty; SqlConnection LSqlConnection = null; OracleConnection LOracleConnection = null; string LStrSelectSQL = string.Empty; List <string> LListStrReplaceArgs = new List <string>(); List <string> LListStrException = new List <string>(); try { #region 局部变量初始化、定义 LStrVerificationCode004 = CreateVerificationCode(EncryptionAndDecryption.UMPKeyAndIVType.M004); LStrVerificationCode104 = CreateVerificationCode(EncryptionAndDecryption.UMPKeyAndIVType.M104); DataOperations01 LDataOperation = new DataOperations01(); DatabaseOperation01Return LDBOperationReturn = new DatabaseOperation01Return(); #endregion #region 获取操作日志流水号 string LStrOperationSerialID = string.Empty; if (AListStrOperationInfo[1] == "11000") { LDBOperationReturn = LDataOperation.GetSerialNumberByProcedure(IIntDBType, IStrDBConnectProfile, 11, 901, AListStrOperationInfo[3], DateTime.UtcNow.ToString("yyyyMMddHHmmss")); } else { LDBOperationReturn = LDataOperation.GetSerialNumberByProcedure(IIntDBType, IStrDBConnectProfile, 11, 902, AListStrOperationInfo[3], DateTime.UtcNow.ToString("yyyyMMddHHmmss")); } if (!LDBOperationReturn.BoolReturn) { //系统分配操作日志流水号失败 AStrReturnCode = EncryptionAndDecryption.EncryptDecryptString("E01B01", LStrVerificationCode004, EncryptionAndDecryption.UMPKeyAndIVType.M004); AStrReturnMessage = LDBOperationReturn.StrReturn; return(false); } else { LStrOperationSerialID = LDBOperationReturn.StrReturn; } #endregion #region 拆分替换参数和异常出错 string LStrReplaceArgs = string.Empty; string LStrException = string.Empty; LStrReplaceArgs = AListStrOperationInfo[11]; LStrException = AListStrOperationInfo[12]; while (LStrReplaceArgs.Length > 1024) { LListStrReplaceArgs.Add(LStrReplaceArgs.Substring(0, 1024)); LStrReplaceArgs = LStrReplaceArgs.Substring(1024); } if (!string.IsNullOrEmpty(LStrReplaceArgs)) { LListStrReplaceArgs.Add(LStrReplaceArgs); } while (LListStrReplaceArgs.Count < 5) { LListStrReplaceArgs.Add(""); } while (LStrException.Length > 1024) { LListStrException.Add(LStrException.Substring(0, 1024)); LStrException = LStrException.Substring(1024); } if (!string.IsNullOrEmpty(LStrException)) { LListStrException.Add(LStrException); } while (LListStrException.Count < 5) { LListStrException.Add(""); } #endregion #region 将日志写入DataSet中 //LStrSelectSQL = "SELECT * FROM T_11_901_" + AListStrOperationInfo[3] + " WHERE 1 = 2"; LStrSelectSQL = "SELECT * FROM T_11_901 WHERE 1 = 2"; LDBOperationReturn = LDataOperation.SelectDataByDynamicSQL(IIntDBType, IStrDBConnectProfile, LStrSelectSQL); DataSet LDataSetSave2DB = LDBOperationReturn.DataSetReturn; LDataSetSave2DB.Tables[0].TableName = "T_11_901"; DataRow LDataRowNewLog = LDataSetSave2DB.Tables[0].NewRow(); LDataRowNewLog.BeginEdit(); LDataRowNewLog["C001"] = long.Parse(LStrOperationSerialID); LDataRowNewLog["C002"] = long.Parse(AListStrOperationInfo[0]); LDataRowNewLog["C003"] = Int16.Parse(AListStrOperationInfo[1]); LDataRowNewLog["C004"] = long.Parse(AListStrOperationInfo[2]); LDataRowNewLog["C005"] = long.Parse(AListStrOperationInfo[4]); LDataRowNewLog["C006"] = AListStrOperationInfo[6]; LDataRowNewLog["C007"] = AListStrOperationInfo[7]; LDataRowNewLog["C008"] = long.Parse((DateTime.Parse(AListStrOperationInfo[8])).ToString("yyyyMMddHHmmss")); LDataRowNewLog["C009"] = AListStrOperationInfo[9]; LDataRowNewLog["C010"] = AListStrOperationInfo[10]; LDataRowNewLog["C011"] = LListStrReplaceArgs[0]; LDataRowNewLog["C012"] = LListStrReplaceArgs[1]; LDataRowNewLog["C013"] = LListStrReplaceArgs[2]; LDataRowNewLog["C014"] = LListStrReplaceArgs[3]; LDataRowNewLog["C015"] = LListStrReplaceArgs[4]; LDataRowNewLog["C016"] = LListStrException[0]; LDataRowNewLog["C017"] = LListStrException[1]; LDataRowNewLog["C018"] = LListStrException[2]; LDataRowNewLog["C019"] = LListStrException[3]; LDataRowNewLog["C020"] = LListStrException[4]; LDataRowNewLog["C021"] = long.Parse(AListStrOperationInfo[5]); LDataRowNewLog["C022"] = AListStrOperationInfo[3]; LDataRowNewLog.EndEdit(); LDataSetSave2DB.Tables[0].Rows.Add(LDataRowNewLog); #endregion #region 将操作日志写入MSSQL数据库 if (IIntDBType == 2) { LSqlConnection = new SqlConnection(IStrDBConnectProfile); SqlDataAdapter LSqlDataAdapter = new SqlDataAdapter(LStrSelectSQL, LSqlConnection); SqlCommandBuilder LSqlCommandBuilder = new SqlCommandBuilder(); LSqlCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges; LSqlCommandBuilder.SetAllValues = false; LSqlCommandBuilder.DataAdapter = LSqlDataAdapter; LSqlDataAdapter.Update(LDataSetSave2DB, "T_11_901"); LDataSetSave2DB.AcceptChanges(); LSqlCommandBuilder.Dispose(); LSqlDataAdapter.Dispose(); } #endregion #region 将操作日志写入Oracle数据库 if (IIntDBType == 3) { LOracleConnection = new OracleConnection(IStrDBConnectProfile); OracleDataAdapter LOracleDataAdapter = new OracleDataAdapter(LStrSelectSQL, LOracleConnection); OracleCommandBuilder LOracleCommandBuilder = new OracleCommandBuilder(); LOracleCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges; LOracleCommandBuilder.SetAllValues = false; LOracleCommandBuilder.DataAdapter = LOracleDataAdapter; LOracleDataAdapter.Update(LDataSetSave2DB, "T_11_901"); LDataSetSave2DB.AcceptChanges(); LOracleCommandBuilder.Dispose(); LOracleDataAdapter.Dispose(); } #endregion } catch (Exception ex) { LBoolReturn = false; AStrReturnCode = EncryptionAndDecryption.EncryptDecryptString("E01B99", LStrVerificationCode004, EncryptionAndDecryption.UMPKeyAndIVType.M004); AStrReturnMessage = "S01BOperations.S01BOperation01()\n" + ex.Message; } finally { if (LSqlConnection != null) { if (LSqlConnection.State == System.Data.ConnectionState.Open) { LSqlConnection.Close(); } LSqlConnection.Dispose(); } if (LOracleConnection != null) { if (LOracleConnection.State == ConnectionState.Open) { LOracleConnection.Close(); } LOracleConnection.Dispose(); LOracleConnection = null; } } return(LBoolReturn); }
private void ActionSynchronousExtensionData() { string LStrDynamicSQL = string.Empty; string LStrTemp = string.Empty; string LStrUserDefaultPwd = string.Empty; string LStrVerificationCode002 = string.Empty; string LStrVerificationCode102 = string.Empty; string LStrDataTimeNow = string.Empty; string LStrDataID = string.Empty; SqlConnection LSqlConnection = null; OracleConnection LOracleConnection = null; string LStr11101001 = string.Empty; string LStr11101002 = string.Empty; string LStr11101012 = string.Empty; string LStr11101013 = string.Empty; string LStr11101014 = string.Empty; string LStr11101015 = string.Empty; string LStr11101017 = string.Empty; string LStrStep = string.Empty; try { DatabaseOperation01Return LDatabaseOperation01Return = new DatabaseOperation01Return(); DataOperations01 LDataOperations01 = new DataOperations01(); LStrVerificationCode002 = CreateVerificationCode(EncryptionAndDecryption.UMPKeyAndIVType.M002); LStrVerificationCode102 = CreateVerificationCode(EncryptionAndDecryption.UMPKeyAndIVType.M102); #region 获取租户列表 LStrDynamicSQL = "SELECT * FROM T_00_121 ORDER BY C001"; LDatabaseOperation01Return = LDataOperations01.SelectDataByDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); //租户列表 DataSet LDataSet00121 = LDatabaseOperation01Return.DataSetReturn; #endregion #region 获取所有录音服务器和录音、录屏通道 List<string> LListStrServerRentInfo = new List<string>(); LStrDynamicSQL = "SELECT * FROM T_11_101_00000 WHERE (C001 > 2210000000000000000 AND C001 < 2220000000000000000 AND C002 = 1) OR (C001 > 2310000000000000000 AND C001 < 2320000000000000000 AND C002 = 1)"; LDatabaseOperation01Return = LDataOperations01.SelectDataByDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); //所有录音录屏服务器 DataSet LDataSet11101AllLGServer = LDatabaseOperation01Return.DataSetReturn; foreach (DataRow LDataRowSingleServer in LDataSet11101AllLGServer.Tables[0].Rows) { LStr11101001 = LDataRowSingleServer["C001"].ToString(); LStr11101017 = LDataRowSingleServer["C017"].ToString(); LStr11101017 = LStr11101017.Substring(9); LStr11101017 = EncryptionAndDecryption.EncryptDecryptString(LStr11101017, LStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102); LDataRowSingleServer["C017"] = LStr11101017; //服务器19位编码 + char(27) + 服务器IP + char(27) + "未被分配" LListStrServerRentInfo.Add(LStr11101001 + IStrSpliterChar + LStr11101017 + IStrSpliterChar + "0"); } LStrDynamicSQL = "SELECT * FROM T_11_101_00000 WHERE (C001 > 2250000000000000000 AND C001 < 2260000000000000000 AND (C002 = 1 OR C002 = 2)) OR (C001 > 2320000000000000000 AND C001 < 2330000000000000000 AND (C002 = 1 OR C002 = 2)) ORDER BY C001, C002"; LDatabaseOperation01Return = LDataOperations01.SelectDataByDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); //所有录音录屏通道 DataSet LDataSet11101AllLGChannel = LDatabaseOperation01Return.DataSetReturn; for (int LIntLoopChannel = 0; LIntLoopChannel < LDataSet11101AllLGChannel.Tables[0].Rows.Count; LIntLoopChannel++) { //C001:通道编码 19 位 //C012:通道ID 0~2000 //C013:录音服务器IP //C014:分机号 //C015:通道名 LStr11101002 = LDataSet11101AllLGChannel.Tables[0].Rows[LIntLoopChannel]["C002"].ToString(); if (LStr11101002 == "1") { LStr11101013 = LDataSet11101AllLGChannel.Tables[0].Rows[LIntLoopChannel]["C013"].ToString(); DataRow[] LDataRowArray = LDataSet11101AllLGServer.Tables[0].Select("C001 = " + LStr11101013); LStr11101017 = LDataRowArray[0]["C017"].ToString(); LDataSet11101AllLGChannel.Tables[0].Rows[LIntLoopChannel]["C017"] = LStr11101017; } if (LStr11101002 == "2") { LStr11101001 = LDataSet11101AllLGChannel.Tables[0].Rows[LIntLoopChannel]["C001"].ToString(); LDataSet11101AllLGChannel.Tables[0].Rows[LIntLoopChannel - 1]["C014"] = LDataSet11101AllLGChannel.Tables[0].Rows[LIntLoopChannel]["C012"].ToString(); if (LStr11101001.Substring(0, 3) == "225") { LDataSet11101AllLGChannel.Tables[0].Rows[LIntLoopChannel - 1]["C015"] = LDataSet11101AllLGChannel.Tables[0].Rows[LIntLoopChannel]["C011"].ToString(); } } } #endregion #region 获取租户租用的服务器 string LStrRentBegin = string.Empty, LStrRentEnd = string.Empty; string LStr11201004 = string.Empty; List<string> LListStrRentToken = new List<string>(); List<List<string>> LListListRentServer = new List<List<string>>(); foreach (DataRow LDataRowSingleRent in LDataSet00121.Tables[0].Rows) { LStrRentBegin = LDataRowSingleRent["C011"].ToString(); LStrRentEnd = LDataRowSingleRent["C012"].ToString(); LStrRentBegin = EncryptionAndDecryption.EncryptDecryptString(LStrRentBegin, LStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102); LStrRentEnd = EncryptionAndDecryption.EncryptDecryptString(LStrRentEnd, LStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102); LStrTemp = LDataRowSingleRent["C021"].ToString(); LStrTemp = EncryptionAndDecryption.EncryptDecryptString(LStrTemp, LStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102); LStrDataID = LDataRowSingleRent["C001"].ToString(); //租户Token + char(27) + 租户19位编码 LListStrRentToken.Add(LStrTemp + IStrSpliterChar + LStrDataID); List<string> LListStrRentServerInfo = new List<string>(); if (DateTime.Parse(LStrRentBegin) <= DateTime.UtcNow && DateTime.Parse(LStrRentEnd) >= DateTime.UtcNow) { LStrDynamicSQL = "SELECT * FROM T_11_201_" + LStrTemp + " WHERE C003 = " + LStrDataID + " AND ((C004 > 2210000000000000000 AND C004 < 2220000000000000000) OR (C004 > 2310000000000000000 AND C004 < 2320000000000000000))"; LDatabaseOperation01Return = LDataOperations01.SelectDataByDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); foreach (DataRow LDataRowRentSingleServer in LDatabaseOperation01Return.DataSetReturn.Tables[0].Rows) { LStr11201004 = LDataRowRentSingleServer["C004"].ToString(); DataRow[] LDataRowArray = LDataSet11101AllLGServer.Tables[0].Select("C001 = " + LStr11201004); if (LDataRowArray.Length > 0) { LStr11101017 = LDataRowArray[0]["C017"].ToString(); //服务器19位编码 + char(27) + 服务器IP LListStrRentServerInfo.Add(LStr11201004 + IStrSpliterChar + LStr11101017); } } } LListListRentServer.Add(LListStrRentServerInfo); } #endregion #region 将未分配给租户的服务器全部分配给顶级租户 for (int LIntLoopServer = 0; LIntLoopServer < LListStrServerRentInfo.Count; LIntLoopServer++) { foreach (List<string> LListStrRentServerInfo in LListListRentServer) { foreach (string LStrsingleServer in LListStrRentServerInfo) { if ((LStrsingleServer + IStrSpliterChar + "0") == LListStrServerRentInfo[LIntLoopServer]) { string[] LStrArrayTemp = LStrsingleServer.Split(IStrSpliterChar.ToCharArray()); LListStrServerRentInfo[LIntLoopServer] = LStrArrayTemp[0] + IStrSpliterChar + LStrArrayTemp[1] + IStrSpliterChar + "1"; } } } } foreach (string LStrUnRentServer in LListStrServerRentInfo) { string[] LStrArrayTemp = LStrUnRentServer.Split(IStrSpliterChar.ToCharArray()); if (LStrArrayTemp[2] == "0") { //List<string> LListTemp = new List<string>(); //LListTemp.Add(LStrArrayTemp[0] + IStrSpliterChar + LStrArrayTemp[1]); LListListRentServer[0].Add(LStrArrayTemp[0] + IStrSpliterChar + LStrArrayTemp[1]); } } #endregion #region 检查租户分机资源是否在原始配置资源中,如果不在,则从表中删除 bool LBoolRented = false; List<string> LListStrRentServers = new List<string>(); for (int LIntLoopRent = 0; LIntLoopRent < LListStrRentToken.Count; LIntLoopRent++) { LListStrRentServers.Clear(); string[] LStrRentBasicArray = LListStrRentToken[LIntLoopRent].Split(IStrSpliterChar.ToCharArray()); foreach (string LStrRentServices in LListListRentServer[LIntLoopRent]) { string[] LStrServerArray = LStrRentServices.Split(IStrSpliterChar.ToCharArray()); LListStrRentServers.Add(LStrServerArray[1]); } LStrDynamicSQL = "SELECT * FROM T_11_101_" + LStrRentBasicArray[0] + " WHERE C001 > 1040000000000000000 AND C001 < 1050000000000000000 AND C002 = 1"; LDatabaseOperation01Return = LDataOperations01.SelectDataByDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); if (!LDatabaseOperation01Return.BoolReturn) { continue; } foreach (DataRow LDataRowSingleChannel in LDatabaseOperation01Return.DataSetReturn.Tables[0].Rows) { LStr11101001 = LDataRowSingleChannel["C001"].ToString(); LStr11101017 = LDataRowSingleChannel["C017"].ToString(); LStrStep = LStr11101017; LStr11101017 = EncryptionAndDecryption.EncryptDecryptString(LStr11101017, LStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102); string[] LStrExtensionServerIP = LStr11101017.Split(IStrSpliterChar.ToCharArray()); if (LStrExtensionServerIP.Length < 2) { //WriteEntryLog("ActionSynchronousExtensionData()\nError Extension\n{" + LStrStep + "}", EventLogEntryType.Warning); continue; } LBoolRented = false; foreach (string LStrSingleServer in LListStrRentServers) { if (LStrSingleServer == LStrExtensionServerIP[1]) { LBoolRented = true; break; } } if (!LBoolRented) { LStrDynamicSQL = "UPDATE T_11_101_" + LStrRentBasicArray[0] + " SET C012 = '0' WHERE C001 = " + LStr11101001; LDataOperations01.ExecuteDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); } else { DataRow[] LDataRowFindChannel = LDataSet11101AllLGChannel.Tables[0].Select("C002 = 1 AND C013 = '" + LStrExtensionServerIP[1] + "' AND C014 = '" + LStrExtensionServerIP[0] + "'"); if (LDataRowFindChannel == null || LDataRowFindChannel.Length <= 0) { LStrDynamicSQL = "UPDATE T_11_101_" + LStrRentBasicArray[0] + " SET C012 = '0' WHERE C001 = " + LStr11101001; LDataOperations01.ExecuteDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); } } } } #endregion #region 遍历所有Channel,添加到对应的租户中 //C001:通道编码 19 位 //C012:通道ID 0~2000 //C017:录音服务器IP //C014:分机号 //C015:通道名 for (int LIntLoopRent = 0; LIntLoopRent < LListStrRentToken.Count; LIntLoopRent++) { string[] LStrRentBasicArray = LListStrRentToken[LIntLoopRent].Split(IStrSpliterChar.ToCharArray()); #region 获取新用户默认密码 LStrDynamicSQL = "SELECT C006 FROM T_11_001_" + LStrRentBasicArray[0] + " WHERE C003 = 11010501"; LDatabaseOperation01Return = LDataOperations01.SelectDataByDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); DataSet LDataSet11010501 = LDatabaseOperation01Return.DataSetReturn; LStrUserDefaultPwd = LDataSet11010501.Tables[0].Rows[0][0].ToString(); LStrUserDefaultPwd = EncryptionAndDecryption.EncryptDecryptString(LStrUserDefaultPwd, LStrVerificationCode102, EncryptionAndDecryption.UMPKeyAndIVType.M102); LStrUserDefaultPwd = LStrUserDefaultPwd.Substring(8); #endregion foreach (string LStrRentServices in LListListRentServer[LIntLoopRent]) { string[] LStrServerArray = LStrRentServices.Split(IStrSpliterChar.ToCharArray()); DataRow[] LDataRowChannelBasic = LDataSet11101AllLGChannel.Tables[0].Select("C002 = 1 AND C017 = '" + LStrServerArray[1] + "'"); foreach (DataRow LDataRowSingleChannel in LDataRowChannelBasic) { LStr11101014 = LDataRowSingleChannel["C014"].ToString(); LStr11101013 = LStrServerArray[1]; LStr11101001 = LDataRowSingleChannel["C001"].ToString(); LStr11101012 = LDataRowSingleChannel["C012"].ToString(); LStr11101015 = LDataRowSingleChannel["C015"].ToString(); LStr11101017 = LDataRowSingleChannel["C017"].ToString(); LStrTemp = EncryptionAndDecryption.EncryptDecryptString(LStr11101014 + IStrSpliterChar + LStr11101013, LStrVerificationCode002, EncryptionAndDecryption.UMPKeyAndIVType.M002); LStrDynamicSQL = "SELECT * FROM T_11_101_" + LStrRentBasicArray[0] + " WHERE C001 IN (SELECT C001 FROM T_11_101_" + LStrRentBasicArray[0] + " WHERE C001 > 1040000000000000000 AND C001 < 1050000000000000000 AND C017 = '" + LStrTemp + "') ORDER BY C002"; LDatabaseOperation01Return = LDataOperations01.SelectDataByDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); DataSet LDataSetSave2DBBI = LDatabaseOperation01Return.DataSetReturn; LDataSetSave2DBBI.Tables[0].TableName = "T_11_101_" + LStrRentBasicArray[0]; if (LDataSetSave2DBBI.Tables[0].Rows.Count <= 0) { LStrDataID = GetSerialIDByType(IIntDatabaseType, IStrDatabaseProfile, LStrRentBasicArray[0], 11, 104).ToString(); //LStrUserDefaultPwd = EncryptionAndDecryption.EncryptDecryptString(LStrDataID + LStrUserDefaultPwd, LStrVerificationCode002, EncryptionAndDecryption.UMPKeyAndIVType.M002); #region 如果是新增分机,初始化2行数据 for (int LIntAddRow = 1; LIntAddRow <= 2; LIntAddRow++) { DataRow LDataRowNewData = LDataSetSave2DBBI.Tables[0].NewRow(); LDataRowNewData.BeginEdit(); LDataRowNewData["C001"] = long.Parse(LStrDataID); LDataRowNewData["C002"] = LIntAddRow; if (LIntAddRow == 1) { LDataRowNewData["C011"] = "101" + LStrRentBasicArray[0] + "00000000001"; LDataRowNewData["C012"] = "1"; LDataRowNewData["C013"] = "1"; LDataRowNewData["C014"] = "0"; LDataRowNewData["C015"] = "N"; LDataRowNewData["C016"] = "09"; LDataRowNewData["C017"] = LStrTemp; LDataRowNewData["C020"] = EncryptionAndDecryption.EncryptStringSHA512(LStrDataID + LStrUserDefaultPwd, LStrVerificationCode002, EncryptionAndDecryption.UMPKeyAndIVType.M002); ; } if (LIntAddRow == 2) { LDataRowNewData["C011"] = "2014/01/01 00:00:00"; LDataRowNewData["C012"] = "0"; LDataRowNewData["C013"] = "0"; } LDataRowNewData.EndEdit(); LDataSetSave2DBBI.Tables[0].Rows.Add(LDataRowNewData); if (IIntDatabaseType == 2) { LStrDynamicSQL = "INSERT INTO T_11_201_" + LStrRentBasicArray[0] + " VALUES(0, 0, 102" + LStrRentBasicArray[0] + "00000000001, " + LStrDataID + ", '2014-01-01 00:00:00', '2199-12-31 23:59:59')"; } else { LStrDynamicSQL = "INSERT INTO T_11_201_" + LStrRentBasicArray[0] + " VALUES(0, 0, 102" + LStrRentBasicArray[0] + "00000000001, " + LStrDataID + ", TO_DATE('2014-01-01 00:00:00','yyyy-MM-dd HH24:mi:ss'), TO_DATE('2199-12-31 23:59:59','yyyy-MM-dd HH24:mi:ss'))"; } LDataOperations01.ExecuteDynamicSQL(IIntDatabaseType, IStrDatabaseProfile, LStrDynamicSQL); } #endregion } LStrDataID = LDataSetSave2DBBI.Tables[0].Rows[0]["C001"].ToString(); #region 更新分机信息 LDataSetSave2DBBI.Tables[0].Rows[0]["C012"] = "1"; LDataSetSave2DBBI.Tables[0].Rows[0]["C018"] = LStr11101015; LStr11101013 = LDataRowSingleChannel["C013"].ToString(); //WriteEntryLog("LStr11101013 = " + LStr11101013, EventLogEntryType.Warning); if (LStr11101013.Substring(0, 3) == "221") { //LDataSetSave2DBBI.Tables[0].Rows[1]["C015"] = LStr11101017; LDataSetSave2DBBI.Tables[0].Rows[1]["C015"] = LStr11101013; LDataSetSave2DBBI.Tables[0].Rows[1]["C016"] = LStr11101012; LDataSetSave2DBBI.Tables[0].Rows[1]["C017"] = LStr11101001; } else { //LDataSetSave2DBBI.Tables[0].Rows[1]["C018"] = LStr11101017; LDataSetSave2DBBI.Tables[0].Rows[1]["C018"] = LStr11101013; LDataSetSave2DBBI.Tables[0].Rows[1]["C019"] = LStr11101012; LDataSetSave2DBBI.Tables[0].Rows[1]["C020"] = LStr11101001; } #endregion #region 将数据保存到MSSQL数据库 if (IIntDatabaseType == 2) { LSqlConnection = new SqlConnection(IStrDatabaseProfile); LStrDynamicSQL = "SELECT * FROM T_11_101_" + LStrRentBasicArray[0] + " WHERE C001 = " + LStrDataID + " ORDER BY C002 ASC"; SqlDataAdapter LSqlDataAdapter1 = new SqlDataAdapter(LStrDynamicSQL, LSqlConnection); SqlCommandBuilder LSqlCommandBuilder1 = new SqlCommandBuilder(); LSqlCommandBuilder1.ConflictOption = ConflictOption.OverwriteChanges; LSqlCommandBuilder1.SetAllValues = false; LSqlCommandBuilder1.DataAdapter = LSqlDataAdapter1; LSqlDataAdapter1.Update(LDataSetSave2DBBI, "T_11_101_" + LStrRentBasicArray[0]); LDataSetSave2DBBI.AcceptChanges(); LSqlCommandBuilder1.Dispose(); LSqlDataAdapter1.Dispose(); } #endregion #region 将数据保存到Oracle数据库 if (IIntDatabaseType == 3) { LOracleConnection = new OracleConnection(IStrDatabaseProfile); LStrDynamicSQL = "SELECT * FROM T_11_101_" + LStrRentBasicArray[0] + " WHERE C001 = " + LStrDataID + " ORDER BY C002 ASC"; OracleDataAdapter LOracleDataAdapter1 = new OracleDataAdapter(LStrDynamicSQL, LOracleConnection); OracleCommandBuilder LOracleCommandBuilder1 = new OracleCommandBuilder(); LOracleCommandBuilder1.ConflictOption = ConflictOption.OverwriteChanges; LOracleCommandBuilder1.SetAllValues = false; LOracleCommandBuilder1.DataAdapter = LOracleDataAdapter1; LOracleDataAdapter1.Update(LDataSetSave2DBBI, "T_11_101_" + LStrRentBasicArray[0]); LDataSetSave2DBBI.AcceptChanges(); LOracleCommandBuilder1.Dispose(); LOracleDataAdapter1.Dispose(); } #endregion } } } #endregion } catch (Exception ex) { IBooInSynchronous = false; IBoolCanAbortSynchronous = true; WriteEntryLog("ActionSynchronousExtensionData()\n" + LStrStep + "\n" + ex.ToString(), EventLogEntryType.Error); } finally { if (LSqlConnection != null) { if (LSqlConnection.State == System.Data.ConnectionState.Open) { LSqlConnection.Close(); } LSqlConnection.Dispose(); } if (LOracleConnection != null) { if (LOracleConnection.State == ConnectionState.Open) { LOracleConnection.Close(); } LOracleConnection.Dispose(); LOracleConnection = null; } } }
public bool UpdateTable(DataTable table, string tableName) { try { TableHelper.SetDefaultColumnValues(table); var con = CONNECTION.OpenCon(); var adapter = new OracleDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con); var cmd = new OracleCommandBuilder(adapter); adapter.Update(table); cmd.Dispose(); adapter.Dispose(); CONNECTION.CloseCon(con); return true; } catch (DBConcurrencyException cex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "UpdateTable DBConcurrencyError!", Ex = cex, }); return false; } catch (Exception ex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "UpdateTable Error!", Ex = ex, }); return false; } }
/// <summary> /// 查询操作日志 /// </summary> /// <param name="AListStringArgs"> /// 0:数据库类型 /// 1:数据库连接串 /// 2:租户Token /// 3~N:查询条件</param> /// <returns></returns> private OperationDataArgs OperationA02(List <string> AListStringArgs) { OperationDataArgs LOperationReturn = new OperationDataArgs(); string LStrFirst4Char = string.Empty; string LStrAfter4Char = string.Empty; string LStr11901008B = string.Empty; //开始时间 yyyy/MM/dd HH:mm:ss string LStr11901008E = string.Empty; //结束时间 yyyy/MM/dd HH:mm:ss List <string> LListStrOperationID = new List <string>(); List <string> LListStrUserID = new List <string>(); List <string> LListStrOperationResult = new List <string>(); List <string> LListStrHostName = new List <string>(); List <string> LListStrHostIPAddress = new List <string>(); string LStrOP911 = string.Empty, LStrUA911 = string.Empty, LStrOR911 = string.Empty, LStrHN911 = string.Empty, LStrIP911 = string.Empty; int LIntDBType = 0; string LStrDBProfile = string.Empty; string LStrSelectSQL = string.Empty; int LIntTempID = 0; SqlConnection LSqlConnection = null; OracleConnection LOracleConnection = null; try { LIntDBType = int.Parse(AListStringArgs[0]); LStrDBProfile = AListStringArgs[1]; DataOperations01 LDataOperation = new DataOperations01(); DatabaseOperation01Return LDBOperationReturn = new DatabaseOperation01Return(); #region 分析日志查询条件 foreach (string LStrSelectSingleArgs in AListStringArgs) { if (LStrSelectSingleArgs.Length < 4) { continue; } LStrFirst4Char = LStrSelectSingleArgs.Substring(0, 4); LStrAfter4Char = LStrSelectSingleArgs.Substring(4); if (LStrFirst4Char == AscCodeToChr(27) + "BT" + AscCodeToChr(27)) { LStr11901008B = (DateTime.Parse(LStrAfter4Char)).ToString("yyyyMMddHHmmss"); continue; } if (LStrFirst4Char == AscCodeToChr(27) + "ET" + AscCodeToChr(27)) { LStr11901008E = (DateTime.Parse(LStrAfter4Char)).ToString("yyyyMMddHHmmss"); continue; } if (LStrFirst4Char == AscCodeToChr(27) + "OP" + AscCodeToChr(27)) { LListStrOperationID.Add(LStrAfter4Char); continue; } if (LStrFirst4Char == AscCodeToChr(27) + "UA" + AscCodeToChr(27)) { LListStrUserID.Add(LStrAfter4Char); continue; } if (LStrFirst4Char == AscCodeToChr(27) + "OR" + AscCodeToChr(27)) { LListStrOperationResult.Add(LStrAfter4Char); continue; } if (LStrFirst4Char == AscCodeToChr(27) + "HN" + AscCodeToChr(27)) { LListStrHostName.Add(LStrAfter4Char); continue; } if (LStrFirst4Char == AscCodeToChr(27) + "IP" + AscCodeToChr(27)) { LListStrHostIPAddress.Add(LStrAfter4Char); continue; } } #endregion #region 保存数据查询条件 LStrSelectSQL = "SELECT * FROM T_00_901 WHERE 1 = 2"; LDBOperationReturn = LDataOperation.SelectDataByDynamicSQL(LIntDBType, LStrDBProfile, LStrSelectSQL); DataSet LDataSetSave2DB = LDBOperationReturn.DataSetReturn; LDataSetSave2DB.Tables[0].TableName = "T_00_901"; if (LListStrOperationID.Count > 0) { LIntTempID = 0; LDBOperationReturn = LDataOperation.GetSerialNumberByProcedure(LIntDBType, LStrDBProfile, 11, 911, AListStringArgs[2], DateTime.UtcNow.ToString("yyyyMMddHHmmss")); if (LDBOperationReturn.BoolReturn) { LStrOP911 = LDBOperationReturn.StrReturn; } else { LOperationReturn.BoolReturn = false; LOperationReturn.StringReturn = LDBOperationReturn.StrReturn; return(LOperationReturn); } foreach (string LStrSingleOperation in LListStrOperationID) { LIntTempID += 1; DataRow LDataRowNewOperation = LDataSetSave2DB.Tables[0].NewRow(); LDataRowNewOperation.BeginEdit(); LDataRowNewOperation["C001"] = long.Parse(LStrOP911); LDataRowNewOperation["C002"] = LIntTempID; LDataRowNewOperation["C011"] = LStrSingleOperation; LDataRowNewOperation.EndEdit(); LDataSetSave2DB.Tables[0].Rows.Add(LDataRowNewOperation); } } if (LListStrUserID.Count > 0) { LIntTempID = 0; LDBOperationReturn = LDataOperation.GetSerialNumberByProcedure(LIntDBType, LStrDBProfile, 11, 911, AListStringArgs[2], DateTime.UtcNow.ToString("yyyyMMddHHmmss")); if (LDBOperationReturn.BoolReturn) { LStrUA911 = LDBOperationReturn.StrReturn; } else { LOperationReturn.BoolReturn = false; LOperationReturn.StringReturn = LDBOperationReturn.StrReturn; return(LOperationReturn); } foreach (string LStrSingleUser in LListStrUserID) { LIntTempID += 1; DataRow LDataRowNewOperation = LDataSetSave2DB.Tables[0].NewRow(); LDataRowNewOperation.BeginEdit(); LDataRowNewOperation["C001"] = long.Parse(LStrUA911); LDataRowNewOperation["C002"] = LIntTempID; LDataRowNewOperation["C011"] = LStrSingleUser; LDataRowNewOperation.EndEdit(); LDataSetSave2DB.Tables[0].Rows.Add(LDataRowNewOperation); } } if (LListStrOperationResult.Count > 0) { LIntTempID = 0; LDBOperationReturn = LDataOperation.GetSerialNumberByProcedure(LIntDBType, LStrDBProfile, 11, 911, AListStringArgs[2], DateTime.UtcNow.ToString("yyyyMMddHHmmss")); if (LDBOperationReturn.BoolReturn) { LStrOR911 = LDBOperationReturn.StrReturn; } else { LOperationReturn.BoolReturn = false; LOperationReturn.StringReturn = LDBOperationReturn.StrReturn; return(LOperationReturn); } foreach (string LStrSingleResult in LListStrOperationResult) { LIntTempID += 1; DataRow LDataRowNewOperation = LDataSetSave2DB.Tables[0].NewRow(); LDataRowNewOperation.BeginEdit(); LDataRowNewOperation["C001"] = long.Parse(LStrOR911); LDataRowNewOperation["C002"] = LIntTempID; LDataRowNewOperation["C011"] = LStrSingleResult; LDataRowNewOperation.EndEdit(); LDataSetSave2DB.Tables[0].Rows.Add(LDataRowNewOperation); } } if (LListStrHostName.Count > 0) { LIntTempID = 0; LDBOperationReturn = LDataOperation.GetSerialNumberByProcedure(LIntDBType, LStrDBProfile, 11, 911, AListStringArgs[2], DateTime.UtcNow.ToString("yyyyMMddHHmmss")); if (LDBOperationReturn.BoolReturn) { LStrHN911 = LDBOperationReturn.StrReturn; } else { LOperationReturn.BoolReturn = false; LOperationReturn.StringReturn = LDBOperationReturn.StrReturn; return(LOperationReturn); } foreach (string LStrSingleHost in LListStrHostName) { LIntTempID += 1; DataRow LDataRowNewOperation = LDataSetSave2DB.Tables[0].NewRow(); LDataRowNewOperation.BeginEdit(); LDataRowNewOperation["C001"] = long.Parse(LStrHN911); LDataRowNewOperation["C002"] = LIntTempID; LDataRowNewOperation["C011"] = LStrSingleHost; LDataRowNewOperation.EndEdit(); LDataSetSave2DB.Tables[0].Rows.Add(LDataRowNewOperation); } } if (LListStrHostIPAddress.Count > 0) { LIntTempID = 0; LDBOperationReturn = LDataOperation.GetSerialNumberByProcedure(LIntDBType, LStrDBProfile, 11, 911, AListStringArgs[2], DateTime.UtcNow.ToString("yyyyMMddHHmmss")); if (LDBOperationReturn.BoolReturn) { LStrIP911 = LDBOperationReturn.StrReturn; } else { LOperationReturn.BoolReturn = false; LOperationReturn.StringReturn = LDBOperationReturn.StrReturn; return(LOperationReturn); } foreach (string LStrSingleIPAddress in LListStrHostIPAddress) { LIntTempID += 1; DataRow LDataRowNewOperation = LDataSetSave2DB.Tables[0].NewRow(); LDataRowNewOperation.BeginEdit(); LDataRowNewOperation["C001"] = long.Parse(LStrIP911); LDataRowNewOperation["C002"] = LIntTempID; LDataRowNewOperation["C011"] = LStrSingleIPAddress; LDataRowNewOperation.EndEdit(); LDataSetSave2DB.Tables[0].Rows.Add(LDataRowNewOperation); } } if (LIntDBType == 2) { LSqlConnection = new SqlConnection(LStrDBProfile); SqlDataAdapter LSqlDataAdapter = new SqlDataAdapter(LStrSelectSQL, LSqlConnection); SqlCommandBuilder LSqlCommandBuilder = new SqlCommandBuilder(); LSqlCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges; LSqlCommandBuilder.SetAllValues = false; LSqlCommandBuilder.DataAdapter = LSqlDataAdapter; LSqlDataAdapter.Update(LDataSetSave2DB, "T_00_901"); LDataSetSave2DB.AcceptChanges(); LSqlCommandBuilder.Dispose(); LSqlDataAdapter.Dispose(); } if (LIntDBType == 3) { LOracleConnection = new OracleConnection(LStrDBProfile); OracleDataAdapter LOracleDataAdapter = new OracleDataAdapter(LStrSelectSQL, LOracleConnection); OracleCommandBuilder LOracleCommandBuilder = new OracleCommandBuilder(); LOracleCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges; LOracleCommandBuilder.SetAllValues = false; LOracleCommandBuilder.DataAdapter = LOracleDataAdapter; LOracleDataAdapter.Update(LDataSetSave2DB, "T_00_901"); LDataSetSave2DB.AcceptChanges(); LOracleCommandBuilder.Dispose(); LOracleDataAdapter.Dispose(); } #endregion #region 创建查询SQL语句 LStrSelectSQL = "SELECT * FROM T_11_901_" + AListStringArgs[2] + " WHERE C008 >= " + LStr11901008B + " AND C008 <= " + LStr11901008E; if (!string.IsNullOrEmpty(LStrOP911)) { if (LIntDBType == 2) { LStrSelectSQL += " AND (C004 IN (SELECT CONVERT(BIGINT, C011) FROM T_00_901 WHERE C001 = " + LStrOP911 + "))"; } else { LStrSelectSQL += " AND (C004 IN (SELECT TO_NUMBER(C011) FROM T_00_901 WHERE C001 = " + LStrOP911 + "))"; } } if (!string.IsNullOrEmpty(LStrUA911)) { if (LIntDBType == 2) { LStrSelectSQL += " AND (C005 IN (SELECT CONVERT(BIGINT, C011) FROM T_00_901 WHERE C001 = " + LStrUA911 + "))"; } else { LStrSelectSQL += " AND (C005 IN (SELECT TO_NUMBER(C011) FROM T_00_901 WHERE C001 = " + LStrUA911 + "))"; } } if (!string.IsNullOrEmpty(LStrOR911)) { LStrSelectSQL += " AND (C009 IN (SELECT C011 FROM T_00_901 WHERE C001 = " + LStrOR911 + "))"; } if (!string.IsNullOrEmpty(LStrHN911)) { LStrSelectSQL += " AND (C006 IN (SELECT C011 FROM T_00_901 WHERE C001 = " + LStrHN911 + "))"; } if (!string.IsNullOrEmpty(LStrIP911)) { LStrSelectSQL += " AND (C007 IN (SELECT C011 FROM T_00_901 WHERE C001 = " + LStrIP911 + "))"; } #endregion #region 根据SQL语句查询操作日志 LDBOperationReturn = LDataOperation.SelectDataByDynamicSQL(LIntDBType, LStrDBProfile, LStrSelectSQL); if (LDBOperationReturn.BoolReturn) { LOperationReturn.DataSetReturn = LDBOperationReturn.DataSetReturn; LOperationReturn.StringReturn = LDBOperationReturn.StrReturn; LOperationReturn.ListStringReturn.Add(LStrSelectSQL); } else { LOperationReturn.BoolReturn = false; LOperationReturn.StringReturn = LDBOperationReturn.StrReturn; } #endregion } catch (Exception ex) { LOperationReturn.BoolReturn = false; LOperationReturn.StringReturn = ex.ToString(); } finally { if (LSqlConnection != null) { if (LSqlConnection.State == System.Data.ConnectionState.Open) { LSqlConnection.Close(); } LSqlConnection.Dispose(); } if (LOracleConnection != null) { if (LOracleConnection.State == ConnectionState.Open) { LOracleConnection.Close(); } LOracleConnection.Dispose(); LOracleConnection = null; } } return(LOperationReturn); }
public static bool UpdateTidbitSDR(int tidbitTypeId, Int32 refId, int tidbitOrder, string tidbitText = "", string timecode = "", bool enabled = false, int newTidbitOrder = 0) { OracleConnection cn = null; OracleCommand cmd = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; DataTable tbl = null; DataRow row = null; bool updated = false; try { cn = createConnectionSDR(); string sql = "select * from drafttidbits where referencetype = " + tidbitTypeId + " and referenceid = " + refId + " and tidbitorder = " + tidbitOrder; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); } else { row = tbl.Rows[0]; } row["referencetype"] = tidbitTypeId; row["referenceid"] = refId; if (newTidbitOrder > 0) { row["tidbitorder"] = newTidbitOrder; } else { row["tidbitorder"] = tidbitOrder; } row["text"] = tidbitText; row["enabled"] = enabled; //row["timecode"] = timecode; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); updated = true; } catch (Exception ex) { } finally { if (cmd != null) cmd.Dispose(); if (adp != null) adp.Dispose(); if (bldr != null) bldr.Dispose(); if (tbl != null) tbl.Dispose(); } return updated; }
public static bool SavePlayer(Player player) { OracleConnection cn = null; OracleCommand cmd = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; DataTable tbl = null; DataRow row = null; bool saved = false; try { cn = createConnectionSDR(); String sql = "select * from draftplayers where playerid = " + player.PlayerId; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["playerid"] = player.PlayerId; } else { row = tbl.Rows[0]; } row["firstname"] = player.FirstName; row["lastname"] = player.LastName; int oldRank = 0; if (row["kiperrank"] != DBNull.Value) { oldRank = Convert.ToInt16(row["kiperrank"]); } if (oldRank != player.KiperRank) { updateKiperRanks(player, oldRank); } if (player.KiperRank == 0) { row["kiperrank"] = DBNull.Value; } else { row["kiperrank"] = player.KiperRank; } oldRank = 0; if (row["mcshayrank"] != DBNull.Value) { oldRank = Convert.ToInt16(row["mcshayrank"]); } if (oldRank != player.McShayRank) { updateMcShayRanks(player, oldRank); } if (player.McShayRank == 0) { row["mcshayrank"] = DBNull.Value; } else { row["mcshayrank"] = player.McShayRank; } if (player.School != null) { row["schoolid"] = player.School.ID; } else { row["schoolid"] = DBNull.Value; } row["position"] = player.Position; row["positionfull"] = player.PositionFull; row["height"] = player.Height; row["weight"] = player.Weight; row["class"] = player.Class; //update the trade tidbit (NBA) UpdateTidbitSDR(1, player.PlayerId, 999, player.TradeTidbit, null, true); if (player.Tidbits != null) { foreach (Tidbit tidbit in player.Tidbits) { UpdateTidbitSDR(tidbit.ReferenceType, tidbit.ReferenceID, tidbit.TidbitOrder, tidbit.TidbitText, null, tidbit.Enabled); } } adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); saved = true; } finally { if (cmd != null) cmd.Dispose(); if (adp != null) adp.Dispose(); if (bldr != null) bldr.Dispose(); if (tbl != null) tbl.Dispose(); if (cn != null) cn.Close(); cn.Dispose(); } return saved; }
public static void ImportTeams(string file) { int teamsImported = 0; int importErrors = 0; int teamsToImport = 0; SetStatusBarMsg("Importing teams info...", "Yellow"); BackgroundWorker worker = new BackgroundWorker(); worker.WorkerReportsProgress = true; worker.DoWork += delegate(object s, DoWorkEventArgs args) { OracleConnection cn = null; OracleCommand cmd = null; OracleDataReader rdr = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; MySqlConnection cnMySql = null; MySqlCommand cmdMySql = null; MySqlDataReader rdrMySql = null; MySqlDataAdapter adpMySql = null; MySqlCommandBuilder bldrMySql = null; //DataTable tblPlayer = null; DataTable tbl = null; DataRow row; string sql; //long teamId; int i; int totalPicks = 0; DataSet dsTeams = new DataSet(); dsTeams.ReadXml(file); try { cn = createConnectionSDR(); teamsToImport = dsTeams.Tables["team"].Rows.Count; foreach (DataRow xmlRow in dsTeams.Tables["team"].Rows) { totalPicks = 0; if (xmlRow["teamid"].ToString().Trim() != "") { if (ConfigurationManager.AppSettings["DraftType"].ToUpper() == "NFL") { #region Picks try { sql = "select count(*) from draftorder where teamid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count > 0) { totalPicks = int.Parse(tbl.Rows[0][0].ToString()); } } finally { cmd.Dispose(); adp.Dispose(); tbl.Dispose(); } #endregion #region 6 Matrix Notes //import the 4 matrix notes for (i = 1; i <= 6; i++) { if (xmlRow["note" + i.ToString()].ToString().Trim() != "") { sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = " + i.ToString() + " and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = i; row["enabled"] = 1; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["note" + i.ToString()].ToString(); adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); } } #endregion #region MySql team ranks/results cnMySql = createConnectionMySql(); sql = "select * from teams where id = " + xmlRow["teamid"]; cmdMySql = new MySqlCommand(sql, cnMySql); adpMySql = new MySqlDataAdapter(cmdMySql); bldrMySql = new MySqlCommandBuilder(adpMySql); tbl = new DataTable(); adpMySql.Fill(tbl); if (tbl.Rows.Count > 0) { row = tbl.Rows[0]; row["totalpicks"] = totalPicks; row["overallrecord"] = xmlRow["record"]; row["divisionresult"] = xmlRow["divresult"]; row["playoffs"] = xmlRow["playoffs"]; row["offrankppg"] = xmlRow["offrankppg"]; row["offrankypg"] = xmlRow["offrankypg"]; row["offrankturns"] = xmlRow["offrankturns"]; row["offrankrush"] = xmlRow["offrankrushyds"]; row["offrankpass"] = xmlRow["offrankpassyds"]; row["defrankppg"] = xmlRow["defrankppg"]; row["defrankypg"] = xmlRow["defrankypg"]; row["defranktakeaways"] = xmlRow["defranktakeaways"]; row["defrankrush"] = xmlRow["defrankrushing"]; row["defrankpass"] = xmlRow["defrankpassing"]; row["teamneeds"] = xmlRow["melsneeds"]; adpMySql.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmdMySql.Dispose(); adpMySql.Dispose(); bldrMySql.Dispose(); tbl.Dispose(); } #endregion } else if (ConfigurationManager.AppSettings["DraftType"].ToUpper() == "NBA") { #region 2 Matrix Notes //import the 4 matrix notes for (i = 1; i <= 2; i++) { if (xmlRow["note" + i.ToString()].ToString().Trim() != "") { sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = " + i.ToString() + " and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = i; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["note" + i.ToString()].ToString(); row["enabled"] = 1; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); } } #endregion #region Finish sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = 21 and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = 21; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["divresult"].ToString(); row["enabled"] = 1; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); #endregion #region Record sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = 20 and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = 20; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["record"].ToString(); row["enabled"] = 1; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); #endregion #region Lineup sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = 30 and referenceid = " + xmlRow["teamid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["referencetype"] = 2; row["referenceid"] = xmlRow["teamid"]; row["tidbitorder"] = 30; } else { row = tbl.Rows[0]; } row["text"] = xmlRow["lineup"].ToString(); row["enabled"] = 1; adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); #endregion } teamsImported++; } worker.ReportProgress(teamsImported / teamsToImport); } //foreach team } //try catch (Exception ex) { importErrors++; } finally { if (cmd != null) cmd.Dispose(); if (adp != null) adp.Dispose(); if (bldr != null) bldr.Dispose(); if (rdr != null) rdr.Dispose(); if (cn != null) cn.Close(); cn.Dispose(); //log.Close(); } }; //dowork worker.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs args) { SetStatusBarMsg(teamsImported.ToString() + " of " + teamsToImport.ToString() + " teams successfully imported at " + DateTime.Now.ToLongTimeString(), "Green"); GlobalCollections.Instance.LoadTeams(); }; worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args) { SetStatusBarMsg(teamsImported.ToString() + " of " + teamsToImport.ToString() + " teams imported.", "Yellow"); }; worker.RunWorkerAsync(file); }
public static void ImportPlayers(string file) { int playersImported = 0; int importErrors = 0; int playersToImport = 0; int playersNotFound = 0; SetStatusBarMsg("Importing players...", "Yellow"); BackgroundWorker worker = new BackgroundWorker(); worker.WorkerReportsProgress = true; worker.DoWork += delegate(object s, DoWorkEventArgs args) { OracleConnection cn = null; OracleCommand cmd = null; OracleDataReader rdr = null; OracleDataAdapter adp = null; OracleCommandBuilder bldr = null; DataTable tblPlayer = null; DataTable tbl = null; DataRow row; //use the log when importing based on player names to find missing/duplicate players //FileInfo logFile = new FileInfo(ConfigurationManager.AppSettings["ImportLogFile"]); //if (logFile.Exists) //{ // logFile.Delete(); //} //TextWriter log = new StreamWriter(ConfigurationManager.AppSettings["ImportLogFile"]); string sql; long playerId; DataSet dsPlayers = new DataSet(); try { dsPlayers.ReadXml(file); cn = createConnectionSDR(); playersToImport = dsPlayers.Tables["player"].Rows.Count; foreach (DataRow xmlRow in dsPlayers.Tables["player"].Rows) { //sql = "select player_id from espnews.news_players where upper(last_name) = :lastname and upper(first_name) = :firstname"; //cmd = new OracleCommand(sql, cn); //cmd.Parameters.Add(":lastname", xmlRow["lastname"].ToString().ToUpper()); //cmd.Parameters.Add(":firstname", xmlRow["firstname"].ToString().ToUpper()); //rdr = cmd.ExecuteReader(); //tblPlayer = new DataTable(); //tblPlayer.Load(rdr); //rdr.Close(); //rdr.Dispose(); //if (tblPlayer.Rows.Count > 0) //{ // if (tblPlayer.Rows.Count > 1) // { // //write to the report file so we can see what player's ids are questionable // log.WriteLine(DateTime.Now + " --- Found multiple records for " + xmlRow["firstname"].ToString() + " " + xmlRow["lastname"].ToString() + ". Used PLAYERID " + tblPlayer.Rows[0]["player_id"].ToString()); // } // playerId = Convert.ToInt32(tblPlayer.Rows[0]["player_id"].ToString()); //} //else //{ // playerId = 0; //} //tblPlayer.Dispose(); //cmd.Dispose(); if (xmlRow["playerid"].ToString().Trim() != "") { sql = "select * from espnews.draftplayers where playerid = " + xmlRow["playerid"]; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tbl = new DataTable(); adp.Fill(tbl); if (tbl.Rows.Count == 0) { row = tbl.Rows.Add(); row["playerid"] = xmlRow["playerid"]; } else { row = tbl.Rows[0]; if (row["lastname"].ToString() != xmlRow["lastname"].ToString()) { string message = "Duplicate player id for " + xmlRow["firstname"].ToString() + " " + xmlRow["lastname"].ToString(); MessageBoxResult result = System.Windows.MessageBox.Show(message, "Duplicate Player ID", MessageBoxButton.OK, MessageBoxImage.Exclamation); //if (result == MessageBoxResult.Yes) //{ // Application.Current.Shutdown(); //} } } row["firstname"] = xmlRow["firstname"].ToString(); row["lastname"] = xmlRow["lastname"].ToString(); Int16 age; if (dsPlayers.Tables["player"].Columns["age"] != null) { if (Int16.TryParse(xmlRow["age"].ToString(), out age)) { row["age"] = age; } } row["position"] = xmlRow["position"].ToString(); if (dsPlayers.Tables["player"].Columns["positionfull"] != null) { row["positionfull"] = xmlRow["positionfull"].ToString(); } if (xmlRow["school"].ToString() != "") { if (Char.IsNumber(xmlRow["school"].ToString().ToCharArray()[0])) { row["schoolid"] = xmlRow["school"]; } } if (xmlRow["kiperrank"].ToString() != "") { row["kiperrank"] = Convert.ToInt16(xmlRow["kiperrank"].ToString()); } else { row["kiperrank"] = DBNull.Value; } if (xmlRow["mcshayrank"].ToString() != "") { row["mcshayrank"] = Convert.ToInt16(xmlRow["mcshayrank"].ToString()); } else { row["mcshayrank"] = DBNull.Value; } row["height"] = xmlRow["height"].ToString(); if (xmlRow["weight"].ToString().Trim() != "") { row["weight"] = xmlRow["weight"].ToString() + " LBS"; } else { row["weight"] = ""; } row["class"] = xmlRow["class"].ToString(); adp.Update(tbl.GetChanges()); tbl.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tbl.Dispose(); DataTable tblTids = null; try { int noteCount; if (ConfigurationManager.AppSettings["DraftType"].ToString().ToUpper() == "NBA") { noteCount = 2; } else { noteCount = 4; } for (int i = 1; i <= noteCount; i++) { if (xmlRow.Table.Columns["matrixnote" + i.ToString()] != null) { if (xmlRow["matrixnote" + i.ToString()].ToString().Trim() != "") { sql = "select * from espnews.drafttidbits where referenceid = " + xmlRow["playerid"] + " and referencetype = 1 and tidbitorder = " + i; cmd = new OracleCommand(sql, cn); adp = new OracleDataAdapter(cmd); bldr = new OracleCommandBuilder(adp); tblTids = new DataTable(); DataRow rowTids = null; adp.Fill(tblTids); if (tblTids.Rows.Count == 0) { rowTids = tblTids.Rows.Add(); rowTids["referenceid"] = xmlRow["playerid"]; rowTids["referencetype"] = 1; rowTids["tidbitorder"] = i; rowTids["enabled"] = 1; } else { rowTids = tblTids.Rows[0]; } rowTids["text"] = xmlRow["matrixnote" + i.ToString()].ToString(); adp.Update(tblTids.GetChanges()); tblTids.AcceptChanges(); cmd.Dispose(); adp.Dispose(); bldr.Dispose(); tblTids.Dispose(); } } } } finally { if (tblTids != null) tblTids.Dispose(); } playersImported++; worker.ReportProgress(playersImported / playersToImport); } //playerid > 0 else { playersNotFound++; // //write to report file with this player, not found // log.WriteLine(DateTime.Now + " --- Not found: " + xmlRow["firstname"].ToString() + " " + xmlRow["lastname"].ToString()); } } //foreach player } //try catch (Exception ex) { importErrors++; } finally { if (cmd != null) cmd.Dispose(); if (adp != null) adp.Dispose(); if (bldr != null) bldr.Dispose(); if (rdr != null) rdr.Dispose(); if (cn != null) cn.Close(); cn.Dispose(); //log.Close(); } }; //dowork worker.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs args) { SetStatusBarMsg(playersImported.ToString() + " of " + playersToImport.ToString() + " players successfully imported at " + DateTime.Now.ToLongTimeString() + ". Players not found by ID: " + playersNotFound.ToString() + ". Errors importing " + importErrors.ToString() + ".", "Green"); GlobalCollections.Instance.LoadPlayers(); }; worker.ProgressChanged += delegate(object s, ProgressChangedEventArgs args) { SetStatusBarMsg(playersImported.ToString() + " of " + playersToImport.ToString() + " players imported.", "Yellow"); }; worker.RunWorkerAsync(file); }