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