HENGCHENG/DSWeb/EntityDA/UserAttributeDA.cs

using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using DSWeb.Models;
using DSWeb.DataAccess;

namespace DSWeb.EntityDA
{
    public class UserAttributeDA
    {
        private const string PARM_REF_GID = "@gid";
        private const string PARM_REF_USERID = "@userid";
        private const string PARM_REF_ATTRIBUTEID = "@attributeid";
        private const string PARM_REF_ATTRIBUTEVALUE = "@attribute_value";
        private const string PARM_USER_ATTRIBUTE_NAME = "@name";

        private const string PARM_USERS_GID = "@userid";

        private const string SQL_SELECT_USERATTR_ALL = " SELECT GID,NAME,DESCRIPTION,DEFAULTVALUE,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME,STATE,SORT "
                                                     + " FROM user_attribute ";
        private const string SQL_SELECT_USERATTR_ALL_BY = " SELECT GID,NAME,DESCRIPTION,DEFAULTVALUE,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME,STATE,SORT "
                                                        + " FROM user_attribute WHERE 1> 0 ";

        //获取用户账户属性的值
        private const string SQL_SELECT_USERATTR = "SELECT A.GID,A.NAME,A.DESCRIPTION,A.DEFAULTVALUE,A.STATE,B.USERID,B.VALUE,B.GID"
                                                       + " FROM user_attribute AS A INNER JOIN user_userattribute AS B "
                                                       + " ON A.GID = B.ATTRIBUTEID WHERE B.USERID = @userid";

        private const string SQL_INSERT_USERATTRREF = "INSERT INTO user_userattribute(GID,USERID,ATTRIBUTEID,VALUE) VALUES(@gid,@userid,@attributeid,@attribute_value)";

        private const string SQL_UPDATE_USERATTRREF = "update user_userattribute set VALUE=@attribute_value where USERID=@userid and GID=@gid and ATTRIBUTEID=@attributeid";

        private const string SQL_SELECT_USERATTR_BY_GID_AND_ATTRNAME = "SELECT A.GID,A.NAME,A.DESCRIPTION,A.DEFAULTVALUE,A.STATE,B.USERID,B.VALUE,B.GID"
                                                       + " FROM user_attribute AS A INNER JOIN user_userattribute AS B "
                                                       + " ON A.GID = B.ATTRIBUTEID WHERE B.USERID = @userid AND A.NAME = @name ";


