public void InsertBook(Book value)
{
// string cs = @"URI=file:C:\Users\jslucas\source\repos\mis321\database\book.db";
// using var con = new SQLiteConnection(cs);
// con.Open();
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"INSERT INTO books(title, author) VALUES(@title, @author)";
cmd.Parameters.AddWithValue("@title", value.Title);
cmd.Parameters.AddWithValue("@author", value.Author);
cmd.Prepare();
cmd.ExecuteNonQuery();
//close connection
db.CloseConnection();
}
}
public void DeleteItem(int id)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
//defining the string
string stm = @"DELETE FROM Item WHERE `Item ID` = @id";
//making new command
MySqlCommand cmd = new MySqlCommand(stm, con);
//preparing the command and executing it; this deletes the record with the right id
cmd.Parameters.AddWithValue("@id", id);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
}
}
public void SaveCustomer(string FirstName, string LastName, string Email, string Password, string PhoneNumber, int RewardsPoints)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"INSERT INTO Customer(`Customer Email`, `Customer First Name`, `Customer Last Name`, `Customer Phone Number`, `Rewards Points`, `Password`) VALUES(@CustomerEmail, @CustomerFirstName, @CustomerLastName, @CustomerPhoneNumber, @RewardsPoints, @Password)";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@CustomerEmail", Email);
cmd.Parameters.AddWithValue("@CustomerFirstName", FirstName);
cmd.Parameters.AddWithValue("@CustomerLastName", LastName);
cmd.Parameters.AddWithValue("@CustomerPhoneNumber", PhoneNumber);
cmd.Parameters.AddWithValue("@RewardsPoints", RewardsPoints);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
}
}
public int UpdateRewardsPoints(Customer value)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"UPDATE Customer SET `Rewards Points` = @RewardsPoints WHERE `Customer Email` = @CustomerEmail";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@CustomerEmail", value.Email);
cmd.Parameters.AddWithValue("@RewardsPoints", value.RewardsPoints);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
return(value.RewardsPoints);
}
//-1 is returned if something goes wrong; -1 is a recognizable sentinel value across the system
return(-1);
}
public List <Item> GetTransactionItems(int id)
{
List <Item> transItems = new List <Item>();
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = "SELECT * FROM Item WHERE `Transaction ID` = @transID";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@transID", id);
cmd.Prepare();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
transItems.Add(ParseItemFromRdr(rdr));
}
}
db.CloseConnection();
return(transItems);
}
else
{
return(new List <Item>());
}
}
public void SaveEmployee(Employee value)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"INSERT INTO Employee(`Employee First Name`, `Employee Last Name`, `Employee Address`, `Employee SSN`, `Employee Birth Date`, `Username`, `Password`) VALUES(@EmployeeFirstName, @EmployeeLastName, @EmployeeAddress, @EmployeeSSN, @EmployeeBirthDate, @Username, @Password)";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@EmployeeFirstName", value.FirstName);
cmd.Parameters.AddWithValue("@EmployeeLastName", value.LastName);
cmd.Parameters.AddWithValue("@EmployeeAddress", value.Address);
cmd.Parameters.AddWithValue("@EmployeeSSN", value.SSN);
cmd.Parameters.AddWithValue("@EmployeeBirthDate", value.BirthDate);
cmd.Parameters.AddWithValue("@Username", value.Username);
cmd.Parameters.AddWithValue("@Password", value.Password);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
}
}
public Item GetItem(int id)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
Item temp = new Item();
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = "SELECT * FROM Item WHERE `Item ID` = @id;";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@id", id);
cmd.Prepare();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
temp = ParseItemFromRdr(rdr);
}
}
db.CloseConnection();
return(temp);
}
else
{
return(new Item());
}
}
public List <Item> GetAllItems()
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = "SELECT * FROM Item";
MySqlCommand cmd = new MySqlCommand(stm, con);
List <Item> items = new List <Item>();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
items.Add(ParseItemFromRdr(rdr));
}
}
db.CloseConnection();
return(items);
}
else
{
//if something goes wrong, we just return an empty list
return(new List <Item>());
}
}
public void AddItem(Item value)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"INSERT INTO Item (`Item Name`, `Item Price`, `Item Year`, `Item Cost`, `Is Purchased`, `Card Condition`, `Card Sport`, `Card Team`, `Memorabilia Description`) VALUES(@ItemName, @ItemPrice, @ItemYear, @ItemCost, @IsPurchased, @CardCondition, @CardSport, @CardTeam, @MemorabiliaDescription)";
//making a command with the connection
MySqlCommand cmd = new MySqlCommand(stm, con);
//adding sample data
cmd.Parameters.AddWithValue("@ItemName", value.ItemName);
cmd.Parameters.AddWithValue("@ItemPrice", value.ItemPrice);
cmd.Parameters.AddWithValue("@ItemYear", value.ItemYear);
cmd.Parameters.AddWithValue("@ItemCost", value.ItemCost);
cmd.Parameters.AddWithValue("@IsPurchased", "false");
cmd.Parameters.AddWithValue("@CardCondition", value.ItemCardCondition);
cmd.Parameters.AddWithValue("@CardSport", value.ItemCardSport);
cmd.Parameters.AddWithValue("@CardTeam", value.ItemCardTeam);
cmd.Parameters.AddWithValue("@MemorabiliaDescription", value.ItemMemorabiliaDescription);
//preparing the command string before touching the database
cmd.Prepare();
//actually executing the command to insert a new item
cmd.ExecuteNonQuery();
db.CloseConnection();
}
}
public void UpdateItem(Item updatedItem)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
//defining the string
string stm = @"UPDATE Item SET `Item Name` = @ItemName, `Item Price` = @ItemPrice, `Item Year` = @ItemYear, `Card Condition` = @CardCondition, `Card Sport` = @CardSport, `Card Team` = @CardTeam, `Memorabilia Description` = @MemorabiliaDescription WHERE `Item ID` = @ItemID";
//making new command
MySqlCommand cmd = new MySqlCommand(stm, con);
//preparing the command and executing it
cmd.Parameters.AddWithValue("@ItemID", updatedItem.ItemID);
cmd.Parameters.AddWithValue("@ItemName", updatedItem.ItemName);
cmd.Parameters.AddWithValue("@ItemPrice", updatedItem.ItemPrice);
cmd.Parameters.AddWithValue("@ItemYear", updatedItem.ItemYear);
cmd.Parameters.AddWithValue("@CardCondition", updatedItem.ItemCardCondition);
cmd.Parameters.AddWithValue("@CardSport", updatedItem.ItemCardSport);
cmd.Parameters.AddWithValue("@CardTeam", updatedItem.ItemCardTeam);
cmd.Parameters.AddWithValue("@MemorabiliaDescription", updatedItem.ItemMemorabiliaDescription);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
}
//if the open fails, the api should just do nothing; hopefully this helps prevent some crashes
}
//the idea behind logging in a customer is to return their rewards points: -1 means the customer wasn't found, anything else means they're signed in
public int FindCustomer(Customer value)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
int temp = -1;
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"SELECT `Customer Email`, `Rewards Points` FROM Customer WHERE `Customer Email` = @email AND `Password` = @password;";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@email", value.Email);
cmd.Parameters.AddWithValue("@password", value.Password);
cmd.Prepare();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
//try to return the rewards points from the customer
try {
temp = Convert.ToInt32(rdr[1]);
} catch {
db.CloseConnection();
//if the customer doesn't exist, this will throw an exception; if the customer doesn't exist, return -1 as rewards points
return(temp);
}
}
}
db.CloseConnection();
return(temp);
}
//this sentinel value will indicate that the connection failed, which might be helpful on the front-end
return(-2);
}
public List <Book> GetAllBooks()
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
string stm = "SELECT * FROM books";
MySqlCommand cmd = new MySqlCommand(stm, conn);
List <Book> allBooks = new List <Book>();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
allBooks.Add(new Book()
{
Id = rdr.GetInt32(0), Title = rdr.GetString(1), Author = rdr.GetString(2)
});
}
}
db.CloseConnection();
return(allBooks);
}
else
{
return(new List <Book>());
}
// string cs = @"URI=file:C:\Users\jslucas\source\repos\mis321\database\book.db";
// using var con = new SQLiteConnection(cs);
// con.Open();
// string stm = "SELECT * FROM books";
// using var cmd = new SQLiteCommand(stm, con);
// using SQLiteDataReader rdr = cmd.ExecuteReader();
// List<Book> allBooks = new List<Book>();
// while(rdr.Read())
// {
// allBooks.Add(new Book(){Id = rdr.GetInt32(0), Title = rdr.GetString(1), Author=rdr.GetString(2)});
// }
// return allBooks;
}
public List <Transaction> GetAllTransactions()
{
//making an item selector up front
IGetTransactionItems readObj = new ReadItemData();
//making an empty list of transactions
List <Transaction> transactions = new List <Transaction>();
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = "SELECT * FROM Transact";
MySqlCommand cmd = new MySqlCommand(stm, con);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Transaction newTrans = new Transaction()
{
TransactionID = rdr.GetInt32(0), TransactionDate = DateTime.Parse(rdr.GetString(1)), AmtDiscounted = rdr.GetDouble(2), PaymentType = rdr.GetString(3), EmployeeID = rdr.GetInt32(4), CustomerEmail = rdr.GetString(5)
};
List <Item> transItems = readObj.GetTransactionItems(newTrans.TransactionID);
newTrans.ItemIDs = GetItemIDs(transItems);
newTrans.Subtotal = GetSubtotal(transItems);
transactions.Add(newTrans);
}
}
db.CloseConnection();
return(transactions);
}
return(transactions);
}
public void SaveTransaction(string PaymentType, int EmployeeID, string CustomerEmail, List <int> ItemIDs, double AmtDiscounted, DateTime TransactionDate)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"INSERT INTO Transact(`Transaction Date`, `Amount Discounted`, `Payment Type`, `Employee ID`, `Customer Email`) VALUES(@TransactionDate, @AmountDiscounted, @PaymentType, @EmployeeID, @CustomerEmail)";
MySqlCommand cmd = new MySqlCommand(stm, con);
//inserting the transaction
cmd.Parameters.AddWithValue("@TransactionDate", TransactionDate);
cmd.Parameters.AddWithValue("@AmountDiscounted", AmtDiscounted);
cmd.Parameters.AddWithValue("@PaymentType", PaymentType);
cmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
cmd.Parameters.AddWithValue("@CustomerEmail", CustomerEmail);
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"SELECT `Transaction ID` FROM Transact ORDER BY `Transaction Date` DESC LIMIT 1";
cmd.Prepare();
var transactionID = cmd.ExecuteScalar();
foreach (int itemId in ItemIDs)
{
cmd.CommandText = @"UPDATE Item SET `Transaction ID` = @TransactionID, `Is Purchased` = 'true' WHERE `Item ID` = @ItemID";
cmd.Parameters.AddWithValue("@TransactionID", transactionID);
cmd.Parameters.AddWithValue("@ItemID", itemId);
cmd.Prepare();
cmd.ExecuteNonQuery();
}
db.CloseConnection();
}
}
public void Create()
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
//Dropping and Creating the tables
//Manager Table
cmd.CommandText = "DROP TABLE IF EXISTS Manager";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE Manager (
ManagerID INTEGER PRIMARY KEY,
ManagerName TEXT,
ManagerPhone TEXT,
ManagerEmail TEXT,
ManagerAddress TEXT
)";
cmd.ExecuteNonQuery();
//Employee Table
cmd.CommandText = "DROP TABLE IF EXISTS Employee";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE Employee (
EmployeeID INTEGER PRIMARY KEY,
EmployeeName TEXT,
EmployeePhone TEXT,
EmployeeEmail TEXT,
EmployeeAddress TEXT
)";
cmd.ExecuteNonQuery();
//Member Table
cmd.CommandText = "DROP TABLE IF EXISTS Member";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE Member (
MemberID INTEGER AUTO_INCREMENT PRIMARY KEY,
MemberName TEXT,
MemberAddress TEXT,
MemberEmail TEXT,
MemberDOB TEXT,
MemberPhone TEXT
)";
cmd.ExecuteNonQuery();
//Product Table
cmd.CommandText = "DROP TABLE IF EXISTS Product";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE Product (
ProductID INTEGER AUTO_INCREMENT PRIMARY KEY,
ProductName TEXT,
ProductPrice INTEGER,
ProductType TEXT,
ProductStatus TEXT,
ProductDiscount INTEGER,
DateOrdered TEXT,
DateAddedToInv TEXT,
ManagerID INTEGER,
ManagerName TEXT,
EmployeeID INTEGER,
EmployeeName TEXT,
FOREIGN KEY (ManagerID)
REFERENCES Manager (ManagerID),
FOREIGN KEY (EmployeeID)
REFERENCES Employee (EmployeeID)
)";
cmd.ExecuteNonQuery();
//Transaction
cmd.CommandText = "DROP TABLE IF EXISTS Transactions";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE Transactions (
TransactionID INTEGER PRIMARY KEY,
TransactionDate TEXT,
TransactionCost INTEGER,
ManagerID INTEGER,
ManagerName TEXT,
EmployeeID INTEGER,
EmployeeName TEXT,
MemberID INTEGER,
FOREIGN KEY (ManagerID)
REFERENCES Manager (ManagerID),
FOREIGN KEY (EmployeeID)
REFERENCES Employee (EmployeeID),
FOREIGN KEY (MemberID)
REFERENCES Member (MemberID)
)";
cmd.ExecuteNonQuery();
//Transaction Line Item
cmd.CommandText = "DROP TABLE IF EXISTS TransactionLineItem";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE TransactionLineItem (
ProductID INTEGER PRIMARY KEY,
ProductName TEXT,
ProductPrice INTEGER,
ProductType TEXT,
ProductDiscount INTEGER,
TransactionID INTEGER,
FOREIGN KEY (ProductID)
REFERENCES Product (ProductID),
FOREIGN KEY (TransactionID)
REFERENCES Transactions (TransactionID)
)";
cmd.ExecuteNonQuery();
//Inserting data
//Manager
cmd.CommandText = @"INSERT INTO Manager(ManagerID, ManagerName, ManagerPhone, ManagerEmail, ManagerAddress)
VALUES(@ManagerID, @ManagerName, @ManagerPhone, @ManagerEmail, @ManagerAddress)";
cmd.Parameters.AddWithValue("@ManagerID", "100");
cmd.Parameters.AddWithValue("@ManagerName", "Preston Gates");
cmd.Parameters.AddWithValue("@ManagerPhone", "111-111-1111");
cmd.Parameters.AddWithValue("@ManagerEmail", "*****@*****.**");
cmd.Parameters.AddWithValue("@ManagerAddress", "321 Bidgood Ln. Tuscaloosa, AL 35407");
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO Manager(ManagerID, ManagerName, ManagerPhone, ManagerEmail, ManagerAddress)
VALUES(@ManagerID, @ManagerName, @ManagerPhone, @ManagerEmail, @ManagerAddress)";
cmd.Parameters.AddWithValue("@ManagerID", "200");
cmd.Parameters.AddWithValue("@ManagerName", "Bobby Smith");
cmd.Parameters.AddWithValue("@ManagerPhone", "121-121-1221");
cmd.Parameters.AddWithValue("@ManagerEmail", "*****@*****.**");
cmd.Parameters.AddWithValue("@ManagerAddress", "330 Bidgood Ln. Tuscaloosa, AL 35407");
cmd.Prepare();
cmd.ExecuteNonQuery();
//Employee
cmd.CommandText = @"INSERT INTO Employee(EmployeeID, EmployeeName, EmployeePhone, EmployeeEmail, EmployeeAddress)
VALUES(@EmployeeID, @EmployeeName, @EmployeePhone, @EmployeeEmail, @EmployeeAddress)";
cmd.Parameters.AddWithValue("@EmployeeID", "10");
cmd.Parameters.AddWithValue("@EmployeeName", "Molly");
cmd.Parameters.AddWithValue("@EmployeePhone", "222-222-2222");
cmd.Parameters.AddWithValue("@EmployeeEmail", "*****@*****.**");
cmd.Parameters.AddWithValue("@EmployeeAddress", "321 Hewson Ln. Tuscaloosa, AL 35407");
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO Employee(EmployeeID, EmployeeName, EmployeePhone, EmployeeEmail, EmployeeAddress)
VALUES(@EmployeeID, @EmployeeName, @EmployeePhone, @EmployeeEmail, @EmployeeAddress)";
cmd.Parameters.AddWithValue("@EmployeeID", "20");
cmd.Parameters.AddWithValue("@EmployeeName", "Kevin");
cmd.Parameters.AddWithValue("@EmployeePhone", "212-212-2112");
cmd.Parameters.AddWithValue("@EmployeeEmail", "*****@*****.**");
cmd.Parameters.AddWithValue("@EmployeeAddress", "330 Hewson Ln. Tuscaloosa, AL 35407");
cmd.Prepare();
cmd.ExecuteNonQuery();
//Member
cmd.CommandText = @"INSERT INTO Member(MemberName, MemberAddress, MemberEmail, MemberDOB, MemberPhone)
VALUES(@MemberName, @MemberAddress, @MemberEmail, @MemberDOB, @MemberPhone)";
cmd.Parameters.AddWithValue("@MemberName", "Johnny Smith");
cmd.Parameters.AddWithValue("@MemberAddress", "123 Computer Science Dr. Apt 15. Tuscaloosa, AL 35407");
cmd.Parameters.AddWithValue("@MemberEmail", "*****@*****.**");
cmd.Parameters.AddWithValue("@MemberDOB", "05/18/1999");
cmd.Parameters.AddWithValue("@MemberPhone", "333-333-3333");
cmd.Prepare();
cmd.ExecuteNonQuery();
//Product
cmd.CommandText = @"INSERT INTO Product(ProductName, ProductPrice, ProductType, ProductStatus, ProductDiscount, DateOrdered, DateAddedToInv, ManagerID, ManagerName, EmployeeID, EmployeeName)
VALUES(@ProductName, @ProductPrice, @ProductType, @ProductStatus, @ProductDiscount, @DateOrdered, @DateAddedToInv, @ManagerID, @ManagerName, @EmployeeID, @EmployeeName)";
cmd.Parameters.AddWithValue("@ProductName", "2020 Albert Pujols - Los Angeles Angels: PSA 7");
cmd.Parameters.AddWithValue("@ProductPrice", "10.00");
cmd.Parameters.AddWithValue("@ProductType", "Baseball Card");
cmd.Parameters.AddWithValue("@ProductStatus", "Sold");
cmd.Parameters.AddWithValue("@ProductDiscount", "0");
cmd.Parameters.AddWithValue("@DateOrdered", "10/11/2020");
cmd.Parameters.AddWithValue("@DateAddedToInv", "11/03/2020");
cmd.Parameters.AddWithValue("@ManagerID", "100");
cmd.Parameters.AddWithValue("@ManagerName", "Preston Gates");
cmd.Parameters.AddWithValue("@EmployeeID", "10");
cmd.Parameters.AddWithValue("@EmployeeName", "Molly");
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO Product(ProductName, ProductPrice, ProductType, ProductStatus, ProductDiscount, DateOrdered, DateAddedToInv, ManagerID, ManagerName, EmployeeID, EmployeeName)
VALUES(@ProductName, @ProductPrice, @ProductType, @ProductStatus, @ProductDiscount, @DateOrdered, @DateAddedToInv, @ManagerID, @ManagerName, @EmployeeID, @EmployeeName)";
cmd.Parameters.AddWithValue("@ProductName", "2020 Dylan Bundy - Los Angeles Angels: PSA 6");
cmd.Parameters.AddWithValue("@ProductPrice", "9.50");
cmd.Parameters.AddWithValue("@ProductType", "Baseball Card");
cmd.Parameters.AddWithValue("@ProductStatus", "In Stock");
cmd.Parameters.AddWithValue("@ProductDiscount", "0");
cmd.Parameters.AddWithValue("@DateOrdered", "10/13/2020");
cmd.Parameters.AddWithValue("@DateAddedToInv", "11/04/2020");
cmd.Parameters.AddWithValue("@ManagerID", "200");
cmd.Parameters.AddWithValue("@ManagerName", "Bobby Smith");
cmd.Parameters.AddWithValue("@EmployeeID", "20");
cmd.Parameters.AddWithValue("@EmployeeName", "Kevin");
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO Product(ProductName, ProductPrice, ProductType, ProductStatus, ProductDiscount, DateOrdered, DateAddedToInv, ManagerID, ManagerName, EmployeeID, EmployeeName)
VALUES(@ProductName, @ProductPrice, @ProductType, @ProductStatus, @ProductDiscount, @DateOrdered, @DateAddedToInv, @ManagerID, @ManagerName, @EmployeeID, @EmployeeName)";
cmd.Parameters.AddWithValue("@ProductName", "2020 Tommy La Stella - Los Angeles Angels: PSA 6");
cmd.Parameters.AddWithValue("@ProductPrice", "9.50");
cmd.Parameters.AddWithValue("@ProductType", "Baseball Card");
cmd.Parameters.AddWithValue("@ProductStatus", "In Stock");
cmd.Parameters.AddWithValue("@ProductDiscount", "0");
cmd.Parameters.AddWithValue("@DateOrdered", "10/09/2020");
cmd.Parameters.AddWithValue("@DateAddedToInv", "11/02/2020");
cmd.Parameters.AddWithValue("@ManagerID", "200");
cmd.Parameters.AddWithValue("@ManagerName", "Bobby Smith");
cmd.Parameters.AddWithValue("@EmployeeID", "10");
cmd.Parameters.AddWithValue("@EmployeeName", "Molly");
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO Product(ProductName, ProductPrice, ProductType, ProductStatus, ProductDiscount, DateOrdered, DateAddedToInv, ManagerID, ManagerName, EmployeeID, EmployeeName)
VALUES(@ProductName, @ProductPrice, @ProductType, @ProductStatus, @ProductDiscount, @DateOrdered, @DateAddedToInv, @ManagerID, @ManagerName, @EmployeeID, @EmployeeName)";
cmd.Parameters.AddWithValue("@ProductName", "2020 Matt Thais - Los Angeles Angels: PSA 7");
cmd.Parameters.AddWithValue("@ProductPrice", "10.50");
cmd.Parameters.AddWithValue("@ProductType", "Baseball Card");
cmd.Parameters.AddWithValue("@ProductStatus", "In Stock");
cmd.Parameters.AddWithValue("@ProductDiscount", "0");
cmd.Parameters.AddWithValue("@DateOrdered", "10/15/2020");
cmd.Parameters.AddWithValue("@DateAddedToInv", "11/02/2020");
cmd.Parameters.AddWithValue("@ManagerID", "100");
cmd.Parameters.AddWithValue("@ManagerName", "Preston Gates");
cmd.Parameters.AddWithValue("@EmployeeID", "20");
cmd.Parameters.AddWithValue("@EmployeeName", "Kevin");
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO Product(ProductName, ProductPrice, ProductType, ProductStatus, ProductDiscount, DateOrdered, DateAddedToInv, ManagerID, ManagerName, EmployeeID, EmployeeName)
VALUES(@ProductName, @ProductPrice, @ProductType, @ProductStatus, @ProductDiscount, @DateOrdered, @DateAddedToInv, @ManagerID, @ManagerName, @EmployeeID, @EmployeeName)";
cmd.Parameters.AddWithValue("@ProductName", "2020 Patrick Sandoval - Los Angeles Angels: PSA 6");
cmd.Parameters.AddWithValue("@ProductPrice", "9.50");
cmd.Parameters.AddWithValue("@ProductType", "Baseball Card");
cmd.Parameters.AddWithValue("@ProductStatus", "In Stock");
cmd.Parameters.AddWithValue("@ProductDiscount", "0");
cmd.Parameters.AddWithValue("@DateOrdered", "10/06/2020");
cmd.Parameters.AddWithValue("@DateAddedToInv", "11/02/2020");
cmd.Parameters.AddWithValue("@ManagerID", "100");
cmd.Parameters.AddWithValue("@ManagerName", "Preston Gates");
cmd.Parameters.AddWithValue("@EmployeeID", "10");
cmd.Parameters.AddWithValue("@EmployeeName", "Molly");
cmd.Prepare();
cmd.ExecuteNonQuery();
//Transactions
cmd.CommandText = @"INSERT INTO Transactions(TransactionID, TransactionDate, TransactionCost, ManagerID, ManagerName, EmployeeID, EmployeeName, MemberID)
VALUES(@TransactionID, @TransactionDate, @TransactionCost, @ManagerID, @ManagerName, @EmployeeID, @EmployeeName, @MemberID)";
cmd.Parameters.AddWithValue("@TransactionID", "1");
cmd.Parameters.AddWithValue("@TransactionDate", "10/26/2020");
cmd.Parameters.AddWithValue("@TransactionCost", "10.00");
cmd.Parameters.AddWithValue("@ManagerID", "100");
cmd.Parameters.AddWithValue("@ManagerName", "Preston Gates");
cmd.Parameters.AddWithValue("@EmployeeID", "20");
cmd.Parameters.AddWithValue("@EmployeeName", "Kevin");
cmd.Parameters.AddWithValue("@MemberID", "1");
cmd.Prepare();
cmd.ExecuteNonQuery();
//Transaction Line Item
cmd.CommandText = @"INSERT INTO TransactionLineItem(ProductID, ProductName, ProductPrice, ProductType, ProductDiscount, TransactionID)
VALUES(@ProductID, @ProductName, @ProductPrice, @ProductType, @ProductDiscount, @TransactionID)";
cmd.Parameters.AddWithValue("@ProductID", "1");
cmd.Parameters.AddWithValue("@ProductName", "2020 Albert Pujols - Los Angeles Angels: PSA 7");
cmd.Parameters.AddWithValue("@ProductPrice", "10.00");
cmd.Parameters.AddWithValue("@ProductType", "Baseball Card");
cmd.Parameters.AddWithValue("@ProductDiscount", "0");
cmd.Parameters.AddWithValue("@TransactionID", "1");
cmd.Prepare();
cmd.ExecuteNonQuery();
//close connection
db.CloseConnection();
}
}