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();
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 Customer GetCustomer(int customerID)
{
Customer cust = null;
//Connection
SqlConnection con = MMABooksDB.GetConnection(); //start with the connection
string selectStatement = "SELECT CustomerID, Name, Address, City, State, ZipCode " + //make sure there is a space after zipcode
"FROM Customers " +
"WHERE CustomerID = @CustomerID";
//Command
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@CustomerID", customerID); // parameter, value. (value comes from the method's argument
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
//process it
if (reader.Read()) //means we found a customer
{
cust = new Customer(); //create new customer object
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 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 productReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (productReader.Read())
{
Product product = new Product();
product.ProductCode = (string)productReader["ProductCode"].ToString();
product.Description = productReader["Description"].ToString();
product.UnitPrice = (decimal)productReader["UnitPrice"];
product.OnHandQuantity = (int)productReader["OnHandQuantity"];
return(product);
}
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 " +
"Name = @Name " +
"AND Address = @Address " +
"AND City = @City " +
"AND State = @State " +
"AND ZipCode = @ZipCode";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
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
{
int count = 0;
connection.Open();
count = deleteCommand.ExecuteNonQuery();
return((count > 0) ? true : false);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.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);
}
public static int AddCustomer(Customer cust)
{
int custID = -1;
using (SqlConnection conn = MMABooksDB.GetConnection())
{
string query = "INSERT INTO Customers(Name,Address,City,State,ZipCode) " +
"OUTPUT inserted.CustomerID " +
"Values(@Name,@Address,@City,@State,@ZipCode) ";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
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();
custID = (int)cmd.ExecuteScalar();
}
}
return(custID);
}
public static Product GetProduct(string prodCode)
{
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement
= "SELECT * "
+ "FROM Products "
+ "WHERE ProductCode = @ProdcutCode";
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();
product.ProductCode = prodReader["ProductCode"].ToString();
product.Description = prodReader["Description"].ToString();
product.UnitPrice = (decimal)prodReader["UnitPrice"];
product.ProductCode = prodReader["ProductCode"].ToString();
prodReader.Close();
return(product);
}
else
{
return(null);
}
}
catch
{
}
}
public static bool UpdateCustomer(Customer oldCust, Customer newCust)
{
SqlConnection connection = MMABooksDB.GetConnection();
// This is a 'verbatim' string literal, which appears to be quite
// similar to a template string in JavaScript. I like it.
string statement = @"
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 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);
command.Parameters.AddWithValue("@oldCustomerID", oldCust.CustomerID);
command.Parameters.AddWithValue("@oldName", oldCust.Name);
command.Parameters.AddWithValue("@oldAddress", oldCust.Address);
command.Parameters.AddWithValue("@oldCity", oldCust.City);
command.Parameters.AddWithValue("@oldState", oldCust.State);
command.Parameters.AddWithValue("@oldZipCode", oldCust.ZipCode);
try
{
connection.Open();
int rowCount = command.ExecuteNonQuery();
if (rowCount > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
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 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(
"@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();
}
}