        #region 获取所有用户账户属性 (GetUserAttributeAll)
        /// <summary>
        /// 获取所有用户账户属性
        /// </summary>
        /// <returns></returns>
        public IList<UserAttributeEntity> GetUserAttributeAll()
        {
            IList<UserAttributeEntity> accountEntities = null;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_USERATTR_ALL, null))
            {
                accountEntities = new List<UserAttributeEntity>();
                try
                {
                    while (sqlRead.Read())
                    {
                        UserAttributeEntity accountEntity = new UserAttributeEntity();
                        if (!sqlRead.IsDBNull(0))
                        {
                            accountEntity.Gid = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            accountEntity.AttributeName = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            accountEntity.Description = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            accountEntity.DefaultValue = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            accountEntity.CreateUserID = sqlRead.GetString(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            accountEntity.CreateTime = sqlRead.GetDateTime(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            accountEntity.ModifiedUserID = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            accountEntity.ModifiedTime = sqlRead.GetDateTime(7);
                        }
                        if (!sqlRead.IsDBNull(8))
                        {
                            accountEntity.State = sqlRead.GetInt32(8);
                        }
                        if (!sqlRead.IsDBNull(9))
                        {
                            accountEntity.Sort = sqlRead.GetInt32(9);
                        }
                        accountEntities.Add(accountEntity);
                    }
                }
                catch (Exception execError)
                {
                    throw execError;
                }
            }
            return accountEntities;
        }
        #endregion


        /// <summary>
        /// 根据用户GID获取相关用户属性
        /// </summary>
        /// <param name="strUserGID"></param>
        /// <returns></returns>
        public IList<UserAttributeEntity> GetUserAttributeValue(string strUserGID)
        {

            IList<UserAttributeEntity> accountEntities = null;

            SqlParameter parm = new SqlParameter(PARM_USERS_GID, SqlDbType.VarChar, 36);
            parm.Value = strUserGID;


            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_USERATTR, parm))
            {
                accountEntities = new List<UserAttributeEntity>();

                try
                {
                    while (sqlRead.Read())
                    {
                        UserAttributeEntity userEntity = new UserAttributeEntity();
                        UserAttributeRefEntity refEntity = new UserAttributeRefEntity();

                        if (!sqlRead.IsDBNull(0))
                        {
                            userEntity.Gid = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            userEntity.AttributeName = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            userEntity.Description = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            userEntity.DefaultValue = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            userEntity.State = sqlRead.GetInt32(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            refEntity.UserID = sqlRead.GetString(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            refEntity.AttributeValue = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            refEntity.Gid = sqlRead.GetString(7);
                        }
                        userEntity.RefEntity = refEntity;
                        accountEntities.Add(userEntity);
                    }
                }
                catch (Exception execError)
                {
                    throw execError;
                }
            }
            return accountEntities;
        }

        public bool InsertUserAttribute(string strUserGid, IList<UserAttributeRefEntity> refEntities)
        {
            bool result = false;
            SqlCommand sqlCommand = new SqlCommand();
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter(PARM_REF_GID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_REF_USERID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_REF_ATTRIBUTEID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_REF_ATTRIBUTEVALUE,SqlDbType.VarChar,36)
                        };

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                foreach (UserAttributeRefEntity refEntity in refEntities)
                {
                    parms[0].Value = refEntity.Gid;
                    parms[1].Value = refEntity.UserID;
                    parms[2].Value = refEntity.AttributeID;
                    parms[3].Value = refEntity.AttributeValue;

                    sqlCommand.Parameters.AddRange(parms);
                    sqlCommand.Connection = conn;
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = SQL_INSERT_USERATTRREF;

                    int val = sqlCommand.ExecuteNonQuery();
                    sqlCommand.Parameters.Clear();

                    if (val > 0)
                    {
                        result = true;
                    }
                    else
                    {
                        result = false;
                    }
                }
            }
            return result;
        }

        public bool UpdateUserAttribute(string strUserGid, IList<UserAttributeRefEntity> tempAttrEntity)
        {
            bool result = false;
            SqlCommand sqlCommand = new SqlCommand();
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter(PARM_REF_GID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_REF_USERID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_REF_ATTRIBUTEID,SqlDbType.VarChar,36),
                        new SqlParameter(PARM_REF_ATTRIBUTEVALUE,SqlDbType.VarChar,36)
                        };

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                foreach (UserAttributeRefEntity refEntity in tempAttrEntity)
                {
                    parms[0].Value = refEntity.Gid;
                    parms[1].Value = refEntity.UserID;
                    parms[2].Value = refEntity.AttributeID;
                    parms[3].Value = refEntity.AttributeValue;

                    sqlCommand.Parameters.AddRange(parms);
                    sqlCommand.Connection = conn;
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = SQL_UPDATE_USERATTRREF;

                    int val = sqlCommand.ExecuteNonQuery();
                    sqlCommand.Parameters.Clear();

                    if (val > 0)
                    {
                        result = true;
                    }
                    else
                    {
                        result = false;
                    }
                }
            }
            return result;
        }

        #region 通过用户属性NAME和用户GID获取用户属性信息
        /// <summary>
        /// 通过用户属性NAME和用户GID获取用户属性信息
        /// </summary>
        /// <param name="tempUserID">用户GID</param>
        /// <param name="tempUserAttributeName">用户属性NAME</param>
        /// <returns>返回用户属性实体类</returns>
        public UserAttributeEntity GetUserAttributeByIdAndAttrName(string tempUserID,string tempUserAttributeName)
        {
            UserAttributeEntity userEntity = null;

            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter(PARM_USERS_GID, SqlDbType.VarChar, 36),
                new SqlParameter(PARM_USER_ATTRIBUTE_NAME, SqlDbType.VarChar, 50)
            };
            parms[0].Value = tempUserID;
            parms[1].Value = tempUserAttributeName;

            using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_USERATTR_BY_GID_AND_ATTRNAME, parms))
            {
                try
                {
                    while (sqlRead.Read())
                    {
                        userEntity = new UserAttributeEntity();
                        UserAttributeRefEntity refEntity = new UserAttributeRefEntity();

                        if (!sqlRead.IsDBNull(0))
                        {
                            userEntity.Gid = sqlRead.GetString(0);
                        }
                        if (!sqlRead.IsDBNull(1))
                        {
                            userEntity.AttributeName = sqlRead.GetString(1);
                        }
                        if (!sqlRead.IsDBNull(2))
                        {
                            userEntity.Description = sqlRead.GetString(2);
                        }
                        if (!sqlRead.IsDBNull(3))
                        {
                            userEntity.DefaultValue = sqlRead.GetString(3);
                        }
                        if (!sqlRead.IsDBNull(4))
                        {
                            userEntity.State = sqlRead.GetInt32(4);
                        }
                        if (!sqlRead.IsDBNull(5))
                        {
                            refEntity.UserID = sqlRead.GetString(5);
                        }
                        if (!sqlRead.IsDBNull(6))
                        {
                            refEntity.AttributeValue = sqlRead.GetString(6);
                        }
                        if (!sqlRead.IsDBNull(7))
                        {
                            refEntity.Gid = sqlRead.GetString(7);
                        }
                        userEntity.RefEntity = refEntity;
                    }
                }
                catch (Exception execError)
                {
                    throw execError;
                }
            }
            return userEntity;
        }
        #endregion
    }
}