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