lwordtask.cs

随便上传一文章 随便上传一文章 随便上传一文章 随便上传一文章

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;	// 需要访问 MS SQL Server 数据库
using System.Web;

using TraceLWord7.Classes;		// 引用实体规范层
using TraceLWord7.DbTask;		// 引用行为规范层

namespace TraceLWord7.SqlServerTask
{
	/// <summary>
	/// LWordTask 留言板任务类
	/// </summary>
	public class LWordTask : ILWordTask
	{
		// 数据库连接字符串
		private const string DB_CONN=@"Server=127.0.0.1; uid=sa; pwd=; DataBase=TraceLWordDb";

		/// <summary>
		/// 读取 LWord 数据表，返回留言对象数组
		/// </summary>
		/// <returns></returns>
		public LWord[] ListLWord()
		{
			// 留言对象集合
			ArrayList lwordList=new ArrayList();

			string cmdText="SELECT * FROM [LWord] ORDER BY [LWordID] DESC";

			SqlConnection dbConn=new SqlConnection(DB_CONN);
			SqlCommand dbCmd=new SqlCommand(cmdText, dbConn);

			try
			{
				dbConn.Open();
				SqlDataReader dr=dbCmd.ExecuteReader();

				while(dr.Read())
				{
					LWord lword=PutObjectProperty(new LWord(), dr);
					lwordList.Add(lword);
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				dbConn.Close();
			}

			// 将集合转型为数组并返回给调用者
			return (LWord[])lwordList.ToArray(typeof(TraceLWord7.Classes.LWord));
		}

		/// <summary>
		/// 获取指定时间段的留言信息
		/// </summary>
		/// <param name="startTime">起始时间</param>
		/// <param name="endTime">结束时间</param>
		/// <returns></returns>
		public LWord[] ListLWord(DateTime startTime, DateTime endTime)
		{
			// 留言对象集合
			ArrayList lwordList=new ArrayList();

			string cmdText="";

			cmdText+="SELECT * FROM [LWord] WHERE [PostTime] BETWEEN ( @StartTime AND @EndTime ) ";
			cmdText+="ORDER BY [LWordID] DESC";

			SqlConnection dbConn=new SqlConnection(DB_CONN);
			SqlCommand dbCmd=new SqlCommand(cmdText, dbConn);

			// 设置起始时间
			dbCmd.Parameters.Add(new SqlParameter("@StartTime", SqlDbType.DateTime));
			dbCmd.Parameters["@StartTime"].Value=startTime;

			// 设置结束时间
			dbCmd.Parameters.Add(new SqlParameter("@EndTime", SqlDbType.DateTime));
			dbCmd.Parameters["@EndTime"].Value=endTime;

			try
			{
				dbConn.Open();
				SqlDataReader dr=dbCmd.ExecuteReader();

				while(dr.Read())
				{
					LWord lword=PutObjectProperty(new LWord(), dr);
					lwordList.Add(lword);
				}
			}
			catch
			{
				throw;
			}
			finally
			{
				dbConn.Close();
			}

			// 将集合转型为数组并返回给调用者
			return (LWord[])lwordList.ToArray(typeof(TraceLWord7.Classes.LWord));
		}

		/// <summary>
		/// 发送留言信息到数据库
		/// </summary>
		/// <param name="newLWord">留言对象</param>
		public void PostLWord(LWord newLWord)
		{
			// 留言内容不能为空
			if(newLWord==null || newLWord.TextContent==null || newLWord.TextContent=="")
				throw new Exception("留言内容为空");

			string cmdText="INSERT INTO [LWord]([TextContent]) VALUES(@TextContent)";

			SqlConnection dbConn=new SqlConnection(DB_CONN);
			SqlCommand dbCmd=new SqlCommand(cmdText, dbConn);

			// 设置留言内容
			dbCmd.Parameters.Add(new SqlParameter("@TextContent", SqlDbType.NText));
			dbCmd.Parameters["@TextContent"].Value=newLWord.TextContent;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			catch
			{
				throw;
			}
			finally
			{
				dbConn.Close();
			}
		}

		#region PutObjectProperty 读取数据源数据，并设置到对象属性
		/// <summary>
		/// 读取数据源数据，并设置到对象属性
		/// </summary>
		/// <param name="lword">留言对象</param>
		/// <param name="dr">数据源</param>
		/// <returns></returns>
		public static LWord PutObjectProperty(LWord lword, SqlDataReader dr)
		{
			if(lword==null || dr==null)
				return lword;

			// 设置留言编号
			lword.UniqueID=(int)dr["LWordID"];
			// 留言内容
			lword.TextContent=(string)dr["TextContent"];
			// 发送时间
			lword.PostTime=(DateTime)dr["PostTime"];

			return lword;
		}
		#endregion
	}
}