public List <Order> getOrders() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT ORDERS.* FROM ORDERS" }; var orders = new List <Order>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { orders.Add(new Order { OrderID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("ORDERID"))), OrderDeliv = reader.GetString(reader.GetOrdinal("ORDERDELIV")) == "1", CustID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("CUSTID"))), StoreID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("STOREID"))) }); } return(orders); }
public List <Pizza> getPizzas() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT Pizza.* FROM Pizza" }; var pizzas = new List <Pizza>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { pizzas.Add(new Pizza { BaseID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("BASEID"))), OrderID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("ORDERID"))), ToppingIdL = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("TOPPINGIDL"))), ToppingIdR = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("TOPPINGIDR"))), PizzaID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("PIZZAID"))) }); } return(pizzas); }
public List <Employee> getEmployees() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT EMPLOYEE.* FROM EMPLOYEE" }; var employees = new List <Employee>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { employees.Add(new Employee { EmployeeID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("EMPLOYEEID"))), EmployeeName = reader.GetString(reader.GetOrdinal("EMPLOYEENAME")), Bod = reader.GetDateTime(reader.GetOrdinal("BOD")), ShomerShabat = reader.GetString(reader.GetOrdinal("SHOMERSHABAT")) == "1", StoreID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("STOREID"))), RankID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("RANKID"))) }); } return(employees); }
public List <Store> getStores() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT STORE.* FROM STORE" }; var stores = new List <Store>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { stores.Add(new Store() { StoreID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("STOREID"))), StoreName = reader.GetString(reader.GetOrdinal("STORENAME")), StoreURL = reader.GetString(reader.GetOrdinal("STOREURL")), PhoneNumber = reader.GetString(reader.GetOrdinal("PHONENUMBER")), Kosher = reader.GetString(reader.GetOrdinal("KOSHER")) == "T", }); } return(stores); }
public List <Customer> getCustomers() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT CUSTOMERS.* FROM CUSTOMERS" }; var customers = new List <Customer>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { customers.Add(new Customer { CustID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("CUSTID"))), CustName = reader.GetString(reader.GetOrdinal("CUSTNAME")), CustAddress = reader.GetString(reader.GetOrdinal("CUSTADDRESS")), CustCc = reader.GetString(reader.GetOrdinal("CUSTCC")), CustCredit = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("CUSTCREDIT"))) }); } return(customers); }
protected void ingresarTarea_Click(object sender, EventArgs e) { try { lblidddl.Text = ""; //Para el Usuario ora.Open(); //Llamar al Stored Procedure Oracle.DataAccess.Client.OracleCommand comando1 = new Oracle.DataAccess.Client.OracleCommand("seleccionar_usuarios", ora); comando1.CommandType = System.Data.CommandType.StoredProcedure; //Asignar variable de cursor comando1.Parameters.Add("p_correo", OracleDbType.Varchar2).Value = txtCorreo.Text; comando1.Parameters.Add("o_id", OracleDbType.Int32, null, ParameterDirection.Output); comando1.ExecuteNonQuery(); Oracle.DataAccess.Client.OracleDataReader dr = comando1.ExecuteReader(); int id = Int32.Parse(comando1.Parameters["o_id"].Value.ToString()); lblidddl.Text = id.ToString(); ora.Close(); //Para la Tarea string tipo = ddlTipoTarea.SelectedItem.ToString(); lblidddl.Text = tipo; int flujo = ddlFlujo.SelectedIndex + 1; //FECHA var date = fecha.SelectedDate; ora2.Open(); System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_TAREA", ora2); comando2.CommandType = System.Data.CommandType.StoredProcedure; comando2.Parameters.Add("P_NOMBRE", OracleType.VarChar).Value = txtNombre.Text; comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtDescripcion.Text; comando2.Parameters.Add("P_IDUSUARIO", OracleType.Int32).Value = id; comando2.Parameters.Add("P_ESTADO", OracleType.Int32).Value = 4; comando2.Parameters.Add("P_TIPOTAREA", OracleType.VarChar).Value = tipo; comando2.Parameters.Add("P_FLUJO", OracleType.VarChar).Value = flujo; comando2.Parameters.Add("P_FECHA", OracleType.DateTime).Value = date.ToString(); comando2.ExecuteNonQuery(); // ora2.Close(); Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Insertada');</script>"); txtNombre.Text = ""; txtDescripcion.Text = ""; // txtCorreo.Text = ""; } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>"); } }
/// <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 command.CommandText = strSql + ""; command.CommandType = System.Data.CommandType.Text; Oracle.DataAccess.Client.OracleDataReader reader; // 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.Number; 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 { } reader.Close(); return(byteData); }
private void updateDataGrid() { Oracle.DataAccess.Client.OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT KUPACID,NAZIV,PIB FROM KUPACPRAVNOLICE"; cmd.CommandType = CommandType.Text; Oracle.DataAccess.Client.OracleDataReader dr = cmd.ExecuteReader(); // DataTable dt = new DataTable(); // dt.Load(dr); // myDataGrid.ItemsSource = dt.DefaultView; DataSet ds = new DataSet(); DataTable dt = new DataTable(); dt.Columns.Add("KUPACID"); dt.Columns.Add("NAZIV"); dt.Columns.Add("PIB"); ds.Tables.Add(dt); OracleParameter valueToReturn = null; while (dr.Read()) { DataRow red = dt.NewRow(); red["KUPACID"] = dr.GetValue(0); red["NAZIV"] = dr.GetString(1); Pib mBroj = (Pib)dr.GetValue(2); // Pib mm = new Pib(); // mm= (Pib)dr.GetValue(2); // red["PIB"] = Convert.ToInt64(mm.Pibb); red["PIB"] = Convert.ToInt64(mBroj.Pibb); } dr.Close(); }
public List <Dough> getDoughs() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT DOUGH.* FROM DOUGH" }; var doughs = new List <Dough>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { doughs.Add(new Dough { DoughID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("DOUGHID"))), Name = reader.GetString(reader.GetOrdinal("NAME")) }); } return(doughs); }
public List <PizzaBaseSize> getPizzaBaseSizes() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT PIZZABASESIZE.* FROM PIZZABASESIZE" }; var pizzaBaseSizes = new List <PizzaBaseSize>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { pizzaBaseSizes.Add(new PizzaBaseSize() { PbsID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("PBSID"))), PdsSize = reader.GetString(reader.GetOrdinal("PDSSIZE")) }); } return(pizzaBaseSizes); }
public List <ToppingType> getToppingTypes() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT TOPPINGTYPE.* FROM TOPPINGTYPE" }; var toppingTypes = new List <ToppingType>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { toppingTypes.Add(new ToppingType() { TopTypeID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("TOPTYPEID"))), TopTypeName = reader.GetString(reader.GetOrdinal("TOPTYPENAME")), TopTypeType = reader.GetString(reader.GetOrdinal("TOPTYPETYPE")) }); } return(toppingTypes); }
public List <Rank> getRanks() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT RANKS.* FROM RANKS" }; var ranks = new List <Rank>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { ranks.Add(new Rank() { RankID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("RANKID"))), RankDescription = reader.GetString(reader.GetOrdinal("RANKDESCRIPTION")), RankSalary = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("RANKSALARY"))) }); } return(ranks); }
public List <PizzaBase> getPizzaBases() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "SELECT PIZZABASE.* FROM PIZZABASE" }; var pizzaBases = new List <PizzaBase>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { pizzaBases.Add(new PizzaBase { BaseID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("BASEID"))), PbsID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("PBSID"))), Dough = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("DOUGH"))) }); } return(pizzaBases); }
/// <summary> /// Executes the query. /// </summary> /// <param name="dataSet">The data set to return containing the data.</param> /// <param name="tables">The datatable schema to add.</param> /// <param name="queryText">The query text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>The sql command containing any return values.</returns> public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, DataTable[] tables, string queryText, CommandType commandType, string connectionString, params DbParameter[] values) { // Initial connection objects. DbCommand dbCommand = null; OracleClient.OracleConnection orlConnection = null; IDataReader dataReader = null; try { // Create a new connection. using (orlConnection = new OracleClient.OracleConnection(connectionString)) { // Open the connection. orlConnection.Open(); // Create the command and assign any parameters. dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer( ConnectionContext.ConnectionDataType.OracleDataType, queryText), orlConnection); dbCommand.CommandType = commandType; if (values != null) { foreach (OracleClient.OracleParameter sqlParameter in values) { dbCommand.Parameters.Add(sqlParameter); } } // Load the data into the table. using (dataReader = dbCommand.ExecuteReader()) { dataSet = new System.Data.DataSet(); dataSet.Tables.AddRange(tables); dataSet.EnforceConstraints = false; dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables); dataReader.Close(); } // Close the database connection. orlConnection.Close(); } // Return the sql command, including // any parameters that have been // marked as output direction. return(dbCommand); } catch (Exception ex) { // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (dataReader != null) { dataReader.Close(); } if (orlConnection != null) { orlConnection.Close(); } } }
/// <summary> /// Executes the query. /// </summary> /// <param name="dataTable">The data table to return containing the data.</param> /// <param name="queryText">The query text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="getSchemaTable">Get the table schema from the database and then load the data. Used when /// returning data from the database for a particilar table.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>The sql command containing any return values.</returns> public DbCommand ExecuteQuery(ref DataTable dataTable, string queryText, CommandType commandType, string connectionString, bool getSchemaTable, params DbParameter[] values) { // Initial connection objects. DbCommand dbCommand = null; OracleClient.OracleConnection orlConnection = null; IDataReader dataReader = null; try { // Create a new connection. using (orlConnection = new OracleClient.OracleConnection(connectionString)) { // Open the connection. orlConnection.Open(); // Create the command and assign any parameters. dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer( ConnectionContext.ConnectionDataType.OracleDataType, queryText), orlConnection); dbCommand.CommandType = commandType; if (values != null) { foreach (OracleClient.OracleParameter sqlParameter in values) { dbCommand.Parameters.Add(sqlParameter); } } // Load the data into the table. using (dataReader = dbCommand.ExecuteReader()) { // Get the schema from the data because the // table has not predefined schema if (getSchemaTable) { // Load the table after the schema is // returned. dataTable = dataReader.GetSchemaTable(); dataTable = new DataTable(); System.Data.DataSet localDataSet = new System.Data.DataSet(); localDataSet.EnforceConstraints = false; localDataSet.Tables.Add(dataTable); dataTable.Load(dataReader); } else { // Load the data into a table schema. // Load the data into a table schema. System.Data.DataSet localDataSet = new System.Data.DataSet(); localDataSet.EnforceConstraints = false; localDataSet.Tables.Add(dataTable); dataTable.Load(dataReader); } dataReader.Close(); } // Close the database connection. orlConnection.Close(); } // Return the sql command, including // any parameters that have been // marked as output direction. return(dbCommand); } catch (Exception ex) { // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (dataReader != null) { dataReader.Close(); } if (orlConnection != null) { orlConnection.Close(); } } }
/// <summary> /// Executes the query. /// </summary> /// <typeparam name="TypeDataSet">The data type to examine.</typeparam> /// <param name="dataSet">The data set to return containing the data.</param> /// <param name="tables">The data tables names to return.</param> /// <param name="queryText">The query text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>The sql command containing any return values.</returns> public DbCommand ExecuteClientQuery <TypeDataSet>(ref TypeDataSet dataSet, string[] tables, string queryText, CommandType commandType, string connectionString, params DbParameter[] values) where TypeDataSet : System.Data.DataSet, new() { // Initial connection objects. OracleClient.OracleCommand sqlCommand = null; OracleClient.OracleConnection connection = null; IDataReader dataReader = null; try { // Create a new connection. using (connection = new OracleClient.OracleConnection(connectionString)) { // Open the connection. connection.Open(); // Create the command and assign any parameters. sqlCommand = new OracleClient.OracleCommand(queryText, connection); sqlCommand.CommandType = commandType; if (values != null) { foreach (OracleClient.OracleParameter sqlParameter in values) { sqlCommand.Parameters.Add(sqlParameter); } } // Load the data into the table. using (dataReader = sqlCommand.ExecuteReader()) { dataSet = new TypeDataSet(); dataSet.EnforceConstraints = false; dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables); dataReader.Close(); } // Close the database connection. connection.Close(); } // Return the sql command, including // any parameters that have been // marked as output direction. return(sqlCommand); } catch (Exception ex) { // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (dataReader != null) { dataReader.Close(); } if (connection != null) { connection.Close(); } } }
static void Main(string[] args) { DateTime d1 = DateTime.Now; int hora, minuto; List <Product> products = new List <Product>(); try { Console.WriteLine("Digite a hora: "); hora = int.Parse(Console.ReadLine()); Console.WriteLine("Digite os minutos: "); minuto = int.Parse(Console.ReadLine()); Console.WriteLine("Dê enter para iniciar o programa!"); Console.ReadLine(); do { IntPtr hWnd = GetConsoleWindow(); ShowWindow(hWnd, 0); do { d1 = DateTime.Now; } while (!(d1.Hour == hora && d1.Minute == minuto)); string data = "'" + d1.ToString("dd/MMM/yyyy", CultureInfo.InvariantCulture) + "'"; ShowWindow(hWnd, 1); OracleConnection conn = DBUtils.GetDBConnection(); OracleCommand cmd = new OracleCommand(); Console.WriteLine("Get Connection: " + conn); conn.Open(); Console.WriteLine("Conexão bem sucedida"); string consulta = "SELECT ESTOQUE.CODPROD ,ESTOQUE.QTESTGER ,VENDAS.GIRO ,ESTOQUE.QTESTGER - VENDAS.GIRO SALDO_FINAL FROM (SELECT CODPROD, QTESTGER " + "FROM PCEST " + "WHERE CODFILIAL = 1 AND CODPROD IN(SELECT A.CODPROD FROM PCMOV A, PCNFSAID B WHERE A.NUMTRANSVENDA = B.NUMTRANSVENDA " + "AND A.DTMOV = " + data + " " + "AND A.CODFILIAL = 50 " + "AND A.CODOPER = 'S' " + "AND B.DTCANCEL IS NULL)) ESTOQUE, " + "(SELECT A.CODPROD,SUM(A.QT) GIRO " + "FROM PCMOV A, PCNFSAID B " + "WHERE A.NUMTRANSVENDA = B.NUMTRANSVENDA " + "AND A.DTMOV = " + data + " " + "AND A.CODFILIAL = 50 " + "AND A.CODOPER = 'S' " + "AND B.DTCANCEL IS NULL " + "GROUP BY CODPROD) VENDAS " + "WHERE ESTOQUE.CODPROD = VENDAS.CODPROD " + "ORDER BY VENDAS.GIRO DESC "; using (var comm = new Oracle.DataAccess.Client.OracleCommand()) { comm.CommandText = consulta; comm.Connection = conn; using (var reader = comm.ExecuteReader()) { while (reader.Read()) { var codprodVar = reader["CODPROD"]; int codprod = (int)codprodVar; var qtestgerVar = reader["QTESTGER"]; decimal qtestger = (decimal)qtestgerVar; var giroVar = reader["GIRO"]; decimal giro = (decimal)giroVar; var saldo_finalVar = reader["SALDO_FINAL"]; decimal saldoFinal = (decimal)saldo_finalVar; products.Add(new Product(codprod, qtestger, giro, saldoFinal)); } } } string update = "UPDATE PCEST SET QTESTGER = QTESTGER - (SELECT SUM(A.QT) " + "FROM PCMOV A, PCNFSAID B" + " WHERE A.NUMTRANSVENDA = B.NUMTRANSVENDA " + "AND A.DTMOV = " + data + " AND A.CODFILIAL = 50 " + "AND A.CODOPER = 'S' AND B.DTCANCEL IS NULL " + "AND A.CODPROD = PCEST.CODPROD) " + "WHERE CODFILIAL = 1 AND CODPROD IN" + "(SELECT A.CODPROD FROM PCMOV A, PCNFSAID B WHERE A.NUMTRANSVENDA = B.NUMTRANSVENDA " + "AND A.DTMOV = " + data + " AND A.CODFILIAL = 50 AND A.CODOPER = 'S' AND B.DTCANCEL IS NULL)"; using (var comm = new Oracle.DataAccess.Client.OracleCommand()) { comm.Connection = conn; comm.CommandText = update; comm.ExecuteNonQuery(); } using (var comm = new Oracle.DataAccess.Client.OracleCommand()) { comm.CommandText = consulta; comm.Connection = conn; using (var reader = comm.ExecuteReader()) { int i = 0; while (reader.Read()) { var codprod = reader["CODPROD"]; var qtestger = reader["QTESTGER"]; decimal qtestgerdec = (decimal)qtestger; var giro = reader["GIRO"]; var saldo_final = reader["SALDO_FINAL"]; if (qtestgerdec != products[i].Qtestger) { using (StreamWriter sw = File.AppendText("entrada2.txt")) { sw.WriteLine(" --------------------------- Execução com erro: " + DateTime.Now + " -----------------------------"); sw.WriteLine("Erro no codigo: " + codprod); } foreach (Product prod in products) { using (var comm2 = new Oracle.DataAccess.Client.OracleCommand()) { comm2.Connection = conn; comm2.CommandText = "UPDATE PCEST SET QTESTGER = :Qtestger where codprod = :Codprod and codfilial = 1"; comm2.Parameters.Add("Qtestger", prod.Qtestger); comm2.Parameters.Add("Codprod", prod.Codprod); comm2.ExecuteNonQuery(); } } break; } using (StreamWriter sw = File.AppendText("entrada2.txt")) { sw.WriteLine(" --------------------------- Execução completa: " + DateTime.Now + " -----------------------------"); foreach (Product prod in products) { sw.WriteLine(prod); } } i++; } } } products.Clear(); do { d1 = DateTime.Now; } while (d1.Hour == hora && d1.Minute == minuto); } while (true); } catch (Exception ex) { Console.WriteLine("## ERROR: " + ex.Message); Console.Read(); return; } }