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