discussiondb.cs

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

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

namespace MyStarterKit.Portal.Web
{
	/// <summary>
	/// DiscussionDB 的摘要说明。
	/// </summary>
	public class DiscussionDB
	{
		//*******************************************************
		//
		// GetTopLevelMessages Method
		//
		// Returns details for all of the messages in the discussion specified by ModuleID.
		//
		// Other relevant sources:
		//     + <a href="GetTopLevelMessages.htm" style="color:green">GetTopLevelMessages Stored Procedure</a>
		//
		//*******************************************************

		public SqlDataReader GetTopLevelMessages(int moduleId) 
		{

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

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

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

			// Execute the command
			myConnection.Open();
			SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			
			// Return the datareader 
			return result;
		}

		//*******************************************************
		//
		// GetThreadMessages Method
		//
		// Returns details for all of the messages the thread, as identified by the Parent id string.
		//
		// Other relevant sources:
		//     + <a href="GetThreadMessages.htm" style="color:green">GetThreadMessages Stored Procedure</a>
		//
		//*******************************************************

		public SqlDataReader GetThreadMessages(String parent) 
		{

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

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

			// Add Parameters to SPROC
			SqlParameter parameterParent = new SqlParameter("@Parent", SqlDbType.NVarChar, 750);
			parameterParent.Value = parent;
			myCommand.Parameters.Add(parameterParent);

			// Execute the command
			myConnection.Open();
			SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			
			// Return the datareader 
			return result;
		}

		//*******************************************************
		//
		// GetSingleMessage Method
		//
		// The GetSingleMessage method returns the details for the message
		// specified by the itemId parameter.
		//
		// Other relevant sources:
		//     + <a href="GetSingleMessage.htm" style="color:green">GetSingleMessage Stored Procedure</a>
		//
		//*******************************************************

		public SqlDataReader GetSingleMessage(int itemId) 
		{

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

		//*********************************************************************
		//
		// AddMessage Method
		//
		// The AddMessage method adds a new message within the
		// Discussions database table, and returns ItemID value as a result.
		//
		// Other relevant sources:
		//     + <a href="AddMessage.htm" style="color:green">AddMessage Stored Procedure</a>
		//
		//*********************************************************************

		public int AddMessage(int moduleId, int parentId, String userName, String title, String body) 
		{

			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_AddMessage", 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 parameterTitle = new SqlParameter("@Title", SqlDbType.NVarChar, 100);
			parameterTitle.Value = title;
			myCommand.Parameters.Add(parameterTitle);

			SqlParameter parameterBody = new SqlParameter("@Body", SqlDbType.NVarChar, 3000);
			parameterBody.Value = body;
			myCommand.Parameters.Add(parameterBody);

			SqlParameter parameterParentID = new SqlParameter("@ParentID", SqlDbType.Int, 4);
			parameterParentID.Value = parentId;
			myCommand.Parameters.Add(parameterParentID);

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

			SqlParameter parameterModuleID = new SqlParameter("@ModuleID", SqlDbType.Int, 4);
			parameterModuleID.Value = moduleId;
			myCommand.Parameters.Add(parameterModuleID);

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

			return (int) parameterItemID.Value;
		}
	}
}