using System; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace ASPNetPortal { //********************************************************************* // // 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("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("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("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("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("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(); } } }