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(); } }