eventsdb.cs

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

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

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

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

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

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

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

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