usersdal.cs

一个即时通信工具

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

namespace DAL
{
    /// <summary>
    /// UsersDAL类 
    /// </summary>
    public class UsersDAL : IDisposable
    {

        public UsersInfo Select(int id)
        {
            string strSQL = "SELECT * FROM Users where id="+id;
            UsersInfo objInfo = new UsersInfo();

            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
            {
                if (rdr.Read())
                {
                    objInfo.Id = Convert.ToInt32(rdr["Id"]);
                    objInfo.LoginPwd = rdr["LoginPwd"].ToString();
                    objInfo.FriendshipPolicy =new FriendshipPolicyInfo(Convert.ToInt32(rdr["FriendshipPolicyId"]));
                    objInfo.NickName = rdr["NickName"].ToString();
                    objInfo.FaceId = Convert.ToInt32(rdr["FaceId"]);
                    objInfo.Sex = rdr["Sex"].ToString();
                    objInfo.Age = Convert.ToInt32(rdr["Age"]);
                    objInfo.Name = rdr["Name"].ToString();
                    objInfo.Star= new StarInfo(Convert.ToInt32(rdr["StarId"]));
                    objInfo.BloodType= new BloodTypeInfo(Convert.ToInt32(rdr["BloodTypeId"]));
                }
            }

            return objInfo;
        }

        public List<UsersInfo> SelectAll()
        {
            string strSQL = "SELECT * FROM Users";
            List<UsersInfo> objItems = new List<UsersInfo>();

            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
            {
                while (rdr.Read())
                {
                    UsersInfo objInfo = new UsersInfo();
                    objInfo.Id = Convert.ToInt32(rdr["Id"]);
                    objInfo.LoginPwd = rdr["LoginPwd"].ToString();
                    objInfo.FriendshipPolicy = new FriendshipPolicyInfo(Convert.ToInt32(rdr["FriendshipPolicyId"]));
                    objInfo.NickName = rdr["NickName"].ToString();
                    objInfo.FaceId = Convert.ToInt32(rdr["FaceId"]);
                    objInfo.Sex = rdr["Sex"].ToString();
                    objInfo.Age = Convert.ToInt32(rdr["Age"]);
                    objInfo.Name = rdr["Name"].ToString();
                    objInfo.Star = new StarInfo(Convert.ToInt32(rdr["StarId"]));
                    objInfo.BloodType = new BloodTypeInfo(Convert.ToInt32(rdr["BloodTypeId"]));
                    objItems.Add(objInfo);
                }
            }

            return objItems;
        }     

        public void Insert(UsersInfo entity)
        {
            string strSQL = "INSERT INTO [Users](LoginPwd,FriendshipPolicyId,NickName,FaceId,Sex,Age,Name,StarId,BloodTypeId) VALUES(@LoginPwd,@FriendshipPolicyId,@NickName,@FaceId,@Sex,@Age,@Name,@StarId,@BloodTypeId) ";

            SqlParameter[] pars = new SqlParameter[]{
                    new SqlParameter("@LoginPwd",entity.LoginPwd),
                    new SqlParameter("@FriendshipPolicyId",entity.FriendshipPolicy.Id),
                    new SqlParameter("@NickName",entity.NickName),
                    new SqlParameter("@FaceId",entity.FaceId),
                    new SqlParameter("@Sex",entity.Sex),
                    new SqlParameter("@Age",entity.Age),
                    new SqlParameter("@Name",entity.Name),
                    new SqlParameter("@StarId",entity.Star.Id),
                    new SqlParameter("@BloodTypeId",entity.BloodType.Id)
            };

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

        }

        /// <summary>
        /// 用户注册
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public object Register(UsersInfo entity)
        {
            string strSQL = "INSERT INTO [Users](LoginPwd,FriendshipPolicyId,NickName,FaceId,Sex,Age,Name,StarId,BloodTypeId) VALUES(@LoginPwd,@FriendshipPolicyId,@NickName,@FaceId,@Sex,@Age,@Name,@StarId,@BloodTypeId) select @@identity";

            SqlParameter[] pars = new SqlParameter[]{
                    new SqlParameter("@LoginPwd",entity.LoginPwd),
                    new SqlParameter("@FriendshipPolicyId",entity.FriendshipPolicy.Id),
                    new SqlParameter("@NickName",entity.NickName),
                    new SqlParameter("@FaceId",entity.FaceId),
                    new SqlParameter("@Sex",entity.Sex),
                    new SqlParameter("@Age",entity.Age),
                    new SqlParameter("@Name",entity.Name),
                    new SqlParameter("@StarId",entity.Star.Id),
                    new SqlParameter("@BloodTypeId",entity.BloodType.Id)
            };

          return   SqlHelper.ExecuteScalar(SqlHelper.ConnString, CommandType.Text, strSQL, pars);

        }

        /// <summary>
        /// 好友搜索
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public List<UsersInfo> Search(UsersInfo entity)
        {
            string strSQL = "SELECT * FROM Users WHERE 1=1";

            //进入条件组合查询
            if (entity != null)
            {
                if (entity.Id > 0)
                    strSQL += " and id=" + entity.Id;
            }

            List<UsersInfo> objItems = new List<UsersInfo>();

            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
            {
                while (rdr.Read())
                {
                    UsersInfo objInfo = new UsersInfo();
                    objInfo.Id = Convert.ToInt32(rdr["Id"]);
                    //objInfo.LoginPwd = rdr["LoginPwd"].ToString();
                    objInfo.FriendshipPolicy = new FriendshipPolicyInfo(Convert.ToInt32(rdr["FriendshipPolicyId"]));
                    objInfo.NickName = rdr["NickName"].ToString();
                    objInfo.FaceId = Convert.ToInt32(rdr["FaceId"]);
                    objInfo.Sex = rdr["Sex"].ToString();
                    objInfo.Age = Convert.ToInt32(rdr["Age"]);
                    objInfo.Name = rdr["Name"].ToString();
                    objInfo.Star = new StarInfo(Convert.ToInt32(rdr["StarId"]));
                    objInfo.BloodType = new BloodTypeInfo(Convert.ToInt32(rdr["BloodTypeId"]));
                    objItems.Add(objInfo);
                }
            }

            return objItems;
        }

        public  List<UsersInfo> Search(int userId, string nickName,int fromAge ,int toAge, string sex)
        {
            string strSQL = "SELECT * FROM Users WHERE 1=1";
            if (userId > 0)
                strSQL += " and Id="+userId;
            if (!string.IsNullOrEmpty(nickName))
                strSQL += " and NickName like '%" + nickName + "%'";
            if (fromAge > 0)
                strSQL += " and Age >=" + fromAge;
            if (toAge > 0)
                strSQL += " and Age <=" + toAge;
            if (!string.IsNullOrEmpty(sex))
                strSQL += " and Sex ='" + sex+"'";

            List<UsersInfo> objItems = new List<UsersInfo>();

            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, strSQL, null))
            {
                while (rdr.Read())
                {
                    UsersInfo objInfo = new UsersInfo();
                    objInfo.Id = Convert.ToInt32(rdr["Id"]);
                    //objInfo.LoginPwd = rdr["LoginPwd"].ToString();
                    objInfo.FriendshipPolicy = new FriendshipPolicyInfo(Convert.ToInt32(rdr["FriendshipPolicyId"]));
                    objInfo.NickName = rdr["NickName"].ToString();
                    objInfo.FaceId = Convert.ToInt32(rdr["FaceId"]);
                    objInfo.Sex = rdr["Sex"].ToString();
                    objInfo.Age = Convert.ToInt32(rdr["Age"]);
                    objInfo.Name = rdr["Name"].ToString();
                    objInfo.Star = new StarInfo(Convert.ToInt32(rdr["StarId"]));
                    objInfo.BloodType = new BloodTypeInfo(Convert.ToInt32(rdr["BloodTypeId"]));
                    objItems.Add(objInfo);
                }
            }

            return objItems;

        }

        /// <summary>
        /// 更新基本资料
        /// </summary>
        /// <param name="entity"></param>
        public void UpdateProfile(UsersInfo entity)
        {
            string strSQL = "UPDATE Users SET NickName =@NickName,FaceId = @FaceId,Sex = @Sex,Age = @Age,Name = @Name,StarId =@StarId,BloodTypeId =@BloodTypeId WHERE Id=@Id";

            SqlParameter[] pars = new SqlParameter[]{
                    new SqlParameter("@NickName",entity.NickName),
                    new SqlParameter("@FaceId",entity.FaceId),
                    new SqlParameter("@Sex",entity.Sex),
                    new SqlParameter("@Age",entity.Age),
                    new SqlParameter("@Name",entity.Name),
                    new SqlParameter("@StarId",entity.Star.Id),
                    new SqlParameter("@BloodTypeId",entity.BloodType.Id),
                    new SqlParameter("@Id",entity.Id)
            };

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

        /// <summary>
        /// 更改密码及身份验证
        /// </summary>
        /// <param name="password"></param>
        /// <param name="identity"></param>
        public void ChangeSecurity(int id, string password, int identity)
        {
            string strSQL = "UPDATE Users SET LoginPwd =@LoginPwd,FriendshipPolicyId = @FriendshipPolicyId WHERE Id=@Id";

            SqlParameter[] pars = new SqlParameter[]{
                    new SqlParameter("@LoginPwd",password),
                    new SqlParameter("@FriendshipPolicyId",identity),
                    new SqlParameter("@Id",id)
            };

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

        /// <summary>
        /// 更改身份验证
        /// </summary>
        /// <param name="password"></param>
        /// <param name="identity"></param>
        public void ChangeIdentity(int id,  int identity)
        {
            string strSQL = string.Format("UPDATE Users SET FriendshipPolicyId = {0} WHERE Id={1}",identity,id);         
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, null);
        }

        /// <summary>
        /// 更换头像
        /// </summary>
        /// <param name="faceId"></param>
        public void ChangeFace(int id, int faceId)
        {

            string strSQL = string.Format("UPDATE Users SET FaceId ={0} WHERE Id={1}", faceId, id);

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSQL, null);
        
        }

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

    }
}