userinfo.cs

来自「该服务平台解决了计算机网络与移动网络之间信息交换问题」· CS 代码 · 共 283 行 · 第 1/2 页

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DataAccess.TableAccessor
{
    public class UserInfo
    {
        private Database db = null;
        public UserInfo()
        {
            db = DatabaseFactory.CreateDatabase("Connection String");
        }
        /// <summary>
        /// 得到UserInfo表中单一用户信息
        /// </summary>
        /// <param name="systemID"></param>
        /// <returns></returns>
        public DataTable GetSingleUserInfo(int systemID)
        {
            //string strSql = string.Format("SELECT UserInfo.UserID, UserInfo.Name, UserInfo.Sex, UserInfo.Birthday, UserInfo.Mobile, "+
            //    "UserInfo.HomeAddress, UserInfo.UserDefineID, UserInfo.TradeID, Area.AreaName, UserInfo.AreaID, "+
            //    "Corporation.CorporationName, UserInfo.Duty, UserInfo.School, UserInfo.SchoolAge, UserInfo.Speciality, "+
            //    "Corporation.CorporationID FROM UserInfo INNER JOIN Area ON UserInfo.AreaID = Area.AreaID INNER JOIN "+
            //    "UserCorporation ON UserInfo.UserID = UserCorporation.UserID INNER JOIN "+
            //    "Corporation ON UserCorporation.CorporationID = Corporation.CorporationID "+
            //    "WHERE UserInfo.IsValid <> 0 AND UserInfo.UserID = {0}", systemID);
            string strSql = string.Format("SELECT UserInfo.UserID, UserInfo.Name, UserInfo.Sex, UserInfo.Birthday, UserInfo.Mobile, " +
                "UserInfo.HomeAddress, UserInfo.UserDefineID, UserInfo.TradeID, Area.AreaName, UserInfo.AreaID, " +
                "Corporation.CorporationName, UserInfo.Duty, UserInfo.School, UserInfo.SchoolAge, UserInfo.Speciality, " +
                "Corporation.CorporationID, UserInfo.UserFunction FROM UserInfo INNER JOIN Area ON UserInfo.AreaID = Area.AreaID INNER JOIN " +
                "Corporation ON UserInfo.CountyID = Corporation.CorporationID WHERE (UserInfo.IsValid <> 0) AND (UserInfo.UserID = {0})", systemID);

            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        /// <summary>
        /// 添加用户信息
        /// </summary>
        /// <param name="AreaID">地市</param>
        /// <param name="countyID"></param>
        /// <param name="villageID"></param>
        /// <param name="userDefineID">用户自定义编号</param>
        /// <param name="name"></param>
        /// <param name="sex"></param>
        /// <param name="birthday"></param>
        /// <param name="homeAddress"></param>
        /// <param name="mobile"></param>
        /// <param name="tradeID"></param>
        /// <param name="userFuncation">用户类型标志</param>
        /// <returns></returns>
        public int AddUserInfo(int AreaID, int countyID, int villageID, string userDefineID, string name, int sex, string birthday,
            string homeAddress, string mobile, int tradeID, int userFuncation,string duty, string school, string schoolAge,
            string speciality)
        {
            string strSQL = string.Format("INSERT INTO UserInfo (AreaID, CountyID, VillageID, UserDefineID, Name, Sex, Birthday, HomeAddress, " +
                "Mobile, TradeID, UserFunction, Duty, School, SchoolAge, Speciality) VALUES "+
                "({0},{1},{2},'{3}','{4}',{5},'{6}','{7}','{8}',{9},{10},'{11}','{12}','{13}','{14}')", AreaID, countyID, villageID, userDefineID, 
                name, sex, birthday, homeAddress, mobile, tradeID, userFuncation, duty, school, schoolAge, speciality);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }

        public int UpdateUserInfo(int areaID, int countyID, int VillageID, string defineID, string name, int sex, string birthday, string homeAddress,
            string mobile, int trade, int userFuncation, int userID, string school, string schoolAge, string Duty, string speciality)
        {
            string strSql = string.Format("UPDATE UserInfo SET AreaID ={0}, CountyID ={1}, VillageID ={2}, UserDefineID ='{3}', Name ='{4}', Sex =  {5}, " +
                "Birthday ='{6}', HomeAddress ='{7}', Mobile ='{8}', TradeID ={9}, UserFunction ={10}, Duty ='{11}', School ='{12}', SchoolAge ='{13}', Speciality = '{14}' " +
                "WHERE UserID = {15}", areaID, countyID, VillageID, defineID, name, sex, birthday, homeAddress, mobile,
                trade, userFuncation, Duty, school, schoolAge, speciality, userID);

            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }


        public DataTable GetUserInfo(int userTypeID,out DataRow[] rows)
        {
            string queryStr = string.Format("UserTypeID = {0}", userTypeID);
            UserTypeKey userTypeKeyAccess = new UserTypeKey();
            TableDefine.UserTypeKeyDefine userTypeDataSet = userTypeKeyAccess.GetMostUserTypeKey();
            rows = userTypeDataSet.Tables["UserTypeKey"].Select(queryStr);

            DbCommand command = db.GetStoredProcCommand("GetUserInfoByUserTypeID");
            db.AddInParameter(command, "@UserTypeID", DbType.Int32, userTypeID);
            DataSet ds = db.ExecuteDataSet(command);
            return ds.Tables[0];
        }

        public DataTable GetUserInfo()
        {
            //string strSql = "SELECT UserInfo.UserID, Area.AreaName, Corporation.CorporationName, UserInfo.Name, UserInfo.Duty, "+
            //    "CASE WHEN (Sex) = 0 THEN '男' ELSE '女' END AS Sex, UserInfo.Mobile FROM UserInfo INNER JOIN "+
            //    "Area ON UserInfo.AreaID = Area.AreaID INNER JOIN "+
            //    "UserCorporation ON UserInfo.UserID = UserCorporation.UserID INNER JOIN "+
            //    "Corporation ON UserCorporation.CorporationID = Corporation.CorporationID WHERE (UserInfo.IsValid <> 0) ORDER BY UserCorporation.CorporationID";
            //DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            //return ds.Tables[0];
            string strSql = "SELECT UserInfo.UserID, UserInfo.Name, CASE WHEN (UserInfo.Sex) = 0 THEN '男' ELSE '女' END AS Sex, UserInfo.Duty, UserInfo.IsValid, Area.AreaName, " +
                "Corporation.CorporationName, UserInfo.Mobile,UserInfo.UserLocation FROM UserInfo INNER JOIN Area ON UserInfo.AreaID = Area.AreaID INNER JOIN " +
                "Corporation ON UserInfo.CountyID = Corporation.CorporationID WHERE (UserInfo.IsValid <> 0) ";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetUserInfoByPKID(int pkid)
        {
            DbCommand command = db.GetStoredProcCommand("GetUserInfoByPKID");
            db.AddInParameter(command, "@PK_ID", DbType.Int32, pkid);
            DataSet ds = db.ExecuteDataSet(command);
            return ds.Tables[0];
        }

        public int DeleteUserInfo(int systemID)
        {
            string strSql = string.Format("UPDATE UserInfo SET IsValid = 0 WHERE UserID = {0}", systemID);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }
        /// <summary>
        /// 得到所有协会名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetOrganise()
        {
            string strSql = "SELECT DISTINCT Organise FROM UserInfo";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetSchoolName()
        {
            string strSql = "SELECT DISTINCT SchoolName FROM UserInfo";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetGrade(string schoolName)
        {
            string strSql = string.Format("SELECT DISTINCT GradeName FROM UserInfo WHERE SchoolName = '{0}'", schoolName);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);