/*
* 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));
}