} //end of this method
// Madan Saini 03/29/2004 -- Clear Text Passwords for PMT
// This method validate an Internet user's membership of a web application
public static bool IsDBUserValid(string loginID, string password, string appName, System.Data.OracleClient.OracleConnection conVITAP)
{
bool valid = false;
//string encodedPassword = SharedPassword.Encode(password);
loginID = loginID.ToUpper();
System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand();
if (conVITAP.State != ConnectionState.Open)
{
conVITAP.Open();
}
cmdVITAP.Connection = conVITAP;
cmdVITAP.CommandText = Queries.getValidUser(loginID, password, appName);
System.Data.OracleClient.OracleDataReader rdrReader = cmdVITAP.ExecuteReader();
if (rdrReader.Read())
{
valid = true;
}
else
{
valid = false;
}
cmdVITAP.Dispose();
return(valid);
} //end of this method
public int NuevaArea(CE_Area objce_area)
{
//el metodo m
try
{
int filasafectadas = 0;
OracleConnection cnx = Conexion.ObtenerConexionOracle();
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnx;
cmd.CommandText = "sp_Nueva_Area";
//asignar paramentros al procedimiento almacenado
cmd.Parameters.AddWithValue("nombreareaX", objce_area.nombrearea);
//abrir la conexion
cnx.Open();
//ejecutar el procedimiento almacenado
filasafectadas = cmd.ExecuteNonQuery();
//Cerrar conexion
cnx.Close();
return filasafectadas;
}
catch (Exception ex)
{
throw ex;
}
}
protected void GV_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
using (OracleConnection conn = new OracleConnection(DBHelper.ConnectionString))
{
string reason_id = GV.DataKeys[e.RowIndex].Values[0].ToString();
string reason_desc = ((TextBox)GV.Rows[e.RowIndex].FindControl("TxtDesc")).Text;
string active = ((CheckBox)(GV.Rows[e.RowIndex].FindControl("ChkActive"))).Checked == true ? "1" : "0";
string sqlupdate = "update jp_lack_reason set reason_desc = '" + reason_desc + "',is_valid='" + active + "' where reason_id = '" + reason_id + "' ";
OracleCommand updatecomm = new OracleCommand(sqlupdate, conn);
try
{
conn.Open();
updatecomm.ExecuteNonQuery();
GV.EditIndex = -1;
GVDataBind();
}
catch (Exception ex)
{
conn.Close();
Response.Write("<script language=javascript>alert('" + ex.Message + "')</script>");
}
finally
{
updatecomm.Dispose();
conn.Dispose();
conn.Close();
}
}
}
int MSOracleClient()
{
System.Data.OracleClient.OracleConnectionStringBuilder connBuilder = new System.Data.OracleClient.OracleConnectionStringBuilder();
connBuilder.DataSource = txtDataSource.Text.Trim();
connBuilder.UserID = txtUserId.Text.Trim();
connBuilder.Password = txtPwd.Text.Trim();
connBuilder.LoadBalanceTimeout = 60;
connBuilder.MinPoolSize = 0;
connBuilder.MaxPoolSize = 50;
int rows = 0;
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString))
{
//System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString);
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = txtSql.Text.Trim();
cmd.CommandTimeout = 300;
//cmd.ResetCommandTimeout();
conn.Open();
using (System.Data.OracleClient.OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
rows++;
}
}
return(rows);
}
}
/// <summary></summary>
/// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1
///
/// UPDATE、INSERT 和 DELETE 语句
public int ExecuteNonQuery(string sql)
{
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
{
return(-1);
}
int rv = -1;
OracleTransaction oracleTransaction = null;
try
{
if (oracleConnection.State == System.Data.ConnectionState.Closed)
{
oracleConnection.Open();
}
oracleCommand = new OracleCommand(sql, oracleConnection);
oracleTransaction = oracleConnection.BeginTransaction();
oracleCommand.Transaction = oracleTransaction;
rv = oracleCommand.ExecuteNonQuery();
oracleTransaction.Commit();
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
oracleTransaction.Rollback();
rv = -1;
}
return(rv);
}
}
public ArrayList getDataORA2(String str)
{
try
{
using (System.Data.OracleClient.OracleConnection con_ora = new System.Data.OracleClient.OracleConnection(strConnectionORA))
{
con_ora.Open();
using (System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(str, con_ora))
{
using (OracleDataReader reader = command.ExecuteReader())
{
ArrayList list = new ArrayList();
while (reader.Read())
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
list.Add(values);
}
return(list);
}
}
}
}
catch (Exception e)
{
// strError = e.Message.ToString();
// MessageBox.Show(strError);
return(null);
}
}
/// <summary></summary>
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
///
/// SELECT 语句
/// <returns></returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用
public object ExecuteScalar(string sql)
{
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
{
return(null);
}
try
{
if (oracleConnection.State == System.Data.ConnectionState.Closed)
{
oracleConnection.Open();
}
oracleCommand = new OracleCommand(sql, oracleConnection);
return(oracleCommand.ExecuteScalar());
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return(null);
}
}
}
/// <summary></summary>
/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据读取器返回
///
///
/// <returns></returns>OracleDataReader
public OracleDataReader RetriveDataReader(string sql)
{
if (sql == null || sql == string.Empty)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
return(null);
}
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
{
return(null);
}
using (oracleCommand = new OracleCommand(sql, oracleConnection))
{
try
{
OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return(oracleDataReader);
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return(null);
}
}
}
}
public static void MarkDeletedSpoolRecord(string projectid, string spname, string drawing, string username)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
conn.Open();
string queryString = "SP_MarkDeleteSpoolRecord";
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand(queryString, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("projectid_in", OracleType.VarChar).Value = projectid;
cmd.Parameters.Add("spname_in", OracleType.VarChar).Value = spname;
cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing;
cmd.Parameters.Add("username_in", OracleType.VarChar).Value = username;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (OracleException ee)
{
trans.Rollback();
MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
public static OracleParameter AddOutParamToSqlCommand(OracleCommand comm, string pName, OracleType pType)
{
OracleParameter param = new OracleParameter(pName, pType);
param.Direction = ParameterDirection.Output;
comm.Parameters.Add(param);
return param;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado");
comando.Connection = ora2;
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tblFlujo.DataSource = dt;
tblFlujo.DataBind();
ora2.Close();
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
}
}
private void createOracleConnection()
{
try
{
if (dbType == DbType.Oracle)
{
if (conOra == null)
{
conOra = new System.Data.OracleClient.OracleConnection(url);
}
if (cmdOra == null)
{
cmdOra = conOra.CreateCommand();
}
if (conOra.State != ConnectionState.Open)
{
conOra.Open();
}
}
}
catch
{
try
{
conOra.Close();
}
catch { }
throw new StaConnectException();
}
}
//2/09/2005 - Madan - web_users table in VITAP.world
//Input parameter UserName = First Name Initial, LastName
public static string IsValidWebUser(string UserName, string Password, System.Data.OracleClient.OracleConnection App_Connection)
{
string Message = "";
try {
System.Data.OracleClient.OracleCommand CmdLotusMail = new System.Data.OracleClient.OracleCommand();
CmdLotusMail.CommandText = "SELECT password from web_users WHERE (externalservice ='T' or app_dfas_fl ='T') and upper(login_id) = '" + Utilities.SqlEncode(UserName.ToUpper().Trim()) + "'";
CmdLotusMail.Connection = App_Connection; //ConnLotusMail;
if (App_Connection.State != System.Data.ConnectionState.Open)
{
CmdLotusMail.Connection.Open();
}
System.Data.OracleClient.OracleDataReader myReader = CmdLotusMail.ExecuteReader();
if (myReader.Read())
{
Message = myReader["password"].ToString().Trim();
}
myReader.Close();
CmdLotusMail.Connection.Close();
}
catch (System.Exception ex) { // if exception occurs, make an entry in the server event log
EventLog.AddWebErrors("GSA.R7BD.Utility", "Security", "IsLutusNotesUser", ex.Message);
}
return(Message.Trim());
} //end of this method
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="name"></param>
/// <param name="paramList"></param>
/// <returns></returns>
public static bool ExecuteProduce(string name, IList<DbParameter> paramList)
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConnectionString;
conn.Open();
OracleCommand dbCommand = new OracleCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = name;
try
{
foreach (DbParameter param in paramList)
{
dbCommand.Parameters.Add(param);
}
dbCommand.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
conn.Close();
return false;
}
finally
{
conn.Close();
}
}
public void NuevoRegistroES(CE_Registro objce_registro)
{
//el metodo me permite almacenar los datos del nuevo colaborador.
try
{
OracleConnection cnx = Conexion.ObtenerConexionOracle();
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnx;
cmd.CommandText = "sp_nuevo_registro_es";
//asignar paramentros al procedimiento almacenado
cmd.Parameters.AddWithValue("idregistro",OracleType.VarChar).Value = objce_registro.idregistro;
cmd.Parameters.AddWithValue("dni",OracleType.VarChar).Value = objce_registro.dni;
cmd.Parameters.AddWithValue("idestado_es", OracleType.VarChar).Value =objce_registro.idestado_es;
//abrir la conexion
cnx.Open();
//ejecutar el procedimiento almacenado
cmd.ExecuteNonQuery();
//Cerrar conexion
cnx.Close();
}
catch (Exception ex)
{
throw ex;
}
}
protected void ddlCampus_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
using (OracleConnection sqlConn = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
using (OracleCommand sqlCmd = new OracleCommand())
{
sqlCmd.CommandText = "select * from TB_FACULTY where CAMPUS_ID = " + ddlCampus.SelectedValue;
sqlCmd.Connection = sqlConn;
sqlConn.Open();
OracleDataAdapter da = new OracleDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
ddlFaculty.DataSource = dt;
ddlFaculty.DataValueField = "FACULTY_ID";
ddlFaculty.DataTextField = "FACULTY_NAME";
ddlFaculty.DataBind();
sqlConn.Close();
ddlFaculty.Items.Insert(0, new ListItem("--กรุณาเลือกสำนัก / สถาบัน / คณะ--", "0"));
ddlDivision.Items.Clear();
ddlDivision.Items.Insert(0, new ListItem("--กรุณาเลือกกอง / สำนักงานเลขา / ภาควิชา--", "0"));
ddlWorkDivision.Items.Clear();
ddlWorkDivision.Items.Insert(0, new ListItem("--กรุณาเลือกงาน / ฝ่าย--", "0"));
}
}
}
catch { }
}
public void run()
{
OracleConnection con = null;
OracleTransaction txn;
Exception exp = null;
try
{
BeginCase("OracleTransaction Rollback");
//
//prepare data
base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
string Result = "";
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
txn = con.BeginTransaction();
OracleCommand cmd = new OracleCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn);
cmd.ExecuteNonQuery();
txn.Rollback();
//
//
cmd = new OracleCommand("Select LastName From Employees Where EmployeeID = 100", con);
Result = cmd.ExecuteScalar().ToString();
Compare(Result,"Last100" );
this.Log(Result);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
}//end of method
public static string AuthenticateLotusNotesUser(string firstName, string lastName, System.Data.OracleClient.OracleConnection App_Connection)
{
string Message = "";
string UserName1 = firstName.Trim().ToUpper() + " " + lastName.Trim().ToUpper();
string UserName2 = lastName.Trim().ToUpper() + ", " + firstName.Trim().ToUpper();
try {
System.Data.OracleClient.OracleCommand CmdLotusMail = new System.Data.OracleClient.OracleCommand();
CmdLotusMail.CommandText = "SELECT Notesname from maildir WHERE Notesname = '" + Utilities.SqlEncode(UserName1.ToUpper().Trim()) + "' or upper(Name) ='" + Utilities.SqlEncode(UserName2) + "'";
CmdLotusMail.Connection = App_Connection;// ConnLotusMail;
if (App_Connection.State != System.Data.ConnectionState.Open)
{
CmdLotusMail.Connection.Open();
}
System.Data.OracleClient.OracleDataReader NotesReader = CmdLotusMail.ExecuteReader();
if (NotesReader.Read())
{
Message = NotesReader["Notesname"].ToString().Trim();
}
NotesReader.Close();
CmdLotusMail.Connection.Close();
}
catch (System.Exception ex) { // if exception occurs, make an entry in the server event log
EventLog.AddWebErrors("GSA.R7BD.Utility", "Security", "IsLutusNotesUser", ex.Message);
}
return(Message);
}//end of method
public bool VerificarExisteArea(CE_Area objce_area)
{
//la funcion me permite recuperar los datos
try
{
OracleConnection cnx = Conexion.ObtenerConexionOracle();
OracleCommand cmd = new OracleCommand(String.Format("select * from area where nombrearea ='{0}'", objce_area.nombrearea), cnx);
cnx.Open();
OracleDataReader reader;
reader = cmd.ExecuteReader();
Boolean existearea = reader.HasRows;
cnx.Close();
return existearea;
}
catch (Exception ex)
{
throw ex;
}
}
public OracleDataAdapter (OracleCommand selectCommand)
{
SelectCommand = selectCommand;
#if NET_2_0
UpdateBatchSize = 1;
#endif
}
public List<Afdeling> GetAfdelingen()
{
List<Afdeling> result = new List<Afdeling>();
try
{
connection.Open();
string query = "SELECT * FROM afdeling";
OracleCommand command = new OracleCommand(query, connection);
OracleDataReader reader = command.ExecuteReader();
Afdeling a;
while (reader.Read())
{
a = new Afdeling(
(string)reader["afd_afk"],
(string)reader["afd_naam"],
(string)reader["afd_omschrijving"]
);
result.Add(a);
}
return result;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.ToString());
return null;
}
finally
{
connection.Close();
}
}
public static LoanList GetList(int applicantId)
{
LoanList loans = new LoanList();
using(OracleConnection oraDbConn =
new OracleConnection(
ConnStringFactory.getConnString(
ConnStringFactory.ConnStringType.Oracle))){
oraDbConn.Open();
using(OracleCommand getLoansByAppIdCommand = new OracleCommand()){
getLoansByAppIdCommand.CommandType = CommandType.StoredProcedure;
getLoansByAppIdCommand.CommandText = "LoansPKG.getLoansByAppId";
getLoansByAppIdCommand.Connection = oraDbConn;
getLoansByAppIdCommand.Parameters.AddWithValue("AppId", applicantId);
OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
outputCursor.Direction = ParameterDirection.Output;
getLoansByAppIdCommand.Parameters.Add(outputCursor);
using (OracleDataReader loanListReader =
getLoansByAppIdCommand.ExecuteReader()) {
while(loanListReader.Read()){
loans.Add(FillDataRecord(loanListReader));
}
}
}
}
return loans;
}
/// <summary>
/// 通过sql语句获得字段值
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns>返回sql语句查询结果的第一记录的第一个字段内容
/// 如果sql语句执行异常或者没有记录，返回空字符串""
/// </returns>
public string GetFieldValue(string sqlstr)
{
string result = string.Empty;
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandText = sqlstr;
try
{
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
}
object obj = dbCommand.ExecuteScalar();
if (obj != null)
{
result = obj.ToString();
}
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " GetFieldValue:" + sqlstr + "\r\n" + e.Message);
return(result);
}
return(result);
}
public void NuevoAbastecimiento(CE_Abastecimiento objce_abastecimiento)
{
//el metodo me permite
try
{
OracleConnection cnx = Conexion.ObtenerConexionOracle();
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnx;
cmd.CommandText = "sp_Nuevo_Abastecimiento";
//asignar paramentros al procedimiento almacenado
cmd.Parameters.AddWithValue("codigo_abastecimiento", objce_abastecimiento.codigo_abastecimiento);
cmd.Parameters.AddWithValue("dni", objce_abastecimiento.dni);
cmd.Parameters.AddWithValue("idtanque", objce_abastecimiento.idtanque);
cmd.Parameters.AddWithValue("volumen_autorizado", objce_abastecimiento.volumen_autorizado);
cmd.Parameters.AddWithValue("idplacavehiculo", objce_abastecimiento.idplacavehiculo);
cmd.Parameters.AddWithValue("estado", objce_abastecimiento.estado);
//abrir la conexion
cnx.Open();
//ejecutar el procedimiento almacenado
cmd.ExecuteNonQuery();
//Cerrar conexion
cnx.Close();
}
catch (Exception ex)
{
throw ex;
}
}
private void bt_edit_Click(object sender, EventArgs e)
{
query = txb_sql1.Text;
string comando = "Create or replace view \"" + cmb_view1.GetItemText(cmb_view1.SelectedItem) + "\" as " + txb_sql1.Text;
OracleConnection con = new System.Data.OracleClient.OracleConnection("Data Source=XE; User Id=" + Form1.user + "; Password="******";");
OracleCommand cmd = new OracleCommand(comando, con);
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Se ha Editado el View " + "\"" + txt_nomview.Text + "\"", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Information);
Ver_Views();
show_viewtbx.Text = txt_nomview.Text;
tabControl1.SelectedTab = this.tabPage4;
}
catch (Exception ex)
{
MessageBox.Show("Error en la Sentencia o tiene el Siguiente Error: \n" + ex.Message.ToString(), "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// Form1.conn.Close();
System.Drawing.Color clr;
clr = System.Drawing.Color.Red;
label12.ForeColor = clr;
}
}
public void NuevoAcceso(CE_Acceso objce_acceso)
{
//el metodo me permite almacenar los datos del nuevo acceso.
try
{
OracleConnection cnx = Conexion.ObtenerConexionOracle();
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnx;
cmd.CommandText = "sp_NuevoAcceso";
//asignar paramentros al procedimiento almacenado
cmd.Parameters.AddWithValue("dni", objce_acceso.dni);
cmd.Parameters.AddWithValue("fechaacceso", objce_acceso.fechaacceso );
cmd.Parameters.AddWithValue("fechadesde", objce_acceso.fechadesde );
cmd.Parameters.AddWithValue("fechahasta", objce_acceso.fechahasta );
cmd.Parameters.AddWithValue("observaciones", objce_acceso.observaciones );
cmd.Parameters.AddWithValue("estado", objce_acceso.estado );
//abrir la conexion
cnx.Open();
//ejecutar el procedimiento almacenado
cmd.ExecuteNonQuery();
//Cerrar conexion
cnx.Close();
}
catch (Exception ex)
{
throw ex;
}
}
private void EditStudentFrm_Load(object sender, EventArgs e)
{
//Author: Niall Stack - t00174406
string CloudDB = "Data Source=cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw4;Password=sw4;";
try
{
OracleConnection conn = new OracleConnection(CloudDB);
OracleCommand cmd = new OracleCommand("SELECT * FROM Students", conn);
cmd.CommandType = CommandType.Text;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ss");
studGrd.DataSource = ds.Tables["ss"];
conn.Close();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.OracleClient.OracleException E)
{
connection.Close();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
protected void btnAsignar_Click(object sender, EventArgs e)
{
try
{
// Get the currently selected row using the SelectedRow property.
GridViewRow rowTarea = tablaPendientes.SelectedRow;
string textTarea = rowTarea.Cells[1].Text;
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("update_tarea_reasignar");
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreo.Text;
comando.Parameters.Add("P_IDTAREA", OracleType.Int32).Value = Int32.Parse(textTarea);
comando.ExecuteNonQuery();
ora2.Close();
if (tablaPendientes.Rows.Count == 0)
{
lblMensaje.Visible = true;
// Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('No hay tareas pendientes');</script>");
}
else
{
lblMensaje.Visible = false;
}
CargarQuery();
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Asignada');</script>");
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
}
public static int AffectData(string TSQL, IDbConnection myConn, IDbTransaction myTrans, List<IDbDataParameter> myParams)
{
bool mustClose = false;
if (myConn == null)
{
mustClose = true;
myConn = clsConn.getConnOracle();
}
OracleCommand myCMD = new OracleCommand();
//
myCMD.Connection = myConn as OracleConnection;
if (myTrans != null)
myCMD.Transaction = myTrans as OracleTransaction;
//
myCMD.CommandType = CommandType.Text;
myCMD.CommandText = TSQL;
myCMD.CommandTimeout = 180000;//3 phut
//
if (myParams != null)
AttachParameters(myCMD, myParams);
int CMDResult = myCMD.ExecuteNonQuery();
//
if (mustClose) myConn.Close();
return CMDResult;
}
public bool VerificarAutorizaciondeAcceso(CE_Acceso objce_acceso)
{
//la funcion me permite recuperar los datos del colaborador en el objeto CE_Colaborador
try
{
CE_Colaborador objce_colaboradortemp = new CE_Colaborador();
OracleConnection cnx = Conexion.ObtenerConexionOracle();
OracleCommand cmd = new OracleCommand(String.Format("SELECT * FROM ACCESO WHERE DNI='{0}' AND (TO_DATE(SYSDATE) BETWEEN TO_DATE(FECHADESDE) AND TO_DATE(FECHAHASTA))", objce_acceso.dni), cnx);
cnx.Open();
OracleDataReader reader;
reader = cmd.ExecuteReader();
//verifico si hay filas devueltas
Boolean hayfilas = reader.HasRows;
//Cerrar conexion
cnx.Close();
return hayfilas;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// execute a query£¬return DataSet
/// </summary>
/// <param name="SQLString"></param>
/// <returns>DataSet</returns>
public static DataSet Query(string connectionString, CommandType cmdType, string SQLString, params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null, cmdType, SQLString, cmdParms);
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
DataSet ds = new DataSet();
//try
//{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
//}
//catch (System.Data.OracleClient.OracleException ex)
//{
// throw new Exception(ex.Message);
//}
//finally
//{
// if (connection.State != ConnectionState.Closed)
// {
// connection.Close();
// }
//}
return ds;
}
}
}
public List<TicketCategorie> GetCategorieen()
{
List<TicketCategorie> result = new List<TicketCategorie>();
try
{
connection.Open();
string query = "SELECT naam, omschrijving FROM categorie";
OracleCommand command = new OracleCommand(query, connection);
OracleDataReader reader = command.ExecuteReader();
TicketCategorie c;
while (reader.Read())
{
c = new TicketCategorie(
(string)reader["naam"],
(string)reader["omschrijving"]
);
result.Add(c);
}
return result;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show("Niet mogelijk om categorieen binnen te halen: " + e.Message);
return null;
}
finally
{
connection.Close();
}
}
public DataAccess()
{
m_Connection = new OracleConnection();
m_Command = new OracleCommand();
//read connection string from xml file
//string str1 = "";
//XmlTextReader xmlR = new XmlTextReader(MapPath("mycompany.xml") "connString.xml");
//str1 = xmlR.GetAttribute("connstring");
//str1 = System.Configuration.ConfigurationManager.AppSettings.Get("connString");
// XmlDocument doc = new XmlDocument();
//doc.Load("~/App_Data/sample.xml");
//XmlNode root = doc.DocumentElement;
//str1 = root.SelectSingleNode("author").ChildNodes[0].Value;
//string str1 = BDGovPayroll.Properties.Settings.Default.connString;
string str1 = @"Data Source=(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)(HOST=soft-server)(PORT=1521)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= orcl)));
User Id=librarry;Password=softech;";
//////// string str1 = @" Data Source = (DESCRIPTION =
//////// (ADDRESS_LIST =
//////// (ADDRESS =
//////// (PROTOCOL = TCP)(HOST = soft1)(PORT = 1521)))(CONNECT_DATA =
//////// (SERVER = DEDICATED)(SERVICE_NAME = XE)));
//////// User Id = payroll_db; Password = payroll;";
//m_Connection.ConnectionString = @"Data Source=soft1;User Id=payroll_db;Password=payroll;";
m_Connection.ConnectionString = str1;
m_Command.Connection = m_Connection;
}
/// <summary>
/// 执行SQL文
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecSQL(string sql, ref string ErrorInfo)
{
int ret=1;
OracleCommand cmd = new OracleCommand(sql, conn);
try
{
ret = cmd.ExecuteNonQuery();
}
catch (OracleException ex) // catches only Oracle errors
{
ret = -1;
switch (ex.Code)
{
case 1:
ErrorInfo = "Error attempting to insert duplicate data.";
return ret;
case 12545:
ErrorInfo = "The database is unavailable.";
return ret;
default:
ErrorInfo = "Database error:" + ex.Message.ToString();
return ret;
}
}
catch (Exception e)
{
ret = -1;
ErrorInfo = e.Message;
}
return ret;
}
/// <summary>
/// Simplify the creation of a Oracle command object by allowing
/// a stored procedure and optional parameters to be provided
/// </summary>
/// <remarks>
/// e.g.:
/// OracleCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
/// </remarks>
/// <param name="connection">A valid OracleConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="sourceColumns">An array of string to be assigned as the source columns of the stored procedure parameters</param>
/// <returns>A valid OracleCommand object</returns>
public static OracleCommand CreateCommand(OracleConnection connection, string spName, params string[] sourceColumns)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
// Create a OracleCommand
OracleCommand cmd = new OracleCommand( spName, connection );
cmd.CommandType = CommandType.StoredProcedure;
// If we receive parameter values, we need to figure out where they go
if ((sourceColumns != null) && (sourceColumns.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided source columns to these parameters based on parameter order
for (int index=0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
// Attach the discovered parameters to the OracleCommand object
AttachParameters (cmd, commandParameters);
}
return cmd;
}
public frmSheetMetaData(IWorkspace workSpace,OracleCommand oraCmd)
{
m_textBoxActive = new List<TextBox>();
m_labelActive = new List<Label>();
m_customField = new List<string>();
InitializeComponent();
m_textBox = new TextBox[9];
m_textBox[0] = textBox24;
m_textBox[1] = textBox18;
m_textBox[2] = textBox20;
m_textBox[3] = textBox16;
m_textBox[4] = textBox15;
m_textBox[5] = textBox17;
m_textBox[6] = textBox21;
m_textBox[7] = textBox19;
m_textBox[8] = textBox14;
m_label = new Label[9];
m_label[0] = label26;
m_label[1] = label20;
m_label[2] = label25;
m_label[3] = label18;
m_label[4] = label17;
m_label[5] = label19;
m_label[6] = label24;
m_label[7] = label21;
m_label[8] = label16;
this.m_workSpace = workSpace;
m_gdata = new GDBData(this.m_workSpace);
m_oraCmd = oraCmd;
InitCustom();
}
public DataTable ListarAreas()
{
DataTable dt = new DataTable();
try
{
OracleConnection cnx = Conexion.ObtenerConexionOracle();
OracleCommand cmd = new OracleCommand(String.Format("select * from area"), cnx);
cnx.Open();
OracleDataReader reader;
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// lleno el DataTable con el datareader
dt.Load(reader);
}
catch (Exception)
{
return null;
}
return dt;
}
//Filling the booking details into the return cars datagridview
public void bookingsDetails(DataGridView grd_Bookings)
{
var tomorrow = DateTime.Today.AddDays(1).ToString("dd-MMM-yyyy");
var dueDate = DateTime.Today.AddDays(-5).ToString("dd-MMM-yyyy");
string query_String = string.Format("SELECT DISTINCT B.Customer_Id ,Forename, Surname ,B.Car_Reg ,Cost ,Customer_Type,Return_Date FROM Cars CA, " +
"Customers C JOIN Bookings B ON C.Customer_Id = B.Customer_Id " +
"WHERE Car_Status = 'U' AND CA.Car_Reg = B.Car_Reg AND Return_Date BETWEEN '{0}' AND '{1}'", dueDate, tomorrow );
// MessageBox.Show(query_String);
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "Bookings");
grd_Bookings.DataSource = data_Set.Tables["Bookings"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception ex) {MessageBox.Show("" + ex);}
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the OracleCommand to be prepared</param>
/// <param name="connection">a valid OracleConnection, on which to execute this command</param>
/// <param name="transaction">a valid OracleTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or Oracle statement)
command.CommandText = commandText;
command.CommandTimeout = 200000;
//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
public int GetNextIdWithTransNewAppWithArea(string area, System.Data.OracleClient.OracleCommand cmd)
{
cmd.CommandText = "";
cmd.CommandText = @"SELECT nvl(to_number(max(SUBSTR(APPLICATION_NO, 4, 10))),0)+1 FROM CGD_APPLICANT_MASTER
where to_number(SUBSTR(APPLICATION_NO, 2, 2)) ='" + area + "'";
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
public void CargarQuerys()
{
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_devueltas2");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando3;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tablasTareasDevueltas.DataSource = dt;
tablasTareasDevueltas.DataBind();
if (tablasTareasDevueltas.Rows.Count == 0)
{
// Response.Write("<script>('No hay tareas pendientes')</script>");
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_y_flujo");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreoEncargado_2.Text;
comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando3;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tablaTareas.DataSource = dt;
tablaTareas.DataBind();
if (tablaTareas.Rows.Count == 0)
{
Response.Write("<script>('No hay tareas pendientes')</script>");
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
}
protected void btnCargarTareas_2_Click(object sender, EventArgs e)
{
int id = ddlCambiarEstado2.SelectedIndex + 1;
if (id == 4)
{
try
{
// Get the currently selected row using the SelectedRow property.
GridViewRow rowTarea = tablaCarga2.SelectedRow;
// In this example, the first column (index 0) contains
string textTarea = rowTarea.Cells[1].Text;
string idTarea = id.ToString();
ora2.Open();
System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("update_tarea_reasignar3");
comando2.Connection = ora2;
comando2.CommandType = System.Data.CommandType.StoredProcedure;
comando2.Parameters.Add("P_IDTAREA", OracleType.Int32).Value = Int32.Parse(textTarea);
comando2.ExecuteNonQuery();
ora2.Close();
CargarQuery();
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea devuelta, revisar el apartado de Agregar Tarea Subordinada');</script>");
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error');</script>");
}
}
else
{
try
{
// Get the currently selected row using the SelectedRow property.
GridViewRow rowTarea = tablaCarga2.SelectedRow;
// In this example, the first column (index 0) contains
string textTarea = rowTarea.Cells[1].Text;
string idTarea = id.ToString();
ora2.Open();
System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("update_tarea_sub_estado");
comando2.Connection = ora2;
comando2.CommandType = System.Data.CommandType.StoredProcedure;
comando2.Parameters.Add("p_estado", OracleType.Int32).Value = id;
comando2.Parameters.Add("P_IDTAREA", OracleType.Int32).Value = Int32.Parse(textTarea);
comando2.ExecuteNonQuery();
ora2.Close();
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Actualizada');</script>");
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error');</script>");
}
}
CargarQuery();
}
//This rountine writes log entry to web_log table in vitap database
public static void WriteWebLog(string appName, string allProcess, string clientIP, string userID, System.Data.OracleClient.OracleConnection conVITAP)
{
System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand();
if (conVITAP.State != ConnectionState.Open)
{
conVITAP.Open();
}
cmdVITAP.Connection = conVITAP;
cmdVITAP.CommandText = Queries.newWebLog(appName, allProcess, clientIP, userID);
cmdVITAP.ExecuteNonQuery();
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="tablename"></param>
/// <param name="optype"></param>
/// <param name="sqlstr"></param>
/// <param name="htParam"></param>
/// <param name="strParamList"></param>
/// <param name="tr"></param>
/// <returns></returns>
private bool Execute(string tablename, string optype, string sqlstr, Hashtable htParam, string strParamList, Transaction tr)
{
#region 创建命令
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandText = sqlstr;
dbCommand.Transaction = tr;
#endregion
#region 设置参数（对于OLEDB方式而言，参数的顺序必须与SQL语句中一致）
foreach (string strkey in strParamList.Split(new char[] { ',' }))
{
#if DT_OLEDB
if (htParam[strkey].GetType() != typeof(DateTime))
{
dbCommand.Parameters.Add(new DbParameter(strkey, htParam[strkey]));
}
else
{
//OleDB连Access时，这个日期类型必须特别指定；连Oracle不需要；SQL没测过
dbCommand.Parameters.Add(new DbParameter(strkey, System.Data.OleDb.OleDbType.Date)).Value = htParam[strkey];
}
#else
dbCommand.Parameters.Add(new DbParameter(strkey, htParam[strkey]));
#endif
}
#endregion
#region 提交命令
try
{
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
}
dbCommand.ExecuteNonQuery();
//DisConnection();
return(true);
}
catch (Exception e)
{
this.WriteLogInfo("error on " + this.GetType().Name + " " + tablename + ".Execute." + optype + ":\r\nsql:" + sqlstr + "\r\n" + e.Message);
return(false);
}
#endregion
}
protected void ingresarTarea_Click(object sender, EventArgs e)
{
try
{
lblidddl.Text = "";
//Para el Usuario
ora.Open();
//Llamar al Stored Procedure
Oracle.DataAccess.Client.OracleCommand comando1 = new Oracle.DataAccess.Client.OracleCommand("seleccionar_usuarios", ora);
comando1.CommandType = System.Data.CommandType.StoredProcedure;
//Asignar variable de cursor
comando1.Parameters.Add("p_correo", OracleDbType.Varchar2).Value = txtCorreo.Text;
comando1.Parameters.Add("o_id", OracleDbType.Int32, null, ParameterDirection.Output);
comando1.ExecuteNonQuery();
Oracle.DataAccess.Client.OracleDataReader dr = comando1.ExecuteReader();
int id = Int32.Parse(comando1.Parameters["o_id"].Value.ToString());
lblidddl.Text = id.ToString();
ora.Close();
//Para la Tarea
string tipo = ddlTipoTarea.SelectedItem.ToString();
lblidddl.Text = tipo;
int flujo = ddlFlujo.SelectedIndex + 1;
//FECHA
var date = fecha.SelectedDate;
ora2.Open();
System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_TAREA", ora2);
comando2.CommandType = System.Data.CommandType.StoredProcedure;
comando2.Parameters.Add("P_NOMBRE", OracleType.VarChar).Value = txtNombre.Text;
comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtDescripcion.Text;
comando2.Parameters.Add("P_IDUSUARIO", OracleType.Int32).Value = id;
comando2.Parameters.Add("P_ESTADO", OracleType.Int32).Value = 4;
comando2.Parameters.Add("P_TIPOTAREA", OracleType.VarChar).Value = tipo;
comando2.Parameters.Add("P_FLUJO", OracleType.VarChar).Value = flujo;
comando2.Parameters.Add("P_FECHA", OracleType.DateTime).Value = date.ToString();
comando2.ExecuteNonQuery();
// ora2.Close();
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Insertada');</script>");
txtNombre.Text = "";
txtDescripcion.Text = "";
// txtCorreo.Text = "";
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>");
}
}
} //end of this method
public static bool IsPassowrdExpired(string loginID, System.Data.OracleClient.OracleConnection conVITAP)
{
bool valid = false;
System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand();
if (conVITAP.State != ConnectionState.Open)
{
conVITAP.Open();
}
cmdVITAP.Connection = conVITAP;
cmdVITAP.CommandText = "";
System.Data.OracleClient.OracleDataReader rdrReader = cmdVITAP.ExecuteReader();
return(valid);
}
public string MianProcess(string Company_code, string ConfID, string FielName, string TransType, string sp_name)
{
string retval = "";
try
{
System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(ConnectionString);
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
cmd.Parameters.Clear();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sp_name;
cmd.Parameters.Add("p_company_code", OracleType.VarChar, 2000).Value = Company_code;
cmd.Parameters.Add("P_CONF_ID", OracleType.VarChar, 2000).Value = ConfID;
cmd.Parameters.Add("P_FILE_NAME", OracleType.VarChar, 2000).Value = FielName;
cmd.Parameters.Add("P_trans_type", OracleType.VarChar, 2000).Value = TransType;
cmd.Parameters.Add("v_retval", OracleType.VarChar, 2000).Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
retval = cmd.Parameters["v_retval"].Value.ToString();
}
catch (Exception ex)
{
throw new Exception("Data Base Error: " + ex.Message);
}
#region
//try
//{
// System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(ConnectionString);
// con.Open();
// System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
// cmd.CommandText = "sp_raw_DATALOAD1";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add("p_company_code", OracleType.VarChar, 2000).Value = Company_code;
// cmd.Parameters.Add("P_CONF_ID", OracleType.VarChar, 2000).Value = ConfID;
// cmd.Parameters.Add("P_FILE_NAME", OracleType.VarChar, 2000).Value = FielName;
// int result = cmd.ExecuteNonQuery();
// con.Close();
// retval = "Data successfully Processed.";
//}
//catch (Exception ex)
//{
// retval = ex.Message;
//}
#endregion
return(retval);
}
public int InputLong(string strSql, string data)
{
System.Data.OracleClient.OracleCommand command = null;
command = new OracleCommand();
command.Connection = this.dbConnection as OracleConnection;
command.Transaction = dbTrans as OracleTransaction;
command.CommandType = System.Data.CommandType.Text;
command.Parameters.Clear();
command.CommandText = strSql + "";
string strParam = "";
int i = strSql.IndexOf(":", 0);
if (i <= 0)
{
this.Err = "未指定参数！" + strSql;
this.WriteErr();
return(-1);
}
strParam = strSql.Substring(i + 1, 1);
OracleParameter param = command.Parameters.Add(strParam, OracleType.LongVarChar);
param.Direction = System.Data.ParameterDirection.Input;
// Assign Byte Array to Oracle Parameter
param.Value = data;
try
{
command.ExecuteNonQuery();
}
catch (OracleException ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.Code;
this.WriteErr();
return(-1);
}
catch (Exception ex)
{
this.Err = ex.Message;
this.WriteErr();
return(-1);
}
return(0);
}
private DbParameter[] DiscoverSpParameterSet(string strProcedureName, bool includeReturnValueParameter)
{
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandText = strProcedureName;
dbCommand.CommandType = CommandType.StoredProcedure;
try
{
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
}
// 查找存储过程的参数
DbCommandBuilder.DeriveParameters(dbCommand);
// 如果包含返回Result value参数（自动生成的，不需要传进去）,如果有，则删除
// Oracle中，function产生此参数，procedure则不产生
// SQL SERVER中，procedure 产生此参数,function 未测试，应该也会有
if (includeReturnValueParameter)
{
dbCommand.Parameters.RemoveAt(0);
}
// 返回找到的参数的副本
DbParameter[] discoveredParameters = new DbParameter[dbCommand.Parameters.Count];
dbCommand.Parameters.CopyTo(discoveredParameters, 0);
// 参数一刀两断,没有关系了.
dbCommand.Parameters.Clear();
// 初始化各参数为DBNull
foreach (DbParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " GetStoredProcedureParamters:" + strProcedureName + "\r\n" + e.Message);
return(null);
}
}
private static void prepareCommand(ref System.Data.OracleClient.OracleCommand command, System.Data.OracleClient.OracleConnection connection, System.Data.CommandType commandType, string commandText)
{
try
{
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
}
catch (Exception ex)
{
// Handle the exception in UI level only
throw ex;
}
}
protected void btnGuardarFlujo_Click(object sender, EventArgs e)
{
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_flujo", ora2);
comando2.CommandType = System.Data.CommandType.StoredProcedure;
comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtNombreFlujo.Text;
comando2.Parameters.Add("P_ESTADO", OracleType.VarChar).Value = "Pendiente de Aprobación";
comando2.ExecuteNonQuery();
ora2.Close();
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Flujo Agregado');</script>");
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado");
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tblFlujo.DataSource = dt;
tblFlujo.DataBind();
ora2.Close();
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
}
/// <summary>
/// 执行存储过程，什么值也不返回
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
public static void RunProcedure(string storedProcName, OracleParameter[] parameters)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
System.Data.OracleClient.OracleCommand cmd = BuildQueryCommand(connection, storedProcName, parameters);
//new OracleCommand(storedProcName, connection);
//cmd.CommandType = CommandType.StoredProcedure;
//foreach (OracleParameter parameter in parameters)
//{
// cmd.Parameters.Add(parameter);
//}
//cmd.Parameters.Add(new OracleParameter("p_userid", OracleType.Int32, 9)).Value = p_userid;
//cmd.Parameters.Add(new OracleParameter("p_yyyy", OracleType.Int32, 7)).Value = p_yyyy;
//cmd.Parameters.Add(new OracleParameter("p_mm", OracleType.Int32, 9)).Value = p_mm;
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ex.Message.ToString();
throw new Exception(ex.Message);
// Console.WriteLine(ex.Message.ToString());
}
finally
{
connection.Close();
}
}
}
protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)
{
int idx = DataList1.SelectedIndex;
Label lbl = (Label)DataList1.Items[idx].FindControl("Label2");
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString());
System.Data.OracleClient.OracleCommand myCommand = new System.Data.OracleClient.OracleCommand("SELECT * FROM wf_cm_images WHERE ID = '" + lbl.Text + "'", conn);
conn.Open();
System.Data.OracleClient.OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
try
{
while (myReader.Read())
{
System.Data.OracleClient.OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("IMAGE"));
if (!myLob.IsNull)
{
string FN = myReader.GetString(myReader.GetOrdinal("IMAGE_NAME"));
//Use buffer to transfer data
byte[] b = new byte[myLob.Length];
//Read data from database
myLob.Read(b, 0, (int)myLob.Length);
Response.AddHeader("content-disposition", "attachment;filename=" + FN);
Response.ContentType = "application/octectstream";
Response.BinaryWrite(b);
Response.End();
}
}
}
finally
{
myReader.Close();
conn.Close();
}
}
protected void reasignar_Click(object sender, EventArgs e)
{
try
{
// Get the currently selected row using the SelectedRow property.
GridViewRow rowTarea = tablasTareasDevueltas.SelectedRow;
string textTarea = rowTarea.Cells[1].Text;
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("update_tarea_reasignar2");
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreoReasignar.Text;
comando.Parameters.Add("P_IDTAREA", OracleType.Int32).Value = Int32.Parse(textTarea);
comando.ExecuteNonQuery();
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
Response.Redirect(HttpContext.Current.Request.Url.ToString(), true);
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <param name="tr">事务,null表示不启用事务</param>
/// <returns>是否执行成功</returns>
public bool ExecSQL(string sqlstr, Transaction tr)
{
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandText = sqlstr;
dbCommand.Transaction = tr;
try
{
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
}
dbCommand.ExecuteNonQuery();
return(true);
}
catch (Exception e)
{
WriteLogInfo("errorn on " + this.GetType().Name + " ExecSQL:" + sqlstr + "\r\n" + e.Message);
return(false);
}
}
public static System.Data.DataTable ExecuteDataTable(ref System.Data.OracleClient.OracleCommand command, System.Data.OracleClient.OracleConnection connection, System.Data.CommandType commandType, string commandText)
{
System.Data.OracleClient.OracleDataAdapter oracleDataAdapter = new System.Data.OracleClient.OracleDataAdapter();
System.Data.DataSet dataSet = new System.Data.DataSet();
System.Data.DataTable dataTable = new System.Data.DataTable();
try
{
prepareCommand(ref command, connection, commandType, commandText);
oracleDataAdapter.SelectCommand = command;
oracleDataAdapter.Fill(dataSet);
if (dataSet != null && dataSet.Tables.Count > 0)
{
dataTable = dataSet.Tables[0];
}
}
catch (Exception ex)
{
// Handle the exception in UI level only
throw ex;
}
return(dataTable);
}
public OracleParameterCollection(OracleCommand parent) : base(parent)
{
}
public static int ExecuteSql(string SQLString)
{
int num2;
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand command = new OracleCommand(SQLString, connection);
try
{
connection.Open();
num2 = command.ExecuteNonQuery();
}
catch (OracleException exception)
{
connection.Close();
throw new Exception(exception.Message);
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
return num2;
}
public void SetUp ()
{
if (connection_string == null)
return;
connection = new OracleConnection (connection_string);
connection.Open ();
using (command = connection.CreateCommand ()) {
// create the tables
command.CommandText =
"create table oratest (id number(10), text varchar2(64),"
+ " text2 varchar2(64) )";
command.ExecuteNonQuery ();
command.CommandText =
"create table culture_test (id number(10), value1 float,"
+ " value2 number(20,10), value3 number (20,10))";
command.ExecuteNonQuery ();
command.CommandText =
"create table oratypes_test (id NUMBER(10), value1 VARCHAR2(100),"
+ " value2 DATE)";
command.ExecuteNonQuery ();
command.CommandText =
"create or replace procedure params_pos_test (param1 in number,"
+ "param2 in number,param3 in number,result out number) as"
+ " begin result:=param3; end;";
command.ExecuteNonQuery ();
}
}