public static Boolean ExecuteListadeSetores(
ConnectionInfo pInfo, decimal pSET_ID, string pCHAVE
)
{
Boolean values;
try
{
string lQuery = "";
DataTable lTable = new DataTable();
OracleCommand cmd = new OracleCommand();
using (OracleConnection cn = new OracleConnection(pInfo.ConnectionString))
{
cmd.Connection = cn;
cmd.CommandText = "spLista";
cmd.CommandType = CommandType.StoredProcedure;
//cmd.CommandText = " BEGIN spLista (" + pSET_ID + ", " + pCHAVE + "); END; ";
cmd.Parameters.Add("pSET_ID", OracleDbType.Decimal).Value = pSET_ID;
cmd.Parameters.Add("pCHAVE", OracleDbType.Decimal).Value = pCHAVE;
cn.Open();
DataSet ds = new DataSet();
OracleDataAdapter Adapter = new OracleDataAdapter(cmd);
Adapter.Fill(ds);
Adapter.Dispose();
values = true;
cmd.Dispose();
cn.Dispose();
cn.Close();
}
return values;
}
catch (Exception ex)
{
values = false;
}
return values;
}
/// <summary>
/// Get New Dll names from db
/// </summary>
/// <returns></returns>
public DataSet GetDllNames()
{
OracleConnection con = new OracleConnection(GetOracleConnectionString());
string strsql = "select dll_name,dll_version from application_dll where application_version=2 order by dll_name";
try
{
OracleDataAdapter objda = new OracleDataAdapter(strsql, con);
DataSet ds = new DataSet();
objda.Fill(ds);
objda.Dispose();
return ds;
}
catch (Exception ex)
{
WriteLog(ex.ToString());
return null;
}
finally
{
if (con.State == ConnectionState.Open)
con.Dispose();
}
return null;
}
public DataSet ExecuteSqlDataSet(string sql)
{
OracleCommand cmd = new OracleCommand();
this.Connect();
OracleDataAdapter da = new OracleDataAdapter();
DataSet ds = new DataSet();
cmd.CommandTimeout = this.CommandTimeout;
cmd.Connection = _connection;
//if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
da.SelectCommand = cmd;
da.Fill(ds);
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection) this.Disconnect();
return ds;
}
void mgffill()
{
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string sqlquery = "SELECT MGF_NAME FROM MFG_INFO ORDER BY MGF_NAME";
OracleCommand cmd = new OracleCommand(sqlquery, conn);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
oda.Fill(dt);
if (dt.Rows.Count > 0)
{
comboBox1.DataSource = dt;
comboBox1.DisplayMember = "MGF_NAME";
if (comboBox1.Items.Count > 0)
{
comboBox1.SelectedIndex = -1;
}
}
oda.Dispose();
cmd.Dispose();
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
}
public String AllRecord(String action, ChartOfAccount chartOfAccount)
{
string result;
if (chartOfAccount == null)
{
OracleDataAdapter obj_ORCL = new OracleDataAdapter("crol_dml_coamst_st", GetConnection().ConnectionString);
obj_ORCL.SelectCommand.CommandType = CommandType.StoredProcedure;
obj_ORCL.SelectCommand.BindByName = true;
obj_ORCL.SelectCommand.Parameters.Add("p_coano", "");
obj_ORCL.SelectCommand.Parameters.Add("p_acc_code", "");
obj_ORCL.SelectCommand.Parameters.Add("p_acc_type", "");
obj_ORCL.SelectCommand.Parameters.Add("p_acc_name", "");
obj_ORCL.SelectCommand.Parameters.Add("p_user_cd", "");
obj_ORCL.SelectCommand.Parameters.Add("p_upduser_cd", "");
obj_ORCL.SelectCommand.Parameters.Add("p_rowid", "");
obj_ORCL.SelectCommand.Parameters.Add("p_action", action);
obj_ORCL.SelectCommand.Parameters.Add("p_dataset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable dt = new DataTable();
try
{
obj_ORCL.Fill(dt);
result = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
}
catch (Exception ex)
{
result = Newtonsoft.Json.JsonConvert.SerializeObject(ex.Message);
}
finally
{
obj_ORCL.Dispose();
dt.Dispose();
}
}
else
{
OracleDataAdapter obj_ORCL = new OracleDataAdapter("crol_dml_coamst_st", GetConnection().ConnectionString);
obj_ORCL.SelectCommand.CommandType = CommandType.StoredProcedure;
obj_ORCL.SelectCommand.BindByName = true;
obj_ORCL.SelectCommand.Parameters.Add("p_coano", chartOfAccount.p_coano);
obj_ORCL.SelectCommand.Parameters.Add("p_acc_code", chartOfAccount.p_acc_code);
obj_ORCL.SelectCommand.Parameters.Add("p_acc_type", chartOfAccount.p_acc_type);
obj_ORCL.SelectCommand.Parameters.Add("p_acc_name", chartOfAccount.p_acc_name);
obj_ORCL.SelectCommand.Parameters.Add("p_user_cd", chartOfAccount.p_user_cd);
obj_ORCL.SelectCommand.Parameters.Add("p_upduser_cd", chartOfAccount.p_upduser_cd);
obj_ORCL.SelectCommand.Parameters.Add("p_rowid", chartOfAccount.p_rowid);
obj_ORCL.SelectCommand.Parameters.Add("p_action", action);
obj_ORCL.SelectCommand.Parameters.Add("p_dataset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable dt = new DataTable();
try
{
obj_ORCL.Fill(dt);
result = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
}
catch (Exception ex)
{
result = Newtonsoft.Json.JsonConvert.SerializeObject(ex.Message);
}
finally
{
obj_ORCL.Dispose();
dt.Dispose();
}
}
return(result);
}
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 DataSet ExecuteProcedureToDataSet(string CommandText, params object[] parameterValues)
{
OracleConnection connection = null;
OracleCommand command = null;
OracleDataAdapter adapter = null;
DataSet ds = null;
string resp = "";
try
{
connection = CreateConnection();
command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = CommandText;
connection.Open();
OracleCommandBuilder.DeriveParameters(command);
int index = 0;
foreach (OracleParameter parameter in command.Parameters)
{
if (parameter.Direction == ParameterDirection.Input || parameter.Direction == ParameterDirection.InputOutput)
{
parameter.Value = parameterValues[index];
index++;
}
else
{
parameter.Value = new object();
index++;
}
}
//command.ExecuteNonQuery();
adapter = new OracleDataAdapter(command);
ds = new DataSet();
adapter.Fill(ds);
resp = "success";
}
catch (Exception ex)
{
resp = "error";
//throw;
}
finally
{
if (command != null)
{
command.Dispose();
command = null;
}
if (adapter != null)
{
adapter.Dispose();
adapter = null;
}
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
connection = null;
}
}
return(ds);
}
public override DataSet ExcuteProc(string procName, DbParameter[] parameters)
{
OpenConn();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = procName;
Cmd.Parameters.Clear();
Cmd.Parameters.AddRange(parameters);
DataSet ds = new DataSet();
DataAdapter adapter = null;
try
{
adapter = new OracleDataAdapter(Cmd as OracleCommand);
adapter.Fill(ds);
}
catch
{
throw;
}
finally
{
adapter.Dispose();
ds.Dispose();
Cmd.Dispose();
}
return ds;
}
//public void SetFocus(Control control)
//{
// StringBuilder sb = new StringBuilder();
// sb.Append("\r\n<script language='JavaScript'>\r\n");
// sb.Append("<!--\r\n");
// sb.Append("function SetFocus()\r\n");
// sb.Append("{\r\n");
// sb.Append("\tdocument.");
// Control p = control.Parent;
// while (!(p is System.Web.UI.HtmlControls.HtmlForm)) p = p.Parent;
// sb.Append(p.ClientID);
// sb.Append("['");
// sb.Append(control.UniqueID);
// sb.Append("'].focus();\r\n");
// sb.Append("}\r\n");
// sb.Append("window.onload = SetFocus;\r\n");
// sb.Append("// -->\r\n");
// sb.Append("</script>");
// control.Page.RegisterClientScriptBlock("SetFocus", sb.ToString());
//}
public DataGrid BindGrid(DataGrid dgrdGen, string strSql)
{
objConnection = open_connection();
if (open_con == true)
{
try
{
objDataSet = new DataSet();
objAdapter = new OracleDataAdapter(strSql, objConnection);
objAdapter.Fill(objDataSet, "role_id");
dgrdGen.DataSource = objDataSet.Tables["role_id"].DefaultView;
dgrdGen.DataBind();
grid_bind = true;
return dgrdGen;
}
catch (OracleException objError)
{
if (objError.Message.Substring(0, 21) == "Table does not exist.")
{
ErrorStr = ErrorMsg(objConnection, "MSG1201");
}
if (objError.Message.Substring(59, 30) == "ORA-00904: invalid column name")
{
ErrorStr = ErrorMsg(objConnection, "MSG1202");
}
grid_bind = false;
return null;
}
finally
{
objConnection.Close();
objConnection.Dispose();
objAdapter.Dispose();
}
}
else
{
return null;
}
}
private bool AtServerConnect2SpecifiedDatabase(List <string> AlistDatabaseProfile)
{
bool LBooReturn = true;
string LStrConnectParam = string.Empty;
string LStrDynamicSQL = string.Empty;
string LStrSelectC000 = App.GStrSpliterChar + App.GStrSpliterChar + App.GStrSpliterChar + App.GStrSpliterChar + "1";
DataSet LDataSetSelectReturn = new DataSet();
int LIntSelectedReturn = 0;
LStrDynamicSQL = "SELECT C002 FROM T_00_000 WHERE C000 = '" + LStrSelectC000 + "'";
IStrApplyReturn = "0.00.000";
#region 连接到MS SQL Server
if (AlistDatabaseProfile[0] == "2")
{
SqlConnection LSqlConnection = null;
try
{
LStrConnectParam = string.Format("Data Source={0},{1};Initial Catalog={2};User Id={3};Password={4}", AlistDatabaseProfile[1], AlistDatabaseProfile[2], AlistDatabaseProfile[5], AlistDatabaseProfile[3], AlistDatabaseProfile[4]);
LSqlConnection = new SqlConnection(LStrConnectParam);
LSqlConnection.Open();
SqlDataAdapter LSqlDataAdapter = new SqlDataAdapter(LStrDynamicSQL, LSqlConnection);
LIntSelectedReturn = LSqlDataAdapter.Fill(LDataSetSelectReturn);
LSqlDataAdapter.Dispose();
if (LIntSelectedReturn != 1)
{
return(false);
}
IStrApplyReturn = LDataSetSelectReturn.Tables[0].Rows[0][0].ToString();
}
catch (Exception ex)
{
LBooReturn = false;
IStrApplyReturn = ex.Message;
}
finally
{
if (LSqlConnection != null)
{
if (LSqlConnection.State == ConnectionState.Open)
{
LSqlConnection.Close();
}
LSqlConnection.Dispose(); LSqlConnection = null;
}
}
}
#endregion
#region 连接到 Oracle
if (AlistDatabaseProfile[0] == "3")
{
OracleConnection LOracleConnection = null;
try
{
LStrConnectParam = string.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0}) (PORT={1})))(CONNECT_DATA=(SERVICE_NAME= {2})));User Id={3}; Password={4}", AlistDatabaseProfile[1], AlistDatabaseProfile[2], AlistDatabaseProfile[5], AlistDatabaseProfile[3], AlistDatabaseProfile[4]);
LOracleConnection = new OracleConnection(LStrConnectParam);
LOracleConnection.Open();
OracleDataAdapter LOracleDataAdapter = new OracleDataAdapter(LStrDynamicSQL, LOracleConnection);
LIntSelectedReturn = LOracleDataAdapter.Fill(LDataSetSelectReturn);
LOracleDataAdapter.Dispose();
if (LIntSelectedReturn != 1)
{
return(false);
}
IStrApplyReturn = LDataSetSelectReturn.Tables[0].Rows[0][0].ToString();
}
catch (Exception ex)
{
LBooReturn = false;
IStrApplyReturn = ex.Message;
}
if (LOracleConnection != null)
{
if (LOracleConnection.State == ConnectionState.Open)
{
LOracleConnection.Close();
}
LOracleConnection.Dispose(); LOracleConnection = null;
}
}
#endregion
return(LBooReturn);
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
OracleCommand cmd = new OracleCommand($"SELECT owner, table_name FROM dba_tables WHERE owner <> 'SYS' AND owner <> 'SYSTEM' AND owner <> 'OUTLN' AND owner <> 'DBSFWUSER' AND owner <> 'DBSNMP' AND owner <> 'APPQOSSYS' AND owner <> 'GSMADMIN_INTERNAL' AND owner <> 'XDB' AND owner <> 'WMSYS' AND owner <> 'OJVMSYS' AND owner <> 'CTXSYS' AND owner <> 'ORDDATA' AND owner <> 'MDSYS' AND owner <> 'ORDSYS' AND owner <> 'OLAPSYS' AND owner <> 'LBACSYS' AND owner <> 'DVSYS' AND owner <> 'AUDSYS' AND owner <> 'DVSYS'", con);
con.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
dt.Clear();
da.Fill(dt);
da.Dispose();
if (dt.Rows.Count <= 0)
{
MessageBox.Show("Rỗng, Thử Lại");
}
else
{
dtg_DSTable.DataSource = dt;
}
}
catch (Exception)
{
MessageBox.Show("Lỗi!");
}
finally
{
con.Close();
}
string sql = "";
sql = "SELECT * FROM DBA_ROLES";
try
{
OracleCommand cmd = new OracleCommand(sql, con);
con.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
dt.Clear();
da.Fill(dt);
da.Dispose();
if (dt.Rows.Count <= 0)
{
MessageBox.Show("Rỗng, Thử Lại");
}
else
{
dtg__ROLE_USER.DataSource = dt;
}
}
catch (Exception)
{
MessageBox.Show("Lỗi!");
}
finally
{
con.Close();
}
String sql1 = "SELECT* FROM DBA_USERS order by created DESC";
try
{
OracleCommand cmd = new OracleCommand(sql1, con);
con.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
dt.Clear();
da.Fill(dt);
da.Dispose();
if (dt.Rows.Count <= 0)
{
MessageBox.Show("Rỗng, Thử Lại");
}
else
{
dtg__user.DataSource = dt;
}
}
catch (Exception)
{
MessageBox.Show("Lỗi!");
}
finally
{
con.Close();
}
label_column.Text = "";
label_table.Text = "";
cbb_table.Visible = false;
cbb_Column.Visible = false;
cbSelect.Visible = false;
cbUpdate.Visible = false;
cbInsert.Visible = false;
cbDelete.Visible = false;
cb__SELECT.Visible = false;
cb__INSERT.Visible = false;
cb__DELETE.Visible = false;
cb__UPDATE.Visible = false;
cb__WGO.Visible = false;
bt_thuhoiquyen.Visible = false;
bt_ChinhSuaquyen.Visible = false;
bt_XacNhan.Visible = false;
}
public bool fishpondGetCase(string ticketNo, ref sCaseInfo caseInfo, ref sCaseInfo remoteCaseInfo, string computerName)
{
bool okay = true;
_error = string.Empty;
try
{
OracleParameter par;
var cmd = new OracleCommand("ITS.OFFENCE_ADJUDICATION.lock_fishpond_user", DbConnection);
cmd.CommandType = CommandType.StoredProcedure;
// Inputs
par = new OracleParameter("P_USER_ID", _credentialID);
par.Direction = ParameterDirection.Input;
par.OracleDbType = OracleDbType.Int32;
cmd.Parameters.Add(par);
par = new OracleParameter("P_COMPUTER_NAME", computerName);
par.Direction = ParameterDirection.Input;
par.OracleDbType = OracleDbType.Varchar2;
cmd.Parameters.Add(par);
par = new OracleParameter("P_TICKET_NO", ticketNo);
par.Direction = ParameterDirection.Input;
par.OracleDbType = OracleDbType.Varchar2;
cmd.Parameters.Add(par);
// Outputs
cmd.Parameters.Add(new OracleParameter("O_VEHICLE_CURSOR", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "O_VEHICLE_CURSOR"));
cmd.Parameters.Add(new OracleParameter("O_OFFENCE_CURSOR", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "O_OFFENCE_CURSOR"));
cmd.Parameters.Add(new OracleParameter("O_LOCAL_IMAGE_CURSOR", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "O_LOCAL_IMAGE_CURSOR"));
cmd.Parameters.Add(new OracleParameter("O_REMOTE_IMAGE_CURSOR", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "O_REMOTE_IMAGE_CURSOR"));
par = new OracleParameter();
par.ParameterName = "O_OFFENCE_SET";
par.Direction = ParameterDirection.Output;
par.OracleDbType = OracleDbType.Int32;
par.Size = 4;
cmd.Parameters.Add(par);
par = new OracleParameter();
par.ParameterName = "O_CENTRAL_INDICATOR";
par.Direction = ParameterDirection.Output;
par.OracleDbType = OracleDbType.Int32;
par.Size = 4;
cmd.Parameters.Add(par);
var ds = new DataSet();
var da = new OracleDataAdapter(cmd);
int val = da.Fill(ds);
//mNPCapturedOnCentral = (int.Parse(cmd.Parameters["O_CENTRAL_INDICATOR"].Value.ToString()) == 1 ? true : false);
caseInitialise(out caseInfo);
caseInfo.mTicketNo = ticketNo;
caseInfo.mOffenceSet = int.Parse(cmd.Parameters[8].Value.ToString()); //Sit hierdie later terug en sien of dit werk (of vice versa)
vehicleFromCursor(ds.Tables[0], ref caseInfo);
offenceFromCursor(ds.Tables[1], ref caseInfo);
if (!imagesFromCursor(ds.Tables[2], ds.Tables[3], ref caseInfo, ref remoteCaseInfo))
{
okay = false;
}
ds.Dispose();
da.Dispose();
cmd.Dispose();
}
catch (Exception ex)
{
_error = ex.Message;
ErrorCheckFriendly();
okay = false;
}
//disconnect();
return(okay);
}
public bool caseReject(ref sCaseInfo caseInfo, ref sCaseInfo remoteCaseInfo, int reasonID, out int count, string computerName)
{
bool okay = true;
_error = string.Empty;
count = 0;
try
{
OracleParameter par;
//var cmd = new OracleCommand("OFFENCE_ADJUDICATION_CENTRAL.REJECT_ADJUDICATION_NOTES", DbConnection);
var cmd = new OracleCommand("ITS.OFFENCE_ADJUDICATION.reject_adj_multi_image", DbConnection);
cmd.CommandType = CommandType.StoredProcedure;
// Inputs
par = new OracleParameter("P_TICKET_NO", caseInfo.mTicketNo);
par.Direction = ParameterDirection.Input;
par.OracleDbType = OracleDbType.Varchar2;
cmd.Parameters.Add(par);
par = new OracleParameter("P_COMPUTER_NAME", computerName);
par.OracleDbType = OracleDbType.Varchar2;
par.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par);
par = new OracleParameter("P_USER_ID", _credentialID);
par.Direction = ParameterDirection.Input;
par.OracleDbType = OracleDbType.Int32;
cmd.Parameters.Add(par);
par = new OracleParameter("P_REJECTION_ID", reasonID);
par.Direction = ParameterDirection.Input;
par.OracleDbType = OracleDbType.Int32;
cmd.Parameters.Add(par);
par = new OracleParameter("P_NOTES", caseInfo.mOffenceNotes);
par.Direction = ParameterDirection.Input;
par.OracleDbType = OracleDbType.Varchar2;
cmd.Parameters.Add(par);
// Outputs
par = new OracleParameter();
par.ParameterName = "O_COUNT";
par.Direction = ParameterDirection.Output;
par.OracleDbType = OracleDbType.Int32;
par.Size = 4;
cmd.Parameters.Add(par);
cmd.Parameters.Add(new OracleParameter("O_VEHICLE_CURSOR", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "O_VEHICLE_CURSOR"));
cmd.Parameters.Add(new OracleParameter("O_OFFENCE_CURSOR", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "O_OFFENCE_CURSOR"));
cmd.Parameters.Add(new OracleParameter("O_LOCAL_IMAGE_CURSOR", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "O_LOCAL_IMAGE_CURSOR"));
cmd.Parameters.Add(new OracleParameter("O_REMOTE_IMAGE_CURSOR", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "O_REMOTE_IMAGE_CURSOR"));
par = new OracleParameter();
par.ParameterName = "O_OFFENCE_SET";
par.Direction = ParameterDirection.Output;
par.OracleDbType = OracleDbType.Int32;
par.Size = 4;
cmd.Parameters.Add(par);
par = new OracleParameter();
par.ParameterName = "O_CENTRAL_INDICATOR";
par.Direction = ParameterDirection.Output;
par.OracleDbType = OracleDbType.Int32;
par.Size = 4;
cmd.Parameters.Add(par);
var ds = new DataSet();
var da = new OracleDataAdapter(cmd);
int val = da.Fill(ds);
//mNPCapturedOnCentral = (int.Parse(cmd.Parameters["O_CENTRAL_INDICATOR"].Value.ToString()) == 1 ? true : false);
count = int.Parse(cmd.Parameters[5].Value.ToString());
if (count <= 0)
{
_error = "No more cases to Adjudicate exist in database.";
}
else
{
caseInfo.mOffenceSet = int.Parse(cmd.Parameters[10].Value.ToString());
vehicleFromCursor(ds.Tables[0], ref caseInfo);
offenceFromCursor(ds.Tables[1], ref caseInfo);
if (!imagesFromCursor(ds.Tables[2], ds.Tables[3], ref caseInfo, ref remoteCaseInfo))
{
okay = false;
}
}
ds.Dispose();
da.Dispose();
cmd.Dispose();
}
catch (Exception ex)
{
_error = ex.Message;
ErrorCheckFriendly();
okay = false;
}
return(okay);
}
/// <summary>
/// 更新一个记录集(使用connString)
/// </summary>
/// <param name="connString">ConnectString（Sql连接字符串）</param>
/// <param name="cmdInsertType">commandInsert类型</param>
/// <param name="cmdInsertText">SQL语句（Insert）</param>
/// <param name="cmdUpdateType">commandUpdate类型</param>
/// <param name="cmdUpdateText">SQL语句（Update）</param>
/// <param name="cmdInsertType">commandDelete类型</param>
/// <param name="cmdDeleteText">SQL语句（Delete）</param>
/// <param name="cmdInsertParms">InsertCommand参数</param>
/// <param name="cmdUpdateParms">UpdateCommand参数</param>
/// <param name="cmdDeleteParms">DeleteCommand参数</param>
/// <param name="dataset">dataset</param>
/// <param name="tablename">表名</param>
/// <returns>是否更新成功</returns>
public static bool UpdateDataset(string connString, CommandType cmdInsertType, string cmdInsertText, CommandType cmdUpdateType, string cmdUpdateText, CommandType cmdDeleteType, string cmdDeleteText, OracleParameter[] cmdInsertParms, OracleParameter[] cmdUpdateParms, OracleParameter[] cmdDeleteParms, DataSet dataset, string tablename)
{
OracleDataAdapter dsCommand = new OracleDataAdapter();
OracleConnection conn = new OracleConnection(connString);
bool isSuccess = false;
if (cmdInsertText != String.Empty)
{
OracleCommand cmdInsert = new OracleCommand();
cmdInsert.Connection = conn;
cmdInsert.CommandText = cmdInsertText;
cmdInsert.CommandType = cmdInsertType;
if (cmdInsertParms != null)
{
foreach (OracleParameter parm in cmdInsertParms)
{
cmdInsert.Parameters.Add(parm);
}
}
dsCommand.InsertCommand = cmdInsert;
}
if (cmdUpdateText != String.Empty)
{
OracleCommand cmdUpdate = new OracleCommand();
cmdUpdate.Connection = conn;
cmdUpdate.CommandText = cmdUpdateText;
cmdUpdate.CommandType = cmdUpdateType;
if (cmdUpdateParms != null)
{
foreach (OracleParameter parm in cmdUpdateParms)
{
cmdUpdate.Parameters.Add(parm);
}
}
dsCommand.UpdateCommand = cmdUpdate;
}
if (cmdDeleteText != String.Empty)
{
OracleCommand cmdDelete = new OracleCommand();
cmdDelete.Connection = conn;
cmdDelete.CommandText = cmdDeleteText;
cmdDelete.CommandType = cmdDeleteType;
if (cmdDeleteParms != null)
{
foreach (OracleParameter parm in cmdDeleteParms)
{
cmdDelete.Parameters.Add(parm);
}
}
dsCommand.DeleteCommand = cmdDelete;
}
if (cmdInsertText == String.Empty && cmdUpdateText == String.Empty && cmdDeleteText == String.Empty)
{
// OracleCommandBuilder scb = new OracleCommandBuilder(dsCommand);
isSuccess = false;
}
try
{
if (conn.State != ConnectionState.Open)
{
Open(conn);
//conn.Open();
}
dsCommand.Update(dataset, tablename);
if (dataset.HasErrors)
{
dataset.Tables[tablename].GetErrors()[0].ClearErrors();
isSuccess = false;
}
else
{
dataset.AcceptChanges();
isSuccess = true;
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (dsCommand != null)
{
dsCommand.Dispose();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(isSuccess);
}
public static DataSet LoadRecords(string SQL)
{
OracleDataAdapter da = new OracleDataAdapter();
OracleCommand cmd = new OracleCommand();
DataSet ds = new DataSet(); ;
string sql = string.Empty;
try
{
cmd.Connection = Conexao.GetInstance();
cmd.CommandType = CommandType.Text;
sql = SQL;
cmd.CommandText = sql;
da.SelectCommand = cmd;
da = new OracleDataAdapter(cmd);
da.Fill(ds, "Table");
//=====================================================================|
//Finaliza ds
da.Dispose();
cmd.Dispose();
return ds;
}
catch (Exception ex)
{
sErrMsg = "Atenção, a função de recuperação de dados (Certificados/Etiquetas) " +
"detectou o seguinte problema:" +
"\r\n" + "\r\n" +
"Descrição : " + ex.Message.ToString() + "\r\n" +
"Origem : " + ex.Source.ToString();
sErrCap = "Procedimento : " + ex.TargetSite.ToString();
MessageBox.Show(sErrMsg, sErrCap, MessageBoxButtons.OK,
MessageBoxIcon.Error);
da.Dispose();
cmd.Dispose();
ds.Dispose();
return null;
}
}
public String AllRecord(String action, BankAccount bankAccount)
{
string result;
if (bankAccount == null)
{
OracleDataAdapter obj_ORCL = new OracleDataAdapter("crol_dml_bankaccnt_st", GetConnection().ConnectionString);
obj_ORCL.SelectCommand.CommandType = CommandType.StoredProcedure;
obj_ORCL.SelectCommand.BindByName = true;
obj_ORCL.SelectCommand.Parameters.Add("p_bankno", "");
obj_ORCL.SelectCommand.Parameters.Add("p_bankcode", "");
obj_ORCL.SelectCommand.Parameters.Add("p_bankname", "");
obj_ORCL.SelectCommand.Parameters.Add("p_usercd", "");
obj_ORCL.SelectCommand.Parameters.Add("p_rowid", "");
obj_ORCL.SelectCommand.Parameters.Add("p_updusercd", "");
obj_ORCL.SelectCommand.Parameters.Add("p_action", action);
obj_ORCL.SelectCommand.Parameters.Add("p_dataset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable bank_dt = new DataTable();
try
{
obj_ORCL.Fill(bank_dt);
result = Newtonsoft.Json.JsonConvert.SerializeObject(bank_dt);
}
catch (Exception ex)
{
result = Newtonsoft.Json.JsonConvert.SerializeObject(ex.Message);
}
finally
{
obj_ORCL.Dispose();
bank_dt.Dispose();
}
return(result);
}
else
{
OracleDataAdapter obj_ORCL = new OracleDataAdapter("crol_dml_bankaccnt_st", GetConnection().ConnectionString);
obj_ORCL.SelectCommand.CommandType = CommandType.StoredProcedure;
obj_ORCL.SelectCommand.BindByName = true;
obj_ORCL.SelectCommand.Parameters.Add("p_bankno", bankAccount.bankNo);
obj_ORCL.SelectCommand.Parameters.Add("p_bankcode", bankAccount.bankCode);
obj_ORCL.SelectCommand.Parameters.Add("p_bankname", bankAccount.bankName);
obj_ORCL.SelectCommand.Parameters.Add("p_usercd", bankAccount.userCd);
obj_ORCL.SelectCommand.Parameters.Add("p_rowid", bankAccount.row_id);
obj_ORCL.SelectCommand.Parameters.Add("p_updusercd", bankAccount.upUserCd);
obj_ORCL.SelectCommand.Parameters.Add("p_action", action);
obj_ORCL.SelectCommand.Parameters.Add("p_dataset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable bank_dt = new DataTable();
try
{
obj_ORCL.Fill(bank_dt);
result = Newtonsoft.Json.JsonConvert.SerializeObject(bank_dt);
}
catch (Exception ex)
{
result = Newtonsoft.Json.JsonConvert.SerializeObject(ex.Message);
}
finally
{
obj_ORCL.Dispose();
bank_dt.Dispose();
}
return(result);
}
}
/// <summary>
/// Creates and runs a command to retrieve a DataTable using a command text
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>DataTable with the command text results</returns>
internal static DataTable GetDataTable(DBOracleConnection connection, string commandText, bool isStoredProcedure, params DBOracleParameter[] parameters)
{
try
{
using (var command = new DBOracleCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
OracleDataAdapter dataAdapter;
try
{
dataAdapter = new OracleDataAdapter(command.Command);
}
catch (Exception ex)
{
try
{
Log.Debug(DBOracleLocalizedText.DBOracleCommand_GetDataSet_DataAdapterError, ex);
}
catch (Exception)
{
// Nothing to do
}
throw;
}
bool suppressDisposeException = false;
try
{
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
return(dataTable);
}
catch (Exception ex)
{
try
{
Log.Debug(DBOracleLocalizedText.DBOracleCommand_GetDataSet_DataRetrieveError, ex);
}
catch (Exception)
{
// Nothing to do
}
suppressDisposeException = true;
throw;
}
finally
{
try
{
dataAdapter.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBOracleLocalizedText.DBOracleCommand_DataAdapter_Disposal_Error, ex);
}
catch (Exception)
{
// Nothing to do
}
if (!suppressDisposeException)
{
throw;
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertOracleException(isConnecting: false);
throw;
}
}
public FormView BindFormView(FormView grdVGenIn, string strSqlIn)
{
objConnection = open_connection();
if (open_con == true)
{
try
{
objDataSet = new DataSet();
objAdapter = new OracleDataAdapter(strSqlIn, objConnection);
objAdapter.Fill(objDataSet, "tblGrdV");
grdVGenIn.DataSource = objDataSet.Tables["tblGrdV"].DefaultView;
grdVGenIn.DataBind();
gridView_bind = true;
return grdVGenIn;
}
catch (OracleException objError)
{
if (objError.Message.Substring(0, 21) == "Table does not exist.")
{
ErrorStr = ErrorMsg(objConnection, "MSG1201");
}
else if (objError.Message.Substring(59, 25) == "ORA-00904: invalid column name")
{
ErrorStr = ErrorMsg(objConnection, "MSG1202");
}
else
{
ErrorStr = objError.Message;
err_flag = true;
}
gridView_bind = false;
return null;
}
finally
{
objConnection.Close();
objConnection.Dispose();
objAdapter.Dispose();
}
}
else
{
return 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;
}
}
public DataSet Fetch_DataSet(string strSql)
{
objConnection = open_connection();
objDataSet = new DataSet();
objAdapter = new OracleDataAdapter(strSql, objConnection);
objAdapter.Fill(objDataSet);
//objDatareader.Close();
objAdapter.Dispose();
objConnection.Close();
objConnection.Dispose();
return objDataSet;
}
protected DataSet ExecuteSelectCommandParamList(string commandText, List <OracleParameter> procParams)
{
OracleConnection cnx = null;
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter();
OracleCommand cmd = null;
try
{
//Setup command object
cmd = new OracleCommand(commandText);
cmd.CommandType = CommandType.Text;
if ((((procParams) != null)))
{
int index = 0;
while ((index < procParams.Count))
{
cmd.Parameters.Add(procParams[index]);
index = (index + 1);
}
}
da.SelectCommand = (OracleCommand)cmd;
//Determine the transaction owner and process accordingly
if (_isOwner)
{
cnx = new OracleConnection(GetConnectionString());
cmd.Connection = cnx;
cnx.Open();
}
else
{
cmd.Connection = _txn.Connection;
cmd.Transaction = _txn;
}
//Fill the dataset
try
{
da.Fill(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
catch (System.Exception ex)
{
throw;
}
finally
{
if ((((da) != null)))
{
da.Dispose();
}
if ((((cmd) != null)))
{
cmd.Dispose();
}
if (_isOwner)
{
cnx.Dispose();
cnx.Close();
//Implicitly calls cnx.Close()
}
}
return(ds);
}
public DataSet Fetch_StoredDataSet(string strSP, string tablename, params OracleParameter[] paramObj)
{
objConnection = open_connection();
objDataSet = new DataSet();
objCommand = new OracleCommand(strSP, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
if (paramObj != null)
{
foreach (OracleParameter paramVal in paramObj)
{
objCommand.Parameters.Add(paramVal);
}
}
OracleParameter oparam = objCommand.Parameters.Add("cur_user", OracleDbType.RefCursor);
oparam.Direction = ParameterDirection.Output;
objAdapter = new OracleDataAdapter(objCommand);
objAdapter.Fill(objDataSet, tablename);
//objDatareader.Close();
objAdapter.Dispose();
objConnection.Close();
objConnection.Dispose();
return objDataSet;
}
public DataTable EjecutarCmd(OracleCommand cmd)
{
DataTable resultado = new DataTable();
OracleDataAdapter adap = null;
bool continuar = true;
int contadorIntentos = 0;
bool disposeCmd = false;
if (_conexionPerdida)
{
return(resultado);
}
while (continuar && contadorIntentos < 3)
{
continuar = false;
try
{
adap = new OracleDataAdapter(cmd);
adap.Fill(resultado);
disposeCmd = true;
}
catch (OracleException oraEx)
{
switch (oraEx.Number)
{
case 3114:
_connexion.Dispose();
_connexion = null;
AsegurarConexion();
cmd.Connection = _connexion;
continuar = true;
break;
case 3113:
case 3135:
case -2:
OnConexionPerdida();
break;
}
PistaMgr.Instance.Error("CNDC.DAL", oraEx);
PistaMgr.Instance.Error("CNDC.DAL", cmd.CommandText);
PistaMgr.Instance.Error("CNDC.DAL", CNDC.UtilesComunes.CallStackMgr.GetPilaLlamadas());
EscribirLogDetalle("CNDC.DAL", (Exception)oraEx, cmd);
}
catch (Exception ex)
{
PistaMgr.Instance.Error("CNDC.DAL", ex);
PistaMgr.Instance.Error("CNDC.DAL", cmd.CommandText);
EscribirLogDetalle("CNDC.DAL", ex, cmd);
}
finally
{
if (disposeCmd || contadorIntentos == 2)
{
DisposeCommand(cmd);
}
if (adap != null)
{
adap.Dispose();
}
}
contadorIntentos++;
}
return(resultado);
}
public void run()
{
Exception exp = null;
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(cmdSelect);
da.DeleteCommand = cmdDelete;
da.UpdateCommand = cmdUpdate;
da.InsertCommand = new OracleCommand("", con);
cmdInsert = da.InsertCommand;
da.Fill(ds);
try
{
BeginCase("Dispose - check DataAdapter select command");
da.Dispose();
Compare(da.SelectCommand == null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check DataAdapter insert command");
Compare(da.InsertCommand == null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check DataAdapter Delete Command");
Compare(da.DeleteCommand == null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check DataAdapter update command");
Compare(da.UpdateCommand == null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check select command object");
Compare(cmdSelect != null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check delete command object");
Compare(cmdDelete != null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check insert command object");
Compare(cmdInsert != null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check Update command object");
Compare(cmdUpdate != null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check cmdSelect.connection object");
Compare(cmdSelect.Connection != null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check cmdDelete.connection object");
Compare(cmdDelete.Connection != null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check cmdUpdate.connection object");
Compare(cmdUpdate.Connection != null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check cmdInsert.connection object");
Compare(cmdInsert.Connection != null, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
try
{
BeginCase("Dispose - check command connection state");
da.Dispose();
Compare(cmdSelect.Connection.State, ConnectionState.Open);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
public static void HandlerData()
{
#region 统计
string startTime = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 00:00:00";
string datestring = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
string endTime = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
//分组查询获取昨天的数据值
string selectSql = string.Format(@"select SPOTTINGID,ILLEGALTYPENO,LEGALIZEILLEGALTYPENO,count(1) as COUNT from punish_illegalvehicle_real
where OCCURTIME>=to_date('{0}','yyyy-MM-dd HH24:mi:ss')
and OCCURTIME< to_date('{1}', 'yyyy-MM-dd HH24:mi:ss')
group by SPOTTINGID,LEGALIZEILLEGALTYPENO,ILLEGALTYPENO", startTime, endTime);
DataTable oldData = new DataTable();
try
{
using (OracleConnection conn = new OracleConnection(DbConn))
{
OracleDataAdapter oda = new OracleDataAdapter(selectSql, conn);
oda.Fill(oldData);
conn.Dispose();
}
}
catch (Exception e)
{
LogHelper.Error(string.Format("统计{0}至{1}的违法数据异常，异常信息：{2}", startTime, endTime, e.ToString()));
}
DataTable table = oldData.Copy();
LogHelper.WriteInfo(string.Format("统计{0}至{1}的违法数据，得到数据{2}条", startTime, endTime, table.Rows.Count.ToString()));
if (table.Rows.Count > 0)
{
#region 处理
table.Columns.Add("OCCERDATE", typeof(System.String));
table.Columns.Add("SGUID", typeof(System.String));
table.Columns.Add("CREATEDTIME", typeof(System.DateTime));
DateTime time = DateTime.Now;
//处理违法数据
foreach (DataRow item in table.Rows)
{
item["OCCERDATE"] = datestring;
item["SGUID"] = Guid.NewGuid().ToString("N");
item["CREATEDTIME"] = time;
}
LogHelper.WriteInfo(string.Format("处理统计{0}至{1}的违法数据，处理成功数据{2}条", startTime, endTime, table.Rows.Count.ToString()));
//插入昨天的统计数据到新表中
string delS1l = string.Format("delete PUNISH_ILLEGALVEHICLECOUNT where OCCERDATE='{0}'", datestring);
int result = 0;
try
{
using (OracleConnection conn = new OracleConnection(DbConn))
{
conn.Open();
OracleCommand cmd = new OracleCommand(delS1l, conn);
result = cmd.ExecuteNonQuery();
conn.Dispose();
}
}
catch (Exception e)
{
LogHelper.Error(string.Format("删除{0}的违法数据异常，异常信息{1}", datestring, e.ToString()));
}
LogHelper.WriteInfo(string.Format("删除{0}的违法数据{1}条", datestring, result));
#endregion
#region 插入
//插入
OracleConnection connection = null;
OracleCommand command = null;
OracleTransaction _transcation = null;
try
{
using (connection = new OracleConnection(DbConn))
{
string SQLString = "SELECT SPOTTINGID,ILLEGALTYPENO,LEGALIZEILLEGALTYPENO,COUNT,OCCERDATE,SGUID,CREATEDTIME FROM PUNISH_ILLEGALVEHICLECOUNT where 1=0";
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
OracleDataAdapter myDataAdapter = new OracleDataAdapter();
myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection);
myDataAdapter.UpdateBatchSize = 0;
DataTable dt = table.Copy();
DataTable dtTemp = new DataTable();
OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter);
myDataAdapter.Fill(dtTemp);
int times = 0;
for (int count = 0; count < dt.Rows.Count;)
{
for (int i = 0; i < 400 && 400 * times + i < dt.Rows.Count; i++)
{
dtTemp.Rows.Add(dt.Rows[count].ItemArray);
count++;
}
myDataAdapter.Update(dtTemp);
times++;
dtTemp.Rows.Clear();
}
dt.Dispose();
dtTemp.Dispose();
myDataAdapter.Dispose();
}
catch (System.Data.OracleClient.OracleException E)
{
connection.Close();
LogHelper.Error(string.Format("插入违法统计数据异常，异常信息{0}", E.ToString()));
}
}
}
}
catch (Exception e)
{
LogHelper.Error(string.Format("插入违法统计数据异常，异常信息{0}", e.ToString()));
}
//try
//{
// using (connection = new OracleConnection(DbConn))
// {
// connection.Open();
// _transcation = connection.BeginTransaction();
// using (command = connection.CreateCommand())
// {
// command.CommandText = "SELECT SPOTTINGID,ILLEGALTYPENO,LEGALIZEILLEGALTYPENO,COUNT,OCCERDATE,SGUID,CREATEDTIME FROM PUNISH_ILLEGALVEHICLECOUNT";// string.Format("SELECT {0} FROM PUNISH_ILLEGALVEHICLECOUNT", string.Join(",", columns));
// command.Transaction = _transcation;
// OracleDataAdapter da = new OracleDataAdapter(command);
// OracleCommandBuilder cb = new OracleCommandBuilder(da);
// da.InsertCommand = cb.GetInsertCommand(true);
// da.Update(table);
// _transcation.Commit();
// }
// }
//}
//catch (System.Exception ex)
//{
// LogHelper.Error(string.Format("插入违法统计数据异常，异常信息{0}", ex.ToString()));
// if (_transcation != null)
// {
// _transcation.Rollback();
// }
// throw ex;
//}
#endregion
}
LogHelper.WriteInfo(string.Format("违法统计数据处理完成，本次操作{0}数据{1}条", datestring, table.Rows.Count.ToString()));
IllegalMonitor.IllegalMonitor.HandlerIllegal(table, datestring);
#endregion
}
public LabelInfo GetOracleQuery(LabelInfo labelInfo)
{
string istr = labelInfo.LabelDefineData["LabelDataSource"];
string str2 = labelInfo.LabelDefineData["LabelSqlString"];
istr = PareProc(istr, labelInfo);
str2 = PareProc(str2, labelInfo);
if (string.IsNullOrEmpty(istr))
{
labelInfo.Error = 1;
labelInfo.LabelContent = new StringBuilder("[err:标签\"" + labelInfo.OriginalData["id"] + "\"数据源连接为空！]");
return(labelInfo);
}
if (string.IsNullOrEmpty(str2))
{
labelInfo.Error = 1;
labelInfo.LabelContent = new StringBuilder("[err:标签\"" + labelInfo.OriginalData["id"] + "\"查询语句为空！]");
return(labelInfo);
}
OracleConnection connection = new OracleConnection(istr);
connection.Open();
if (labelInfo.PageSize > 0)
{
string str3 = labelInfo.LabelDefineData["LabelSqlCount"];
str3 = PareProc(str3, labelInfo);
if (string.IsNullOrEmpty(str3))
{
labelInfo.Error = 1;
labelInfo.LabelContent = new StringBuilder("[err:标签\"" + labelInfo.OriginalData["id"] + "\"统计查询语句为空！]");
return(labelInfo);
}
str2 = str2.Replace("@pagesize", labelInfo.PageSize.ToString());
str3 = str3.Replace("@pagesize", labelInfo.PageSize.ToString());
int num = (labelInfo.Page - 1) * labelInfo.PageSize;
if (num < 0)
{
num = 0;
}
str2 = str2.Replace("@startrow", num.ToString());
OracleCommand command = new OracleCommand(str3.Replace("@startrow", num.ToString()), connection);
if (labelInfo.PageSize > 0)
{
try
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (reader[0] != DBNull.Value)
{
labelInfo.TotalPub = reader.GetInt32(0);
}
}
else
{
labelInfo.TotalPub = 0;
}
}
}
catch
{
labelInfo.TotalPub = 0;
}
}
}
else
{
str2 = str2.Replace("@pagesize", "1000").Replace("@startrow", "0");
}
OracleDataAdapter adapter = new OracleDataAdapter(str2, connection);
DataSet dataSet = new DataSet();
try
{
adapter.Fill(dataSet);
if (labelInfo.TotalPub == 0)
{
DataTable table = dataSet.Tables[0];
labelInfo.TotalPub = table.Rows.Count;
}
labelInfo.LabelContent = new StringBuilder(dataSet.GetXml());
}
catch (OracleException exception)
{
labelInfo.Error = 2;
labelInfo.LabelContent = new StringBuilder("[err:标签\"" + labelInfo.OriginalData["id"] + "\"Oracle数据源读取错，原因：" + exception.Message + "]");
}
finally
{
adapter.Dispose();
}
connection.Dispose();
return(labelInfo);
}
public DataTable RetrieveRecords()
{
OracleConnection con = new OracleConnection(cnString);
OracleDataAdapter da = new OracleDataAdapter("select employeeid, firstname, lastname, email, phonenumber from employeess", con);
//da.SelectCommand.CommandType = CommandType.StoredProcedure;
OracleCommandBuilder bui = new OracleCommandBuilder(da);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
return dt;
}
catch
{
throw;
}
finally
{
da.Dispose();
con.Close();
con.Dispose();
}
}
void mgffill()
{
try
{
conn.Open();
string sqlquery = "SELECT MGF_NAME FROM MFG_INFO ORDER BY MGF_NAME";
OracleCommand cmd = new OracleCommand(sqlquery, conn);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
oda.Fill(dt);
if (dt.Rows.Count > 0)
{
comboBox1.DataSource = dt;
comboBox1.DisplayMember = "MGF_NAME";
//comboBox1.ValueMember = "Cust_Name";
}
oda.Dispose();
cmd.Dispose();
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
}
public void ExecuteSqlDataSet(ref DataSet dataSet, string sql, string tableName)
{
OracleCommand cmd = new OracleCommand();
this.Connect();
OracleDataAdapter da = new OracleDataAdapter();
cmd.CommandTimeout = this.CommandTimeout;
cmd.Connection = _connection;
//if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
da.SelectCommand = cmd;
da.Fill(dataSet, tableName);
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection) this.Disconnect();
}
public DataTable ExecuteReaderToDataTable(string TableName)
{
try
{
DataTable dt = new DataTable(TableName);
OracleDataAdapter da = new OracleDataAdapter((OracleCommand)this.Command);
da.Fill(dt);
da.Dispose();
return dt;
}
catch (Exception ex)
{
LoggingHelper.Debug("-----Oracle ExecuteReaderToDataTable Error----");
LoggingHelper.Debug(ex.Message);
return null;
}
}
protected void cautaBtn_Click(object sender, EventArgs e)
{
if (numeTB.Text.Trim() != "" && prenumeTB.Text.Trim() == "")
{
try
{
con.Open();
string cmd = "select NrCrt as \"Nr.crt.\", Nume as \"Nume\", Prenume as \"Prenume\", Functie as \"Functie\", SalarBaza as \"Salariu de baza\", SporProcent as \"Spor (%)\", PremiiBrute as \"Premii brute\", TotalBrut as \"Total brut\", BrutImpozabil as \"Brut impozabil\", Impozit as \"Impozit\", CAS, CASS, Retineri as \"Retineri salariale\", ViratCard as \"Virat pe card\" from Angajati where Nume='" + numeTB.Text.Trim() + "' order by NrCrt";
oDa = new OracleDataAdapter(cmd, con);
ds = new DataSet();
oDa.Fill(ds, "Angajati");
if (ds.Tables["Angajati"].Rows.Count > 0)
{
LabelCautareErr.Text = "Cautarea a avut succes!";
LabelCautareErr.ForeColor = Color.Green;
IncarcareRaport(ds);
}
else
{
LabelCautareErr.Text = "Angajatul cu numele '" + numeTB.Text.Trim() + "' nu a fost gasit!";
LabelCautareErr.ForeColor = Color.Red;
}
oDa.Dispose();
ds.Dispose();
}
catch (Exception ex)
{
LabelCautareErr.Text = "Nu se poate realiza conexiunea la baza de date: " + ex.Message;
LabelCautareErr.ForeColor = Color.Red;
}
finally
{
con.Close();
}
}
else if (numeTB.Text.Trim() == "" && prenumeTB.Text.Trim() != "")
{
try
{
con.Open();
string cmd = "select NrCrt as \"Nr.crt.\", Nume as \"Nume\", Prenume as \"Prenume\", Functie as \"Functie\", SalarBaza as \"Salariu de baza\", SporProcent as \"Spor (%)\", PremiiBrute as \"Premii brute\", TotalBrut as \"Total brut\", BrutImpozabil as \"Brut impozabil\", Impozit as \"Impozit\", CAS, CASS, Retineri as \"Retineri salariale\", ViratCard as \"Virat pe card\" from Angajati where Prenume like '%" + prenumeTB.Text.Trim() + "%' order by NrCrt";
oDa = new OracleDataAdapter(cmd, con);
ds = new DataSet();
oDa.Fill(ds, "Angajati");
if (ds.Tables["Angajati"].Rows.Count > 0)
{
LabelCautareErr.Text = "Cautarea a avut succes!";
LabelCautareErr.ForeColor = Color.Green;
IncarcareRaport(ds);
}
else
{
LabelCautareErr.Text = "Angajatul cu prenumele '" + prenumeTB.Text.Trim() + "' nu a fost gasit!";
LabelCautareErr.ForeColor = Color.Red;
}
oDa.Dispose();
ds.Dispose();
}
catch (Exception ex)
{
LabelCautareErr.Text = "Nu se poate realiza conexiunea la baza de date: " + ex.Message;
LabelCautareErr.ForeColor = Color.Red;
}
finally
{
con.Close();
}
}
else if (numeTB.Text.Trim() != "" && prenumeTB.Text.Trim() != "")
{
try
{
con.Open();
string cmd = "select NrCrt as \"Nr.crt.\", Nume as \"Nume\", Prenume as \"Prenume\", Functie as \"Functie\", SalarBaza as \"Salariu de baza\", SporProcent as \"Spor (%)\", PremiiBrute as \"Premii brute\", TotalBrut as \"Total brut\", BrutImpozabil as \"Brut impozabil\", Impozit as \"Impozit\", CAS, CASS, Retineri as \"Retineri salariale\", ViratCard as \"Virat pe card\" from Angajati where Nume='" + numeTB.Text.Trim() + "' and Prenume like '%" + prenumeTB.Text.Trim() + "%' order by NrCrt";
oDa = new OracleDataAdapter(cmd, con);
ds = new DataSet();
oDa.Fill(ds, "Angajati");
if (ds.Tables["Angajati"].Rows.Count > 0)
{
LabelCautareErr.Text = "Cautarea a avut succes!";
LabelCautareErr.ForeColor = Color.Green;
IncarcareRaport(ds);
}
else
{
LabelCautareErr.Text = "Angajatul '" + numeTB.Text.Trim() + " " + prenumeTB.Text.Trim() + "' nu a fost gasit!";
LabelCautareErr.ForeColor = Color.Red;
}
oDa.Dispose();
ds.Dispose();
}
catch (Exception ex)
{
LabelCautareErr.Text = "Nu se poate realiza conexiunea la baza de date: " + ex.Message;
LabelCautareErr.ForeColor = Color.Red;
}
finally
{
con.Close();
}
}
else
{
LabelCautareErr.Text = "Nu ati introdus criteriul de cautare!";
LabelCautareErr.ForeColor = Color.Red;
}
}
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);
}
public void CheckSMSStatus(Object stateInfo)
{
try
{
var _url = ConfigurationManager.AppSettings["ServiceURL"];
var _user = ConfigurationManager.AppSettings["wsUser"];
var _pass = ConfigurationManager.AppSettings["wsPwd"];
var connstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
var ws = new LG.SMS.Service();
ws.Url = _url;
var sql = " SELECT moseq " +
",shortcode " +
",cell_no " +
",msgbody " +
",msgtype " +
",mttotalseq " +
",mtseqref " +
",cpid" +
" FROM tb_mt_hist " +
" WHERE send_flag ='N' AND ROWNUM <=100";
var oradbConnection = new OracleConnection(connstring);
var sqlcmd = new OracleCommand(sql, oradbConnection);
var _dataAdapter = new OracleDataAdapter();
_dataAdapter.SelectCommand = sqlcmd;
var _dataTable = new DataTable();
_dataAdapter.Fill(_dataTable);
StringBuilder rtbLog = new StringBuilder();
sql = string.Empty;
int count = (_dataTable.Rows == null ? 0 : _dataTable.Rows.Count);
//Task[] tasks = new Task[] { };
//if (count > 0) Array.Resize(ref tasks, count);
int index = 0;
for (index = 0; index < count; index++)
{
int idtm = index;
//Application.DoEvents();
// Dim _mt_send_datetime = Format(Date.Now, "MMddyyyyHHmmss")
//tasks[idtm] = Task.Factory.StartNew(() =>
{
DataRow _row = _dataTable.Rows[idtm];
System.Console.WriteLine("<==============================MT sending==============================>");
System.Console.WriteLine("MT started : " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + " to " + (_row["cell_no"] == null ? string.Empty : _row["cell_no"].ToString()));
var _result = ws.SendMT((_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()),
(_row["shortcode"] == null ? string.Empty : _row["shortcode"].ToString()),
(_row["cell_no"] == null ? string.Empty : _row["cell_no"].ToString()),
(_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()),
(_row["msgtype"] == null ? string.Empty : _row["msgtype"].ToString()),
(_row["mttotalseq"] == null ? string.Empty : _row["mttotalseq"].ToString()),
(_row["mtseqref"] == null ? string.Empty : _row["mtseqref"].ToString()),
(_row["cpid"] == null ? string.Empty : _row["cpid"].ToString()),
"1",
_user,
_pass);
//int _result = 200;
byte attemp = 1;
int mo_cnt = 0;
do
{
var _mt_sent_datetime = DateTime.Now.ToString("MMddyyyyHHmmss");
if (attemp <= 3)
{
if (_result == 200)
{
sql = " UPDATE tb_mt_hist SET send_flag ='Y', " +
" result ='" + _result.ToString() + "'," +
" send_time ='" + _mt_sent_datetime + "', " +
" finish_time ='" + _mt_sent_datetime + "' " +
" WHERE moseq ='" + (_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()) + "'";
System.Console.WriteLine("Success :" + (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()) + "=>" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
break;
}
else
{
sql = " UPDATE tb_mt_hist SET result ='" + _result.ToString() + "', " +
" send_time ='" + _mt_sent_datetime + "'" +
" WHERE moseq ='" + (_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()) + "'";
System.Console.WriteLine("Fail : " + (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()) + "=>" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
attemp++;
}
}
else
{
sql = " UPDATE tb_mt_hist SET send_flag ='Y', " +
" result ='" + _result.ToString() + "'," +
" send_time ='" + _mt_sent_datetime + "', " +
" finish_time ='" + _mt_sent_datetime + "' " +
" WHERE moseq ='" + (_row["moseq"] == null ? string.Empty : _row["moseq"].ToString()) + "'";
System.Console.WriteLine("Fail : " + (_row["msgbody"] == null ? string.Empty : _row["msgbody"].ToString()) + "=>" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
break;
}
mo_cnt++;
} while (attemp == 5);
mo_cnt++;
System.Console.WriteLine("...done, cnt : " + mo_cnt.ToString());
var oradbConnectionSub = new OracleConnection(connstring);
oradbConnectionSub.Open();
using (var sqlcmdsub = new OracleCommand(sql, oradbConnectionSub))
{
sqlcmdsub.CommandType = CommandType.Text;
sqlcmdsub.ExecuteNonQuery();
}
oradbConnectionSub.Close();
oradbConnectionSub.Dispose();
}//);
}
//Task.WaitAll(tasks);
sqlcmd.Dispose();
_dataAdapter.Dispose();
_dataTable.Dispose();
oradbConnection.Close();
oradbConnection.Dispose();
}
catch (Exception ex)
{
System.Console.WriteLine("<==========================MT sending errors===============================>");
System.Console.WriteLine(ex.Message + ", " + DateTime.Now.ToString("yyyyMMdd HH:mm:ss"));
}
string stt = smsindex.ToString();
if (smsindex % 10 == 1)
{
stt += "ST";
}
else if (smsindex % 10 == 2)
{
stt += "ND";
}
else if (smsindex % 10 == 3)
{
stt += "RD";
}
else
{
stt += "TH";
}
System.Console.WriteLine("---------------------------------------------------------------------------");
System.Console.WriteLine("---------------------- END SMS CONSOLE APPLICATION AT " + stt.PadRight(21, '-'));
System.Console.WriteLine("---------------------------------------------------------------------------");
System.Console.WriteLine("\n\n");
smsindex++;
//reset index
if (smsindex > 1000000)
{
smsindex = 0;
}
System.Console.Read();
}
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 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 bool TradePick(Pick pick, Team newTeam)
{
OracleConnection cn = null;
OracleCommand cmd = null;
OracleDataAdapter adp = null;
MySqlConnection cnMySql = null;
MySqlCommand cmdMySql = null;
DataTable tbl = null;
string sql = "";
bool saved = false;
int fromTeam = 0;
try
{
cn = createConnectionSDR();
sql = "select teamid from draftorder where pick = " + pick.OverallPick;
cmd = new OracleCommand(sql, cn);
adp = new OracleDataAdapter(cmd);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
fromTeam = int.Parse(tbl.Rows[0][0].ToString());
}
sql = "update draftorder set teamid = " + newTeam.ID + " where pick = " + pick.OverallPick;
cmd = new OracleCommand(sql, cn);
cmd.ExecuteNonQuery();
//update the MySql database's teams table with the new total pick counts
try
{
cnMySql = createConnectionMySql();
sql = "select count(*) from draftorder where teamid = " + fromTeam;
cmd = new OracleCommand(sql, cn);
adp = new OracleDataAdapter(cmd);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
sql = "update teams set totalpicks = '" + tbl.Rows[0][0].ToString() + "' where id = " + fromTeam;
cmdMySql = new MySqlCommand(sql, cnMySql);
cmdMySql.ExecuteNonQuery();
}
sql = "select count(*) from draftorder where teamid = " + newTeam.ID;
cmd = new OracleCommand(sql, cn);
adp = new OracleDataAdapter(cmd);
tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
sql = "update teams set totalpicks = '" + tbl.Rows[0][0].ToString() + "' where id = " + newTeam.ID;
cmdMySql = new MySqlCommand(sql, cnMySql);
cmdMySql.ExecuteNonQuery();
}
}
finally
{
cmd.Dispose();
adp.Dispose();
tbl.Dispose();
}
saved = true;
}
finally
{
if (cmd != null) cmd.Dispose();
if (cn != null) cn.Close(); cn.Dispose();
}
return saved;
}
public static void ExportToExcelNopi(string FilePath, string SqlQuery)
{
DataTable dt = new DataTable();
OracleConnection conn = WebTools.GetIpmsConnection();
OracleCommand cmd = new OracleCommand(SqlQuery, conn);
// create data adapter
OracleDataAdapter da = new OracleDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(dt);
// close objects
conn.Close();
da.Dispose();
cmd.Dispose();
using (FileStream stream = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
ICreationHelper cH = wb.GetCreationHelper();
// create header
IRow header_row = sheet.CreateRow(0); // first row = 0
// style for heading
IFont boldFont = wb.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle header_style = wb.CreateCellStyle();
header_style.SetFont(boldFont);
//boldStyle.BorderBottom = BorderStyle.Medium;
header_style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
header_style.FillPattern = FillPattern.SolidForeground;
header_style.Alignment = HorizontalAlignment.Center;
header_style.VerticalAlignment = VerticalAlignment.Center;
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = header_row.CreateCell(j);
cell.SetCellValue(cH.CreateRichTextString(dt.Columns[j].ColumnName));
cell.CellStyle = header_style;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
DataRow drow = dt.Rows[i];
string str_value = drow.ItemArray[j].ToString();
ICell cell = row.CreateCell(j);
if (dt.Columns[j].DataType == typeof(DateTime) && drow.ItemArray[j] != DBNull.Value)
{
cell.SetCellValue(DateTime.Now);
IDataFormat dataFormatCustom = wb.CreateDataFormat();
DateTime?date_field = drow.Field <DateTime?>(j);
//cell.CellStyle.DataFormat = dataFormatCustom.GetFormat("yyyyMMdd HH:mm:ss");
//DateTime.ParseExact(str_value, "dd/M/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture)
cell.SetCellValue(date_field.Value);
ICellStyle date_style = wb.CreateCellStyle();
date_style.DataFormat = dataFormatCustom.GetFormat("dd-mmm-yyyy;@");
cell.CellStyle = date_style;
}
{
cell.SetCellValue(cH.CreateRichTextString(str_value.ToString()));
}
}
}
wb.Write(stream);
}
}
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;
}
protected void cautaBtn_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
if (numeTB.Text.Trim() != "" && prenumeTB.Text.Trim() == "")
{
try
{
con.Open();
string cmd = "select NrCrt as \"Numar curent\", Nume as \"Nume\", Prenume as \"Prenume\", Functie as \"Functie\", SalarBaza as \"Salariu de baza\", SporProcent as \"Spor (%)\", PremiiBrute as \"Premii brute\", TotalBrut as \"Total brut\", BrutImpozabil as \"Brut impozabil\", Impozit as \"Impozit\", CAS, CASS, Retineri as \"Retineri salariale\", ViratCard as \"Virat pe card\" from Angajati where Nume='" + numeTB.Text.Trim() + "' order by NrCrt";
//string cmd = "select * from Angajati where Nume='" + numeTB.Text.Trim() + "'";
oDa = new OracleDataAdapter(cmd, con);
ds = new DataSet();
oDa.Fill(ds, "Angajati");
GridView1.DataSource = ds.Tables["Angajati"].DefaultView;
GridView1.DataBind();
if (ds.Tables["Angajati"].Rows.Count > 0)
{
if (btn.Text == "Cauta")
{
LabelCautareErr.Text = "Cautarea a avut succes!";
LabelCautareErr.ForeColor = Color.Green;
}
if (ds.Tables["Angajati"].Rows.Count == 1)
{
if (btn.Text == "Cauta")
{
GridView1.SelectedIndex = 0;
}
else
{
GridView1.SelectedIndex = -1;
}
stergeBtn.Visible = true;
}
else
{
stergeBtn.Visible = true;
}
}
else
{
if (btn.Text == "Cauta")
{
LabelCautareErr.Text = "Angajatul cu numele '" + numeTB.Text.Trim() + "' nu a fost gasit!";
LabelCautareErr.ForeColor = Color.Red;
stergeBtn.Visible = false;
}
}
oDa.Dispose();
ds.Dispose();
}
catch (Exception ex)
{
LabelCautareErr.Text = "Nu se poate realiza conexiunea la baza de date: " + ex.Message;
LabelCautareErr.ForeColor = Color.Red;
stergeBtn.Visible = false;
}
finally
{
con.Close();
}
}
else if (numeTB.Text.Trim() == "" && prenumeTB.Text.Trim() != "")
{
try
{
con.Open();
string cmd = "select NrCrt as \"Numar curent\", Nume as \"Nume\", Prenume as \"Prenume\", Functie as \"Functie\", SalarBaza as \"Salariu de baza\", SporProcent as \"Spor (%)\", PremiiBrute as \"Premii brute\", TotalBrut as \"Total brut\", BrutImpozabil as \"Brut impozabil\", Impozit as \"Impozit\", CAS, CASS, Retineri as \"Retineri salariale\", ViratCard as \"Virat pe card\" from Angajati where Prenume like '%" + prenumeTB.Text.Trim() + "%' order by NrCrt";
//string cmd = "select * from Angajati where Nume='" + numeTB.Text.Trim() + "'";
oDa = new OracleDataAdapter(cmd, con);
ds = new DataSet();
oDa.Fill(ds, "Angajati");
GridView1.DataSource = ds.Tables["Angajati"].DefaultView;
GridView1.DataBind();
if (ds.Tables["Angajati"].Rows.Count > 0)
{
if (btn.Text == "Cauta")
{
LabelCautareErr.Text = "Cautarea a avut succes!";
LabelCautareErr.ForeColor = Color.Green;
}
if (ds.Tables["Angajati"].Rows.Count == 1)
{
if (btn.Text == "Cauta")
{
GridView1.SelectedIndex = 0;
}
else
{
GridView1.SelectedIndex = -1;
}
stergeBtn.Visible = true;
}
else
{
stergeBtn.Visible = true;
}
}
else
{
if (btn.Text == "Cauta")
{
LabelCautareErr.Text = "Angajatul cu prenumele '" + prenumeTB.Text.Trim() + "' nu a fost gasit!";
LabelCautareErr.ForeColor = Color.Red;
stergeBtn.Visible = false;
}
}
oDa.Dispose();
ds.Dispose();
}
catch (Exception ex)
{
LabelCautareErr.Text = "Nu se poate realiza conexiunea la baza de date: " + ex.Message;
LabelCautareErr.ForeColor = Color.Red;
stergeBtn.Visible = false;
}
finally
{
con.Close();
}
}
else if (numeTB.Text.Trim() != "" && prenumeTB.Text.Trim() != "")
{
try
{
con.Open();
string cmd = "select NrCrt as \"Numar curent\", Nume as \"Nume\", Prenume as \"Prenume\", Functie as \"Functie\", SalarBaza as \"Salariu de baza\", SporProcent as \"Spor (%)\", PremiiBrute as \"Premii brute\", TotalBrut as \"Total brut\", BrutImpozabil as \"Brut impozabil\", Impozit as \"Impozit\", CAS, CASS, Retineri as \"Retineri salariale\", ViratCard as \"Virat pe card\" from Angajati where Nume='" + numeTB.Text.Trim() + "' and Prenume like '%" + prenumeTB.Text.Trim() + "%' order by NrCrt";
//string cmd = "select * from Angajati where Nume='" + numeTB.Text.Trim() + "'";
oDa = new OracleDataAdapter(cmd, con);
ds = new DataSet();
oDa.Fill(ds, "Angajati");
GridView1.DataSource = ds.Tables["Angajati"].DefaultView;
GridView1.DataBind();
if (ds.Tables["Angajati"].Rows.Count > 0)
{
if (btn.Text == "Cauta")
{
LabelCautareErr.Text = "Cautarea a avut succes!";
LabelCautareErr.ForeColor = Color.Green;
}
if (ds.Tables["Angajati"].Rows.Count == 1)
{
if (btn.Text == "Cauta")
{
GridView1.SelectedIndex = 0;
}
else
{
GridView1.SelectedIndex = -1;
}
stergeBtn.Visible = true;
}
else
{
stergeBtn.Visible = true;
}
}
else
{
if (btn.Text == "Cauta")
{
LabelCautareErr.Text = "Angajatul '" + numeTB.Text.Trim() + " " + prenumeTB.Text.Trim() + "' nu a fost gasit!";
LabelCautareErr.ForeColor = Color.Red;
stergeBtn.Visible = false;
}
}
oDa.Dispose();
ds.Dispose();
}
catch (Exception ex)
{
LabelCautareErr.Text = "Nu se poate realiza conexiunea la baza de date: " + ex.Message;
LabelCautareErr.ForeColor = Color.Red;
stergeBtn.Visible = false;
}
finally
{
con.Close();
}
}
else if (numeTB.Text.Trim() == "" && prenumeTB.Text.Trim() == "" && GridView1.Rows.Count > 0)
{
stergeBtn.Visible = false;
GridView1.SelectedIndex = -1;
try
{
IncarcareDate();
LabelPrincipalErr.Text = "";
}
catch (Exception ex)
{
LabelPrincipalErr.Text = "Nu se poate realiza conexiunea la baza de date: " + ex.Message;
LabelPrincipalErr.ForeColor = Color.Red;
}
finally
{
con.Close();
}
}
else
{
LabelCautareErr.Text = "Nu ati introdus criteriul de cautare!";
LabelCautareErr.ForeColor = Color.Red;
stergeBtn.Visible = false;
}
}
private void AsegurarDatosInicioSesion()
{
OracleCommand cmd = _conexion.CrearCommand();
cmd.CommandText = "INICIOSESSION.InformacionSession";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_refcursorAgente = new OracleParameter();
OracleParameter p_refcursorRol = new OracleParameter();
OracleParameter p_refcursorDatosUsuario = new OracleParameter();
OracleParameter p_refcursorSession = new OracleParameter();
OracleParameter p_refcursorRolEmpresa = new OracleParameter();
p_refcursorAgente.OracleDbType = OracleDbType.RefCursor;
p_refcursorRol.OracleDbType = OracleDbType.RefCursor;
p_refcursorDatosUsuario.OracleDbType = OracleDbType.RefCursor;
p_refcursorSession.OracleDbType = OracleDbType.RefCursor;
p_refcursorRolEmpresa.OracleDbType = OracleDbType.RefCursor;
p_refcursorAgente.Direction = ParameterDirection.Output;
p_refcursorRol.Direction = ParameterDirection.Output;
p_refcursorDatosUsuario.Direction = ParameterDirection.Output;
p_refcursorSession.Direction = ParameterDirection.Output;
p_refcursorRolEmpresa.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_refcursorAgente);
cmd.Parameters.Add(p_refcursorRol);
cmd.Parameters.Add(p_refcursorDatosUsuario);
cmd.Parameters.Add(p_refcursorSession);
cmd.Parameters.Add(p_refcursorRolEmpresa);
PistaMgr.Instance.Debug("Sesion", "Previo Sesion");
PistaMgr.Instance.Debug("Sesion", "Iniciando Sesion");
DataSet ds = new DataSet();
OracleDataAdapter da = null;
try
{
PistaMgr.Instance.Debug("Sesion", "Iniciando TRY");
da = new OracleDataAdapter(cmd);
da.Fill(ds);
PistaMgr.Instance.Debug("Sesion", "Fin TRY");
}
catch (Exception excep)
{
PistaMgr.Instance.Error("Sesion", excep);
}
foreach (DataTable tabla in ds.Tables)
{
PistaMgr.Instance.Debug("Sesion", string.Format("TABLA: {0} REGISTROS:{1}", tabla.TableName, tabla.Rows.Count));
}
_empresaActual = new Persona(ds.Tables[0].Rows[0]);
_rolesActuales = readRoles(ds.Tables[1]);
_usuarioActual = new Usuario(ds.Tables[2].Rows[0]);
_rolSIN = ObjetoDeNegocio.GetValor <string>(ds.Tables[4].Rows[0]["NOM_ROL"]);
ReadValuesSession(ds.Tables[3]);
_conexion.DisposeCommand(cmd);
if (da != null)
{
da.Dispose();
}
_idSesion = GetIdSesionBD();
PistaMgr.Instance.Debug("Sesion", "Sesion Iniciada IdSesion = " + _idSesion);
}
protected DataSet ExecuteDataSet(ref OracleCommand cmd, string procName, params IDataParameter[] procParams)
{
OracleConnection cnx = null;
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter();
cmd = null;
try
{
//Setup command object
cmd = new OracleCommand(procName);
cmd.CommandType = CommandType.StoredProcedure;
if ((((procParams) != null)))
{
int index = 0;
while ((index < procParams.Length))
{
cmd.Parameters.Add(procParams[index]);
index = (index + 1);
}
}
da.SelectCommand = (OracleCommand)cmd;
//Determine the transaction owner and process accordingly
if (_isOwner)
{
cnx = new OracleConnection(GetConnectionString());
cmd.Connection = cnx;
cnx.Open();
}
else
{
cmd.Connection = _txn.Connection;
cmd.Transaction = _txn;
}
//Fill the dataset
try
{
da.Fill(ds);
}
catch {
}
}
catch (System.Exception ex)
{
throw;
}
finally
{
if ((((da) != null)))
{
da.Dispose();
}
if ((((cmd) != null)))
{
cmd.Dispose();
cmd.Parameters.AddRange(procParams); // Added from malik hourani to put the parameters
}
if (_isOwner)
{
cnx.Dispose();
cnx.Close();
//Implicitly calls cnx.Close()
}
}
return(ds);
}
//Ejecuta el query y retorna datos en el tipo que se requiera
public object Query(string strSQL, TipoDato eTipoDato)
{
object query = new object();
DataSet dtsTemp = new DataSet();
DataTable dttTemp = new DataTable();
DataView dtvTemp = new DataView();
switch (motorBD)
{
case "SqlServer":
SqlConnection cnConexionSQL = new SqlConnection(mstrConnectionStringMSSQL);
SqlCommand cmComandoSQL = new SqlCommand(strSQL, cnConexionSQL);
SqlDataAdapter dtaAdaptadorSQL = new SqlDataAdapter();
SqlDataReader dtrTempSQL;
cnConexionSQL.Open();
cmComandoSQL.CommandType = CommandType.Text;
dtaAdaptadorSQL.SelectCommand = cmComandoSQL;
switch (eTipoDato)
{
case TipoDato.DataSet:
dtaAdaptadorSQL.Fill(dtsTemp, "Temp");
query = dtsTemp;
break;
case TipoDato.Table:
dtaAdaptadorSQL.Fill(dttTemp);
query = dttTemp;
break;
case TipoDato.View:
dtaAdaptadorSQL.Fill(dttTemp);
dttTemp.TableName = "Temp";
dtvTemp.Table = dttTemp;
query = dtvTemp;
break;
case TipoDato.RecordSet:
dtrTempSQL = cmComandoSQL.ExecuteReader();
query = dtrTempSQL;
break;
}
cnConexionSQL.Close();
cnConexionSQL.Dispose();
cmComandoSQL.Dispose();
dtaAdaptadorSQL.Dispose();
dtrTempSQL = null;
break;
case "Oracle":
OracleConnection cnConexion = new OracleConnection(mstrConnectionStringORACLE);
OracleCommand cmComandoORA = new OracleCommand(strSQL, cnConexion);
OracleDataAdapter dtaAdaptadorORA = new OracleDataAdapter();
OracleDataReader dtrTempORA;
cnConexion.Open();
OracleGlobalization SessionGlob = cnConexion.GetSessionInfo();
SessionGlob.NumericCharacters = ",.";
SessionGlob.DateFormat = "dd/mm/yyyy";
cnConexion.SetSessionInfo(SessionGlob);
cmComandoORA.CommandType = CommandType.Text;
dtaAdaptadorORA.SelectCommand = cmComandoORA;
switch (eTipoDato)
{
case TipoDato.DataSet:
dtaAdaptadorORA.Fill(dtsTemp, "Temp");
query = dtsTemp;
break;
case TipoDato.Table:
dtaAdaptadorORA.Fill(dttTemp);
query = dttTemp;
break;
case TipoDato.View:
dtaAdaptadorORA.Fill(dttTemp);
dttTemp.TableName = "Temp";
dtvTemp.Table = dttTemp;
query = dtvTemp;
break;
case TipoDato.RecordSet:
dtrTempORA = cmComandoORA.ExecuteReader();
query = dtrTempORA;
break;
}
cnConexion.Close();
cnConexion.Dispose();
cmComandoORA.Dispose();
dtaAdaptadorORA.Dispose();
dtrTempORA = null;
break;
case "ODBC":
OdbcConnection cnConexionOdbc = new OdbcConnection(mstrConnectionStringODBC);
OdbcCommand cmComandoOdbc = new OdbcCommand(strSQL, cnConexionOdbc);
OdbcDataAdapter dtaAdaptadorOdbc = new OdbcDataAdapter();
OdbcDataReader dtrTempOdbc;
cnConexionOdbc.Open();
cmComandoOdbc.CommandType = CommandType.Text;
dtaAdaptadorOdbc.SelectCommand = cmComandoOdbc;
switch (eTipoDato)
{
case TipoDato.DataSet:
dtaAdaptadorOdbc.Fill(dtsTemp, "Temp");
query = dtsTemp;
break;
case TipoDato.Table:
dtaAdaptadorOdbc.Fill(dttTemp);
query = dttTemp;
break;
case TipoDato.View:
dtaAdaptadorOdbc.Fill(dttTemp);
dttTemp.TableName = "Temp";
dtvTemp.Table = dttTemp;
query = dtvTemp;
break;
case TipoDato.RecordSet:
dtrTempOdbc = cmComandoOdbc.ExecuteReader();
query = dtrTempOdbc;
break;
}
cnConexionOdbc.Close();
cnConexionOdbc.Dispose();
cmComandoOdbc.Dispose();
dtaAdaptadorOdbc.Dispose();
dtrTempORA = null;
break;
}
dtsTemp.Dispose();
dttTemp.Dispose();
dtvTemp.Dispose();
return(query);
}
//
// This method is for addtional information, such as Credential and Email
//
public personInfo SearchHD(string fname, string lname, string personIdFromList)
{
string strConnect = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbprod2.hscnet.hsc.usf.edu)(PORT=1522)))(CONNECT_DATA=(SID=DBAFRZN)(SERVER=DEDICATED)));User Id=hd_search_web;Password=flakSchn0z;";
HealthIS.Apps.MVC.Models.personInfo person = new HealthIS.Apps.MVC.Models.personInfo();
try
{
using (OracleConnection orCN = new OracleConnection(strConnect))
{
orCN.Open();
OracleCommand orCmd = new OracleCommand("hsc.hd_search.sel_matching_entries", orCN);
orCmd.CommandType = System.Data.CommandType.StoredProcedure;
orCmd.Parameters.Add("p_first_name", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.Input;
orCmd.Parameters["p_first_name"].Value = fname;
orCmd.Parameters.Add("p_last_name", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.Input;
orCmd.Parameters["p_last_name"].Value = lname;
orCmd.Parameters.Add("p_unique_id", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.Input;
orCmd.Parameters["p_unique_id"].Value = "";
orCmd.Parameters.Add("p_mdm_id", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.Input;
orCmd.Parameters["p_mdm_id"].Value = "";
orCmd.Parameters.Add("p_persons", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
orCmd.Parameters.Add("p_roles", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
orCmd.Parameters.Add("p_limit", OracleDbType.Int16).Direction = System.Data.ParameterDirection.Input;
orCmd.Parameters["p_limit"].Value = 10;
OracleDataAdapter adapt = new OracleDataAdapter(orCmd);
System.Data.DataSet orDS = new System.Data.DataSet();
orCmd.ExecuteNonQuery();
adapt.Fill(orDS);
foreach (System.Data.DataRow dr in orDS.Tables[0].Rows)
{
string personIdFromSearch = dr["PERSON_ID"].ToString();
if (personIdFromList == personIdFromSearch)
{
person.PERSON_ID = dr["PERSON_ID"].ToString();
person.FIRST_NAME = dr["FNAME"].ToString();
person.LAST_NAME = dr["LNAME"].ToString();
person.EMAIL = dr["EMAIL"].ToString();
person.HSCNET_ID = dr["HSCNET_ID"].ToString();
}
}
orDS.Dispose();
adapt.Dispose();
orCmd.Dispose();
orCN.Close();
orCN.Dispose();
}
}
catch (Exception ex)
{
string error = "Error: " + ex.Message + "<br/>" + ex.StackTrace;
error += ex.InnerException == null ? "" : "<br/>Inner Exception: " + ex.InnerException.Message;
Sitecore.Diagnostics.Log.Error("Faculty Direcotry Error - 'SearchHD' method: ", error);
}
return(person);
}
/// <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 void GetDownloadFileNameOracle(string sPatientID, string sModality, string sStudyDate, string accessionNum, ref List <ArrayList> imageList, ref string strPatientName)
{
OracleDataAdapter sqlQueryImages;
OracleDataReader sqlImagesReader;
ArrayList arrSeriesString = new ArrayList();
OracleDataAdapter sqlCommand;
OracleDataReader SeriesReader;
string sqlstr;
strPatientName = "";
if (accessionNum != "")
{
sqlstr = string.Format(" select name " +
" from examrecord " +
" where id='{0}' and modulename='RIS' ", accessionNum);
sqlCommand = new OracleDataAdapter(sqlstr, GlobalData.MainConn.ChangeTypeOracle());
sqlCommand.SelectCommand.CommandType = CommandType.Text;
SeriesReader = sqlCommand.SelectCommand.ExecuteReader();
while (SeriesReader.Read())
{
strPatientName = (string)SeriesReader["name"];
}
sqlCommand.Dispose();
SeriesReader.Close();
sqlstr = string.Format(" select b.SeriesInstanceUID,b.SeriesNumber,a.AccessionNumber " +
" from studies a,series b " +
" where a.StudyInstanceUID=b.StudyInstanceUID " +
" and a.AccessionNumber='{0}' order by SeriesNumber ", accessionNum);
sqlCommand = new OracleDataAdapter(sqlstr, GlobalData.MainConn.ChangeTypeOracle());
sqlCommand.SelectCommand.CommandType = CommandType.Text;
SeriesReader = sqlCommand.SelectCommand.ExecuteReader();
while (SeriesReader.Read())
{
arrSeriesString.Add((string)SeriesReader["SeriesInstanceUID"]);
}
sqlCommand.Dispose();
SeriesReader.Close();
if (arrSeriesString.Count == 0) //根据ACCESSION获取设备类型，影像号，检查日期
{
ArrayList l_list = new ArrayList();
sqlstr = string.Format("select ReferencedFile from images where AccessionNumber='{0}'", accessionNum);
//sqlstr = string.Format(" select machinetype, to_char(submittime, 'YYYY-MM-DD') as EXAMDATE, olddocid " +
// " from examrecord " +
// " where modulename='RIS' " +
// " and id='{0}'", accessionNum);
sqlCommand = new OracleDataAdapter(sqlstr, GlobalData.MainConn.ChangeTypeOracle());
sqlCommand.SelectCommand.CommandType = CommandType.Text;
SeriesReader = sqlCommand.SelectCommand.ExecuteReader();
while (SeriesReader.Read())
{
l_list.Add((string)SeriesReader["ReferencedFile"]);
}
sqlCommand.Dispose();
SeriesReader.Close();
imageList.Add(l_list);
return;
}
else
{
for (int i = 0; i < arrSeriesString.Count; i++)
{
ArrayList l_list = new ArrayList();
string seriesuid = (string)arrSeriesString[i];
string sel = string.Format("select ReferencedFile,SOPInstanceUID,SeriesInstanceUID from images " +
" where SeriesInstanceUID='{0}'" +
" order by imagenumber ",
seriesuid);
//Loger.Info(sel, "SQL");
sqlQueryImages = new OracleDataAdapter(sel, GlobalData.MainConn.ChangeTypeOracle());
sqlQueryImages.SelectCommand.CommandType = CommandType.Text;
sqlImagesReader = sqlQueryImages.SelectCommand.ExecuteReader();
while (sqlImagesReader.Read())
{
l_list.Add((string)sqlImagesReader["ReferencedFile"]);
}
imageList.Add(l_list);
sqlImagesReader.Close();
sqlQueryImages.Dispose();
}
return;
}
}
//按照设备类型获取图像
sqlstr = string.Format(" select b.SeriesInstanceUID,b.SeriesNumber,a.AccessionNumber " +
" from studies a,series b " +
" where a.StudyInstanceUID=b.StudyInstanceUID " +
" and a.studydate>=to_date('{0} 00:00:00', 'yyyy-mm-dd,hh24:mi:ss') and a.studydate<= to_date('{1} 23:59:59', 'yyyy-mm-dd,hh24:mi:ss') " +
" and a.patientid='{2}' and b.Modality='{3}' " +
" order by SeriesNumber ",
sStudyDate, sStudyDate, sPatientID, sModality);
//Loger.Info(sqlstr, "SQL");
sqlCommand = new OracleDataAdapter(sqlstr, GlobalData.MainConn.ChangeTypeOracle());
sqlCommand.SelectCommand.CommandType = CommandType.Text;
SeriesReader = sqlCommand.SelectCommand.ExecuteReader();
arrSeriesString.Clear();
while (SeriesReader.Read())
{
arrSeriesString.Add((string)SeriesReader["SeriesInstanceUID"]);
}
SeriesReader.Close();
sqlCommand.Dispose();
for (int i = 0; i < arrSeriesString.Count; i++)
{
ArrayList l_list = new ArrayList();
string seriesuid = (string)arrSeriesString[i];
string sel = string.Format("select ReferencedFile,SOPInstanceUID,SeriesInstanceUID from images " +
" where patientid='{0}' " +
" and modality='{1}' " +
" and SeriesInstanceUID='{2}'" +
" order by imagenumber ",
sPatientID, sModality, seriesuid);
sqlQueryImages = new OracleDataAdapter(sel, GlobalData.MainConn.ChangeTypeOracle());
sqlQueryImages.SelectCommand.CommandType = CommandType.Text;
sqlImagesReader = sqlQueryImages.SelectCommand.ExecuteReader();
while (sqlImagesReader.Read())
{
l_list.Add((string)sqlImagesReader["ReferencedFile"]);
}
imageList.Add(l_list);
sqlImagesReader.Close();
sqlQueryImages.Dispose();
}
return;
}
private void button1_Click(object sender, EventArgs e)
{
OracleConnection conn = null;
OracleDataAdapter DataAdapter = null;
DataSet dataset = null;
try
{
string ConnectionString = "User ID=scott;Password=tiger;Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))";
conn = new OracleConnection(ConnectionString);
conn.Open();
DataAdapter = new OracleDataAdapter();
dataset = new DataSet();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
//cmd.CommandText = "insert into student(no,name,sex,grade) values (9,'张三','男',99)";
//cmd.ExecuteNonQuery();
cmd.CommandText = "select * from student";
DataAdapter.SelectCommand = cmd;
DataAdapter.Fill(dataset, "t1");
cmd.CommandText = "select * from teacher";
DataAdapter.SelectCommand = cmd;
DataAdapter.Fill(dataset, "t2");
string s = " ";
for (int i = 0; i < dataset.Tables["t1"].Columns.Count; i++)
{
s += dataset.Tables["t1"].Columns[i].ToString() + "\t";
}
listBox1.Items.Add(s);
for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)
{
DataRow dr = dataset.Tables[0].Rows[i];
s = "";
for (int j = 0; j < dataset.Tables[0].Columns.Count; j++)
{
s += dr[j].ToString() + "\t";
}
listBox1.Items.Add(s);
}
textBox1.DataBindings.Add("Text", dataset, "t2.t_name");
dataGridView1.DataSource = dataset;
dataGridView1.DataMember = "t2";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (conn != null)
{
conn.Dispose();
}
if (dataset != null)
{
dataset.Dispose();
}
if (DataAdapter != null)
{
DataAdapter.Dispose();
}
}
}
/// <summary>
/// 执行查询操作，查询结果保存至 DataTable 中。
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="sqlParams">参数数组</param>
/// <param name="timeOut">等待命令执行的时间（以秒为单位）,默认值为 30 秒。</param>
/// <param name="errorMsg">返回的异常信息</param>
public DataTable QueryDt(string sql, OracleParameter[] sqlParams, int timeOut, out string errorMsg)
{
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleDataAdapter adapter = new OracleDataAdapter();
DataTable dt = new DataTable();
errorMsg = string.Empty;
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
foreach (OracleParameter sp in sqlParams)
{
cmd.Parameters.Add(sp);
}
}
//等待命令执行的时间（以秒为单位）,默认值为 30 秒。
if (timeOut <= 30)
{
cmd.CommandTimeout = 30;
}
else
{
cmd.CommandTimeout = timeOut;
}
adapter.SelectCommand = cmd;
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
finally
{
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
adapter.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + "|*|ErrorMsg:" + errorMsg);
}
return(dt);
}
public string AllRecords(string action, string xml, string id)
{
string result;
if (id == "")
{
OracleDataAdapter obj_ORCL = new OracleDataAdapter("crol_dml_vochmst_dtl_st", this.GetConnection().ConnectionString);
obj_ORCL.SelectCommand.CommandType = CommandType.StoredProcedure;
obj_ORCL.SelectCommand.BindByName = true;
obj_ORCL.SelectCommand.Parameters.Add("p_action", action);
obj_ORCL.SelectCommand.Parameters.Add("p_xml", xml);
obj_ORCL.SelectCommand.Parameters.Add("p_vochno", id);
obj_ORCL.SelectCommand.Parameters.Add("p_dataset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable bu_dt = new DataTable();
try
{
obj_ORCL.Fill(bu_dt);
result = Newtonsoft.Json.JsonConvert.SerializeObject(bu_dt);
}
catch (Exception ex)
{
result = Newtonsoft.Json.JsonConvert.SerializeObject(ex.Message);
}
finally
{
bu_dt.Dispose();
obj_ORCL.Dispose();
}
}
else
{
OracleDataAdapter obj_ORCL = new OracleDataAdapter("crol_dml_vochmst_dtl_st", this.GetConnection().ConnectionString);
obj_ORCL.SelectCommand.CommandType = CommandType.StoredProcedure;
obj_ORCL.SelectCommand.BindByName = true;
obj_ORCL.SelectCommand.Parameters.Add("p_action", action);
obj_ORCL.SelectCommand.Parameters.Add("p_xml", xml);
obj_ORCL.SelectCommand.Parameters.Add("p_vochno", id);
obj_ORCL.SelectCommand.Parameters.Add("p_dataset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
DataTable bu_dt = new DataTable();
try
{
obj_ORCL.Fill(bu_dt);
var res = bu_dt.Rows[0][":B1"];
result = res.ToString();
}
catch (Exception ex)
{
result = Newtonsoft.Json.JsonConvert.SerializeObject(ex.Message);
}
finally
{
bu_dt.Dispose();
obj_ORCL.Dispose();
}
}
return(result);
}
/// <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);
}
/// <summary>
/// Returns an equipment dataset containing the customer's equipment info
/// </summary>
/// <param name="siteId"></param>
/// <param name="accountNumber9"></param>
/// <returns></returns>
public EquipmentSchema GetEquipment(int siteId, string siteCode, string accountNumber9)
{
siteId = 541;
siteCode = "SAN";
accountNumber9 = "65297304";
OracleCommand cmd = null;
OracleCommand cmd2 = null;
OracleDataAdapter da = null;
OracleDataAdapter da2 = null;
try
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try{ oracleConn.Open(); }
catch { throw new LogonException(); }
//get customer equipment
StringBuilder equipmentSql = new StringBuilder();
equipmentSql.Append("select distinct serial_number, equipment_type_code ");
equipmentSql.AppendFormat("from {0}_EQUIPMENT ", siteCode);
//equipmentSql.Append( "where equipment_type_code != 'O' ");
equipmentSql.AppendFormat("where account_number = {0} and site_id = {1}", accountNumber9, siteId);
// build the command object
cmd = new OracleCommand(equipmentSql.ToString(), oracleConn);
cmd.CommandType = CommandType.Text;
// build the dataadapter
da = new OracleDataAdapter(cmd);
// create the dataset to fill
EquipmentSchema ds = new EquipmentSchema();
// fill equipment info
da.Fill(ds.CustomerEquipment);
//convert date to icoms date for query
Icoms1900Date now = new Icoms1900Date(DateTime.Now.Subtract(new TimeSpan(2500, 0, 0, 0)));
//Icoms1900Date now = new Icoms1900Date(1030925);
string time = now.Date.Hour.ToString() + now.Date.Minute.ToString();
//get ppv events for account
StringBuilder ppvSql = new StringBuilder();
ppvSql.Append("select distinct p.serial_number, t.event_title, p.event_start_date, ");
ppvSql.Append("p.event_start_time, d.end_date, d.end_time ");
ppvSql.AppendFormat("from {0}_title t, {0}_ppv_purchase p, {0}_pricing_group_dtl d ", siteCode);
ppvSql.Append("where p.title_code = t.title_code and p.event_start_date = d.start_date ");
ppvSql.Append("and p.event_start_time = d.start_time and p.showing_number = d.showing_number ");
ppvSql.AppendFormat("and p.ACCOUNT_NUMBER = {0} and p.SITE_ID = {1} ", accountNumber9, siteId);
ppvSql.Append("and p.purchase_status = 'P' ");
ppvSql.AppendFormat("and ((d.end_date > {0}) or (d.end_date = {0} and d.end_time >= {1}))", now.ToString(), time);
//Oracle does not support multiple recordsets like SQL Server does
//so we have to use another cmd and da...grr
// build the command object
cmd2 = new OracleCommand(ppvSql.ToString(), oracleConn);
cmd2.CommandType = CommandType.Text;
// build the dataadapter
da2 = new OracleDataAdapter(cmd2);
// now fill ppv dt
da2.Fill(ds.ActivePpvEvents);
// all done, return
return(ds);
}
}
catch (LogonException)
{
// just rethrow it. it is from our internal code block
throw;
}
catch (Exception ex)
{
// DataSourceException.
throw new DataSourceException(ex);
}
finally
{
//clean up
if (da != null)
{
da.Dispose();
}
if (da2 != null)
{
da2.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (cmd2 != null)
{
cmd2.Dispose();
}
}
}
private void getData(AutoCompleteStringCollection dataCollection)
{
OracleCommand command;
OracleDataAdapter adapter = new OracleDataAdapter();
DataSet ds = new DataSet();
string sql = "SELECT MGF_NAME FROM MFG_INFO ORDER BY MGF_NAME";
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command = new OracleCommand(sql, conn);
adapter.SelectCommand = command;
adapter.Fill(ds);
adapter.Dispose();
command.Dispose();
conn.Close();
foreach (DataRow row in ds.Tables[0].Rows)
{
dataCollection.Add(row[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("Can not open connection ! ");
}
}
public void ExecuteSPDataSet(ref DataSet dataSet, string procedureName, string tableName)
{
OracleCommand cmd = new OracleCommand();
this.Connect();
OracleDataAdapter da = new OracleDataAdapter();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
//if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
da.SelectCommand = cmd;
da.Fill(dataSet, tableName);
_parameterCollection = cmd.Parameters;
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection) this.Disconnect();
}
public static DataTable GetDatatable_DPIS(string sql, OracleParameter[] param)
{
DataTable dt = new DataTable();
try
{
OracleConnection conn = new OracleConnection(DPISConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 240;
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (param != null)
{
foreach (OracleParameter p in param)
{
if (p != null)
{
cmd.Parameters.Add(p);
}
}
}
da.Fill(dt);
da.Dispose();
}
catch (Exception ex)
{
dt = new DataTable();
}
return dt;
}
/// <summary>
/// Liste les joueurs
/// </summary>
/// <param name="afficherTout">Affiche les joueurs non confirmés</param>
/// <returns>Le dataset</returns>
public static DataSet listPlayers(bool afficherTout)
{
OracleConnection conn = Connection.getInstance().conn;
DataSet monDataSet = new DataSet();
string sql = "SELECT JID, USERNAME, EMAIL, HASH_KEY, JOINDATE, MONEY, CONFIRMED FROM JOUEURS WHERE CONFIRMED = 1 ";
if (afficherTout)
sql += "OR CONFIRMED = 0 ";
sql += "ORDER BY JID";
try
{
OracleDataAdapter oraSelect = new OracleDataAdapter(sql, conn);
if (monDataSet.Tables.Contains("JOUEURS"))
monDataSet.Tables["JOUEURS"].Clear();
oraSelect.Fill(monDataSet, "JOUEURS");
oraSelect.Dispose();
return monDataSet;
}
catch (OracleException ex)
{
Erreur.ErrorMessage(ex);
return null;
}
}