static void ADO_NET_Example()
{
var cs = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=www.machinejar.com)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=devorc02)));User Id=hr;Password=hr;";
OracleConnection con = new OracleConnection();
con.ConnectionString = cs;
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "HR_DEPTPERCOUNTRY";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter orcParam = new OracleParameter("CNTR_CD", OracleDbType.Varchar2);
orcParam.Size = 50;
orcParam.Value = "UK";
orcParam.Direction = System.Data.ParameterDirection.Input;
cmd.Parameters.Add(orcParam);
OracleParameter orcOutParam = new OracleParameter("RS_CURSOR", OracleDbType.RefCursor);
orcOutParam.Size = 50;
orcOutParam.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(orcOutParam);
cmd.ExecuteNonQuery();
OracleDataReader reader = ((OracleRefCursor)cmd.Parameters["RS_CURSOR"].Value).GetDataReader();
while (reader.Read())
{
Console.WriteLine("Dept Name: {0} | Address: {1}, | Country: {2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
}
}
public void TestConnectionWithString()
{
int ret = 0;
string connstring = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdborcl)));User Id=system;Password=admin1234;";
string sql = @"select *from user_tables;";
sql = getSqlString();
// string sql = @"begin
//create sequence TABLE22_SEQ;
//create table table22(id NUMBER DEFAULT 1);
//end;";
using (DbConnection conn = new OracleConnection(connstring))
{
conn.Open();
DbCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
ret = command.ExecuteNonQuery();
conn.Close();
}
Assert.AreNotEqual(ret,0);
}
public ChangeResultSettings IsExistsEmail(string email)
{
var result = new ChangeResultSettings();
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_check_exists_email";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new OracleParameter("email_check", OracleDbType.Varchar2)).Value = email;
var op = new OracleParameter("total_result", OracleDbType.Long, 15) { Direction = ParameterDirection.Output };
cm.Parameters.Add(op);
cm.ExecuteNonQuery();
var total = Convert.ToInt32(cm.Parameters["total_result"].Value.ToString());
switch (total)
{
case 0:
result.ChangeResult = ChangeResult.ThanhCong;
break;
default:
result.ChangeResult = ChangeResult.ThatBai;
break;
}
}
catch (Exception ex)
{
result.Message = "Có lỗi trong quá trình kiểm tra email. Vui lòng thử lại!";
result.ChangeResult = ChangeResult.ThatBai;
}
return result;
}
public DataTable ChiTietDuAn(string mdv, string nsd, string pas, string maDonVi, long idDuAn)
{
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_ChiTiet_DuAn";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = "";
cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "";
cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = "";
cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = idDuAn;
cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi;
cm.Parameters.Add(new OracleParameter("cs_lke", OracleDbType.RefCursor)).Direction =
ParameterDirection.Output;
var tableGs = new DataTable();
_oracleAdapter = new OracleDataAdapter(cm);
_oracleAdapter.Fill(tableGs);
return tableGs;
}
catch (Exception)
{
return null;
}
finally
{
ConnectDB.CloseConnection(_connectGs);
}
}
public ChangeResultSettings CapNhatLoaiNguonVon(string mdv, string nsd, string pas, string maDonVi, long idDuAn, int loaiNguonVon)
{
var result = new ChangeResultSettings();
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_CapNhat_DuAn_LoaiNguonVon";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = "";
cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "";
cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = "";
cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = idDuAn;
cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi;
cm.Parameters.Add(new OracleParameter("loai_nguonvon", OracleDbType.Int32)).Value = loaiNguonVon;
var op = new OracleParameter("row_updated", OracleDbType.Long, 15) { Direction = ParameterDirection.Output };
cm.Parameters.Add(op);
cm.ExecuteNonQuery();
var rowUpdated = cm.Parameters["row_updated"].Value.ToString();
if (rowUpdated == "0")
result.ChangeResult = ChangeResult.ThatBai;
}
catch (Exception ex)
{
result.ChangeResult = ChangeResult.ThatBai;
result.Message = ex.Message;
throw;
}
return result;
}
/// <summary>
/// Construtor, por default já conecta no banco de dados, caso não queira passe false no parâmetro.
/// Construct open database connetion by default, if you don't want set startConnection = false
/// </summary>
/// <param name="startConnection">
/// Conectar no banco de dados, default true
/// Open database connection
/// </param>
public ODPNetConnect(bool startConnection = false)
{
ClearMessages();
if (!startConnection)
{
return;
}
myConnection = GetConnection();
if (!String.IsNullOrWhiteSpace(this.ERROR)) //Erro ao conectar. Connection error
{
return;
}
try
{
if (!myConnection.State.ToString().Equals("Open"))
{
myConnection.Open();
}
myCommand = myConnection.CreateCommand();
}
catch (Exception e)
{
ERROR = e.Message;
if (e.InnerException != null)
{
ERROR += e.InnerException.Message;
}
}
}
/// <summary>
/// 测试 调用 Oracle 返回结果集的函数.
/// </summary>
private void CallFuncWithTable(OracleConnection conn)
{
// 创建一个 Command.
OracleCommand testCommand = conn.CreateCommand();
// 定义需要执行的SQL语句.
testCommand.CommandText = "pkg_HelloWorld.getHelloWorld";
// 定义好，本次执行的类型，是存储过程.
testCommand.CommandType = CommandType.StoredProcedure;
// 定义好，我这个参数，是 游标 + 返回值.
OracleParameter para = new OracleParameter("c", OracleDbType.RefCursor);
para.Direction = ParameterDirection.ReturnValue;
testCommand.Parameters.Add(para);
// 执行SQL命令，结果存储到Reader中.
OracleDataReader testReader = testCommand.ExecuteReader();
// 处理检索出来的每一条数据.
while (testReader.Read())
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("调用函数:{0}; 返回:{1} - {2}",
testCommand.CommandText, testReader[0], testReader[1]
);
}
// 关闭Reader.
testReader.Close();
}
/// <summary>
/// выполнение запроса к базе
/// </summary>
/// <param name="cmdString">строка запроса</param>
/// <returns>возвращает значение bool выполнено или нет</returns>
public static bool ExecuteQuery(string cmdString)
{
using (var connection = new OracleConnection(GetConnectionString()))
{
var cmd = connection.CreateCommand();
cmd.CommandText = cmdString;
try
{
connection.Open();
var transaction = connection.BeginTransaction();
Debug.WriteLine("Соединение открыто");
cmd.ExecuteNonQuery();
transaction.Commit();
Debug.WriteLine("Запрос выполнен");
}
catch (OracleException exception)
{
Debug.WriteLine(exception.Message);
return false;
}
finally
{
connection.Close();
Debug.WriteLine("Соединение закрыто");
}
}
return true;
}
public DatabaseKoppeling()
{
string user = "******";
string pw = "FRAGIE";
conn = new OracleConnection();
command = conn.CreateCommand();
conn.ConnectionString = "User Id=" + user + ";Password="******";Data Source=" + " //localhost:1521/xe" + ";";
}
public void Start()
{
string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.92)(PORT=1521))(CONNECT_DATA=(SID=XE)));User Id=sa;Password=sa123456;";
OracleConnection conn = new OracleConnection(connectionString);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from test";
IDataReader dr = cmd.ExecuteReader();
}
public DBReader(String ConnStr_, String PipeName_)
{
using (Logger l = new Logger("PipeCatcher"))
{
ConnStr = ConnStr_;
PipeName = PipeName_;
OraConn = new OracleConnection(ConnStr);
OraCmd = OraConn.CreateCommand();
l.msg("A new DBReader created ConnStr={0} PipeName={1}", ConnStr, PipeName);
}
}
public static List<MessageInfo> GetMessages(string businessId, DBConnection connection)
{
var connString = connection.getConnectionString();
var result = new List<MessageInfo>();
var output = "npu, correlationNpu, businessid, creationdate, processid, interfacetoinvoke, operation, payload";
var filter = string.Format(" businessId = '{0}' ", businessId) ;
var orderBy = " ORDER BY creationdate ASC";
if (!string.IsNullOrEmpty(Configuration.IgnoreOperations))
{
var opers = Configuration.IgnoreOperations.Split(';');
var formOper = " {0} AND NPU not like '%{1}%' ";
foreach (var o in opers)
{
filter = string.Format(formOper, filter, o);
}
}
using(OracleConnection conn = new OracleConnection(connString)){
var command = conn.CreateCommand();
command.CommandText = connection.CreateQuery(output, "pt_msg_inbound", filter, orderBy);
conn.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
var msg = MessageInfo.LoadFromDB(reader);
result.Add(msg);
}
command.CommandText = connection.CreateQuery(output, "pt_msg_inbound_archive", filter, orderBy);
reader = command.ExecuteReader();
while (reader.Read())
{
var msg = MessageInfo.LoadFromDB(reader);
result.Add(msg);
}
}
result = result.OrderBy(a => a.ReceivedDatetime).ToList();
return result;
}
public static void CreateQueueIfNecessary(Address address, string connectionString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = InstallSql;
cmd.Parameters.Add("queue", NormalizeQueueName(address));
cmd.Parameters.Add("queueTable", (NormalizeQueueName(address) + "_tab").ToUpper());
connection.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Initializes the message receiver.
/// </summary>
/// <param name="address">The address of the message source.</param>
/// <param name="transactional">Indicates if the receiver should be transactional.</param>
public void Init(Address address, bool transactional)
{
this.inputQueueAddress = OracleAqsUtilities.NormalizeQueueName(address);
using (OracleConnection conn = new OracleConnection(this.ConnectionString))
{
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT queue_table FROM dba_queues WHERE name = :queue";
cmd.Parameters.Add("queue", this.inputQueueAddress);
conn.Open();
this.queueTable = cmd.ExecuteScalar() as string;
}
}
public static DataSet getResultTable(OracleConnection conn, string sqlText)
{
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sqlText;
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
ds.Tables.Add(new DataTable("DbSource"));
adapter.Fill(ds, "DbSource");
return ds;
}
public void Purge(string queue)
{
using (OracleConnection conn = new OracleConnection(this.ConnectionString))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = PurgeSql;
cmd.Parameters.Add("queue", queue);
cmd.Parameters.Add("queueCondition", string.Format("qtview.queue = '{0}'", queue));
cmd.ExecuteNonQuery();
}
}
}
public OracleDebuggerSession(OracleConnectionAdapter connectionAdapter, OracleCommand debuggedCommand, StatementExecutionBatchResult executionResult)
{
_connectionAdapter = connectionAdapter;
ExecutionResult = executionResult;
DebuggedCommand = debuggedCommand;
var debuggedConnection = DebuggedCommand.Connection;
_debuggedSessionCommand = debuggedConnection.CreateCommand();
_debuggedSessionCommand.BindByName = true;
_debuggerConnection = (OracleConnection)debuggedConnection.Clone();
_debuggerSessionCommand = _debuggerConnection.CreateCommand();
_debuggerSessionCommand.BindByName = true;
_sources.Add(PlSqlBlockTitle, debuggedCommand.CommandText.Trim());
}
/// <summary>
/// Execute a query on the server with no return value, like an INSERT or DELETE query
/// </summary>
/// <param name="query">The input query</param>
public static void ExecuteQuery(string query)
{
try
{
using (OracleConnection conn = new OracleConnection(connectionstring))
{
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static string GetDbVersion(string connectionName)
{
try
{
using (var conn = new OracleConnection(ConfigurationManager.ConnectionStrings[connectionName].ConnectionString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT HOST_NAME || '-' || VERSION AS DBNAME FROM V$INSTANCE";
return (string)cmd.ExecuteScalar();
}
}
catch
{
return string.Empty;
}
}
public Result SQLRequest(string SQLString)
{
var res = new Result();
try {
using (var oraConn = new OracleConnection(m_connectionString)) {
var oraCmd = oraConn.CreateCommand();
oraCmd.CommandText = SQLString;
oraCmd.CommandType = System.Data.CommandType.Text;
if (oraCmd.Connection.State != System.Data.ConnectionState.Closed) oraCmd.Connection.Close();
try {
oraCmd.Connection.Open();
var oraReader = oraCmd.ExecuteReader();
if (oraReader.HasRows) {
while (oraReader.Read()) {
for (int i = 0; i < oraReader.FieldCount; i++) {
var k = oraReader.GetName(i);
var v = oraReader[i];
if (!res.ResultData.ContainsKey(k)) {
res.ResultData.Add(k,new List<object>());
}
res.ResultData[k].Add(v);
}
}
}
}
finally {
oraCmd.Connection.Close();
oraCmd.Dispose();
}
}
}
catch (Exception e) {
res.ErrorCode = Result.Es.S_ERROR;
res.ErrorStr += String.Format("{0}\n", e.Message);
return res;
}
return res;
}
private void CreateQueue(Address address)
{
string queue = this.NamePolicy.GetQueueName(address);
string queueTable = this.NamePolicy.GetQueueTableName(address);
using (OracleConnection conn = new OracleConnection(this.ConnectionString))
{
conn.Open();
using (OracleCommand createQueue = conn.CreateCommand())
{
createQueue.BindByName = true;
createQueue.CommandText = CreateQueueSql;
createQueue.Parameters.Add("queue", queue);
createQueue.Parameters.Add("queueTable", queueTable);
createQueue.ExecuteNonQuery();
}
Logger.DebugFormat("Created queue, name: [{0}], queue table: [{1}]", queue, queueTable);
}
}
public OraclePipeListener(string connectionString, string dataBase, string tableName)
{
m_DateBase = dataBase;
m_TableName = tableName;
m_Connection = new OracleConnection(connectionString);
m_Connection.Open();
m_Command = m_Connection.CreateCommand();
m_Command.CommandText = string.Format("SELECT * FROM {0}.{1} WHERE rownum = 1", DateBase,TableName);
m_Dependency = new OracleDependency(m_Command);
m_Dependency.OnChange += new OnChangeEventHandler(DependencyChange);
m_Command.Notification.IsNotifiedOnce = false;
m_Command.AddRowid = true;
m_Command.ExecuteNonQuery();
m_Connection.Close();
m_Connection.Dispose();
}
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;
}
/// <summary>
/// 测试 调用 Oracle 函数.
/// </summary>
private void CallFunc(OracleConnection conn)
{
// 创建一个 Command.
OracleCommand testCommand = conn.CreateCommand();
// 定义需要执行的SQL语句.
testCommand.CommandText = "SELECT HelloWorldFunc() FROM dual";
// 执行SQL命令，结果存储到Reader中.
OracleDataReader testReader = testCommand.ExecuteReader();
// 处理检索出来的每一条数据.
while (testReader.Read())
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("调用函数:{0}; 返回:{1}",
testCommand.CommandText, testReader[0]
);
}
// 关闭Reader.
testReader.Close();
}
//public string BulkCancelationProcess(DataTable dt, string tableName)
public string BulkCancelationProcess(int count, string[] arr_Company_code, string[] arr_xpin, string[] arr_Filename, int[] arrSize, string sp_name)
{
//OracleBulkCopy obj = new OracleBulkCopy(ConnectionString);
string retval = "";
try
{
#region
//obj.BulkCopyTimeout = 10000;
//obj.DestinationTableName = tableName;
//obj.Connection.Open();
//obj.WriteToServer(dt);
//retval = "0;File successfully Uploaded.";
#endregion
Oracle.DataAccess.Client.OracleConnection ocnn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
ocnn.Open();
Oracle.DataAccess.Client.OracleCommand ocmd = ocnn.CreateCommand();
ocmd.CommandText = sp_name;// "sp_bulk_cancel_detail";
ocmd.CommandType = CommandType.StoredProcedure;
ocmd.BindByName = true;
ocmd.ArrayBindCount = count;
ocmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, arr_Company_code, ParameterDirection.Input);
ocmd.Parameters.Add("p_xpin", OracleDbType.Varchar2, arr_xpin, ParameterDirection.Input);
ocmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, arr_Filename, ParameterDirection.Input);
ocmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize;
int result = ocmd.ExecuteNonQuery();
ocnn.Close();
retval = ((Oracle.DataAccess.Types.OracleString[])(ocmd.Parameters["p_retval"].Value))[0].ToString();
}
catch (Exception ex)
{
retval = "1;" + ex.Message;
}
//obj.Connection.Close();
return(retval);
}
public void GenerateId()
{
var memberInfos = GetType().GetProperties().Take(2);
foreach (var memberInfo in memberInfos)
{
var keyGener = memberInfo.GetCustomAttributes(true).OfType<KeyGenerate>().ToList();
if (keyGener.Any())
{
var key = keyGener.First();
switch (key.KeyKind)
{
case KeyKind.Guid:
memberInfo.SetValue(this, Guid.NewGuid().ToString("N"));
break;
case KeyKind.Id:
if (!string.IsNullOrEmpty(key.SeqenceName))
{
string slectedSqu = string.Format("select {0}.nextval from dual", key.SeqenceName);
using (var connection = new OracleConnection(ConfigurationManager.ConnectionStrings[BaseObjectContext.Connect].ConnectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = slectedSqu;
var seq = command.ExecuteScalar();
memberInfo.SetValue(this, seq);
}
}
break;
}
}
}
}
public string postLeadger(string arr_company_Code, string arr_FileName, string arr_trans_type,
string arr_Rowid, string arr_bank_code, string arr_branch_code, string arr_Userid, string arr_type)
{
string p_return = "";
try
{
int[] arrSize = new int[1];
arrSize[0] = 1000;
string[] p_empno = new string[1];
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_FileName;
string[] p_trans_type = new string[1];
p_trans_type[0] = arr_trans_type;
string[] p_rowid = new string[1];
p_rowid[0] = arr_Rowid;
string[] p_bank_code = new string[1];
p_bank_code[0] = arr_bank_code;
string[] p_branch_code = new string[1];
p_branch_code[0] = arr_branch_code;
string[] p_userid = new string[1];
p_userid[0] = arr_Userid;
string[] p_type = new string[1];
p_type[0] = arr_type;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
conn.Open();
Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SP_Data_Segregation";
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_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("DATA_RESULTSET", OracleDbType.RefCursor, "", ParameterDirection.Output).ArrayBindSize = arrSize;
cmd.Parameters.Add("v_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize;
int result = cmd.ExecuteNonQuery();
p_return = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["v_retval"].Value))[0].ToString();
#region
/*string v_return = "";
* OracleParameter[] parm = new OracleParameter[10];
* int pno = 0;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("P_company_code", OracleType.VarChar, ParameterDirection.Input, ddlCompanyCode.SelectedValue.ToString());
* pno++;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("P_FILE_NAME", OracleType.VarChar, ParameterDirection.Input, ddlFile.SelectedValue.ToString());
* pno++;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("P_Trans_type", OracleType.VarChar, ParameterDirection.Input, "");
* pno++;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("P_Rowid", OracleType.VarChar, ParameterDirection.Input, "");
* pno++;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("p_bank_code", OracleType.VarChar, ParameterDirection.Input, "");
* pno++;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("p_branch_code", OracleType.VarChar, ParameterDirection.Input, "");
* pno++;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("P_Userid", OracleType.VarChar, ParameterDirection.Input, Session["U_NAME"].ToString());
* pno++;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("P_type", OracleType.VarChar, ParameterDirection.Input, "14");
* pno++;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("DATA_RESULTSET", OracleType.Cursor, ParameterDirection.Output, "");
* pno++;
* parm[pno] = new OracleParameter();
* parm[pno] = _dbConfig.Oracle_Param("v_retval", OracleType.VarChar, ParameterDirection.Output, "");
*
* v_return = _dbConfig.TransSPOracle_GetReturnStringVal1(conn, tran, "SP_Data_Segregation", parm, pno);
* lblMessage.Text = v_return.Split(',').GetValue(1).ToString();
* return v_return;*/
#endregion
}
catch (Exception ex)
{
p_return = ex.Message;
}
return(p_return);
}
private bool DoesQueueExist(Address address)
{
using (OracleConnection conn = new OracleConnection(this.ConnectionString))
{
conn.Open();
using (OracleCommand createTable = conn.CreateCommand())
{
createTable.BindByName = true;
createTable.CommandText = DoesQueueExistSql;
createTable.Parameters.Add("queue", this.NamePolicy.GetQueueName(address));
return Convert.ToBoolean(createTable.ExecuteScalar());
}
}
}
public OracleHelper(string ConnectionString)
{
connection_str = ConnectionString;
Connection = new OracleConnection(connection_str);
Command = Connection.CreateCommand();
}
public OracleHelper(int ConnectionStringsIndex)
{
connection_str = ConfigurationManager.ConnectionStrings[ConnectionStringsIndex].ConnectionString;
Connection = new OracleConnection(connection_str);
Command = Connection.CreateCommand();
}
public OracleHelper()
{
connection_str = default_connection_str;
Connection = new OracleConnection(connection_str);
Command = Connection.CreateCommand();
}
public DataTable EP_RUN_QUERY(string DB_Platform, string ConnAuth, string SQLin)
{
DataTable dt = new DataTable("ReturnData");
DataColumn column;
DataRow row;
switch (DB_Platform)
{
case "Oracle":
case "ORACLE":
using (OracleConnection connection = new OracleConnection(ConnAuth))
{
try
{
//Open Connection
connection.Open();
//Initiate sqlplus.
OracleCommand sqlplus = connection.CreateCommand();
//Initiate buffer for SQL syntax.
StringBuilder sqlStatement = new StringBuilder();
//Input Received SQL.
sqlStatement.Append(SQLin);
//Stage SQL Statement.
sqlplus.CommandText = sqlStatement.ToString();
//Run the SQL Statement and load data into Reader.
OracleDataReader dr = sqlplus.ExecuteReader();
//Pass Data to Datatable
dt.Load(dr);
// return (SuccessMessage);
}
catch (OracleException ex)
{
//Return Oracle Error.
//return ex.ToString();
// Create first column and add to the DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "ChildID";
column.AutoIncrement = true;
column.AutoIncrementSeed = 0;
column.AutoIncrementStep = 1;
column.Caption = "ID";
column.ReadOnly = true;
column.Unique = true;
dt.Columns.Add(column);
// Create second column and add to the DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ChildType";
column.AutoIncrement = false;
column.Caption = "ChildType";
column.ReadOnly = false;
column.Unique = false;
dt.Columns.Add(column);
// Create third column and add to the DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ChildItem";
column.AutoIncrement = false;
column.Caption = "ChildItem";
column.ReadOnly = false;
column.Unique = false;
dt.Columns.Add(column);
// Create fourth column and add to the DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ChildValue";
column.AutoIncrement = false;
column.Caption = "ChildValue";
column.ReadOnly = false;
column.Unique = false;
dt.Columns.Add(column);
row = dt.NewRow();
row["ChildType"] = "RETURN";
row["ChildItem"] = "ERROR";
row["ChildValue"] = DB_ERROR_FORMATTER("ORACLE", ex.ToString());
dt.Rows.Add(row);
//return dt;
}
finally
{
//Close connection.
connection.Close();
connection.Dispose();
}
}
return dt;
case "Microsoft":
case "MICROSOFT":
using (SqlConnection connection = new SqlConnection(ConnAuth))
{
try
{
connection.Open();
SqlCommand sqlplus = connection.CreateCommand();
StringBuilder sqlStatement = new StringBuilder();
sqlStatement.Append(SQLin);
sqlplus.CommandText = sqlStatement.ToString();
SqlDataReader dr = sqlplus.ExecuteReader();
dt.Load(dr);
}
catch (SqlException ex)
{
//Return Oracle Error.
//return ex.ToString();
// Create first column and add to the DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "ChildID";
column.AutoIncrement = true;
column.AutoIncrementSeed = 0;
column.AutoIncrementStep = 1;
column.Caption = "ID";
column.ReadOnly = true;
column.Unique = true;
dt.Columns.Add(column);
// Create second column and add to the DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ChildType";
column.AutoIncrement = false;
column.Caption = "ChildType";
column.ReadOnly = false;
column.Unique = false;
dt.Columns.Add(column);
// Create third column and add to the DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ChildItem";
column.AutoIncrement = false;
column.Caption = "ChildItem";
column.ReadOnly = false;
column.Unique = false;
dt.Columns.Add(column);
// Create fourth column and add to the DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ChildValue";
column.AutoIncrement = false;
column.Caption = "ChildValue";
column.ReadOnly = false;
column.Unique = false;
dt.Columns.Add(column);
row = dt.NewRow();
row["ChildType"] = "RETURN";
row["ChildItem"] = "ERROR";
row["ChildValue"] = DB_ERROR_FORMATTER("MICROSOFT", ex.ToString());
dt.Rows.Add(row);
}
finally
{
connection.Close();
connection.Dispose();
}
return dt;
}
default:
return dt;
}
}
public string UploadProcess(int count, DataTable dt, string Company_code, string ConfID, string FielName)
{
string retval = "";
try
{
#region //Array Declare
string[] arr_company_Code = new string[count];
string[] arr_ConfID = new string[count];
string[] arr_FielName = new string[count];
string[] F1 = new string[count];
string[] F2 = new string[count];
string[] F3 = new string[count];
string[] F4 = new string[count];
string[] F5 = new string[count];
string[] F6 = new string[count];
string[] F7 = new string[count];
string[] F8 = new string[count];
string[] F9 = new string[count];
string[] F10 = new string[count];
string[] F11 = new string[count];
string[] F12 = new string[count];
string[] F13 = new string[count];
string[] F14 = new string[count];
string[] F15 = new string[count];
string[] F16 = new string[count];
string[] F17 = new string[count];
string[] F18 = new string[count];
string[] F19 = new string[count];
string[] F20 = new string[count];
string[] F21 = new string[count];
string[] F22 = new string[count];
string[] F23 = new string[count];
string[] F24 = new string[count];
string[] F25 = new string[count];
string[] F26 = new string[count];
string[] F27 = new string[count];
string[] F28 = new string[count];
string[] F29 = new string[count];
string[] F30 = new string[count];
string[] F31 = new string[count];
string[] F32 = new string[count];
string[] F33 = new string[count];
string[] F34 = new string[count];
string[] F35 = new string[count];
string[] F36 = new string[count];
string[] F37 = new string[count];
string[] F38 = new string[count];
string[] F39 = new string[count];
string[] F40 = new string[count];
string[] F41 = new string[count];
string[] F42 = new string[count];
string[] F43 = new string[count];
string[] F44 = new string[count];
string[] F45 = new string[count];
string[] F46 = new string[count];
string[] F47 = new string[count];
string[] F48 = new string[count];
string[] F49 = new string[count];
string[] F50 = new string[count];
#endregion
int col = 0;
int[] arrSize = new int[count];
for (int i = 0; i < count; i++)
{
arrSize[i] = 1000;
#region // Array Bind
arr_company_Code[i] = Company_code;
arr_ConfID[i] = ConfID;
arr_FielName[i] = FielName;
col = 0;
F1[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F2[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F3[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F4[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F5[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F6[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F7[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F8[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F9[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F10[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F11[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F12[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F13[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F14[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F15[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F16[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F17[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F18[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F19[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F20[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F21[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F22[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F23[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F24[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F25[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F26[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F27[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F28[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F29[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F30[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F31[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F32[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F33[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F34[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F35[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F36[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F37[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F38[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F39[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F40[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F41[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F42[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F43[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F44[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F45[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F46[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F47[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F48[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F49[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
col++; if (dt.Columns.Count <= col)
{
continue;
}
F50[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : "";
#endregion
//arrSize[i] = 1000;
}
Oracle.DataAccess.Client.OracleConnection ocnn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
ocnn.Open();
Oracle.DataAccess.Client.OracleCommand ocmd = ocnn.CreateCommand();
ocmd.CommandText = "SP_RAW_DATALOAD";
ocmd.CommandType = CommandType.StoredProcedure;
ocmd.BindByName = true;
ocmd.ArrayBindCount = count;
#region //Pass array to parameter
ocmd.Parameters.Add("P_COMPANY_CODE", OracleDbType.Varchar2, arr_company_Code, ParameterDirection.Input);
ocmd.Parameters.Add("P_CONF_ID", OracleDbType.Varchar2, arr_ConfID, ParameterDirection.Input);
ocmd.Parameters.Add("P_FILE_Name", OracleDbType.Varchar2, arr_FielName, ParameterDirection.Input);
ocmd.Parameters.Add("P_F1", OracleDbType.Varchar2, F1, ParameterDirection.Input);
ocmd.Parameters.Add("P_F2", OracleDbType.Varchar2, F2, ParameterDirection.Input);
ocmd.Parameters.Add("P_F3", OracleDbType.Varchar2, F3, ParameterDirection.Input);
ocmd.Parameters.Add("P_F4", OracleDbType.Varchar2, F4, ParameterDirection.Input);
ocmd.Parameters.Add("P_F5", OracleDbType.Varchar2, F5, ParameterDirection.Input);
ocmd.Parameters.Add("P_F6", OracleDbType.Varchar2, F6, ParameterDirection.Input);
ocmd.Parameters.Add("P_F7", OracleDbType.Varchar2, F7, ParameterDirection.Input);
ocmd.Parameters.Add("P_F8", OracleDbType.Varchar2, F8, ParameterDirection.Input);
ocmd.Parameters.Add("P_F9", OracleDbType.Varchar2, F9, ParameterDirection.Input);
ocmd.Parameters.Add("P_F10", OracleDbType.Varchar2, F10, ParameterDirection.Input);
ocmd.Parameters.Add("P_F11", OracleDbType.Varchar2, F11, ParameterDirection.Input);
ocmd.Parameters.Add("P_F12", OracleDbType.Varchar2, F12, ParameterDirection.Input);
ocmd.Parameters.Add("P_F13", OracleDbType.Varchar2, F13, ParameterDirection.Input);
ocmd.Parameters.Add("P_F14", OracleDbType.Varchar2, F14, ParameterDirection.Input);
ocmd.Parameters.Add("P_F15", OracleDbType.Varchar2, F15, ParameterDirection.Input);
ocmd.Parameters.Add("P_F16", OracleDbType.Varchar2, F16, ParameterDirection.Input);
ocmd.Parameters.Add("P_F17", OracleDbType.Varchar2, F17, ParameterDirection.Input);
ocmd.Parameters.Add("P_F18", OracleDbType.Varchar2, F18, ParameterDirection.Input);
ocmd.Parameters.Add("P_F19", OracleDbType.Varchar2, F19, ParameterDirection.Input);
ocmd.Parameters.Add("P_F20", OracleDbType.Varchar2, F20, ParameterDirection.Input);
ocmd.Parameters.Add("P_F21", OracleDbType.Varchar2, F21, ParameterDirection.Input);
ocmd.Parameters.Add("P_F22", OracleDbType.Varchar2, F22, ParameterDirection.Input);
ocmd.Parameters.Add("P_F23", OracleDbType.Varchar2, F23, ParameterDirection.Input);
ocmd.Parameters.Add("P_F24", OracleDbType.Varchar2, F24, ParameterDirection.Input);
ocmd.Parameters.Add("P_F25", OracleDbType.Varchar2, F25, ParameterDirection.Input);
ocmd.Parameters.Add("P_F26", OracleDbType.Varchar2, F26, ParameterDirection.Input);
ocmd.Parameters.Add("P_F27", OracleDbType.Varchar2, F27, ParameterDirection.Input);
ocmd.Parameters.Add("P_F28", OracleDbType.Varchar2, F28, ParameterDirection.Input);
ocmd.Parameters.Add("P_F29", OracleDbType.Varchar2, F29, ParameterDirection.Input);
ocmd.Parameters.Add("P_F30", OracleDbType.Varchar2, F30, ParameterDirection.Input);
ocmd.Parameters.Add("P_F31", OracleDbType.Varchar2, F31, ParameterDirection.Input);
ocmd.Parameters.Add("P_F32", OracleDbType.Varchar2, F32, ParameterDirection.Input);
ocmd.Parameters.Add("P_F33", OracleDbType.Varchar2, F33, ParameterDirection.Input);
ocmd.Parameters.Add("P_F34", OracleDbType.Varchar2, F34, ParameterDirection.Input);
ocmd.Parameters.Add("P_F35", OracleDbType.Varchar2, F35, ParameterDirection.Input);
ocmd.Parameters.Add("P_F36", OracleDbType.Varchar2, F36, ParameterDirection.Input);
ocmd.Parameters.Add("P_F37", OracleDbType.Varchar2, F37, ParameterDirection.Input);
ocmd.Parameters.Add("P_F38", OracleDbType.Varchar2, F38, ParameterDirection.Input);
ocmd.Parameters.Add("P_F39", OracleDbType.Varchar2, F39, ParameterDirection.Input);
ocmd.Parameters.Add("P_F40", OracleDbType.Varchar2, F40, ParameterDirection.Input);
ocmd.Parameters.Add("P_F41", OracleDbType.Varchar2, F41, ParameterDirection.Input);
ocmd.Parameters.Add("P_F42", OracleDbType.Varchar2, F42, ParameterDirection.Input);
ocmd.Parameters.Add("P_F43", OracleDbType.Varchar2, F43, ParameterDirection.Input);
ocmd.Parameters.Add("P_F44", OracleDbType.Varchar2, F44, ParameterDirection.Input);
ocmd.Parameters.Add("P_F45", OracleDbType.Varchar2, F45, ParameterDirection.Input);
ocmd.Parameters.Add("P_F46", OracleDbType.Varchar2, F46, ParameterDirection.Input);
ocmd.Parameters.Add("P_F47", OracleDbType.Varchar2, F47, ParameterDirection.Input);
ocmd.Parameters.Add("P_F48", OracleDbType.Varchar2, F48, ParameterDirection.Input);
ocmd.Parameters.Add("P_F49", OracleDbType.Varchar2, F49, ParameterDirection.Input);
ocmd.Parameters.Add("P_F50", OracleDbType.Varchar2, F50, ParameterDirection.Input);
ocmd.Parameters.Add("v_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize;
#endregion
int result = ocmd.ExecuteNonQuery();
ocnn.Close();
retval = ((Oracle.DataAccess.Types.OracleString[])(ocmd.Parameters["v_retval"].Value))[0].ToString();
}
catch (Exception ex)
{
retval = ex.Message;
}
return(retval);
}
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 string[] CustomerFindingUpload(int count, string Userid, DataTable dt, string batch_no, out string val)
{
string retval = "";
string[] retmsg = new string[count];
string[] retCount = new string[count];
Oracle.DataAccess.Client.OracleConnection conn = null;
try
{
#region
string[] p_company_code = new string[count];
string[] p_value_date = new string[count];
string[] p_amount = new string[count];
string[] p_control_no = new string[count];
string[] p_dated = new string[count];
string[] p_narration = new string[count];
string[] p_ref1 = new string[count];
string[] p_ref2 = new string[count];
string[] p_ref3 = new string[count];
string[] p_ref4 = new string[count];
string[] p_ref5 = new string[count];
string[] p_ref6 = new string[count];
string[] p_userid = new string[count];
string[] p_retval = new string[count];
string[] p_batch_no = new string[count];
int[] arrSize = new int[count];
for (int i = 0; i < count; i++)
{
p_company_code[i] = dt.Rows[i][0].ToString();
p_value_date[i] = dt.Rows[i][1].ToString();
p_amount[i] = dt.Rows[i][2].ToString();
p_control_no[i] = dt.Rows[i][3].ToString();
p_dated[i] = dt.Rows[i][4].ToString();
p_narration[i] = dt.Rows[i][5].ToString();
p_ref1[i] = dt.Rows[i][6].ToString();
p_ref2[i] = dt.Rows[i][7].ToString();
p_ref3[i] = dt.Rows[i][8].ToString();
p_ref4[i] = dt.Rows[i][9].ToString();
p_ref5[i] = dt.Rows[i][10].ToString();
p_ref6[i] = dt.Rows[i][11].ToString();
p_userid[i] = Userid;
p_batch_no[i] = batch_no;
arrSize[i] = 1000;
}
#endregion
#region
conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "sp_cust_funding_upload";
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.ArrayBindCount = count;
cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, p_company_code, ParameterDirection.Input);
cmd.Parameters.Add("p_value_date", OracleDbType.Varchar2, p_value_date, ParameterDirection.Input);
cmd.Parameters.Add("p_amount", OracleDbType.Varchar2, p_amount, ParameterDirection.Input);
cmd.Parameters.Add("p_control_no", OracleDbType.Varchar2, p_control_no, ParameterDirection.Input);
cmd.Parameters.Add("p_dated", OracleDbType.Varchar2, p_dated, ParameterDirection.Input);
cmd.Parameters.Add("p_narration", OracleDbType.Varchar2, p_narration, ParameterDirection.Input);
cmd.Parameters.Add("p_ref1", OracleDbType.Varchar2, p_ref1, ParameterDirection.Input);
cmd.Parameters.Add("p_ref2", OracleDbType.Varchar2, p_ref2, ParameterDirection.Input);
cmd.Parameters.Add("p_ref3", OracleDbType.Varchar2, p_ref3, ParameterDirection.Input);
cmd.Parameters.Add("p_ref4", OracleDbType.Varchar2, p_ref4, ParameterDirection.Input);
cmd.Parameters.Add("p_ref5", OracleDbType.Varchar2, p_ref5, ParameterDirection.Input);
cmd.Parameters.Add("p_ref6", OracleDbType.Varchar2, p_ref6, ParameterDirection.Input);
cmd.Parameters.Add("p_userid", OracleDbType.Varchar2, p_userid, ParameterDirection.Input);
cmd.Parameters.Add("p_batch_no", OracleDbType.Varchar2, p_batch_no, ParameterDirection.Input);
cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, p_retval, ParameterDirection.Output).ArrayBindSize = arrSize;
#endregion
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
for (int i = 0; i < count; i++)
{
retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[i].ToString();
}
val = "0;File successfully uploaded.";
}
catch (Exception ex)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
val = "1;" + ex.Message;
}
return(retCount);
}
public string A2ATransaction(int count, string companyCode, string fileName, GridView gv)
{
string retval = "";
string[] retCount = new string[count];
try
{
string[] p_company_code = new string[1];
string[] p_filename = new string[1];
string[] p_row_id = new string[1];
int[] arrSize = new int[1];
int y = 0;
for (int i = 0; i < count; i++)
{
CheckBox cb = ((CheckBox)gv.Rows[i].FindControl("cbPublish"));
if (((CheckBox)gv.Rows[i].FindControl("cbPublish")).Checked == true)
{
/*arrSize[i] = 1000;
* p_company_code[i] = companyCode;
* p_filename[i] = fileName;
* p_row_id[i] = gv.Rows[i].Cells[0].Text;*/
Array.Resize(ref arrSize, y + 1);
arrSize[y] = 1000;
Array.Resize(ref p_company_code, y + 1);
p_company_code[y] = companyCode;
Array.Resize(ref p_filename, y + 1);
p_filename[y] = fileName;
Array.Resize(ref p_row_id, y + 1);
p_row_id[y] = gv.Rows[i].Cells[0].Text;
y++;
}
}
count = y;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
conn.Open();
Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "corebank_system_int_symbol";
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.ArrayBindCount = count;
cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, p_company_code, ParameterDirection.Input);
cmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, p_filename, ParameterDirection.Input);
cmd.Parameters.Add("p_row_id", OracleDbType.Varchar2, p_row_id, ParameterDirection.Input);
cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize;
int result = cmd.ExecuteNonQuery();
for (int i = 0; i < count; i++)
{
retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[i].ToString();
if (retCount[i].ToString().StartsWith("1") == false)
{
return(retCount[i]);
}
}
retval = retCount[0];
}
catch (Exception ex)
{
retval = ex.Message;
}
return(retval);
}
//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);
}
