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

    namespace ASPNET.StarterKit.Portal {

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

        public class AnnouncementsDB {

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

            public DataSet GetAnnouncements(int moduleId) {

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

            //*********************************************************************
            //
            // GetSingleAnnouncement Method
            //
            // The GetSingleAnnouncement method returns a SqlDataReader containing details
            // about a specific announcement from the Announcements database table.
            //
            // Other relevant sources:
            //     + GetSingleAnnouncement Stored Procedure
            //
            //*********************************************************************

            public SqlDataReader GetSingleAnnouncement(int itemId) {

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

            //*********************************************************************
            //
            // DeleteAnnouncement Method
            //
            // The DeleteAnnouncement method deletes the specified announcement from
            // the Announcements database table.
            //
            // Other relevant sources:
            //     + DeleteAnnouncement Stored Procedure
            //
            //*********************************************************************

            public void DeleteAnnouncement(int itemID) {

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

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

            //*********************************************************************
            //
            // AddAnnouncement Method
            //
            // The AddAnnouncement method adds a new announcement to the
            // Announcements database table, and returns the ItemId value as a result.
            //
            // Other relevant sources:
            //     + AddAnnouncement Stored Procedure
            //
            //*********************************************************************

            public int AddAnnouncement(int moduleId, int itemId, String userName, String title, DateTime expireDate, String description, String moreLink, String mobileMoreLink) {

                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_AddAnnouncement", 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, 150);
                parameterTitle.Value = title;
                myCommand.Parameters.Add(parameterTitle);

                SqlParameter parameterMoreLink = new SqlParameter("@MoreLink", SqlDbType.NVarChar, 150);
                parameterMoreLink.Value = moreLink;
                myCommand.Parameters.Add(parameterMoreLink);

                SqlParameter parameterMobileMoreLink = new SqlParameter("@MobileMoreLink", SqlDbType.NVarChar, 150);
                parameterMobileMoreLink.Value = mobileMoreLink;
                myCommand.Parameters.Add(parameterMobileMoreLink);

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

                return (int)parameterItemID.Value;
            }

            //*********************************************************************
            //
            // UpdateAnnouncement Method
            //
            // The UpdateAnnouncement method updates the specified announcement within
            // the Announcements database table.
            //
            // Other relevant sources:
            //     + UpdateAnnouncement Stored Procedure
            //
            //*********************************************************************

            public void UpdateAnnouncement(int moduleId, int itemId, String userName, String title, DateTime expireDate, String description, String moreLink, String mobileMoreLink) {

                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_UpdateAnnouncement", 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, 150);
                parameterTitle.Value = title;
                myCommand.Parameters.Add(parameterTitle);

                SqlParameter parameterMoreLink = new SqlParameter("@MoreLink", SqlDbType.NVarChar, 150);
                parameterMoreLink.Value = moreLink;
                myCommand.Parameters.Add(parameterMoreLink);

                SqlParameter parameterMobileMoreLink = new SqlParameter("@MobileMoreLink", SqlDbType.NVarChar, 150);
                parameterMobileMoreLink.Value = mobileMoreLink;
                myCommand.Parameters.Add(parameterMobileMoreLink);

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