public void RawQuery(String Query) { OracleConnection conn = new OracleConnection(); conn.ConnectionString = "User Id=SYSTEM;Password=oracle"; OracleDataReader rdr; try { conn.Open(); //OracleCommand cmd = new OracleCommand("select * from employee", conn); OracleCommand cmd = new OracleCommand(Query, conn); rdr = cmd.ExecuteReader(); int rdr_size = (int)rdr.VisibleFieldCount; int n = 0; while (rdr.Read()) { n = 0; while (n < rdr_size) { Console.Write("{0,-15} ",rdr[n]); n++; } Console.WriteLine(); } conn.Dispose(); } catch (Exception e) { Console.WriteLine(e); } }
public static void CloseCon(OracleConnection con) { con.Close(); con.Dispose(); GC.Collect(); }
/// <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_1(object sender, EventArgs e) { int a; a = Convert.ToInt32(textBox4.Text); a = int.Parse(textBox4.Text); string oradb = "Data Source=XE;User Id=system;Password=admin;"; OracleConnection conn = new OracleConnection(oradb); conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "update pharm.DRUG set QUANTITY =QUANTITY - " + a + ""; int rowsUpdated = cmd.ExecuteNonQuery(); if (rowsUpdated == 0) MessageBox.Show("Record not inserted"); else MessageBox.Show("Success!"); conn.Dispose(); }
public 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; }
private void buttonupdate_Click(object sender, EventArgs e) { string oradb = "Data Source=XE;User Id=system;Password=admin;"; OracleConnection conn = new OracleConnection(oradb); conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "update pharm.DRUG set D_NAME = '" + updatename.Text + "', MFD = '"+ textBox3.Text +"', EXP= '"+ updateexp.Text + "' , COST ="+ updatecost.Text +", QUANTITY = "+ textBox2.Text + " WHERE DRUG_ID = " + textBox1.Text; int rowsUpdated = cmd.ExecuteNonQuery(); if (rowsUpdated == 0) MessageBox.Show("Record not inserted"); else MessageBox.Show("Success!"); conn.Dispose(); }
private void button1_Click(object sender, EventArgs e) { string oradb = "Data Source=XE;User Id=system;Password=admin;"; OracleConnection conn = new OracleConnection(oradb); conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "Delete from pharm.DRUG where DRUG_ID=" + textBox1.Text + " "; int rowsUpdated = cmd.ExecuteNonQuery(); if (rowsUpdated == 0) MessageBox.Show("Record not deleted"); else MessageBox.Show("Successfully deleted!"); conn.Dispose(); }
public UsAuth getAuthForIdUser(string email, string passwd) { OracleConnection connection = null; OracleDataReader dr = null; UsAuth auth = null; try { using (connection = new OracleConnection(OracleHelper.connectionString())) { using (var command = new OracleCommand("US_AUTH_GETFOR_USER", connection)) { connection.Open(); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("P_CUR_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output; command.Parameters.Add("EMAIL", email); command.Parameters.Add("PASWD", passwd); using (dr = command.ExecuteReader()) { if (dr.HasRows) { auth = new UsAuth() { IdUser = Convert.ToInt32(dr["IDUSER"].ToString()), Email = dr["EMAIL"].ToString(), FirstName = dr["FIRSTNAME"].ToString(), LastName = dr["LASTNAME"].ToString(), Role = new UsRole() { Id = Convert.ToInt32(dr["IDROLE"].ToString()), Name = dr["NAMEROLE"].ToString() }, Photo = dr["PHOTO"].ToString(), ListRoleModule = new DARoleModule().getRoleModuleForRole(Convert.ToInt32(dr["IDROLE"].ToString())), ListRoleModulePrivilege = new DARoleModulePrivilege().getRoleModulePrivilegeForRole(Convert.ToInt32(dr["IDROLE"].ToString())) }; } } } } return auth; } catch (Exception e) { dr.Dispose(); if (connection.State == ConnectionState.Open) connection.Dispose(); LogHelper.WriteLog(e); throw e; } finally { auth = null; } }
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); }
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 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 List<UsRole> getRoleForState(int idState) { OracleConnection connection = null; OracleDataReader dr = null; List<UsRole> list = null; try { using (connection = new OracleConnection(OracleHelper.connectionString())) { using (var command = new OracleCommand("US_ROLE_GETFOR_STATE", connection)) { connection.Open(); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("P_CUR_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output; command.Parameters.Add("ID", idState); using (dr = command.ExecuteReader()) { list = new List<UsRole>(); if (dr.HasRows) { while (dr.Read()) { list.Add( new UsRole() { Id = Convert.ToInt32(dr["ID"].ToString()), Name = dr["NAME"].ToString() } ); } } } } } return list; } catch (Exception e) { dr.Dispose(); if (connection.State == ConnectionState.Open) connection.Dispose(); LogHelper.WriteLog(e); throw e; } finally { list = null; } }
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; } } }
public static Boolean ExecuteListadeSetores( ConnectionInfo pInfo, decimal pSET_ID, string pCHAVE ) { Boolean values; try { string lQuery = ""; DataTable lTable = new DataTable(); OracleCommand cmd = new OracleCommand(); using (OracleConnection cn = new OracleConnection(pInfo.ConnectionString)) { cmd.Connection = cn; cmd.CommandText = "spLista"; cmd.CommandType = CommandType.StoredProcedure; //cmd.CommandText = " BEGIN spLista (" + pSET_ID + ", " + pCHAVE + "); END; "; cmd.Parameters.Add("pSET_ID", OracleDbType.Decimal).Value = pSET_ID; cmd.Parameters.Add("pCHAVE", OracleDbType.Decimal).Value = pCHAVE; cn.Open(); DataSet ds = new DataSet(); OracleDataAdapter Adapter = new OracleDataAdapter(cmd); Adapter.Fill(ds); Adapter.Dispose(); values = true; cmd.Dispose(); cn.Dispose(); cn.Close(); } return values; } catch (Exception ex) { values = false; } return values; }
private void btnConnectNow_Click(object sender, EventArgs e) { var oracleConnectionStringBuilder = OracleConnectionStringBuilder; var oracleConnection = new OracleConnection(); oracleConnection.ConnectionString = oracleConnectionStringBuilder.ConnectionString; try { oracleConnection.Open(); oracleConnection.Dispose(); MessageBox.Show("Connection successful!"); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error connecting to Oracle"); } }
/// <summary> /// 执行一条返回结果集的OracleCommand命令,通过专用的连接字符串。 /// 使用参数数组提供参数 /// </summary> /// <remarks> /// 使用示例: /// DateSet ds = ExecuteDataSet(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">OracleCommand命令类型 (存储过程, T-Oracle语句, 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-Oracle 语句</param> /// <param name="commandParameters">以数组形式提供OracleCommand命令中用到的参数列表</param> /// <returns>返回一个包含结果的DataSet</returns> public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) { OracleCommand cmd = new OracleCommand(); OracleConnection conn = new OracleConnection(connectionString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); OracleDataAdapter adapter = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); return ds; } catch { throw; } finally { cmd.Parameters.Clear(); conn.Dispose(); } }
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(); } }
public OraclePipeListener(string connectionString, string dataBase, string tableName) { m_DateBase = dataBase; m_TableName = tableName; m_Connection = new OracleConnection(connectionString); m_Connection.Open(); m_Command = m_Connection.CreateCommand(); m_Command.CommandText = string.Format("SELECT * FROM {0}.{1} WHERE rownum = 1", DateBase,TableName); m_Dependency = new OracleDependency(m_Command); m_Dependency.OnChange += new OnChangeEventHandler(DependencyChange); m_Command.Notification.IsNotifiedOnce = false; m_Command.AddRowid = true; m_Command.ExecuteNonQuery(); m_Connection.Close(); m_Connection.Dispose(); }
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 bool Fill() { try { conn = new OracleConnection(GetConnectionString()); conn.Open(); transaction = conn.BeginTransaction(); } catch (Exception e) { MessageBox.Show("При подключении к базе данных произошла ошибка:\n\n" + e.Message); conn.Close(); conn.Dispose(); return false; } cmd = conn.CreateCommand(); cmd.CommandText = "SELECT TO_CHAR(id), l_name from akriko.cat_executors WHERE id in (SELECT distinct(ispolnitel_cik_id) FROM akriko.appeal) ORDER BY UPPER(l_name)"; try { dr = cmd.ExecuteReader(); while (dr.Read()) { if (dr.IsDBNull(0) || dr.IsDBNull(1)) { MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n"); break; } else { UsersGrid.Rows.Add(); UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["executor_id"].Value = dr.GetString(0); UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["akriko"].Value = dr.GetString(1); } } dr.Close(); dr.Dispose(); } catch (Exception e) { MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n" + e.Message); } if (UsersGrid.Rows.Count == 0) { MessageBox.Show("Отсутствуют неотконвертированные исполнители", "Конвертация исполнителей", MessageBoxButtons.OK, MessageBoxIcon.Information); ConvertButton.Enabled = false; } FillDeloDepartmentsColumn(); return true; }
/// <summary> /// Connect with Database and return connection. /// </summary> /// <returns></returns> public void Connect(string UserId, string Password, string Privilege = "") { ConnectionSetup(UserId, Password); if (Privilege != "") connectionString = "DBA Privilege=" + Privilege + ";" + connectionString; //Console.WriteLine(); //Console.WriteLine("**** Database Connection Variables ****"); //Console.WriteLine("UserID - " + UserId); //Console.WriteLine("Password - " + Password); //Console.WriteLine("Privilege - " + Privilege); try { conn = new OracleConnection(connectionString); conn.Open(); //Console.WriteLine("Database Connection Success!"); //Console.WriteLine(); } catch (OracleException e) { Console.WriteLine(e.Message); if (conn != null) { try { conn.Dispose(); } catch { } } } catch (Exception e) { Console.WriteLine(e.Message); } }
private void Form1_Load(object sender, EventArgs e) { var oracleConnectionStringBuilder = OracleConnectionStringBuilder; var oracleConnection = new OracleConnection(); oracleConnection.ConnectionString = oracleConnectionStringBuilder.ConnectionString; try { this.employeeTableAdapter.Connection = oracleConnection; oracleConnection.Open(); this.employeeTableAdapter.Fill(this.employeeDataSet.Employee); oracleConnection.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error connecting to Oracle"); } // TODO: This line of code loads data into the 'employeeDataSet.Employee' table. You can move, or remove it, as needed. }
public static ICollection<Measure> GetMeasureTable(string host, string oraUser, string oraPass, string tableName, String[] fields) { string oradb = "Data Source=(DESCRIPTION=" + "(ADDRESS=(PROTOCOL=TCP)(HOST=" + host + ")(PORT=1521))" + "(CONNECT_DATA=(SERVICE_NAME=XE)));" + "User Id=" + oraUser + ";Password="******";"; //string oradb = "Data Source=XE;User Id=" + oraUser + ";Password="******";"; OracleConnection conn = new OracleConnection(oradb); conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "select " + string.Join(", ", fields) + " from " + tableName; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); ICollection<Measure> measures = new HashSet<Measure>(); while (dr.Read()) { int id = Int32.Parse(dr[0].ToString()); string measureName = dr[1].ToString(); Measure measure = new Measure(id, measureName); measures.Add(measure); } conn.Dispose(); return measures; }
public bool deleteUser(UsUser u) { OracleConnection connection = null; try { using (connection = new OracleConnection(OracleHelper.connectionString())) { using (var command = new OracleCommand("US_USER_DELETE")) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("ID", u.Id); connection.Open(); if (command.ExecuteNonQuery() > 0) return true; } } } catch (Exception e) { if (connection.State == ConnectionState.Open) connection.Dispose(); LogHelper.WriteLog(e); throw e; } finally { connection.Close(); } return false; }
public DataTable EP_RUN_QUERY(string DB_Platform, string ConnAuth, string SQLin) { DataTable dt = new DataTable("ReturnData"); DataColumn column; DataRow row; switch (DB_Platform) { case "Oracle": case "ORACLE": using (OracleConnection connection = new OracleConnection(ConnAuth)) { try { //Open Connection connection.Open(); //Initiate sqlplus. OracleCommand sqlplus = connection.CreateCommand(); //Initiate buffer for SQL syntax. StringBuilder sqlStatement = new StringBuilder(); //Input Received SQL. sqlStatement.Append(SQLin); //Stage SQL Statement. sqlplus.CommandText = sqlStatement.ToString(); //Run the SQL Statement and load data into Reader. OracleDataReader dr = sqlplus.ExecuteReader(); //Pass Data to Datatable dt.Load(dr); // return (SuccessMessage); } catch (OracleException ex) { //Return Oracle Error. //return ex.ToString(); // Create first column and add to the DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.Int32"); column.ColumnName = "ChildID"; column.AutoIncrement = true; column.AutoIncrementSeed = 0; column.AutoIncrementStep = 1; column.Caption = "ID"; column.ReadOnly = true; column.Unique = true; dt.Columns.Add(column); // Create second column and add to the DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "ChildType"; column.AutoIncrement = false; column.Caption = "ChildType"; column.ReadOnly = false; column.Unique = false; dt.Columns.Add(column); // Create third column and add to the DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "ChildItem"; column.AutoIncrement = false; column.Caption = "ChildItem"; column.ReadOnly = false; column.Unique = false; dt.Columns.Add(column); // Create fourth column and add to the DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "ChildValue"; column.AutoIncrement = false; column.Caption = "ChildValue"; column.ReadOnly = false; column.Unique = false; dt.Columns.Add(column); row = dt.NewRow(); row["ChildType"] = "RETURN"; row["ChildItem"] = "ERROR"; row["ChildValue"] = DB_ERROR_FORMATTER("ORACLE", ex.ToString()); dt.Rows.Add(row); //return dt; } finally { //Close connection. connection.Close(); connection.Dispose(); } } return dt; case "Microsoft": case "MICROSOFT": using (SqlConnection connection = new SqlConnection(ConnAuth)) { try { connection.Open(); SqlCommand sqlplus = connection.CreateCommand(); StringBuilder sqlStatement = new StringBuilder(); sqlStatement.Append(SQLin); sqlplus.CommandText = sqlStatement.ToString(); SqlDataReader dr = sqlplus.ExecuteReader(); dt.Load(dr); } catch (SqlException ex) { //Return Oracle Error. //return ex.ToString(); // Create first column and add to the DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.Int32"); column.ColumnName = "ChildID"; column.AutoIncrement = true; column.AutoIncrementSeed = 0; column.AutoIncrementStep = 1; column.Caption = "ID"; column.ReadOnly = true; column.Unique = true; dt.Columns.Add(column); // Create second column and add to the DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "ChildType"; column.AutoIncrement = false; column.Caption = "ChildType"; column.ReadOnly = false; column.Unique = false; dt.Columns.Add(column); // Create third column and add to the DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "ChildItem"; column.AutoIncrement = false; column.Caption = "ChildItem"; column.ReadOnly = false; column.Unique = false; dt.Columns.Add(column); // Create fourth column and add to the DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "ChildValue"; column.AutoIncrement = false; column.Caption = "ChildValue"; column.ReadOnly = false; column.Unique = false; dt.Columns.Add(column); row = dt.NewRow(); row["ChildType"] = "RETURN"; row["ChildItem"] = "ERROR"; row["ChildValue"] = DB_ERROR_FORMATTER("MICROSOFT", ex.ToString()); dt.Rows.Add(row); } finally { connection.Close(); connection.Dispose(); } return dt; } default: return dt; } }
//ORACLE STRUCTURE METHODS // //Creates User in Oracle public string EP_ADD_ORACLE_USER(string ConnAuth, string Name, string Password) { using (OracleConnection connection = new OracleConnection(constr)) { try { Int32 NonQueryReturn = 0; connection.Open(); OracleCommand sqlplus = connection.CreateCommand(); StringBuilder sqlStatement = new StringBuilder(); sqlStatement.Append("CREATE USER " + MaxNameLength(Name, 30) + " PROFILE "); sqlStatement.Append("DEFAULT IDENTIFIED BY " + Password + " "); sqlStatement.Append("DEFAULT TABLESPACE " + Name + "_TS "); sqlStatement.Append("TEMPORARY TABLESPACE TEMP "); sqlStatement.Append("QUOTA UNLIMITED ON " + Name + "_TS "); sqlStatement.Append("ACCOUNT UNLOCK "); sqlplus.CommandText = sqlStatement.ToString(); NonQueryReturn = sqlplus.ExecuteNonQuery(); //Clear sqlstatement for new sql. sqlStatement.Clear(); sqlStatement.Append("GRANT CONNECT TO " + MaxNameLength(Name, 30)); sqlplus.CommandText = sqlStatement.ToString(); sqlplus.ExecuteNonQuery(); // return sqlStatement.ToString(); return ("User " + Name + " created."); } catch (OracleException ex) { return DB_ERROR_FORMATTER("ORACLE", ex.ToString()); } finally { connection.Close(); connection.Dispose(); } } }
// START DB SQL Executes, USER for DDL, and DML //Only for None Query SQL statements. public string EP_RUN_NONE_QUERY(string DB_Platform, string ConnAuth, string SQLin, string SuccessMessage) { switch (DB_Platform) { case "Oracle": case "ORACLE": using (OracleConnection connection = new OracleConnection(ConnAuth)) { try { //Open Connection connection.Open(); //Initiate sqlplus. OracleCommand sqlplus = connection.CreateCommand(); //Initiate buffer for SQL syntax. StringBuilder sqlStatement = new StringBuilder(); //Input Received SQL. sqlStatement.Append(SQLin); //Stage SQL Statement. sqlplus.CommandText = sqlStatement.ToString(); //Run the SQL Statement. sqlplus.ExecuteNonQuery(); return (SuccessMessage); } catch (OracleException ex) { //Return Oracle Error. return DB_ERROR_FORMATTER("ORACLE", ex.ToString()); } finally { //Close connection. connection.Close(); connection.Dispose(); } } case "Microsoft": case "MICROSOFT": using (SqlConnection connection = new SqlConnection(ConnAuth)) { try { connection.Open(); SqlCommand sqlplus = connection.CreateCommand(); StringBuilder sqlStatement = new StringBuilder(); sqlStatement.Append(SQLin); sqlplus.CommandText = sqlStatement.ToString(); sqlplus.ExecuteNonQuery(); return (SuccessMessage); } catch (SqlException ex) { return DB_ERROR_FORMATTER("MICROSOFT", ex.ToString()); } finally { connection.Close(); connection.Dispose(); } } default: return "Invalid DB Platform"; } }
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(); } }
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(); } }
public DataTable ExecuteOracle() { var connection = new OracleConnection(System.Configuration.ConfigurationManager.AppSettings["DataConnOracle"]); using (connection) { var imageInfo = new DataTable(); try { connection.Open(); var selectCommand = new OracleCommand { Connection = connection, CommandText = @"SELECT b.brandname, mediaid, medianame, originalfilename, mediastoragepath, repwebimagepath, m.createdby, p1.lastname AS createdbyname, m.createdat, editedby, p2.lastname AS editedbyname, editedat FROM cql_owner.media m INNER JOIN cql_owner.brand b on m.brandid = b.brandid INNER JOIN cql_owner.person p1 on m.createdby = p1.personid LEFT OUTER JOIN cql_owner.person p2 on m.editedby = p2.personid" }; var oracleDataAdapter = new OracleDataAdapter(selectCommand); oracleDataAdapter.Fill(imageInfo); return imageInfo; } finally { connection.Dispose(); connection.Close(); } } }
// START DB Procedure Methods // public DataTable SQL_PROCEDURE(string DB_Platform, string ConnAuth, string ProcedureName, List<DBParameters> _dbParameters) { //string _return = ""; //DataSet ProcReturn = new DataSet(); DataTable table = ProcDataTable(); DataRow row; switch (DB_Platform) { case "Oracle": case "ORACLE": using (OracleConnection connection = new OracleConnection(ConnAuth)) { OracleCommand DBProcedure = new OracleCommand(); //Pass DB Connection Settings DBProcedure.Connection = connection; //Pass Procedure Name DBProcedure.CommandText = ProcedureName; //Set Commandtype to Storeprocedure DBProcedure.CommandType = CommandType.StoredProcedure; //Iterate through all passed parameters. foreach (DBParameters i in _dbParameters) { //Add Parameters to DBProcedure DBProcedure.Parameters.Add(i.ParamName, i.OracleParamDataType, i.ParamSize, i.ParamValue, i.ParamDirection); } try { //Open Connection connection.Open(); DBProcedure.ExecuteNonQuery(); int iNumber = 0; foreach (DBParameters ii in _dbParameters) { //Resulting Data will be added to returning table. row = table.NewRow(); row["ChildType"] = ii.ParamDirection.ToString(); row["ChildItem"] = DBProcedure.Parameters[iNumber].ParameterName.ToString(); row["ChildValue"] = DBProcedure.Parameters[iNumber].Value.ToString(); table.Rows.Add(row); iNumber++; } } catch (OracleException ex) { //Return Oracle Error. row = table.NewRow(); row["ChildType"] = "Error"; row["ChildItem"] = "Exception"; row["ChildValue"] = DB_ERROR_FORMATTER("ORACLE", ex.ToString()); table.Rows.Add(row); } finally { //Close connection. connection.Close(); connection.Dispose(); } } break; case "Microsoft": case "MICROSOFT": using (SqlConnection connection = new SqlConnection(ConnAuth)) { SqlCommand DBProcedure = new SqlCommand(); //Pass DB Connection Settings DBProcedure.Connection = connection; //Pass Procedure Name DBProcedure.CommandText = ProcedureName; //Set Commandtype to Storeprocedure DBProcedure.CommandType = CommandType.StoredProcedure; SqlParameter[] param = new SqlParameter[_dbParameters.Count]; int paramcount = 0; //Iterate through all passed parameters. foreach (DBParameters i in _dbParameters) { //Add Parameters to DBProcedure //DBProcedure.Parameters.Add(i.ParamName, i.MSSqlParamDataType, i.ParamSize, i.ParamValue, i.ParamDirection); //DBProcedure.Parameters.Add(i.ParamName, i.MSSqlParamDataType).Value = i.ParamValue; param[paramcount] = new SqlParameter(); param[paramcount].Direction = i.ParamDirection; param[paramcount].ParameterName = i.ParamName; param[paramcount].SqlDbType = i.MSSqlParamDataType; if (param[paramcount].SqlDbType == SqlDbType.Int) if (i.ParamValue == "" || i.ParamValue == null) param[paramcount].Value = 0; else param[paramcount].Value = Convert.ToInt32(i.ParamValue); else param[paramcount].Value = i.ParamValue; DBProcedure.Parameters.Add(param[paramcount]); paramcount++; } try { //Open Connection connection.Open(); DBProcedure.ExecuteNonQuery(); int iNumber = 0; foreach (DBParameters ii in _dbParameters) { //Resulting Data will be added to returning table. row = table.NewRow(); row["ChildType"] = ii.ParamDirection.ToString(); row["ChildItem"] = DBProcedure.Parameters[iNumber].ParameterName.ToString(); row["ChildValue"] = DBProcedure.Parameters[iNumber].Value.ToString(); table.Rows.Add(row); iNumber++; } } catch (SqlException ex) { //Return Oracle Error. row = table.NewRow(); row["ChildType"] = "Error"; row["ChildItem"] = "Exception"; row["ChildValue"] = DB_ERROR_FORMATTER("Microsoft", ex.ToString()); table.Rows.Add(row); } finally { //Close connection. connection.Close(); connection.Dispose(); } } break; } return table; }
private void button2_Click(object sender, EventArgs e) { int a, b, c; string y; a = Convert.ToInt32(textBox4.Text); a = int.Parse(textBox4.Text); b = Convert.ToInt32(textBox6.Text); b = int.Parse(textBox6.Text); c = a * b; y = c.ToString(); this.textBox5.Text = y; string oradb = "Data Source=XE;User Id=system;Password=admin;"; OracleConnection conn = new OracleConnection(oradb); conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "Insert into pharm.HIST (DRUG_ID,PURCHASE_ID,DIS,PRICE,QUANTITY) values (" + textBox1.Text + "," + textBox2.Text + ",'" + textBox3.Text + "' ," + textBox4.Text + " ," + textBox6.Text + ")"; cmd.CommandText = "update pharm.DRUG set QUANTITY =QUANTITY - "+a+""; int rowsUpdated = cmd.ExecuteNonQuery(); if (rowsUpdated == 0) MessageBox.Show("Record not inserted"); else MessageBox.Show("Success!"); conn.Dispose(); }
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 List<UsUser> getUserAll() { OracleConnection connection = null; OracleDataReader dr = null; List<UsUser> list = null; try { using (connection = new OracleConnection(OracleHelper.connectionString())) { using (var command = new OracleCommand("US_USER_GETALL", connection)) { connection.Open(); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("P_CUR_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output; using (dr = command.ExecuteReader()) { list = new List<UsUser>(); if (dr.HasRows) { while (dr.Read()) { list.Add( new UsUser() { Id = Convert.ToInt32(dr["ID"].ToString()), Email = dr["EMAIL"].ToString(), State = new UsState() { Id = Convert.ToInt32(dr["IDSTATE"].ToString()), Name = dr["NAMESTATE"].ToString() }, Role = new UsRole() { Id = Convert.ToInt32(dr["ID"].ToString()), Name = dr["NAME"].ToString() }, FirstName = dr["FIRSTNAME"].ToString(), LastName = dr["LASTNAME"].ToString(), Dni = dr["DNI"].ToString(), Ruc = dr["RUC"].ToString(), BirthDate = Convert.ToDateTime(dr["BIRTHDATE"].ToString()), Subscribed = dr["SUBSCRIBED"].ToString(), Photo = dr["PHOTO"].ToString(), UserCreate = dr["USERCREATE"].ToString(), DateCreate = Convert.ToDateTime(dr["DATECREATE"].ToString()), UserUpdate = dr["USERUPDATE"].ToString(), DateUpdate = Convert.ToDateTime(dr["DATEUPDATE"].ToString()) } ); } } } } } return list; } catch (Exception e) { dr.Dispose(); if (connection.State == ConnectionState.Open) connection.Dispose(); LogHelper.WriteLog(e); throw e; } finally { list = null; } }
public int Archivage() { int intCodeRetour = 0x00; //=== Code de retour Normalement Terminer //==== Periode selon la date du jour par défaut string strSQL = "SELECT cp.cal_no_emission FROM lg2_calen_prod cp WHERE cp.cal_dt_emission <= ADD_MONTHS(SYSDATE, :Mois_A_Conserver) ORDER BY cp.cal_dt_emission DESC"; OracleConnection objConn = new OracleConnection(Properties.Settings.Default.CONNECTSTRING); ; OracleCommand objCmd = new OracleCommand(strSQL, objConn); OracleDataReader odr = null; // create a writer and open the file TextWriter log = new StreamWriter(".\\logs\\DV1L3001_" + DateTime.Now.ToString("ddMMyyyyHHmmss") + ".log"); try { // write a line of text to the file log.WriteLine(DateTime.Now.ToString() + "==== DEBUT DU TRAITEMENT"); log.WriteLine(DateTime.Now + " _sourceFolder = " + _sourceFolder); log.WriteLine(DateTime.Now + " _backupFolder = " + _backupFolder); log.WriteLine(DateTime.Now + " _nbMoisAConserver = " + _nbMoisAConserver); #region Validation des informations string msgErreur = ""; //=== On vérifie si répertoire de destination existe if (!Directory.Exists(_sourceFolder)) { msgErreur += "Le répertoire source est inexistant. [source_folder = " + _sourceFolder + "]<BR>"; } if (!Directory.Exists(_backupFolder)) { msgErreur += "Le répertoire de sauvegarde est inexistant à l'endroit spécifié. [backup_destination_folder = " + _backupFolder + "]<BR>"; } if (msgErreur != "") { log.WriteLine(DateTime.Now + " ERROR : " + msgErreur.ToString()); throw new ApplicationException(msgErreur); } #endregion objConn.Open(); //==== On soustrait les mois pour trouver la date de départ objCmd.Parameters.Add("Mois_A_Conserver", OracleDbType.Varchar2).Value = _nbMoisAConserver * -1; odr = objCmd.ExecuteReader(CommandBehavior.CloseConnection); long noEmission = 0; if (odr.Read()) { noEmission = _noEmissionBase + (int)odr["cal_no_emission"]; log.WriteLine(DateTime.Now + " Start with noEmission = " + noEmission.ToString()); } else { log.WriteLine(DateTime.Now + " Start with noEmission = AUCUNE ÉMISSION"); } int nbMissingFolders = 0; for (long i = noEmission; i >= _noEmissionBase; i--) { //=== Si le folder existe on le zip string archiveFolder = _sourceFolder + "\\" + i.ToString(); if (Directory.Exists(archiveFolder)) { nbMissingFolders = 0; log.WriteLine(DateTime.Now + " FOLDER EXIST archiveFolder = " + archiveFolder.ToString()); using (ZipFile zip = new ZipFile()) { zip.UpdateDirectory(archiveFolder);//==== Répertoire qui sera compressé zip.Save(_backupFolder + "\\" + i.ToString() + ".zip"); //=== Nom et emplacement du fichier ZIP log.WriteLine(DateTime.Now + " ZIP TO : " + _backupFolder + "\\" + i.ToString() + ".zip"); zip.Dispose(); } Directory.Delete(archiveFolder, true); //==== Supprime le répertoire qui a été compressé log.WriteLine(DateTime.Now + " DELETE UNCOMPRESSED FOLDER : " + archiveFolder.ToString()); } else { nbMissingFolders++; //=== Cumule le nombre de répertoire manquan log.WriteLine(DateTime.Now + " _" + nbMissingFolders + "_ FOLDER DOESNT EXIST archiveFolder = " + archiveFolder.ToString()); if (nbMissingFolders >= _maxNbMissingFolder)//=== Si 5 répertoire consécutif n'existe pas on arrete le traitement. { log.WriteLine(DateTime.Now + " STOP CAUSE BY MORE THEN " + _maxNbMissingFolder.ToString() + " MISSING FOLDERS"); break; } } } log.WriteLine(DateTime.Now.ToString() + "==== FIN DU TRAITEMENT"); } catch (Exception ex) { //=== Envoie de courriel sur erreur intCodeRetour = _codeRetourErreur; log.WriteLine(DateTime.Now.ToString() + " ERROR : " + ex.Message); GZMMailer mail = new GZMMailer(_emailFrom, _emailErrorTo, "Erreur dans l'application : DV1L3001_ArchivageFichiersRdi", ex.Message, "", 1, "", ""); } finally { // close the stream if (log != null) { log.Close(); log.Dispose(); } //=== Ferme les objets if (odr != null) { odr.Close(); odr.Dispose(); } if (objConn != null) { objConn.Close(); objConn.Dispose(); } } return intCodeRetour; }
private static void Close(OracleConnection connection) { try { connection.Close(); connection.Dispose(); } catch (Exception e) { Logger.Write(e.Message); } }
/* public Datatable SQL_CREATE_DB_DT(string DB_PLATFORM, string connAuth, string SystemName) { ArrayList rows = new ArrayList(); //DROP EXISTING SYSTEM TABLES rows.Add(EP_DROP_ORACLE_USER(SystemName)); rows.Add(EP_DROP_ORACLE_TABLESPACE(SystemName)); rows.Add(EP_DROP_ORACLE_PROFILE(SystemName)); rows.Add(EP_DROP_ORACLE_ROLE(SystemName + "_ROLE")); //CREATE NECESSARY STRUCTURE FOR STORING OBJECTS rows.Add(EP_ADD_ORACLE_PROFILE(SystemName)); rows.Add(EP_ADD_ORACLE_TABLESPACE(SystemName)); rows.Add(SQL_Add_USER(SystemName)); rows.Add(EP_ADD_ORACLE_ROLE(SystemName + "_ROLE")); rows.Add(EP_GRANT_ORACLE_PRIVILEGE("CREATE TABLE", SystemName)); rows.Add(EP_GRANT_ORACLE_PRIVILEGE("CREATE PROCEDURE", SystemName)); rows.Add(EP_GRANT_ORACLE_PRIVILEGE("CREATE SEQUENCE", SystemName)); rows.Add(EP_GRANT_ORACLE_PRIVILEGE("SELECT ANY DICTIONARY", SystemName)); //1st Stage of Core TABLES // rows.Add(EP_ADD_TABLE("Applications")); //Application_ID, NAME rows.Add(EP_ADD_TABLE("Events")); //Has Applications_ID //Events_ID, Date_Created, Application_ID, Event_Type_ID rows.Add(EP_ADD_TABLE("Logs")); //LOG_ID rows.Add(EP_ADD_TABLE("Event_Types")); //Event_Type_ID, Value rows.Add(EP_ADD_TABLE("Errors")); //Has Application_ID //Error_ID, Error_Number, Error_Description, User, Program, Procedure_Name, //2nd Stage of Core Tables // rows.Add(EP_ADD_TABLE("Organizations")); //Organization_ID rows.Add(EP_ADD_TABLE("Users")); //User_ID rows.Add(EP_ADD_TABLE("Transactions")); //Transaction_ID, Application_ID, Organization_ID, User_ID rows.Add(EP_ADD_TABLE("Sessions")); //SESSION_ID, USER_ID, Application_ID rows.Add(EP_ADD_TABLE("Tokens")); //TOKEN_ID, SESSION_ID rows.Add(EP_ADD_TABLE("HTML_ELEMENTS")); //HTML_ELEMENT_ID, Name, Start Tag, End Tag, Empty, Depr., DTD, Description //http;//www.w3.org/TR/html4/index/elements.html rows.Add(EP_ADD_TABLE("Cases")); //CASE_ID, CASE_TYPE, ORGANIZATION_ID, rows.Add(EP_ADD_TABLE("Case_Types")); //Case_Type_ID, NAME rows.Add(EP_ADD_TABLE("Forms")); //Form_ID, FIELD_NAME, FIELD_TYPE, VALUE, TEXT rows.Add(EP_ADD_TABLE("Forms_HTML_Objects")); //Foreign Keys TO Forms(FormID) & HTML_Objects(HTML_Object_ID) rows.Add(EP_ADD_TABLE("WorkFlows")); //Workflow_ID rows.Add(EP_ADD_TABLE("Roles")); //Role_ID, Applications_ID, Privelege_ID rows.Add(EP_ADD_TABLE("Roles_Priveleges")); rows.Add(EP_ADD_TABLE("USERS_ROLES_PRIVELEGES")); rows.Add(EP_ADD_TABLE("Priveleges")); //Privelege_ID } */ //START COMMON DATATABASE COMMANDS // public string SQL_SELECT_DoesCMSUserExist(string Name) { OracleConnection connection = new OracleConnection(constr); try { connection.Open(); string tempSQL = "select username from dba_users where username = '******'"; OracleCommand SQLCommand = new OracleCommand(tempSQL); SQLCommand.Connection = connection; OracleDataReader reader = SQLCommand.ExecuteReader(); if (reader.HasRows) { return "yes"; } else { return "no"; } } catch (OracleException ex) { return DB_ERROR_FORMATTER("ORACLE", ex.ToString()); } finally { connection.Close(); connection.Dispose(); } }
public bool updateUser(UsUser u) { OracleConnection connection = null; try { using (connection = new OracleConnection(OracleHelper.connectionString())) { using (var command = new OracleCommand("US_USER_UPDATE")) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("ID", u.Id); command.Parameters.Add("EMAIL", u.Email); command.Parameters.Add("PASSWD", u.Passwd); command.Parameters.Add("IDSTATE", u.State.Id); command.Parameters.Add("IDROLE", u.Role.Id); command.Parameters.Add("FIRSTNAME", u.FirstName); command.Parameters.Add("LASTNAME", u.LastName); command.Parameters.Add("DNI", u.Dni); command.Parameters.Add("RUC", u.Ruc); command.Parameters.Add("BIRTHDATE", u.BirthDate); command.Parameters.Add("SUBSCRIBED", u.Subscribed); command.Parameters.Add("PHOTO", u.Photo); command.Parameters.Add("USER_CREATE", u.UserCreate); command.Parameters.Add("DATE_CREATE", u.DateCreate); command.Parameters.Add("USER_UPDATE", u.UserUpdate); command.Parameters.Add("DATE_UPDATE", u.DateUpdate); connection.Open(); if (command.ExecuteNonQuery() > 0) return true; } } } catch (Exception e) { if (connection.State == ConnectionState.Open) connection.Dispose(); LogHelper.WriteLog(e); throw e; } finally { connection.Close(); } return false; }