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