public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OracleCommand cmd = new OracleCommand("Select * From Orders", con);
OracleDataReader rdr = cmd.ExecuteReader();
//change a connection's state without closing the datareader (should fail
try
{
BeginCase("InvalidOperationException");
try
{
((IDbConnection)con).ChangeDatabase("msdb");
ExpectedExceptionNotCaught(typeof(InvalidOperationException).FullName);
}
catch (InvalidOperationException ex)
{
ExpectedExceptionCaught(ex);
}
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public static Applicant GetItem(int Id)
{
Applicant applicant = null;
using (OracleConnection loanDbConn =
new OracleConnection(
ConnStringFactory.getConnString(
ConnStringFactory.ConnStringType.Oracle))) {
loanDbConn.Open();
using (OracleCommand getApplicantByIdCommand = new OracleCommand()) {
getApplicantByIdCommand.CommandType = CommandType.StoredProcedure;
getApplicantByIdCommand.CommandText = "ApplicantsPKG.getApplicantById";
getApplicantByIdCommand.Connection = loanDbConn;
getApplicantByIdCommand.Parameters.AddWithValue("Id", Id);
OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
outputCursor.Direction = ParameterDirection.Output;
getApplicantByIdCommand.Parameters.Add(outputCursor);
using (OracleDataReader applicantReader =
getApplicantByIdCommand.ExecuteReader()) {
if (applicantReader.Read()) {
applicant = FillDataRecord(applicantReader);
}
}
}
}
return applicant;
}
public OracleDataReader execQuery(string sql)
{
OracleCommand cmd = new OracleCommand(sql);
OracleDataReader reader = null;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
try
{
reader = cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
}
return reader;
}
//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
}//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 CE_Servidor FechayHoradelServidor()
{
//la funcion me permite recuperar los datos del colaborador en el objeto CE_Colaborador
try
{
CE_Servidor objce_servidortemp = new CE_Servidor();
OracleConnection cnx = Conexion.ObtenerConexionOracle();
OracleCommand cmd = new OracleCommand("select current_timestamp from dual", cnx);
cnx.Open();
OracleDataReader reader;
reader = cmd.ExecuteReader();
//verifico si hay filas devueltas
Boolean hayfilas = reader.HasRows;
if (hayfilas == true)
{//si hay filas devuelvo el resultado de la consulta
while (reader.Read())
{
objce_servidortemp.datetimeservidor = Convert.ToDateTime(reader["current_timestamp"]);
}
}
//Cerrar conexion
cnx.Close();
return objce_servidortemp;
}
catch (Exception ex)
{
throw ex;
}
}
} //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 List<String> make_connection()
{
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
string selectStmt = string.Format("select car.*, mar.IMENAMARKA from VOZILA car, MODELI mod, MARKA mar where car.IMENAMODEL = mod.IMENAMODEL and mod.IMENAMARKA = mar.IMENAMARKA ");
OracleCommand cmd = new OracleCommand(selectStmt, conn);
OracleDataReader dataReader = cmd.ExecuteReader();
List<String> redovi = new List<string>();
while (dataReader.Read())
{
string temp = dataReader.GetString(1).ToString() + " ";
temp = temp + dataReader.GetString(16).ToString() + " ";
temp = temp + dataReader.GetString(15).ToString() + " ";
temp = temp + dataReader.GetValue(2).ToString() + " ";
temp = temp + dataReader.GetValue(3).ToString() + " ";
temp = temp + dataReader.GetValue(4).ToString() + " ";
temp = temp + dataReader.GetValue(5).ToString() + " ";
temp = temp + dataReader.GetValue(6).ToString() + " ";
temp = temp + dataReader.GetValue(7).ToString() ;
redovi.Add(temp);
}
conn.Close();
conn.Dispose();
return redovi;
}
private void OK_Click( object sender, EventArgs e )
{
try
{
OracleConnection conn = ConnectionOracle.creaConnection();
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "select count(*) from admins where id='"+tno.Text+"' and psw='"+Psw.Text+"'";
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader[0].ToString ().Equals ( "1" ))
{
this.Hide();
View_illstudent stu = new View_illstudent();
stu.Show();
return;
}
}
MessageBox.Show("用户没有找到");
// cmd.ExecuteScalar() + "";
// int raw = cmd.ExecuteOracleScalar();
}
catch (Exception e1)
{
MessageBox.Show(e1.Message);
throw;
}
}
public void run()
{
Exception exp = null;
//prepare data
base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
int intRecordsAffected = 0;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OracleCommand cmd = new OracleCommand("Update Employees set Title = 'title' where EmployeeID = 100", con);
OracleDataReader rdr = cmd.ExecuteReader();
rdr.Read();
intRecordsAffected = rdr.RecordsAffected;
try
{
BeginCase("RecordsAffected");
Compare(intRecordsAffected,1 );
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public static OracleDataReader ExecuteReader(string sqlStr, List<OracleParameter> parListOracleParameters)
{
OracleDataReader oracleDataReader = null;
OracleConnection oracleConnection = new OracleConnection();
oracleConnection.ConnectionString = OracleHelper.connectStr;
try
{
if (oracleConnection.State != ConnectionState.Open)
{
oracleConnection.Open();
}
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = sqlStr;
for (int i = 0; i < parListOracleParameters.Count; i++)
{
oracleCommand.Parameters.Add(parListOracleParameters[i]);
}
oracleDataReader = oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);
oracleCommand.Dispose();
}
catch (Exception ex)
{
oracleDataReader.Close();
oracleConnection.Close();
throw ex;
}
return oracleDataReader;
}
public static OracleDataReader ExecuteReader(string sqlStr)
{
OracleDataReader oracleDataReader = null;
OracleConnection oracleConnection = new OracleConnection();
oracleConnection.ConnectionString = OracleHelper.connectStr;
try
{
if (oracleConnection.State != ConnectionState.Open)
{
oracleConnection.Open();
}
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = sqlStr;
oracleCommand.CommandType = CommandType.Text;
oracleDataReader = oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);
oracleCommand.Dispose();
}
catch (Exception ex)
{
oracleDataReader.Close();
oracleConnection.Close();
throw ex;
}
return oracleDataReader;
}
public static ApplicantList GetList()
{
ApplicantList applicants = new ApplicantList();
using (OracleConnection localDbConn =
new OracleConnection(
ConnStringFactory.getConnString(
ConnStringFactory.ConnStringType.Oracle))) {
localDbConn.Open();
using (OracleCommand getAllApplicantCommand = new OracleCommand()) {
getAllApplicantCommand.CommandType = CommandType.StoredProcedure;
getAllApplicantCommand.CommandText = "ApplicantsPKG.getAllApplicants";
getAllApplicantCommand.Connection = localDbConn;
OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
outputCursor.Direction = ParameterDirection.Output;
getAllApplicantCommand.Parameters.Add(outputCursor);
using (OracleDataReader applicantListReader =
getAllApplicantCommand.ExecuteReader()) {
if (applicantListReader.HasRows) {
while (applicantListReader.Read()) {
applicants.Add(FillDataRecord(applicantListReader));
}
}
}
}
}
return applicants;
}
public ActionResult Create(CreateChart Chart)
{
if (ModelState.IsValid)
{
string connectionString = "Data Source=HRMSDEV;user id=PERSON;password=PERSON;Unicode=True;";
string CHART_ID ;
using (OracleConnection connection = new OracleConnection(connectionString))
{
string number = null;
number = "";
OracleCommand cmd1 = new OracleCommand();
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "select nvl(max(substr(CHART_ID,7,3)),0) from KMIINTRANET_MASTER_CHART where substr(CHART_ID,1,6)='" + Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + "'";
cmd1.Connection = connection;
connection.Open();
objDataReader = cmd1.ExecuteReader();
if (objDataReader.HasRows)
{
objDataReader.Read();
number = Convert.ToString(Convert.ToInt32(objDataReader[0]) + 1);
if (number.Length == 1)
{
number = "00" + number;
}
else if (number.Length == 2)
{
number = "0" + number;
}
else if (number.Length == 3)
{
number = number;
}
}
cmd1 = null;
connection.Close();
OracleCommand cmd2 = new OracleCommand();
CHART_ID = Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + number;
byte[] tempFile = new byte[Chart.ChartFile.InputStream.Length];
Chart.ChartFile.InputStream.Read(tempFile, 0, tempFile.Length);
cmd2.CommandText = "INSERT INTO KMIINTRANET_MASTER_CHART(CHART_ID,CHART_TITLE,CHART_FILE,CREATE_BY,CREATE_DATE) VALUES (:chartid,:title,:chartfile,'" + Session["USER"] + "',sysdate)";
cmd2.CommandType = CommandType.Text;
cmd2.Connection = connection;
connection.Open();
cmd2.Parameters.Add(":chartfile", OracleType.Blob).Value = tempFile;
cmd2.Parameters.Add(":title", OracleType.VarChar).Value = Chart.title.ToString();
cmd2.Parameters.Add(":chartid", OracleType.VarChar).Value = CHART_ID;
cmd2.ExecuteNonQuery();
cmd2 = null;
connection.Close();
return RedirectToAction("Index");
}
}
return View();
}
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>
/// 执行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 void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OracleCommand cmd = new OracleCommand("Select * From Customers", con);
OracleDataReader rdr = cmd.ExecuteReader();
try
{
BeginCase("column REGION ordinal");
Compare(rdr.GetOrdinal("REGION"),6 );
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("column not exists");
try
{
int i = rdr.GetOrdinal("blabla");
}
catch (Exception ex) {exp=ex;}
Compare(exp.GetType().FullName,typeof(IndexOutOfRangeException).FullName);
exp=null;
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleCommand cmd = new OracleCommand("Customers", con);
cmd.CommandType = CommandType.TableDirect;
con.Open();
OracleDataReader rdr = cmd.ExecuteReader();
rdr.Read();
try
{
BeginCase("GetName field 0");
string str = rdr.GetName(0);
Compare(str.ToUpper(),"CUSTOMERID" );
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("GetName last field ");
string str = rdr.GetName(rdr.FieldCount -1);
Compare(str.ToUpper(),"FAX" );
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
private void ReportTitle_Load(string jsdid)
{
string StrCon = FrmLogin.strDataCent;
OracleConnection connection = new OracleConnection(StrCon);
string str = "select ztidmc,GHDWMC,XSJSDH,jsfsmc,jsr,czrmc,ZHJSRQ from VIEW_JT_C_XSJSD where XSJSDID='" + jsdid + "'";
OracleCommand comm = new OracleCommand(str, connection);
try
{
connection.Open();
OracleDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
this.txtGHDW.Text = reader["GHDWMC"].ToString();
this.txtZTIDMC.Text = reader["ztidmc"].ToString();
this.txtJSFS.Text = reader["jsfsmc"].ToString();
this.txtJSDH.Text = reader["XSJSDH"].ToString();
this.txtJSR.Text = reader["jsr"].ToString();
this.txtZDR.Text = reader["czrmc"].ToString();
this.txtJSRQ.Text = reader["ZHJSRQ"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public void fillAccountDetails(DataGridView dataGridAccounts)
{
string query_String = "SELECT DISTINCT C.Customer_Id, COUNT(C.Customer_Id) AS NO_OF_Bookings, SUM(Balance)AS Balance ,Forename,Surname,Address,Town,County,Phone_No, Account_Status " +
"FROM Customers C RIGHT JOIN Accounts A ON C.Customer_Id = A.Customer_Id WHERE Account_Status = 'U' AND C.Customer_Id IN(SELECT Customer_Id FROM Accounts )" +
"GROUP BY C.Customer_Id,Address,Forename,Surname ,Town,County,Phone_No ,Account_Status ORDER BY C.Customer_Id";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_Adapter = new OracleDataAdapter(cmd);
data_Set = new DataSet();
data_Adapter.Fill(data_Set, "Accounts");
dataGridAccounts.DataSource = data_Set.Tables["Accounts"];
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
data_reader.Read();
connection.Close();
}
catch (Exception)
{
MessageBox.Show("No record Found");
}
}
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);
}
}
protected void ddlVX1WorktimeSec_SelectedIndexChanged(object sender, EventArgs e)
{
if(ddlVX1WorktimeSec.SelectedIndex == 0) {
lbVX1WorkTimeTime.Text = "";
lbVX1WorkTimeDes.Text = "";
return;
}
using(OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) {
con.Open();
using(OracleCommand com = new OracleCommand("SELECT * FROM LEV_WORKTIME_SEC WHERE WORKTIME_SEC_ID = " + ddlVX1WorktimeSec.SelectedValue, con)) {
using(OracleDataReader reader = com.ExecuteReader()) {
while(reader.Read()) {
string shi = int.Parse(reader.GetValue(1).ToString()).ToString("00");
string smi = int.Parse(reader.GetValue(2).ToString()).ToString("00");
string sho = int.Parse(reader.GetValue(3).ToString()).ToString("00");
string smo = int.Parse(reader.GetValue(4).ToString()).ToString("00");
string time = shi + ":" + smi + " - " + sho + ":" + smo;
string des = reader.GetValue(6).ToString();
lbVX1WorkTimeTime.Text = time;
lbVX1WorkTimeDes.Text = des;
hfHI.Value = shi;
hfMI.Value = smi;
hfHO.Value = sho;
hfMO.Value = smo;
}
}
}
}
}
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;
}
}
/*
* This method selects all details of a selected combo box class description and displays the details in the selected text boxes
*/
public void selectDetails(ComboBox cmo_Description, TextBox txt_Class_Id, TextBox txt_Display_Desc, TextBox txt_Rate)
{
try
{
//Select query that brings back the details of car class using description
string query_String2 = String.Format("SELECT * FROM Car_Class WHERE Description = '{0}'", cmo_Description.Text);
connection.Open();
cmd = connection.CreateCommand();
/*
* Creating the query string to retrieve the required record when a customer id is supplied
*/
cmd.CommandText = query_String2;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
/*
* Assigning the values of the Car_Class table to the appropriate text boxes after retreving
*/
if (!data_reader.HasRows)
return;
txt_Class_Id.Text = data_reader.GetValue(0).ToString();
txt_Display_Desc.Text = data_reader.GetValue(1).ToString();
txt_Rate.Text = data_reader.GetValue(2).ToString();
}
connection.Close();
}
catch (Exception) // Catching exception if the id dosent match any records
{
//MessageBox.Show("No Record Found");
MessageBox.Show("No Records Found", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OracleCommand cmd = new OracleCommand("Select * From Customers", con);
OracleDataReader rdr = cmd.ExecuteReader();
try
{
BeginCase("Before execute");
Compare(rdr.Depth , 0);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("After execute");
rdr.Read();
Compare(rdr.Depth , 0);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
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;
}
public string CampoChavesDaTabela(string tabela)
{
if (!string.IsNullOrEmpty(tabela))
{
string SQL = @"SELECT ALL_CONS_COLUMNS.COLUMN_NAME
FROM ALL_CONSTRAINTS, ALL_CONS_COLUMNS
WHERE ALL_CONSTRAINTS.OWNER = :OWNER
AND ALL_CONSTRAINTS.TABLE_NAME = :TABLE_NAME
AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'P'
AND ALL_CONSTRAINTS.OWNER = ALL_CONS_COLUMNS.OWNER
AND ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME";
IConexao conexao = FactoryConexao.GetConexao(BD);
using (var select = new OracleCommand(SQL, conexao.Oralceconnection))
{
select.Parameters.Add("OWNER", this.owner);
select.Parameters.Add("TABLE_NAME", tabela);
var read = select.ExecuteReader();
string toReturn = string.Empty;
while (read.Read())
{
toReturn = (read["COLUMN_NAME"].ToString().ToUpper());
}
read.Close();
conexao.Oralceconnection.Close();
return toReturn;
}
}
else
{
MessageBox.Show(Messagen.IMFORMARTABELAECAMPO, Messagen.ATENCAO, MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
public DataTable sp_Busqueda_Captura_X_Codigo_Abastecimiento(string codigo_abastecimiento)
{
DataTable dt = new DataTable();
try
{
OracleConnection cnx = Conexion.ObtenerConexionOracle();
OracleCommand cmd = new OracleCommand(String.Format(" select c.dni, c.nombres, c.apellidos, t.codigo_abastecimiento, t.snapshotpicture, t.snapshotvideo,t.volumen_retirado,a.idtanque from colaboradores c inner join registroes r on r.dni=c.dni inner join tanquedetallemov t on t.idregistro=r.idregistro inner join abastecimiento a on a.codigo_abastecimiento=t.codigo_abastecimiento where t.codigo_abastecimiento = '{0}'", codigo_abastecimiento), 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;
}
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;
}
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 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 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();
}
}
//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);}
}
private void button3_Click(object sender, EventArgs e)
{
Evento evento;
EventoDAO eventoDAO = new EventoDAO();
evento = eventoDAO.Buscar(textBox1.Text);
try
{
using (OracleCommand Comando = new OracleCommand())
{
Comando.Connection = ConexaoBD.Conectar();
Comando.CommandType = System.Data.CommandType.Text;
Comando.CommandText = "SELECT ID_EVENTO, NOME, DATA_INICIO, DATA_FIM, LOCAL, ID_RESPONSAVEL FROM EVENTO WHERE ID_EVENTO = :idevento";
Comando.Parameters.Add(":idevento", OracleType.Number).Value = OracleNumber.Parse(textBox1.Text);
OracleDataReader leitor = Comando.ExecuteReader();
while (leitor.HasRows)
{
leitor.Read();
leitor.NextResult();
}
}
}
catch (OracleException ex) {
MessageBox.Show(ex.Message);
}
}
/*
* Finding the difference between the start booking date and end booking date
* to calculate the cost of a booking
*/
public void calculateBookingCost(DateTimePicker startDate, DateTimePicker endDate, ComboBox cbo_Description, TextBox txt_Cost)
{
connection.Close();
DateTime dt1 = Convert.ToDateTime(startDate.Text);
DateTime dt2 = Convert.ToDateTime(endDate.Text);
TimeSpan timeSpan = dt2 - dt1;
int numberOfDays = timeSpan.Days;
string query_String = "SELECT Car_Rate FROM Car_Class WHERE Description ='" + cbo_Description.Text + "'";
connection.Open();
try
{
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
if (data_reader.HasRows)
{
data_reader.Read();
txt_Cost.Text = ("" + data_reader.GetInt32(0) * numberOfDays);
}
connection.Close();
}
catch (Exception) { }
}
/*
* The Description combo box is populated with each car class description from the database
*/
public void display_Description(ComboBox cmo_Description)
{
cmo_Description.Items.Clear();
string query_String = "Select Description FROM Car_Class ";
try
{
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = query_String;
data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
while (data_reader.Read())
{
if (!data_reader.HasRows)
return;
cmo_Description.Items.Add(data_reader[0].ToString());
}
connection.Close();
}
catch (Exception)
{
MessageBox.Show("Cannot Retrive Description", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
/// <summary>
/// 输出blob
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public byte[] OutputBlob(string strSql)
{
//string block="SELECT image from test_image WHERE id = 100";//INTO :b
// Set command to create Anonymous PL/SQL Block
this.command.Connection = this.con as OracleConnection;
if (this.command.Transaction == null) //没有事务，用全局事务
{
this.command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as OracleTransaction;
}
command.CommandText = strSql + "";
command.CommandType = System.Data.CommandType.Text;
System.Data.OracleClient.OracleDataReader reader;
command.Parameters.Clear();
// The anonymous PL/SQL block retrieves the images as an output parameter
try
{
//cmd.ExecuteNonQuery();
reader = command.ExecuteReader();
}
catch (OracleException ex)
{
this.Err = "执行产生错误!" + ex.Message;
this.ErrCode = strSql;
this.DBErrCode = ex.Code;
this.WriteErr();
return(null);
}
catch (Exception ex)
{
this.Err = ex.Message;
this.ErrorException = ex.InnerException + "+ " + ex.Source;
this.WriteErr();
return(null);
}
reader.Read();
// Create a byte array
byte[] byteData = new byte[0];
// fetch the value of Oracle parameter into the byte array
//byteData = (byte[])(cmd.Parameters[0].Value);
try
{
byteData = (byte[])(reader[0]);
}
catch (Exception ex) {
this.Err = ex.Message;
reader.Close();
return(null);
}
reader.Close();
return(byteData);
}
private void btnNoBinds_Click(object sender, System.EventArgs e)
{
// this illustrates the "traditional" approach
// that does not use bind variables
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
OracleDataReader dataReader;
// we must build our command text string
// since we are concatenating values at runtime
cmdNoBinds1.CommandText = "select first_name, last_name from employees where employee_id = " + selectedItem.ToString();
// get our data reader
dataReader = cmdNoBinds1.ExecuteReader();
// get the results - our query will only return 1 row
// since we are using the primary key
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
// get the data that Lookup 2 performed above
// again, we must build the string here in code
// rather than in the design environment
cmdNoBinds2.CommandText = "select email, phone_number from employees where first_name = '" + lblFirstName.Text + "' and last_name = '" + lblLastName.Text + "'";
// get our data reader
dataReader = cmdNoBinds2.ExecuteReader();
// get the results - our query will only return 1 row
// since we are using known unique values for the first
// and last names
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
}
}
} //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);
}
private void btnGetIDs_Click(object sender, System.EventArgs e)
{
try
{
// get a data reader
OracleDataReader dataReader = cmdGetIDs.ExecuteReader();
// simply iterate the result set and add
// the values to the drop down list
while (dataReader.Read())
{
cbEmpIds.Items.Add(dataReader.GetDecimal(0));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception Caught");
}
}
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();
}
}
private void btnLookup2_Click(object sender, System.EventArgs e)
{
// we need to bind in order since the Microsoft driver
// does not support the BindByName property
cmdLookup2.Parameters[0].Value = lblFirstName.Text;
cmdLookup2.Parameters[1].Value = lblLastName.Text;
// get our data reader
OracleDataReader dataReader = cmdLookup2.ExecuteReader();
// get the results - our query will only return 1 row
// since we are using known unique values for the first
// and last names
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
}
private void btnLookup1_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
// we need to set the parameter value
cmdLookup1.Parameters[0].Value = Convert.ToDecimal(selectedItem.ToString());
// get our data reader
OracleDataReader dataReader = cmdLookup1.ExecuteReader();
// get the results - our query will only return 1 row
// since we are using the primary key
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
}
}
//public ActionResult Search()
//{
// return View("Index");
//}
//[HttpPost]
public ActionResult Search(string BondLicenseNo)
{
if (!(System.Web.HttpContext.Current.User.IsInRole("Bonder")))
{
var bonderslno = (from b in db.BONDERs where b.BONDLICENSENO == BondLicenseNo select b.BONDERSLNO).SingleOrDefault();
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection();
cmd.Connection = conn;
System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction();
cmd.Transaction = bmsTransaction;
cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", bonderslno);
System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader();
if (drBondStatus.HasRows)
{
drBondStatus.Read();
ViewBag.STATUS = drBondStatus.GetValue(0);
ViewBag.BSDATE = drBondStatus.GetValue(1);
ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2);
ViewBag.REMARKS = drBondStatus.GetValue(3);
}
try
{
return(View(getRenewalInfo(bonderslno)));
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
ViewBag.Message = "Insertion Failed";
return(View("RenewalForm"));
}
return(RedirectToAction("Index"));
}
else
{
USERPERMISSION permission = session.getStoredUserPermission();
var bonderName = permission.BONDER.BONDERNAME;
var bonderslno = (from b in db.BONDERs where b.BONDLICENSENO == BondLicenseNo && b.BONDERNAME == bonderName select b.BONDERSLNO).SingleOrDefault();
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection();
cmd.Connection = conn;
System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction();
cmd.Transaction = bmsTransaction;
cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", bonderslno);
System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader();
if (drBondStatus.HasRows)
{
drBondStatus.Read();
ViewBag.STATUS = drBondStatus.GetValue(0);
ViewBag.BSDATE = drBondStatus.GetValue(1);
ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2);
ViewBag.REMARKS = drBondStatus.GetValue(3);
}
try
{
return(View(getRenewalInfo(bonderslno)));
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
ViewBag.Message = "Insertion Failed";
return(View("RenewalForm"));
}
return(RedirectToAction("Index"));
}
}
protected override DbDataReader ExecuteDbDataReader(System.Data.CommandBehavior behavior)
{
return(new OracleDataReader(cmd.ExecuteReader(behavior)));
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
lblidddl.Visible = false;
txtCorreo.Text = Session["Value"].ToString();
Array enumList1 = Enum.GetValues(typeof(tiposTarea));
foreach (tiposTarea getTipoTarea in enumList1)
{
ddlTipoTarea.Items.Add(new ListItem(getTipoTarea.ToString(), ((int)getTipoTarea).ToString()));
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_devueltas");
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 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();
System.Data.OracleClient.OracleDataReader rdr = comando.ExecuteReader();
while (rdr.Read())
{
ddlFlujo.Items.Add(rdr.GetString(0).ToString());
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>");
}
}
}
/// <summary>
/// Fetch a VADIR formatted MOS report for a patient given the EDIPI or Name, SSN and DOB
/// </summary>
/// <param name="patient"></param>
/// <returns>TextReport with report text set to VADIR report</returns>
public TextReport getMOSReport(Patient patient)
{
if (!isValidMosPatient(patient))
{
throw new ArgumentException("Invalid patient. Need name, SSN and DOB or EDIPI");
}
//System.Data.OracleClient. OracleQuery query = new OracleQuery();
System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand();
command.CommandText = "BLUE_BUTTON.FETCHREPORT";
command.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter idParam = new System.Data.OracleClient.OracleParameter("VA_ID_IN", OracleType.Number);
//OracleParameter idParam = new OracleParameter("VA_ID_IN", OracleDbType.Decimal);
idParam.Direction = System.Data.ParameterDirection.Input;
idParam.Value = Convert.ToDecimal(patient.EDIPI);
command.Parameters.Add(idParam);
OracleParameter lNameParam = new System.Data.OracleClient.OracleParameter("LNAME_IN", OracleType.VarChar, 26);
//OracleParameter lNameParam = new OracleParameter("LNAME_IN", OracleDbType.Varchar2, 26);
lNameParam.Direction = System.Data.ParameterDirection.Input;
lNameParam.Value = "";
command.Parameters.Add(lNameParam);
OracleParameter ssnParam = new System.Data.OracleClient.OracleParameter("SSN_IN", OracleType.VarChar, 9);
//OracleParameter ssnParam = new OracleParameter("SSN_IN", OracleDbType.Varchar2, 9);
ssnParam.Direction = System.Data.ParameterDirection.Input;
ssnParam.Value = "";
command.Parameters.Add(ssnParam);
OracleParameter dobParam = new System.Data.OracleClient.OracleParameter("DOB_IN", OracleType.DateTime);
//OracleParameter dobParam = new OracleParameter("DOB_IN", OracleDbType.TimeStamp);
dobParam.Direction = System.Data.ParameterDirection.Input;
dobParam.Value = DBNull.Value;
command.Parameters.Add(dobParam);
OracleParameter returnParam = new OracleParameter("v_Return", OracleType.Clob);
//OracleParameter returnParam = new OracleParameter("v_Return", OracleDbType.Clob);
returnParam.Direction = System.Data.ParameterDirection.ReturnValue;
command.Parameters.Add(returnParam);
System.Data.OracleClient.OracleConnection cxn = new OracleConnection(_cxn.DataSource.ConnectionString);
cxn.Open();
try
{
command.Connection = cxn;
OracleDataReader reader = command.ExecuteReader();
//_cxn.connect();
//executeReader executeReader = delegate() { return command.ExecuteReader(); };
//OracleDataReader reader = (OracleDataReader)_cxn.query(query, executeReader);
//if (query.Command.Parameters["v_Return"] == null || query.Command.Parameters["v_Return"].Value == DBNull.Value)
//{
// return null;
//}
string text = ((System.Data.OracleClient.OracleLob)command.Parameters["v_Return"].Value).Value.ToString();
return(new TextReport()
{
Text = text
});
}
catch (Exception)
{
throw;
}
finally
{
cxn.Close();
}
}
public ActionResult Renewinfo(RenewalViewModel rvm, string[] doc, string[] rgno, string[] issuedate, string[] expdate, HttpPostedFileBase[] files)
{
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection();
cmd.Connection = conn;
System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction();
cmd.Transaction = bmsTransaction;
cmd.CommandText = "update BONDSTATUS set STATUS=:STATUS,BSDATE=:BSDATE,SUBMITTEDBYNM=:SUBMITTEDBYNM,REMARKS=:REMARKS"
+ " where BONDERSLNO=:BONDERSLNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("STATUS", Request["STATUS"]);
if (!String.IsNullOrEmpty(Request["ApplicationSubmissionDate"]))
{
cmd.Parameters.Add(new OracleParameter(":BSDATE", OracleType.DateTime)).Value = Request["ApplicationSubmissionDate"];
}
else
{
cmd.Parameters.Add(new OracleParameter(":BSDATE", OracleType.DateTime)).Value = DBNull.Value;
}
cmd.Parameters.AddWithValue("SUBMITTEDBYNM", Request["SubmittedBy"]);
cmd.Parameters.AddWithValue("REMARKS", Request["Remarks"]);
cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew);
try
{
try
{
bmsTransaction = conn.BeginTransaction();
}
catch { }
cmd.Transaction = bmsTransaction;
cmd.ExecuteNonQuery();
bmsTransaction.Commit();
}
catch
{
bmsTransaction.Rollback();
}
if (Request["STATUS"] == "Cm")
{
cmd.CommandText = "update BONDAPPLICATIONPROGRESS set READYFORAPP=:READYFORAPP where BONDERSLNO=:BONDERSLNO and BSNO=:BSNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew);
cmd.Parameters.AddWithValue("BSNO", BondInfo.BSNoToRenew);
cmd.Parameters.AddWithValue("READYFORAPP", "Y");
try
{
try
{
bmsTransaction = conn.BeginTransaction();
}
catch { }
cmd.Transaction = bmsTransaction;
cmd.ExecuteNonQuery();
bmsTransaction.Commit();
}
catch
{
bmsTransaction.Rollback();
}
}
int p = 1;
if (doc != null)
{
try
{
for (int i = 0; i < doc.Length; i++)
{
if (doc[i] != null && doc[i] != "")
{
DOCUMENTATTACHMENT D = new DOCUMENTATTACHMENT();
var path = "";
D.ATTCHSLNO = (Int16)p;
//foreach (var outitem in rvm)
//{
// foreach (var item in outitem.Bonder)
// {
D.BONDERSLNO = BondInfo.bondSlNoToRenew;
//}
//foreach (var item in outitem.Bondstatus)
//{
D.BSNO = BondInfo.BSNoToRenew;
// }
//}
if (files[i] != null)
{
var filename = Path.GetFileName(files[i].FileName);
path = Path.Combine(Server.MapPath(Url.Content("~/Uploads/")), filename);
files[i].SaveAs(path);
D.ATTACHFILENM = path;
}
D.DOCHEADINGNAME = doc[i];
D.RGATTCHNAME = rgno[i];
//D.ISSUEDATE = Convert.ToDateTime(issuedate[i], CultureInfo.CurrentCulture);
//D.EXPDATE = Convert.ToDateTime(expdate[i], CultureInfo.CurrentCulture);
if (!String.IsNullOrEmpty(issuedate[i]))
{
D.ISSUEDATE = DateTime.ParseExact(issuedate[i], "dd/MM/yyyy", null);
}
if (!String.IsNullOrEmpty(expdate[i]))
{
D.EXPDATE = DateTime.ParseExact(expdate[i], "dd/MM/yyyy", null);
}
db.DOCUMENTATTACHMENTs.Add(D);
p++;
db.SaveChanges();
}
}
ViewBag.Message = "Successfully Inserted";
//return View(renewalviewmodel);
//return Search(BIMS.CommonAppSet.BondInfo.bondLicenseNoToRenew);
//return View("Search",rvm);
//return View("RenewalForm");
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
ViewBag.Message = "Insertion Failed";
return(View("RenewalForm"));
}
}
cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew);
System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader();
if (drBondStatus.HasRows)
{
drBondStatus.Read();
ViewBag.STATUS = drBondStatus.GetValue(0);
ViewBag.BSDATE = drBondStatus.GetValue(1);
ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2);
ViewBag.REMARKS = drBondStatus.GetValue(3);
}
return(View("Search", getRenewalInfo(BondInfo.bondSlNoToRenew)));
//return View("RenewalForm");
}