/** * this method gets a specific forum post and all comments * * */ internal ForumPost getForumPost(int postID) { List <Comment> comments = new List <Comment>(); ForumTopic topic = new ForumTopic(); String database = System.Configuration.ConfigurationManager.ConnectionStrings["programaholics_anonymous_databaseConnectionString"].ConnectionString; using (OleDbConnection sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + database)) { try { sqlConn.Open(); OleDbCommand query = sqlConn.CreateCommand(); String sql = "SELECT * FROM [forum_topic] WHERE [ID] = @ID"; query.CommandText = sql; query.Parameters.Add("ID", OleDbType.Integer).Value = postID; query.Prepare(); OleDbDataReader reader = query.ExecuteReader(); while (reader.Read()) { topic.title = reader["title"].ToString(); topic.ID = Convert.ToInt32(reader["ID"]); topic.createdBy = reader["createdBy"].ToString(); topic.createdOnDate = DateTime.Parse(reader["createdOnDate"].ToString()); } reader.Close(); sql = "SELECT * FROM [forum_comment] WHERE [forum_topic] = @ID ORDER BY [createdOnDate] ASC"; query.CommandText = sql; query.Parameters.Clear(); query.Parameters.Add("ID", OleDbType.Integer).Value = postID; query.Prepare(); reader = query.ExecuteReader(); while (reader.Read()) { int ID = Convert.ToInt32(reader["ID"]); String username = reader["username"].ToString(); String comment = reader["comment"].ToString(); int forum_topic = Convert.ToInt32(reader["forum_topic"]); DateTime createdOnDate = DateTime.Parse(reader["createdOnDate"].ToString()); Comment userComment = new Comment(ID, username, comment, forum_topic, createdOnDate); comments.Add(userComment); } } catch (OleDbException ex) { } finally { sqlConn.Close(); } ForumPost post = new ForumPost(topic, comments); return(post); } }
static void Main(string[] args) { MySqlConnection myConnection = new MySqlConnection("Database = vlad_m; Data Source = 192.168.27.79; User Id = vlad_m; charset=cp1251;default command timeout = 999; Password=vlad19957"); MySqlCommand myCommand = new MySqlCommand(); MySqlDataReader MyDataReader; OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE 5.0;Data Source=D:\\Alex\\RNCB\\exp_db_gis"); myCommand.Connection = myConnection; myConnection.Open(); conn.Open(); string s = DateTime.Now.ToString("yyyyMMdd" + "_9103006160.DBF"); OleDbCommand dbf = new OleDbCommand(string.Format("CREATE TABLE [" + s + "] (ELS VARCHAR(20),PAYERIDENT VARCHAR(11), FIO VARCHAR(50), LS VARCHAR(11), STREET VARCHAR(25), BUILDING VARCHAR(10), FLAT VARCHAR(10), SUM1 NUMERIC(10,2), SERVICECOD VARCHAR(2))"), conn); dbf.Prepare(); dbf.ExecuteNonQuery(); // myCommand.CommandText = string.Format("UPDATE rncb,id_ls set rncb.jky = id_ls.ls_jky where rncb.ls = id_ls.id"); //myCommand.Prepare();//подготавливает строку // myCommand.ExecuteNonQuery();//выполняет запрос myCommand.CommandText = string.Format("select " + "id_ls.ls_jky, rncb.PAYERIDENT, rncb.FIO, rncb.LS, rncb.STREET, rncb.BUILDING, rncb.FLAT, rncb.SUM1, rncb.SERVICECOD " + "from rncb " + "left OUTER JOIN id_ls on rncb.LS = id_ls.id " + "order by 4; "); myCommand.Prepare();//подготавливает строку MyDataReader = myCommand.ExecuteReader(); while (MyDataReader.Read()) { dbf.CommandText = string.Format("INSERT INTO [" + s + "] (ELS,PAYERIDENT,FIO,LS,STREET,BUILDING,FLAT,SUM1,SERVICECOD) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", MySqlHelper.EscapeString(MyDataReader.GetValue(0).ToString()), MySqlHelper.EscapeString(MyDataReader.GetValue(1).ToString()), MySqlHelper.EscapeString(MyDataReader.GetValue(2).ToString()), MySqlHelper.EscapeString(MyDataReader.GetValue(3).ToString()), MySqlHelper.EscapeString(MyDataReader.GetValue(4).ToString()), MySqlHelper.EscapeString(MyDataReader.GetValue(5).ToString()), MySqlHelper.EscapeString(MyDataReader.GetValue(6).ToString()), MySqlHelper.EscapeString(MyDataReader.GetValue(7).ToString()), MySqlHelper.EscapeString(MyDataReader.GetValue(8).ToString())); dbf.Prepare(); dbf.ExecuteNonQuery(); } MyDataReader.Close(); MyDataReader.Close(); conn.Close(); }
private static void AddNewCar(string carName, string carPrice) { if (connStr != null) { OleDbConnection con = new OleDbConnection(connStr); using (con) { con.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = con; string query = "INSERT INTO cars(name, price) VALUES(@name, @price)"; cmd.CommandText = query; cmd.Parameters.Add("@name", OleDbType.VarChar, 255).Value = carName; cmd.Parameters.Add("@price", OleDbType.Integer).Value = carPrice; cmd.Prepare(); cmd.ExecuteNonQuery(); Console.WriteLine("\n Car added!!"); System.Threading.Thread.Sleep(3000); } } }
public void actualizarRegistro(ObjConexion obj, Registro registro) { conexion(obj); string sql = Constantes.ACTUALIZAR_REGISTRO; // Set the Connection, CommandText and Parameters. cmd = new OleDbCommand(sql, con); cmd.Parameters.Add("idRegistro", OleDbType.Integer); cmd.Parameters.Add("idCliente", OleDbType.Integer); cmd.Parameters.Add("idMercaderia", OleDbType.Integer); cmd.Parameters.Add("idTipoRegistro", OleDbType.Integer); cmd.Parameters.Add("idEmpleado", OleDbType.Integer); cmd.Parameters.Add("fecha", OleDbType.VarChar, 10); cmd.Parameters.Add("cantidad", OleDbType.Integer); cmd.Parameters.Add("areaCubica", OleDbType.Double); cmd.Parameters.Add("estado", OleDbType.Integer); cmd.Parameters[0].Value = registro.IdRegistro; cmd.Parameters[1].Value = registro.IdCliente; cmd.Parameters[2].Value = registro.IdMercaderia; cmd.Parameters[3].Value = registro.IdTipoRegistro; cmd.Parameters[4].Value = registro.IdEmpleado; cmd.Parameters[5].Value = registro.Fecha; cmd.Parameters[6].Value = registro.Cantidad; cmd.Parameters[7].Value = registro.AreaCubica; cmd.Parameters[8].Value = registro.Estado; // Call Prepare and ExecuteNonQuery. cmd.Prepare(); cmd.ExecuteNonQuery(); closeConexion(); }
/// <summary> /// Crea la tabella "Report_Vendite". /// </summary> public void CreateTableReport() { if (connStr != null) { OleDbConnection con = new OleDbConnection(connStr); using (con) { con.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = con; try { cmd.CommandText = @"CREATE TABLE Report_Vendite(Targa VARCHAR(255) identity(1,1) NOT NULL PRIMARY KEY," + "Marca VARCHAR(255), Modello VARCHAR(255)," + "Colore VARCHAR(255), Cilindrata double," + "Potenza double, Immatricolazione VARCHAR(255)," + "Usato bit, Km0 bit," + "KmPercorsi double, MarcaSella VARCHAR(255), NumAirbag int, Prezzo double, Tipo VARCHAR(255));"; cmd.Prepare(); cmd.ExecuteNonQuery(); } catch (OleDbException exc) { Error(exc); return; } Console.WriteLine("\nTabella \"Report_Vendite\" creata correttamente."); System.Threading.Thread.Sleep(2000); } } }
private bool IsThisEventRelay(OleDbCommand cmd) { bool isRelay = false; string eventTypeSql = "SELECT kid FROM arr"; cmd.CommandText = eventTypeSql; cmd.Prepare(); IDataReader readeret = cmd.ExecuteReader(); while (readeret.Read()) { if (readeret[0] != null && readeret[0] != DBNull.Value) { int kid = Convert.ToInt16(readeret[0]); FireLogMsg("Event type is " + kid); if (kid == 3) { isRelay = true; } } } readeret.Close(); return(isRelay); }
private void allTodayLabel_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { try { stockGridView.Rows.Clear(); OleDbConnection conn = new OleDbConnection { ConnectionString = String.Format("Provider={0};Data Source={1}\\pawndata;Extended Properties=Paradox 4.x;", DataProvider, DataSource) }; conn.Open(); OleDbCommand getStock = new OleDbCommand("SELECT [Stock #], Description, Price, Cost, Added FROM STOCK WHERE [Added]=:value1", conn); getStock.Parameters.Add("value1", OleDbType.Date); getStock.Prepare(); getStock.Parameters[0].Value = DateTime.Today; int results = 0; using (OleDbDataReader myreader = getStock.ExecuteReader()) { while (myreader.Read()) { results++; stockGridView.Rows.Add(myreader[0], myreader[1], myreader[2], myreader[3], myreader[4]); } } noResultsLabel.Visible = results == 0; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public static string dropTable(string connectionStr) { if (connectionStr != null) { OleDbConnection connection = new OleDbConnection(connectionStr); using (connection) { try { connection.Open(); OleDbCommand command = new OleDbCommand(); command.Connection = connection; string SQLquery = $"DROP TABLE veicoli;"; command.CommandText = SQLquery; command.Prepare(); command.ExecuteNonQuery(); Console.ForegroundColor = ConsoleColor.Green; return("\nTABELLA ELIMINATA"); } catch (Exception ex) { Console.ForegroundColor = ConsoleColor.Red; return("\n!!ERROR!!" + ex.Message); } } } else { Console.ForegroundColor = ConsoleColor.Red; return("\n!!ERROR!!"); } }
internal InventoryItem getSingleItem(int productId) { using (OleDbConnection sqlConn = new OleDbConnection(database)) { InventoryItem item = null; try { sqlConn.Open(); String select = "SELECT * FROM [INVENTORY_ITEM] WHERE [inventory_id] = @productId"; OleDbCommand cmd = new OleDbCommand(select, sqlConn); cmd.Parameters.Add("productId", OleDbType.Integer).Value = productId; cmd.Prepare(); OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { item = getItemFromReader(reader); } return(item); } catch (OleDbException ex) { return(item); } finally { sqlConn.Close(); } } }
private long RunQueryWithExecutionTimeResult() { long returnValue = 0; System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); OleDbConnection conn = GetConnection(); try { using (OleDbCommand cmnd = new OleDbCommand(SummaryQuery, conn)) { cmnd.CommandType = UseSPForSummary ? CommandType.StoredProcedure : CommandType.Text; cmnd.CommandTimeout = cmndTimeOut; cmnd.Prepare(); sw.Start(); using (OleDbDataReader r = cmnd.ExecuteReader()) { while (r.Read()) { } } sw.Stop(); returnValue = sw.ElapsedMilliseconds; } CloseConnection(); } catch { CloseConnection(true); throw; } return returnValue; }
public static List <UserLink> getUserLinks(String username) { List <UserLink> links = new List <UserLink>(); //list to hold results of query String database = System.Configuration.ConfigurationManager.ConnectionStrings["programaholics_anonymous_databaseConnectionString"].ConnectionString; //connection string for db OleDbConnection sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + database); //db connection sqlConn.Open(); //connect to db //sql select string String select = "SELECT [links].path, [links].textValue FROM [links] INNER JOIN [users] ON [users].accessLevel = [links].accessLevel WHERE username = @username"; OleDbCommand cmd = new OleDbCommand(select, sqlConn); //add parameters to command cmd.Parameters.Add("userName", OleDbType.VarChar, 255).Value = username; cmd.Prepare(); //create data reader OleDbDataReader dr = cmd.ExecuteReader(); //add results of query to links list while (dr.Read()) { String path = dr["path"].ToString(); String textValue = dr["textValue"].ToString(); UserLink link = new UserLink(path, textValue); links.Add(link); } //close all resources and return list to calling method dr.Close(); sqlConn.Close(); return(links); }
public override CSUser find(CSUser user) { if (user == null) { throw new Exception("CSUser cannot be null !"); } try { OleDbCommand cmdFind = new OleDbCommand(); cmdFind.Connection = con; cmdFind.CommandText = "SELECT login, points, nbparties FROM [user] WHERE login = @login AND pass = @pass"; cmdFind.Parameters.Add("@login", OleDbType.VarChar, 25).Value = user.Login; cmdFind.Parameters.Add("@pass", OleDbType.VarChar, 25).Value = sha1Encrypt(user.Pass); cmdFind.Prepare(); OleDbDataReader reader = cmdFind.ExecuteReader(); CSUser logUser = null; if (reader.Read()) { logUser = new CSUser(); logUser.Login = reader.GetString(0); logUser.Points = reader.GetInt32(1); logUser.NbParties = reader.GetInt32(2); } reader.Close(); return(logUser); } catch (Exception e) { throw new Exception("Error when find user '" + user.Login + "' : " + e.Message); } }
public void eliminarCliente(ObjConexion obj, Cliente cliente) { conexion(obj); string sql = Constantes.ACTUALIZAR_CLIENTE; // Set the Connection, CommandText and Parameters. cmd = new OleDbCommand(sql, con); cmd.Parameters.Add("idEmpleado", OleDbType.Integer); cmd.Parameters.Add("nombreEmpresa", OleDbType.VarWChar, 20); cmd.Parameters.Add("dueñoEmpresa", OleDbType.VarWChar, 20); cmd.Parameters.Add("RUC", OleDbType.VarWChar, 13); cmd.Parameters.Add("estado", OleDbType.Integer); cmd.Parameters[0].Value = cliente.IdCliente; cmd.Parameters[1].Value = cliente.NombreEmpresa; cmd.Parameters[2].Value = cliente.DueñoEmpresa; cmd.Parameters[3].Value = cliente.RUC; cmd.Parameters[4].Value = 0; // estado 0 --> Eliminado // Call Prepare and ExecuteNonQuery. cmd.Prepare(); cmd.ExecuteNonQuery(); closeConexion(); }
public static string deleteVehicle(string connectionStr, int id) { if (connectionStr != null) { OleDbConnection connection = new OleDbConnection(connectionStr); using (connection) { try { connection.Open(); OleDbCommand command = new OleDbCommand(); command.Connection = connection; string SQLquery = $"DELETE FROM veicoli WHERE id={id};"; command.CommandText = SQLquery; command.Prepare(); command.ExecuteNonQuery(); Console.ForegroundColor = ConsoleColor.Green; return("Done"); } catch (Exception ex) { Console.ForegroundColor = ConsoleColor.Red; return("\n" + ex.Message); } } } else { Console.ForegroundColor = ConsoleColor.Red; return("\n!!ERROR!!"); } }
public void registrarEmpleado(ObjConexion obj, Empleado empleado) { conexion(obj); string sql = Constantes.REGISTRAR_EMPLEADO; // Set the Connection, CommandText and Parameters. cmd = new OleDbCommand(sql, con); cmd.Parameters.Add("idCargo", OleDbType.Integer); cmd.Parameters.Add("DNI", OleDbType.VarWChar, 8); cmd.Parameters.Add("nombreEmpleado", OleDbType.VarWChar, 20); cmd.Parameters.Add("usuario", OleDbType.VarWChar, 20); cmd.Parameters.Add("contraseña", OleDbType.VarWChar, 20); cmd.Parameters.Add("estado", OleDbType.VarWChar, 20); cmd.Parameters[0].Value = empleado.IdCargo; cmd.Parameters[1].Value = empleado.DNI; cmd.Parameters[2].Value = empleado.Nombre; cmd.Parameters[3].Value = empleado.Usuario; cmd.Parameters[4].Value = empleado.Contraseña; cmd.Parameters[5].Value = empleado.Estado; // Call Prepare and ExecuteNonQuery. cmd.Prepare(); cmd.ExecuteNonQuery(); closeConexion(); }
/// <summary> /// Crea la tabella "Moto". /// </summary> public void CreateTableMoto() { if (connStr != null) { OleDbConnection con = new OleDbConnection(connStr); using (con) { con.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = con; try { cmd.CommandText = @"CREATE TABLE Moto(Targa VARCHAR(255) identity(1,1) NOT NULL PRIMARY KEY," + "Marca VARCHAR(255), Modello VARCHAR(255)," + "Colore VARCHAR(255), Cilindrata double," + "Potenza double, Immatricolazione date," + "Usato bit, Km0 bit," + "KmPercorsi double, MarcaSella VARCHAR(255), Prezzo double, ImgPath varchar(255));"; cmd.Prepare(); cmd.ExecuteNonQuery(); } catch (OleDbException exc) { Error(exc); return; } Console.WriteLine("\nTabella \"Moto\" creata correttamente."); } } }
void Generate(OleDbConnection connection, DateTime startDate, int daysCount) { using (OleDbCommand command = new OleDbCommand()) { command.Connection = connection; command.CommandText = "insert into sales (id, units, cost_per_unit, discount, total_cost, sale_date, productId, RegionId, ChannelId, SectorId) values (@id, @units, @cost_per_unit, @discount, @total_cost, @sale_date, @productId, @RegionId, @ChannelId, @SectorId)"; command.Parameters.AddRange( new OleDbParameter[] { new OleDbParameter("@id", OleDbType.Integer), new OleDbParameter("@units", OleDbType.Integer), new OleDbParameter("@cost_per_unit", OleDbType.Integer), new OleDbParameter("@discount", OleDbType.Integer), new OleDbParameter("@total_cost", OleDbType.Integer), new OleDbParameter("@sale_date", OleDbType.Date), new OleDbParameter("@productId", OleDbType.Integer), new OleDbParameter("@RegionId", OleDbType.Integer), new OleDbParameter("@ChannelId", OleDbType.Integer), new OleDbParameter("@SectorId", OleDbType.Integer) } ); command.Prepare(); for (int n = 0; n < daysCount; n++) { Console.Write("{0} of {1}\r", n + 1, daysCount); GenerateDay(command, startDate.AddDays(n)); } } }
public void actualizarMercaderia(ObjConexion obj, Mercaderia mercaderia) { conexion(obj); string sql = Constantes.ACTUALIZAR_MERCADERIA; // Set the Connection, CommandText and Parameters. cmd = new OleDbCommand(sql, con); cmd.Parameters.Add("idMercaderia", OleDbType.Integer); cmd.Parameters.Add("idTipoMerca", OleDbType.Integer); cmd.Parameters.Add("idCliente", OleDbType.Integer); cmd.Parameters.Add("descripcionMercaderia", OleDbType.VarWChar, 100); cmd.Parameters.Add("areaCubica", OleDbType.Double); cmd.Parameters.Add("categoria", OleDbType.VarWChar, 100); cmd.Parameters.Add("unidad", OleDbType.VarWChar, 100); cmd.Parameters.Add("cantidad", OleDbType.Integer); cmd.Parameters[0].Value = mercaderia.IdMercaderia; cmd.Parameters[1].Value = mercaderia.IdTipoMercaderia; cmd.Parameters[2].Value = mercaderia.IdCliente; cmd.Parameters[3].Value = mercaderia.Descripcion; cmd.Parameters[4].Value = mercaderia.AreaCubica; cmd.Parameters[5].Value = mercaderia.Categoria; cmd.Parameters[6].Value = mercaderia.Unidad; cmd.Parameters[7].Value = mercaderia.Cantidad; // Call Prepare and ExecuteNonQuery. cmd.Prepare(); cmd.ExecuteNonQuery(); closeConexion(); }
private async Task <object> ExecuteNonQuery(string connectionString, List_val prep) { try { if (prep.Values != null) { prep = Create_non_parametr(prep); } using (OleDbConnection connection = new OleDbConnection(connectionString)) { await connection.OpenAsync(); using (OleDbCommand command = new OleDbCommand(prep.Query, connection)) { if (prep.Values != null) { if (prep.Values.Count > 0) { foreach (Prep_val itm in prep.Values) { command.Parameters.Add(new OleDbParameter(itm.Val_name, GetOleDbType(itm.Type), itm.Len)).Value = Convert.ChangeType(itm.Value, itm.Value.GetType()); } } command.Prepare(); } return(await command.ExecuteNonQueryAsync()); } } } catch (Exception e) { throw new Exception("ExecuteNonQuery Error ", e); } }
// <Snippet1> private static void OleDbCommandPrepare(string connectionString) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); // Create the Command. OleDbCommand command = new OleDbCommand(); // Set the Connection, CommandText and Parameters. command.Connection = connection; command.CommandText = "INSERT INTO dbo.Region (RegionID, RegionDescription) VALUES (?, ?)"; command.Parameters.Add("RegionID", OleDbType.Integer, 4); command.Parameters.Add("RegionDescription", OleDbType.VarWChar, 50); command.Parameters[0].Value = 20; command.Parameters[1].Value = "First Region"; // Call Prepare and ExecuteNonQuery. command.Prepare(); command.ExecuteNonQuery(); // Change parameter values and call ExecuteNonQuery. command.Parameters[0].Value = 21; command.Parameters[1].Value = "SecondRegion"; command.ExecuteNonQuery(); } }
public static void AggiungiVeicolo(Veicolo v) { try { OleDbConnection connection = new OleDbConnection(connstr); using (connection) { connection.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = connection; ///Comandi di eseguzione SQL ///Inserisco tutti i dettagli dell'auto //////inserisco tutti i dettagli del moto cmd.CommandText = @"INSERT INTO Veicoli(TIPO,MARCA,MODELLO,COLORE,CILINDRATA,POTENZAKW,IMMATRICOLAZIONE,USATO,KMZERO,KM_PERCORSI,NUMAIRBAG,MARCASELLA,PREZZO) VALUES(@TIPO,@MARCA,@MODELLO,@COLORE,@CILINDRATA,@POTENZAKW,@IMMATRICOLAZIONE,@ISUSATO,@ISKMZERO,@KM_PERCORSI,@NUMAIRBAG,@MARCASELLA,@PREZZO)"; AddParametres(v, cmd); cmd.Prepare(); cmd.ExecuteNonQuery(); } } catch (OleDbException exc) { throw new Exception("Errore --->" + exc.Message); } }
public static void AggiornaDataBase(string tablename, string id, string option, string data) { try { OleDbConnection connection = new OleDbConnection(connstr); using (connection) { OleDbCommand cmd = new OleDbCommand(); cmd.Connection = connection; cmd.CommandText = $@"UPDATE {tablename} SET MARCA = @MARCA, MODELLO = @MODELLO, COLORE = @COLORE, CILINDRATA = @CILINDRATA, POTENZAKW = @POTENZAKW, IMMATRICOLAZIONE = @IMMATRICOLAZIONE, USATO= @USATO, KMZERO=@KMZERO, KM_PERCORSI=@KM_PERCORSI ,NUMAIRBAG = @NUMAIRBAG, MARCASELLA = @MARCASELLA, PREZZO = @PREZZO WHERE ID={id}"; if ((option == "MARCA") || (option == "MODELLO") || (option == "COLORE") || (option == "MARCASELLA")) { cmd.Parameters.Add($"{option}", OleDbType.VarChar, 255).Value = data; } else if ((option == "CILINDRATA") || (option == "KM_PERCORSI") || (option == "NUMAIRBAG") || (option == "PREZZO")) { cmd.Parameters.Add($"{option}", OleDbType.Integer).Value = data; } else if (option == "POTENZAKW") { cmd.Parameters.Add($"{option}", OleDbType.Double).Value = data; } else if ((option == "USATO") || (option == "KMZERO")) { cmd.Parameters.Add($"{option}", OleDbType.Boolean).Value = data; } cmd.Prepare(); cmd.ExecuteNonQuery(); } } catch (OleDbException exc) { }; }
private static void AddNewVehicle(veicolo v) { if (connStr != null) { OleDbConnection con = new OleDbConnection(connStr); using (con) { con.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = con; try { v.Marca = SetFields("\nInserisci marca: "); v.Modello = SetFields("Inserisci modello: "); v.Cilindrata = Convert.ToInt32(SetFields("Inserisci cilindrata: ")); v.PotenzaKw = Convert.ToDouble(SetFields("Inserisci potenza [KW]: ")); v.Immatricolazione = Convert.ToDateTime(SetFields("Inserisci data di immatricolazione [gg/mm/aaaa]: ")); v.IsUsato = Convert.ToBoolean(SetFields("Il veicolo è usato? [Si/No] ")); v.IsKmZero = Convert.ToBoolean(SetFields("Il veicolo è Km zero? [Si/No] ")); v.KmPercorsi = v.IsUsato ? Convert.ToInt32(SetFields("Inserisci km percorsi: ")) : 0; v.Colore = SetFields("Inserisci colore: "); v.Prezzo = Convert.ToInt32(SetFields("Inserisci prezzo: ")); string query; if (v is auto) { (v as auto).NumairBag = Convert.ToInt32(SetFields("Inserisci numero di airbag: ")); query = "INSERT INTO auto(marca, modello, cilindrata, potenzaKw," + "immatr, kmPercorsi, colore, isUsato, isKmZero, prezzo, nAirBag) VALUES(@marca, @modello," + "@cilindrata, @potenzaKw, @immatr, @kmPercorsi, @colore, @isUsato, @isKmZero, @prezzo, @nAirBag)"; } else { (v as moto).MarcaSella = SetFields("Inserisci la marca della sella: "); query = "INSERT INTO moto(marca, modello, cilindrata, potenzaKw," + "immatr, kmPercorsi, colore, isUsato, isKmZero, prezzo, mSella) VALUES(@marca, @modello," + "@cilindrata, @potenzaKw, @immatr, @kmPercorsi, @colore, @isUsato, @isKmZero, @prezzo, @mSella)"; } cmd.CommandText = query; addParameters(cmd, v); cmd.Prepare(); cmd.ExecuteNonQuery(); System.Threading.Thread.Sleep(3000); Console.WriteLine("\nVeicolo aggiunto correttamente!"); Console.ReadKey(); } catch (Exception ex) { Console.WriteLine(ex.Message + " -- Errore durante il caricamento del veicolo!"); } } } }
public OleDbDataReader ExecDataReader(String query, OleDbParameter[] filterParameters) { using (OleDbCommand cmd = CreateCommand(query, CommandType.Text, filterParameters)) { cmd.Prepare(); return(cmd.ExecuteReader()); } }
internal PaymentInformation getUserCreditCard(int paymentInfoId) { PaymentInformation userCreditCard = new PaymentInformation(); User user = new User(); using (OleDbConnection sqlconn = new OleDbConnection(database)) { try { sqlconn.Open(); OleDbCommand cmd = sqlconn.CreateCommand(); String select = "SELECT [PAYMENT_INFORMATION].payment_information_id, [PAYMENT_INFORMATION].credit_card_type, [PAYMENT_INFORMATION].credit_card_number, [PAYMENT_INFORMATION].card_city, [PAYMENT_INFORMATION].card_state, " + "[PAYMENT_INFORMATION].card_exp_date, [PAYMENT_INFORMATION].security_code, [USERS].user_id, [USERS].username, [USERS].first_name, [USERS].last_name, [USERS].address, " + "[USERS].city, [USERS].state, [USERS].zip_code, [USERS].account_creation_date, [USERS].access_level, [USERS].email FROM [PAYMENT_INFORMATION] " + "INNER JOIN [USERS] ON [USERS].user_id = [PAYMENT_INFORMATION].user_id " + "WHERE [PAYMENT_INFORMATION].payment_information_id = @paymentInfoId"; cmd.CommandText = select; cmd.Parameters.Add("paymentInfoId", OleDbType.Integer).Value = paymentInfoId; cmd.Prepare(); OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { user.setId((int)reader["user_id"]); user.setUsername(reader["username"].ToString()); user.setFirstName(reader["first_name"].ToString()); user.setLastName(reader["last_name"].ToString()); user.setAddress(reader["address"].ToString()); user.setCity(reader["city"].ToString()); user.setState(reader["state"].ToString()); user.setZipCode(reader["zip_code"].ToString()); user.setAccountCreationDate(DateTime.Parse(reader["account_creation_date"].ToString())); user.setEmail(reader["email"].ToString()); userCreditCard.setUser(user); userCreditCard.setPaymentInformationId((int)reader["payment_information_id"]); userCreditCard.setCreditCardType(reader["credit_card_type"].ToString()); userCreditCard.setCreditCardNumber(reader["credit_card_number"].ToString()); userCreditCard.setCity(reader["card_city"].ToString()); userCreditCard.setState(reader["card_state"].ToString()); userCreditCard.setCardExpDated(DateTime.Parse(reader["card_exp_date"].ToString())); userCreditCard.setSecurityCode(reader["security_code"].ToString()); } return(userCreditCard); } catch (OleDbException ex) { userCreditCard = null; return(userCreditCard); } finally { sqlconn.Close(); } } }
public void AggiungiVendita(Veicolo v) { if (connStr != null) { OleDbConnection con = new OleDbConnection(connStr); using (con) { con.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = con; cmd.CommandText = "INSERT INTO Report_Vendite(Targa, Marca, Modello, Colore, Cilindrata, Potenza, Immatricolazione, Usato, Km0, KmPercorsi, MarcaSella, NumAirbag, Prezzo, Tipo)" + " VALUES(@Targa, @Marca, @Modello, @Colore, @Cilindrata, @Potenza, @Immatricolazione, @Usato, @Km0, @KmPercorsi, @MarcaSella, @NumAirbag, @Prezzo, @Tipo);"; //Sostituzione parametri cmd.Parameters.Add(new OleDbParameter("@Targa", OleDbType.VarChar, 255)).Value = v.Targa; cmd.Parameters.Add(new OleDbParameter("@Marca", OleDbType.VarChar, 255)).Value = v.Marca; cmd.Parameters.Add(new OleDbParameter("@Modello", OleDbType.VarChar, 255)).Value = v.Modello; cmd.Parameters.Add(new OleDbParameter("@Colore", OleDbType.VarChar, 255)).Value = v.Colore; cmd.Parameters.Add("@Cilindrata", OleDbType.Double).Value = v.Cilindrata; cmd.Parameters.Add("@Potenza", OleDbType.Double).Value = v.PotenzaKw; cmd.Parameters.Add("@Immatricolazione", OleDbType.Date).Value = v.Immatricolazione; cmd.Parameters.Add("@Usato", OleDbType.Boolean).Value = v.IsUsato; cmd.Parameters.Add("@Km0", OleDbType.Boolean).Value = v.IsKmZero; cmd.Parameters.Add("@KmPercorsi", OleDbType.Double).Value = v.KmPercorsi; if (v is Moto) { cmd.Parameters.Add(new OleDbParameter("@MarcaSella", OleDbType.VarChar, 255)).Value = (v as Moto).MarcaSella; cmd.Parameters.Add("@NumAirbag", OleDbType.Integer).Value = 0; } else { cmd.Parameters.Add(new OleDbParameter("@MarcaSella", OleDbType.VarChar, 255)).Value = "-"; cmd.Parameters.Add("@NumAirbag", OleDbType.Integer).Value = (v as Automobili).NumAirbag; } cmd.Parameters.Add("@Prezzo", OleDbType.Double).Value = v.Prezzo; if (v is Moto) { cmd.Parameters.Add(new OleDbParameter("@Tipo", OleDbType.VarChar, 255)).Value = "Moto"; } else { cmd.Parameters.Add(new OleDbParameter("@Tipo", OleDbType.VarChar, 255)).Value = "Automobili"; } cmd.Prepare(); try { cmd.ExecuteNonQuery(); } catch (OleDbException exc) { Error(exc); return; } } } }
public override void Prepare() { try { OleDbCommand.Prepare(); } catch (OleDbException e) { throw new VfpException(e.Message, e); } }
public OleDbDataReader busca() { OleDbDataReader busca_int; comando.Prepare(); busca_int = comando.ExecuteReader(); comando.CommandTimeout = 0; return(busca_int); }
public void ClearAll(string dbpath) { _connection.Open(); OleDbCommand oCmd = _connection.CreateCommand(); oCmd.CommandText = "delete from " + dbpath; oCmd.Prepare(); oCmd.ExecuteNonQuery(); _connection.Close(); }
public void aggiungiItem(string tabName, string marca, string modello, string colore, int cilindrata, double potenzaKw, DateTime immatricolazione, string usata, string isKm0, int kmPercorsi, double prezzo, int numAirbag, string marcaSella) { if (conStr != null) { OleDbConnection con = new OleDbConnection(conStr); using (con) { con.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = con; string command = string.Empty; if (tabName == "AUTO") { command = $"INSERT INTO {tabName}" + $"(marca, modello, colore, cilindrata, potenzaKw, immatricolazione, usato, kmZero," + $" kmPercorsi, prezzo, numAirbag) VALUES(@marca, @modello, @colore, @cilindrata, @potenzaKw, " + $"@immatricolazione, @usata, @isKm0, @kmPercorsi, @prezzo, @numAirbag)"; } else { command = $"INSERT INTO {tabName}" + $"(marca, modello, colore, cilindrata, potenzaKw, immatricolazione, usato, kmZero, " + $"kmPercorsi, prezzo, marcaSella) VALUES(@marca, @modello, @colore, @cilindrata, @potenzaKw," + $" @immatricolazione, @usata, @isKm0, @kmPercorsi, @prezzo, @marcaSella)"; } cmd.CommandText = command; cmd.Parameters.Add(new OleDbParameter("@marca", OleDbType.VarChar, 255)).Value = marca; cmd.Parameters.Add(new OleDbParameter("@modello", OleDbType.VarChar, 255)).Value = modello; cmd.Parameters.Add(new OleDbParameter("@colore", OleDbType.VarChar, 255)).Value = colore; cmd.Parameters.Add("@cilindrata", OleDbType.Integer).Value = cilindrata; cmd.Parameters.Add("@potenzaKw", OleDbType.Double).Value = potenzaKw; cmd.Parameters.Add(new OleDbParameter("@immatricolazione", OleDbType.Date)).Value = immatricolazione.ToShortDateString(); cmd.Parameters.Add(new OleDbParameter("@usato", OleDbType.VarChar, 255)).Value = usata; cmd.Parameters.Add(new OleDbParameter("@isKm0", OleDbType.VarChar, 255)).Value = isKm0; cmd.Parameters.Add("@kmPercorsi", OleDbType.Integer).Value = kmPercorsi; cmd.Parameters.Add("@prezzo", OleDbType.Double).Value = prezzo; if (tabName == "AUTO") { cmd.Parameters.Add("@numAirbag", OleDbType.Integer).Value = numAirbag; } else { cmd.Parameters.Add(new OleDbParameter("@marcaSella", OleDbType.VarChar, 255)).Value = marcaSella; } cmd.Prepare(); cmd.ExecuteNonQuery(); } } }