public int InsertNewProduct(string commandString, string name, decimal price, int vendor, int measure, int category)
{
int result = -1;
OracleParameter parameterName = new OracleParameter(":name", name);
OracleParameter parameterPrice = new OracleParameter(":price", price);
OracleParameter parameterVendor = new OracleParameter(":vendor", vendor);
OracleParameter parameterMeasure = new OracleParameter(":measure", measure);
OracleParameter parameterCategory = new OracleParameter(":category", category);
OracleNotSelectConnection notSelectConnection = null;
try
{
using (notSelectConnection = new OracleNotSelectConnection())
{
result = notSelectConnection.UpdateDatabase(commandString,
new OracleParameter[] { parameterName, parameterPrice, parameterVendor, parameterMeasure, parameterCategory });
}
}
catch (Exception ex)
{
if (notSelectConnection != null)
{
notSelectConnection.Dispose();
notSelectConnection = null;
}
throw ex;
}
return result;
}
private static IDbDataParameter ConvertToNativeParameter(DbParameter dbParameter, CommandType cmdType)
{
IDbDataParameter clone = new OracleParameter() { IsNullable = dbParameter.IsNullable };
// Remove leading ':' character for stored procedures.
if (cmdType == CommandType.StoredProcedure)
{
string name = parameterRenderer.RenderParameterName(dbParameter);
if (name.StartsWith(":", StringComparison.Ordinal))
name = name.Substring(1, name.Length - 1);
clone.ParameterName = name;
}
else
{
clone.ParameterName = parameterRenderer.RenderParameterName(dbParameter);
}
if (dbParameter.PassMode == SpArgumentPassMode.DataTableFilledByAdapter)
((OracleParameter)clone).OracleDbType = OracleDbType.RefCursor;
else
clone.DbType = dbParameter.DbType;
clone.Direction = dbParameter.Direction;
clone.Precision = dbParameter.Precision;
clone.Scale = dbParameter.Scale;
clone.Size = dbParameter.Size;
clone.SourceColumn = dbParameter.SourceColumn;
clone.SourceVersion = dbParameter.SourceVersion;
clone.Value = dbParameter.Value;
return clone;
}
//-------------------------------------INSERT / UPDATE / DELETE PLAYER-------------------------------------------
/// <summary>
/// Fonction qui sert à insérer un joueur dans la BD
/// </summary>
/// <param name="username"></param>
/// <param name="email"></param>
/// <param name="password"></param>
/// <returns> true ou false si l'ajout marche ou pas</returns>
public static bool insertPlayer(string username, string email, string password)
{
OracleConnection conn = Connection.getInstance().conn;
string sqlAjout = "insert into joueurs (username,EMAIL,Hash_KEY)" +
" VALUES(:username,:EMAIL,:Hash_KEY)";
try
{
OracleCommand oraAjout = new OracleCommand(sqlAjout, conn);
OracleParameter OraParaUsername = new OracleParameter(":username", OracleDbType.Varchar2, 40);
OracleParameter OraParamEmail = new OracleParameter(":EMAIL", OracleDbType.Varchar2, 40);
OracleParameter OraParamHashKey = new OracleParameter(":Hash_KEY", OracleDbType.Char, 75); //Ajout
OraParaUsername.Value = username.ToLower();
OraParamEmail.Value = email;
OraParamHashKey.Value = Controle.hashPassword(password, null, System.Security.Cryptography.SHA256.Create());
oraAjout.Parameters.Add(OraParaUsername);
oraAjout.Parameters.Add(OraParamEmail);
oraAjout.Parameters.Add(OraParamHashKey);
oraAjout.ExecuteNonQuery();
return true;
}
catch (OracleException ex)
{
Erreur.ErrorMessage(ex);
return false;
}
}
public void AddNewBroker(Broker brokerToAdd)
{
// disconnected version
string sqlStatement = "INSERT INTO brokers(broker_id, first_name, last_name) VALUES (:broker_id, :first_name, :last_name)";
IDbConnection connection = new OracleConnection(connectionString);
OracleCommand command = new OracleCommand(sqlStatement, (OracleConnection)connection);
command.BindByName = true;
IDbDataParameter param = new OracleParameter(":first_name", OracleDbType.Varchar2, 25);
param.Value = brokerToAdd.firstName;
command.Parameters.Add(param);
param = new OracleParameter(":last_name", OracleDbType.Varchar2, 25);
param.Value = brokerToAdd.lastName;
command.Parameters.Add(param);
param = new OracleParameter(":broker_id", OracleDbType.Int16, 50);
param.Value = brokerToAdd.id;
command.Parameters.Add(param);
IDbDataAdapter da = new OracleDataAdapter();
da.InsertCommand = command;
DataRow newRow = dataSet.Tables[0].NewRow();
newRow["broker_id"] = brokerToAdd.id;
newRow["first_name"] = brokerToAdd.firstName;
newRow["last_name"] = brokerToAdd.lastName;
dataSet.Tables[0].Rows.Add(newRow);
// it's not clear from this code but we could make many changes to
// the dataset before submitting back to the database.
connection.Open();
da.Update(dataSet);
connection.Close();
}
//TEMPORAIRE VERIFIER Si jid ou USERNAME
public static bool updatePlayer(int jid, string username, string email, string password)
{
OracleConnection conn = Connection.getInstance().conn;
string sqlupdate = "update joueurs set email=:EMAIL,Hash_KEY=:Hash_KEY where jid=:jid";
try
{
OracleCommand oraUpdate = new OracleCommand(sqlupdate, conn);
OracleParameter OraParamEmail = new OracleParameter(":EMAIL", OracleDbType.Varchar2, 40);
OracleParameter OraParamHashKey = new OracleParameter(":Hash_KEY", OracleDbType.Char, 75); //Ajout
OracleParameter OraParamJid = new OracleParameter(":jid", OracleDbType.Int32);
OraParamEmail.Value = email;
OraParamHashKey.Value = Controle.hashPassword(password, null, System.Security.Cryptography.SHA256.Create());
OraParamJid.Value = jid;
oraUpdate.Parameters.Add(OraParamEmail);
oraUpdate.Parameters.Add(OraParamHashKey);
oraUpdate.Parameters.Add(OraParamJid);
oraUpdate.ExecuteNonQuery();
return true;
}
catch (OracleException ex)
{
Erreur.ErrorMessage(ex);
return false;
}
}
protected void Page_Load(object sender, EventArgs e)
{
var cookielist = sm.GetCartFromCookie();
if(cookielist != null)
{
Cart.Text = "Cart: " + cookielist.Count().ToString() + " product(s)";
if (cookielist.Count() > 0)
{
foreach (var product in cookielist)
{
OracleParameter[] parameterList = new OracleParameter[]
{
new OracleParameter("serienumber",product.ProductId)
};
DataTable DT = db.ExecuteReadQuery("SELECT * FROM PRODUCT WHERE SERIE_NUMBER = :serienumber", parameterList);
foreach (DataRow DR in DT.Rows)
{
ProductListControl productListControl = LoadControl("~/controls/ProductListControl.ascx") as ProductListControl;
productListControl.Name = DR["NAME"].ToString();
productListControl.ItemNumber = DR["SERIE_NUMBER"].ToString();
productListControl.Price = DR["PRICE"].ToString();
productListControl.ImagePath = DR["IMAGE_PATH"].ToString();
productListControl.Id = DR["ID"].ToString();
products.Controls.Add(productListControl);
}
}
}
}
}
public ChangeResultSettings IsExistsEmail(string email)
{
var result = new ChangeResultSettings();
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_check_exists_email";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new OracleParameter("email_check", OracleDbType.Varchar2)).Value = email;
var op = new OracleParameter("total_result", OracleDbType.Long, 15) { Direction = ParameterDirection.Output };
cm.Parameters.Add(op);
cm.ExecuteNonQuery();
var total = Convert.ToInt32(cm.Parameters["total_result"].Value.ToString());
switch (total)
{
case 0:
result.ChangeResult = ChangeResult.ThanhCong;
break;
default:
result.ChangeResult = ChangeResult.ThatBai;
break;
}
}
catch (Exception ex)
{
result.Message = "Có lỗi trong quá trình kiểm tra email. Vui lòng thử lại!";
result.ChangeResult = ChangeResult.ThatBai;
}
return result;
}
public void editarAtencionCliente(string idAC, string passwordAC, string nombreAC, string apellidosAC, string cedulaAC, string correoAC)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "atencion_cliente_paquete.editarAtencionCliente";
OracleParameter idACP = new OracleParameter("idAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
idACP.Value = idAC;
OracleParameter passwordACP = new OracleParameter("passwordAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
passwordACP.Value = passwordAC;
OracleParameter nombreACP = new OracleParameter("nombreAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
nombreACP.Value = nombreAC;
OracleParameter apellidosACP = new OracleParameter("apellidosAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
apellidosACP.Value = apellidosAC;
OracleParameter cedulaACP = new OracleParameter("cedulaAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
cedulaACP.Value = cedulaAC;
OracleParameter correoACP = new OracleParameter("correoAC", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
correoACP.Value = correoAC;
cmd.Parameters.AddRange(new OracleParameter[] { idACP,passwordACP, nombreACP, apellidosACP, cedulaACP, correoACP });
cmd.ExecuteNonQuery();
cmd.Dispose();
base.cerrarConexion();
}
public void RegisterUser(string username, string password, string email)// nieuwe gebruiker registreren
{
OracleParameter[] parameterListUsername = new OracleParameter[]
{
new OracleParameter("username",username)
};
DataTable DT = db.ExecuteReadQuery("SELECT DISTINCT LOWER(USER_NAME) FROM CUSTOMER WHERE USER_NAME = :username", parameterListUsername);// check of gebruikersnaam voorkomt
OracleParameter[] parameterListEmail = new OracleParameter[]
{
new OracleParameter("email",email)
};
DataTable DT2 = db.ExecuteReadQuery("SELECT DISTINCT LOWER(EMAIL) FROM CUSTOMER WHERE EMAIL = :email", parameterListEmail); // check of email voorkomt
//check of de username en email al bestaan
if (DT.Rows.Count == 0 && DT2.Rows.Count == 0)
{
OracleParameter[] parameterListRegister = new OracleParameter[]
{
new OracleParameter("email",email),
new OracleParameter("username",username),
new OracleParameter("password",HashPassword(password))
};
db.ExecuteInsertQuery("INSERT INTO \"CUSTOMER\" (EMAIL, USER_NAME, PASSWORD, NEWSLETTER) VALUES (:email, :username, :password, '1')", parameterListRegister); // nieuwe customer inserten
ScriptManager.RegisterClientScriptBlock(HttpContext.Current.CurrentHandler as Page, GetType(), "alertMessage", "alert('You have been successfully registered and logged in.')", true);// succesbericht
}
else
{
ScriptManager.RegisterClientScriptBlock(HttpContext.Current.CurrentHandler as Page, GetType(), "alertMessage", "alert('Username or email already in use!')", true); //foutmelding
}
}
/// <summary>
/// Créé la commande. Ne permet pas le remplissage des médicaments.
/// </summary>
/// <param name="oMsg"></param>
/// <returns></returns>
public STR_MSG CreerCommandeCliente(STR_MSG oMsg)
{
char status = '1'; // commande OK
char prescription = '0'; // commande sans prescription
char hasRead = '1'; //
int prescription_id = 1;
int patientID = (int)oMsg.Data[1];
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PHARMAWEB.Create_Commande";
OracleParameter RV = new OracleParameter();
RV.Direction = ParameterDirection.ReturnValue; // indique si c'est un paramètre entrant, ou de retour
RV.OracleDbType = OracleDbType.Int32;
RV.ParameterName = "id";
cmd.Parameters.Add(RV);
cmd.Parameters.Add("statut", OracleDbType.Char).Value = status;
cmd.Parameters.Add("prescription", OracleDbType.Char).Value = prescription;
cmd.Parameters.Add("hasRead", OracleDbType.Char).Value = hasRead;
cmd.Parameters.Add("prescription_id", OracleDbType.Int32, 255).Value = prescription_id;
cmd.Parameters.Add("patientID", OracleDbType.Int32, 255).Value = patientID;
this.oMsg = CL_MESSAGE_Factory.msg_factory("", new object[] { cmd }, "", "", "", true, "");
return this.oMsg;
}
public static DataTable ExecuteReadQuery(string sqlquery, OracleParameter[] parameters)
{
try
{
using (Connection)
using (var command = new OracleCommand(sqlquery, _connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
var dt = new DataTable();
using (OracleDataReader reader = command.ExecuteReader())
{
dt.Load(reader);
}
return dt;
}
}
catch (OracleException oe)
{
Debug.WriteLine(oe.Message);
return null;
}
}
public string Add(OracleParameter parameter)
{
var name = ":p" + (NamedParameters.Count + 1);
parameter.ParameterName = name;
NamedParameters.Add(parameter);
return name;
}
public override bool ExecuteProcedure(string ProcedureName, List<System.Data.IDbDataParameter> parameters, System.Data.DataSet ds)
{
bool result = false;
try
{
this.DBCommand.CommandType = CommandType.StoredProcedure;
this.DBCommand.CommandText = ProcedureName;
this.DBCommand.Connection = this.DBConnection;
this.DBCommand.Parameters.Clear();
foreach (IDataParameter parameter in parameters)
{
this.DBCommand.Parameters.Add(parameter);
}
OracleParameter paramRefCursor = new OracleParameter();
paramRefCursor.OracleDbType = OracleDbType.RefCursor;
paramRefCursor.Direction = ParameterDirection.Output;
paramRefCursor.ParameterName = PARAM_REF_CURSOR;
this.DBCommand.Parameters.Add(paramRefCursor);
OracleDataAdapter da = new OracleDataAdapter((OracleCommand)this.DBCommand);
da.Fill(ds);
result = true;
}
catch (Exception ex)
{
throw ex;
}
return result;
}
public void salva(MigrationModel m)
{
string sql = "INSERT INTO AMC_PERSONNEL_INTEGRATION(FILE_ID, DESCRIPTION, STATUS) VALUES(:FILE_ID, :DESCRIPTION, :STATUS);";
OracleParameter FILE_ID = new OracleParameter();
FILE_ID.Value = m.FileId;
FILE_ID.ParameterName = "FILE_ID";
OracleParameter DESCRIPTION = new OracleParameter();
DESCRIPTION.Value = m.Description;
DESCRIPTION.ParameterName = "DESCRIPTION";
OracleParameter STATUS = new OracleParameter();
STATUS.Value = m.status;
STATUS.ParameterName = "STATUS";
OracleCommand command = this.con.CreateCommand();
command.CommandText = sql;
try
{
command.ExecuteNonQuery();
}
catch (OracleException ex)
{
con.Close();
throw ex;
}
}
public ChangeResultSettings CapNhatLoaiNguonVon(string mdv, string nsd, string pas, string maDonVi, long idDuAn, int loaiNguonVon)
{
var result = new ChangeResultSettings();
try
{
ConnectDB.CloseConnection(_connectGs);
_connectGs = new OracleConnection();
_connectGs = ConnectDB.GetOracleConnection(_connectGs);
var cm = _connectGs.CreateCommand();
cm.CommandText = "usp_CapNhat_DuAn_LoaiNguonVon";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = "";
cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "";
cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = "";
cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = idDuAn;
cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi;
cm.Parameters.Add(new OracleParameter("loai_nguonvon", OracleDbType.Int32)).Value = loaiNguonVon;
var op = new OracleParameter("row_updated", OracleDbType.Long, 15) { Direction = ParameterDirection.Output };
cm.Parameters.Add(op);
cm.ExecuteNonQuery();
var rowUpdated = cm.Parameters["row_updated"].Value.ToString();
if (rowUpdated == "0")
result.ChangeResult = ChangeResult.ThatBai;
}
catch (Exception ex)
{
result.ChangeResult = ChangeResult.ThatBai;
result.Message = ex.Message;
throw;
}
return result;
}
public void agregarNoticia(string fecha_expiracionN, string contenidoN,string encabezadoN,string tituloN)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "noticias_paquete.agregarNoticia";
OracleParameter fechaExpiracionP = new OracleParameter("fecha_expiracionN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
fechaExpiracionP.Value = fecha_expiracionN;
OracleParameter contenidoP = new OracleParameter("contenidoN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
contenidoP.Value = contenidoN;
OracleParameter encabezadoP = new OracleParameter("encabezadoN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
encabezadoP.Value = encabezadoN;
OracleParameter tituloP = new OracleParameter("tituloN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
tituloP.Value = tituloN;
cmd.Parameters.AddRange(new OracleParameter[] { fechaExpiracionP, contenidoP, encabezadoP, tituloP});
cmd.ExecuteNonQuery();
cmd.Dispose();
base.cerrarConexion();
}
private void button1_Click(object sender, EventArgs e)
{
string connection = "Data Source=ORCL; User Id=pharmacy; Password=pharmacy";
OracleConnection InsertData = new OracleConnection(connection);
InsertData.Open();
OracleParameter empno = new OracleParameter();
empno.OracleDbType = OracleDbType.Int16;
empno.Value = Quantity.Text;
OracleParameter deptno = new OracleParameter();
deptno.OracleDbType = OracleDbType.Int16;
deptno.Value = Price.Text;
OracleCommand insertEmpno = new OracleCommand();
insertEmpno.Connection = InsertData;
insertEmpno.Parameters.Add(empno);
insertEmpno.CommandText = "select ename from employees where empid = 101";
insertEmpno.CommandType = CommandType.Text;
OracleDataReader DataRead = insertEmpno.ExecuteReader();
DataRead.Read();
while (DataRead.Read())
{
listBox1.Items.Add(DataRead.GetString(1));
}
InsertData.Dispose();
}
static void ADO_NET_Example()
{
var cs = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=www.machinejar.com)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=devorc02)));User Id=hr;Password=hr;";
OracleConnection con = new OracleConnection();
con.ConnectionString = cs;
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "HR_DEPTPERCOUNTRY";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter orcParam = new OracleParameter("CNTR_CD", OracleDbType.Varchar2);
orcParam.Size = 50;
orcParam.Value = "UK";
orcParam.Direction = System.Data.ParameterDirection.Input;
cmd.Parameters.Add(orcParam);
OracleParameter orcOutParam = new OracleParameter("RS_CURSOR", OracleDbType.RefCursor);
orcOutParam.Size = 50;
orcOutParam.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(orcOutParam);
cmd.ExecuteNonQuery();
OracleDataReader reader = ((OracleRefCursor)cmd.Parameters["RS_CURSOR"].Value).GetDataReader();
while (reader.Read())
{
Console.WriteLine("Dept Name: {0} | Address: {1}, | Country: {2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
}
}
public int InsertProductCategory(string commandString, string categoryName, string categoryValue, string parentName, string parentValue)
{
int result = -1;
OracleNotSelectConnection notSelectConnection = null;
OracleParameter insertParameter = new OracleParameter(categoryName, categoryValue);
OracleParameter parentParameter = new OracleParameter(parentName, parentValue);
if (parentValue == string.Empty)
{
parentParameter.Value = System.DBNull.Value;
}
try
{
using (notSelectConnection = new OracleNotSelectConnection())
{
result = notSelectConnection.UpdateDatabase(commandString, new OracleParameter[] { insertParameter, parentParameter });
}
}
catch (Exception ex)
{
if (notSelectConnection != null)
{
notSelectConnection.Dispose();
notSelectConnection = null;
}
throw ex;
}
return result;
}
public void adicionarProfesional(string idProfesional,string passwordProfesional,string nombreProfesional,string apellidosProfesional,string cedulaProfesional ,string correoProfesional ,string areaProfesional)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "profesionales_paquete.adicionarProfesional";
OracleParameter idProfesionalP = new OracleParameter("idProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
idProfesionalP.Value = idProfesional;
OracleParameter passwordProfesionalP = new OracleParameter("passwordProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
passwordProfesionalP.Value = passwordProfesional;
OracleParameter nombreProfesionalP = new OracleParameter("nombreProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
nombreProfesionalP.Value = nombreProfesional;
OracleParameter apellidosProfesionalP = new OracleParameter("apellidosProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
apellidosProfesionalP.Value = apellidosProfesional;
OracleParameter cedulaProfesionalP = new OracleParameter("cedulaProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
cedulaProfesionalP.Value = cedulaProfesional;
OracleParameter correoProfesionalP = new OracleParameter("correoProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
correoProfesionalP.Value = correoProfesional;
OracleParameter areaProfesionalP = new OracleParameter("areaProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
areaProfesionalP.Value = areaProfesional;
cmd.Parameters.AddRange(new OracleParameter[] { idProfesionalP, passwordProfesionalP, nombreProfesionalP, apellidosProfesionalP, cedulaProfesionalP, correoProfesionalP, areaProfesionalP });
cmd.ExecuteNonQuery();
cmd.Dispose();
base.cerrarConexion();
}
public List<Profesional> buscarProfesional(string valorBusqueda)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "profesionales_paquete.buscarProfesional";
OracleParameter valorBusquedaP = new OracleParameter("valorBusqueda", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
valorBusquedaP.Value = valorBusqueda;
OracleParameter cursor_datos = new OracleParameter("cursor_datos", OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
cmd.Parameters.AddRange(new OracleParameter[] { valorBusquedaP, cursor_datos});
List<Profesional> profesionalesConsulta = new List<Profesional>();
OracleDataReader lectorDatos = cmd.ExecuteReader();
if (lectorDatos.HasRows)
{
while (lectorDatos.Read())
{
profesionalesConsulta.Add(new Profesional()
{
id = lectorDatos.GetString(0),
password = lectorDatos.GetString(1),
nombre = lectorDatos.GetString(2),
apellidos = lectorDatos.GetString(3),
cedula = lectorDatos.GetString(5),
correo = lectorDatos.GetString(6),
area = lectorDatos.GetString(7)
});
}
lectorDatos.Close();
cmd.Dispose();
base.cerrarConexion();
}
return profesionalesConsulta;
}
public static string f_del_psst_person(string strPersonID)
{
string ret = "";
try
{
string cmd = "update psst_person set per_status='2',per_id_dpis=null where per_id_dpis=" + f_genField(strPersonID, "N");
ret = OPMOracleDb.ExecuteNonQuery(cmd).ToString();
if (ret.ToLower() == "true") {
ret = OPMSqlDb.ExecuteNonQuery(cmd).ToString().ToLower();
cmd = " select nvl(per_name,'') || ' ' || nvl(per_surname) person_name from per_personal where per_id=:PER_ID ";
OracleParameter[] p = new OracleParameter[1];
p[0] = DPISOracleDb.setNumber(":PER_ID", Convert.ToInt64(strPersonID));
DataTable dt = DPISOracleDb.ExecuteTable(cmd, p);
if (dt.Rows.Count > 0)
{
DLLLogFile.AddInterfaceLog("DPIS", "OPM", "per_personal", dt.Rows[0]["person_name"].ToString() + "(per_id =" + strPersonID + ")", "Interface P0101 ข้อมูลข้าราชการ/ลูกจ้างประจำ/พนักงานราชการ > ลบข้อมูล to OPM");
DLLLogFile.AddInterfaceLog("DPIS", "OPM_BO", "per_personal", dt.Rows[0]["person_name"].ToString() + "(per_id =" + strPersonID + ")", "Interface P0101 ข้อมูลข้าราชการ/ลูกจ้างประจำ/พนักงานราชการ > ลบข้อมูล to OPM_BO");
}
}
}
catch (Exception ex) { }
return ret;
}
private int UpdateDataBase(string commandString, OracleParameter[] parameters)
{
OracleNotSelectConnection notSelectConnection = null;
int result = -1;
try
{
using (notSelectConnection = new OracleNotSelectConnection())
{
result = notSelectConnection.UpdateDataBase(commandString, parameters);
}
}
catch (Exception ex)
{
if (notSelectConnection != null)
{
notSelectConnection.Dispose();
notSelectConnection = null;
}
throw ex;
}
return result;
}
public static string CMNSaveDPISOrg(string org_serial, string org_name, string org_type)
{
string ret = "false";
try {
//ค่า org_type ที่ส่งมาจะเป็นค่าที่อยู่ใน OPM.CTLT_ORGANIZE_TYPE.org_type แต่ค่าที่จะใช้ต้องไปคิวรี่หาใน OPM.CTLT_ORGANIZE_TYPE.ot_code
string sql = "select ot_code ";
sql += " from CTLT_ORGANIZE_TYPE ";
sql += " where org_type=:ORG_TYPE";
OracleParameter[] param = new OracleParameter[1];
param[0] = OPMOracleDb.setText(":ORG_TYPE", org_type);
DataTable dtType = OPMOracleDb.ExecuteTable(sql, param);
if (dtType.Rows.Count > 0)
{
string ot_code = dtType.Rows[0]["ot_code"].ToString();
sql = "select org_id from CTLT_ORGANIZE where org_serial=:ORG_SERIAL";
param = new OracleParameter[1];
param[0] = OPMOracleDb.setNumber(":ORG_SERIAL", Convert.ToInt64(org_serial));
DataTable dtOrg = OPMOracleDb.ExecuteTable(sql, param);
if (dtOrg.Rows.Count > 0)
{
long org_id = Convert.ToInt64(dtOrg.Rows[0]["org_id"]);
sql = " select org_id ";
sql += " from per_org ";
sql += " where org_id=:ORG_ID ";
param = new OracleParameter[1];
param[0] = DPISOracleDb.setNumber(":ORG_ID", org_id);
DataTable dt = DPISOracleDb.ExecuteTable(sql, param);
if (dt.Rows.Count > 0)
{
sql = "update per_org ";
sql += " set org_name=:ORG_NAME";
sql += ", ot_code=:ORG_TYPE";
sql += " where org_id=:ORG_ID";
}
else
{
sql = " insert into per_org(org_id,org_name,ot_code)";
sql += " values(:ORG_ID,:ORG_NAME,:ORG_TYPE)";
}
dt.Dispose();
param = new OracleParameter[3];
param[0] = DPISOracleDb.setText(":ORG_NAME", org_name);
param[1] = DPISOracleDb.setText(":ORG_TYPE", ot_code);
param[2] = DPISOracleDb.setNumber(":ORG_ID", Convert.ToInt64(org_id));
ret = DPISOracleDb.ExecuteNonQuery(sql, param);
}
dtOrg.Dispose();
}
dtType.Dispose();
}
catch (Exception ex) {
ret = "false|Exception " + ex.Message + Environment.NewLine + ex.StackTrace;
}
return ret;
}
public int sesionActiva(string toquenId)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "pk_usuarios.usuarioEnSistema";
OracleParameter toquenIdP = new OracleParameter("userTokenID", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
toquenIdP.Value = toquenId;
OracleParameter resultado = new OracleParameter("resultado", OracleDbType.Varchar2, System.Data.ParameterDirection.Output);
resultado.Size = 1024;
cmd.Parameters.AddRange(new OracleParameter[] { toquenIdP, resultado });
try
{
cmd.ExecuteNonQuery();
int respuesta = int.Parse(cmd.Parameters["resultado"].Value.ToString());
cmd.Dispose();
base.cerrarConexion();
return respuesta;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.Message);
throw e;
}
}
public void ReceiveSQL(ref string racoSecur, ref string racoFrom, ref string racoMsg)
{
try
{
if (orcl.State == ConnectionState.Closed)
orcl.Open();
}
catch (Exception ex)
{
//Error happening here
}
myCMD.Connection = orcl;
myCMD.CommandText = "insertintoracoreceived";
myCMD.CommandType = CommandType.StoredProcedure;
OracleParameter security = new OracleParameter("p_security", OracleDbType.Varchar2, ParameterDirection.Input);
OracleParameter phone = new OracleParameter("p_from", OracleDbType.Int64, ParameterDirection.Input);
OracleParameter msg = new OracleParameter("p_msg", OracleDbType.Varchar2, ParameterDirection.Input);
security.Value = racoSecur;
phone.Value = Convert.ToInt64(racoFrom);
msg.Value = racoMsg;
myCMD.Parameters.Add(security);
myCMD.Parameters.Add(phone);
myCMD.Parameters.Add(msg);
try
{
myCMD.ExecuteNonQuery();
}
catch (Exception ex)
{
return;
}
orcl.Close();
}
public SesionUsuario inicioSesion(string userCorreo, string userPassword, string tipoUsuario)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "pk_usuarios.session_login";
OracleParameter userCorreoP = new OracleParameter("correoLogin", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
userCorreoP.Value = userCorreo;
OracleParameter userPasswordP = new OracleParameter("passwordLogin", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
userPasswordP.Value = userPassword;
//cmd.Parameters.Add("co", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
OracleParameter tipoUsuarioP = new OracleParameter("rol", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
tipoUsuarioP.Value = tipoUsuario;
OracleParameter resultado = new OracleParameter("resultado", OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
cmd.Parameters.AddRange(new OracleParameter[] { userCorreoP, userPasswordP, tipoUsuarioP, resultado });
OracleDataReader lectorDatos = cmd.ExecuteReader();
if (lectorDatos.HasRows)
{
lectorDatos.Read();
return new SesionUsuario() { id = lectorDatos.GetString(0), operacionExitosa = true, rol = tipoUsuario };
}
else
return new SesionUsuario() { id = string.Empty, operacionExitosa = false, rol = tipoUsuario };
}
public override void AddParticipantToConversation(User participant, Conversation conversation)
{
try
{
OracleCommand command = CreateCommand("insertar_Usuario_Grupo");
OracleParameter[] parameters = new OracleParameter[]
{
new OracleParameter("id_usuarioV", OracleDbType.Int32) { Value = participant.ID },
new OracleParameter("id_grupoV", OracleDbType.Int32) { Value = conversation.ID }
};
command.Parameters.Add(parameters);
_connection.Open();
command.ExecuteReader();
}
catch (Exception e)
{
throw e;
}
finally
{
_connection.Close();
}
}
private void BindData()
{
string sql = "";
sql += " SELECT EXH_ID, EXH_EFFECTIVEDATE, peh.EX_CODE, pet.EX_NAME, EXH_AMT, EXH_ENDDATE, \n";
sql += " EXH_ORG_NAME , peh.EXH_DOCNO \n";
sql += " , EXH_DOCDATE, EXH_SALARY, EXH_REMARK, EXH_ACTIVE \n";
sql += " , PER_PRENAME.pn_shortname || PERSONAL.Per_Name || ' ' || PERSONAL.PER_SURNAME UPDATE_USER_Fullname \n";
sql += " , peh.UPDATE_DATE \n";
sql += " FROM PER_EXTRAHIS peh \n";
sql += " LEFT JOIN PER_EXTRATYPE pet ON peh.EX_CODE=pet.EX_CODE \n";
sql += " LEFT JOIN PER_PERSONAL PERSONAL ON PERSONAL.PER_ID=peh.Update_User \n";
sql += " LEFT JOIN PER_PRENAME ON PER_PRENAME.pn_code=PERSONAL.PN_CODE \n";
sql += " WHERE peh.EXH_ID= :EXH_ID \n";
//------------สำหรับ connect Oracle----------------
OracleParameter[] param = new OracleParameter[1];
param[0] = OPM_BL.setParameter(":EXH_ID", OracleType.VarChar, EXH_ID);
DataTable dt = OPM_BL.GetDatatable_DPIS(sql, param);
if (dt.Rows.Count > 0)
{
if (Convert.IsDBNull(dt.Rows[0]["EXH_EFFECTIVEDATE"]) == false)
{
txtEXH_EFFECTIVEDATE.Value = Convert.ToDateTime(dt.Rows[0]["EXH_EFFECTIVEDATE"]).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("th-TH"));
}
if (Convert.IsDBNull(dt.Rows[0]["EXH_ENDDATE"]) == false)
{
txtEXH_ENDDATE.Value = Convert.ToDateTime(dt.Rows[0]["EXH_ENDDATE"]).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("th-TH"));
}
txtEX_NAME.Text = dt.Rows[0]["EX_NAME"].ToString();
txtEXH_AMT.Text = dt.Rows[0]["EXH_AMT"].ToString();
txtEXH_ORG_NAME.Text = dt.Rows[0]["EXH_ORG_NAME"].ToString();
txtEXH_SALARY.Text = dt.Rows[0]["EXH_SALARY"].ToString();
txtEXH_DOCNO.Text = dt.Rows[0]["EXH_DOCNO"].ToString();
if (Convert.IsDBNull(dt.Rows[0]["EXH_DOCDATE"]) == false)
{
txtEXH_DOCDATE.Value = Convert.ToDateTime(dt.Rows[0]["EXH_DOCDATE"]).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("th-TH"));
}
txtEXH_REMARK.Text = dt.Rows[0]["EXH_REMARK"].ToString();
if (dt.Rows[0]["EXH_ACTIVE"].ToString() == "1")
{
ck_EXH_ACTIVE_Active.Attributes["Class"] = "icheckbox_flat-green checked";
ck_EXH_ACTIVE_NonActive.Attributes["Class"] = "icheckbox_flat-grey";
}
else
{
ck_EXH_ACTIVE_Active.Attributes["Class"] = "icheckbox_flat-grey";
ck_EXH_ACTIVE_NonActive.Attributes["Class"] = "icheckbox_flat-green checked";
}
txtUPDATE_USER_Fullname.Text = dt.Rows[0]["UPDATE_USER_Fullname"].ToString();
if (Convert.IsDBNull(dt.Rows[0]["UPDATE_DATE"]) == false)
{
txtUPDATE_DATE.Value = Convert.ToDateTime(dt.Rows[0]["UPDATE_DATE"]).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("th-TH"));
}
}
}
public static DataTable GetPhone(int? OrgId)
{
try
{
string SelectPhoneSQL = "SP_GET_ORG_PHONE";
OracleParameter[] ParamArray = new OracleParameter[3];
ParamArray[0] = FRAMEWORK.Utilities.GetOraParam(":p_ORG_ID", OrgId, OracleDbType.Int64, ParameterDirection.Input);
ParamArray[1] = FRAMEWORK.Utilities.GetOraParam(":p_active", null, OracleDbType.Varchar2, ParameterDirection.Input);
ParamArray[2] = FRAMEWORK.Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output);
DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, SelectPhoneSQL, ParamArray);
return (DataTable)ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
}
public TipoIniciativaBE EliminarTipoIniciativa(TipoIniciativaBE entidad)
{
try
{
using (IDbConnection db = new OracleConnection(CadenaConexion))
{
string sp = sPackage + "USP_DEL_TIPO_INICIATIVA";
var parametros = new OracleParameter[1];
parametros[0] = new OracleParameter("pID_TIPO_INICIATIVA", entidad.ID_TIPO_INICIATIVA);
OracleHelper.ExecuteNonQuery(CadenaConexion, CommandType.StoredProcedure, sp, parametros);
entidad.OK = true;
}
}
catch (Exception ex)
{
entidad.extra = ex.Message;
Log.Error(ex);
}
return(entidad);
}
/// <summary>
/// 将 <see cref="SqlParameterCollection"/> 对象中的参数加入到 <see cref="OracleParameterCollection"/> 对象中。
/// </summary>
private void AddDbParameter(SqlParameterCollection sqlParameterCollection, System.Data.OracleClient.OracleParameterCollection oracleParameterCollection)
{
if (sqlParameterCollection == null || oracleParameterCollection == null)
{
return;
}
OracleParameter[] clonedParameters = new OracleParameter[sqlParameterCollection.Count];
for (int i = 0, j = sqlParameterCollection.Count; i < j; i++)
{
OracleParameter p = (OracleParameter)((ICloneable)sqlParameterCollection[i]).Clone();
// 检查未分配值的输入参数,将其分配以DBNull.Value.
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
{
p.Value = DBNull.Value;
}
clonedParameters[i] = p;
}
oracleParameterCollection.AddRange(clonedParameters);
}
public RoadFlow.Data.Model.WorkTime Get(Guid id)
{
string sql = "SELECT * FROM WorkTime WHERE ID=:ID";
OracleParameter[] oracleParameterArray = new OracleParameter[1];
int index = 0;
OracleParameter oracleParameter = new OracleParameter(":ID", OracleDbType.Varchar2);
oracleParameter.Value = (object)id;
oracleParameterArray[index] = oracleParameter;
OracleParameter[] parameter = oracleParameterArray;
OracleDataReader dataReader = this.dbHelper.GetDataReader(sql, parameter);
List <RoadFlow.Data.Model.WorkTime> list = this.DataReaderToList(dataReader);
dataReader.Close();
if (list.Count <= 0)
{
return((RoadFlow.Data.Model.WorkTime)null);
}
return(list[0]);
}
//Insert
public int Insert(Horario t)
{
string query = @"INSERT INTO timetable (hourStart, hourFinish, day)
VALUES( :horaIn, :horaFin, :Day)";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":horaIn", t.HoraInicio);
parameters1[1] = new OracleParameter(":horaFin", t.HoraFin);
parameters1[2] = new OracleParameter(":Day", t.Day);
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteBasicCommand(cmd));
}
catch (Exception ex)
{
throw ex;
}
}
internal OracleQuery buildGetUsersSurrogatesQuery(Int32 userId)
{
String sql = "SELECT SUR.SMS_SURROGATE_ID, SUR.SMS_USER_ID, SUR.SURROGATE_ID, " +
"SUR.SURROGATE_TYPE FROM SMS.SMS_SURROGATE SUR WHERE SUR.SMS_USER_ID = :userId " +
"AND SYSDATE BETWEEN SUR.SURROGATE_START_DATE AND SUR.SURROGATE_END_DATE";
OracleQuery query = new OracleQuery();
query.Command = new OracleCommand(sql);
OracleParameter userIdParam = new OracleParameter("userId", OracleDbType.Decimal);
userIdParam.Value = Convert.ToDecimal(userId);
query.Command.Parameters.Add(userIdParam);
//OracleParameter nowDateParam = new OracleParameter("nowDate", OracleDbType.TimeStamp);
//nowDateParam.Value = DateTime.Now; // edit this when testing to try different dates
//query.Command.Parameters.Add(nowDateParam);
return(query);
}
/// <summary>
/// 获取参数
/// </summary>
/// <param name="paramName">目标字段</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="direction">参数类型</param>
/// <param name="value">值</param>
/// <returns>参数</returns>
public IDbDataParameter MakeParameter(string parameterName, object parameterValue, DbType dbType, Int32 parameterSize, ParameterDirection parameterDirection)
{
OracleParameter result;
if (parameterSize > 0)
{
result = new OracleParameter(parameterName, (OracleDbType)dbType, parameterSize);
}
else
{
result = new OracleParameter(parameterName, (OracleDbType)dbType);
}
result.Direction = parameterDirection;
if (!(parameterDirection == ParameterDirection.Output && parameterValue == null))
{
result.Value = parameterValue;
}
return(result);
}
/// <summary>
/// 分页获取数据列表 适用于SQL2000
/// </summary>
/// <param name="fieldlist">查找的字段</param>
/// <param name="tablename">表名</param>
/// <param name="where">查询条件</param>
/// <param name="orderfield">排序字段 如 id asc,name desc</param>
/// <param name="key">主键</param>
/// <param name="pageindex">页索引</param>
/// <param name="pagesize">每页记录数</param>
/// <returns></returns>
public static DataTable GetDataByPager2000(string fieldlist, string tablename, string where, string orderfield, string key, int pageindex, int pagesize, out int recordcount)
{
string cmd = "ProcCustomPage";
OracleParameter[] para = new OracleParameter[8];
para[0] = new OracleParameter("tbname", tablename);
para[1] = new OracleParameter("FieldKey", key);
para[2] = new OracleParameter("WhereString", where);
para[3] = new OracleParameter("PageSize", pagesize);
para[4] = new OracleParameter("PageCurrent", pageindex);
para[5] = new OracleParameter("FieldOrder", orderfield);
para[6] = new OracleParameter("FieldShow", fieldlist);
para[7] = new OracleParameter("RecordCount", SqlDbType.Int);
para[7].Direction = ParameterDirection.Output;
DataTable dt = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, cmd, para).Tables[0];
recordcount = PublicMethod.GetInt(para[7].Value);
return(dt);
}
public static DataTable GetTVDangerContainerByID(string planNo)
{
string sql = @"SELECT CONTAINERNO, PLANNO
FROM v_Tvdanger_Containers
WHERE (PLANNO = :planNo) order by CONTAINERNO DESC";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("planNo", planNo);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public bool RemoveSP(int id)
{
String sglosa = string.Empty;
var param1 = new OracleParameter("p_id", OracleDbType.Decimal, obj: id, direction: ParameterDirection.Input);
var param2 = new OracleParameter("o_glosa", OracleDbType.Varchar2, 100, obj: sglosa, direction: ParameterDirection.Output);
CommonBC.ModeloSafe.Database.ExecuteSqlCommand(
"BEGIN pkg_ssfMedicoespecialidad.sp_delete(:p_id, :o_glosa); end;",
param1, param2);
System.Diagnostics.Debug.WriteLine("o_glosa: {0}", param2.Value);
if (param2.Value.ToString().ToLower().Contains("xito"))
{
return(true);
}
else
{
return(false);
}
}
public decimal Insert(ToDoInfo todo)
{
try
{
OracleParameter paramReturn = new OracleParameter("p_result", OracleDbType.Decimal, ParameterDirection.Output);
OracleHelper.ExecuteNonQuery(Common.gConnectString, CommandType.StoredProcedure, "pkg_todo.proc_todo_insert",
new OracleParameter("p_type", OracleDbType.Decimal, todo.Type, ParameterDirection.Input),
new OracleParameter("p_code", OracleDbType.Decimal, todo.Code, ParameterDirection.Input),
new OracleParameter("p_content", OracleDbType.Varchar2, todo.Content, ParameterDirection.Input),
new OracleParameter("p_request_by", OracleDbType.Varchar2, todo.Request_By, ParameterDirection.Input),
new OracleParameter("p_request_date", OracleDbType.Date, DateTime.Now, ParameterDirection.Input),
new OracleParameter("p_status", OracleDbType.Decimal, todo.Status, ParameterDirection.Input),
paramReturn);
return(Convert.ToDecimal(paramReturn.Value.ToString()));
}
catch (Exception ex)
{
NaviCommon.Common.log.Error(ex.ToString());
return(-1);
}
}
private static void SetInValueInternalWithOdbType <TKey>(this OracleParametersWrapper <TKey> paramWrapper, TKey key, object value, OracleDbType odbType, ParameterDirection pDir = ParameterDirection.Input)
{
value = value ?? DBNull.Value;
//aggiorno
if (paramWrapper.ContainsKey(key))
{
paramWrapper[key].Value = value;
}
else
{
var p = new OracleParameter
{
Direction = pDir,
ParameterName = "@par_" + key,
OracleDbType = odbType,
Value = value,
};
paramWrapper[key] = p;
}
}
public static IList <IDbDataParameter> TranslateParameters(FindRowIdentityParameters parameters)
{
IList <IDbDataParameter> parameterList = new List <IDbDataParameter>();
IDbDataParameter dbParameter;
dbParameter = new OracleParameter();
dbParameter.ParameterName = "ID_I";
dbParameter.DbType = DbTypeConvertor.ConvertToDbType(typeof(string));
dbParameter.Direction = ParameterDirection.Input;
dbParameter.Value = parameters.Id;
parameterList.Add(dbParameter);
dbParameter = new OracleParameter();
dbParameter.ParameterName = "ROWIDENTITY_I";
dbParameter.DbType = DbTypeConvertor.ConvertToDbType(typeof(string));
dbParameter.Direction = ParameterDirection.Output;
dbParameter.Size = 255;
parameterList.Add(dbParameter);
return(parameterList);
}
public bool ExistsDlbm(string dwbm, string gh, string dlbm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from XT_ZZJG_RYBM");
strSql.Append(ConfigHelper.GetConfigString("OrcDBLinq"));
strSql.Append(" where DWBM=:DWBM AND Trim(DLBM) = Trim(:DLBM)");
OracleParameter[] parameters = null;
if (string.IsNullOrEmpty(gh))
{
parameters = new OracleParameter[] {
new OracleParameter(":DWBM", OracleType.VarChar, 50),
new OracleParameter(":DLBM", OracleType.VarChar, 60)
};
parameters[0].Value = dwbm;
parameters[1].Value = dlbm;
}
else
{
strSql.Append(" AND GH<>:GH");
parameters = new OracleParameter[] {
new OracleParameter(":DWBM", OracleType.Char, 12),
new OracleParameter(":DLBM", OracleType.VarChar, 60),
new OracleParameter(":GH", OracleType.Char, 6)
};
parameters[0].Value = dwbm;
parameters[1].Value = dlbm;
parameters[2].Value = gh;
}
try
{
return(DbHelperOra.Exists(strSql.ToString(), parameters));
}
catch (Exception ex)
{
EDRS.Common.LogHelper.LogError(this.context, "Exception", ex.Message, "public bool ExistsDlbm(string dwbm,string dlbm)", "EDRS.OracleDAL.XT_ZZJG_RYBM", strSql.ToString(), parameters);
}
return(false);
}
public bool CheckIsbn_azonosito(string isbn_azonosito)
{
OracleConnection oc = new OracleConnection();
oc.Open();
OracleCommand command = new OracleCommand()
{
CommandType = System.Data.CommandType.StoredProcedure,
CommandText = "sf_check_isbn_azonosito"
};
OracleParameter correct = new OracleParameter()
{
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.ReturnValue
};
OracleParameter isbn_azonositoParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = "p_isbn_azonosito",
Direction = System.Data.ParameterDirection.Input,
Value = isbn_azonosito
};
command.Parameters.Add(isbn_azonositoParameter);
command.Connection = oc;
try
{
int successful = int.Parse(correct.Value.ToString());
return(successful != 0);
}
catch (Exception)
{
return(false);
}
}
public DataSet TraerPermisosUsuario(string _rut_usuario, string _rut_empresa)
{
OracleCommand cmd = null;
OracleDataReader dr = null;
DataSet datos = new DataSet();
DataTable dt = new DataTable();
try
{
string procedure = "PERMISOS_TRAER_PERMISO_USUARIO";
OracleConnection cnx = Global.CadenaConexionGlobal;
cmd = new OracleCommand(procedure, cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("V_RUT_USUARIO", OracleDbType.NVarchar2).Value = _rut_usuario;
cmd.Parameters.Add("V_RUT_EMPRESA", OracleDbType.NVarchar2).Value = _rut_empresa;
OracleParameter retorno = cmd.Parameters.Add("C_RESULTADO", OracleDbType.RefCursor);
retorno.Direction = ParameterDirection.Output;
cmd.Connection.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dt.Load(dr);
datos.Tables.Add(dt);
}
}
catch (Exception pe)
{
Console.Write(pe.Message);
}
finally
{
cmd.Connection.Close();
}
return(datos);
}
public int Add(string name, int quantity, string uqid)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = openConn();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT INTO items (id, name, quantity, uqid) VALUES (seq_item.nextval, :name, :quantity, :uqid)";
OracleParameter pName = new OracleParameter();
pName.ParameterName = ":name";
pName.OracleDbType = OracleDbType.Varchar2;
pName.Value = name;
OracleParameter pQuantity = new OracleParameter();
pQuantity.ParameterName = ":quantity";
pQuantity.OracleDbType = OracleDbType.Int32;
pQuantity.Value = quantity;
Regex r = new Regex(@"^\w{4}\d{4}$");
if (r.Match(uqid).Success == false)
{
throw new Exception("Nem megfelelő UQID formátum!");
}
OracleParameter pUqid = new OracleParameter();
pUqid.ParameterName = ":uqid";
pUqid.OracleDbType = OracleDbType.Varchar2;
pUqid.Value = uqid;
cmd.Parameters.Add(pName);
cmd.Parameters.Add(pQuantity);
cmd.Parameters.Add(pUqid);
return(cmd.ExecuteNonQuery());
}
public static IDbDataParameter[] GetParameters(DataProvider providerType, int paramsCount)
{
IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
switch (providerType)
{
case DataProvider.SqlServer:
for (int i = 0; i < paramsCount; ++i)
{
idbParams[i] = new SqlParameter();
}
break;
case DataProvider.OleDb:
for (int i = 0; i < paramsCount; ++i)
{
idbParams[i] = new OleDbParameter();
}
break;
case DataProvider.Odbc:
for (int i = 0; i < paramsCount; ++i)
{
idbParams[i] = new OdbcParameter();
}
break;
case DataProvider.Oracle:
for (int i = 0; i < paramsCount; ++i)
{
idbParams[i] = new OracleParameter();
}
break;
default:
idbParams = null;
break;
}
return(idbParams);
}
/// <summary>
/// 查询值列表数据
/// </summary>
/// <param name="query_entity">查询条件实体</param>
/// <param name="pageno">要查询的页</param>
/// <param name="pagesize">页大小</param>
/// <param name="icnt">结果记录条数</param>
/// <returns>值列表数据清单</returns>
internal List<EapValueList> QueryValueList(EapValueList query_entity, int pno, int psize, out int icnt)
{
int para_count = 0;
StringBuilder sql = new StringBuilder(200);
sql.Append("select T.VLIST_ID,T.VLIST_NAME from T_EAP_VALUE_LIST T where 1=1");
if (query_entity.VLIST_ID != string.Empty)
{
sql.Append(" and T.VLIST_ID like '%'||:VLIST_ID||'%'");
para_count++;
}
if (query_entity.VLIST_NAME != string.Empty)
{
sql.Append(" and T.VLIST_NAME like '%'||:VLIST_NAME||'%'");
para_count++;
}
sql.Append(" order by T.VLIST_ID");
OracleParameter[] para = new OracleParameter[para_count];
for (int i = 0; i < para_count; i++)
{
if (query_entity.VLIST_ID != string.Empty)
{
para[i] = new OracleParameter(":VLIST_ID", OracleType.VarChar);
para[i].Value = query_entity.VLIST_ID;
query_entity.VLIST_ID = string.Empty;
continue;
}
if (query_entity.VLIST_NAME != string.Empty)
{
para[i] = new OracleParameter(":VLIST_NAME", OracleType.VarChar);
para[i].Value = query_entity.VLIST_NAME;
query_entity.VLIST_NAME = string.Empty;
continue;
}
}
return Oracle.GetOracle().QueryToPage<EapValueList>(sql, para, pno, psize, out icnt);
}
private List <UserNotifyTo> GetUserNotifyToList(int id)
{
if (id <= 0)
{
return(null);
}
var WhereClause = new StringBuilder();
var parameters = new List <OracleParameter>();
var parm = new OracleParameter {
ParameterName = "InsId", OracleDbType = OracleDbType.Int32, Value = id
};
parameters.Add(parm);
var strSqlStatment = new StringBuilder();
strSqlStatment.Append($"select t.to_id, t.ins_id, t.role_id,r.rolename from users_instruct_to t, approle r where t.role_id = r.roleid and t.ins_id = :InsId ");
var masterDataTable = this.db.GetData(strSqlStatment.ToString(), parameters);
if (masterDataTable == null)
{
return(null);
}
if (masterDataTable.Rows.Count == 0)
{
return(null);
}
var results = new List <UserNotifyTo>();
foreach (DataRow row in masterDataTable.Rows)
{
var obj = new UserNotifyTo();
obj.Id = row["to_id"] == DBNull.Value ? -1 : int.Parse(row["to_id"].ToString());
obj.UserNotifyId = row["ins_id"] == DBNull.Value ? -1 : int.Parse(row["ins_id"].ToString());
obj.Role.Id = row["role_id"] == DBNull.Value ? -1 : int.Parse(row["role_id"].ToString());
obj.Role.Name = row["rolename"] == DBNull.Value ? string.Empty : row["rolename"].ToString();
results.Add(obj);
}
return(results);
}
public List <RoadFlow.Data.Model.WorkFlow> GetPagerData(out long count, int pageSize, int pageNumber, string userid = "", string typeid = "", string name = "", string order = "")
{
//IL_002e: Unknown result type (might be due to invalid IL or missing references)
//IL_0033: Unknown result type (might be due to invalid IL or missing references)
//IL_003b: Expected O, but got Unknown
//IL_0040: Expected O, but got Unknown
//IL_005d: Unknown result type (might be due to invalid IL or missing references)
//IL_0062: Unknown result type (might be due to invalid IL or missing references)
//IL_006a: Expected O, but got Unknown
//IL_006f: Expected O, but got Unknown
StringBuilder stringBuilder = new StringBuilder("AND Status!=4 ");
List <OracleParameter> list = new List <OracleParameter>();
if (!userid.IsNullOrEmpty())
{
stringBuilder.Append("AND INSTR(Manager,:Manager,1,1)>0 ");
List <OracleParameter> list2 = list;
OracleParameter val = new OracleParameter(":Manager", 126);
((DbParameter)val).Value = userid;
list2.Add(val);
}
if (!name.IsNullOrEmpty())
{
stringBuilder.Append("AND INSTR(Name,:Name,1,1)>0 ");
List <OracleParameter> list3 = list;
OracleParameter val2 = new OracleParameter(":Name", 126);
((DbParameter)val2).Value = name;
list3.Add(val2);
}
if (!typeid.IsNullOrEmpty())
{
stringBuilder.Append("AND Type IN (" + Tools.GetSqlInString(typeid) + ")");
}
string paerSql = dbHelper.GetPaerSql("WorkFlow", "ID,Name,Type,Manager,InstanceManager,CreateDate,CreateUserID,'' DesignJSON,InstallDate,InstallUserID,'' RunJSON,Status", stringBuilder.ToString(), order.IsNullOrEmpty() ? "CreateDate DESC" : order, pageSize, pageNumber, out count, list.ToArray());
OracleDataReader dataReader = dbHelper.GetDataReader(paerSql, list.ToArray());
List <RoadFlow.Data.Model.WorkFlow> result = DataReaderToList(dataReader);
((DbDataReader)dataReader).Close();
return(result);
}
public static EntityObject_MCE_B_ASSETS_FILETEMPLATE Get(EntityObject_MCE_B_ASSETS_FILETEMPLATE obj, IDbTransaction trans)
{
//平台自动生成代码
string sSql = "select COMMENTS, ID, NAME from MCE_B_ASSETS_FILETEMPLATE where ID = :ID ";
#region Parameters Block
OracleParameter[] parameters = new OracleParameter[1];
parameters[0] = new OracleParameter();
parameters[0].ParameterName = ":ID";
parameters[0].Size = 36;
parameters[0].OracleType = System.Data.OracleClient.OracleType.Char;
parameters[0].Value = obj.ID;
#endregion
#region Push Block
EntityObject_MCE_B_ASSETS_FILETEMPLATE dataObj = null;
ArrayList objArray = new ArrayList();
IDataReader sqlReader = IDALProvider.IDAL.ExecuteReader(sSql, parameters, trans);
while (sqlReader.Read())
{
dataObj = new EntityObject_MCE_B_ASSETS_FILETEMPLATE();
if (!sqlReader.IsDBNull(0))
{
dataObj.COMMENTS = sqlReader.GetString(0);
}
if (!sqlReader.IsDBNull(1))
{
dataObj.ID = sqlReader.GetString(1);
}
if (!sqlReader.IsDBNull(2))
{
dataObj.NAME = sqlReader.GetString(2);
}
objArray.Add(dataObj);
}
sqlReader.Close();
#endregion
EntityObject_MCE_B_ASSETS_FILETEMPLATE[] objs = new EntityObject_MCE_B_ASSETS_FILETEMPLATE[objArray.Count];
objArray.CopyTo(0, objs, 0, objs.Length);
return(objs.Length == 0 ? null:objs[0]);
}
public IEnumerable<Estudiante> GetEstudiantesBD2(int idPar)
{
string strCon = _configuration.GetConnectionString("OracleDBConnection");
// string strCon = "Data Source=DW; user id=webacad; password=cefalopatia; Min Pool Size=1";
List<Estudiante> listaEstudiantes = new List<Estudiante>();
using (OracleConnection connection = new OracleConnection(strCon))
using (OracleCommand command = connection.CreateCommand())
{
try
{
connection.Open();
command.CommandText = $"select * from estudiante where ID_ESTUDIANTE = :id";
OracleParameter id = new OracleParameter();
id.ParameterName = "id";
id.Value = idPar;
command.Parameters.Add(id);
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
listaEstudiantes.Add(new Estudiante()
{
ID_ESTUDIANTE = reader.GetInt32(0),
CI = reader.GetString(1),
NOMBRES = reader.GetString(2),
APELLIDOS = reader.GetString(3),
FECHA_NACIMIENTO = reader.GetDateTime(4)
});
}
reader.Dispose();
}
catch (Exception err)
{
throw new Exception(err.Message);
}
}
return listaEstudiantes;
}
///// <summary>
///// 发票开具
///// </summary>
///// <param name="invdata"></param>
///// <param name="ifprt"></param>
///// <param name="pjlx"></param>
///// <param name="bz"></param>
///// <returns></returns>
//public static string Invoice(string invdata,int ifprt,string pjlx,string bz)
//{
// StringBuilder sb_res = new StringBuilder();
// try
// {
// PZrPj(invdata, ifprt, pjlx, bz, sb_res);
// }
// catch (Exception ee)
// {
// XtraMessageBox.Show(ee.ToString(),"错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
// }
// return sb_res.ToString();
//}
///// <summary>
///// 作废财政发票
///// </summary>
///// <param name="pjh"></param>
///// <returns></returns>
//public static string Remove(string pjh)
//{
// StringBuilder sb_res = new StringBuilder();
// PDelPj(pjh, sb_res);
// return sb_res.ToString();
//}
///// <summary>
///// 作废财政发票
///// </summary>
///// <param name="zch"></param>
///// <param name="pjlx"></param>
///// <param name="pjh"></param>
///// <returns></returns>
//public static int Remove(string zch,string pjlx,string pjh)
//{
// string s_content = "票据类型=" + pjlx + "|票据号=" + pjh + "|注册号=" + zch;
// //MessageBox.Show(s_content);
// string retstr = Remove(s_content);
// if (retstr.IndexOf("成功") >= 0)
// {
// XtraMessageBox.Show("作废财政发票成功!\r\n" + "票据类型:" + pjlx + ",票据号:" + pjh + ",注册号:" + zch, "提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
// return 1;
// }
// else
// {
// LogUtils.Error("作废财政发票失败:\r\n" + "票据号:" + pjh + "注册号:" + zch + "\r\n" + "返回字符串:" + retstr);
// XtraMessageBox.Show("作废财政发票失败!请与管理员联系!\r\n" + retstr,"提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
// return -1;
// }
//}
///// <summary>
///// 退费
///// </summary>
///// <param name="OldPjlx"></param>
///// <param name="OldPjh"></param>
///// <param name="OldZch"></param>
///// <param name="NewPjlx"></param>
///// <param name="tkitem"></param>
///// <param name="aQt"></param>
///// <param name="res"></param>
///// <returns></returns>
//public static int Refund(string OldPjlx,string OldPjh,string OldZch,string tkitem,string aQt,string fa001,string NewPjh,decimal hjje)
//{
// StringBuilder sb_res = new StringBuilder();
// PZrTkkp(OldPjh,OldPjlx, OldZch, Envior.FIN_INVOICE_TYPE, tkitem, aQt, sb_res);
// if (sb_res.ToString().IndexOf("成功") >= 0) //退费发票开具成功
// {
// if (FinInvoiceLog(fa001, Envior.FIN_INVOICE_TYPE, NewPjh, "", 0 - Math.Abs(hjje),Envior.cur_userId) > 0)
// {
// XtraMessageBox.Show("发票开具成功!\r\n" + "发票类型:" + Envior.FIN_INVOICE_TYPE + "\r\n" + "发票号:" + NewPjh, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
// return 1;
// }
// else
// {
// XtraMessageBox.Show("发票开具成功!但记录日志失败，请与管理员联系！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return 1;
// }
// }
// else
// {
// XtraMessageBox.Show("发票开具失败!\r\n" + sb_res.ToString(),"错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
// return -1;
// }
//}
///// <summary>
///// 根据结算流水号开具发票
///// </summary>
///// <param name="fa001"></param>
///// <returns></returns>
//public static int Invoice(string fa001)
//{
// OracleParameter op_fa001 = new OracleParameter("fa001", OracleDbType.Varchar2, 10);
// op_fa001.Direction = ParameterDirection.Input;
// op_fa001.Value = fa001;
// OracleDataReader reader_fa01 = SqlAssist.ExecuteReader("select fa003,fa180 from fa01 where fa001 = :fa001",new OracleParameter[] { op_fa001});
// string s_head = string.Empty;
// string s_memo = string.Empty; //备注
// decimal dec_hjje = decimal.Zero;
// while (reader_fa01.Read())
// {
// //读取交款人
// s_head = Envior.FIN_INVOICE_TITLE + "=" + reader_fa01["FA003"].ToString() + " ";
// s_memo = reader_fa01["FA180"].ToString();
// }
// reader_fa01.Dispose();
// string s_sql = @"select sa002,
// sa004,
// sa003,
// price,
// nums,
// sa020,
// sa007,
// pkg_business.fun_GetInvoiceCode(sa002,sa004) invcode
// from v_sa01
// where sa010 = :fa001 order by sa001";
// OracleDataReader reader_sa01 = SqlAssist.ExecuteReader(s_sql,new OracleParameter[] { op_fa001});
// StringBuilder sb_detail = new StringBuilder(100);
// while (reader_sa01.Read())
// {
// if (reader_sa01["SA020"].ToString() != "F") continue; //如果不是财政发票,忽略
// sb_detail.Append("收费项目=" + reader_sa01["INVCODE"].ToString() + " " + "计费数量=" + reader_sa01["NUMS"].ToString() + " " + "收费标准=" + reader_sa01["PRICE"].ToString() + " " + "金额=" + reader_sa01["SA007"].ToString() + " ");
// dec_hjje += Convert.ToDecimal(reader_sa01["SA007"]);
// }
// reader_sa01.Dispose();
// string vContent = "<&票据><&票据头>" + s_head + " " + "</&票据头>" + "<&收费项目>" + sb_detail.ToString() + "</&收费项目></&票据>";
// string retstr = Invoice(vContent, 1,Envior.FIN_INVOICE_TYPE, s_memo);
// if(retstr.IndexOf("成功") >= 0)
// {
// //TODO 4. 记录财政发票日志
// string s_info = retstr.Substring(3);
// string[] s_arry = s_info.Split(',');
// if(s_arry.Length >= 4)
// {
// string s_pjlx = s_arry[0]; //票据类型
// string s_pjh = s_arry[1]; //发票号
// string s_zch = s_arry[3]; //注册号
// if(FinInvoiceLog(fa001,s_pjlx,s_pjh,s_zch,dec_hjje,Envior.cur_userId) > 0)
// {
// XtraMessageBox.Show("发票开具成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
// return 1;
// }
// else
// {
// XtraMessageBox.Show("发票开具成功!!!但记录日志失败，请与管理员联系！","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
// XtraMessageBox.Show("票据类型:" + s_pjlx + "\r\n" + "票据号:" + s_pjh + "\r\n注册号:" + s_zch + "\r\n");
// return 1;
// }
// }
// else
// {
// XtraMessageBox.Show("发票开具成功!但记录日志出现错误，请与管理员联系!！\r\n" + retstr + "数组大小:" + s_arry.Length.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return 1;
// }
// }
// else
// {
// XtraMessageBox.Show("发票开具失败!\r\n" + retstr,"提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
// return -1;
// }
//}
/// <summary>
/// 财政发票开票日志
/// </summary>
/// <param name="fa001">结算流水号</param>
/// <param name="fplx">发票类型</param>
/// <param name="fph">发票号</param>
/// <param name="zch">注册号</param>
/// <returns></returns>
public static int FinInvoiceLog(string fa001, string fplx, string fph, string zch, decimal hjje, string kpr)
{
//逝者编号
OracleParameter op_fa001 = new OracleParameter("ic_fa001", OracleDbType.Varchar2, 10);
op_fa001.Direction = ParameterDirection.Input;
op_fa001.Value = fa001;
//票据类型
OracleParameter op_pjlx = new OracleParameter("ic_billCode", OracleDbType.Varchar2, 10);
op_pjlx.Direction = ParameterDirection.Input;
op_pjlx.Value = fplx;
//票号
OracleParameter op_fph = new OracleParameter("ic_billNo", OracleDbType.Varchar2, 10);
op_fph.Direction = ParameterDirection.Input;
op_fph.Value = fph;
//注册号
OracleParameter op_zch = new OracleParameter("ic_zch", OracleDbType.Varchar2, 10);
op_zch.Direction = ParameterDirection.Input;
op_zch.Value = zch;
//合计金额
OracleParameter op_hjje = new OracleParameter("in_hjje", OracleDbType.Decimal);
op_hjje.Direction = ParameterDirection.Input;
op_hjje.Value = hjje;
//开票人
OracleParameter op_kpr = new OracleParameter("ic_kpr", OracleDbType.Varchar2, 10);
op_kpr.Direction = ParameterDirection.Input;
op_kpr.Value = kpr;
return(SqlAssist.ExecuteProcedure("pkg_business.prc_FinInvoiceLog",
new OracleParameter[] { op_fa001, op_pjlx, op_fph, op_zch, op_hjje, op_kpr }));
}
OracleParameter GetOracleParameter(OracleDbType OracleType, DataRow row, string parameterName, ParameterDirection direction)
{
OracleParameter oracleParameter;
if (OracleType == 0)
{
var dataType = row["DATA_TYPE"].ToString();
OracleType = GetOracleDbType(dataType);
oracleParameter = new OracleParameter(parameterName, OracleType)
{
Direction = direction
};
}
else
{
oracleParameter = new OracleParameter(parameterName, OracleType)
{
Direction = direction
};
}
var size = "";
try
{
var sizeVal = row["SIZE"];
if (sizeVal != null)
{
size = sizeVal.ToString();
}
}
catch (Exception)
{
size = string.Empty;
}
var sizeValue = string.IsNullOrEmpty(size) ? GetSizeForType(OracleType) : int.Parse(size);
oracleParameter.Size = sizeValue;
return(oracleParameter);
}
public int Delete(Konyvek record)
{
OracleConnection oc = GetOracleConnection();
oc.Open();
OracleTransaction ot = oc.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
OracleCommand command = new OracleCommand()
{
CommandType = System.Data.CommandType.Text,
CommandText = "DELETE FROM konyvek WHERE isbn_azonosito = :isbn_azonosito"
};
OracleParameter isbn_azonositoParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":isbn_azonosito",
Direction = System.Data.ParameterDirection.Input,
Value = record.Isbn_azonosito
};
command.Parameters.Add(isbn_azonositoParameter);
command.Connection = oc;
command.Transaction = ot;
int affectedRows = 0;
try
{
affectedRows = command.ExecuteNonQuery();
}
catch (Exception)
{
ot.Rollback();
}
oc.Close();
return(affectedRows);
}
/// <summary>
/// 转换对应的数据库参数
/// </summary>
/// <param name="dbParameter">参数</param>
/// <returns></returns>
public static DbParameter[] ToDbParameter(DbParameter[] dbParameter)
{
int i = 0;
int size = dbParameter.Length;
DbParameter[] _dbParameter = null;
switch (DbHelper.DbType)
{
case DatabaseType.SqlServer:
_dbParameter = new SqlParameter[size];
while (i < size)
{
_dbParameter[i] = new SqlParameter(dbParameter[i].ParameterName, dbParameter[i].Value);
i++;
}
break;
case DatabaseType.MySql:
_dbParameter = new MySqlParameter[size];
while (i < size)
{
_dbParameter[i] = new MySqlParameter(dbParameter[i].ParameterName, dbParameter[i].Value);
i++;
}
break;
case DatabaseType.Oracle:
_dbParameter = new OracleParameter[size];
while (i < size)
{
_dbParameter[i] = new OracleParameter(dbParameter[i].ParameterName, dbParameter[i].Value);
i++;
}
break;
default:
throw new Exception("数据库类型目前不支持！");
}
return(_dbParameter);
}
public void GenerateGroupSummaries(string loggedUser, Int32 groupId, out Int32 iErrorId)
{
try
{
OpenConnection(out iErrorId, out sErrorMsg);
ErrorId = new OracleParameter();
OracleParameter pLoggedUser = new OracleParameter();
OracleParameter pGroupId = new OracleParameter();
orlCmd = new OracleCommand("pkg_maint_royaltor_groupings.p_generate_group_summary", orlConn);
orlCmd.CommandType = System.Data.CommandType.StoredProcedure;
pLoggedUser.OracleDbType = OracleDbType.Varchar2;
pLoggedUser.Direction = ParameterDirection.Input;
pLoggedUser.Value = loggedUser;
orlCmd.Parameters.Add(pLoggedUser);
pGroupId.OracleDbType = OracleDbType.Int32;
pGroupId.Direction = ParameterDirection.Input;
pGroupId.Value = groupId;
orlCmd.Parameters.Add(pGroupId);
ErrorId.OracleDbType = OracleDbType.Int32;
ErrorId.Direction = ParameterDirection.Output;
ErrorId.ParameterName = "ErrorId";
orlCmd.Parameters.Add(ErrorId);
orlCmd.ExecuteNonQuery();
iErrorId = Convert.ToInt32(orlCmd.Parameters["ErrorId"].Value.ToString());
}
catch (Exception ex)
{
iErrorId = 2;
}
finally
{
CloseConnection();
}
}
public DataSet TraerTodosTareaSubtareas(int id_tarea, string _rut_empresa)
{
OracleCommand cmd = null;
OracleDataReader dr = null;
DataSet datos = new DataSet();
DataTable dt = new DataTable();
try
{
string procedure = "TAREA_TRAER_TODAS_SUBTAREAS";
OracleConnection cnx = Global.CadenaConexionGlobal;
cmd = new OracleCommand(procedure, cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("V_ID_TAREA", OracleDbType.Int32).Value = id_tarea;
cmd.Parameters.Add("V_RUT_EMPRESA", OracleDbType.NVarchar2).Value = _rut_empresa;
OracleParameter retorno = cmd.Parameters.Add("C_TAREAS", OracleDbType.RefCursor);
retorno.Direction = ParameterDirection.Output;
cmd.Connection.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dt.Load(dr);
datos.Tables.Add(dt);
}
}
catch (Exception pe)
{
Console.Write(pe.Message);
}
finally
{
cmd.Connection.Close();
}
return(datos);
}
public static IDbDataParameter CreateDataParameter(string parameterName, object theValue)
{
if (DataProvider.ProviderType == DataAccessProviderType.SqlClient)
{
return(new SqlParameter(parameterName, theValue));
}
else if (DataProvider.ProviderType == DataAccessProviderType.OleDb)
{
return(new OleDbParameter(parameterName, theValue));
}
else
{
if (parameterName != "Result")
{
if (parameterName != "errmsg")
{
OracleParameter OracleParam;
OracleParam = new OracleParameter(parameterName, theValue);
OracleParam.Direction = ParameterDirection.Input;
return(OracleParam);
}
else
{
OracleParameter OracleParam;
OracleParam = new OracleParameter(parameterName, Oracle.DataAccess.Client.OracleDbType.Int32);
OracleParam.Direction = ParameterDirection.Output;
return(OracleParam);
}
}
else
{
OracleParameter OracleParamReturn;
OracleParamReturn = new OracleParameter(parameterName, Oracle.DataAccess.Client.OracleDbType.RefCursor);
OracleParamReturn.Direction = ParameterDirection.Output;
return(OracleParamReturn);
}
}
}
public static OracleParameter CreateParameter(ParameterDirection direction, string paramName, OracleDbType dbType, int size, object value)
{
OracleParameter param = new OracleParameter(paramName, dbType, size);
param.Value = value;
param.Direction = direction;
return param;
}
