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

    namespace ASPNET.StarterKit.Portal {

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

        public class EventsDB {

            //*********************************************************************
            //
            // GetEvents Method
            //
            // The GetEvents method returns a DataSet containing all of the
            // events for a specific portal module from the events
            // database.
            //
            // NOTE: A DataSet is returned from this method to allow this method to support
            // both desktop and mobile Web UI.
            //
            // Other relevant sources:
            //     + GetEvents Stored Procedure
            //
            //*********************************************************************

            public DataSet GetEvents(int moduleId) {

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

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

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

                // Create and Fill the DataSet
                DataSet myDataSet = new DataSet();
                myCommand.Fill(myDataSet);

                // Return the DataSet
                return myDataSet;
            }

            //*********************************************************************
            //
            // GetSingleEvent Method
            //
            // The GetSingleEvent method returns a SqlDataReader containing details
            // about a specific event from the events database.
            //
            // Other relevant sources:
            //     + GetSingleEvent Stored Procedure
            //
            //*********************************************************************

            public SqlDataReader GetSingleEvent(int itemId) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_GetSingleEvent", 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;
            }

            //*********************************************************************
            //
            // DeleteEvent Method
            //
            // The DeleteEvent method deletes a specified event from
            // the events database.
            //
            // Other relevant sources:
            //     + DeleteEvent Stored Procedure
            //
            //*********************************************************************

            public void DeleteEvent(int itemID) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
                SqlCommand myCommand = new SqlCommand("Portal_DeleteEvent", 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);

                // Open the database connection and execute SQL Command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }

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

            public int AddEvent(int moduleId, int itemId, String userName, String title, DateTime expireDate, String description, String wherewhen) {

                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_AddEvent", 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 parameterModuleID = new SqlParameter("@ModuleID", SqlDbType.Int, 4);
                parameterModuleID.Value = moduleId;
                myCommand.Parameters.Add(parameterModuleID);

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

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

                SqlParameter parameterWhereWhen = new SqlParameter("@WhereWhen", SqlDbType.NVarChar, 100);
                parameterWhereWhen.Value = wherewhen;
                myCommand.Parameters.Add(parameterWhereWhen);

                SqlParameter parameterExpireDate = new SqlParameter("@ExpireDate", SqlDbType.DateTime, 8);
                parameterExpireDate.Value = expireDate;
                myCommand.Parameters.Add(parameterExpireDate);

                SqlParameter parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 2000);
                parameterDescription.Value = description;
                myCommand.Parameters.Add(parameterDescription);

                // Open the database connection and execute SQL Command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();

                // Return the new Event ItemID
                return (int)parameterItemID.Value;
            }

            //*********************************************************************
            //
            // UpdateEvent Method
            //
            // The UpdateEvent method updates the specified event within
            // the Events database table.
            //
            // Other relevant sources:
            //     + UpdateEvent Stored Procedure
            //
            //*********************************************************************

            public void UpdateEvent(int moduleId, int itemId, String userName, String title, DateTime expireDate, String description, String wherewhen) {

                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_UpdateEvent", 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);

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

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

                SqlParameter parameterWhereWhen = new SqlParameter("@WhereWhen", SqlDbType.NVarChar, 100);
                parameterWhereWhen.Value = wherewhen;
                myCommand.Parameters.Add(parameterWhereWhen);

                SqlParameter parameterExpireDate = new SqlParameter("@ExpireDate", SqlDbType.DateTime, 8);
                parameterExpireDate.Value = expireDate;
                myCommand.Parameters.Add(parameterExpireDate);

                SqlParameter parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 2000);
                parameterDescription.Value = description;
                myCommand.Parameters.Add(parameterDescription);

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