/// <summary> /// Adiciona um parâmetro à lista de parâmetros /// </summary> /// <param name="nomeParametro">Nome do parâmetro</param> /// <param name="tipoParametro">Tipo do parâmetro</param> /// <param name="valor">Valor do parâmetro</param> public void AddParam(string nomeParametro, OracleDbType tipoParametro, object valor) { // Remove o parâmetro caso já exista OracleParameter paramExiste = ListaParametros.Where(x => x.ParameterName.Equals(nomeParametro)).FirstOrDefault(); if (paramExiste != null) { ListaParametros.Remove(paramExiste); } // Adiciona o parâmetro //OracleParameter param = new OracleParameter(nomeParametro, tipoParametro); OracleParameter param = new OracleParameter(); param = new Oracle.DataAccess.Client.OracleParameter(); param.ParameterName = nomeParametro; param.OracleDbType = tipoParametro; param.Direction = ParameterDirection.Input; if (tipoParametro == OracleDbType.Blob) { param.Size = ((byte[])valor).Length; } param.Value = valor; ListaParametros.Add(param); }
public bool new_customers(int id) { var con = new OracleConnection(constr); con.Open(); var objCmd = new OracleCommand { Connection = con, CommandText = "Tables.new_customers", CommandType = CommandType.StoredProcedure }; var prm = new OracleParameter { Direction = ParameterDirection.ReturnValue, DbType = DbType.Int64 }; objCmd.Parameters.Add(prm); objCmd.Parameters.Add("new_cust", OracleDbType.Int64).Direction = ParameterDirection.Input; objCmd.Parameters["new_cust"].Value = id; objCmd.ExecuteNonQuery(); return(Convert.ToInt32(objCmd.Parameters[0].Value) == 1); }
private string SaveToDatabase(byte[] btReport, string id) { // Write to J Drive string filepath = ""; string timestamp = DateTime.Now.ToString("MMddHHmmssfff"); string filename = "fax" + id + "_" + timestamp + ".pdf"; string credentials = Data.getJCredential(); String[] credentialsArray = credentials.Split("|".ToCharArray()); string COMPUTER_IP = credentialsArray[0].ToString().Split(':')[1]; string DOMAIN = credentialsArray[1].ToString().Split(':')[1]; string USER_NAME = credentialsArray[2].ToString().Split(':')[1]; string PASSWORD = credentialsArray[3].ToString().Split(':')[1]; try { using (NetworkShareAccesser.Access(COMPUTER_IP, DOMAIN, USER_NAME, PASSWORD)) { filepath = "\\\\" + COMPUTER_IP + "\\j_drive\\Clients\\LifeEvent\\" + filename; File.WriteAllBytes(filepath, btReport); filepath = filepath.Replace("\\\\" + COMPUTER_IP + "\\j_drive\\", "J:\\"); } } catch { } // Save to Database Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection(); try { Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Enrollment_Wizard_LE.SaveFaxImage", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", Request.Params["DpNo"]); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", SQLStatic.Sessions.GetUserName(HttpContext.Current.Request.Cookies["Session_ID"].Value.ToString())); Oracle.DataAccess.Client.OracleParameter parm; parm = new Oracle.DataAccess.Client.OracleParameter( "value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport); cmd.Parameters.Add(parm); try { cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } } finally { conn.Close(); conn.Dispose(); } return(filename); }
private void CreateOracleParameter(IDbCommand command, OracleDataAccessClient.OracleCommand commandNew) { foreach (OracleParameter para in command.Parameters) { OracleDataAccessClient.OracleDbType oracleType = ConvertOracleType(para.OracleType); OracleDataAccessClient.OracleParameter paraNew = new OracleDataAccessClient.OracleParameter(para.ParameterName, oracleType); paraNew.Value = para.Value; commandNew.Parameters.Add(paraNew); } }
public IDbDataParameter CreateParameter() { var name = this.Name; switch (this.DbType.DatabaseServer) { case DatabaseServer.SqlServer: { var p = new System.Data.SqlClient.SqlParameter(name, this.DbType.SqlServerDbType.Value); p.Direction = ParameterDirection.Input; p.Value = GetParameterValue(this.DbType.SqlServerDbType.Value); return(p); } case DatabaseServer.Oracle: { var p = new Oracle.DataAccess.Client.OracleParameter(name, this.DbType.OracleServerDbType.Value); p.Direction = ParameterDirection.Input; //p.Value = GetParameterValue(this.OracleServerDbType.Value); return(p); } case DatabaseServer.MySql: { var p = new MySql.Data.MySqlClient.MySqlParameter(name, this.DbType.MySqlServerDbType.Value); p.Direction = ParameterDirection.Input; p.Value = GetParameterValue(this.DbType.MySqlServerDbType.Value); return(p); } case DatabaseServer.PostgreSql: { var p = new Npgsql.NpgsqlParameter(name, this.DbType.PostgreSqlServerDbType.Value); p.Direction = ParameterDirection.Input; //p.Value = GetParameterValue(this.MySqlDbType.Value); return(p); } default: throw new InvalidOperationException(); } }
public static Oracle.DataAccess.Client.OracleParameter[] GetParamArray <T>(T t) { string tStr = string.Empty; if (t == null) { return(null); } System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public); if (properties.Length <= 0) { return(null); } System.Collections.Generic.List <Oracle.DataAccess.Client.OracleParameter> list = new System.Collections.Generic.List <Oracle.DataAccess.Client.OracleParameter>(); Oracle.DataAccess.Client.OracleParameter[] paramArray = new Oracle.DataAccess.Client.OracleParameter[properties.Length]; for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ string type = properties[i].PropertyType.Name; Oracle.DataAccess.Client.OracleParameter parm = new Oracle.DataAccess.Client.OracleParameter(); parm.ParameterName = ":" + name; if (null == value) { //parm = new Oracle.DataAccess.Client.OracleParameter(":" + name, DBNull.Value); parm.Value = DBNull.Value; } else { //parm = new Oracle.DataAccess.Client.OracleParameter(":" + name, value); parm.Value = value; } list.Add(parm); break; } return(list.ToArray()); }
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 int promotion_average_credit() { var con = new OracleConnection(constr); con.Open(); var objCmd = new OracleCommand { Connection = con, CommandText = "Tables.promotion_average_credit", CommandType = CommandType.StoredProcedure }; var prm = new OracleParameter { Direction = ParameterDirection.ReturnValue, DbType = DbType.Int64 }; objCmd.Parameters.Add(prm); objCmd.ExecuteNonQuery(); return(Convert.ToInt32(objCmd.Parameters[0].Value)); }
public IDbDataParameter CreateParameter() { var name = this.Name; switch (this.DbType.DatabaseServer) { case DatabaseServer.SqlServer: { var p = new System.Data.SqlClient.SqlParameter(name, this.DbType.SqlServerDbType.Value); p.Direction = ParameterDirection.Input; p.Value = GetParameterValue(this.DbType.SqlServerDbType.Value); return p; } case DatabaseServer.Oracle: { var p = new Oracle.DataAccess.Client.OracleParameter(name, this.DbType.OracleServerDbType.Value); p.Direction = ParameterDirection.Input; //p.Value = GetParameterValue(this.OracleServerDbType.Value); return p; } case DatabaseServer.MySql: { var p = new MySql.Data.MySqlClient.MySqlParameter(name, this.DbType.MySqlServerDbType.Value); p.Direction = ParameterDirection.Input; p.Value = GetParameterValue(this.DbType.MySqlServerDbType.Value); return p; } case DatabaseServer.PostgreSql: { var p = new Npgsql.NpgsqlParameter(name, this.DbType.PostgreSqlServerDbType.Value); p.Direction = ParameterDirection.Input; //p.Value = GetParameterValue(this.MySqlDbType.Value); return p; } default: throw new InvalidOperationException(); } }
public DbParameter[] GetParamArray <T>(T t, MyDBType mydbtype) { string tStr = string.Empty; if (t == null) { return(null); } System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public); if (properties.Length <= 0) { return(null); } System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>(); DbParameter[] paramArray = null; switch (mydbtype) { case MyDBType.Access: paramArray = new System.Data.OleDb.OleDbParameter[properties.Length]; break; case MyDBType.Sql: paramArray = new System.Data.SqlClient.SqlParameter[properties.Length]; break; case MyDBType.Oracle: paramArray = new Oracle.DataAccess.Client.OracleParameter[properties.Length]; break; } for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ string type = properties[i].PropertyType.Name; switch (mydbtype) { case MyDBType.Access: if (null == value) { paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, DBNull.Value); } else { paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, value); } break; case MyDBType.Sql: if (null == value) { paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, DBNull.Value); } else { paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, value); } break; case MyDBType.Oracle: if (null == value) { paramArray[i] = new Oracle.DataAccess.Client.OracleParameter(":" + name, DBNull.Value); } else { paramArray[i] = new Oracle.DataAccess.Client.OracleParameter(":" + name, value); } break; } } return(paramArray); }
public DbParameter[] GetSelectParamArray <T>(T t, MyDBType mydbtype) { string tStr = string.Empty; if (t == null) { return(null); } System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public); if (properties.Length <= 0) { return(null); } System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>(); DbParameter[] paramArray = null; int lenght = 0; for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ if (value != null) { if (CanAdd(name, t.GetType().Name)) { lenght++; } } } switch (mydbtype) { case MyDBType.Access: paramArray = new System.Data.OleDb.OleDbParameter[lenght]; break; case MyDBType.Sql: paramArray = new System.Data.SqlClient.SqlParameter[lenght]; break; case MyDBType.Oracle: paramArray = new Oracle.DataAccess.Client.OracleParameter[lenght]; break; } int index = 0; for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ string type = properties[i].PropertyType.Name; if (value != null) { if (CanAdd(name, t.GetType().Name)) { switch (mydbtype) { case MyDBType.Access: paramArray[index] = new System.Data.OleDb.OleDbParameter("@" + name, value); break; case MyDBType.Sql: paramArray[index] = new System.Data.SqlClient.SqlParameter("@" + name, value); break; case MyDBType.Oracle: paramArray[index] = new Oracle.DataAccess.Client.OracleParameter(":" + name, value); break; } index++; } } } return(paramArray); }
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(); // } //} //} }