using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

/*=====================================================================

  File:      ReviewsDB.cs for Adventure Works Cycles Storefront Sample
  Summary:   Middle tier component for manipulating product reviews.
  Date:	     June 16, 2003

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */

namespace Microsoft.Samples.SqlServer 
{

    //*******************************************************
    //
    // ReviewsDB Class
    //
    // Business/Data Logic Class that encapsulates all data
    // logic necessary to list/access/add reviews from
    // the AdventureWorks database.
    //
    //*******************************************************

    public class ReviewsDB {

        //*******************************************************
        //
        // ReviewsDB.GetReviews() Method <a name="GetReviews"></a>
        //
        // 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:
        //     + <a href="usp_ReviewsList.htm" style="color:green">usp_ReviewsList Stored Procedure</a>
        //
        //*******************************************************

        public SqlDataReader GetReviews(int productID) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand myCommand = new SqlCommand("usp_ReviewsList", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
            parameterProductID.Value = productID;
            myCommand.Parameters.Add(parameterProductID);

            // Execute the command
            myConnection.Open();
            SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // Return the datareader result
            return result;
        }

        //*******************************************************
        //
        // ReviewsDB.AddReview() Method <a name="AddReview"></a>
        //
        // The AddReview method adds a new review into the
        // AdventureWorks database.
        //
        // Other relevant sources:
        //     + <a href="usp_ReviewsAdd.htm" style="color:green">usp_ReviewsAdd Stored Procedure</a>
        //
        //*******************************************************

        public void AddReview(int productID, string customerName, string customerEmail, int rating, string comments) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand myCommand = new SqlCommand("usp_ReviewsAdd", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
            parameterProductID.Value = productID;
            myCommand.Parameters.Add(parameterProductID);

            SqlParameter parameterCustomerName = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50);
            parameterCustomerName.Value = customerName;
            myCommand.Parameters.Add(parameterCustomerName);

            SqlParameter parameterEmail = new SqlParameter("@CustomerEmail", SqlDbType.NVarChar, 50);
            parameterEmail.Value = customerEmail;
            myCommand.Parameters.Add(parameterEmail);

            SqlParameter parameterRating = new SqlParameter("@Rating", SqlDbType.Int, 4);
            parameterRating.Value = rating;
            myCommand.Parameters.Add(parameterRating);

            SqlParameter parameterComments = new SqlParameter("@Comments", SqlDbType.NVarChar, 3850);
            parameterComments.Value = comments;
            myCommand.Parameters.Add(parameterComments);

            SqlParameter parameterReviewID = new SqlParameter("@ReviewID", SqlDbType.Int, 4);
            parameterReviewID.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameterReviewID);

            // Open the connection and execute the Command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                myConnection.Close();
            }
        }
    }
}