/// <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();
}
}
}
/// <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();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = "User ID=SYSTEM; Password=19; Data Source=PC";
try
{
conn.Open();
MessageBox.Show("Veritabanına Baglantı Basarılı.");
}
catch
{
MessageBox.Show("Veritabanına Baglanılamadı.");
}
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "SELECT ogrenci_tablosu.ogrenci_ad, fakulte_tablosu.fakulte_ad FROM ogrenci_tablosu, bolum_tablosu, fakulte_tablosu WHERE ogrenci_tablosu.ogrenci_bolum = bolum_tablosu.bolum_id AND bolum_tablosu.bolum_fakulte = fakulte_tablosu.fakulte_id";
cmd.Connection = conn;
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = cmd;
DataSet dataset = new DataSet();
adapter.Fill(dataset);
dataGridView1.DataSource = dataset.Tables[0];
conn.Close();
}
public void Desconectar()
{
if (ora.State == System.Data.ConnectionState.Open)
{
ora.Close();
}
}
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();
}
}
}
}
/// <summary>
/// Writes the customer to the database.
/// </summary>
public int CallWriteCustomer(int firstValue, int secondValue)
{
// create Oracle connection
var con = new OracleConnection("User Id=pts22;Password=pts22;Data Source=127.0.0.1:1521/xe;");
var comm = new OracleCommand(); // create command
int retVal = -1;
try
{
con.Open(); // open connection
comm.Connection = con; // add connection to command
comm.CommandText = "WRITE_CUSTOMER"; // name of the Oracle procedure
comm.CommandType = CommandType.StoredProcedure; // set type to stored procedure
// add input parameters
comm.Parameters.Add("ReturnValue", OracleDbType.Int16, ParameterDirection.ReturnValue);
comm.Parameters.Add(new OracleParameter("USERID", firstValue));
comm.Parameters.Add(new OracleParameter("RESERVATIONSID", secondValue));
comm.ExecuteNonQuery(); // execute procedure
retVal = int.Parse(comm.Parameters["ReturnValue"].Value.ToString()); // read return value
}
finally
{
con.Close();
}
return retVal;
}
private bool ColumnIsBoolean(string nameOwner, string nameTable, string nameColumn)
{
List <string> listIndicateColumns = new List <string>();
string strSQL = DistinctsValuesQuery(nameOwner, nameTable, nameColumn);
Oracle.DataAccess.Client.OracleConnection conn = DBConnectionDTOP();
conn.Open();
try
{
OracleCommand cmd = new OracleCommand(strSQL, conn);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
listIndicateColumns.Add(dr[0].ToString());
}
if (listIndicateColumns.Count <= 2 && (listIndicateColumns.Contains("S") || listIndicateColumns.Contains("N")))
{
return(true);
}
return(false);
}
finally { conn.Close(); }
}
public DataSet GetProdOCRData(string queryName,string startDate, string endDate)
{
string queryPath = string.Format(@"{0}\Query\{1}", Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location),queryName);
string query = File.ReadAllText(queryPath);
query = string.Format(query, startDate, endDate);
DataSet ds = new DataSet();
using (oraconnection = new OracleConnection(ConfigurationManager.AppSettings["DSN_PNRP"]))
{
try
{
oraconnection.Open();
oracommand = new OracleCommand(query, oraconnection);
oracommand.CommandType = CommandType.Text;
OracleDataAdapter adp = new OracleDataAdapter(oracommand);
ds = new DataSet();
adp.Fill(ds);
}
catch (Exception)
{
}
finally
{
oraconnection.Close();
}
return ds;
}
}
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 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 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 static IEnumerable<Model.Post> Selecionar()
{
using (var c = new OracleConnection(Helper.Oracle.CS))
{
const String cmd = "SELECT P.*, C.DESCRICAO_CATEGORIA FROM BLOG.POST P INNER JOIN BLOG.POST_CATEGORIA PC ON P.CODIGO_POST = PC.CODIGO_POST INNER JOIN BLOG.CATEGORIA C ON C.CODIGO_CATEGORIA = PC.CODIGO_CATEGORIA WHERE C.DATA_EXCLUSAO_CATEGORIA IS NULL AND P.DATA_LIBERACAO_POST <= SYSDATE";
using (var k = new OracleCommand(cmd, c))
{
c.Open();
var dr = k.ExecuteReader();
Model.Post post = null;
while (dr.Read())
{
if (post != null && post.Codigo != Convert.ToInt32(dr[0]))
yield return post;
if (post == null || post.Codigo != Convert.ToInt32(dr[0]))
post = new Model.Post(Convert.ToInt32(dr[0]), dr[1].ToString(), dr[2].ToString(), Convert.ToDateTime(dr[3]), Convert.ToDateTime(dr[4]), Convert.ToInt32(dr[5]), Convert.ToChar(dr[6]), new List<Model.Categoria>());
post.Categorias.Add(new Model.Categoria(descricao: dr[7].ToString()));
}
if (post != null)
yield return post;
c.Close();
}
}
}
public static void Inserir(Model.Post obj)
{
using (var c = new OracleConnection(Helper.Oracle.CS))
{
const String cmd = "INSERT INTO BLOG.POST (TITULO_POST, CONTEUDO_POST, DATA_INCLUSAO_POST, DATA_LIBERACAO_POST, CODIGO_USUARIO, PRIVADO_POST) VALUES (:TITULO_POST, :CONTEUDO_POST, :DATA_INCLUSAO_POST, :DATA_LIBERACAO_POST, :CODIGO_USUARIO, :PRIVADO_POST)";
using (var k = new OracleCommand(cmd, c))
{
k.Parameters.Add(":TITULO_POST", obj.Titulo);
k.Parameters.Add(":CONTEUDO_POST", obj.Conteudo);
k.Parameters.Add(":DATA_INCLUSAO_POST", DateTime.Now);
k.Parameters.Add(":DATA_LIBERACAO_POST", obj.DataLiberacao);
k.Parameters.Add(":CODIGO_USUARIO", obj.CodigoUsuario);
k.Parameters.Add(":PRIVADO_POST", obj.Privado);
c.Open();
k.ExecuteNonQuery();
var codigo = ObterCodigoPost(c);
foreach (var item in obj.Categorias)
{
InserirPostCategoria(codigo, item.Codigo, c);
}
c.Close();
}
}
}
public static bool DeleteAvailability(int userId, string day, string period)
{
Nullable<int> foundID = null;//zoek naar bestaan van entry
DataTable tdb = RetrieveQuery("SELECT \"ID\" FROM \"Availability\" WHERE \"Day\" = " + day + ", \"Period\" = " + period);
foreach (DataRow r in tdb.Rows)
{
foundID = Convert.ToInt32(r["ID"]);//haal id van bestaande entry op, zo niet dan null
}
if (foundID == null) return false;
using (OracleConnection c = new OracleConnection(@connectionstring))
{
c.Open();
OracleCommand cmd = new OracleCommand("DELETE \"Availability_Acc\" WHERE \"ACC_ID\" = :a AND \"AVAILABILITY_ID\" = " + foundID);
cmd.Parameters.Add(new OracleParameter("a", userId));
cmd.Connection = c;
try
{
cmd.ExecuteNonQuery();
}
catch (OracleException e)
{
Console.WriteLine(e.Message);
return false;
}
c.Close();
return true;
}
}
/// <summary>
/// 将数据读取到 DataSet 中.
/// </summary>
public void ReadDataToDataSet()
{
Console.WriteLine("使用DataAdapter，将数据填充到DataSet中，然后脱离数据库，直接对DataSet进行处理。");
// 建立数据库连接.
OracleConnection conn = new OracleConnection(connString);
// 创建一个适配器
OracleDataAdapter adapter = new OracleDataAdapter(SQL, conn);
// 创建DataSet，用于存储数据.
DataSet testDataSet = new DataSet();
// 执行查询，并将数据导入DataSet.
adapter.Fill(testDataSet, "result_data");
// 关闭数据库连接.
conn.Close();
// 处理DataSet中的每一行数据.
foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("NO:{0} ; Date:{1} ; Money:{2} ",
testRow["NO"], testRow["SALE_DATE"], testRow["SUM_MONEY"]
);
}
}
/// <summary>
/// Selects and retrieves values from the database
/// </summary>
/// <param name="query">The selection statement</param>
/// <returns>A DataTable with the retrieved values></returns>
public static DataTable RetrieveQuery(string query)
{
using (OracleConnection c = new OracleConnection(@connectionstring))
{
try
{
c.Open();
OracleCommand cmd = new OracleCommand(@query);
cmd.Connection = c;
try
{
OracleDataReader r = cmd.ExecuteReader();
DataTable result = new DataTable();
result.Load(r);
c.Close();
return result;
}
catch (OracleException e)
{
Console.Write(e.Message);
throw;
}
}
catch (OracleException e)
{
Console.Write(e.Message);
return new DataTable();
}
}
}
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 static void CloseCon(OracleConnection con)
{
con.Close();
con.Dispose();
GC.Collect();
}
static void Main()
{
const String cs = @"DATA SOURCE=127.0.0.1/ORCL;DBA PRIVILEGE=SYSDBA;USER ID=SYS;PASSWORD=queC0isa;";
var c = new OracleConnection { ConnectionString = cs };
c.StateChange += CStateChange;
c.Disposed += CDisposed;
c.Open();
Thread.Sleep(2000);
c.Close();
//tentativa de liberar o objeto da memória
//c.Dispose();
//ou
//c = null;
//GC.Collect();
Console.ReadKey();
}
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 object DLookUp(String column, string table, string condition)
{
OracleConnection objConexion = default(OracleConnection);
OracleDataAdapter objComando = default(OracleDataAdapter);
DataSet requestquery = new DataSet();
object result = null;
objConexion = new OracleConnection(driver);
objConexion.Open();
if (string.IsNullOrEmpty(condition))
{
objComando = new OracleDataAdapter("Select " + column + " from " + table, objConexion);
}
else
{
objComando = new OracleDataAdapter("Select " + column + " from " + table + " where " + condition, objConexion);
}
objComando.Fill(requestquery);
try
{
result = requestquery.Tables[0].Rows[0][requestquery.Tables[0].Columns.IndexOf(column)];
}
catch (Exception ex)
{
result = -1;
}
objConexion.Close();
return result;
}
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();
}
}
//Opens the Connection
private static void OpenDbConnection()
{
try
{
if (_OdacConn == null)
{
_OdacConn = new Oracle.DataAccess.Client.OracleConnection();
}
//Just Force the ConnectionString
_OdacConn.ConnectionString = OdacConnectionString;
if (_OdacConn.State != System.Data.ConnectionState.Closed)
{
_OdacConn.Close();
}
int connAttempts = 0;
do
{
connAttempts++;
_OdacConn.Open();
}while (_OdacConn.State != System.Data.ConnectionState.Open && connAttempts < MAX_DB_CONNECT_ATTEMPTS);
}
catch (Exception ex)
{
LogToFileServer("Server", "ERR: " + ex.Message + " TRACE: " + ex.StackTrace);
}
}
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 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);
}
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();
}
}
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;
}
static void Main(string[] args)
{
Oracle.DataAccess.Client.OracleConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
// Tạo một đối tượng Command gọi thủ tục Get_Employee_Info.
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("Get_Employee_Info", conn);
// Kiểu của Command là StoredProcedure
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// Thêm tham số @p_Emp_Id và sét giá trị của nó = 100.
cmd.Parameters.Add("@p_Emp_Id", Oracle.DataAccess.Client.OracleDbType.Int32).Value = 100;
// Thêm tham số @v_Emp_No kiểu Varchar(20).
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("@v_Emp_No", Oracle.DataAccess.Client.OracleDbType.Varchar2, 20));
cmd.Parameters.Add(new OracleParameter("@v_First_Name", Oracle.DataAccess.Client.OracleDbType.Varchar2, 50));
cmd.Parameters.Add(new OracleParameter("@v_Last_Name", Oracle.DataAccess.Client.OracleDbType.Varchar2, 50));
cmd.Parameters.Add(new OracleParameter("@v_Hire_Date", OracleDbType.Date));
// Đăng ký tham số @v_Emp_No là OUTPUT.
cmd.Parameters["@v_Emp_No"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@v_First_Name"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@v_Last_Name"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["@v_Hire_Date"].Direction = System.Data.ParameterDirection.Output;
// Thực thi thủ tục.
cmd.ExecuteNonQuery();
// Lấy các giá trị đầu ra.
string empNo = cmd.Parameters["@v_Emp_No"].Value.ToString();
string firstName = cmd.Parameters["@v_First_Name"].Value.ToString();
string lastName = cmd.Parameters["@v_Last_Name"].Value.ToString();
object hireDateObj = cmd.Parameters["@v_Hire_Date"].Value;
Console.WriteLine("hireDateObj type: " + hireDateObj.GetType().ToString());
Oracle.DataAccess.Types.OracleDate hireDate = (Oracle.DataAccess.Types.OracleDate)hireDateObj;
Console.WriteLine("Emp No: " + empNo);
Console.WriteLine("First Name: " + firstName);
Console.WriteLine("Last Name: " + lastName);
Console.WriteLine("Hire Date: " + hireDate);
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
Console.WriteLine(e.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
Console.Read();
}
private string SaveToDatabase(byte[] btReport, string id)
{
// Write to J Drive
string filepath = "";
string timestamp = DateTime.Now.ToString("MMddHHmmssfff");
string filename = "fax" + id + "_" + timestamp + ".pdf";
string credentials = Data.getJCredential();
String[] credentialsArray = credentials.Split("|".ToCharArray());
string COMPUTER_IP = credentialsArray[0].ToString().Split(':')[1];
string DOMAIN = credentialsArray[1].ToString().Split(':')[1];
string USER_NAME = credentialsArray[2].ToString().Split(':')[1];
string PASSWORD = credentialsArray[3].ToString().Split(':')[1];
try
{
using (NetworkShareAccesser.Access(COMPUTER_IP, DOMAIN, USER_NAME, PASSWORD))
{
filepath = "\\\\" + COMPUTER_IP + "\\j_drive\\Clients\\LifeEvent\\" + filename;
File.WriteAllBytes(filepath, btReport);
filepath = filepath.Replace("\\\\" + COMPUTER_IP + "\\j_drive\\", "J:\\");
}
}
catch
{
}
// Save to Database
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
try
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Enrollment_Wizard_LE.SaveFaxImage", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", Request.Params["DpNo"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", SQLStatic.Sessions.GetUserName(HttpContext.Current.Request.Cookies["Session_ID"].Value.ToString()));
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
finally
{
conn.Close();
conn.Dispose();
}
return(filename);
}
private static void GetFile(string person_id, char type, System.Web.HttpResponse Response)
{
string cmd = "";
switch (type)
{
case 'P':
cmd = "research_dir_user.profile_views.get_profile_picture";
break;
case 'C':
cmd = "research_dir_user.profile_views.get_profile_cv";
break;
default:
Response.Write("Error:type not defined");
return;
}
using (Oracle.DataAccess.Client.OracleConnection orCN = HealthIS.Apps.Util.getDBConnection())
{
orCN.Open();
OracleCommand orCmd = new OracleCommand(cmd, orCN);
orCmd.CommandType = System.Data.CommandType.StoredProcedure;
orCmd.Parameters.Add("p_person_id", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.Input;
orCmd.Parameters["p_person_id"].Value = person_id;
orCmd.Parameters.Add("r_cur", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
OracleDataAdapter adapt = new OracleDataAdapter(orCmd);
System.Data.DataSet orDS = new System.Data.DataSet();
orCmd.ExecuteNonQuery();
adapt.Fill(orDS);
if (orDS.Tables[0].Rows.Count > 0)
{
System.Data.DataRow dr = orDS.Tables[0].Rows[0];
byte[] barray = (byte[])dr["file_binary"];
Response.ContentType = (String)dr["file_mimetype"];
Response.AddHeader("Content-Disposition", "attachment; filename=" + person_id + "." + dr["file_ext"].ToString() + ";");
Response.AddHeader("Content-Length", barray.Length.ToString());
Response.OutputStream.Write(barray, 0, barray.Length);
}
else
{
Response.Write("Error, no file found for person_id '" + person_id + "'");
}
orDS.Dispose();
adapt.Dispose();
orCmd.Dispose();
orCN.Close();
orCN.Dispose();
}
}
public static void CloseConnection(OracleConnection connect)
{
if (connect == null) return;
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
connect.Dispose();
}
public DataSet getData(string query, string table)
{
OracleConnection objConexion;
OracleDataAdapter objComando;
DataSet requestquery = new DataSet();
objConexion = new OracleConnection(driver);
objConexion.Open();
objComando = new OracleDataAdapter(query, objConexion);
objComando.Fill(requestquery, table);
objConexion.Close();
return requestquery;
}
public static DataTable GetDataTable(string sqlString)
{
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
DataSet dataset = new DataSet();
try
{
dataset = ExecuteDataset(conn, CommandType.Text, sqlString, (OracleParameter[])null);
}
catch (Oracle.DataAccess.Client.OracleException e)
{
conn.Close();
throw new Exception(e.Message);
}
finally
{
conn.Close();
}
return dataset.Tables[0];
}
}
public override void excute()
{
this.ResultDataSet_ = new DataSet();
OracleConnection conn = new OracleConnection();
conn.ConnectionString = this.ConnectionString_;
try
{
OracleCommand sqlCommand = new OracleCommand();
conn.Open();
// ------------------------------------------ Position --------------------------------------------
//sqlCommand.CommandText = "SELECT STD_DATE, SECTOR_ID , STOCK_ID , STOCK_NAME, CURR_PRICE, " +
// " ADJ_STOCK_PRICE, MIN_PRICE, MAX_PRICE, DIVIDEND_RATE " +
// " FROM TD_MRSTOCKFACTOR " +
// " WHERE (SUBSTR(STOCK_ID, 1, 3) = 'KR7') " +
// " AND STD_DATE = '"++"' AND STD_DATE = '20130909' " +
// " AND (SECTOR_ID = 'KOSPI200')";
foreach (var item in QueryList_)
{
sqlCommand.CommandText = item.Value;
OracleDataAdapter dataAdapter = new OracleDataAdapter(sqlCommand.CommandText, conn);
ResultDataSet_.Tables.Add(item.Key);
dataAdapter.Fill(ResultDataSet_.Tables[item.Key]);
}
conn.Close();
}
catch (OracleException e)
{
conn.Close();
}
}
protected void ingresarTarea_Click(object sender, EventArgs e)
{
try
{
lblidddl.Text = "";
//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 = txtCorreo.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.Text = id.ToString();
ora.Close();
//Para la Tarea
string tipo = ddlTipoTarea.SelectedItem.ToString();
lblidddl.Text = tipo;
int flujo = ddlFlujo.SelectedIndex + 1;
//FECHA
var date = fecha.SelectedDate;
ora2.Open();
System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_TAREA", ora2);
comando2.CommandType = System.Data.CommandType.StoredProcedure;
comando2.Parameters.Add("P_NOMBRE", OracleType.VarChar).Value = txtNombre.Text;
comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtDescripcion.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_FLUJO", OracleType.VarChar).Value = flujo;
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>");
txtNombre.Text = "";
txtDescripcion.Text = "";
// txtCorreo.Text = "";
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>");
}
}
public void FecharBanco(OracleConnection conn)
{
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
catch (Exception e)
{
throw e;
}
}
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 virtual void CloseDb(OrlDb db)
{
if (!db.IsClosed)
{
if (!db.IsAdapter && db.Connection != null)
{
Oracle.DataAccess.Client.OracleConnection cn = (Oracle.DataAccess.Client.OracleConnection)db.Connection;
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
cn.Dispose();
db.Connection = 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(); }
}
public static void SaveArray(int intCount, string[] strRecordID, string[] strFieldName, string[] strRate, string[] strUserName)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(" pkg_automated_rate_update.saverate", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ArrayBindCount = intCount;
OracleParameter paramRecordID = new OracleParameter("record_id_", OracleDbType.Varchar2);
paramRecordID.Direction = ParameterDirection.Input;
paramRecordID.Value = strRecordID;
cmd.Parameters.Add(paramRecordID);
OracleParameter paramFieldName = new OracleParameter("fieldname_", OracleDbType.Varchar2);
paramFieldName.Direction = ParameterDirection.Input;
paramFieldName.Value = strFieldName;
cmd.Parameters.Add(paramFieldName);
OracleParameter paramRates = new OracleParameter("rates_", OracleDbType.Varchar2);
paramRates.Direction = ParameterDirection.Input;
paramRates.Value = strRate;
cmd.Parameters.Add(paramRates);
OracleParameter paramUserName = new OracleParameter("user_name_", OracleDbType.Varchar2);
paramUserName.Direction = ParameterDirection.Input;
paramUserName.Value = strUserName;
cmd.Parameters.Add(paramUserName);
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
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(); }
}
private void SaveToDatabase2(byte[] btReport)
{
// SQLStatic.SQL.ExecScaler("select max(record_id) from ee_dependent_verify_docs v where v.dep_pending_id = " + Request.Params["DpNo"]).ToString();
//string reccord_id = Data.newFaxDocRecordID(Request.Params["DpNo"]);
//if (reccord_id.Equals("-1"))
// return;
//SQLStatic.Sessions.SetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "reprt", reccord_id);
//SQLStatic.Sessions.SetBLOBSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "reprt", btReport);
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
try
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Dependents_Audit.SaveFaxImage", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", Request.Params["DpNo"]);
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
finally
{
conn.Close();
conn.Dispose();
}
}
//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);
}
protected void Button1_Click(object sender, EventArgs e)
{
string connStr = "Data Source=OIC;User Id=AGDOI;Password=password;";
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connStr);
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
conn.Open();
Oracle.DataAccess.Client.OracleCommand comm = new Oracle.DataAccess.Client.OracleCommand();
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.CommandText = "AG_GET_PETNAME"; // "AG_TESTING_RUNNING";
comm.Connection = conn;
//comm.BindByName = true;
//comm.CommandType = System.Data.CommandType.Text;
//comm.CommandText = "Select * From ag_app_running_no_t;";
//comm.Connection = conn;
//comm.Parameters.Add("I_Date", OracleDbType.Date).Value = DateTime.Now.Date;
//comm.Parameters.Add("i_license_type", OracleDbType.Varchar2).Value = "";
//comm.Parameters.Add("I_Exam_Status", OracleDbType.Varchar2).Value = "";
//comm.Parameters.Add("V_Year", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.ReturnValue;
//comm.Parameters.Add("V_Last_No", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.ReturnValue;
//OracleDataAdapter da = new OracleDataAdapter(comm);
//System.Data.DataTable dt = new System.Data.DataTable();
//da.Fill(dt);
comm.Parameters.Add("I_Code", OracleDbType.Varchar2, 2, "11", System.Data.ParameterDirection.Input);
comm.Parameters.Add("V_Name", OracleDbType.Varchar2, 120).Direction = System.Data.ParameterDirection.Output;
comm.ExecuteNonQuery();
//string vName = comm.Parameters["V_Name"].Value.ToString();
//string vYear = comm.Parameters["V_Year"].Value.ToString();
//string vLastNo = comm.Parameters["V_Last_No"].Value.ToString();
//Response.Write(string.Format("vYear = {0}, vLastNo = {1}", vYear, vLastNo));
//Oracle.DataAccess.Client.OracleParameter p1 = new Oracle.DataAccess.Client.OracleParameter("I_Date", OracleDbType.Date);
//p1.Value = DateTime.Now.Date;
//Oracle.DataAccess.Client.OracleParameter p2 = new Oracle.DataAccess.Client.OracleParameter("I_License_Type", OracleDbType.Varchar2);
//p2.Value = string.Empty;
//Oracle.DataAccess.Client.OracleParameter p3 = new Oracle.DataAccess.Client.OracleParameter("I_Exam_Status", OracleDbType.Varchar2);
//p3.Value = string.Empty;
//comm.Parameters.Add(p1);
//comm.Parameters.Add(p2);
//comm.Parameters.Add(p3);
//Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(comm);
//System.Data.DataSet ds = new System.Data.DataSet();
//da.Fill(ds);
}
/// <summary>
/// Déconnexion à la base de données
/// </summary>
public void DataBasisDisconnection()
{
m_connection.Close();
}
public static void SaveCBArray(int intCount, string[] account_number, string[] processing_year, string[] in_open_enrollment, string[] category_code,
string[] categoty_plan, string[] class_code, string[] batch_id)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(" pkg_automated_rate_update.Save_In_OE", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ArrayBindCount = intCount;
OracleParameter parmaAccountNumber = new OracleParameter("account_number_", OracleDbType.Varchar2);
parmaAccountNumber.Direction = ParameterDirection.Input;
parmaAccountNumber.Value = account_number;
cmd.Parameters.Add(parmaAccountNumber);
OracleParameter paramprocessing_year = new OracleParameter("processing_year_", OracleDbType.Varchar2);
paramprocessing_year.Direction = ParameterDirection.Input;
paramprocessing_year.Value = processing_year;
cmd.Parameters.Add(paramprocessing_year);
OracleParameter paramRates = new OracleParameter("in_open_enrollment_", OracleDbType.Varchar2);
paramRates.Direction = ParameterDirection.Input;
paramRates.Value = in_open_enrollment;
cmd.Parameters.Add(paramRates);
OracleParameter paramcategory_code = new OracleParameter("category_code_", OracleDbType.Varchar2);
paramcategory_code.Direction = ParameterDirection.Input;
paramcategory_code.Value = category_code;
cmd.Parameters.Add(paramcategory_code);
OracleParameter parmcategoty_plan = new OracleParameter("categoty_plan_", OracleDbType.Varchar2);
parmcategoty_plan.Direction = ParameterDirection.Input;
parmcategoty_plan.Value = categoty_plan;
cmd.Parameters.Add(parmcategoty_plan);
OracleParameter paramclass_code = new OracleParameter("class_code_", OracleDbType.Varchar2);
paramclass_code.Direction = ParameterDirection.Input;
paramclass_code.Value = class_code;
cmd.Parameters.Add(paramclass_code);
OracleParameter parambatch_id = new OracleParameter("batch_id_", OracleDbType.Varchar2);
parambatch_id.Direction = ParameterDirection.Input;
parambatch_id.Value = batch_id;
cmd.Parameters.Add(parambatch_id);
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
OracleClient.OracleConnection orlConnection = null;
OracleClient.OracleTransaction orlTransaction = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Start a new transaction.
orlTransaction = orlConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, commandText), orlConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = orlTransaction;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
orlTransaction.Commit();
// Close the database connection.
orlConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (orlTransaction != null)
{
orlTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
public 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);
}
private void SaveToDatabase(byte[] btReport)
{
// SQLStatic.SQL.ExecScaler("select max(record_id) from ee_dependent_verify_docs v where v.dep_pending_id = " + Request.Params["DpNo"]).ToString();
string reccord_id = Data.newFaxDocRecordID(Request.Params["DpNo"]);
if (reccord_id.Equals("-1"))
{
return;
}
SQLStatic.Sessions.SetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "reprt", reccord_id);
SQLStatic.Sessions.SetBLOBSessionValue(Request.Cookies["Session_ID"].Value.ToString(), ViewState["EE"].ToString() + " " + Request.Params["DpNo"], btReport);
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
try
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Dependents_Audit.SaveFaxImage", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", reccord_id);
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
finally
{
conn.Close();
conn.Dispose();
}
// Here you save bytes to the database
//using (OracleConnection con = new OracleConnection(ConnStr))
//{
// using (OracleCommand cmd = new OracleCommand("PKG_Name.Procedure_Save_PDF", con))
// {
// con.Open();
// try
// {
// foreach (ReportParameters p in parameters)
// {
// try
// {
// // GenerateReportByteArray will return byte array for the database
// cmd.Parameters.Add("file_blob_", OracleDbType.Blob, ParameterDirection.Input);
// cmd.Parameters["file_blob_"].Value = btReport;
// cmd.ExecuteNonQuery();
// }
// catch (Exception ex)
// {
// Message = ex.Message;
// }
// }
// Message = "Insert done";
// }
// catch (Exception ex)
// {
// Message = ex.Message;
// }
// finally
// {
// if (con.State != ConnectionState.Closed) con.Close();
// }
//}
//}
}
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 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);
}
private void btnCreateSheets_Click(object sender, EventArgs e)
{
if (cboOwner.SelectedIndex == -1)
{
MessageBox.Show("Please, select a owner for continue.");
return;
}
string domainPathSource = "C:\\ExcelSheets\\TEMP\\" + cboOwner.SelectedValue.ToString();
string strSQL = "";
int count = 0;
strSQL = TablesForOwnerQuery(cboOwner.SelectedValue.ToString());
Oracle.DataAccess.Client.OracleConnection conn = DBConnectionDTOP();
conn.Open();
try
{
OracleCommand cmd = new OracleCommand(strSQL, conn);
OracleDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
int totalLines = dt.Rows.Count;
dt.Dispose();
progressBar2.Value = 0; // Esse é o valor da progress bar ela vai de 0 a Maximum (padrão 100)
progressBar2.Maximum = totalLines;
dr.Close();
dr = cmd.ExecuteReader();
while (dr.Read())
{
string domainPathTarget = "C:\\ExcelSheets\\TEMP\\" + cboOwner.SelectedValue.ToString() + "\\" + dr[2].ToString();
if (!System.IO.Directory.Exists(domainPathTarget))
{
System.IO.Directory.CreateDirectory(domainPathTarget);
}
if (System.IO.File.Exists(domainPathSource + "\\" + dr[1].ToString() + ".xls"))
{
System.IO.File.Move(domainPathSource + "\\" + dr[1].ToString() + ".xls", domainPathTarget + "\\" + dr[1].ToString() + ".xls");
}
else
{
label2.Text = "Exportando tabela: " + cboOwner.SelectedValue.ToString() + "." + dr[1].ToString() + " para planilha: " + domainPathTarget + "\\" + dr[1].ToString() + ".xls";
label2.Visible = true;
strSQL = MakeDomainsQuery(cboOwner.SelectedValue.ToString(), dr[1].ToString());
SQLToCSV(cboOwner.SelectedValue.ToString(), strSQL, dr[1].ToString(), domainPathTarget);
}
count++;
progressBar2.Value = count;
progressBar2.Visible = true;
label3.Text = "Exportada: " + count.ToString() + " de " + totalLines + " tabelas do Owner " + cboOwner.SelectedValue.ToString();
label3.Visible = true;
}
label3.Text = "Exportação realizada com sucesso para : " + count.ToString() + " tabelas do Owner " + cboOwner.SelectedValue.ToString();
}
finally { conn.Close(); conn.Dispose(); }
}
/// <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();
}
}
}
/// <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();
}
}
}
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();
}
}
}
/// <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();
}
}
}
public void Close()
{
OracleTransaction1 = null;
OracleConnection1.Close();
OracleConnection1 = null;
}
//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 static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
try
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
OracleDataAdapter MyAdapter = new OracleDataAdapter();
MyAdapter.SelectCommand = cmd;
DataSet ds = new DataSet();
MyAdapter.Fill(ds);
cmd.Parameters.Clear();
DataTable table = ds.Tables[0];
ds.Dispose();
connection.Close();
return table;
}
catch
{
connection.Close();
throw;
}
}
}
