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(); }
//Filling the booking details into the return cars datagridview public void bookingsDetails(DataGridView grd_Bookings) { var tomorrow = DateTime.Today.AddDays(1).ToString("dd-MMM-yyyy"); var dueDate = DateTime.Today.AddDays(-5).ToString("dd-MMM-yyyy"); string query_String = string.Format("SELECT DISTINCT B.Customer_Id ,Forename, Surname ,B.Car_Reg ,Cost ,Customer_Type,Return_Date FROM Cars CA, " + "Customers C JOIN Bookings B ON C.Customer_Id = B.Customer_Id " + "WHERE Car_Status = 'U' AND CA.Car_Reg = B.Car_Reg AND Return_Date BETWEEN '{0}' AND '{1}'", dueDate, tomorrow ); // MessageBox.Show(query_String); try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_Adapter = new OracleDataAdapter(cmd); data_Set = new DataSet(); data_Adapter.Fill(data_Set, "Bookings"); grd_Bookings.DataSource = data_Set.Tables["Bookings"]; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record data_reader.Read(); connection.Close(); } catch (Exception ex) {MessageBox.Show("" + ex);} }
/// <summary> /// 执行sql /// </summary> /// <param name="strSql"></param> /// <param name="strDataSet"></param> /// <returns></returns> public int ExecQuery(string strSql, ref string strDataSet, string strXSLFileName) { this.command.Connection = this.con as OracleConnection; if (this.command.Transaction == null) //没有事务,用全局事务 { this.command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as OracleTransaction; } this.command.CommandType = System.Data.CommandType.Text; this.command.Parameters.Clear(); this.command.CommandText = strSql + ""; try { this.TempReader1 = this.command.ExecuteReader(); XmlDocument doc = new XmlDocument(); XmlNode root; XmlElement node, row; doc.AppendChild(doc.CreateXmlDeclaration("1.0", "GB2312", "")); if (strXSLFileName != null && strXSLFileName != "") { string PI = "type='text/xsl' href='" + strXSLFileName + "'"; System.Xml.XmlProcessingInstruction xmlProcessingInstruction = doc.CreateProcessingInstruction("xml-stylesheet", PI); doc.AppendChild(xmlProcessingInstruction); } root = doc.CreateElement("Table"); doc.AppendChild(root); while (this.TempReader1.Read()) { row = doc.CreateElement("Row"); for (int i = 0; i < this.TempReader1.FieldCount; i++) { node = doc.CreateElement("Column"); node.SetAttribute("Name", this.TempReader1.GetName(i).ToString()); node.InnerText = this.TempReader1[i].ToString() + ""; row.AppendChild(node); } root.AppendChild(row); } strDataSet = doc.OuterXml; TempReader1.Close(); } catch (OracleException ex) { this.Err = "执行产生错误!" + ex.Message; this.ErrCode = strSql; this.DBErrCode = ex.Code; this.WriteErr(); return(-1); } catch (Exception ex) { this.Err = "执行语句产生错误!" + ex.Message; this.ErrorException = ex.InnerException + "+ " + ex.Source; this.ErrCode = strSql; this.WriteErr(); return(-1); } WriteDebug("执行查询sql语句!" + strSql); return(0); }
public static string ToJSON(OracleDataReader rdr) { StringBuilder sb = new StringBuilder(); if (rdr.HasRows) { sb.Append("["); while (rdr.Read()) { sb.Append("{"); for (var i = 0; i < rdr.FieldCount; i++) { sb.Append("\""+rdr.GetName(i)+"\":\""); sb.Append(rdr[i].ToString().Trim() + "\","); } sb.Remove(sb.Length - 1, 1); sb.Append("},"); } sb.Remove(sb.Length - 1, 1); sb.Append("]"); } else { sb.Append("[]"); } return sb.ToString(); }
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); } }
/* * 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) { } }
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"); } }
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)); }
/* * 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); } }
//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
} //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
/* * 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); }
/// <summary> /// 执行sql语句 重载 /// </summary> /// <param name="strSql"></param> /// <param name="strDataSet">返回DataSet xml</param> /// <returns></returns> public int ExecQuery(string strSql, ref string strDataSet) { this.command.Connection = this.con as OracleConnection; if (this.command.Transaction == null) //没有事务,用全局事务 { this.command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as OracleTransaction; } this.command.CommandType = System.Data.CommandType.Text; this.command.Parameters.Clear(); this.command.CommandText = strSql + ""; try { this.TempReader1 = this.command.ExecuteReader(); XmlDocument doc = new XmlDocument(); XmlNode root; XmlNode node, table; root = doc.CreateElement("DataSet"); doc.AppendChild(root); while (this.TempReader1.Read()) { table = doc.CreateElement("Table"); for (int i = 0; i < this.TempReader1.FieldCount; i++) { node = doc.CreateElement(this.TempReader1.GetName(i).ToString()); node.InnerText = this.TempReader1[i].ToString() + ""; table.AppendChild(node); } root.AppendChild(table); } strDataSet = doc.OuterXml; TempReader1.Close(); } catch (OracleException ex) { this.Err = "执行产生错误!" + ex.Message; this.ErrCode = strSql; this.DBErrCode = ex.Code; this.WriteErr(); return(-1); } catch (Exception ex) { this.Err = "执行语句产生错误!" + ex.Message; this.ErrorException = ex.InnerException + "+ " + ex.Source; this.ErrCode = strSql; this.WriteErr(); return(-1); } WriteDebug("执行查询sql语句!" + strSql); return(0); }
} //end of this method public static bool IsPassowrdExpired(string loginID, System.Data.OracleClient.OracleConnection conVITAP) { bool valid = false; System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand(); if (conVITAP.State != ConnectionState.Open) { conVITAP.Open(); } cmdVITAP.Connection = conVITAP; cmdVITAP.CommandText = ""; System.Data.OracleClient.OracleDataReader rdrReader = cmdVITAP.ExecuteReader(); return(valid); }
public void SetUp() { Exception exp = null; BeginCase("Setup"); try { con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); cmd = new OracleCommand("Select OrderID, CustomerID, OrderDate From Orders", con); rdr = cmd.ExecuteReader(); rdr.Read(); } catch(Exception ex){exp = ex;} finally {EndCase(exp);} }
public System.Data.OracleClient.OracleDataReader Get_From_DB(String qry) { System.Data.OracleClient.OracleDataReader myReader = null; try { OracleCommand comm = new OracleCommand(qry, this.GetConn()); //creates a command with given query and connection string myReader = comm.ExecuteReader(); //executes that command that returns rows. //System.Console.WriteLine(myReader.GetValue(0).ToString()+""); } catch (Exception ex) { MessageBox.Show(ex.Message + "in qry and conn"); } return(myReader); }
public DataTable Consulta(string query) { DataTable dt = new DataTable(); if(conexao.State == ConnectionState.Open) { command = conexao.CreateCommand(); command.CommandText = query; reader = command.ExecuteReader(); dt.Load(reader); reader.Close(); return dt; } else { MessageBox.Show("Erro ao conectar-se"); return null; } }
public List <UsuarioSistema> ConsultarUsuario(UsuarioSistema user) { ConexaoOracle objConexao = new ConexaoOracle(); List <UsuarioSistema> list = new List <UsuarioSistema>(); UsuarioSistema usu; try { objConexao.AdicionarParametro("P_LOGIN", user.login); objConexao.AdicionarParametro("P_NOME", user.nome); objConexao.AdicionarParametro("P_DEPARTAMENTO", user.departamento); objConexao.AdicionarParametro("P_EMAIL", user.email); objConexao.AdicionarParametroCursor("dados"); System.Data.OracleClient.OracleDataReader leitor = objConexao.ObterLeitor("FUN_PKG_USUARIO.LISTAR_USUARIOS_IMPORTADOS"); while (leitor.Read()) { usu = new UsuarioSistema(); usu.departamento = leitor["DEPARTAMENTO"].ToString(); usu.email = leitor["EMAIL"].ToString(); usu.login = leitor["LOGIN"].ToString(); usu.nome = leitor["NOME"].ToString(); usu.dt_inclusao = DateTime.Parse(leitor["DT_INCLUSAO"].ToString()); usu.id_usuario = int.Parse(leitor["ID_USUARIO"].ToString()); usu.id_status = int.Parse(leitor["status"].ToString()); usu.descricao_status = usu.id_status == 0 ? "INATIVO" : "ATIVO"; list.Add(usu); } leitor.Dispose(); } catch (Exception ex) { throw new Exception("Problemas contate o administrador do sistema: \\n" + ex.Message); } finally { objConexao.Dispose(); } return(list); }
/* * 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 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 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 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 void SetUp() { Exception exp = null; BeginCase("Setup"); try { con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); cmd = new OracleCommand("Select EmployeeID From Employees Where FirstName = 'Oved'", con); cmd.CommandType = CommandType.Text; con.Open(); rdr = cmd.ExecuteReader(); rdr.Read(); typeName = rdr.GetDataTypeName(0); con.Close(); } catch(Exception ex){exp = ex;} finally {EndCase(exp);} }
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); }
public List <UsuarioHistorico> ConsultarMovimentacao(UsuarioSistema user) { ConexaoOracle objConexao = new ConexaoOracle(); List <UsuarioHistorico> list = new List <UsuarioHistorico>(); UsuarioHistorico usu; try { objConexao.AdicionarParametro("P_LOGIN", user.login); objConexao.AdicionarParametroCursor("dados"); System.Data.OracleClient.OracleDataReader leitor = objConexao.ObterLeitor("FUN_PKG_USUARIO.LISTAR_MOVIMENTACAO_USUARIO"); while (leitor.Read()) { usu = new UsuarioHistorico(); usu.nome = leitor["nome"].ToString(); usu.login = leitor["LOGIN"].ToString(); usu.dt_inclusao = DateTime.Parse(leitor["DT_INCLUSAO"].ToString()); usu.ds_justitificativa = leitor["ds_justificativa"].ToString(); usu.id_status = int.Parse(leitor["status"].ToString()); usu.descricao_status = usu.id_status == 0 ? "INATIVO" : "ATIVO"; list.Add(usu); } leitor.Dispose(); } catch (Exception ex) { throw new Exception("Problemas contate o administrador do sistema: \\n" + ex.Message); } finally { objConexao.Dispose(); } return(list); }
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(); } }
/// <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); }
internal OracleDataReaderWrapper(OracleDataReader innerReader) : base(innerReader) { }
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> /// Executes a select command that selects all columns specified by this collection, /// from the table specified by this collections TableName property, /// filtered by the given unique id. /// The select command is executed using ExecuteReader. /// </summary> /// <param name="a_sUniqueId">The unique id to use as rows filter.</param> /// <param name="a_oReader">Reader that holds the results of the select command.</param> /// <param name="a_oConnection">A connection object that serves the reader a_oReader for retrivieng data.</param> /// <remarks> /// While the OracleDataReader is in use, the associated OracleConnection is open and busy serving the OracleDataReader. /// While in this state, no other operations can be performed on the OracleConnection other than closing it. /// This is the case until the Close method of the OracleDataReader is called. /// It is the users responsibility to close the OracleConnection explicitly when it is no longer needed. /// </remarks> public virtual void ExecuteSelectReader(string a_sUniqueId, out OracleDataReader a_oReader, out OracleConnection a_oConnection) { OracleCommand l_cmdSelect = BuildSelectCommand(a_sUniqueId); l_cmdSelect.Connection.Open(); a_oConnection = l_cmdSelect.Connection; a_oReader = l_cmdSelect.ExecuteReader(); }
OracleDataReader ExecuteReader (CommandBehavior behavior) { AssertConnectionIsOpen (); AssertTransactionMatch (); AssertCommandTextIsSet (); moreResults = -1; bool hasRows = false; this.behavior = behavior; if (Transaction != null) Transaction.AttachToServiceContext (); OciStatementHandle statement = GetStatementHandle (); OracleDataReader rd = null; try { if (preparedStatement == null) PrepareStatement (statement); else preparedStatement = null; // OracleDataReader releases the statement handle bool isNonQuery = IsNonQuery (statement); BindParameters (statement); if (isNonQuery) ExecuteNonQueryInternal (statement, false); else { if ((behavior & CommandBehavior.SchemaOnly) != 0) statement.ExecuteQuery (true); else hasRows = statement.ExecuteQuery (false); UpdateParameterValues (); } if (Parameters.Count > 0) { for (int p = 0; p < Parameters.Count; p++) { OracleParameter parm = Parameters [p]; if (parm.OracleType.Equals (OracleType.Cursor)) { if (parm.Direction != ParameterDirection.Input) { rd = (OracleDataReader) parm.Value; break; } } } } if (rd == null) rd = new OracleDataReader (this, statement, hasRows, behavior); } finally { if (statement != null && rd == null) statement.Dispose(); } return rd; }
/* * 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 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 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"); }
OracleDataReader ExecuteReader(CommandBehavior behavior) { AssertConnectionIsOpen(); AssertTransactionMatch(); AssertCommandTextIsSet(); moreResults = -1; bool hasRows = false; this.behavior = behavior; if (Transaction != null) { Transaction.AttachToServiceContext(); } OciStatementHandle statement = GetStatementHandle(); OracleDataReader rd = null; try { if (preparedStatement == null) { PrepareStatement(statement); } else { preparedStatement = null; // OracleDataReader releases the statement handle } bool isNonQuery = IsNonQuery(statement); BindParameters(statement); if (isNonQuery) { ExecuteNonQueryInternal(statement, false); } else { if ((behavior & CommandBehavior.SchemaOnly) != 0) { statement.ExecuteQuery(true); } else { hasRows = statement.ExecuteQuery(false); } UpdateParameterValues(); } if (Parameters.Count > 0) { for (int p = 0; p < Parameters.Count; p++) { OracleParameter parm = Parameters [p]; if (parm.OracleType.Equals(OracleType.Cursor)) { if (parm.Direction != ParameterDirection.Input) { rd = (OracleDataReader)parm.Value; break; } } } } if (rd == null) { rd = new OracleDataReader(this, statement, hasRows, behavior); } } finally { if (statement != null && rd == null) { statement.Dispose(); } } return(rd); }
internal RefCountingOracleDataReaderWrapper(DatabaseConnectionWrapper connection, OracleDataReader innerReader) : base(innerReader) { this.connection = connection; this.connection.AddRef(); }
public OracleDataReaderWrapper(OracleDataReader reader) { this.innerReader = reader; }
public void recalculate(DateTimePicker start, DateTimePicker end, TextBox txt_Cost, ComboBox cbo_Car_Reg) { DateTime dt1 = Convert.ToDateTime(start.Text); DateTime dt2 = Convert.ToDateTime(end.Text); TimeSpan timeSpan = dt2 - dt1; int numberOfDays = timeSpan.Days; string query_String2 = string.Format("SELECT Car_Rate FROM Car_Class C JOIN Cars S ON C.Class_Id = S.Class_Id WHERE Car_Reg = '{0}'", cbo_Car_Reg.Text); try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = query_String2; 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) // Catching exception if the id dosent match any records { //MessageBox.Show("No Record Found"); MessageBox.Show("No Cars Found", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error); } }
//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); } }
/* * Filling the rental datagridview the details from the bookings table using surname to retrieve data */ public void select_SurnameRentals(TextBox txt_Search_Surname, DataGridView grd_Rental_Details) { string query_String = string.Format("SELECT * From Customers WHERE Surname LIKE '{0}'", txt_Search_Surname.Text); try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_Adapter = new OracleDataAdapter(cmd); data_Set = new DataSet(); data_Adapter.Fill(data_Set, "Rentals"); grd_Rental_Details.DataSource = data_Set.Tables["Rentals"]; 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 For supplied name"); } }
/* * Select all bookings that are collected */ public void selectUptodate(DataGridView dataGridDailyBookings) { string query_String = "SELECT Booking_Id ,Forename, Surname ,Address, Phone_No ,B.Car_Reg ,Make ,Model ,Cost,Customer_Type FROM Cars R, " + "Customers C JOIN Bookings B ON C.Customer_Id = B.Customer_Id " + "WHERE Booking_Status = 'C' AND Car_Status = 'U' AND R.Car_Reg = B.Car_Reg AND C.Customer_Id IN (SELECT Customer_Id FROM Bookings )"; try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_Adapter = new OracleDataAdapter(cmd); data_Set = new DataSet(); data_Adapter.Fill(data_Set, "DailyBookings"); dataGridDailyBookings.DataSource = data_Set.Tables["DailyBookings"]; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record data_reader.Read(); connection.Close(); } catch (Exception) { } }
public OracleDataReader(System.Data.OracleClient.OracleDataReader reader) { this.rdr = reader; }
public ActionResult Create(CreateMemo Memo) { if (ModelState.IsValid) { string connectionString = "Data Source=HRMSDEV;user id=PERSON;password=PERSON;Unicode=True;"; string MEMO_ID,AUTORIZE_DETAIL; using (OracleConnection connection = new OracleConnection(connectionString)) { if (Memo.Autorize.ToString() == "Department") { AUTORIZE_DETAIL = Memo.Autorize_detail; } else { AUTORIZE_DETAIL = ""; } string number = null; number = ""; OracleCommand cmd1 = new OracleCommand(); cmd1.CommandType = CommandType.Text; cmd1.CommandText = "select nvl(max(substr(MEMO_ID,7,3)),0) from KMIINTRANET_MASTER_MEMO where substr(MEMO_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(); MEMO_ID = Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + number; byte[] tempFile = new byte[Memo.MemoFile.InputStream.Length]; Memo.MemoFile.InputStream.Read(tempFile, 0, tempFile.Length); cmd2.CommandText = "INSERT INTO KMIINTRANET_MASTER_MEMO(MEMO_ID,MEMO_THEME,AUTORIZE,AUTORIZE_DETAIL,MEMO_FILE,VALID_FROM,VALID_UNTIL,CREATE_BY,CREATE_DATE) VALUES (:memoid,:theme,:autorize,:detail,:memofile,:validfrom,:validuntil,'" + Session["USER"] + "',sysdate)"; cmd2.CommandType = CommandType.Text; cmd2.Connection = connection; connection.Open(); cmd2.Parameters.Add(":memofile", OracleType.Blob).Value = tempFile; cmd2.Parameters.Add(":validuntil", OracleType.DateTime).Value = Memo.ValidUntil.ToString("MM/dd/yyyy"); cmd2.Parameters.Add(":validfrom", OracleType.DateTime).Value = Memo.ValidFrom.ToString("MM/dd/yyyy"); cmd2.Parameters.Add(":detail", OracleType.VarChar).Value = AUTORIZE_DETAIL; cmd2.Parameters.Add(":autorize", OracleType.VarChar).Value = Memo.Autorize.ToString(); cmd2.Parameters.Add(":theme", OracleType.VarChar).Value = Memo.Theme.ToString(); cmd2.Parameters.Add(":memoid", OracleType.VarChar).Value = MEMO_ID; cmd2.ExecuteNonQuery(); cmd2 = null; connection.Close(); return RedirectToAction("Index"); } } return View(); }
//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")); } }
public void select_Car_Reg( TextBox txt_Search_Reg, DataGridView grd_Booking_Details) { string query_String = string.Format("SELECT DISTINCT B.Customer_Id, Forename,Surname, B.Car_Reg,Make,Model,B.Cost,Customer_Type,Return_Date " + "From Cars Ca,Bookings B ,Customers C " + "WHERE Ca.Car_Reg = B.Car_Reg AND C.Customer_Id = B.Customer_Id AND B.Car_Reg LIKE '{0}'" , txt_Search_Reg.Text); try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_Adapter = new OracleDataAdapter(cmd); data_Set = new DataSet(); data_Adapter.Fill(data_Set, "Rentals"); grd_Booking_Details.DataSource = data_Set.Tables["Rentals"]; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record data_reader.Read(); connection.Close(); } catch (Exception) { MessageBox.Show(""); } }
public void extraCost(TextBox txt_Days_Late, TextBox txt_Search_Reg, TextBox txt_Additional_Costs,EventArgs e) { string query_String = string.Format("SELECT Car_Rate FROM Car_Class C JOIN Cars S ON C.Class_Id = S.Class_Id WHERE Car_Reg = '{0}'", txt_Search_Reg.Text); try { connection.Open(); 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(); if (numberOfDays < 0) { txt_Additional_Costs.Text =("" + 0); } else { txt_Additional_Costs.Text = (" " + data_reader.GetInt32(0) * numberOfDays); } } connection.Close(); } catch (Exception ex) // Catching exception if the id dosent match any records { ex.GetBaseException(); } }
public void selectBookingId( TextBox txt_Booking_Id) { string query_String = "SELECT MAX(Booking_Id) FROM Bookings"; try { connection.Open(); 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(); var count = data_reader.GetInt32(0); var nextId = count + 1; txt_Booking_Id.Text =("" + nextId) ; } connection.Close(); } catch (Exception ex) // Catching exception if the id dosent match any records { ex.GetBaseException(); } }
//Populating the car make combobox public void select_car_Make(ComboBox cmb_Car_Make) { cmb_Car_Make.Items.Clear(); string query_String = "SELECT Make FROM Cars"; 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; cmb_Car_Make.Items.Add(data_reader[0].ToString()); } connection.Close(); } catch (Exception) { error_Message(); } }
/* * 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); } } }
protected override void Dispose (bool disposing) { if (!disposed) { if (State == ConnectionState.Open) Close (); dataReader = null; transaction = null; oci = null; pool = null; conInfo.Username = string.Empty; conInfo.Database = string.Empty; conInfo.Password = string.Empty; connectionString = null; parsedConnectionString = null; base.Dispose (disposing); disposed = true; } }
/* * Inserting the avaliable cars into the car_Reg combo_Box that fit the booking criteria */ /// <summary> /// /// </summary> /// <param name="cbo_Car_Reg"></param> /// <param name="grd_Booking_Details"></param> public void fillReg(ComboBox cbo_Car_Reg, DataGridView grd_Booking_Details) { cbo_Car_Reg.Items.Clear(); cbo_Car_Reg.Text = ""; string query_String = "Select Car_Reg FROM Cars C JOIN Car_Class C2 ON C.Class_Id = C2.Class_Id WHERE Car_Status = 'A' AND Description = '" + description + "'AND Transmission = '" + transmission + "'AND C.location = '" + location + "'"; try { connection.Open(); 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) { //Select query that brings back the details of car class using description string query_String2 = "SELECT Car_Reg FROM Cars WHERE Car_Status = 'A' AND location = '" + location + "'"; cmd = connection.CreateCommand(); cmd.CommandText = query_String2; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record while (data_reader.Read()) { cbo_Car_Reg.Items.Add(data_reader[0].ToString()); } } else { while (data_reader.Read()) { cbo_Car_Reg.Items.Add(data_reader[0].ToString()); } } connection.Close(); } catch (Exception) { MessageBox.Show("No Cars Avaliable", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error); } }