public static List <State> GetStates() //state list defined called GetStates()
{
List <State> states = new List <State>(); //empty
//Step 1 Connection
SqlConnection connection = MMABooksDB.GetConnection(); // getting connection form the MMABooksDB class
//Step 2 Command
string selectStatement = "SELECT StateCode, StateName FROM States ORDER BY StateName"; //SQL command
SqlCommand cmd = new SqlCommand(selectStatement, connection); //Takes two parameters, the stament and the connection
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
State s;
while (reader.Read()) //while there is another record
{
s = new State(); //new state object
s.StateCode = reader["StateCode"].ToString();
s.StateName = reader["StateName"].ToString();
states.Add(s);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(states);
}
/// <summary>
/// Adds a new customer to the Customers table in MMABooks database
/// </summary>
/// <param name="cust">Customer object that contains dtat for the new record</param>
/// <returns>generated CustomerID</returns>
public static int AddCustomer(Customer cust)
{
SqlConnection con = MMABooksDB.GetConnection();
string insertStatement = "INSERT INTO Customers (Name, Address, City, State, ZipCode) " +
"VALUES (@Name, @Address, @City, @State, @ZipCode)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@Name", cust.Name);
cmd.Parameters.AddWithValue("@Address", cust.Address);
cmd.Parameters.AddWithValue("@City", cust.City);
cmd.Parameters.AddWithValue("@State", cust.State);
cmd.Parameters.AddWithValue("@ZipCode", cust.ZipCode);
try
{
con.Open();
cmd.ExecuteNonQuery(); //run the insert command
// get the generated ID - current identity value for Customers table
string selectQuery = "SELECT IDENT_CURRENT('Customers') FROM Customers";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int customerID = Convert.ToInt32(selectCmd.ExecuteScalar()); //single value
return(customerID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <State> GetStates()
{
List <State> states = new List <State>();
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement = "SELECT StateCode, StateName "
+ "FROM States "
+ "ORDER BY StateName";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
State s = new State();
s.StateCode = reader["StateCode"].ToString();
s.StateName = reader["StateName"].ToString();
states.Add(s);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(states);
}
public static Customer GetCustomer(int customerID)
{
Customer cust = null;
SqlConnection con = MMABooksDB.GetConnection();
string selectStatement = "SELECT CustomerID, Name, Address, City, State, ZipCode " +
"FROM Customers " +
"WHERE CustomerID = @CustomerID";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@CustomerID", customerID); // value comes from the method's argument
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read()) // found a customer
{
cust = new Customer();
cust.CustomerID = (int)reader["CustomerID"];
cust.Name = reader["Name"].ToString();
cust.Address = reader["Address"].ToString();
cust.City = reader["City"].ToString();
cust.State = reader["State"].ToString();
cust.ZipCode = reader["ZipCode"].ToString();
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(cust);
}
public static void AddProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
string insertStatement =
"INSERT Products " +
"(ProductCode, Description, UnitPrice, OnHandQuantity) " +
"VALUES (@ProductCode, @Description, @UnitPrice, @OnHandQuantity)";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@ProductCode", product.ProductCode);
insertCommand.Parameters.AddWithValue(
"@Description", product.Description);
insertCommand.Parameters.AddWithValue(
"@UnitPrice", product.UnitPrice);
insertCommand.Parameters.AddWithValue(
"@OnHandQuantity", product.OnHandQuantity);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Customer GetCustomer(int custID)
{
Customer cust = null;
using (SqlConnection conn = MMABooksDB.GetConnection())
{
string query = "SELECT CustomerID,Name,Address,City,State,ZipCode " +
"FROM Customers Where CustomerID = @CustomerID";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@CustomerID", custID);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.Read())
{
cust = new Customer
{
CustomerID = (int)reader["CustomerID"],
Name = reader["Name"].ToString(),
Address = reader["Address"].ToString(),
City = reader["City"].ToString(),
State = reader["State"].ToString(),
ZipCode = reader["ZipCode"].ToString()
};
}
} // closes reader
} // closes cmd
} // closes conection
return(cust);
}
public static List <State> GetStates()
{
List <State> states = new List <State>();
State st;
// retrieve all states and add to list
// create connection
using (SqlConnection conn = MMABooksDB.GetConnection())
{
//create command
string query = "SELECT StateCode,StateName FROM States ORDER BY StateName";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
//run command and process results
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
st = new State
{
StateCode = reader["StateCode"].ToString(),
StateName = reader["StateName"].ToString()
};
states.Add(st);
}
} // closes reader
} // cmd object recycled
} // connection is closed if open & Connection object recycled
return(states);
} // end of method
public static bool DeleteCustomer(Customer cust)
{
int count = 0;
using (SqlConnection conn = MMABooksDB.GetConnection())
{ // only delete the record if all the values are the same -
// no one has updated the record previous to delete
string query = "DELETE from Customers where CustomerID = @CustomerID " +
"AND Name=@Name " + // for optimistic concurrancy
"AND Address=@Address " +
"AND City=@City " +
"AND State=@State " +
"AND ZipCode=@ZipCode";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@CustomerID", cust.CustomerID);
cmd.Parameters.AddWithValue("@Name", cust.Name);
cmd.Parameters.AddWithValue("@Address", cust.Address);
cmd.Parameters.AddWithValue("@City", cust.City);
cmd.Parameters.AddWithValue("@State", cust.State);
cmd.Parameters.AddWithValue("@ZipCode", cust.ZipCode);
conn.Open();
count = cmd.ExecuteNonQuery();
}
}
return(count > 0);
}
public static Customer GetCustomer(int customerID)
{
// Creates a connection object.
SqlConnection connection = MMABooksDB.GetConnection();
// Our SELECT statement, parameterized.
string statement = "SELECT * FROM Customers WHERE CustomerID=@customerID;";
// The command object we're going to modify to access the database.
SqlCommand command = new SqlCommand(statement, connection);
// Adds the customerID parameter shown above.
command.Parameters.AddWithValue("@customerID", customerID);
try
{
// Opens the connection to the SQL server.
connection.Open();
// Creates the reader object so we can get the information from the SQL server.
// Also executes the statement above.
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
// Creates a new temporary customer object.
Customer customer = new Customer();
// Sets the variables of our customer object.
customer.CustomerID = (int)reader["CustomerID"];
customer.Name = (string)reader["Name"];
customer.Address = (string)reader["Address"];
customer.City = (string)reader["City"];
customer.State = (string)reader["State"];
customer.ZipCode = (string)reader["ZipCode"];
// Returns our customer object.
return(customer);
}
else
{
// No object was found.
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool UpdateProduct(Product oldProduct,
Product newProduct)
{
SqlConnection connection = MMABooksDB.GetConnection();
string updateStatement =
"UPDATE Products SET " +
"ProductCode = @NewProductCode, " +
"Description = @NewDescription, " +
"UnitPrice = @NewUnitPrice, " +
"OnHandQuantity = @NewOnHandQuantity " +
"WHERE ProductCode = @OldProductCode " +
"AND Description = @OldDescription " +
"AND UnitPrice = @OldUnitPrice " +
"AND OnHandQuantity = @OldOnHandQuantity";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@NewProductCode", newProduct.ProductCode);
updateCommand.Parameters.AddWithValue(
"@NewDescription", newProduct.Description);
updateCommand.Parameters.AddWithValue(
"@NewUnitPrice", newProduct.UnitPrice);
updateCommand.Parameters.AddWithValue(
"@NewOnHandQuantity", newProduct.OnHandQuantity);
updateCommand.Parameters.AddWithValue(
"@OldProductCode", oldProduct.ProductCode);
updateCommand.Parameters.AddWithValue(
"@OldDescription", oldProduct.Description);
updateCommand.Parameters.AddWithValue(
"@OldUnitPrice", oldProduct.UnitPrice);
updateCommand.Parameters.AddWithValue(
"@OldOnHandQuantity", oldProduct.OnHandQuantity);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <State> GetStates()
{
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement = "SELECT StateCode, StateName "
+ "FROM States "
+ "ORDER BY StateName";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
return(QueryStates(connection, selectCommand));
}
public static bool UpdateCustomer(Customer oldCustomer,
Customer newCustomer)
{
SqlConnection connection = MMABooksDB.GetConnection();
string updateStatement =
"UPDATE Customers SET " +
"Name = @NewName, " +
"Address = @NewAddress, " +
"City = @NewCity, " +
"State = @NewState, " +
"ZipCode = @NewZipCode " +
"WHERE CustomerID = @OldCustomerID " +
"AND Name = @OldName " +
"AND Address = @OldAddress " +
"AND City = @OldCity " +
"AND State = @OldState " +
"AND ZipCode = @OldZipCode";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@NewName", newCustomer.Name);
updateCommand.Parameters.AddWithValue("@NewAddress", newCustomer.Address);
updateCommand.Parameters.AddWithValue("@NewCity", newCustomer.City);
updateCommand.Parameters.AddWithValue("@NewState", newCustomer.State);
updateCommand.Parameters.AddWithValue("@NewZipCode", newCustomer.ZipCode);
updateCommand.Parameters.AddWithValue("@OldCustomerID", oldCustomer.CustomerID);
updateCommand.Parameters.AddWithValue("@OldName", oldCustomer.Name);
updateCommand.Parameters.AddWithValue("@OldAddress", oldCustomer.Address);
updateCommand.Parameters.AddWithValue("@OldCity", oldCustomer.City);
updateCommand.Parameters.AddWithValue("@OldState", oldCustomer.State);
updateCommand.Parameters.AddWithValue("@OldZipCode", oldCustomer.ZipCode);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool DeleteCustomer(Customer toDelete)
{
SqlConnection connection = MMABooksDB.GetConnection();
// Should I be placing the semicolon at the end of each
// query? I always did it in my node web servers and I
// still don't know if it truly matters.
string statement = @"
DELETE FROM Customers
WHERE CustomerID = @customerID
AND Name = @name
AND Address = @address
AND City = @city
AND State = @state
AND ZipCode = @zipCode;
";
SqlCommand command = new SqlCommand(statement, connection);
command.Parameters.AddWithValue("@customerID", toDelete.CustomerID);
command.Parameters.AddWithValue("@name", toDelete.Name);
command.Parameters.AddWithValue("@address", toDelete.Address);
command.Parameters.AddWithValue("@city", toDelete.City);
command.Parameters.AddWithValue("@state", toDelete.State);
command.Parameters.AddWithValue("@zipCode", toDelete.ZipCode);
try
{
connection.Open();
int count = command.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <State> GetStates()
{
// The list we're going to return.
List <State> states = new List <State>();
// Creates a new connection to the database.
SqlConnection connection = MMABooksDB.GetConnection();
// Selects the StateCode and StateName from the States table.
string statement = "SELECT * FROM States ORDER BY StateName;";
SqlCommand command = new SqlCommand(statement, connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
while (reader.Read())
{
State state = new State();
state.StateCode = (string)reader["StateCode"];
state.StateName = (string)reader["StateName"];
states.Add(state);
}
reader.Close();
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(states);
}
public static Customer GetCustomer(int customerID)
{
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement =
"SELECT CustomerID, Name, Address, City, State, ZipCode" +
" FROM Customers" +
" WHERE CustomerID = @CustomerID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
SqlParameter customerIDParam = new SqlParameter();
customerIDParam.ParameterName = "@CustomerID";
customerIDParam.Value = customerID;
selectCommand.Parameters.Add(customerIDParam);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
Customer customer = new Customer();
customer.Name = reader["Name"].ToString();
customer.Address = reader["Address"].ToString();
customer.City = reader["City"].ToString();
customer.State = reader["State"].ToString();
customer.ZipCode = reader["ZipCode"].ToString();
customer.CustomerID = customerID;
return(customer);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool DeleteCustomer(Customer customer)
{
SqlConnection connection = MMABooksDB.GetConnection();
string deleteStatement =
"DELETE FROM Customers " +
"WHERE CustomerID = @CustomerID " +
"AND Name = @Name " +
"AND Address = @Address " +
"AND City = @City " +
"AND State = @State " +
"AND ZipCode = @ZipCode";
SqlCommand deleteCommand =
new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue(
"@CustomerID", customer.CustomerID);
deleteCommand.Parameters.AddWithValue(
"@Name", customer.Name);
deleteCommand.Parameters.AddWithValue(
"@Address", customer.Address);
deleteCommand.Parameters.AddWithValue(
"@City", customer.City);
deleteCommand.Parameters.AddWithValue(
"@State", customer.State);
deleteCommand.Parameters.AddWithValue(
"@ZipCode", customer.ZipCode);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
// get Customer object, takes int customerID parameter
public static Customer GetCustomer(int customerID)
{
SqlConnection connection = MMABooksDB.GetConnection();
// SQL select statement. @ goes in front of variables for SQL
string selectStatement
= "SELECT CustomerID, Name, Address, City, State, ZipCode "
+ "FROM Customers "
+ "WHERE CustomerID = @CustomerID";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
// creates CustomerID parameter to hold value in customerID variable
selectCommand.Parameters.AddWithValue("@CustomerID", customerID);
try
{
connection.Open();
SqlDataReader custReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
//
if (custReader.Read())
{
// note all the casting btwn types!
Customer customer = new Customer();
customer.CustomerID = (int)custReader["CustomerID"];
customer.Name = custReader["Name"].ToString();
customer.Address = custReader["Address"].ToString();
customer.City = custReader["City"].ToString();
customer.State = custReader["State"].ToString();
customer.ZipCode = custReader["ZipCode"].ToString();
custReader.Close();
return(customer);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool AddProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
string insertStatement =
"INSERT Products " +
"(ProductCode, Description, UnitPrice, OnHandQuantity) " +
"VALUES (@ProductCode, @Description, @UnitPrice, @OnHandQuantity)";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@ProductCode", product.ProductCode);
insertCommand.Parameters.AddWithValue(
"@Description", product.Description);
insertCommand.Parameters.AddWithValue(
"@UnitPrice", product.UnitPrice);
insertCommand.Parameters.AddWithValue(
"@OnHandQuantity", product.OnHandQuantity);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT count(*) FROM Products WHERE ProductCode=@ProductCode";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue(
"@ProductCode", product.ProductCode);
int count = Convert.ToInt32(selectCommand.ExecuteScalar());
if (count == 1)
{
return(true);
}
return(false);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <State> GetStates()
{
// declare and instantiate List of States
List <State> states = new List <State>();
// create connection object to connect to database
SqlConnection connection = MMABooksDB.GetConnection();
// string variable of sql select statement
string selectStatement = "SELECT StateCode, StateName "
+ "FROM States "
+ "ORDER BY StateName";
// create a sql command object to execute commands
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
try
{
// establishes connection
connection.Open();
// returns a reader object
SqlDataReader reader = selectCommand.ExecuteReader();
// while the reader is read
while (reader.Read())
{
// State object
State s = new State();
// read the code and name fields (represented as array of chars)
// and convert into a string
s.StateCode = reader["StateCode"].ToString();
s.StateName = reader["StateName"].ToString();
// add State to the states List
states.Add(s);
}
// close data reader
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
// finally block, closes connection after try or catch is executed
finally
{
connection.Close();
}
// return the states List
return(states);
}
public static int AddCustomer(Customer newCust)
{
SqlConnection connection = MMABooksDB.GetConnection();
// SQL query that inserts a new customer with the given parameters. I think
// it's great that we're learning about parameterized queries first, though
// I do hope the book teaches WHY we're doing this. I see a lot of SQL injection
// attacks waiting to happen when browsing GitHub projects. It's a shame.
string statement = @"
INSERT INTO Customers (Name, Address, City, State, ZipCode)
VALUES (@newName, @newAddress, @newCity, @newState, @newZipCode);
";
SqlCommand command = new SqlCommand(statement, connection);
command.Parameters.AddWithValue("@newName", newCust.Name);
command.Parameters.AddWithValue("@newAddress", newCust.Address);
command.Parameters.AddWithValue("@newCity", newCust.City);
command.Parameters.AddWithValue("@newState", newCust.State);
command.Parameters.AddWithValue("@newZipCode", newCust.ZipCode);
try
{
connection.Open();
command.ExecuteNonQuery();
// Acquires the new customer's ID.
string getCustomerID = "SELECT IDENT_CURRENT('Customers') FROM Customers;";
SqlCommand getCustomerIDCommand = new SqlCommand(getCustomerID, connection);
int CustomerID = Convert.ToInt32(getCustomerIDCommand.ExecuteScalar());
return(CustomerID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
// get Product object, takes string productCode parameter
public static Product GetProduct(string productCode)
{
SqlConnection connection = MMABooksDB.GetConnection();
// SQL select statement. @ goes in front of variables for SQL. * = all
string selectStatement
= "SELECT * "
+ "FROM Products "
+ "WHERE ProductCode = @ProductCode";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
// creates ProductCode parameter to hold value in productCode variable
selectCommand.Parameters.AddWithValue("@ProductCode", productCode);
try
{
connection.Open();
SqlDataReader prodReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
//
if (prodReader.Read())
{
// note all the casting btwn types!
Product product = new Product();
product.ProductCode = prodReader["ProductCode"].ToString();
product.Description = prodReader["Description"].ToString();
product.UnitPrice = (decimal)prodReader["UnitPrice"];
product.OnHandQuantity = (int)prodReader["OnHandQuantity"];
prodReader.Close();
return(product);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool DeleteProduct(Product product)
{
// this method requires all parameters to match, in order to handle
// concurrency. this way, if another person edits a record, it won't delete
SqlConnection connection = MMABooksDB.GetConnection();
string deleteStatement =
"DELETE FROM Products " +
"WHERE ProductCode = @ProductCode " +
"AND Description = @Description " +
"AND UnitPrice = @UnitPrice " +
"AND OnHandQuantity = @OnHandQuantity";
SqlCommand deleteCommand =
new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue(
"@ProductCode", product.ProductCode);
deleteCommand.Parameters.AddWithValue(
"@Description", product.Description);
deleteCommand.Parameters.AddWithValue(
"@UnitPrice", product.UnitPrice);
deleteCommand.Parameters.AddWithValue(
"@OnHandQuantity", product.OnHandQuantity);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Customer GetCustomer(int customerID)
{
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement
= "SELECT CustomerID, Name, Address, City, State, ZipCode "
+ "FROM Customers "
+ "WHERE CustomerID = @CustomerID";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@CustomerID", customerID);
try
{
connection.Open();
SqlDataReader custReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (custReader.Read())
{
Customer customer = new Customer();
//Because data tables are not c# data types they need to be cast of tostring to make them useable
customer.CustomerID = (int)custReader["CustomerID"];
customer.Name = custReader["Name"].ToString();
customer.Address = custReader["Address"].ToString();
customer.City = custReader["City"].ToString();
customer.State = custReader["State"].ToString();
customer.ZipCode = custReader["ZipCode"].ToString();
custReader.Close();
return(customer);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool DeleteProduct(Product product)
{
SqlConnection connection = MMABooksDB.GetConnection();
string deleteStatement =
"DELETE FROM Products " +
"WHERE ProductCode = @ProductCode " +
"AND Description = @Description " +
"AND UnitPrice = @UnitPrice " +
"AND OnHandQuantity = @OnHandQuantity ";
SqlCommand deleteCommand =
new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue(
"@ProductCode", product.ProductCode);
deleteCommand.Parameters.AddWithValue(
"@Description", product.Description);
deleteCommand.Parameters.AddWithValue(
"@UnitPrice", product.UnitPrice);
deleteCommand.Parameters.AddWithValue(
"@OnHandQuantity", product.OnHandQuantity);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Product GetProduct(string prodCode)
{
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement
= "SELECT * "
+ "FROM Products "
+ "WHERE ProductCode = @ProductCode";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductCode", prodCode);
try
{
connection.Open();
SqlDataReader prodReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (prodReader.Read())
{
Product product = new Product();
//Because data tables are not c# data types they need to be cast of tostring to make them useable
product.ProductCode = prodReader["ProductCode"].ToString();
product.Description = prodReader["Description"].ToString();
product.UnitPrice = (decimal)prodReader["UnitPrice"];
product.OnHandQuantity = (int)prodReader["OnHandQuantity"];
prodReader.Close();
return(product);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static int AddCustomer(Customer customer)
{
SqlConnection connection = MMABooksDB.GetConnection();
string insertStatement =
"INSERT Customers " +
"(Name, Address, City, State, ZipCode) " +
"VALUES (@Name, @Address, @City, @State, @ZipCode)";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@Name", customer.Name);
insertCommand.Parameters.AddWithValue(
"@Address", customer.Address);
insertCommand.Parameters.AddWithValue(
"@City", customer.City);
insertCommand.Parameters.AddWithValue(
"@State", customer.State);
insertCommand.Parameters.AddWithValue(
"@ZipCode", customer.ZipCode);
try
{
connection.Open();
//Method that is used when query does not return a record set
insertCommand.ExecuteNonQuery();
//
string selectStatement =
"SELECT IDENT_CURRENT('Customers') FROM Customers";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
//executescalar only returns one value which will return the primary key
int customerID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(customerID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Product GetProduct(string ProductCode)
{
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement
= "SELECT ProductCode, Description, UnitPrice, OnHandQuantity "
+ "FROM Products "
+ "WHERE ProductCode = @ProductCode";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductCode", ProductCode);
try
{
connection.Open();
SqlDataReader prodReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (prodReader.Read())
{
Product product = new Product();
product.ProductCode = (string)prodReader["ProductCode"].ToString();
product.Description = (string)prodReader["Description"].ToString();
product.UnitPrice = (decimal)prodReader["UnitPrice"];
product.OnHandQuantity = (int)prodReader["OnHandQuantity"];
return(product);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static int AddCustomer(Customer customer)
{
SqlConnection connection = MMABooksDB.GetConnection();
string insertStatement =
"INSERT Customers " +
"(Name, Address, City, State, ZipCode) " +
"VALUES (@Name, @Address, @City, @State, @ZipCode)";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@Name", customer.Name);
insertCommand.Parameters.AddWithValue(
"@Address", customer.Address);
insertCommand.Parameters.AddWithValue(
"@City", customer.City);
insertCommand.Parameters.AddWithValue(
"@State", customer.State);
insertCommand.Parameters.AddWithValue(
"@ZipCode", customer.ZipCode);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
// IDENT_CURRENT = autonumber customerID
string selectStatement =
"SELECT IDENT_CURRENT('Customers') FROM Customers";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
int customerID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(customerID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool DeleteCustomer(Customer cust)
{
SqlConnection con = MMABooksDB.GetConnection();
string deleteStatement = "DELETE FROM customers " +
"WHERE CustomerID = @CustomerID " + // to identify the customer to be deleted
"AND Name = @Name " + //remaining conditions - to ensure optimistic concurrency
"AND Address = @Address " +
"AND City = @City " +
"AND State = @State " +
"AND ZipCode = @ZipCode";
SqlCommand cmd = new SqlCommand(deleteStatement, con);
cmd.Parameters.AddWithValue("@CustomerID", cust.CustomerID);
cmd.Parameters.AddWithValue("@Name", cust.Name);
cmd.Parameters.AddWithValue("@Address", cust.Address);
cmd.Parameters.AddWithValue("@City", cust.City);
cmd.Parameters.AddWithValue("@State", cust.State);
cmd.Parameters.AddWithValue("@ZipCode", cust.ZipCode);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static bool UpdateCustomer(Customer oldCust, Customer newCust)
{
int count = 0;
using (SqlConnection conn = MMABooksDB.GetConnection())
{
string query =
"UPDATE Customers SET " +
"Name=@newName, " +
"Address=@newAddress, " +
"City=@newCity, " +
"State=@newState, " +
"ZipCode=@newZipCode " +
"Where CustomerID =@oldCustomerID " + //to identify record
"AND Name=@oldName " + // remaining conditions for optimistic concurrency
"AND Address=@oldAddress " +
"AND City=@oldCity " +
"AND State=@oldState " +
"AND ZipCode=@oldZipCode";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@newName", newCust.Name);
cmd.Parameters.AddWithValue("@newAddress", newCust.Address);
cmd.Parameters.AddWithValue("@newCity", newCust.City);
cmd.Parameters.AddWithValue("@newState", newCust.State);
cmd.Parameters.AddWithValue("@newZipCode", newCust.ZipCode);
cmd.Parameters.AddWithValue("@oldCustomerID", oldCust.CustomerID);
cmd.Parameters.AddWithValue("@oldName", oldCust.Name);
cmd.Parameters.AddWithValue("@oldAddress", oldCust.Address);
cmd.Parameters.AddWithValue("@oldCity", oldCust.City);
cmd.Parameters.AddWithValue("@oldState", oldCust.State);
cmd.Parameters.AddWithValue("@oldZipCode", oldCust.ZipCode);
conn.Open();
count = cmd.ExecuteNonQuery(); // returns # of row updated
}
}
return(count > 0);
}