예제 #1
0
        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);
        }
예제 #2
0
        //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);
        }
예제 #3
0
        //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);
        }
예제 #4
0
        //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);
        }
예제 #5
0
        //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);
        }
예제 #6
0
        //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);
        }
예제 #7
0
        //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);
        }
예제 #8
0
        //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);
        }