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