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 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 state = new State();
state.StateCode = reader["StateCode"].ToString();
state.StateName = reader["StateName"].ToString();
states.Add(state);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(states);
}
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();
}
}
