public List <AddressModel> GetAllAddresses()
{
//Store raw query results to data table
DataTable dt = new DataTable();
//SQLite stuff
SQLiteCommand comm = null;
SQLiteConnection connection = null;
string query = "SELECT * FROM ADDRESS;";
//useable Data after conversion to be returned
List <AddressModel> addressList = new List <AddressModel>();
try
{
connection = SQLiteHelper.OpenConn();
comm = new SQLiteCommand(query, connection);
//Execute the command and load data to table
SQLiteDataReader reader = comm.ExecuteReader();
dt.Load(reader);
//Closes reader stream then connection
reader.Close();
SQLiteHelper.CloseConn();
//Use Datamapper to map selected results to objects
DataNamesMapper <AddressModel> mapper = new DataNamesMapper <AddressModel>();
addressList = mapper.Map(dt).ToList();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
SQLiteHelper.CloseConn();
MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(addressList);
}
//Get Assembly by Data
public List <AssemblyGroupModel> GetAssemblyByData(string searchFor, AssemblySearch columnName)
{
//Store raw query results to data table
DataTable dt = new DataTable();
//SQLite stuff
SQLiteCommand comm = null;
SQLiteConnection connection = null;
string query = String.Format("SELECT * FROM {0} WHERE {0}.@C LIKE '%@param%';", TableName);
//useable Data after conversion to be returned
List <AssemblyGroupModel> assemblyList = new List <AssemblyGroupModel>();
try
{
connection = SQLiteHelper.OpenConn();
//comm = new SQLiteCommand(query, connection);
switch (columnName)
{
case AssemblySearch.ID:
query = query.Replace("@C", "AssemblyId");
comm = new SQLiteCommand(query, connection);
int idRes;
if (!int.TryParse(searchFor, out idRes))
{
throw new Exception("Assembly ID search is restricted to numbers");
}
comm.Parameters.Add("@param", DbType.Int32).Value = idRes;
break;
case AssemblySearch.PARTSID:
query = query.Replace("@C", "PartsId");
comm = new SQLiteCommand(query, connection);
int partIdRes;
if (!int.TryParse(searchFor, out partIdRes))
{
throw new Exception("Parts ID search is restricted to numbers");
}
comm.Parameters.Add("@param", DbType.Int32).Value = partIdRes;
break;
case AssemblySearch.QUANTITY:
query = query.Replace("@C", "PartsQuantity");
comm = new SQLiteCommand(query, connection);
int quantity;
if (!int.TryParse(searchFor, out quantity))
{
throw new Exception("Parts ID search is restricted to numbers");
}
comm.Parameters.Add("@param", DbType.Int32).Value = quantity;
break;
}
//Execute the command and load data to table
SQLiteDataReader reader = comm.ExecuteReader();
dt.Load(reader);
//Closes reader stream then connection
reader.Close();
SQLiteHelper.CloseConn();
//Use Datamapper to map selected results to objects
DataNamesMapper <AssemblyGroupModel> mapper = new DataNamesMapper <AssemblyGroupModel>();
assemblyList = mapper.Map(dt).ToList();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
SQLiteHelper.CloseConn();
MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(assemblyList);
}
//Add new Assembly Group
public int CreateNewAssemblyGroup(List <AssemblyGroupModel> assembly)
{
int affected = 0;
string commandString = "INSERT INTO `ASSEMBLY_GROUP`(`AssemblyId`,`PartsId`,`PartsQuantity`) " +
"VALUES (@p1,@p2,@p3);";
SQLiteConnection connection = null;
SQLiteTransaction transaction = null;
SQLiteCommand comm = null;
try
{
//Open a new connection
connection = SQLiteHelper.OpenConn();
//Start transaction (ATOMICITY)
transaction = connection.BeginTransaction();
//Start a new query and assign the transaction
comm = connection.CreateCommand();
comm.Transaction = transaction;
//Start inserting items
foreach (AssemblyGroupModel item in assembly)
{
comm.CommandText = commandString;
comm.Parameters.Add("@p1", DbType.Int32).Value = item.AssemblyID;
comm.Parameters.Add("@p2", DbType.Int32).Value = item.PartsID;
comm.Parameters.Add("@p3", DbType.Int32).Value = item.PartsQuantity;
affected += comm.ExecuteNonQuery();
}
//Commit transaction
transaction.Commit();
//Close connection
SQLiteHelper.CloseConn();
}
catch (Exception e)
{
//Transaction Rollback Failure
if (transaction != null)
{
try
{
transaction.Rollback();
affected = 0;
}
catch (SQLiteException sqlEx)
{
MessageBox.Show("Transaction rollback Failure: " + sqlEx.Message);
}
finally
{
transaction.Dispose();
}
}
Console.WriteLine(e.Message);
SQLiteHelper.CloseConn();
MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
affected = -1;
}
return(affected);
}
//New Address(es)
//INSERT INTO `ADDRESS`(`AddressID`,`Street`,`City`,`Zip`,`State`) VALUES (1001,'','',0,'');
public int CreateNewAddress(List <AddressModel> address)
{
int affected = 0;
string commandString = "INSERT INTO `ADDRESS`(`Street`,`City`,`Zip`,`State`) " +
"VALUES (@p1,@p2,@p3,@p4);";
SQLiteConnection connection = null;
SQLiteTransaction transaction = null;
SQLiteCommand comm = null;
try
{
//Open a new connection
connection = SQLiteHelper.OpenConn();
//Start transaction (ATOMICITY)
transaction = connection.BeginTransaction();
//Start a new query and assign the transaction
comm = connection.CreateCommand();
comm.Transaction = transaction;
//Start inserting items
foreach (AddressModel item in address)
{
//String format allows for SQL injection attacks
//comm.CommandText = String.Format(commandString, item.Street, item.City, item.Zip, item.State);
comm.CommandText = commandString;
comm.Parameters.Add("@p1", DbType.String).Value = item.Street;
comm.Parameters.Add("@p2", DbType.String).Value = item.City;
comm.Parameters.Add("@p3", DbType.Int32).Value = item.Zip;
comm.Parameters.Add("@p4", DbType.String).Value = item.State;
affected += comm.ExecuteNonQuery();
}
//Commit transaction
transaction.Commit();
//Close connection
SQLiteHelper.CloseConn();
}
catch (Exception e)
{
//Transaction Rollback Failure
if (transaction != null)
{
try
{
transaction.Rollback();
affected = 0;
}
catch (SQLiteException sqlEx)
{
MessageBox.Show("Transaction rollback Failure: " + sqlEx.Message);
}
finally
{
transaction.Dispose();
}
}
Console.WriteLine(e.Message);
SQLiteHelper.CloseConn();
MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
affected = -1;
}
return(affected);
}
//Delete
public int DeleteAddress(List <AddressModel> addressList)
{
int affected = 0;
string commandString = "DELETE FROM `ADDRESS` WHERE ADDRESS.AddressId = @p1;";
SQLiteConnection connection = null;
SQLiteTransaction transaction = null;
SQLiteCommand comm = null;
try
{
//Open a new connection
connection = SQLiteHelper.OpenConn();
//Start transaction (ATOMICITY)
transaction = connection.BeginTransaction();
//Start a new query and assign the transaction
comm = connection.CreateCommand();
comm.Transaction = transaction;
foreach (AddressModel address in addressList)
{
comm.CommandText = commandString;
comm.Parameters.Add("@p1", DbType.Int32).Value = address.AddressID;
affected += comm.ExecuteNonQuery();
}
//Commit transaction
transaction.Commit();
//Close connection
SQLiteHelper.CloseConn();
}
catch (Exception e)
{
//Transaction Rollback Failure
if (transaction != null)
{
try
{
transaction.Rollback();
affected = 0;
}
catch (SQLiteException sqlEx)
{
MessageBox.Show("Transaction rollback Failure: " + sqlEx.Message);
}
finally
{
transaction.Dispose();
}
}
Console.WriteLine(e.Message);
SQLiteHelper.CloseConn();
MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
affected = -1;
}
return(affected);
}
//Get Address by column
public List <AddressModel> GetAddressByData(string searchFor, AddressSearch columnName)
{
//Store raw query results to data table
DataTable dt = new DataTable();
//SQLite stuff
SQLiteCommand comm = null;
SQLiteConnection connection = null;
string query = "SELECT * FROM ADDRESS WHERE ADDRESS.@C LIKE '%@param%';";
//useable Data after conversion to be returned
List <AddressModel> addressList = new List <AddressModel>();
try
{
connection = SQLiteHelper.OpenConn();
//comm = new SQLiteCommand(query, connection);
switch (columnName)
{
case AddressSearch.ID:
query = query.Replace("@C", "AddressId");
comm = new SQLiteCommand(query, connection);
int idRes;
if (!int.TryParse(searchFor, out idRes))
{
throw new Exception("Address ID search is restricted to numbers");
}
comm.Parameters.Add("@param", DbType.Int32).Value = idRes;
break;
case AddressSearch.STREET:
query = query.Replace("@C", "Street");
comm = new SQLiteCommand(query, connection);
comm.Parameters.Add("@param", DbType.String).Value = searchFor;
break;
case AddressSearch.CITY:
query = query.Replace("@C", "City");
comm = new SQLiteCommand(query, connection);
comm.Parameters.Add("@param", DbType.String).Value = searchFor;
break;
case AddressSearch.ZIP:
query = query.Replace("@C", "Zip");
comm = new SQLiteCommand(query, connection);
int results;
if (!int.TryParse(searchFor, out results))
{
throw new Exception("ZIP code is restricted to numbers");
}
comm.Parameters.Add("@param", DbType.Int32).Value = results;
break;
case AddressSearch.STATE:
query = query.Replace("@C", "State");
comm = new SQLiteCommand(query, connection);
comm.Parameters.Add("@param", DbType.String).Value = searchFor;
break;
}
//Execute the command and load data to table
SQLiteDataReader reader = comm.ExecuteReader();
dt.Load(reader);
//Closes reader stream then connection
reader.Close();
SQLiteHelper.CloseConn();
//Use Datamapper to map selected results to objects
DataNamesMapper <AddressModel> mapper = new DataNamesMapper <AddressModel>();
addressList = mapper.Map(dt).ToList();
//foreach (DataRow item in dt.Rows)
//{
// Console.WriteLine(item["Street"].ToString()); //BREAKTHRUUUUUUUUU
//}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
SQLiteHelper.CloseConn();
MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(addressList);
}
//Get customer by Data
public List <CustomerModel> GetCustomerByData(string searchFor, CustomerSearch columnName)
{
//Store raw query results to data table
DataTable dt = new DataTable();
//SQLite stuff
SQLiteCommand comm = null;
SQLiteConnection connection = null;
string query = String.Format("SELECT * FROM {0} WHERE {0}.@C LIKE '%'||@param||'%';", TableName); //NEW FIX USE THIS CONCAT
//useable Data after conversion to be returned
List <CustomerModel> customerList = new List <CustomerModel>();
try
{
connection = SQLiteHelper.OpenConn();
//comm = new SQLiteCommand(query, connection);
switch (columnName)
{
case CustomerSearch.ID:
query = query.Replace("@C", "CustomerId");
comm = new SQLiteCommand(query, connection);
int idRes;
if (!int.TryParse(searchFor, out idRes))
{
throw new Exception("Customer ID search is restricted to numbers");
}
SQLiteParameter param = new SQLiteParameter("@param", DbType.Int32); //NEW FIX
param.Value = idRes;
comm.Parameters.Add(param);
break;
case CustomerSearch.FIRSTNAME:
query = query.Replace("@C", "CustFName");
comm = new SQLiteCommand(query, connection);
SQLiteParameter paramFName = new SQLiteParameter("@param", DbType.String);
paramFName.Value = searchFor;
comm.Parameters.Add(paramFName);
break;
case CustomerSearch.LASTNAME:
query = query.Replace("@C", "CustLName");
comm = new SQLiteCommand(query, connection);
SQLiteParameter paramLName = new SQLiteParameter("@param", DbType.String);
paramLName.Value = searchFor;
comm.Parameters.Add(paramLName);
break;
case CustomerSearch.EMAIL:
query = query.Replace("@C", "CustEmail");
comm = new SQLiteCommand(query, connection);
SQLiteParameter paramEmail = new SQLiteParameter("@param", DbType.String);
paramEmail.Value = searchFor;
comm.Parameters.Add(paramEmail);
break;
}
//Execute the command and load data to table
SQLiteDataReader reader = comm.ExecuteReader();
dt.Load(reader);
//Closes reader stream then connection
reader.Close();
SQLiteHelper.CloseConn();
//Use Datamapper to map selected results to objects
DataNamesMapper <CustomerModel> mapper = new DataNamesMapper <CustomerModel>();
customerList = mapper.Map(dt).ToList();
//foreach (DataRow item in dt.Rows)
//{
// Console.WriteLine(item["Street"].ToString()); //BREAKTHRUUUUUUUUU
//}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
SQLiteHelper.CloseConn();
MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(customerList);
}
//New Customer
public int CreateNewCustomer(List <CustomerModel> customer)
{
int affected = 0;
string commandString = "INSERT INTO `CUSTOMER`(`CustFName`,`CustLName`,`CustEmail`) " +
"VALUES (@p1,@p2,@p3);";
SQLiteConnection connection = null;
SQLiteTransaction transaction = null;
SQLiteCommand comm = null;
try
{
//Open a new connection
connection = SQLiteHelper.OpenConn();
//Start transaction (ATOMICITY)
transaction = connection.BeginTransaction();
//Start a new query and assign the transaction
comm = connection.CreateCommand();
comm.Transaction = transaction;
//Start inserting items
foreach (CustomerModel item in customer)
{
comm.CommandText = commandString;
comm.Parameters.Add("@p1", DbType.String).Value = item.CustFName;
comm.Parameters.Add("@p2", DbType.String).Value = item.CustLName;
comm.Parameters.Add("@p3", DbType.String).Value = item.CustEmail;
affected += comm.ExecuteNonQuery();
}
//Commit transaction
transaction.Commit();
//Close connection
SQLiteHelper.CloseConn();
}
catch (Exception e)
{
//Transaction Rollback Failure
if (transaction != null)
{
try
{
transaction.Rollback();
affected = 0;
}
catch (SQLiteException sqlEx)
{
MessageBox.Show("Transaction rollback Failure: " + sqlEx.Message);
}
finally
{
transaction.Dispose();
}
}
Console.WriteLine(e.Message);
SQLiteHelper.CloseConn();
MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
affected = -1;
}
return(affected);
}
