/// <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; } } }