public static bool UpdateProduct(product oldProduct, product newProduct) { //There is an old and new product here. Basically this means that the original value //when the method runs for each properties is stored into an object oldproduct. //the values changed by the user will go into newProduct. //Cross Check that the old values match in the database prior to changing with the new values. SqlConnection conn = BooksDB.GetConnection(); string updateStatement = "UPDATE Products SET " + "Description = @NewDescription, " + "UnitPrice = @NewPrice " + "WHERE ProductCode = @OldCode " + "AND Description = @OldDescription " + "AND UnitPrice = @OldPrice"; SqlCommand cmd = new SqlCommand(updateStatement, conn); //create paramaters and set to value cmd.Parameters.AddWithValue("@NewDescription", newProduct.Description); cmd.Parameters.AddWithValue("@NewPrice", newProduct.Price); cmd.Parameters.AddWithValue("@OldCode", oldProduct.Code); cmd.Parameters.AddWithValue("@OldDescription", oldProduct.Description); cmd.Parameters.AddWithValue("@OldPrice", oldProduct.Price); try { //open connection to the database conn.Open(); //execute query and store either 0 for failed or 1 for updated int count = cmd.ExecuteNonQuery(); //if the update happens then return a true otherwise false. if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { //throw sql error. throw ex; } finally { //close connection to the database conn.Close(); } }
public static product GetProduct(string code) { //establish connection string to database SqlConnection conn = BooksDB.GetConnection(); //select statement string strSel = "SELECT ProductCode, Description, UnitPrice " + "FROM Products " + "WHERE ProductCode = @Code"; // set database connection and select statement to command. SqlCommand cmd = new SqlCommand(strSel, conn); //Declare Parameter cmd.Parameters.AddWithValue("@Code", code); try { //open database conn.Open(); //data reader is used to get select statement. SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow); //if there are results if (rdr.Read()) { product p = new product(); p.Code = rdr["ProductCode"].ToString(); p.Description = rdr["Description"].ToString(); p.Price = (decimal)rdr["UnitPrice"]; return(p); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { //Whether failed or succeeded shut down database connection. conn.Close(); } }
public static bool AddProduct(product product) { //Link Connection to conn object SqlConnection conn = BooksDB.GetConnection(); //set string insertstatement to equal the query syntax string insertStatement = "INSERT INTO Products " + "(ProductCode, Description, UnitPrice) " + "Values (@Code, @Description, @Price)"; //Declare Command and link query and connection SqlCommand cmd = new SqlCommand(insertStatement, conn); //declare all paramaters cmd.Parameters.AddWithValue("@Code", product.Code); cmd.Parameters.AddWithValue("@Description", product.Description); cmd.Parameters.AddWithValue("Price", product.Price); try { //open connection to database conn.Open(); //execute insert statement int count = cmd.ExecuteNonQuery(); //if record was inserted return a true value if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { conn.Close(); } }
public static bool DeleteProduct(product product) { //get connection string SqlConnection conn = BooksDB.GetConnection(); //Set delete statement to equal the delete query string deleteStatement = "DELETE FROM Products " + "WHERE ProductCode = @Code " + "AND Description = @Description " + "AND UnitPrice = @Price "; //set cmd to have query and connection SqlCommand cmd = new SqlCommand(deleteStatement, conn); //declare parameters for the delete query. cmd.Parameters.AddWithValue("@Code", product.Code); cmd.Parameters.AddWithValue("@Description", product.Description); cmd.Parameters.AddWithValue("@Price", product.Price); try { //open the database conn.Open(); //execute query int count = cmd.ExecuteNonQuery(); //if row was deleted then return true otherwise false. if (count > 0) { return(true); } return(false); } catch (SqlException ex) { //throw sql error throw ex; } finally { //close connection to the database conn.Close(); } }