import System; import System.Configuration; import System.Data; import System.Data.SqlClient; package IBuySpy { //******************************************************* // // ReviewsDB Class // // Business/Data Logic Class that encapsulates all data // logic necessary to list/access/add reviews from // the IBuySpy Reviews database. // //******************************************************* public class ReviewsDB { //******************************************************* // // ReviewsDB.GetReviews() Method // // The GetReviews method returns a struct containing // a forward-only, read-only DataReader. This displays a list of all // user-submitted reviews for a specified product. // The SQLDataReaderResult struct also returns the SQL connection, // which must be explicitly closed after the data from the DataReader // is bound into the controls. // // Other relevant sources: // + ReviewsList Stored Procedure // //******************************************************* public function GetReviews(productID : int) : SqlDataReader { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ReviewsList", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterProductID : SqlParameter = new SqlParameter("@ProductID", SqlDbType.Int, 4); parameterProductID.Value = productID; myCommand.Parameters.Add(parameterProductID); // Execute the command myConnection.Open(); var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader result return result; } //******************************************************* // // ReviewsDB.AddReview() Method // // The AddReview method adds a new review into the // IBuySpy Reviews database. // // Other relevant sources: // + ReviewsAdd Stored Procedure // //******************************************************* public function AddReview(productID: int, customerName: String, customerEmail: String, rating: int, comments: String) : void { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("ReviewsAdd", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterProductID : SqlParameter = new SqlParameter("@ProductID", SqlDbType.Int, 4); parameterProductID.Value = productID; myCommand.Parameters.Add(parameterProductID); var parameterCustomerName : SqlParameter = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50); parameterCustomerName.Value = customerName; myCommand.Parameters.Add(parameterCustomerName); var parameterEmail : SqlParameter = new SqlParameter("@CustomerEmail", SqlDbType.NVarChar, 50); parameterEmail.Value = customerEmail; myCommand.Parameters.Add(parameterEmail); var parameterRating : SqlParameter = new SqlParameter("@Rating", SqlDbType.Int, 4); parameterRating.Value = rating; myCommand.Parameters.Add(parameterRating); var parameterComments : SqlParameter = new SqlParameter("@Comments", SqlDbType.NVarChar, 3850); parameterComments.Value = comments; myCommand.Parameters.Add(parameterComments); var parameterReviewID : SqlParameter = new SqlParameter("@ReviewID", SqlDbType.Int, 4); parameterReviewID.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterReviewID); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } } }