messagesdal.cs

一个即时通信工具

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    /// <summary>
    /// MessagesDAL类 
    /// </summary>
    public class MessagesDAL : IDisposable
    {
        /// <summary>
        /// 读取指定用户的所有消息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<MessagesInfo> GetMyMessages(int hostId)
        {
            string strSQL = string.Format("SELECT * FROM Messages where ToUserId={0} order by MessageTypeId ", hostId);
            List<MessagesInfo> objItems = new List<MessagesInfo>();

            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
            {
                while (rdr.Read())
                {
                    MessagesInfo objInfo = new MessagesInfo();
                    objInfo.Id = Convert.ToInt32(rdr["Id"]);
                    objInfo.FromUser = new UsersInfo(Convert.ToInt32(rdr["FromUserId"]));
                    objInfo.ToUser = new UsersInfo(Convert.ToInt32(rdr["ToUserId"]));
                    objInfo.Message = rdr["Message"].ToString();
                    objInfo.MessageType = new MessageTypeInfo(Convert.ToInt32(rdr["MessageTypeId"]));
                    objInfo.MessageState = Convert.ToInt32(rdr["MessageState"]);
                    objInfo.MessageTime = Convert.ToDateTime(rdr["MessageTime"]);
                    objItems.Add(objInfo);
                }
            }

            return objItems;
        }

        /// <summary>
        /// 读取指定用户的所有消息(未读)
        /// </summary>
        /// <returns></returns>
        public List<MessagesInfo> GetMyNotReadMessages(int hostId)
        {
            string strSQL = string.Format("SELECT * FROM Messages where ToUserId={0}  and MessageState={1} order by MessageTypeId", hostId, (int)EnumMessageState.NotRead);
            List<MessagesInfo> objItems = new List<MessagesInfo>();
            UsersDAL udal = new UsersDAL();
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
            {
                while (rdr.Read())
                {
                    MessagesInfo objInfo = new MessagesInfo();
                    objInfo.Id = Convert.ToInt32(rdr["Id"]);
                    objInfo.FromUser = udal.Select(Convert.ToInt32(rdr["FromUserId"]));
                    objInfo.ToUser = new UsersInfo(Convert.ToInt32(rdr["ToUserId"]));
                    objInfo.Message = rdr["Message"].ToString();
                    objInfo.MessageType = new MessageTypeInfo(Convert.ToInt32(rdr["MessageTypeId"]));
                    objInfo.MessageState = Convert.ToInt32(rdr["MessageState"]);
                    objInfo.MessageTime = Convert.ToDateTime(rdr["MessageTime"]);
                    objItems.Add(objInfo);
                }
            }

            return objItems;

            //  return GetMyNotReadMessages(hostId, null);
        }

        /// <summary>
        /// 读取指定用户的所有消息(未读)
        /// </summary>
        /// <returns></returns>
        public List<MessagesInfo> GetMyNotReadMessages(int hostId, EnumMessageType messageType)
        {

            string strSQL = string.Format("SELECT * FROM Messages where ToUserId={0}  and MessageState={1} and MessageTypeId ={2}", hostId, (int)EnumMessageState.NotRead, (int)messageType);


            List<MessagesInfo> objItems = new List<MessagesInfo>();
            UsersDAL udal = new UsersDAL();
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
            {
                while (rdr.Read())
                {
                    MessagesInfo objInfo = new MessagesInfo();
                    objInfo.Id = Convert.ToInt32(rdr["Id"]);
                    objInfo.FromUser = udal.Select(Convert.ToInt32(rdr["FromUserId"]));
                    objInfo.ToUser = new UsersInfo(Convert.ToInt32(rdr["ToUserId"]));
                    objInfo.Message = rdr["Message"].ToString();
                    objInfo.MessageType = new MessageTypeInfo(Convert.ToInt32(rdr["MessageTypeId"]));
                    objInfo.MessageState = Convert.ToInt32(rdr["MessageState"]);
                    objInfo.MessageTime = Convert.ToDateTime(rdr["MessageTime"]);
                    objItems.Add(objInfo);
                }
            }

            return objItems;
        }



        /// <summary>
        /// 增加消息记录
        /// </summary>
        /// <param name="entity"></param>
        public void Insert(MessagesInfo entity)
        {
            string strSQL = "INSERT INTO [Messages](FromUserId,ToUserId,Message,MessageTypeId,MessageState,MessageTime) VALUES(@FromUserId,@ToUserId,@Message,@MessageTypeId,@MessageState,getdate()) ";

            SqlParameter[] pars = new SqlParameter[]{
                    new SqlParameter("@FromUserId",entity.FromUser.Id),
                    new SqlParameter("@ToUserId",entity.ToUser.Id),
                    new SqlParameter("@Message",entity.Message),
                    new SqlParameter("@MessageTypeId",entity.MessageType.Id),
                    new SqlParameter("@MessageState",(int)EnumMessageState.NotRead)//表示未读
            };

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, pars);

        }

        /// <summary>
        ///  修改消息状态
        /// </summary>
        /// <param name="hostId"></param>
        public void Update(int id, int messageState)
        {
            string strSQL = string.Format("UPDATE  Messages SET MessageState={0} WHERE Id={1}", messageState, id);
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, null);
        }

        /// <summary>
        ///  删除指定的消息
        /// </summary>
        /// <param name="hostId"></param>
        public void Delete(int id)
        {
            string strSQL = "DELETE FROM Messages WHERE Id=" + id;
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, null);
        }

        #region IDisposable 成员
        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }
        #endregion

    }
}