public void Execute(string name, Action<OracleConnection> a, bool useTransaction = false)
{
ConnectionString cs = Database.GetConnectionString(name);
OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder
{
DataSource = cs.DataSource,
UserID = cs.UserID,
Password = cs.Password,
PersistSecurityInfo = cs.PersistSecurityInfo
};
using (OracleConnection conn = new OracleConnection(builder.ConnectionString))
{
conn.Open();
if (useTransaction)
{
using (OracleTransaction trans = conn.BeginTransaction())
{
a(conn);
trans.Commit();
}
}
else
a(conn);
conn.Close();
}
}
public static OracleConnection GetConnection()
{
if (conn != null) {
if (conn.State == System.Data.ConnectionState.Open) {
return conn;
}
}
string DBName = "RA00C000";
string DBUser = "******";
string DBPass = "******";
string[] args = Environment.GetCommandLineArgs();
if (args.Length > 3) {
DBName = args[1];
DBUser = args[2];
DBPass = args[3];
}
oradb = "Data Source=" + DBName + ";User Id=" + DBUser + ";Password="******";";
try {
conn = new OracleConnection(oradb);
conn.Open();
transaction = conn.BeginTransaction();
return conn;
} catch (Exception) {
return null;
}
}
/// <summary>
/// Connects to the database.
/// </summary>
/// <param name="connDetails"></param>
public SqlRunner(ConnectionDetails connDetails)
{
this.connDetails = connDetails;
string connStr = GetConnectionString();
log.Info("connecting to [" + connDetails.Name + "] with connection string " + connStr);
conn = new OracleConnection(connStr);
conn.Open();
currentTransaction = conn.BeginTransaction();
}
public OWDB(string DataBaseName, string LoginUser, string LoginPassword)
{
OracleConnection1 = new Oracle.DataAccess.Client.OracleConnection();
// Sample Connectionstring 'User Id=owuser;Password=owuser;Data source=WHTRUNK'
OracleConnection1.ConnectionString =
"User Id=" +
LoginUser +
";Password="******";Data source=" +
DataBaseName +
"";
OracleConnection1.Open();
OracleTransaction1 = OracleConnection1.BeginTransaction();
}
public bool Fill()
{
try {
conn = new OracleConnection(GetConnectionString());
conn.Open();
transaction = conn.BeginTransaction();
} catch (Exception e) {
MessageBox.Show("При подключении к базе данных произошла ошибка:\n\n" + e.Message);
conn.Close();
conn.Dispose();
return false;
}
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TO_CHAR(id), l_name from akriko.cat_executors WHERE id in (SELECT distinct(ispolnitel_cik_id) FROM akriko.appeal) ORDER BY UPPER(l_name)";
try {
dr = cmd.ExecuteReader();
while (dr.Read()) {
if (dr.IsDBNull(0) || dr.IsDBNull(1)) {
MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n");
break;
} else {
UsersGrid.Rows.Add();
UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["executor_id"].Value = dr.GetString(0);
UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["akriko"].Value = dr.GetString(1);
}
}
dr.Close();
dr.Dispose();
} catch (Exception e) {
MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n" + e.Message);
}
if (UsersGrid.Rows.Count == 0) {
MessageBox.Show("Отсутствуют неотконвертированные исполнители", "Конвертация исполнителей", MessageBoxButtons.OK, MessageBoxIcon.Information);
ConvertButton.Enabled = false;
}
FillDeloDepartmentsColumn();
return true;
}
public IDatabaseQuery StartQuery(bool withTransaction)
{
var connection = new OracleConnection(ConnectionString);
OracleTransaction transaction = null;
if (withTransaction)
{
try
{
connection.Open();
}
catch (Exception ex)
{
TraceSource.TraceEvent(TraceEventType.Error, 5010, "{0}", ex);
try { connection.Close(); }
catch { }
OracleConnection.ClearAllPools();
connection = new OracleConnection(ConnectionString);
connection.Open();
}
transaction = connection.BeginTransaction();
}
IDatabaseQuery query;
try
{
query = QueryFactory(connection, transaction);
}
catch (Exception ex)
{
TraceSource.TraceEvent(TraceEventType.Critical, 5101, "{0}", ex);
TraceSource.TraceEvent(TraceEventType.Information, 5101, "Transactions: {0}, connections: {1}", OpenTransactions.Count, OpenConnections.Count);
throw;
}
if (withTransaction)
OpenTransactions.TryAdd(query, transaction);
OpenConnections.TryAdd(query, connection);
return query;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static bool ExecuteSqlTran(string conStr, List<CommandInfo> cmdList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo c in cmdList)
{
if (!String.IsNullOrEmpty(c.CommandText))
{
PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (c.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式");
//return false;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0");
//return false;
}
if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0");
//eturn false;
}
continue;
}
int res = cmd.ExecuteNonQuery();
if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行");
// return false;
}
}
}
tx.Commit();
return true;
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw E;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// 개별 발송 로그 조회
/// </summary>
public void SingleStart()
{
Biz.CM.CMXmlAutoBizTx oBiz = null;
Biz.CM.CMXmlAutoBiz oBiz2 = null;
DataSet ds = null;
int taskIdx = 0;
try
{
#region 개별 SMS 발송 Function
Func<object, int> action = (object oSms) =>
{
int oraResult = 0;
string insertSQL ="";
// string insertValues = "";
CMXmlAutoBizTx bizTx = null;
StringBuilder sbQuery = new StringBuilder();
string oraConn = ConfigurationManager.AppSettings["OraConnStr"];
int totRows = 0;
int rowIdx = 0;
string minId = "";
string maxId = "";
string dataRange = "";
// SMS TRAN TABLE
string sc_tran_table = ConfigurationManager.AppSettings["SMS_TRAN_TABLE"];
// SMS SEQUENCE
string sc_sequence = ConfigurationManager.AppSettings["SMS_SEQUENCE"];
DataTable dt = (DataTable)oSms;
Hashtable ht = new Hashtable();
OracleConnection oCon = new OracleConnection(oraConn);
OracleTransaction oTx = null;
OracleCommand cmd = oCon.CreateCommand();
try
{
// 전체 대상 건수
totRows = dt.Rows.Count;
// 시작ID
maxId = dt.Rows[0]["SNGSMSLOGID"].ToString();
// 종료ID
minId = dt.Rows[totRows - 1]["SNGSMSLOGID"].ToString();
// 작업 대상 ID
dataRange = "SngSMSLogId> " + minId + " ~ " + maxId;
try
{
// Connection Open.
oCon.Open();
// 데이터 저장 Array 생성
/* TR_SENDDATE DATE
* TR_PHONE VARCHAR2(20)
* TR_CALLBACK VARCHAR2(20)
* TR_MSG VARCHAR2(160)
* TR_ETC3 VARCHAR2(160)
*/
DateTime[] sendDateVals = new DateTime[totRows];
string[] sendStatVals = new string[totRows];
string[] msgTypeVals = new string[totRows];
string[] phoneVals = new string[totRows];
string[] callBackVals = new string[totRows];
string[] msgVals = new string[totRows];
string[] etc1Vals = new string[totRows];
string[] etc2Vals = new string[totRows];
string[] etc3Vals = new string[totRows];
// 데이터 저장
foreach (DataRow dr in dt.Rows)
{
sendDateVals[rowIdx] = Convert.ToDateTime(dr["ReqDate"].ToString());
sendStatVals[rowIdx] = "0";
msgTypeVals[rowIdx] = "0";
phoneVals[rowIdx] = dr["Mobile"].ToString();
callBackVals[rowIdx] = dr["ReplyTelNo"].ToString();
msgVals[rowIdx] = dr["SendMessage"].ToString();
etc1Vals[rowIdx] = "TMS";
etc2Vals[rowIdx] = "Single";
etc3Vals[rowIdx] = dr["SngSMSLogId"].ToString();
rowIdx++;
}
// Parameter 생성
OracleParameter pSendDate = new OracleParameter();
pSendDate.OracleDbType = OracleDbType.Date;
pSendDate.Value = sendDateVals;
OracleParameter pSendStat = new OracleParameter();
pSendStat.OracleDbType = OracleDbType.Varchar2;
pSendStat.Value = sendStatVals;
OracleParameter pMsgType = new OracleParameter();
pMsgType.OracleDbType = OracleDbType.Varchar2;
pMsgType.Value = msgTypeVals;
OracleParameter pPhone = new OracleParameter();
pPhone.OracleDbType = OracleDbType.Varchar2;
pPhone.Value = phoneVals;
OracleParameter pCallBack = new OracleParameter();
pCallBack.OracleDbType = OracleDbType.Varchar2;
pCallBack.Value = callBackVals;
OracleParameter pMsg = new OracleParameter();
pMsg.OracleDbType = OracleDbType.Varchar2;
pMsg.Value = msgVals;
OracleParameter pEtc1 = new OracleParameter();
pEtc1.OracleDbType = OracleDbType.Varchar2;
pEtc1.Value = etc1Vals;
OracleParameter pEtc2 = new OracleParameter();
pEtc2.OracleDbType = OracleDbType.Varchar2;
pEtc2.Value = etc2Vals;
OracleParameter pEtc3 = new OracleParameter();
pEtc3.OracleDbType = OracleDbType.Varchar2;
pEtc3.Value = etc3Vals;
oTx = oCon.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = oTx;
cmd.CommandType = CommandType.Text;
insertSQL = @"INSERT INTO " + sc_tran_table +" (TR_NUM"
+ ", TR_SENDDATE, TR_SENDSTAT, TR_MSGTYPE"
+ ", TR_PHONE, TR_CALLBACK, TR_MSG"
+ ", TR_ETC1, TR_ETC2, TR_ETC3)";
insertSQL = insertSQL + @" VALUES(" + sc_sequence +".NEXTVAL"
+ ", :1, :2, :3"
+ ", :4, :5, :6 "
+ ", :7, :8, :9)";
cmd.CommandText = insertSQL;
cmd.ArrayBindCount = totRows;
cmd.Parameters.Add(pSendDate);
cmd.Parameters.Add(pSendStat);
cmd.Parameters.Add(pMsgType);
cmd.Parameters.Add(pPhone);
cmd.Parameters.Add(pCallBack);
cmd.Parameters.Add(pMsg);
cmd.Parameters.Add(pEtc1);
cmd.Parameters.Add(pEtc2);
cmd.Parameters.Add(pEtc3);
// Insert 실행
oraResult = cmd.ExecuteNonQuery();
if (oraResult > 0)
{
oTx.Commit();
log.Info(dataRange + " - 개별발송>SMS DB Insert> 성공");
ht.Clear();
ht.Add("SENDRESULT", "20");
ht.Add("MINID", minId);
ht.Add("MAXID", maxId);
bizTx = new CMXmlAutoBizTx();
if (bizTx.XmlNonExecute("uspSingleSMSLogSendAfter", ht) > 0)
{
log.Info(dataRange + " - 개별발송>발송결과(20) 갱신 성공");
}
else
{
log.Error(dataRange + " - 개별발송>발송결과(20) 갱신 실패");
}
}
else
{
ht.Clear();
ht.Add("SENDRESULT", "40");
ht.Add("MINID", minId);
ht.Add("MAXID", maxId);
bizTx = new CMXmlAutoBizTx();
if (bizTx.XmlNonExecute("uspSingleSMSLogSendAfter", ht) > 0)
{
log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 성공");
}
else
{
log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 실패");
}
}
}
catch (Exception ex)
{
log.Error(dataRange + " - 개별발송>SMS DB Insert 실패>Task Sub" + ex.ToString());
oTx.Rollback();
ht.Clear();
ht.Add("SENDRESULT", "40");
ht.Add("MINID", minId);
ht.Add("MAXID", maxId);
bizTx = new CMXmlAutoBizTx();
if (bizTx.XmlNonExecute("uspSingleSMSLogSendAfter", ht) > 0)
{
log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 실패");
}
else
{
log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 실패");
}
}
finally
{
oCon.Close();
cmd.Dispose();
}
}
catch (Exception ex)
{
log.Error(dataRange + " - 개별발송>SMS DB Insert 실패>Task Main" + ex.ToString());
ht.Clear();
ht.Add("SENDRESULT", "40");
ht.Add("MINID", minId);
ht.Add("MAXID", maxId);
bizTx = new CMXmlAutoBizTx();
if (bizTx.XmlNonExecute("uspSingleSMSLogSendAfter", ht) > 0)
{
log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 성공");
}
else
{
log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 실패");
}
}
return oraResult;
};
#endregion 개별 SMS 발송 Function
#region 개별 SMS 로그 조회 Task
bool sendCheck = false;
int iResult=-1;
Task<int>[] tasks = new Task<int>[1];
while(sendCheck == false)
{
oBiz = new CMXmlAutoBizTx();
iResult = oBiz.XmlNonExecute("uspSingleSMSLogStand");
if (iResult < 1)
{
sendCheck = true;
break;
}
else
{
oBiz2 = new CMXmlAutoBiz();
ds = oBiz2.GetData("uspSingleSMSLogSend");
log.Info(taskIdx.ToString() + " - 개별 발송대상 데이터 건수> " + ds.Tables[0].Rows.Count.ToString());
if(ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
tasks[taskIdx] = Task<int>.Factory.StartNew(action, ds.Tables[0]);
taskIdx++;
Array.Resize(ref tasks, taskIdx+1);
continue;
}
else
{
sendCheck = true;
break;
}
}
}
if (tasks.Length > 0)
{
// 작업 종료할 때까지 대기
if (tasks.GetValue(0) != null)
{
if (taskIdx > 0)
{
Array.Resize(ref tasks, taskIdx );
}
Task.WaitAll(tasks);
}
else
{
log.Info(taskIdx.ToString() + " - 개별 발송할 데이터 없음");
}
}
log.Info(taskIdx.ToString() + " - 개별 발송 작업 완료");
#endregion 개별 SMS 로그 조회 Task
}
catch (Exception ex)
{
log.Error(taskIdx.ToString() + " - 개별발송>발송 작업 실패>" + ex.ToString());
}
finally
{
if (oBiz != null)
oBiz = null;
if (oBiz2 != null)
oBiz2 = null;
}
}
public string execute_transaction(string[] strSql)
{
objConnection = open_connection();
try
{
if (open_con)
{
objTransaction = objConnection.BeginTransaction();
Int32 i = 0;
string strQuery = "";
for (i = 0; i <= strSql.Length - 1; i++)
{
strQuery = "";
strQuery = strSql[i];
if (strQuery.Length != 0)
{
objCommand = new OracleCommand(strQuery, objConnection);
objCommand.CommandType = CommandType.Text;
try
{
objCommand.ExecuteNonQuery();
err_flag = false;
}
catch (Exception objError)
{
ErrorStr = objError.Message;
err_flag = true;
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
}
}
if (!err_flag)
{
objTransaction.Commit();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
else
{
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
}
else
{
ErrorStr = "Error : Unable to connect to database.";
err_flag = true;
}
}
catch (Exception objError)
{
ErrorStr = objError.Message;
err_flag = true;
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return ErrorStr;
}
public JsonResult UpdateRecords(string id, string records)
{
OutgoingNotice notice = repository.FindByNotCache(id);
if (notice == null)
{
return Json(new { success = false, message = "未找到指定的发料通知单" });
}
if (string.IsNullOrEmpty(records))
{
return Json(new { success = false, message = "发料记录不能为空" });
}
IPipeOutgoingRecordRepository porRepository = RepositoryFactory.GetRepository<IPipeOutgoingRecordRepository, PipeOutgoingRecord>(unitOfWork);
IPipeRepository pipeRepository = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>(unitOfWork);
JavaScriptSerializer serializer = new JavaScriptSerializer();
Account onlineAccount = UserSession.OnlineAccount;
IList<PDCPMS.DataContracts.Outgoing.PipeOutgoingRecord> recordContracts = serializer.Deserialize<IList<PDCPMS.DataContracts.Outgoing.PipeOutgoingRecord>>(records);
IList<PipeOutgoingRecord> existsRecords = notice.Records;
IList<long> pipeIds = new List<long>();
float totalWorkLength = 0f;
float totalBusinessLength = 0f;
float totalWeight = 0f;
int count = 0;
OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString);
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
try
{
foreach (PDCPMS.DataContracts.Outgoing.PipeOutgoingRecord rc in recordContracts)
{
PDCPMS.Model.Common.Pipe pipe = null;
PipeOutgoingRecord record = null;
if (string.IsNullOrEmpty(rc.Key))
{
pipe = pipeRepository.FindBy(rc.PipeId);
if (pipe == null ||
!PipePolicy.CanSentout(pipe) ||
pipeIds.Contains(rc.PipeId))
{
continue;
}
if ((pipe.State & PipeState.SentingOut) != PipeState.SentingOut)
{
pipe.State |= PipeState.SentingOut;
}
//pipeRepository[pipe.Key] = pipe;
pipeRepository.UpdatedPipe(pipe, conn, tran);
record = new PipeOutgoingRecord(Guid.NewGuid().ToString("N"), notice, pipe, DateTime.Now);
//porRepository.Add(record);
porRepository.AddPipeOutgoingRecord(record, conn, tran);
pipeIds.Add(rc.PipeId);
}
else
{
record = existsRecords.FirstOrDefault(r => r.Key.ToString() == rc.Key);
if (record == null)
{
continue;
}
pipe = record.Pipe;
pipeIds.Add((long)pipe.Key);
existsRecords.Remove(record);
}
count += 1;
totalBusinessLength += pipe.BusinessLength.HasValue ? pipe.BusinessLength.Value : 0;
totalWorkLength += pipe.WorkLength.HasValue ? pipe.WorkLength.Value : 0;
totalWeight += pipe.Weight.HasValue ? pipe.Weight.Value : 0;
}
// 已删除
foreach (PipeOutgoingRecord record in existsRecords)
{
PDCPMS.Model.Common.Pipe pipe = record.Pipe;
if ((pipe.State & PipeState.SentingOut) == PipeState.SentingOut)
{
pipe.State ^= PipeState.SentingOut;
}
//pipeRepository[pipe.Key] = pipe;
pipeRepository.UpdatedPipe(pipe, conn, tran);
//porRepository.Remove(record);
porRepository.RemovePipeOutgoingRecord(record, conn, tran);
}
notice.SentBusinessLength = totalBusinessLength;
notice.SentCount = count;
notice.SentWeight = totalWeight / 1000;
notice.SentWorkLength = totalWorkLength;
notice.Sender = onlineAccount;
notice.DateFinished=DateTime.Now;
//repository[notice.Key] = notice;
repository.UpdatedItemOutgoingNotice(notice, conn, tran);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
return Json(new { success = false, message = "发料记录更新未成功"+ex });
}
//unitOfWork.Commit();
//(unitOfWork as UnitOfWork).addedEntities[
return Json(new { success = true, message = "发料记录更新成功" });
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
//public static int ExecuteSql(string SQLString)
//{
// using (OracleConnection connection = new OracleConnection(connectionString))
// {
// using (OracleCommand cmd = new OracleCommand(SQLString,connection))
// {
// try
// {
// connection.Open();
// int rows=cmd.ExecuteNonQuery();
// return rows;
// }
// catch(System.Data.OracleClient.OracleException E)
// {
// connection.Close();
// throw new Exception(E.Message);
// }
// }
// }
//}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (OracleException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
/// <summary>
/// Na Delete wordt het aantal verwijderde rijen teruggegeven.
/// </summary>
/// <param name="businessObject"></param>
/// <returns></returns>
public int Delete(BeheerContextEntity businessObject)
{
int rowsaffected = 0;
using (OracleConnection conn = new OracleConnection(_oradb))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
string sqlUpdate =
@"
DELETE FROM {0}
WHERE {1} = '{2}'
";
sqlUpdate = string.Format(sqlUpdate, businessObject.Tablename,
businessObject.DataKeyName, businessObject.DataKeyValue);
var cmd = new OracleCommand
{
Connection = conn,
CommandText = sqlUpdate,
CommandType = CommandType.Text
};
cmd.Connection = conn;
rowsaffected = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
return rowsaffected;
}
public string DataPublishTrans(int count, DataTable dt, string Company_code, string FielName, string User_ID, string type)
{
string retval = "";
string[] retCount = new string[count];
try
{
string[] arr_company_Code = new string[count];
string[] arr_FielName = new string[count];
string[] P_Trans_type = new string[count];
string[] P_Rowid = new string[count];
string[] p_bank_code = new string[count];
string[] p_branch_code = new string[count];
string[] P_Userid = new string[count];
string[] P_type = new string[count];
int[] arrSize = new int[count];
for (int i = 0; i < count; i++)
{
arrSize[i] = 1000;
arr_company_Code[i] = Company_code;
arr_FielName[i] = FielName;
P_Trans_type[i] = dt.Rows[i]["TRANS_TYPE"].ToString();
P_Rowid[i] = dt.Rows[i]["ROWID"].ToString();
p_bank_code[i] = "";
p_branch_code[i] = "";
P_Userid[i] = User_ID;
P_type[i] = type;
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
conn.Open();
Oracle.DataAccess.Client.OracleTransaction trans = conn.BeginTransaction();
Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "sp_Data_Publish";
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.ArrayBindCount = count;
cmd.Parameters.Add("P_company_code", OracleDbType.Varchar2, arr_company_Code, ParameterDirection.Input);
cmd.Parameters.Add("P_FILE_NAME", OracleDbType.Varchar2, arr_FielName, ParameterDirection.Input);
cmd.Parameters.Add("P_Trans_type", OracleDbType.Varchar2, P_Trans_type, ParameterDirection.Input);
cmd.Parameters.Add("P_Rowid", OracleDbType.Varchar2, P_Rowid, ParameterDirection.Input);
cmd.Parameters.Add("p_bank_code", OracleDbType.Varchar2, p_bank_code, ParameterDirection.Input);
cmd.Parameters.Add("p_branch_code", OracleDbType.Varchar2, p_branch_code, ParameterDirection.Input);
cmd.Parameters.Add("P_Userid", OracleDbType.Varchar2, P_Userid, ParameterDirection.Input);
cmd.Parameters.Add("P_type", OracleDbType.Varchar2, P_type, ParameterDirection.Input);
cmd.Parameters.Add("v_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize;
#region
int result = cmd.ExecuteNonQuery();
for (int i = 0; i < count; i++)
{
retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["v_retval"].Value))[i].ToString();
if (retCount[i].ToString().StartsWith("04") == false)
{
trans.Rollback();
conn.Close();
return(retCount[i]);
}
}
string val = postLeadger(arr_company_Code[0], arr_FielName[0], P_Trans_type[0], P_Rowid[0], p_bank_code[0],
p_branch_code[0], P_Userid[0], "14");
if (val.StartsWith("04"))
{
trans.Commit();
conn.Close();
//CoreBankingPoolCall(arr_company_Code[0], arr_FielName[0], P_Userid[0]);
retval = retCount[0];
}
else
{
trans.Rollback();
conn.Close();
return(val);
}
#endregion
}
catch (Exception ex)
{
retval = ex.Message;
}
return(retval);
}
public IDatabaseQuery StartQuery(bool withTransaction)
{
var connection = new OracleConnection(ConnectionString);
OracleTransaction transaction = null;
if (withTransaction)
{
try
{
connection.Open();
}
catch (Exception ex)
{
LogFactory.Create("Oracle database layer - start query").Error(ex.ToString());
try { connection.Close(); }
catch { }
OracleConnection.ClearAllPools();
connection = new OracleConnection(ConnectionString);
connection.Open();
}
transaction = connection.BeginTransaction();
}
IDatabaseQuery query;
try
{
query = QueryFactory(connection, transaction, LogFactory);
}
catch (Exception ex)
{
var log = LogFactory.Create("Oracle database layer - start query");
log.Error(ex.ToString());
log.Info("Transactions: {0}, connections: {1}".With(OpenTransactions.Count, OpenConnections.Count));
throw;
}
if (withTransaction)
OpenTransactions.TryAdd(query, transaction);
OpenConnections.TryAdd(query, connection);
return query;
}
public OracleTransaction BeginTransaction(OracleConnection conn)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn.BeginTransaction();
}
public JsonResult Approve(string code, string ReportNO)
{
NormalCheckTask task = repository.FindByCode(code);
Account onlineAccount = UserSession.OnlineAccount;
if (task == null)
{
return Json(new { success = false, message = "未找到指定常检任务" });
}
if (task.Approver != null)
{
return Json(new { success = false, message = string.Format("该批常检任务已完成批准,报告号为{0}，请不要重复批准！", ReportNO) });
}
Regex codePattern = new Regex(@"^[-|\.|a-z|A-Z|0-9]+$");
if (!codePattern.IsMatch(ReportNO))
{
return Json(new { success = false, message = "报告号格式不正确" });
}
if (null != repository.FindByReportNO(ReportNO,onlineAccount))
{
return Json(new { success = false, message = string.Format("报告号{0}已经存在，请尝试使用其它报告号", ReportNO) });
}
#region 回滚事务
OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString);
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
try
{
#endregion
task.ReportNO = ReportNO;
task.Approver = UserSession.OnlineAccount;
task.DateApproved = DateTime.Now;
//repository[task.Key] = task;
repository.UpdatedTask(task, conn, tran);
// Create Records
INormalCheckRecordRepository recordRepository = RepositoryFactory.GetRepository<INormalCheckRecordRepository, NormalCheckRecord>(unitOfWork);
TransferTask transferTask = task.TransferTask;
int orederNO = 0;
DateTime now = DateTime.Now;
transferTask.Items.ForEach(transferItem =>
{
transferItem.Records.ForEach(transferRecord =>
{
orederNO++;
NormalCheckRecord r = new NormalCheckRecord(Guid.NewGuid().ToString("N"),
task,
transferRecord.Pipe,
now,
orederNO);
//recordRepository.Add(r);
recordRepository.AddRecord(r, conn, tran);
});
});
//unitOfWork.Commit();
#region 回滚事务
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
return Json(new { success = true, message = "没有成功批准检测任务，请重新批准！" + ex });
}
#endregion
return Json(new { success = true, message = "成功批准检测任务！" });
}
public JsonResult UpdateCheckResults(string code)
{
NormalCheckTask task = repository.FindByCode(code);
if (task == null)
{
return Json(new { success = false, message = "未找到指定的常检任务" });
}
if ((int)task.State < (int)NormalCheckTaskState.Received)
{
return Json(new { success = false, message = "当前常检任务沿不允许更新检测数据" });
}
if (task.State == NormalCheckTaskState.Verified)
{
return Json(new { success = false, message = "当前常检任务已审核存档，不允许被编辑" });
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
IList<PDCPMS.DataContracts.NormalCheck.NormalCheckRecord> recordContracts = null;
try
{
recordContracts = serializer.Deserialize<IList<PDCPMS.DataContracts.NormalCheck.NormalCheckRecord>>(Request.Form["Records"]);
}
catch
{
return Json(new { success = false, message = "请求数据有误" });
}
INormalCheckRecordRepository recordRepository = RepositoryFactory.GetRepository<INormalCheckRecordRepository, NormalCheckRecord>(unitOfWork);
IPipeRepository pipeRepository = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>(unitOfWork);
IList<String> codes = new List<string>();
#region 回滚事务
OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString);
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
try
{
#endregion
foreach (PDCPMS.DataContracts.NormalCheck.NormalCheckRecord rc in recordContracts)
{
NormalCheckRecord record = recordRepository.FindBy(rc.Key);
if (record == null)
continue;
record.IsBodyValid = rc.IsBodyValid;
record.IsCheckBlindCrackValid = rc.IsCheckBlindCrackValid;
record.IsCheckBodyCrackValid = rc.IsCheckBodyCrackValid;
record.IsClosenessValid = rc.IsClosenessValid;
record.IsEndValid = rc.IsEndValid;
record.IsPressureValid = rc.IsPressureValid;
record.IsScrewValid = rc.IsScrewValid;
record.IsStraightValid = rc.IsStraightValid;
record.IsThicknessValid = rc.IsThicknessValid;
record.IsUnBlockValid = rc.IsUnBlockValid;
record.IsValid = rc.IsValid;
record.MaleP = rc.MaleP;
record.FemaleA = rc.FemaleA;
record.PressureDetectValue = rc.PressureDetectValue;
record.PressureHoldValue = rc.PressureHoldValue;
//2010-11-12 by huncherby
record.OutDiameter = rc.OutDiameter;
record.ThicknessValid = rc.ThicknessValid;
record.CheckUnBlock = rc.CheckUnBlock;
record.MinOutDiameter = rc.MinOutDiameter;
record.Comment = rc.Comment;
//recordRepository[record.Key] = record;
recordRepository.UpdateRecord(record,conn,tran);
if (!string.IsNullOrEmpty(rc.Code))
{
rc.Code = rc.Code.ToUpper().Trim();
if (codes.Contains(rc.Code))
{
return Json(new { success = false, message = "存在重复的工序号" });
}
codes.Add(rc.Code);
record.Pipe.Code = task.WorkNO + rc.Code;
}
else
{
record.Pipe.Code = string.Empty;
}
record.Pipe.WorkLength = rc.WorkLength;
//pipeRepository[record.Pipe.Key] = record.Pipe;
pipeRepository.UpdatedPipe(record.Pipe,conn,tran);
}
task.ReportComment = Request.Form["ReportComment"];
task.CheckConclusion = Request.Form["CheckConclusion"];
task.Finisher = UserSession.OnlineAccount;
task.DateFinished = DateTime.Now;
//repository[task.Key] = task;
repository.UpdatedTask(task,conn,tran);
//unitOfWork.Commit();
#region 回滚事务
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
return Json(new { success = true, message = "检测数据更新失败，请保存检测数据！" + ex });
}
#endregion
//改变保存数据的状态 2010-11-22 by hunherby
pipeRepository.VerifyPipeBy(task);
retory.updateisvaild(task);
//更新结论
repository.updateCheckConclusion(task);
return Json(new { success = true, message = "检测数据更新成功" });
}
/**
* @brief Adds a user to the database
* @param User user The user to add
**/
public static int UpdateUserSettings(User user)
{
int result = -1;
bool error = false;
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
OracleTransaction transaction = null;
try
{
//Open the connection
connection.Open();
//Start a local transaction
transaction = connection.BeginTransaction();
//First we add the user to the database
//Setup the command to run
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.Transaction = transaction;
if (user.User_Id > 0)
{
command.Parameters.Clear();
command.BindByName = true;
if (user.Password != null && user.Password.Length > 0)
{
command.CommandText = UPDATE_USER_PASSWORD;
command.Parameters.Add(":password", user.Password);
}
else
{
command.CommandText = UPDATE_USER_NO_PASSWORD;
}
//First we set the parameters
command.Parameters.Add(":user_id", user.User_Id);
command.Parameters.Add(":name", user.Name);
command.Parameters.Add(":mobile", user.Mobile);
command.Parameters.Add(":address", user.Address);
command.Parameters.Add(":paypal_id", user.Paypal_Id);
command.Parameters.Add(":betfair_id", user.Betfair_Id);
Tools.printDebug(command.CommandText.ToString());
Tools.printDebug(command.Parameters.ToString());
//Run the command
result = command.ExecuteNonQuery();
Tools.printDebug("UPDATE USER RESULT: " + result);
}
}
catch (Exception exception)
{
error = true;
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
if (transaction != null)
{
if (error)
{
transaction.Rollback();
}
else
{
transaction.Commit();
}
}
//Closes the datareader and connection to the database
if (connection != null)
{
connection.Close();
}
}
return result;
}
/**
* @brief Adds a user to the database
* @param User user The user to add
**/
public static int RegisterNewUser(User user)
{
int lastInsertedId = -1;
bool error = false;
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
OracleTransaction transaction = null;
try
{
//Open the connection
connection.Open();
//Start a local transaction
transaction = connection.BeginTransaction();
//First we add the user to the database
//Setup the command to run
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.Transaction = transaction;
//Get the next id
command.CommandText = DatabaseSchema.SELECT_USERS_NEXT_ID;
int user_id = Convert.ToInt32(command.ExecuteScalar());
if (user_id > 0)
{
command.Parameters.Clear();
command.CommandText = INSERT_USER;
//First we set the parameters
command.Parameters.Add("user_id", user_id);
command.Parameters.Add("password", user.Password);
command.Parameters.Add("role_id", (int)user.Role_Id);
command.Parameters.Add("name", user.Name);
command.Parameters.Add("email", user.Email);
command.Parameters.Add("registered", DatabaseSchema.USER_NOT_REGISTERED);
command.Parameters.Add("mobile", user.Mobile);
command.Parameters.Add("address", user.Address);
command.Parameters.Add("paypal_id", user.Paypal_Id);
command.Parameters.Add("betfair_id", user.Betfair_Id);
//Run the command
command.ExecuteNonQuery();
lastInsertedId = user_id;
}
}
catch (Exception exception)
{
error = true;
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
if (transaction != null)
{
if (error)
{
transaction.Rollback();
}
else
{
transaction.Commit();
}
}
//Closes the datareader and connection to the database
if (connection != null)
{
connection.Close();
}
}
return lastInsertedId;
}
public static int ConfirmUserRegistration(String user_id, String email)
{
int result = -1;
bool error = false;
OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
OracleTransaction transaction = null;
try
{
//Open the connection
connection.Open();
//Start a local transaction
transaction = connection.BeginTransaction();
//First we add the user to the database
//Setup the command to run
string strCommand = REGISTER_USER;
strCommand = strCommand.Replace("@user_id", user_id);
strCommand = strCommand.Replace("@email", email);
strCommand = strCommand.Replace("@registered", "1");
Tools.printDebug(strCommand);
OracleCommand command = new OracleCommand(strCommand);
command.Connection = connection;
command.Transaction = transaction;
//Run the command
result = command.ExecuteNonQuery();
}
catch (Exception exception)
{
error = true;
Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
}
finally
{
if (transaction != null)
{
if (error)
{
transaction.Rollback();
}
else
{
transaction.Commit();
}
}
//Closes the datareader and connection to the database
if (connection != null)
{
connection.Close();
}
}
return result;
}
/// <summary>
/// Na Update wordt het aantal bijgewerkte rijen teruggegeven.
/// </summary>
/// <param name="oldBusinessObject"></param>
/// <param name="newBusinessObject"></param>
/// <returns></returns>
public int Update(BeheerContextEntity oldBusinessObject, BeheerContextEntity newBusinessObject)
{
int rowsAffected = 0;
using (OracleConnection conn = new OracleConnection(_oradb))
{
string sqlUpdate = "update {0} set {1} ='{2}' where {1} = '{3}'";
string tableName = oldBusinessObject.Tablename;
string columnName = oldBusinessObject.DataKeyName;
sqlUpdate = string.Format(sqlUpdate,
tableName,
columnName,
newBusinessObject.DataKeyValue,
oldBusinessObject.DataKeyValue);
conn.Open();
var trans = conn.BeginTransaction();
try
{
var cmd = new OracleCommand
{
Connection = conn,
CommandText = sqlUpdate,
CommandType = CommandType.Text
};
cmd.Connection = conn;
rowsAffected = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
return rowsAffected;
}
public void InsertExample()
{
using (var connection = new OracleConnection(ConnectionString))
{
connection.Open();
var id = new Random().Next();
var insertCommand = new OracleCommand();
insertCommand.Connection = connection;
insertCommand.CommandType = CommandType.Text;
insertCommand.CommandText = "Insert into \"MCC_User\" (\"Id\",\"Name\",\"SabreID\",\"Pcc\") Values (:Id,'Roman', '123123','3213')";
insertCommand.Parameters.Add(new OracleParameter(":Id", id));
insertCommand.ExecuteNonQuery();
var command = new OracleCommand("Select * from \"MCC_User\" where \"Name\" = 'Roman'");
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine(reader["Id"] + ": " + reader["Name"]);
}
}
}
command.CommandText = "select count(*) from \"MCC_User\"";
Console.WriteLine(command.ExecuteScalar());
var transaction = connection.BeginTransaction();
command.CommandText = "Delete from \"MCC_User\" where \"Name\" = 'Roman'";
command.ExecuteNonQuery();
transaction.Rollback();
}
Console.ReadKey();
}
public int Insert(ParentKeyEntity masterForeignKey, BeheerContextEntity newBusinessObject)
{
int rowsAffected = 0;
using (OracleConnection conn = new OracleConnection(_oradb))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
string sqlUpdate =
@"INSERT INTO {0}
({1}, {3})
VALUES
('{2}','{4}')
";
sqlUpdate = string.Format(sqlUpdate,
newBusinessObject.Tablename,
newBusinessObject.DataKeyName,
newBusinessObject.DataKeyValue,
masterForeignKey.DataKeyName,
masterForeignKey.DataKeyValue);
var cmd = new OracleCommand
{
Connection = conn,
CommandText = sqlUpdate,
CommandType = CommandType.Text
};
cmd.Connection = conn;
rowsAffected = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
return rowsAffected;
}
private void SafeExecuteCommand(Action<OracleCommand> action)
{
using (var connection = new OracleConnection(_connectionString))
{
OracleTransaction transaction = null;
OracleCommand com;
try
{
connection.Open();
OracleSqlUtility.SetSqlUserInfo(connection, _userInfo);
transaction = connection.BeginTransaction();
com = connection.CreateCommand();
com.CommandTimeout = SqlUtility.SqlCommandTimeout;
com.Transaction = transaction;
}
catch (OracleException ex)
{
if (transaction != null)
transaction.Rollback();
var csb = new OracleConnectionStringBuilder(_connectionString);
string msg = string.Format(CultureInfo.InvariantCulture, "Could not connect to data source '{0}', userID '{1}'.", csb.DataSource, csb.UserID);
_logger.Error(msg);
_logger.Error(ex.ToString());
throw new FrameworkException(msg, ex);
}
try
{
var setNationalLanguage = OracleSqlUtility.SetNationalLanguageQuery();
if (!string.IsNullOrEmpty(setNationalLanguage))
{
_logger.Trace("Setting national language: {0}", SqlUtility.NationalLanguage);
com.CommandText = setNationalLanguage;
com.ExecuteNonQuery();
}
action(com);
transaction.Commit();
}
catch (OracleException ex)
{
if (com != null && !string.IsNullOrWhiteSpace(com.CommandText))
_logger.Error("Unable to execute SQL query:\r\n" + com.CommandText);
string msg = "OracleException has occurred:\r\n" + ReportSqlErrors(ex);
if (ex.Number == 911)
msg += "\r\nCheck that you are not using ';' at the end of the command's SQL query.";
_logger.Error(msg);
_logger.Error(ex.ToString());
throw new FrameworkException(msg, ex);
}
finally
{
TryRollback(transaction);
}
}
}
public JsonResult Finish(string id)
{
TransferTask task = repository.FindByNotCache(id);
if (task == null)
{
return Json(new { success = false, message = "无法找到指定的调度任务" });
}
if (task.IsFinished)
{
return Json(new { success = false, message = "指定的调度任务已经完成，不能重复确认" });
}
DateTime now = DateTime.Now;
#region 回滚事务
OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString);
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
try
{
#endregion
task.IsFinished = true;
task.DateFinished = now;
ITransferItemRepository transferItemRepository = RepositoryFactory.GetRepository<ITransferItemRepository, TransferItem>(unitOfWork);
ITransferRecordRepository transferRecordRepository = RepositoryFactory.GetRepository<ITransferRecordRepository, TransferRecord>(unitOfWork);
IPipeRepository pipeRepository = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>(unitOfWork);
// Li Zheng 2010.11.28 替换掉
for (int i = task.Items.Count - 1; i > -1; i--)
{
TransferItem item = task.Items[i];
for (int j = item.Records.Count - 1; j > -1; j--)
{
TransferRecord r = item.Records[j];
r.DateFinished = now;
PDCPMS.Model.Common.Pipe pipe = r.Pipe;
if ((pipe.State & PipeState.Transfering) == PipeState.Transfering)
pipe.State ^= PipeState.Transfering;
if (task.IsSystemTask)
{
pipe.State |= PipeState.NormalChecking;
}
pipe.PosLastChangeTime = now;
pipe.PrevPosition = pipe.Position;
pipe.Position = r.NewPos;
//pipeRepository[pipe.Key] = pipe;
pipeRepository.UpdatedPipe(pipe, conn, tran);
}
}
// Li Zheng 2010.11.28
//pipeRepository.FinishTranUpdatePipe(id);
if (task.IsSystemTask)
{
INormalCheckTaskRepository taskRepository = RepositoryFactory.GetRepository<INormalCheckTaskRepository, NormalCheckTask>(unitOfWork);
NormalCheckTask normalCheckTask = taskRepository.FindBy(task);
if (normalCheckTask != null)
{
normalCheckTask.Sender = UserSession.OnlineAccount;
normalCheckTask.DateSent = now;
normalCheckTask.State = NormalCheckTaskState.Transferd;
//taskRepository[normalCheckTask.Key] = normalCheckTask;
taskRepository.UpdatedTask(normalCheckTask, conn, tran);
}
}
//repository[task.Key] = task;
repository.UpdateTransfertask(task, conn, tran);
//unitOfWork.Commit();
#region 回滚事务
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
return Json(new { success = true, message = "没有成功确认完成调度任务，请重新确认！"+ex });
}
#endregion
//TempData["HintMessage"] = new PDCPMS.Application.HintMessage { Content = "成功确认完成调度任务" };
return Json(new { success = true, message = "成功确认完成调度任务" });
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(string conStr,List<String> SQLStringList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (string sql in SQLStringList)
{
if (!String.IsNullOrEmpty(sql))
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
//public string CoreBankingPoolCall(string arr_company_code, string arr_file_name, string arr_userid)
//{
// try
// {
// string[] p_company_code = new string[1];
// p_company_code[0] = arr_company_code;
// string[] p_file_name = new string[1];
// p_file_name[0] = arr_file_name;
// string[] p_userid = new string[1];
// p_userid[0] = arr_userid;
// int[] arrSize = new int[1];
// arrSize[0] = 1000;
// Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
// conn.Open();
// Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand();
// cmd.CommandText = "corebank_system_sym_pub";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.BindByName = true;
// cmd.ArrayBindCount = 1;
// cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, p_company_code, ParameterDirection.Input);
// cmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, p_file_name, ParameterDirection.Input);
// cmd.Parameters.Add("p_userid", OracleDbType.Varchar2, p_userid, ParameterDirection.Input);
// cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize;
// int result = cmd.ExecuteNonQuery();
// string p_return = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[0].ToString();
// }
// catch (Exception ex)
// {
// return ex.Message;
// }
// return "";
//}
public string force_updation(GridView gv, string User_ID, string type, int count, string company_code, string file_name)
{
string retval = "";
string[] retCount;
try
{
int updateCount = 0;
string[] arr = new string[count];
int m = 0;
for (int i = 0; i < count; i++)
{
//if (((HiddenField)gv.Rows[i].FindControl("hf_BranchCode")).Value != "" && ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text != "")
if (((TextBox)gv.Rows[i].FindControl("txtBranchCode")).Text != "Branch not found" &&
((TextBox)gv.Rows[i].FindControl("txtBranchCode")).Text != "" &&
((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text != "" &&
//((TextBox)gv.Rows[i].FindControl("txtSymbolTitle")).Text != "Account Title not found." &&
((TextBox)gv.Rows[i].FindControl("txtSymbolTitle")).Text != "Symbol Title" &&
((TextBox)gv.Rows[i].FindControl("txtSymbolTitle")).Text != "")
{
arr[i] = m.ToString();
updateCount++;
}
else
{
arr[i] = "N";
}
m++;
}
string[] p_Rowid = new string[updateCount];
string[] p_branch_code = new string[updateCount];
string[] p_Account_no = new string[updateCount];
string[] p_benename = new string[updateCount];
string[] P_Userid = new string[updateCount];
string[] P_type = new string[updateCount];
string[] P_company_code = new string[updateCount];
string[] p_file_name = new string[updateCount];
int[] arrSize = new int[updateCount];
retCount = new string[updateCount];
int u = 0;
for (int i = 0; i < count; i++)
{
if (arr[i].ToString() != "N")
{
arrSize[u] = 1000;
p_Rowid[u] = gv.Rows[i].Cells[0].Text;
//p_branch_code[u] = (((HiddenField)gv.Rows[Convert.ToInt32(arr[i])].FindControl("hf_BranchCode")).Value == "" ? gv.Rows[i].Cells[3].Text : ((HiddenField)gv.Rows[i].FindControl("hf_BranchCode")).Value);
p_branch_code[u] = (((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtBranchCode")).Text == "" ? gv.Rows[i].Cells[3].Text : ((TextBox)gv.Rows[i].FindControl("txtBranchCode")).Text);
p_Account_no[u] = (((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtaccountno_symbol")).Text == "" ? gv.Rows[i].Cells[1].Text : ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text);
p_benename[u] = ((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtSymbolTitle")).Text;
//(((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtSymbolTitle")).Text == "" ? gv.Rows[i].Cells[1].Text : ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text);
P_Userid[u] = User_ID;
P_type[u] = type;
P_company_code[u] = company_code;
p_file_name[u] = file_name;
u++;
}
}
if (p_Rowid.Length > 0)
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
conn.Open();
Oracle.DataAccess.Client.OracleTransaction trans = conn.BeginTransaction();
Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "sp_force_Updation";
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.ArrayBindCount = updateCount;
cmd.Parameters.Add("p_rowid", OracleDbType.Varchar2, p_Rowid, ParameterDirection.Input);
cmd.Parameters.Add("p_branch_code", OracleDbType.Varchar2, p_branch_code, ParameterDirection.Input);
cmd.Parameters.Add("p_Account_no", OracleDbType.Varchar2, p_Account_no, ParameterDirection.Input);
cmd.Parameters.Add("p_benename", OracleDbType.Varchar2, p_benename, ParameterDirection.Input);
cmd.Parameters.Add("P_Userid", OracleDbType.Varchar2, P_Userid, ParameterDirection.Input);
cmd.Parameters.Add("P_type", OracleDbType.Varchar2, P_type, ParameterDirection.Input);
cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, P_company_code, ParameterDirection.Input);
cmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, p_file_name, ParameterDirection.Input);
cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize;
int result = cmd.ExecuteNonQuery();
for (int i = 0; i < updateCount; i++)
{
retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[i].ToString();
if (retCount[i].ToString().StartsWith("0") == true) //Error
{
trans.Rollback();
conn.Close();
return(retCount[i]);
}
}
trans.Commit();
conn.Close();
retval = retCount[0];
}
}
catch (Exception ex)
{
retval = "0;" + ex.Message;
}
return(retval);
}
/// <summary>
/// 通过事务执行SQL语句命令
/// </summary>
/// <param name="connection"></param>
/// <param name="SQLStringList"></param>
public static void ExecuteSQLTransaction(OracleConnection connection, List<string> SQLStringList)
{
connection.Open();
OracleCommand command = new OracleCommand();
command.Connection = connection;
OracleTransaction transaction = connection.BeginTransaction();
if (transaction != null)
{
//command.Transaction = transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
}
try
{
for (int i = 0; i < SQLStringList.Count; i++)
{
string str = SQLStringList[i].ToString();
if (str.Trim().Length > 1)
{
command.CommandText = str;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (OracleException exception)
{
transaction.Rollback();
throw new Exception(exception.Message);
}
finally
{
connection.Close();
}
}
/// <summary>
/// OracleへUPDATE文を実行します。
/// 戻り値 boolean型 正常=true 異常=false
/// </summary>
/// <param name="sql">実行するSQL文です</param>
public Boolean OracleUpdate(string sql)
{
Boolean rtncode = false; //戻り値用（初期値false）
string connStr = GetConnectionString();
using (OracleCommand cmd = new OracleCommand())
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connStr;
//トランザクション開始
conn.Open();
OracleTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
//トランザクションをコミットします。
transaction.Commit();
rtncode = true;
}
catch (Exception)
{
//トランザクションをロールバック
transaction.Rollback();
rtncode = false;
GetUser();
ErrorLogWrite(user_cd, "OracleUpdate", sql.Replace("'", "###"));
}
finally
{
conn.Close();
}
}
return rtncode;
}
/// <summary>
///打开(如果必要的话),指定一个命令、连接、事务、参数类型和参数
/// </summary>
/// <param name="command">OracleCommand</param>
/// <param name="connection">OracleConnection</param>
/// <param name="transaction">OracleTransaction 或 'null'</param>
/// <param name="commandType"> CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或SQL语句</param>
/// <param name="commandParameters">OracleParameter[]</param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
//command.Transaction = transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
OracleCommand cmd = new OracleCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
OracleParameter[] cmdParms = (OracleParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch
{
trans.Rollback();
throw;
}
}
}
}
/// <summary>
/// 演示 事务处理的部分。
/// </summary>
/// <param name="conn"></param>
private void Transaction(OracleConnection conn)
{
Console.WriteLine("开始事务处理操作！");
// 开始创建一个事务.
OracleTransaction t = conn.BeginTransaction();
try
{
Console.WriteLine("模拟插入2条相同数据，造成主键冲突的异常！");
InsertData(conn, t);
InsertData(conn, t);
// 提交事务.
t.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
// 回滚事务.
t.Rollback();
}
if (ExistData(conn))
{
Console.WriteLine("数据库中依然存在着主键为{0} 与 {1} 的的数据！", TEST_SALE_DATE, TEST_SALE_ITEM);
}
else
{
Console.WriteLine("数据库中已检索不到主键为{0} 与 {1} 的的数据！", TEST_SALE_DATE, TEST_SALE_ITEM);
}
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
OracleClient.OracleConnection orlConnection = null;
OracleClient.OracleTransaction orlTransaction = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Start a new transaction.
orlTransaction = orlConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, commandText), orlConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = orlTransaction;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
orlTransaction.Commit();
// Close the database connection.
orlConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (orlTransaction != null)
{
orlTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
public OracleTransaction BeginTransaction(OracleConnection conn, IsolationLevel isolationLevel)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn.BeginTransaction(isolationLevel);
}