announcementsdb.cs

三层架构的.net源码三层架构的.net源码

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

namespace MyStarterKit.Portal.Web
{
	/// <summary>
	/// AnnouncementsDB 的摘要说明。
	/// </summary>
	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:
		//     + <a href="GetAnnouncements.htm" style="color:green">GetAnnouncements Stored Procedure</a>
		//
		//*********************************************************************

		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:
		//     + <a href="GetSingleAnnouncement.htm" style="color:green">GetSingleAnnouncement Stored Procedure</a>
		//
		//*********************************************************************

		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:
		//     + <a href="DeleteAnnouncement.htm" style="color:green">DeleteAnnouncement Stored Procedure</a>
		//
		//*********************************************************************

		/// <summary>
		/// 删除公告
		/// </summary>
		/// <param name="itemID"></param>
		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:
		//     + <a href="AddAnnouncement.htm" style="color:green">AddAnnouncement Stored Procedure</a>
		//
		//*********************************************************************

		/// <summary>
		/// 添加新公告
		/// </summary>
		/// <param name="moduleId"></param>
		/// <param name="itemId"></param>
		/// <param name="userName"></param>
		/// <param name="title"></param>
		/// <param name="expireDate"></param>
		/// <param name="description"></param>
		/// <param name="moreLink"></param>
		/// <param name="mobileMoreLink"></param>
		/// <returns></returns>
		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:
		//     + <a href="UpdateAnnouncement.htm" style="color:green">UpdateAnnouncement Stored Procedure</a>
		//
		//*********************************************************************

		/// <summary>
		/// 更新公告
		/// </summary>
		/// <param name="moduleId"></param>
		/// <param name="itemId"></param>
		/// <param name="userName"></param>
		/// <param name="title"></param>
		/// <param name="expireDate"></param>
		/// <param name="description"></param>
		/// <param name="moreLink"></param>
		/// <param name="mobileMoreLink"></param>
		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();
		}
	}
}