/* * 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 int getmaxattendenceid(string eid) { int x = 0; //int x1 = chkentry(eid); //if (x1 == -1) { string qry = "select max(aid) from attendance"; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { string str = reader.GetValue(0).ToString(); if (str != "") { x = Convert.ToInt32(str); } } } catch (Exception e) { MessageBox.Show("problem with database"); } return(x); } /*else * return -1;*/ }
public int getTotalAbsent(string date) { string qry = "select distinct count(eid)\n" + "from employee\n" + "where eid not in(select emp_id\n" + "from attendance\n" + "where to_date('" + date + "','dd/mm/yyyy')=to_date(to_char(attendance_date,'dd/mm/yyyy'),'dd/mm/yyyy'))\n" + "and eid in(select eid\n" + "from ba_leave\n" + "where to_date('" + date + "','dd/mm/yyyy') between start_date and end_date)\n"; string totalEmp = ""; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { totalEmp = reader.GetValue(0).ToString(); } } catch (Exception e) { MessageBox.Show("problem with database"); } return(Convert.ToInt32(totalEmp)); }
/* * Creating the query string to retrieve the required record when a customer id is supplied */ public void customerDetails(TextBox txt_Retrieve_Id, TextBox txt_Forename,TextBox txt_Cust_Id, TextBox txt_Surname, TextBox txt_Address, TextBox txt_Town, TextBox txt_County, TextBox txt_Phone_No, TextBox txt_Cust_Type) { /* *Check For empty String when retrieve button is pressed */ string query_String = "SELECT * FROM Customers Where Customer_Id = " + txt_Cust_Id.Text; try { connection.Open();//opening a connection to the database 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; txt_Retrieve_Id.Text = data_reader.GetValue(0).ToString(); txt_Forename.Text = data_reader.GetValue(1).ToString(); txt_Surname.Text = data_reader.GetValue(2).ToString(); txt_Address.Text = data_reader.GetValue(3).ToString(); txt_Town.Text = data_reader.GetValue(4).ToString(); txt_County.Text = data_reader.GetValue(5).ToString(); txt_Phone_No.Text = data_reader.GetValue(6).ToString(); txt_Cust_Type.Text = data_reader.GetValue(7).ToString(); } } catch (Exception ex) { MessageBox.Show("" + ex); } finally { connection.Close(); } }
public string getStartTimefromDb(string empId) { string startTime = ""; string qry = "select Start_time from attendance where emp_id = '" + empId + "' and to_date(attendance_date,'dd-mm-yyyy')= to_date(sysdate,'dd-mm-yyyy')"; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { startTime = reader.GetValue(0).ToString(); } } catch (Exception e) { MessageBox.Show("problem with database"); } return(startTime); }
public string getEmpNamefromDb(string empId) { string name = ""; string qry = "select name from employee where eid = '" + empId + "'"; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { name = reader.GetValue(0).ToString(); } } catch (Exception e) { MessageBox.Show("problem with database"); } return(name); }
public string getEndTimefromDbwithdate(string empId, string date) { string endTime = ""; string qry = "select end_time from attendance where emp_id = '" + empId + "' and to_date(to_char(attendance_date,'dd/mm/yyyy'),'dd-mm-yyyy')= to_date('" + date + "','dd-mm-yyyy')"; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { endTime = reader.GetValue(0).ToString(); } } catch (Exception e) { MessageBox.Show("problem with database"); } return(endTime); }
public string getCurrentDate() { string qry = "select to_char(sysdate,'dd/mm/yyyy') from dual"; string date = ""; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { date = reader.GetValue(0).ToString(); } } catch (Exception e) { MessageBox.Show("problem with database"); } return(date); }
public int getTotalLeave(string date) { string qry = "select distinct count(eid) from ba_leave where to_date('" + date + "','dd/mm/yyyy') between start_date and end_date"; string totalEmp = ""; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { totalEmp = reader.GetValue(0).ToString(); } } catch (Exception e) { MessageBox.Show("problem with database"); } return(Convert.ToInt32(totalEmp)); }
public int getTotalEmp() { string qry = "select count(eid) from employee"; string totalEmp = ""; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { totalEmp = reader.GetValue(0).ToString(); } } catch (Exception e) { MessageBox.Show("problem with database"); } int x = Convert.ToInt32(totalEmp); return(x); }
public int checklogin(string eid) { int x = 0; string qry = "select count(*) from attendance where emp_id = '" + eid + "' and to_date(attendance_date,'dd-mm-yyyy') = to_date(SYSDATE,'dd-mm-yyyy')"; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { string aid; aid = reader.GetValue(0).ToString(); x = Convert.ToInt32(aid); } } catch (Exception e) { MessageBox.Show("problem with database"); } return(x); }
/// <summary> /// take table name and attribute name and return a string of array; /// </summary> /// <param name="tableName"></param> /// <param name="attribute"></param> /// <returns></returns> public string[] getDataItem(string tableName, string attribute) { string [] strArray = new string [N]; string qry = "select distinct " + attribute + " from " + tableName + " where " + attribute + " is not null order by " + attribute + " asc"; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { int i = 0; while (reader.Read()) { strArray[i] = reader.GetValue(0).ToString(); i++; } } catch (Exception e) { MessageBox.Show("problem with database"); } return(strArray); }
public string[] getEidlist() { string [] empidlist = new string[NOEMP]; string qry = "select distinct emp_id from attendance order by emp_id asc"; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { int i = 0; while (reader.Read()) { empidlist[i] = reader.GetValue(0).ToString(); i++; } } catch (Exception e) { MessageBox.Show("problem with database"); } return(empidlist); }
public String Get_From_Control_pass_verify(String str_control_id) { String str = ""; try { String qry = "select password from employee where eid = '" + str_control_id + "'"; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); if (reader.Read()) { str = reader.GetValue(0).ToString(); } else { str = ""; } } catch (Exception ex) { MessageBox.Show(ex.Message); } return(str); }
public int getAid(string eid, string atdDate) { int x = 0; string str; string qry = "select aid from attendance where emp_id='" + eid + "' and to_date(to_char(attendance_date,'dd/mm/yyyy'),'dd/mm/yyyy')= to_date('" + atdDate + "','dd/mm/yyyy')"; System.Data.OracleClient.OracleDataReader reader = Get_From_DB(qry); try { if (reader.Read()) { str = reader.GetValue(0).ToString(); if (str != "") { x = Convert.ToInt32(str); } } } catch (Exception e) { MessageBox.Show("problem with database"); } return(x); }
/* * Selecting all the customer details from the customer table */ public void retrieveCustomerDetails(TextBox txt_Search, TextBox txt_Customer_Id, ComboBox cbo_Forename, TextBox txt_Surname, TextBox txt_Address, TextBox txt_Town, TextBox txt_County, TextBox txt_Phone_No, TextBox txt_Cust_Type) { /* * Creating the query string to retrieve the required record when a customer id is supplied */ string query_String = "SELECT * FROM Customers Where Customer_Id =" + txt_Search.Text; try { connection.Open();//opening a connection to the database cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record /* * Assigning the values ot the customer table to the appropriate text boxes */ while (data_reader.Read()) { if (!data_reader.HasRows) return; txt_Customer_Id.Text = data_reader.GetValue(0).ToString(); cbo_Forename.Text = data_reader.GetValue(1).ToString(); txt_Surname.Text = data_reader.GetValue(2).ToString(); txt_Address.Text = data_reader.GetValue(3).ToString(); txt_Town.Text = data_reader.GetValue(4).ToString(); txt_County.Text = data_reader.GetValue(5).ToString(); txt_Phone_No.Text = data_reader.GetValue(6).ToString(); txt_Cust_Type.Text = data_reader.GetValue(7).ToString(); } } catch (Exception exc) // Catching exception if the id dosent match any records { MessageBox.Show("" + exc); } finally { connection.Close(); } }
/* * Returns a count of the number of bookings a customer Has had */ public void selectBookingDetails(TextBox txt_Retrieve_Id, TextBox txt_No_Bookings) { string query_String = "SELECT COUNT(Booking_Id) FROM Customers C JOIN Bookings B " + "ON C.Customer_Id = B.Customer_Id WHERE B.Customer_Id = " + txt_Retrieve_Id.Text; try { connection.Open();//opening a connection to the database 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; txt_No_Bookings.Text = data_reader.GetValue(0).ToString(); } connection.Close(); } catch (Exception) // Catching exception if the id dosent match any records { } }
/* * Inserting the customer details into the selected text boxes */ //// public void selectedName(ComboBox cbo_Forename, TextBox txt_Customer_Id, TextBox txt_Forename, TextBox txt_Address, TextBox txt_Town,TextBox txt_County, TextBox txt_Phone_No, TextBox txt_Cust_Type) { string query_String = string.Format("SELECT * FROM Customers Where Forename LIKE '{0}'", cbo_Forename.Text); try { connection.Open();//opening a connection to the database cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record /* * Assigning the values ot the customer table to the appropriate text boxes */ while (data_reader.Read()) { if (!data_reader.HasRows) return; txt_Customer_Id.Text = data_reader.GetValue(0).ToString(); txt_Forename.Text = data_reader.GetValue(1).ToString(); cbo_Forename.Text = data_reader.GetValue(2).ToString(); txt_Address.Text = data_reader.GetValue(3).ToString(); txt_Town.Text = data_reader.GetValue(4).ToString(); txt_County.Text = data_reader.GetValue(5).ToString(); txt_Phone_No.Text = data_reader.GetValue(6).ToString(); txt_Cust_Type.Text = data_reader.GetValue(7).ToString(); } } catch (Exception ex) // Catching exception if the id dosent match any records { MessageBox.Show("" + ex); } finally { connection.Close(); } }
/// <summary> /// Converts a NpgsqlDataReader to a DataSet /// <param name='reader'> /// NpgsqlDataReader to convert.</param> /// <returns> /// DataSet filled with the contents of the reader.</returns> /// </summary> public static DataSet ConvertDataReaderToDataSet(OracleDataReader reader, string tabela) { DataSet dataSet = new DataSet(); do { // Create new data table DataTable schemaTable = reader.GetSchemaTable(); DataTable dataTable = new DataTable(tabela); if (schemaTable != null) { // A query returning records was executed for (int i = 0; i < schemaTable.Rows.Count; i++) { DataRow dataRow = schemaTable.Rows[i]; // Create a column name that is unique in the data table string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>"; // Add the column definition to the data table DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]); dataTable.Columns.Add(column); } dataSet.Tables.Add(dataTable); // Fill the data table we just created while (reader.Read()) { DataRow dataRow = dataTable.NewRow(); for (int i = 0; i < reader.FieldCount; i++) dataRow[i] = reader.GetValue(i); dataTable.Rows.Add(dataRow); } } else { // No records were returned DataColumn column = new DataColumn("RowsAffected"); dataTable.Columns.Add(column); dataSet.Tables.Add(dataTable); DataRow dataRow = dataTable.NewRow(); dataRow[0] = reader.RecordsAffected; dataTable.Rows.Add(dataRow); } } while (reader.NextResult()); return dataSet; }
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"); }
public void MinDate() { BeginCase("Test Min date."); exp = null; string[] dateColumns; DateTime[] expectedValues; InitMinDates(out dateColumns, out expectedValues); try { con = new OracleConnection(ConnectedDataProvider.ConnectionString); cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = BuildMinDateTimeSelectSql(dateColumns); con.Open(); rdr = cmd.ExecuteReader(); Compare(true, rdr.HasRows); bool b = rdr.Read(); for (int i=0; i<dateColumns.Length && i<expectedValues.Length; i++) { int j=-1; j = rdr.GetOrdinal(dateColumns[i]); //DateTime result = rdr.GetDateTime(j); object result = rdr.GetValue(j); Compare(result, expectedValues[i]); } } catch (Exception ex) { exp = ex; } finally { EndCase(exp); if (rdr != null && !rdr.IsClosed) { rdr.Close(); } if (con != null && con.State != ConnectionState.Closed) { con.Close(); } } }
//Selecting the make and model of car that match the car reg public void carDetails(TextBox txt_Car_Reg, TextBox txt_Make, TextBox txt_Model) { try { //Select query that brings back the details of car class using description string query_String2 = string.Format("SELECT Make ,Model FROM Cars WHERE Car_Reg = '{0}'", txt_Car_Reg.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 /* * Assigning the values of the Car_Class table to the appropriate text boxes after retreving */ if (!data_reader.HasRows) { return; } else { data_reader.Read(); txt_Make.Text = data_reader.GetValue(0).ToString(); txt_Model.Text = data_reader.GetValue(1).ToString(); connection.Close(); } } catch (Exception ex) // Catching exception if the id dosent match any records { MessageBox.Show("" + ex); } }
public void selectBReg(TextBox txt_Search_Reg, TextBox txt_Car_Reg, TextBox txt_Make, TextBox txt_Model, TextBox txt_Return_Date, EventArgs e) { string query_String = string.Format("SELECT DISTINCT R.Car_Reg,Make,Model,Return_Date,Cost From Rentals R" + " JOIN Cars C ON R.Car_Reg = C.Car_Reg WHERE R.Car_Reg = '{0}'", txt_Search_Reg.Text); try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_reader = cmd.ExecuteReader(); while (data_reader.Read()) { if (!data_reader.HasRows) return; txt_Car_Reg.Text = data_reader.GetValue(0).ToString(); txt_Make.Text = data_reader.GetValue(1).ToString(); txt_Model.Text = data_reader.GetValue(2).ToString(); txt_Return_Date.Text = data_reader.GetValue(3).ToString(); } connection.Close(); } catch (Exception) { } }
public void run() { Exception exp = null; OracleCommand cmd = new OracleCommand(); try { BeginCase("CommandType - default"); Assert.AreEqual(cmd.CommandType , CommandType.Text ); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} #if NotWorking try { BeginCase("CommandType - TableDirect"); cmd.CommandType = CommandType.TableDirect; Assert.AreEqual(cmd.CommandType , CommandType.TableDirect); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} #endif try { BeginCase("CommandType - Text"); cmd.CommandType = CommandType.Text ; Assert.AreEqual(cmd.CommandType , CommandType.Text); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} #region ---- CommandType.Text using Parameters.Add ---- try { BeginCase("CommandType.Text using Parameters.Add"); cmd = new OracleCommand(); cmd.Connection = con; cmd.Transaction = tr; cmd.CommandType = CommandType.Text; cmd.CommandText = "call GH_REFCURSOR3(:IN_LASTNAME, :RCT_Employees)"; OracleParameter param1 = cmd.Parameters.Add("IN_LASTNAME", OracleType.VarChar,20); param1.Direction = ParameterDirection.Input; param1.Value = "Yavine"; cmd.Parameters.Add("RCT_Employees", OracleType.Cursor).Direction = ParameterDirection.Output; #if DAAB #if !JAVA if ((dbServerType == DataBaseServer.PostgreSQL)) { dr = PostgresOracleHelper.OLEDB4ODBCExecuteReader(cmd,true); } else #endif #endif { dr = cmd.ExecuteReader(); } if (dr.HasRows) { dr.Read(); Assert.AreEqual(dr.GetValue(0).ToString(),"1"); Assert.AreEqual(dr.GetString(1),"Yavine"); } else Assert.AreEqual("error","HasRows=0"); } catch(Exception ex) { exp = ex; } finally { if (dr != null)dr.Close(); if (con != null) {if (con.State == ConnectionState.Open) con.Close();} EndCase(exp); exp = null; } #endregion CommandTypeSP_Manual_InOutParameters(); #region ---- ORACLE CommandType.StoredProcedure using DeriveParameters ---- if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.Oracle) { try { BeginCase("ORACLE CommandType.StoredProcedure using DeriveParameters"); con.Open(); cmd = new OracleCommand(); cmd.Connection = con; cmd.Transaction = tr; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "GH_REFCURSOR3"; OracleCommandBuilder.DeriveParameters(cmd); cmd.Parameters[0].Value = "Yavine"; //cmd.Parameters.RemoveAt(1); // the ORACLE DAAB trick is to remove the out parameter dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); Assert.AreEqual(dr.GetValue(0).ToString(),"1"); Assert.AreEqual(dr.GetString(1),"Yavine"); } else Assert.AreEqual("error","HasRows=0"); } catch(Exception ex) { exp = ex; } finally { if (dr != null)dr.Close(); if (con != null) {if (con.State == ConnectionState.Open) con.Close();} EndCase(exp); exp = null; } } #endregion #region CommandType.StoredProcedure in order to repreduce bug 4003 if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.SQLServer) { exp = null; try { if (con.State == ConnectionState.Closed) con.Open(); BeginCase("Bug 4003"); OracleCommand cmd4003 = new OracleCommand("[mainsoft].[GH_DUMMY]",con); cmd4003.CommandType = CommandType.StoredProcedure; cmd4003.Parameters.Add("@EmployeeIDPrm","1"); cmd4003.ExecuteReader(); } catch (Exception ex) { exp=ex; } finally { if (con.State == ConnectionState.Open) con.Close(); EndCase(exp); } } #endregion }
/* * Selecting customer details from the customer table */ public void customerDetails(TextBox txt_Forename, TextBox txt_Customer_Id, TextBox txt_Surname, TextBox txt_Phone_No) { /* * Creating the query string to retrieve the required record when a customer id is supplied */ string query_String = "SELECT Forename ,Surname,Phone_No From Customers WHERE Customer_Id = " + txt_Customer_Id.Text; try { connection.Open();//opening a connection to the database cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record data_reader.Read(); /* * Assigning the values ot the customer table to the appropriate text boxes */ txt_Forename.Text = data_reader.GetValue(0).ToString(); txt_Surname.Text = data_reader.GetValue(1).ToString(); txt_Phone_No.Text = data_reader.GetValue(2).ToString(); connection.Close(); } catch (Exception) // Catching exception if the id dosent match any records { MessageBox.Show("No Record Found"); } }
public void selectDetails(TextBox txt_Total, DateTimePicker startDatePicker, DateTimePicker endDatePicker) { var startDate = startDatePicker.Value.ToString("dd-MMM-yyyy"); var endDate = endDatePicker.Value.ToString("dd-MMM-yyyy"); string query_String = "SELECT SUM(Cost) FROM Bookings WHERE Return_Date BETWEEN'" + startDate + "'AND'" + endDate + "'"; try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record data_reader.Read(); txt_Total.Text = data_reader.GetValue(0).ToString(); connection.Close(); } catch (Exception) { MessageBox.Show("No Records For the Chosen Period", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Information); } }
/// <summary> /// Create a MembershipUser object from a data reader. /// </summary> /// <param name="sqlDataReader">Data reader.</param> /// <returns>MembershipUser object.</returns> private MembershipUser GetUserFromReader( OracleDataReader oracleDataReader ) { object idPersonne = oracleDataReader.GetValue(0); string courriel = oracleDataReader.GetString(1); string motDePasse = oracleDataReader.GetString(2); string username = String.Empty; string passwordQuestion = String.Empty; if (oracleDataReader.GetValue(3) != DBNull.Value) { passwordQuestion = oracleDataReader.GetString(3); } string comment = String.Empty; if (oracleDataReader.GetValue(4) != DBNull.Value) { comment = oracleDataReader.GetString(4); } bool isApproved = true; if (oracleDataReader.GetBoolean(5) != null) { isApproved = oracleDataReader.GetBoolean(5); } bool isLockedOut = oracleDataReader.GetBoolean(6); if (oracleDataReader.GetBoolean(6) != null) { isLockedOut = oracleDataReader.GetBoolean(6); } DateTime creationDate = new DateTime(); if (oracleDataReader.GetValue(7) != DBNull.Value) { creationDate = oracleDataReader.GetDateTime(7); } DateTime lastLoginDate = new DateTime(); if (oracleDataReader.GetValue(8) != DBNull.Value) { lastLoginDate = oracleDataReader.GetDateTime(8); } DateTime lastActivityDate = new DateTime(); if (oracleDataReader.GetValue(9) != DBNull.Value) { lastActivityDate = oracleDataReader.GetDateTime(9); } DateTime lastPasswordChangedDate = new DateTime(); if (oracleDataReader.GetValue(10) != DBNull.Value) { lastPasswordChangedDate = oracleDataReader.GetDateTime(10); } DateTime lastLockedOutDate = new DateTime(); if (oracleDataReader.GetValue(11) != DBNull.Value) { lastLockedOutDate = oracleDataReader.GetDateTime(11); } MembershipUser membershipUser = new MembershipUser( this.Name, username, idPersonne, courriel, passwordQuestion, comment, isApproved, isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockedOutDate ); return membershipUser; }
//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")); } }
/// <summary> /// /// </summary> /// <param name="txt_Retrieve_Id"></param> /// <param name="txt_Booking_cost"></param> public void sumTotalBookings(TextBox txt_Retrieve_Id, TextBox txt_Booking_cost) { string query_String = "SELECT SUM(Cost) FROM Bookings WHERE Customer_Id = " + txt_Retrieve_Id.Text; try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_reader = cmd.ExecuteReader(); while (data_reader.Read()) { if (!data_reader.HasRows) return; txt_Booking_cost.Text = data_reader.GetValue(0).ToString(); } connection.Close(); } catch (Exception) { } }
/* * Selecting avalible cars from the selected car class */ public void fillAvaliableCars(DataGridView dataGrid_Avaliable_Cars, TextBox txt_Car_Reg, TextBox txt_Make, TextBox txt_Model) { string query_String = "SELECT * FROM Cars WHERE Car_Status = 'A'"; { try { connection.Open(); cmd = connection.CreateCommand(); /* * Creating the query string to retrieve the required record */ cmd.CommandText = query_String; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record /* * Assigning the values of the Car_Class table to the appropriate text boxes after retreving */ if (!data_reader.HasRows) { return; } else { while (data_reader.Read()) { txt_Car_Reg.Text = data_reader.GetValue(0).ToString(); txt_Make.Text = data_reader.GetValue(1).ToString(); txt_Model.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 Cars Found", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error); } } }
public override object GetValue(int ordinal) { return(rdr.GetValue(ordinal)); }