    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Web;
    using ASPNET.StarterKit.Portal;

    namespace ASPNET.StarterKit.Portal {

        //*********************************************************************
        //
        // DiscussionDB Class
        //
        // Class that encapsulates all data logic necessary to add/query/delete
        // discussions within the Portal database.
        //
        //*********************************************************************

        public class DiscussionDB {

            //*******************************************************
            //
            // GetTopLevelMessages Method
            //
            // Returns details for all of the messages in the discussion specified by ModuleID.
            //
            // Other relevant sources:
            //     + GetTopLevelMessages Stored Procedure
            //
            //*******************************************************

            public SqlDataReader GetTopLevelMessages(int moduleId) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_GetTopLevelMessages", myConnection);

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

                // Add Parameters to SPROC
                SqlParameter parameterModuleId = new SqlParameter("@ModuleID", SqlDbType.Int, 4);
                parameterModuleId.Value = moduleId;
                myCommand.Parameters.Add(parameterModuleId);

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

                // Return the datareader
                return result;
            }

            //*******************************************************
            //
            // GetThreadMessages Method
            //
            // Returns details for all of the messages the thread, as identified by the Parent id string.
            //
            // Other relevant sources:
            //     + GetThreadMessages Stored Procedure
            //
            //*******************************************************

            public SqlDataReader GetThreadMessages(String parent) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_GetThreadMessages", myConnection);

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

                // Add Parameters to SPROC
                SqlParameter parameterParent = new SqlParameter("@Parent", SqlDbType.NVarChar, 750);
                parameterParent.Value = parent;
                myCommand.Parameters.Add(parameterParent);

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

                // Return the datareader
                return result;
            }

            //*******************************************************
            //
            // GetSingleMessage Method
            //
            // The GetSingleMessage method returns the details for the message
            // specified by the itemId parameter.
            //
            // Other relevant sources:
            //     + GetSingleMessage Stored Procedure
            //
            //*******************************************************

            public SqlDataReader GetSingleMessage(int itemId) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_GetSingleMessage", myConnection);

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

                // Add Parameters to SPROC
                SqlParameter parameterItemId = new SqlParameter("@ItemID", SqlDbType.Int, 4);
                parameterItemId.Value = itemId;
                myCommand.Parameters.Add(parameterItemId);

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

                // Return the datareader
                return result;
            }

            //*********************************************************************
            //
            // AddMessage Method
            //
            // The AddMessage method adds a new message within the
            // Discussions database table, and returns ItemID value as a result.
            //
            // Other relevant sources:
            //     + AddMessage Stored Procedure
            //
            //*********************************************************************

            public int AddMessage(int moduleId, int parentId, String userName, String title, String body) {

                if (userName.Length < 1) {
                    userName = "unknown";
                }

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_AddMessage", myConnection);

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

                // Add Parameters to SPROC
                SqlParameter parameterItemID = new SqlParameter("@ItemID", SqlDbType.Int, 4);
                parameterItemID.Direction = ParameterDirection.Output;
                myCommand.Parameters.Add(parameterItemID);

                SqlParameter parameterTitle = new SqlParameter("@Title", SqlDbType.NVarChar, 100);
                parameterTitle.Value = title;
                myCommand.Parameters.Add(parameterTitle);

                SqlParameter parameterBody = new SqlParameter("@Body", SqlDbType.NVarChar, 3000);
                parameterBody.Value = body;
                myCommand.Parameters.Add(parameterBody);

                SqlParameter parameterParentID = new SqlParameter("@ParentID", SqlDbType.Int, 4);
                parameterParentID.Value = parentId;
                myCommand.Parameters.Add(parameterParentID);

                SqlParameter parameterUserName = new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
                parameterUserName.Value = userName;
                myCommand.Parameters.Add(parameterUserName);

                SqlParameter parameterModuleID = new SqlParameter("@ModuleID", SqlDbType.Int, 4);
                parameterModuleID.Value = moduleId;
                myCommand.Parameters.Add(parameterModuleID);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();

                return (int) parameterItemID.Value;
            }
        }
    }