public static User GetUser(string username)
{
User user = null;
if (username != null)
{
try
{
using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
{
OracleCommand command = new OracleCommand();
command.CommandText = "SELECT password,customerId,securityQuestion,securityAnswer,email FROM Users WHERE username LIKE :username";
command.Parameters.Add(":username", OracleDbType.NVarchar2).Value = username;
command.Connection = connection;
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string password = reader["password"].ToString();
string customerId = reader["customerId"].ToString();
string securityQuestion = reader["securityQuestion"].ToString();
string securityAnswer = reader["securityAnswer"].ToString();
string email = reader["email"].ToString();
user = new User(username, password, customerId, securityQuestion, securityAnswer, email);
}
}
}
catch (Exception e)
{
user = null;
Logger.LogException(e);
}
}
return user;
}
public bool Limpeza()
{
bool ret = false;
try
{
using (OracleConnection connection = new OracleConnection(RetornoCIP.CNX_C3))
{
OracleCommand command = new OracleCommand("TRUNCATE TABLE CCCTBLBRE", connection);
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
if (Utils._logger != null)
Utils._logger.Info("Limpeza ok");
ret = true;
}
}
catch (Exception ex)
{
if (Utils._logger != null)
Utils._logger.Error(ex.Message);
}
return ret;
}
public OracleTransformationProvider(Dialect dialect, string connectionString)
: base(dialect, connectionString)
{
_connection = new OracleConnection();
_connection.ConnectionString = _connectionString;
_connection.Open();
}
public int Insert(EmployeeTSI info)
{
OracleConnection conn = new OracleConnection(ConStr);
try
{
int EmpId=0;
OracleCommand command = new OracleCommand("EmpTSI_update", conn);
command.CommandType = CommandType.StoredProcedure;
var _params = command.Parameters;
conn.Open();
SetParameterValue(_params, "p_empl_id_no", info.Empl_Id_No, OracleDbType.Int32);
SetParameterValue(_params, "p_tsi", info.TSI, OracleDbType.Varchar2);
SetParameterValue(_params, "p_user_id", this.UserId, OracleDbType.Varchar2);
SetParameterValue(_params, "p_img", info.IMG, OracleDbType.Blob);
SetParameterValue(_params, "p_rtf", info.RTF, OracleDbType.Blob);
SetParameterValue(_params, "p_rif", info.RIF, OracleDbType.Blob);
SetParameterValue(_params, "p_rmf", info.RMF, OracleDbType.Blob);
SetParameterValue(_params, "p_rrf", info.RRF, OracleDbType.Blob);
SetParameterValue(_params, "p_rsf", info.RSF, OracleDbType.Blob);
SetParameterValue(_params, "p_ltf", info.LTF, OracleDbType.Blob);
SetParameterValue(_params, "p_lif", info.LIF, OracleDbType.Blob);
SetParameterValue(_params, "p_lmf", info.LMF, OracleDbType.Blob);
SetParameterValue(_params, "p_lrf", info.LRF, OracleDbType.Blob);
SetParameterValue(_params, "p_lsf", info.LSF, OracleDbType.Blob);
command.ExecuteNonQuery();
conn.Close();
return EmpId;
}
catch (Exception ex)
{
throw ex;
}
}
public string CheckConnection()
{
DB_Toolbox env_dbkit = new DB_Toolbox();
using (OracleConnection connection =
new OracleConnection())
{
connection.ConnectionString =
constr;
try
{
connection.Open();
// DBstatus = ("Connection Successful!");
return ("EMIN-00001-Connection Successful!");
//stops the console from closing
//until you hit the ENTER key
//Console.ReadLine();
}
catch (OracleException ex)
{
//DBstatus = ("Connection Error!");
return ("EMIN-00000-Connection Error! " + env_dbkit.DB_ERROR_FORMATTER("ORACLE", ex.ToString()));
//Console.WriteLine(ex.ToString());
// stops the console from closing
//until you hit the ENTER key
// Console.ReadLine();
}
}
}
public Database(string serverHostName, string userName, string userPassword, string sidDbInstanceName = "orcl", int port = 1521)
{
this.serverHostName = serverHostName;
this.sidDbInstanceName = sidDbInstanceName;
this.userName = userName;
this.userPassword = userPassword;
this.port = port;
// SID = identify a particular database instance on a computer
string database = $"Data Source = (DESCRIPTION = " +
$"(ADDRESS = (PROTOCOL = TCP)(HOST = {serverHostName})(PORT = {port}))" +
$"(CONNECT_DATA = (SID = {sidDbInstanceName})));" +
$"User Id = {userName};Password = {userPassword};";
try
{
db = new OracleConnection(database);
db.Open();
System.Windows.MessageBox.Show($"Success!\nConnected to Oracle {db.ServerVersion}", "Connecting to database...");
}
catch (OracleException e)
{
string issue = $"Failed to connect to database host < {serverHostName} >\nException: {e.Message}\nSource: {e.Source}";
System.Windows.MessageBox.Show(issue, "This app will not work correctly until connected, closing...");
App.Current.Shutdown();
}
}
private void button1_Click(object sender, EventArgs e)
{
string oradb = "Data Source=XE;User Id=system;Password=admin;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "Delete from pharm.DRUG where DRUG_ID=" + textBox1.Text + " ";
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
MessageBox.Show("Record not deleted");
else
MessageBox.Show("Successfully deleted!");
conn.Dispose();
}
private void buttonupdate_Click(object sender, EventArgs e)
{
string oradb = "Data Source=XE;User Id=system;Password=admin;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "update pharm.DRUG set D_NAME = '" + updatename.Text + "', MFD = '"+ textBox3.Text +"', EXP= '"+ updateexp.Text + "' , COST ="+ updatecost.Text +", QUANTITY = "+ textBox2.Text + " WHERE DRUG_ID = " + textBox1.Text;
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
MessageBox.Show("Record not inserted");
else
MessageBox.Show("Success!");
conn.Dispose();
}
// prompt to repeat connection attempt
public void Connect(string connectionString)
{
/* loop incase we want to retry the connection */
bool retryConnection = true;
while (retryConnection)
{
try
{
_db = new OracleConnection(connectionString);
_db.Open();
MessageBox.Show($"Success!\n\nConnected to Oracle {_db.ServerVersion}", "Connecting to database...");
retryConnection = false; // connected successfully
}
catch (OracleException e)
{
string issue = $"Failed to connect to database host < {_serverHostName} >\nException: {e.Message}\nSource: {e.Source}\n\n" +
"Would you like to retry?";
if (MessageBox.Show(issue, "Sorry! Looks like we ran into an error!",
MessageBoxButton.YesNo) == MessageBoxResult.No)
{
retryConnection = false;
App.Current.Shutdown(); // terminate
}
}
}
}
public Object DLookUp(String column,String table,String condition)
{
OracleConnection objConexion;
OracleDataAdapter objComando;
DataSet requestquery = new DataSet();
object result;
objConexion = new OracleConnection(driver);
objConexion.Open();
if(condition.Equals("")){
objComando = new OracleDataAdapter("Select " + column + " from " + table, objConexion);
}
else
{
objComando = new OracleDataAdapter("Select " + column + " from " + table + " where " + condition, objConexion);
}
try
{
objComando.Fill(requestquery);
result = requestquery.Tables[0].Rows[0][requestquery.Tables[0].Columns.IndexOf(column)];
}
catch (Exception e)
{
result = -1;
}
objConexion.Close();
return result;
}
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 void Connect()
{
con = new OracleConnection();
con.ConnectionString = connectionstring;
con.Open();
Console.WriteLine("Connection succesfull");
}
/// <summary>
/// Metodo utilizado para validad la conexion a una base de datos Oracle
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public bool TestConnection(string username, string password)
{
this.username = username;
this.password = password;
string[] parametrosConnectionString = CadenaConexion.Split(';');
var dataSourceEncontrado = CadenaConexion.ToUpper().Split(';').FirstOrDefault(c => c.Contains("DATA SOURCE"));
DataSource = dataSourceEncontrado.Substring(dataSourceEncontrado.IndexOf("DATA SOURCE") + 12);
CadenaConexion = ConstruirCadenaConexion(parametrosConnectionString);
//Se agrego referencia Oracle.DataAccess.Client para framework 4
using (OracleConnection oracle = new OracleConnection())
{
try
{
var CadenaConexion1 = string.Format("Data Source={0}; User Id={1}; Password={2}; Pooling=true; Min Pool Size=1; Max Pool Size=2; Connection Timeout=30", DataSource, Username, Password);
oracle.ConnectionString = CadenaConexion1;
oracle.Open();
return true;
}
catch
{
return false;
}
finally
{
oracle.Close();
oracle.Dispose();
}
}
}
public void ConnectExample()
{
using (var connection = new OracleConnection(ConnectionString))
{
connection.Open();
var command = new OracleCommand("Select * from \"MCC_User\"");
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine(reader["Name"]);
}
}
var schema = reader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
Console.WriteLine(row[0]);
}
}
Console.ReadKey();
}
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 void RawQuery(String Query)
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = "User Id=SYSTEM;Password=oracle";
OracleDataReader rdr;
try
{
conn.Open();
//OracleCommand cmd = new OracleCommand("select * from employee", conn);
OracleCommand cmd = new OracleCommand(Query, conn);
rdr = cmd.ExecuteReader();
int rdr_size = (int)rdr.VisibleFieldCount;
int n = 0;
while (rdr.Read())
{
n = 0;
while (n < rdr_size)
{
Console.Write("{0,-15} ",rdr[n]);
n++;
}
Console.WriteLine();
}
conn.Dispose();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private void bProcedure_Click(object sender, EventArgs e)
{
//Verbinding openen (waarschijnlijk doe je dit in je applicatie niet voor ieder database commando)
using (OracleConnection objConn = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=webdb.hi.fontys.nl)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=cicdb.informatica.local))); User ID=" + mUsername.Text + "; Password="******"Verdubbel"; //Naam van de stored procedure welke we aanroepen
//Mee te geven parameters instellen (richting, type en eventueel een waarde)
objCmd.Parameters.Add("teVerdubbelen",OracleDbType.Decimal).Value = Convert.ToInt32(nTeVerdubbelenGetal.Value);
objCmd.Parameters.Add("resultaat", OracleDbType.Decimal).Direction = ParameterDirection.Output;
//Connectie maken en het commando uitvoeren
try
{
objConn.Open();
objCmd.ExecuteNonQuery(); //Voert de stored procedure uit
//Tonen van de waarde in de resultaat parameter na uitvoeren van de stored procedure
MessageBox.Show("De verdubbelde waarde is: "+objCmd.Parameters["resultaat"].Value);
}
catch (Exception ex)
{
//Voor het geval "iets" mis gaat, de letterlijke foutmelding tonen (doe je natuurlijk niet in een "echte" applicatie)
MessageBox.Show("De volgende fout is opgetreden: "+ex.ToString());
}
//Verbinding sluiten (waarschijnlijk doe je dit in je applicatie niet per database commando)
objConn.Close();
}
}
private void button1_Click_1(object sender, EventArgs e)
{
int a;
a = Convert.ToInt32(textBox4.Text);
a = int.Parse(textBox4.Text);
string oradb = "Data Source=XE;User Id=system;Password=admin;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "update pharm.DRUG set QUANTITY =QUANTITY - " + a + "";
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
MessageBox.Show("Record not inserted");
else
MessageBox.Show("Success!");
conn.Dispose();
}
public OracleConnection NewConnection(string username, string password)
{
string oradb = "Data Source=HUNDIPLUG12; User Id="+ username + "; Password="******";";
connection = new OracleConnection(oradb); // C#
connection.Open();
return connection;
}
public UsAuth getAuthForIdUser(string email, string passwd)
{
OracleConnection connection = null;
OracleDataReader dr = null;
UsAuth auth = null;
try
{
using (connection = new OracleConnection(OracleHelper.connectionString()))
{
using (var command = new OracleCommand("US_AUTH_GETFOR_USER", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("P_CUR_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
command.Parameters.Add("EMAIL", email);
command.Parameters.Add("PASWD", passwd);
using (dr = command.ExecuteReader())
{
if (dr.HasRows)
{
auth = new UsAuth()
{
IdUser = Convert.ToInt32(dr["IDUSER"].ToString()),
Email = dr["EMAIL"].ToString(),
FirstName = dr["FIRSTNAME"].ToString(),
LastName = dr["LASTNAME"].ToString(),
Role = new UsRole()
{
Id = Convert.ToInt32(dr["IDROLE"].ToString()),
Name = dr["NAMEROLE"].ToString()
},
Photo = dr["PHOTO"].ToString(),
ListRoleModule = new DARoleModule().getRoleModuleForRole(Convert.ToInt32(dr["IDROLE"].ToString())),
ListRoleModulePrivilege = new DARoleModulePrivilege().getRoleModulePrivilegeForRole(Convert.ToInt32(dr["IDROLE"].ToString()))
};
}
}
}
}
return auth;
}
catch (Exception e)
{
dr.Dispose();
if (connection.State == ConnectionState.Open)
connection.Dispose();
LogHelper.WriteLog(e);
throw e;
}
finally
{
auth = null;
}
}
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 static bool IsAvailable(string columnName, string value)
{
bool available = false;
if (!String.IsNullOrWhiteSpace(columnName) && value != null)
{
try
{
using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
{
OracleCommand command = new OracleCommand();
command.CommandText = String.Format("SELECT COUNT(*) FROM Users WHERE {0} LIKE :value",columnName);
command.Parameters.Add(":value", OracleDbType.NVarchar2).Value = value;
command.Connection = connection;
connection.Open();
int count = Convert.ToInt32(command.ExecuteScalar());
if (count==0)
available = true;
else
available = false;
}
}
catch (Exception e)
{
available = false;
Logger.LogException(e);
}
}
return available;
}
/// <summary>
/// Use only to retrieve user on initialization
/// </summary>
/// <param name="query">The input query</param>
/// <returns>A DataTable containing the results of the query</returns>
public static DataTable HaalGebruikersOp(string query)
{
using (OracleConnection conn = new OracleConnection(connectionstring2))
{
try
{
conn.Open();
OracleCommand cmd = new OracleCommand(query);
cmd.Connection = conn;
try
{
OracleDataReader reader = cmd.ExecuteReader();
DataTable result = new DataTable();
result.Load(reader);
conn.Close();
return result;
}
catch (OracleException ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
catch (OracleException ex)
{
Console.WriteLine(ex.Message);
return new DataTable();
}
}
}
private Connection()
{
try
{
string Dsource = "(DESCRIPTION="
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=bd.thronewars.ca)(PORT=1521)))"
+ "(CONNECT_DATA=(SERVICE_NAME=ORCL)))";
String ChaineConnexion = "Data Source=" + Dsource
+ ";User Id=THRONE; Password =Warst";
conn = new OracleConnection(ChaineConnexion);
conn.Open();
if (conn.State.ToString() != "Open")
{
// to do
}
}
catch (OracleException ex)
{
Erreur.ErrorMessage(ex);
}
}
public IBeheerContextEntity GetBusinessObject(string qry, BeheerContextEntity beheerObject)
{
using (var conn = new OracleConnection(_oradb))
{
conn.Open();
var cmd = new OracleCommand
{
Connection = conn,
CommandText = qry,
CommandType = CommandType.Text
};
cmd.Connection = conn;
var reader = cmd.ExecuteReader();
var smartReader = new SmartDataReader(reader);
BeheerContextEntity businessObject = null;
while (smartReader.Read())
{
businessObject = new BeheerContextEntity
{
Id = smartReader.GetInt32("id"),
DataKeyValue = smartReader.GetString(beheerObject.DataKeyName, ""),
Tablename = beheerObject.Tablename,
DataKeyName = beheerObject.DataKeyName
};
}
return businessObject;
}
}
public static bool AddIncident(Incident i, int missieUID)
{
using (OracleConnection o = new OracleConnection(connstring))
{
using (OracleCommand c = new OracleCommand())
{
try
{
c.Connection = o;
o.Open();
c.CommandText = "INSERT INTO TIncident (Beschrijving) VALUES ('" + i.Beschrijving + "')";
c.ExecuteNonQuery();
c.CommandText = "SELECT MAX(ID) FROM TIncident";
OracleDataReader dr = c.ExecuteReader();
if (dr.Read())
{
c.CommandText = "INSERT INTO TMissie_Incident (MissieID, IncidentID) VALUES ('" + missieUID + "', '" + GetInt(dr[0]) + "')";
c.ExecuteNonQuery();
}
return true;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return false;
}
finally
{
o.Close();
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
//todo: (004-xxxDataReader) remover o botão, mantendo a funcionalidade da página
#region solução de "remover o botão, mantendo a funcionalidade da página"
//ver o arquivo WebForm1.aspx
#endregion
DropDownList1.SelectedIndexChanged += DropDownList1SelectedIndexChanged;
if (IsPostBack) return;
DropDownList1.Items.Clear();
DropDownList1.Items.Add(new ListItem("(escolha uma opção)", "-1"));
using (var c = new OracleConnection(@"DATA SOURCE=127.0.0.1/ORCL;USER ID=chinook;PASSWORD=p4ssw0rd;"))
{
var k = new OracleCommand("SELECT * FROM GENRE", c);
c.Open();
var dr = k.ExecuteReader();
while (dr.Read())
{
DropDownList1.Items.Add(new ListItem(dr[1].ToString(), dr[0].ToString()));
}
c.Close();
DropDownList1.DataBind();
}
}
public static void GetEmployeeMatches(List<Employee> list, DPFP.Sample Sample,int FingerNo)
{
OracleConnection conn = new OracleConnection(ConStr);
Employee info = null;
string FingerDesc = Util.GetFingerDesc(FingerNo);
try
{
string _result = string.Empty;
string sql = "select Empl_Id_No,RTF,RIF,RMF,RRF,RSF,LTF,LIF,LMF,LRF,LSF from EMPTSI";
OracleCommand command = new OracleCommand(sql, conn);
command.CommandType = CommandType.Text;
conn.Open();
OracleDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
info = dcEmployee.GetInfo(reader, Sample, FingerDesc);
AddInfo(list, info);
}
}
if (conn.State == ConnectionState.Open) conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable Consulta
(
string pQuery,
string pConnectionString
)
{
DataTable lTable = new DataTable();
DataSet lDataSet = new DataSet();
OracleConnection lConnection = new OracleConnection(pConnectionString);
try
{
lConnection.Open();
OracleDataAdapter lDataAdapter = new OracleDataAdapter(pQuery, pConnectionString);
lDataAdapter.Fill(lDataSet);
lTable = lDataSet.Tables[0];
return lTable;
}
finally
{
lConnection.Close();
}
}
public static Employee GetInfo(OracleDataReader reader, DPFP.Sample Sample, string Finger)
{
OracleConnection conn = new OracleConnection(ConStr);
Employee _info = null;
DPFP.Template _template = null;
bool IsFound = false;
if (reader[Finger] != DBNull.Value)
{
_template = Util.ProcessDBTemplate((byte[])reader[Finger]);
IsFound = Util.Verify(Sample, _template);
}
if (IsFound == true)
{
string sqlEmp = "select * from employees where Empl_Id_No=" + reader["Empl_Id_No"];
OracleCommand cmd = new OracleCommand(sqlEmp, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
_info = new Employee();
_info.Empl_Id_No = Convert.ToInt32(reader["Empl_Id_No"]);
_info.Empl_Name = (string)odr["Empl_Name"];
_info.Empl_Deptname = (string)odr["Empl_Deptname"];
_info.Shift_Id = Convert.ToInt32(odr["Shift_Id"]);
}
odr.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return _info;
}
protected void IngresarTarea_2_Click(object sender, EventArgs e)
{
try
{
//Para el Usuario
ora.Open();
//Llamar al Stored Procedure
Oracle.DataAccess.Client.OracleCommand comando1 = new Oracle.DataAccess.Client.OracleCommand("seleccionar_usuarios", ora);
comando1.CommandType = System.Data.CommandType.StoredProcedure;
//Asignar variable de cursor
comando1.Parameters.Add("p_correo", OracleDbType.Varchar2).Value = txtCorreoEncargado_2.Text;
comando1.Parameters.Add("o_id", OracleDbType.Int32, null, ParameterDirection.Output);
comando1.ExecuteNonQuery();
Oracle.DataAccess.Client.OracleDataReader dr = comando1.ExecuteReader();
int id = Int32.Parse(comando1.Parameters["o_id"].Value.ToString());
lblidddl = id.ToString();
ora.Close();
//Para la Tarea
string tipo = ddlTipoTarea_2.SelectedItem.ToString();
lblidddl = tipo;
GridViewRow rowTarea = tablaTareas.SelectedRow;
string textTarea = rowTarea.Cells[1].Text;
//FECHA
var date = fecha.SelectedDate;
ora2.Open();
System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_TAREA_SUB", ora2);
comando2.CommandType = System.Data.CommandType.StoredProcedure;
comando2.Parameters.Add("P_IDTAREA", OracleType.Int32).Value = Int32.Parse(textTarea);
comando2.Parameters.Add("P_NOMBRE", OracleType.VarChar).Value = txtNombre.Text;
comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtDescripcionTarea_2.Text;
comando2.Parameters.Add("P_IDUSUARIO", OracleType.Int32).Value = id;
comando2.Parameters.Add("P_ESTADO", OracleType.Int32).Value = 4;
comando2.Parameters.Add("P_TIPOTAREA", OracleType.VarChar).Value = tipo;
comando2.Parameters.Add("P_FECHA", OracleType.DateTime).Value = date.ToString();
comando2.ExecuteNonQuery();
// ora2.Close();
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Insertada');</script>");
CargarQuerys();
txtDescripcionTarea_2.Text = "";
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>");
}
}
private void SQLToCSV(string nameOwner, string query, string domainFileName, string pathDomain)
{
Oracle.DataAccess.Client.OracleConnection conn = DBConnectionDTOP();
conn.Open();
OracleCommand cmd = new OracleCommand(query, conn);
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
CreateExcelFile(nameOwner, domainFileName, pathDomain, dr);
}
dr.Close();
dr.Dispose();
conn.Close();
}
public static Database GetInstance()
{
string dbConnName = DataConnectionName;
if (_db == null)
{
try
{
_db = new Database(dbConnName);
}
catch
{
string connstr = System.Configuration.ConfigurationManager.ConnectionStrings[dbConnName].ToString();
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connstr);
conn.Open();
_db = new Database(conn);
}
}
else
{
//重试连接，以下代码尚未测试，请进行测试。。。by liuzhy 20140421
int n = 3; //设定重试多少次；
int delay = 200; //设定每次重试间隔多少毫秒；
string connstr = System.Configuration.ConfigurationManager.ConnectionStrings[dbConnName].ToString();
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connstr);
while (_db.Connection.State != System.Data.ConnectionState.Open)
{
try
{
conn.Open();
_db = new Database(conn);
}
catch (Exception ex)
{
//可以记录一下访问失败的一些信息，比如时间、ip地址、ex.Message等
}
System.Threading.Thread.Sleep(delay);
n--;
if (n.Equals(0))
{
break;
}
}
}
_db.OpenSharedConnection();
return(_db);
}
public int ExecuteNonQuery(string cmdText, CommandType cmdType, List <Oracle.DataAccess.Client.OracleParameter> parameters = null)
{
using (var sCon = new Oracle.DataAccess.Client.OracleConnection(GetConnectionString()))
{
using (var sCmd = new Oracle.DataAccess.Client.OracleCommand(cmdText, sCon))
{
sCmd.CommandTimeout = 600;
sCmd.CommandType = cmdType;
if (null != parameters)
{
sCmd.Parameters.AddRange(parameters.ToArray());
}
sCon.Open();
return(sCmd.ExecuteNonQuery());
}
}
}
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 static IEnumerable <Customer> getCustomerStored()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "tables.getcustomertable",
CommandType = CommandType.StoredProcedure
};
var reader = cmd.ExecuteNonQuery();
return(null);
}
private string SearchDomain(string columnType, int columnSize, int columnPrecision)
{
string strSQL = RelatedDomainQuery(columnType, columnSize, columnPrecision);
Oracle.DataAccess.Client.OracleConnection conn = DBConnectionCUBO();
conn.Open();
try
{
OracleCommand cmd = new OracleCommand(strSQL, conn);
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
return(dr[0].ToString());
}
return("");
}
finally { conn.Close(); }
}
private void FillsOwnerList()
{
string strSQL = "";
strSQL = OwnersQuery();
Oracle.DataAccess.Client.OracleConnection conn = DBConnectionDTOP();
conn.Open();
OracleCommand cmd = new OracleCommand(strSQL, conn);
OracleDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
cboOwner.DisplayMember = "Owner";
cboOwner.ValueMember = "Owner";
cboOwner.DataSource = dt;
conn.Close();
}
private bool VerifyPrimaryKey(string domainOwner, string tableDomain)
{
string strSQL = VerifyPrimaryKeyQuery(domainOwner, tableDomain);
Oracle.DataAccess.Client.OracleConnection conn = DBConnectionDTOP();
conn.Open();
try
{
OracleCommand cmd = new OracleCommand(strSQL, conn);
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
return(true);
}
return(false);
}
finally
{ conn.Close(); }
}
public void addPizzaBase(PizzaBase d)
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "insert into pizzabase values (:BASEID, :PBSID, :DOUGH)"
};
cmd.Parameters.Add("BASEID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["BASEID"].Value = d.BaseID;
cmd.Parameters.Add("PBSID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["PBSID"].Value = d.PbsID;
cmd.Parameters.Add("DOUGH", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["DOUGH"].Value = d.Dough;
cmd.ExecuteNonQuery();
DataSource.pizzasBase = getPizzaBases();
}
public void addOrder(Order d)
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "insert into Orders values (:ORDERID, :ORDERDELIV, :CUSTID, :STOREID)"
};
cmd.Parameters.Add("ORDERID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["ORDERID"].Value = d.OrderID;
cmd.Parameters.Add("ORDERDELIV", OracleDbType.Char).Direction = ParameterDirection.Input;
cmd.Parameters["ORDERDELIV"].Value = d.OrderDeliv?1:0;
cmd.Parameters.Add("CUSTID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["CUSTID"].Value = d.CustID;
cmd.Parameters.Add("STOREID", OracleDbType.Decimal).Direction = ParameterDirection.Input;
cmd.Parameters["STOREID"].Value = d.StoreID;
cmd.ExecuteNonQuery();
}
public List <Dough> getDoughs()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT DOUGH.* FROM DOUGH"
};
var doughs = new List <Dough>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
doughs.Add(new Dough
{
DoughID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("DOUGHID"))),
Name = reader.GetString(reader.GetOrdinal("NAME"))
});
}
return(doughs);
}
public List <PizzaBaseSize> getPizzaBaseSizes()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT PIZZABASESIZE.* FROM PIZZABASESIZE"
};
var pizzaBaseSizes = new List <PizzaBaseSize>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
pizzaBaseSizes.Add(new PizzaBaseSize()
{
PbsID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("PBSID"))),
PdsSize = reader.GetString(reader.GetOrdinal("PDSSIZE"))
});
}
return(pizzaBaseSizes);
}
public int promotion_average_credit()
{
var con = new OracleConnection(constr);
con.Open();
var objCmd = new OracleCommand
{
Connection = con,
CommandText = "Tables.promotion_average_credit",
CommandType = CommandType.StoredProcedure
};
var prm = new OracleParameter
{
Direction = ParameterDirection.ReturnValue,
DbType = DbType.Int64
};
objCmd.Parameters.Add(prm);
objCmd.ExecuteNonQuery();
return(Convert.ToInt32(objCmd.Parameters[0].Value));
}
public List <Rank> getRanks()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT RANKS.* FROM RANKS"
};
var ranks = new List <Rank>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
ranks.Add(new Rank()
{
RankID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("RANKID"))),
RankDescription = reader.GetString(reader.GetOrdinal("RANKDESCRIPTION")),
RankSalary = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("RANKSALARY")))
});
}
return(ranks);
}
//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 List <ToppingType> getToppingTypes()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT TOPPINGTYPE.* FROM TOPPINGTYPE"
};
var toppingTypes = new List <ToppingType>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
toppingTypes.Add(new ToppingType()
{
TopTypeID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("TOPTYPEID"))),
TopTypeName = reader.GetString(reader.GetOrdinal("TOPTYPENAME")),
TopTypeType = reader.GetString(reader.GetOrdinal("TOPTYPETYPE"))
});
}
return(toppingTypes);
}
public List <PizzaBase> getPizzaBases()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT PIZZABASE.* FROM PIZZABASE"
};
var pizzaBases = new List <PizzaBase>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
pizzaBases.Add(new PizzaBase
{
BaseID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("BASEID"))),
PbsID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("PBSID"))),
Dough = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("DOUGH")))
});
}
return(pizzaBases);
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The datatable schema to add.</param>
/// <param name="queryText">The query 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>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, DataTable[] tables, string queryText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
OracleClient.OracleConnection orlConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, queryText), orlConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
dataSet = new System.Data.DataSet();
dataSet.Tables.AddRange(tables);
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
orlConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
/// <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();
}
}
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataTable">The data table to return containing the data.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="getSchemaTable">Get the table schema from the database and then load the data. Used when
/// returning data from the database for a particilar table.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref DataTable dataTable, string queryText, CommandType commandType,
string connectionString, bool getSchemaTable, params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
OracleClient.OracleConnection orlConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, queryText), orlConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
// Get the schema from the data because the
// table has not predefined schema
if (getSchemaTable)
{
// Load the table after the schema is
// returned.
dataTable = dataReader.GetSchemaTable();
dataTable = new DataTable();
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
else
{
// Load the data into a table schema.
// Load the data into a table schema.
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
dataReader.Close();
}
// Close the database connection.
orlConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
/// <summary>
/// Executes the query.
/// </summary>
/// <typeparam name="TypeDataSet">The data type to examine.</typeparam>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The data tables names to return.</param>
/// <param name="queryText">The query 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>The sql command containing any return values.</returns>
public DbCommand ExecuteClientQuery <TypeDataSet>(ref TypeDataSet dataSet, string[] tables,
string queryText, CommandType commandType, string connectionString, params DbParameter[] values)
where TypeDataSet : System.Data.DataSet, new()
{
// Initial connection objects.
OracleClient.OracleCommand sqlCommand = null;
OracleClient.OracleConnection connection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (connection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
connection.Open();
// Create the command and assign any parameters.
sqlCommand = new OracleClient.OracleCommand(queryText, connection);
sqlCommand.CommandType = commandType;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
sqlCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = sqlCommand.ExecuteReader())
{
dataSet = new TypeDataSet();
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
connection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(sqlCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
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 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);
}
//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);
}
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[] 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);
}
/// <summary>
/// Connexion à la base de données
/// </summary>
/// <returns></returns>
public bool DataBasisConnection()
{
try
{
m_connection = new OracleConnection("data source=" + m_strServer + ";user id=" + m_strLogin + ";password="******";pooling=false");
m_connection.Open();
}
catch (Exception error)
{
MessageBox.Show("Data basis connection error : " + error.Message);
return(false);
}
if (m_connection.State != System.Data.ConnectionState.Open)
{
MessageBox.Show("Data basis connection error.");
return(false);
}
//récupère info sur l'utilisateur connecté
OracleCommand command = new OracleCommand();
command.Connection = m_connection;
OracleDataReader sqlReader = null;
string sql = "SELECT id_user_ ,user_ ,user_oracle ,service,commentary,dba_status " +
"FROM MOU01.my_user WHERE user_oracle='" + m_strLogin.ToUpper() + "'";
try
{
command.CommandText = sql;
sqlReader = command.ExecuteReader();
if (sqlReader.Read())
{
m_connectedUser = new User();
m_connectedUser.Id = sqlReader.GetInt64(0);
m_connectedUser.Name = sqlReader.GetString(1);
m_connectedUser.UserOracle = sqlReader.GetString(2);
m_connectedUser.Service = sqlReader.GetString(3);
if (!sqlReader.IsDBNull(4))
{
m_connectedUser.Commentary = sqlReader.GetString(4);
}
if (!sqlReader.IsDBNull(5))
{
m_connectedUser.Admin = sqlReader.GetInt16(5);
}
else
{
m_connectedUser.Admin = AccountMgmt.Common.Constants.NoAdminLevel;
}
}
else
{
MessageBox.Show("L'utilisateur correspondant n'existe pas en base, veuillez en sélectionner un autre ou appeler le responsable base de données.");
return(false);
}
}
catch (Exception error)
{
if (error.Message.StartsWith("ORA-00942"))
{
MessageBox.Show("Vous n'avez pas les droits suffisants pour lancer cette application. Si ce n'est pas normal, veuillez contacter le responsable base de données.",
"Droits insuffisants", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
}
m_bConnected = true;
return(true);
}
public void BulkToDB(DataTable dt, string targetTable)
{
this.Invoke(new Action(() =>
{
richTextBox4.AppendText(DateTime.Now.ToString() + "--开始插入查询结果... ");
}));
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connstr);
//System.Data.OracleClient.OracleConnection conn = new OracleConnection(conStr);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OracleBulkCopy bulkCopy = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default);
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.BulkCopyTimeout = 260;
bulkCopy.DestinationTableName = targetTable;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
// conn.Open();
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
}
else
{
this.Invoke(new Action(() =>
{
richTextBox4.AppendText("没有可以插入的记录 ");
}));
}
this.Invoke(new Action(() =>
{
richTextBox4.AppendText("插入记录数:" + dt.Rows.Count.ToString() + " ");
// richTextBox3.AppendText(DateTime.Now.ToString() + "--查询报表中... ");
richTextBox4.Focus();
richTextBox4.AppendText(DateTime.Now.ToString() + "--插入完成... ");
richTextBox4.Focus();
}));
Task.Run(() => ExpFromorcl());
}
catch (Exception ex)
{
this.Invoke(new Action(() =>
{
richTextBox4.AppendText(DateTime.Now.ToString() + "--插入报错" + ex.ToString() + " ");
richTextBox4.Focus();
}));
}
finally
{
conn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}