public List<Employee> datos()
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "myProcedure";
cmd.Parameters.Add("co", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
OracleDataReader dr = cmd.ExecuteReader();
List<Employee> employees = new List<Employee>();
if (dr.HasRows)
{
while (dr.Read())
{
int id = dr.GetInt32(0);
string first_name = dr.GetString(1);
string last_name = dr.GetString(2);
employees.Add(new Employee(id, first_name, last_name));
Console.WriteLine(dr.GetString(1));
}
}
else
{
Console.WriteLine("No data found");
}
dr.Close();
cmd.Dispose();
base.cerrarConexion();
return employees;
}
private void button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(this.textBox1.Text) || string.IsNullOrWhiteSpace(this.textBox2.Text) || string.IsNullOrWhiteSpace(richTextBox1.Text))
{
MessageBox.Show("Fill all the requierd field correctly", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
try
{
conn.Open();
string sqlquery = "INSERT INTO MFG_INFO(MGF_NAME,MFG_ADD,MFG_PHN) VALUES('" + textBox1.Text + "','" + richTextBox1.Text + "','" + textBox2.Text + "')";
OracleCommand cmd = new OracleCommand(sqlquery,conn);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
cmd.Dispose();
this.Hide();
adminControls adc = new adminControls();
adc.Show();
}
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
}
}
public static Employee GetInfo(OracleDataReader reader, DPFP.Sample Sample, string Finger)
{
OracleConnection conn = new OracleConnection(ConStr);
Employee _info = null;
DPFP.Template _template = null;
bool IsFound = false;
if (reader[Finger] != DBNull.Value)
{
_template = Util.ProcessDBTemplate((byte[])reader[Finger]);
IsFound = Util.Verify(Sample, _template);
}
if (IsFound == true)
{
string sqlEmp = "select * from employees where Empl_Id_No=" + reader["Empl_Id_No"];
OracleCommand cmd = new OracleCommand(sqlEmp, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
_info = new Employee();
_info.Empl_Id_No = Convert.ToInt32(reader["Empl_Id_No"]);
_info.Empl_Name = (string)odr["Empl_Name"];
_info.Empl_Deptname = (string)odr["Empl_Deptname"];
_info.Shift_Id = Convert.ToInt32(odr["Shift_Id"]);
}
odr.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return _info;
}
private void button3_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Are You Sure", "Some Title", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{
conn.Open();
string sqlquery = "DELETE FROM MED_INFO WHERE MED_ID='" + ni + "'";
OracleCommand cmd = new OracleCommand(sqlquery, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
sqlquery = "DELETE FROM MED_STORE WHERE MED_ID='" + ni + "'";
cmd = new OracleCommand(sqlquery, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
this.Hide();
}
else if (dialogResult == DialogResult.No)
{
///
}
}
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 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();
}
/// <summary>
/// 执行数据库事务查询操作,返回受影响的行数
/// </summary>
/// <param name="transaction">数据库事务对象</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>
/// <param name="commandParameters">命令参数集合</param>
/// <returns>当前事务查询操作影响的数据行数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand command = new OracleCommand();
OracleConnection connection = transaction.Connection;
int result = 0;
try
{
PrepareCommand(command, connection, transaction, cmdType, cmdText, commandParameters);
result = command.ExecuteNonQuery();
command.Parameters.Clear();
}
catch
{
throw;
}
finally
{
transaction.Dispose();
command.Dispose();
connection.Close();
connection.Dispose();
}
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();
}
void AutocompleteText()
{
textBox5.AutoCompleteMode = AutoCompleteMode.Suggest;
textBox5.AutoCompleteSource = AutoCompleteSource.CustomSource;
AutoCompleteStringCollection coll = new AutoCompleteStringCollection();
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string sqluery = "SELECT * FROM MED_INFO WHERE MED_MGF='" + comboBox1.Text + "' ORDER BY MED_NAME";
OracleCommand cd = new OracleCommand(sqluery, conn);
OracleDataReader r;
r = cd.ExecuteReader();
while(r.Read())
{
string sn = r.GetString(1);
coll.Add(sn);
}
r.Dispose();
cd.Dispose();
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
textBox5.AutoCompleteCustomSource = coll;
}
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 int ExecuteNonQuery(List<string> sqlList)
{
int rowsUpdated = 0;
try
{
var con = CONNECTION.OpenCon();
foreach (var sql in sqlList)
{
var cmd = new OracleCommand(sql, con);
rowsUpdated += cmd.ExecuteNonQuery();
cmd.Dispose();
}
CONNECTION.CloseCon(con);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteNonQuery Error!",
Ex = ex,
});
return -1;
}
return rowsUpdated;
}
/// <summary>
/// Метод, реализующий insert-запрос в базу данных Oracle
/// </summary>
/// <param name="cmdQuery">Текст sql-запроса</param>
public static void Insert(string cmdQuery)
{
_logger.WriteLine(cmdQuery);
try
{
_open();
OracleCommand cmd = new OracleCommand(cmdQuery, _conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
_logger.WriteLine("Удачно!");
}
catch (TimeoutException)
{
throw;
}
catch (OracleException ex)
{
string mess = "Ошибка в запросе к БД!" + Environment.NewLine + ex;
Message.Show(mess);
_logger.WriteError(mess);
throw new BadQueryExeption();
}
catch (Exception ex)
{
string mess = "Ошибка в запросе к БД!" + Environment.NewLine + ex;
_logger.WriteError(mess);
Message.Show(mess);
}
finally
{
_close();
}
}
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;
}
//____________________________________________________________
//Lista "objeto" con StoredProcedure
public static List<LocalidadDTO> ListarLocalidadSP()
{
OracleCommand cmd = new OracleCommand();
List<LocalidadDTO> ListaLocalidades = new List<LocalidadDTO>();
cmd.Connection = new OracleConnection(ConfigurationManager.ConnectionStrings["CHAPA"].ConnectionString);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "LOCALIDAD_SELECT";
cmd.BindByName = true;
cmd.Parameters.Add("o_c_loc", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Connection.Open();
OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
LocalidadDTO objLoc = new LocalidadDTO();
objLoc.locId = Convert.ToInt32(odr[0]);
objLoc.locDescripcion = odr[1].ToString();
ListaLocalidades.Add(objLoc);
}
cmd.Connection.Close();
cmd.Dispose();
return ListaLocalidades;
}
/// <summary>
/// 查询
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns></returns>
private static DataTable Select(string sql)
{
OracleCommand cmd = new OracleCommand(sql, Connection);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
oda.Fill(dt);
cmd.Dispose();
return dt;
}
public static string GetLocalRol(int ID_REL)
{
OracleDataReader dr;
OracleCommand cmd = new OracleCommand();
string res = null;
string sql = string.Empty;
try
{
cmd.Connection = Conexao.GetInstance();
cmd.CommandType = CommandType.Text;
sql = "SELECT LOCAL, NOME " +
"FROM RELatorio " +
"WHERE ID = '" + ID_REL + "'";
cmd.CommandText = sql;
dr = cmd.ExecuteReader();
if (dr.Read())
{
res = dr["LOCAL"].ToString() + dr["NOME"].ToString();
}
//=====================================================================|
//Finaliza ds
cmd.Dispose();
return res;
}
catch (Exception ex)
{
sErrMsg = "Atenção, a função de recuperação de dados (Certificados/Etiquetas) " +
"detectou o seguinte problema:" +
"\r\n" + "\r\n" +
"Descrição : " + ex.Message.ToString() + "\r\n" +
"Origem : " + ex.Source.ToString();
sErrCap = "Procedimento : " + ex.TargetSite.ToString();
MessageBox.Show(sErrMsg, sErrCap, MessageBoxButtons.OK,
MessageBoxIcon.Error);
cmd.Dispose();
return null;
}
}
/// <summary>
/// Метод, реализующий UPDATE-запрос
/// </summary>
/// <param name="cmdQuery">Текст UPDATE-запроса</param>
public static void Update(string cmdQuery)
{
_open();
_logger.WriteLine(cmdQuery);
OracleCommand cmd = new OracleCommand(cmdQuery, _conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
_close();
}
private void button1_Click(object sender, EventArgs e)
{
string mgfnm = comboBox1.GetItemText(this.comboBox1.SelectedItem);
string grp = comboBox2.GetItemText(this.comboBox2.SelectedItem);
string mtype = comboBox3.GetItemText(this.comboBox3.SelectedItem);
string nid = MedIDgen.IdGenrator(mgfnm);
string mnm = textBox1.Text;
string mbt = textBox3.Text;
string mcp = textBox4.Text;
string msp = textBox2.Text;
string mnt = richTextBox1.Text;
string stg = textBox6.Text;
conn.Open();
try
{
OracleCommand cmd = new OracleCommand("INSERT INTO MED_INFO(MED_ID,MED_NAME,MED_STG,MED_MGF,MED_BATCH,MED_GROUP,MED_TYPE,COST_PRICE,SELL_PRICE,NOTES) VALUES('" + nid + "','" + mnm + "','"+stg+"','" + mgfnm + "','" + mbt + "','" + grp + "','" + mtype + "','" + mcp + "','" + msp + "','" + mnt + "')", conn);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
MessageBox.Show("DONE");
cmd.Dispose();
this.Hide();
}
}
catch(Exception exe){
MessageBox.Show(exe.Message);
}
try
{
OracleCommand cmd = new OracleCommand("INSERT INTO MED_STORE(MED_ID,MED_NAME,MED_QNTY,DAM_QNTY,RE_LEVEL) VALUES('" + nid + "','" + mnm + "','" + 0 + "','" + 0 + "','" + 0 + "')", conn);
int j = cmd.ExecuteNonQuery();
if (j > 0)
{
cmd.Dispose();
this.Hide();
adminControls adm = new adminControls();
adm.Show();
MedInfoView miv = new MedInfoView(nid);
miv.Show();
}
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
conn.Close();
}
public AddAnEmployee()
{
InitializeComponent();
ex_empid.Hide();
ex_ename.Hide();
ex_inss.Hide();
ex_payroll.Hide();
ex_sal.Hide();
msg_bno.Show();
msg_bno.Text = "Please Select A Branch";
msg_mgr.Show();
msg_mgr.Text = "Please Select A Manager";
// string _sql;
try
{
DataSet _ds;
OracleConnection _connObj = new OracleConnection(_connstring);
_ds = new DataSet();
_connObj.Open();
OracleCommand ShowBNO = new OracleCommand();
ShowBNO.Connection = _connObj;
ShowBNO.CommandText = "select bno from branch";
ShowBNO.CommandType = CommandType.Text;
OracleDataReader DataRead = ShowBNO.ExecuteReader();
DataRead.Read();
while (DataRead.Read())
{
bno.Items.Add(DataRead.GetString(1));
}
ShowBNO.Dispose();
//_sql = "SELECT * FROM branch";
// OracleDataAdapter _adapterObj = new OracleDataAdapter(_sql, _connObj);
// _adapterObj.Fill(_ds);
_connObj.Close();
_connObj.Dispose();
_connObj = null;
bno.DataSource = _ds.Tables[0] ;
dataGridView1.DataSource = _ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
//____________________________________________________________
//Insert con SP
public static void InsertarPrueba(LocalidadDTO prueba)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = new OracleConnection(ConfigurationManager.ConnectionStrings["CHAPA"].ConnectionString);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PRUEBA_INSERT";
cmd.BindByName = true;
cmd.Parameters.Add(new OracleParameter("valID", prueba.locId));
cmd.Parameters.Add(new OracleParameter("valDESCRI", prueba.locDescripcion));
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.Dispose();
}
/// <summary>
/// Opens the data stored under the given ID for reading
/// </summary>
public Task <Stream> Read(string id)
{
try
{
// update last read time quickly
UpdateLastReadTime(id);
OracleDbConnection connection = null;
OracleCommand command = null;
OracleDataReader reader = null;
try
{
connection = _connectionHelper.GetConnection();
command = connection.CreateCommand();
command.CommandText = $"SELECT data FROM {_table} WHERE id = :id";
command.Parameters.Add("id", id);
command.InitialLOBFetchSize = 4000;
reader = command.ExecuteReader(CommandBehavior.SingleRow);
if (!reader.Read())
{
throw new ArgumentException($"DataBus row with ID {id} not found");
}
var blob = reader.GetOracleBlob(0);
return(Task.FromResult <Stream>(new StreamWrapper(blob, /* dispose with stream: */ reader, command, connection)));
}
catch
{
// if something of the above fails, we did not pass ownership to someone who can dispose it... therefore:
reader?.Dispose();
command?.Dispose();
connection?.Dispose();
throw;
}
}
catch (Exception exception)
{
// Wrap in AggregateException to comply with Rebus contract. Tests do look for this specific exception type.
throw new AggregateException(exception);
}
}
public static Boolean ExecuteListadeSetores(
ConnectionInfo pInfo, decimal pSET_ID, string pCHAVE
)
{
Boolean values;
try
{
string lQuery = "";
DataTable lTable = new DataTable();
OracleCommand cmd = new OracleCommand();
using (OracleConnection cn = new OracleConnection(pInfo.ConnectionString))
{
cmd.Connection = cn;
cmd.CommandText = "spLista";
cmd.CommandType = CommandType.StoredProcedure;
//cmd.CommandText = " BEGIN spLista (" + pSET_ID + ", " + pCHAVE + "); END; ";
cmd.Parameters.Add("pSET_ID", OracleDbType.Decimal).Value = pSET_ID;
cmd.Parameters.Add("pCHAVE", OracleDbType.Decimal).Value = pCHAVE;
cn.Open();
DataSet ds = new DataSet();
OracleDataAdapter Adapter = new OracleDataAdapter(cmd);
Adapter.Fill(ds);
Adapter.Dispose();
values = true;
cmd.Dispose();
cn.Dispose();
cn.Close();
}
return values;
}
catch (Exception ex)
{
values = false;
}
return values;
}
private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
// TODO: dispose managed state (managed objects).
_command?.Dispose();
// dali ne tiabva i parametrite da osvobodim ? dali odp.net sam gi osvobovdava ??
}
// TODO: free unmanaged resources (unmanaged objects) and override a finalizer below.
// TODO: set large fields to null.
disposedValue = true;
}
}
public void aprobarSolicitud(string numeroSolicitud)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "solicitudes_paquete.aprobarSolicitud";
OracleParameter numeroSolicitudP = new OracleParameter("numeroSolicitud", OracleDbType.Int16, System.Data.ParameterDirection.Input);
numeroSolicitudP.Value = int.Parse(numeroSolicitud);
cmd.Parameters.Add(numeroSolicitudP);
cmd.ExecuteNonQuery();
cmd.Dispose();
base.cerrarConexion();
}
public SolicitudRegistro detalleSolicitud(string numeroSolicitud)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "solicitudes_paquete.detalleSolicitud";
OracleParameter numeroSolicitudP = new OracleParameter("numeroSolicitud", OracleDbType.Int32, System.Data.ParameterDirection.Input);
numeroSolicitudP.Value = int.Parse(numeroSolicitud);
OracleParameter cursor_datos = new OracleParameter("cursor_datos", OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
cmd.Parameters.AddRange(new OracleParameter[] {numeroSolicitudP, cursor_datos });
OracleDataReader lectorDatos = cmd.ExecuteReader();
SolicitudRegistro solicitudConsulta = new SolicitudRegistro();
if (lectorDatos.HasRows)
{
while (lectorDatos.Read())
{
solicitudConsulta = new SolicitudRegistro()
{
numero = lectorDatos.GetInt16(0),
id = lectorDatos.GetString(1),
password = lectorDatos.GetString(2),
nombre = lectorDatos.GetString(3),
apellidos = lectorDatos.GetString(4),
categoria = lectorDatos.GetInt16(5),
edad = lectorDatos.GetInt16(6),
estado = lectorDatos.GetString(7),
correo = lectorDatos.GetString(8),
cedula = lectorDatos.GetString(9)
};
}
}
lectorDatos.Close();
cmd.Dispose();
base.cerrarConexion();
return solicitudConsulta;
}
public int ExecuteBlobQuery(string sql, string param, byte[] blob)
{
try
{
OracleParameter blobParameter = new OracleParameter();
blobParameter.OracleDbType = OracleDbType.Blob;
blobParameter.ParameterName = param;
blobParameter.Value = blob;
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(blobParameter);
int affected = cmd.ExecuteNonQuery();
cmd.Dispose();
return affected;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return -1;
}
public int ExecuteBlobQuery(string sql, string param, byte[] blob)
{
try
{
OracleParameter blobParameter = new OracleParameter();
blobParameter.OracleDbType = OracleDbType.Blob;
blobParameter.ParameterName = param;
blobParameter.Value = blob;
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(blobParameter);
int affected = cmd.ExecuteNonQuery();
cmd.Dispose();
return affected;
}
catch (Exception ex)
{
MessageBox.Show("Query gagal dijalankan, pastikan definisi data di dalam Oracle 11g sudah sesuai dengan upstream.\nPesan kesalahan : \n" + ex.ToString(), "Galat", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return -1;
}
public string DoPicking(string productCode,
string providerLote,
int pickingQuntity,
string warehouseCode,
string clientName,
string shipNumber,
string userCode,
string username,
string password)
{
OracleConnection conn = null;
string sql = "insert into sga_littmoden.custom_sivart_picking " +
"(cod_prod, lote_proveedor, cantidad_picking, cod_alm, creation_date, nombre_cliente, codigo_pedido, codigo_operario) " +
"values ('" + productCode + "', '" + providerLote + "', " + pickingQuntity + ", '" + warehouseCode + "', SYSDATE, '" + clientName + "', '" + shipNumber + "', '" + userCode + "')";
string sql2 = "insert into sga_littmoden.custom_sivart_picking " +
"(cod_prod, lote_proveedor, cantidad_picking, cod_alm, creation_date) " +
"values ('" + productCode + "', '" + providerLote + "', " + pickingQuntity + ", '" + warehouseCode + "', SYSDATE)";
string result = "OK";
try
{
conn = new OracleConnection("Data Source=orclmlx; User Id=mlxsga_protein_sivart; Password=mlxsga_protein_sivart;");
conn.Open();
OracleCommand cmd = new OracleCommand(sql2, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
result = "Exception, " + sql2 + " " + ex.ToString();
}
finally
{
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
return result;
}
public void agregarDiagnosticoCita(string idCita, string descripcionDiagnostico)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "citas_paquete.agregarDiagnosticoCita";
OracleParameter idCitaP = new OracleParameter("idCita", OracleDbType.Int32, System.Data.ParameterDirection.Input);
idCitaP.Value = int.Parse(idCita);
OracleParameter descripcionDiagnosticoP = new OracleParameter("descripcionDiagnostico", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
descripcionDiagnosticoP.Value = descripcionDiagnostico;
cmd.Parameters.AddRange(new OracleParameter[] { idCitaP, descripcionDiagnosticoP });
cmd.ExecuteNonQuery();
cmd.Dispose();
base.cerrarConexion();
}
public void calificarAtencion(string idCita, string calificacionCita)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "afiliados_paquete.calificarAtencion";
OracleParameter idCitaP = new OracleParameter("idCita", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
idCitaP.Value = idCita;
OracleParameter calificacionCitaP = new OracleParameter("calificacionCita", OracleDbType.Int32, System.Data.ParameterDirection.Input);
calificacionCitaP.Value = int.Parse(calificacionCita);
cmd.Parameters.AddRange(new OracleParameter[] { idCitaP, calificacionCitaP });
cmd.ExecuteNonQuery();
cmd.Dispose();
base.cerrarConexion();
}
public List<Afiliado> buscarAfiliado(string nombreAfiliado)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "afiliados_paquete.buscarAfiliado";
OracleParameter nombreAfiliadoP = new OracleParameter("nombreAfiliado", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
nombreAfiliadoP.Value = nombreAfiliado;
OracleParameter cursor_datos = new OracleParameter("cursor_datos", OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
cmd.Parameters.AddRange(new OracleParameter[] { nombreAfiliadoP, cursor_datos });
OracleDataReader lectorDatos = cmd.ExecuteReader();
List<Afiliado> afiliadosConsulta = new List<Afiliado>();
if (lectorDatos.HasRows)
{
while (lectorDatos.Read())
{
afiliadosConsulta.Add(new Afiliado()
{
id = lectorDatos.GetString(0),
password = lectorDatos.GetString(1),
nombre = lectorDatos.GetString(2),
apellidos = lectorDatos.GetString(3),
edad = lectorDatos.GetInt32(4),
cedula = lectorDatos.GetString(5),
fecha_afiliacion = lectorDatos.GetOracleDate(6).ToString(),
estado = lectorDatos.GetString(7),
categoria = lectorDatos.GetInt32(8),
correo = lectorDatos.GetString(9)
});
}
}
lectorDatos.Close();
cmd.Dispose();
base.cerrarConexion();
return afiliadosConsulta;
}
public static Byte[] GetBytes(string cmdQuery, Dictionary<string, string> paramsDict)
{
try
{
_open();
OracleCommand cmd = new OracleCommand(cmdQuery, _conn);
foreach (KeyValuePair<string, string> pair in paramsDict)
{
cmd.Parameters.AddWithValue(":" + pair.Key, pair.Value);
}
Byte[] b = null;
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
b = new Byte[Convert.ToInt32((reader.GetBytes(0, 0, null, 0, Int32.MaxValue)))];
reader.GetBytes(0, 0, b, 0, b.Length);
}
reader.Close();
cmd.Dispose();
return b;
}
catch (UnauthorizedAccessException ex)
{
_logger.WriteError(ex, "Путь для записи - ");
throw;
}
catch (TimeoutException)
{
return null;
}
finally
{
_close();
}
}
public void Update(GiftShopT gshop)
{
string command = "UPDATE gift_shop SET gift_sender_first_name = :gs_sender_f_name, gift_sender_last_name = :gs_sender_l_name, gift_recipient_first_name = :gs_recipient_f_name, gift_recipient_last_name = :gs_recipient_l_name, gift_type = :gs_type, gift_desc = :gs_desc, gift_price = :gs_price, gift_sender_id = :gs_sender_id, gift_recipient_id = :gs_recipient_id WHERE gift_id = :g_id";
conn.Open();
OracleCommand cmd = new OracleCommand(command, conn);
cmd.Parameters.Add(new OracleParameter("gs_sender_f_name", gshop.Gift_Sender_First_Name));
cmd.Parameters.Add(new OracleParameter("gs_sender_l_name", gshop.Gift_Sender_Last_Name));
cmd.Parameters.Add(new OracleParameter("gs_recipient_f_name", gshop.Gift_Recipient_First_Name));
cmd.Parameters.Add(new OracleParameter("gs_recipient_l_name", gshop.Gift_Recipient_Last_Name));
cmd.Parameters.Add(new OracleParameter("gs_type", gshop.Gift_Type));
cmd.Parameters.Add(new OracleParameter("gs_desc", gshop.Gift_Desc));
cmd.Parameters.Add(new OracleParameter("gs_price", gshop.Gift_Price));
cmd.Parameters.Add(new OracleParameter("gs_sender_id", gshop.Gift_Sender_Id));
cmd.Parameters.Add(new OracleParameter("gs_recipient_id", gshop.Gift_Recipient_Id));
cmd.Parameters.Add(new OracleParameter("gs_id", gshop.Gift_Id));
_rows = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
public void LoadDropDownList(DropDownList dd, string key)
{
String sql = "SELECT DISTINCT * FROM GTS.PICKLIST WHERE lower(picklist_code) = lower(:picklist_code) ORDER BY picklist_order";
OracleCommand cmd = new OracleCommand();
OracleConnection conn = MakeOracleConnection();
conn.Open();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.Add("picklist_code", OracleDbType.Varchar2).Value = key;
OracleDataReader dr = cmd.ExecuteReader();
dd.Items.Clear();
dd.AppendDataBoundItems = true;
dd.Items.Add(new ListItem("", ""));
dd.DataSource = dr;
dd.DataTextField = "picklist_display";
dd.DataValueField = "picklist_value";
dd.DataBind();
conn.Close();
cmd.Dispose();
}
public static int ExecuteSqlArray(List <string> sqlStr)
{
int result = 0;
OracleConnection oracleConnection = new OracleConnection();
oracleConnection.ConnectionString = OracleHelper.connectStr;
OracleTransaction oracleTransaction = null;
try
{
if (oracleConnection.State != ConnectionState.Open)
{
oracleConnection.Open();
}
oracleTransaction = oracleConnection.BeginTransaction();
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.Transaction = oracleTransaction;
oracleCommand.CommandType = CommandType.Text;
for (int i = 0; i < sqlStr.Count; i++)
{
oracleCommand.CommandText = sqlStr[i];
result = oracleCommand.ExecuteNonQuery();
}
oracleCommand.Dispose();
oracleTransaction.Commit();
oracleConnection.Close();
}
catch (Exception ex)
{
oracleTransaction.Rollback();
oracleConnection.Close();
throw ex;
}
return(result);
}
public void BtnUpdate_Click(object sender, EventArgs e)
{
if (IS_EDIT_ACTIVE == "Enable")
{
OracleConnection conn = new OracleConnection(strConnString);
conn.Open();
int userID = Convert.ToInt32(Session["USER_ID"]);
string DataMonthYear = TextMonthYear0.Text;
string u_date = System.DateTime.Now.ToString("dd-MM-yyyy h:mm:ss tt");
string update_inven_mas = "update PF_PROCESSING_COST set COST_RATE = :NoCostRate, UPDATE_DATE = TO_DATE(:u_date, 'DD-MM-YYYY HH:MI:SS AM'), U_USER_ID = :NoCuserID where ITEM_ID =:NoItemID AND To_CHAR(MONTH_YEAR,'mm/yyyy') = :TextMonthYear ";
cmdu = new OracleCommand(update_inven_mas, conn);
OracleParameter[] objPrmInevenMas = new OracleParameter[5];
objPrmInevenMas[0] = cmdu.Parameters.Add("NoItemID", Convert.ToInt32(DropDownItemID.Text));
objPrmInevenMas[1] = cmdu.Parameters.Add("NoCostRate", Convert.ToDouble(TextItemCostRate.Text));
objPrmInevenMas[2] = cmdu.Parameters.Add("u_date", u_date);
objPrmInevenMas[3] = cmdu.Parameters.Add("NoCuserID", userID);
objPrmInevenMas[4] = cmdu.Parameters.Add("TextMonthYear", DataMonthYear);
cmdu.ExecuteNonQuery();
cmdu.Parameters.Clear();
cmdu.Dispose();
alert_box.Visible = true;
alert_box.Controls.Add(new LiteralControl("Processing Cost Data Update successfully"));
alert_box.Attributes.Add("class", "alert alert-success alert-dismissible");
clearText();
Display();
}
else
{
Response.Redirect("~/PagePermissionError.aspx");
}
}
private void loadBranches()
{
ddlBranch.Items.Clear();
ddlBranch.Items.Add(new ListItem("--- Select One ---", "0"));
ddlSearchBranch.Items.Clear();
ddlSearchBranch.Items.Add(new ListItem("--- Select One ---", "0"));
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString());
OracleDataReader dr;
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
String selectQuery = "";
selectQuery = "select t.branch_code,t.branch_name from mis_gi_branches t order by t.branch_name ";
cmd.CommandText = selectQuery;
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
ddlBranch.Items.Add(new ListItem(dr[1].ToString(), dr[0].ToString()));
ddlSearchBranch.Items.Add(new ListItem(dr[1].ToString(), dr[0].ToString()));
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();
}
private void loadAdvisors(string branchCode, string companyCode)
{
ddlAdvisorCode.Items.Clear();
ddlAdvisorCode.Items.Add(new ListItem("--- Select One ---", "0"));
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString());
OracleDataReader dr;
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
String selectQuery = "";
selectQuery = "SELECT AGENT_CODE,AGENT_NAME FROM welf_advisor WHERE COMPANY='" + companyCode + "' AND BRANCH_CODE='" + branchCode + "' ORDER BY AGENT_NAME ASC ";
cmd.CommandText = selectQuery;
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
string text = dr[1].ToString() + "(" + dr[0].ToString() + ")";
ddlAdvisorCode.Items.Add(new ListItem(text, dr[0].ToString()));
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();
}
/// <summary>
/// To Update MNE Goal Details
/// </summary>
/// <param name="GoalNameBOObj"></param>
/// <returns></returns>
public string UpdateMNEGoalDetails(MNEGoalBO GoalNameBOObj)
{
OracleConnection cnn = new OracleConnection(AppConfiguration.ConnectionString);
cnn.Open();
OracleCommand dcmd = new OracleCommand("USP_MST_MNE_UPDATE_GOAL", cnn);
dcmd.CommandType = CommandType.StoredProcedure;
string result = "";
try
{
dcmd.Parameters.Add("GOALNAME_", GoalNameBOObj.GoalName);
dcmd.Parameters.Add("UPDATEDBY_", GoalNameBOObj.CreatedBy);
dcmd.Parameters.Add("GOALID_", GoalNameBOObj.GoalID);
dcmd.Parameters.Add("errorMessage_", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;
dcmd.ExecuteNonQuery();
if (dcmd.Parameters["errorMessage_"].Value != null)
{
result = dcmd.Parameters["errorMessage_"].Value.ToString();
}
}
catch
{
throw;
}
finally
{
dcmd.Dispose();
cnn.Close();
cnn.Dispose();
}
return(result);
}
public DataTable SELECT_KH_NONTAI(string pMA_DVIQLY, string ma_khang, int Ploai)
{
DataTable dt = new DataTable();
OracleConnection objConn = new OracleConnection(ConnectString.ConnectionString(pMA_DVIQLY));
OracleCommand objCmd = new OracleCommand();
try
{
objConn.Open();
objCmd.Connection = objConn;
objCmd.CommandText = "PKG_TRUYTHUNONTAI.SELECT_KH_NONTAI";
objCmd.Parameters.Add("pMA_DVIQLY", OracleType.VarChar).Value = pMA_DVIQLY;
objCmd.Parameters.Add("pMA_KHANG", OracleType.VarChar).Value = ma_khang;
objCmd.Parameters.Add("pLOAI", OracleType.VarChar).Value = Ploai + "";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add("rs", OracleType.Cursor).Direction = ParameterDirection.Output;
OracleDataReader objReader = objCmd.ExecuteReader();
dt.Load(objReader);
objConn.Close();
objCmd.Dispose();
objCmd = null;
}
catch (Exception ex)
{
System.Console.WriteLine("Exception: {0}", ex.ToString());
}
finally
{
objConn.Close();
objConn.Dispose();
objCmd = null;
}
return(dt);
}
// 2. Select one or more rows using bind variables and command Parameters
private static string CheckIfUserIsDoctor(int ID, out string isPractitionerOrResident)
{
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = $"SELECT * FROM Doctor WHERE ID={ID}",
CommandType = CommandType.Text,
};
OracleDataReader IDreader = cmd.ExecuteReader();
// Check if only 1 user is found
if (IDreader.Read())
{
isPractitionerOrResident = (string)IDreader[1];
cmd.Dispose();
IDreader.Dispose();
IDreader.Close();
return("Doctor");
}
isPractitionerOrResident = ""; //(string)IDreader[1];
return("Nurse");
}
public bool UpdateMINISTRY()
{
bool result = false;
OracleConnection conn = ConnectionDB.GetOracleConnection();
string query = "Update TB_MINISTRY Set ";
query += " MINISTRY_ID = :MINISTRY_ID,";
query += " MINISTRY_NAME = :MINISTRY_NAME";
query += " where MINISTRY_ID = :MINISTRY_ID";
OracleCommand command = new OracleCommand(query, conn);
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command.Parameters.Add(new OracleParameter("MINISTRY_ID", MINISTRY_ID));
command.Parameters.Add(new OracleParameter("MINISTRY_NAME", MINISTRY_NAME));
if (command.ExecuteNonQuery() > 0)
{
result = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
command.Dispose();
}
return(result);
}
/// <summary>
/// 关闭数据库连接.
/// </summary>
public void Close()
{
try
{
//事务未结束防止自动关闭
//if (_AutoClose && _IsTransactionBegin)
//{
// return;
//}
if (MyConn.State != System.Data.ConnectionState.Closed)
{
MyConn.Close();
}
if (MyComm != null)
{
MyComm.Dispose();
}
MyConn.Dispose();
}
catch (Exception Ex)
{
HandelException(Ex);
}
}
//get the plan code :
/// <summary>
/// Get the list for plan code as per the site code
/// </summary>
/// <param name="command">country code</param>
/// <returns></returns>
public List <string> Getplantcode(string command)//, List<OracleParameter> oracleParameters)
{
List <String> ListText = new List <string>();
OracleDataReader datareader;
OracleConnection _connectionString = new OracleConnection(commanager.ConnectionString);
try
{
_connectionString.Open();
var cmd = new OracleCommand();
if (cmd != null)
{
cmd.Connection = _connectionString;
cmd.CommandText = command;
datareader = cmd.ExecuteReader();
if (cmd.ExecuteReader().HasRows)
{
while (datareader.Read())
{
DDRSessionEntity.Current.plantcode = datareader.GetString(0);
ListText.Add(DDRSessionEntity.Current.plantcode);
}
}
cmd.Dispose();
}
}
catch (Exception)
{
throw;
}
finally
{
_connectionString.Close();
}
return(ListText);
} //end plan code
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static int ExecuteSqlTran(List <String> SQLStringList)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
OracleTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return(count);
}
catch
{
tx.Rollback();
return(0);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 从指定的连接获取DataTable
/// </summary>
/// <param name="procName"></param>
/// <param name="cmdParams"></param>
/// <param name="conn"></param>
/// <returns></returns>
public DataTable getDTBySepecificConn(string procName, OracleParameter[] cmdParams, OracleConnection conn)
{
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
CreateCommand(cmd, conn, null, procName, cmdParams);
try
{
OracleDataReader odr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(odr);
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
throw new Exception(ex.ToString());
}
finally
{
cmd.Parameters.Clear();
cmd.Dispose();
}
}
public static int InsertTurno(TurneroDto turno)
{
try
{
OracleCommand cmd = GetDbSprocCommand("PRC_TURNERO_INSERT");
cmd.Parameters.Add(CreateParameter(":iTURTITULO", turno.TurTitulo, 45)); //VARCHAR
cmd.Parameters.Add(CreateParameter(":iTURDESCRIP", turno.TurDescripcion, 45)); //VARCHAR
cmd.Parameters.Add(CreateParameter(":iTURFECHAINI", turno.TurFechaIni)); //DATETIME
cmd.Parameters.Add(CreateParameter(":iTURFECHAFIN", turno.TurFechaFin)); //DATETIME
cmd.Parameters.Add(new OracleParameter(":iTURTODODIA", turno.TurTodoDia)); //BOOL
cmd.Parameters.Add(new OracleParameter(":iTUR_PAEID", turno.TurPae)); //NUMBER
cmd.Parameters.Add(CreateParameter(":iTUR_PROID", turno.TurPro)); //NUMBER
cmd.Parameters.Add(CreateParameter(":iTURMONTO", turno.TurMonto)); //NUMBER
cmd.Parameters.Add(CreateParameter(":iTUR_OSPID", turno.TurOspId)); //NUMBER
cmd.Parameters.Add(CrearParametroSalida("oTURID", OracleDbType.Int32)); //NUMBER
cmd.Connection.Open();
cmd.ExecuteNonQuery();
object turId = cmd.Parameters["oTURID"].Value;
turno.TurId = Convert.ToInt16(turId.ToString());
int key = 0;
key = turno.TurId;
cmd.Connection.Close();
cmd.Dispose();
return(key);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">sql语句</param>
/// <param name="strConn">数据库链接</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString, string strConn)
{
ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings[strConn];
using (OracleConnection connection = new OracleConnection(connectionString.ToString()))
{
OracleCommand cmd = new OracleCommand(SQLString, connection);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
connection.Close();
connection.Dispose();
return(null);
}
else
{
connection.Close();
connection.Dispose();
return(obj);
}
}
catch (OracleException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
connection.Dispose();
}
}
}
/// <summary>
/// To Insert MNE Goal Details
/// </summary>
/// <param name="GoalNameBOObj"></param>
/// <returns></returns>
public string InsertMNEGoalDetails(MNEGoalBO GoalNameBOObj)
{
string result = "";
OracleConnection Con = new OracleConnection(AppConfiguration.ConnectionString);
Con.Open();
OracleCommand cmd = new OracleCommand("USP_MST_MNE_INSERT_GOAL", Con);
cmd.CommandType = CommandType.StoredProcedure;
int Count = Convert.ToInt32(cmd.CommandType);
try
{
cmd.Parameters.Add("GOALNAME_", GoalNameBOObj.GoalName);
cmd.Parameters.Add("CREATEDBY_", GoalNameBOObj.CreatedBy);
cmd.Parameters.Add("errorMessage_", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
if (cmd.Parameters["errorMessage_"].Value != null)
{
result = cmd.Parameters["errorMessage_"].Value.ToString();
}
}
catch
{
throw;
}
finally
{
cmd.Dispose();
Con.Close();
Con.Dispose();
}
return(result);
}
public static object ExecuteScalar(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(CONN_STRING_NON_DTC);
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
object val = null;
try
{
if (conn.State != ConnectionState.Open)
{
Open(conn);
//conn.Open();
}
val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(val);
}
//Vérifie l'existence de la Prestation
public int CompterPrest(string code)
{
DBConnect c = new DBConnect();
OracleConnection conn = c.GetConnection();
conn.Open();
OracleTransaction trans = conn.BeginTransaction();
Int32 nb = 0;
OracleCommand cmd = new OracleCommand
{
CommandText = "SELECT COUNT(CODE_PREST) FROM PRESTAT WHERE CODE_PREST= '" + code + "'",
Connection = conn,
CommandType = CommandType.Text
};
try
{
// Exécution de la requête
dr = cmd.ExecuteReader();
// On soumet la requête au serveur: tout s'est bien déroulé , la requête est exécutée
trans.Commit();
}
catch (Exception ex)
{
// Une erreur est survenue: on ne valide pas la requête
trans.Rollback();
Console.WriteLine("Requête non effectuée !!\nErreur: '" + ex.Message);
}
finally
{ // Libération des ressources
cmd.Dispose();
}
dr.Read();
nb = dr.GetInt32(0);
return(nb);
}
private bool Execute_Insert(string st)
{
bool ret = false;
OracleCommand ocmd = new OracleCommand(st);
ocmd.Connection = this.oracleconnection;
try
{
ocmd.ExecuteReader();
//ocmd.CommandText = "commit";
//ocmd.ExecuteReader();
ret = true;
}
catch (OracleException ex)
{
MessageBox.Show("DBHandling-Execute_Insert-OracleException: " + ex.Message);
ret = false;
}
finally
{
ocmd.Dispose();
}
return(ret);
} // Execute_Insert(string st)
public Boolean DoNonQuery(string query, OracleCommand cmd)
{
//OracleCommand cmd = new OracleCommand();
// OracleDataReader dr;
cmd.Connection = this.ocl;
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.error = ex;
return(false);
}
cmd.Dispose();
return(true);
}
/// <summary>
/// To Edit Fence
/// </summary>
/// <param name="FenceBOobj"></param>
/// <returns></returns>
public int EditFence(OtherFenceBO FenceBOobj)
{
OracleConnection cnn = new OracleConnection(AppConfiguration.ConnectionString);
cnn.Open();
OracleCommand dcmd = new OracleCommand("USP_TRN_UPD_OTHERFENCE", cnn);
dcmd.CommandType = CommandType.StoredProcedure;
int count = Convert.ToInt32(dcmd.CommandType);
try
{
dcmd.Parameters.Add("PAP_FIXTUREID_", FenceBOobj.Pap_otherfenceid);
dcmd.Parameters.Add("HHID", FenceBOobj.HouseholdID);
dcmd.Parameters.Add("FIXTURETYPE_", FenceBOobj.Otherfencedescription);
dcmd.Parameters.Add("DIMEN_LENGTH_", FenceBOobj.DIMEN_LENGTH);
dcmd.Parameters.Add("DIMEN_WIDTH_", FenceBOobj.DIMEN_WIDTH);
dcmd.Parameters.Add("DEPRECIATEDVALUE_", FenceBOobj.Depreciatedvalue);
dcmd.Parameters.Add("UPDATEDBY_", FenceBOobj.CreatedBy);
//if (FenceBOobj.Photo != null)
// dcmd.Parameters.Add("FENCEPHOTO_", OracleDbType.Blob).Value = FenceBOobj.Photo;
//else
// dcmd.Parameters.Add("FENCEPHOTO_", Oracle.DataAccess.Types.OracleBlob.Null);
return(dcmd.ExecuteNonQuery());
}
catch
{
throw;
}
finally
{
dcmd.Dispose();
cnn.Close();
cnn.Dispose();
}
}
public void LeaveDetails(LeaveModel leaveModel)
{
OracleConnection con = new OracleConnection(connStr);
con.Open();
OracleCommand cmd = new OracleCommand("INSERT_LEAVETABLE", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.Add("E_ID", leaveModel.empid);
cmd.Parameters.Add("E_LEAVETYPE", leaveModel.leavetype);
cmd.Parameters.Add("E_STARTDATE", leaveModel.startdate);
cmd.Parameters.Add("E_ENDDATE", leaveModel.enddate);
cmd.Parameters.Add("E_LEAVEDURATION", leaveModel.leaveduration);
cmd.Parameters.Add("E_LEAVEDESCRIPTION", leaveModel.leavedescription);
cmd.Parameters.Add("E_LEAVESTATUS", leaveModel.leavestatus);
cmd.Parameters.Add("E_LEAVEBALANCE", leaveModel.leavebalance);
cmd.Parameters.Add("E_CREATEDBY", leaveModel.name);
cmd.Parameters.Add("E_CREATEDDATETIME", leaveModel.created_datetime);
cmd.Parameters.Add("E_UPDATEDBY", leaveModel.name);
cmd.Parameters.Add("E_UPDATEDDATETIME", leaveModel.created_datetime);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
con.Close();
cmd.Dispose();
}
}
/// <summary>
/// 将数据填充到DataSet中(使用connString + OracleParameterCollection)
/// </summary>
/// <param name="connString">ConnectString</param>
/// <param name="cmdType">类型</param>
/// <param name="cmdText">Command的语句</param>
/// <param name="tablename">表名</param>
/// <param name="cmdParms">Command的参数(OracleParameterCollection)</param>
public static void FillData(string connString, CommandType cmdType, string cmdText, DataSet dataset, string tablename, OracleParameterCollection cmdParms)
{
OracleDataAdapter dsCommand = new OracleDataAdapter();
OracleCommand cmd = new OracleCommand();
dsCommand.SelectCommand = cmd;
dsCommand.TableMappings.Add("Table", tablename);
OracleConnection conn = new OracleConnection(connString);
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
try
{
dsCommand.Fill(dataset);
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (dsCommand != null)
{
dsCommand.Dispose();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
}
public UserResponse GetUser(UserModel request)
{
UserResponse response = null;
using (dbConn = ConnectionFactory.GetOpenDefaultConnection())
{
string query = string.Format("Select * from Users where LoginId = '{0}' ", request.UserName);
OracleCommand command = new OracleCommand(query, (OracleConnection)dbConn);
command.CommandText = query;
DbDataReader dataReader = command.ExecuteReader();
if (dataReader != null)
{
while (dataReader.Read())
{
response = new UserResponse()
{
result = true, userId = Convert.ToInt32(dataReader["Id"]), userName = dataReader["LoginId"].ToString(), BadgeNumber = Convert.ToInt32(dataReader["BAdgeNumber"])
};
}
}
else
{
response = new UserResponse()
{
result = false
};
}
dataReader.Close();
command.Dispose();
dbConn.Close();
dbConn.Dispose();
}
return(response);
}
// 입력
public int thrm_add(String empno, DateTime car_com, String car_region, String car_yyyymm_f, String car_yyyymm_t, String car_pos, String car_dept, String car_job, String car_reason, String award_dept)
{
int check = 1;
try
{
if (_DB.GetConnection() == true)
{
OracleCommand comm = new OracleCommand();
comm.Connection = _DB.Connection;
comm.CommandText = @"INSERT INTO thrm_award_hwy values(:empno, :resno, :name, :cname, :ename, :datasys1, :datasys2, :datasys3, :car_reason, :award_dept)";
comm.Parameters.Add("empno", empno);
comm.Parameters.Add("resno", car_com.ToString("yyyyMMdd"));
comm.Parameters.Add("name", car_region);
comm.Parameters.Add("cname", car_yyyymm_f);
comm.Parameters.Add("ename", car_yyyymm_t);
comm.Parameters.Add("datasys1", car_pos);
comm.Parameters.Add("datasys2", car_dept);
comm.Parameters.Add("datasys3", car_job);
comm.Parameters.Add("car_reason", car_reason);
comm.Parameters.Add("award_dept", award_dept);
comm.Prepare();
var a = comm.ExecuteNonQuery();
Console.WriteLine(a + " row insert");
comm.Cancel();
comm.Dispose();
check = 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(check);
}
/// <summary>
/// To Delete
/// </summary>
/// <param name="Pap_fenceid"></param>
/// <returns></returns>
public int Delete(int Pap_fenceid)
{
OracleConnection conn = new OracleConnection(AppConfiguration.ConnectionString);
conn.Open();
OracleCommand dCmd = new OracleCommand("USP_TRN_DEL_OTHERFENCE", conn);
dCmd.CommandType = CommandType.StoredProcedure;
try
{
dCmd.Parameters.Add("PAP_FIXTUREID_", Pap_fenceid);
return(dCmd.ExecuteNonQuery());
}
catch (Exception ex)
{
throw ex;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 将数据填充到DataSet中(默认connString)
/// </summary>
/// <param name="cmdType">类型</param>
/// <param name="cmdText">Command的语句</param>
/// <param name="tablename">表名</param>
/// <param name="cmdParms">Command的参数</param>
public static void FillData(CommandType cmdType, string cmdText, DataSet dataset, string tablename, int startRecord, int maxRecords, params OracleParameter[] cmdParms)
{
OracleDataAdapter dsCommand = new OracleDataAdapter();
OracleCommand cmd = new OracleCommand();
dsCommand.SelectCommand = cmd;
//dsCommand.TableMappings.Add("Table",tablename);
OracleConnection conn = new OracleConnection(CONN_STRING_NON_DTC);
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
try
{
dsCommand.Fill(dataset, startRecord, maxRecords, tablename);
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (dsCommand != null)
{
dsCommand.Dispose();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
}