/*
'' ============================================================================================
'' 项目名称：风越项目范例ASPX普通三层结构
'' 项目版本：V1.0
'' 项目描述：风越项目范例，用于建立ASPX普通三层结构
'' 文件名称：DAL_USER.cs
'' 文件描述：数据访问层 USER （用户信息）
'' 公司名称：中极网盛
'' 开发人员：刘怒
'' 创建日期：2008-12-25 20:23:50
'' 修订日期：2009-03-10 06:08:33
'' 版权信息：Copyright (C) 2008

'' 项目升级时需读取以下生成工具的版权信息
'' 生成工具：风越代码生成器 [FireCode Creator]
'' 当前版本：通用版 V1.8
'' 官方网站：http://www.sino8848.com
'' ============================================================================================
*/

using System;
using System.Text;
using System.Data;


using System.Data.SqlClient;

using FypSystem.DBManager;

namespace Fyp_3TierCommon.DAL
{
    /// <summary>
    /// 数据访问类USER。
    /// </summary>
    public class DAL_USER
    {
        public DAL_USER()
        {
        }

        #region  记录是否存在
        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="USER_ID">编号</param>, 
        /// <param name="USER_NAME">姓名</param>
        /// <returns></returns>
        public bool Exists(int? USER_ID, string USER_NAME)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from [USER] ");
            strSql.Append("where [USER_ID] = @USER_ID and [USER_NAME] = @USER_NAME");
            

            
            

            
            
            SqlParameter[] arrParameters = new SqlParameter[2];
            
            arrParameters[0] = new SqlParameter("@USER_ID", SqlDbType.Int, 10);
            arrParameters[0].Value = USER_ID;
            
            arrParameters[1] = new SqlParameter("@USER_NAME", SqlDbType.NVarChar, 200);
            arrParameters[1].Value = USER_NAME;
            
            

    
    
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    
            {
                

                
                return Convert.ToBoolean(dbManager.ExecScalar(strSql.ToString(), arrParameters));
                
            }
        }
        #endregion

        #region 增加数据
        /// <summary>
        /// 增加数据
        /// </summary>
        /// <param name="model">USER实体对象</param>
        public void Add(Fyp_3TierCommon.Model.Model_USER model)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [USER] (");
            strSql.Append("USER_NAME, USER_PWD, USER_EMAIL, USER_BIRTHDAY, USER_PHOTO, USER_PHOTOS2, USER_NATION, USER_COUNTRY, USER_PROVINCE");
            strSql.Append(") values (");
            strSql.Append("@USER_NAME, @USER_PWD, @USER_EMAIL, @USER_BIRTHDAY, @USER_PHOTO, @USER_PHOTOS2, @USER_NATION, @USER_COUNTRY, @USER_PROVINCE");
            strSql.Append(")");
            

            
            

            
            
            SqlParameter[] arrParameters = new SqlParameter[9];
            
            arrParameters[0] = new SqlParameter("@USER_NAME", SqlDbType.NVarChar, 200);
            arrParameters[0].Value = model.USER_NAME;
            
            arrParameters[1] = new SqlParameter("@USER_PWD", SqlDbType.NVarChar, 50);
            arrParameters[1].Value = model.USER_PWD;
            
            arrParameters[2] = new SqlParameter("@USER_EMAIL", SqlDbType.NVarChar, 50);
            arrParameters[2].Value = model.USER_EMAIL;
            
            arrParameters[3] = new SqlParameter("@USER_BIRTHDAY", SqlDbType.DateTime, 23);
            arrParameters[3].Value = model.USER_BIRTHDAY;
            
            arrParameters[4] = new SqlParameter("@USER_PHOTO", SqlDbType.NText, 1073741823);
            arrParameters[4].Value = model.USER_PHOTO;
            
            arrParameters[5] = new SqlParameter("@USER_PHOTOS2", SqlDbType.Image, 2147483647);
            arrParameters[5].Value = model.USER_PHOTOS2;
            
            arrParameters[6] = new SqlParameter("@USER_NATION", SqlDbType.Int, 10);
            arrParameters[6].Value = model.USER_NATION;
            
            arrParameters[7] = new SqlParameter("@USER_COUNTRY", SqlDbType.Int, 10);
            arrParameters[7].Value = model.USER_COUNTRY;
            
            arrParameters[8] = new SqlParameter("@USER_PROVINCE", SqlDbType.Int, 10);
            arrParameters[8].Value = model.USER_PROVINCE;
            
            

    
    
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    
            {
                

                
                dbManager.ExecNonQuery(strSql.ToString(), arrParameters);
                
            }
        }
        #endregion

        #region 更新数据
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="model">USER实体对象</param>
        public void Update(Fyp_3TierCommon.Model.Model_USER model)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [USER] set ");
            
            strSql.Append("USER_NAME=@USER_NAME");
            strSql.Append("USER_PWD=@USER_PWD");
            strSql.Append("USER_EMAIL=@USER_EMAIL");
            strSql.Append("USER_BIRTHDAY=@USER_BIRTHDAY");
            strSql.Append("USER_PHOTO=@USER_PHOTO");
            strSql.Append("USER_PHOTOS2=@USER_PHOTOS2");
            strSql.Append("USER_NATION=@USER_NATION");
            strSql.Append("USER_COUNTRY=@USER_COUNTRY");
            strSql.Append("USER_PROVINCE=@USER_PROVINCE");
            strSql.Append(" where [USER_ID] = @USER_ID and [USER_NAME] = @USER_NAME");
            

            
            

            
            
            SqlParameter[] arrParameters = new SqlParameter[10];
            
            arrParameters[0] = new SqlParameter("@USER_ID", SqlDbType.Int, 10);
            arrParameters[0].Value = model.USER_ID;
            
            arrParameters[1] = new SqlParameter("@USER_NAME", SqlDbType.NVarChar, 200);
            arrParameters[1].Value = model.USER_NAME;
            
            arrParameters[2] = new SqlParameter("@USER_PWD", SqlDbType.NVarChar, 50);
            arrParameters[2].Value = model.USER_PWD;
            
            arrParameters[3] = new SqlParameter("@USER_EMAIL", SqlDbType.NVarChar, 50);
            arrParameters[3].Value = model.USER_EMAIL;
            
            arrParameters[4] = new SqlParameter("@USER_BIRTHDAY", SqlDbType.DateTime, 23);
            arrParameters[4].Value = model.USER_BIRTHDAY;
            
            arrParameters[5] = new SqlParameter("@USER_PHOTO", SqlDbType.NText, 1073741823);
            arrParameters[5].Value = model.USER_PHOTO;
            
            arrParameters[6] = new SqlParameter("@USER_PHOTOS2", SqlDbType.Image, 2147483647);
            arrParameters[6].Value = model.USER_PHOTOS2;
            
            arrParameters[7] = new SqlParameter("@USER_NATION", SqlDbType.Int, 10);
            arrParameters[7].Value = model.USER_NATION;
            
            arrParameters[8] = new SqlParameter("@USER_COUNTRY", SqlDbType.Int, 10);
            arrParameters[8].Value = model.USER_COUNTRY;
            
            arrParameters[9] = new SqlParameter("@USER_PROVINCE", SqlDbType.Int, 10);
            arrParameters[9].Value = model.USER_PROVINCE;
            
            

    
    
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    
            {
                

                
                dbManager.ExecNonQuery(strSql.ToString(), arrParameters);
                
            }
        }
        #endregion

        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="USER_ID">编号</param>, 
        /// <param name="USER_NAME">姓名</param>
        public void Delete(int? USER_ID, string USER_NAME)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [USER] ");
            strSql.Append(" where [USER_ID] = @USER_ID and [USER_NAME] = @USER_NAME");
            

            
            

            
            
            SqlParameter[] arrParameters = new SqlParameter[2];
            
            arrParameters[0] = new SqlParameter("@USER_ID", SqlDbType.Int, 10);
            arrParameters[0].Value = USER_ID;
            
            arrParameters[1] = new SqlParameter("@USER_NAME", SqlDbType.NVarChar, 200);
            arrParameters[1].Value = USER_NAME;
            
            

    
    
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    
            {
                

                
                dbManager.ExecNonQuery(strSql.ToString(), arrParameters);
                
            }
        }
        #endregion

        #region 获取对象实体
        /// <summary>
        /// 获取对象实体
        /// </summary>
        /// <param name="USER_ID">编号</param>, 
        /// <param name="USER_NAME">姓名</param>
        /// <returns></returns>
        public Fyp_3TierCommon.Model.Model_USER GetModel(int? USER_ID, string USER_NAME)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select USER_ID, USER_NAME, USER_PWD, USER_EMAIL, USER_BIRTHDAY, USER_PHOTO, USER_PHOTOS2, USER_NATION, USER_COUNTRY, USER_PROVINCE from [USER]");
            strSql.Append(" where [USER_ID] = @USER_ID and [USER_NAME] = @USER_NAME");
            

            
            

            
            
            SqlParameter[] arrParameters = new SqlParameter[2];
            
            arrParameters[0] = new SqlParameter("@USER_ID", SqlDbType.Int, 10);
            arrParameters[0].Value = USER_ID;
            
            arrParameters[1] = new SqlParameter("@USER_NAME", SqlDbType.NVarChar, 200);
            arrParameters[1].Value = USER_NAME;
            
            

            Fyp_3TierCommon.Model.Model_USER model = new Fyp_3TierCommon.Model.Model_USER();

            DataSet ds = new DataSet();;
    
    
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    
            {
                

                
                ds = dbManager.GetDataSet(strSql.ToString(), arrParameters);
                
            }

            if(ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];

            
                

                 
                if (dr["USER_ID"].ToString().Length > 0)
                    model.USER_ID = int.Parse(dr["USER_ID"].ToString());
                

                
            
                 
                model.USER_NAME = dr["USER_NAME"].ToString();
                

                

                
            
                 
                model.USER_PWD = dr["USER_PWD"].ToString();
                

                

                
            
                 
                model.USER_EMAIL = dr["USER_EMAIL"].ToString();
                

                

                
            
                

                 
                if (dr["USER_BIRTHDAY"].ToString().Length > 0)
                    model.USER_BIRTHDAY = DateTime.Parse(dr["USER_BIRTHDAY"].ToString());
                

                
            
                 
                model.USER_PHOTO = dr["USER_PHOTO"].ToString();
                

                

                
            
                

                

                 
                if (dr["USER_PHOTOS2"].ToString().Length > 0)
                    model.USER_PHOTOS2 = (byte[])dr["USER_PHOTOS2"];
                
            
                

                 
                if (dr["USER_NATION"].ToString().Length > 0)
                    model.USER_NATION = int.Parse(dr["USER_NATION"].ToString());
                

                
            
                

                 
                if (dr["USER_COUNTRY"].ToString().Length > 0)
                    model.USER_COUNTRY = int.Parse(dr["USER_COUNTRY"].ToString());
                

                
            
                

                 
                if (dr["USER_PROVINCE"].ToString().Length > 0)
                    model.USER_PROVINCE = int.Parse(dr["USER_PROVINCE"].ToString());
                

                
            

                return model;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 获取数据列表
        /// <summary>
        /// 获取数据列表 DataSet
        /// </summary>
        /// <param name="strWhere">指定查询条件的SQL语句，不包含where</param>
        /// <returns></returns>
        public DataSet GetDataSet(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select USER_ID, USER_NAME, USER_PWD, USER_EMAIL, USER_BIRTHDAY, USER_PHOTO, USER_PHOTOS2, USER_NATION, USER_COUNTRY, USER_PROVINCE from [USER]");
            if(strWhere.Trim().Length > 0)
            {
                strSql.Append(" where " + strWhere);
            }
    
    
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    
            {
                return dbManager.GetDataSet(strSql.ToString());
            }
        }

        /// <summary>
        /// 获取数据列表 DataTable
        /// </summary>
        /// <param name="strWhere">指定查询条件的SQL语句，不包含where</param>
        /// <returns></returns>
        public DataTable GetDataTable(string strWhere)
        {
            return GetDataTable(strWhere, "");
        }

        /// <summary>
        /// 获取数据列表 DataTable
        /// </summary>
        /// <param name="strWhere">指定查询条件的SQL语句，不包含where</param>
        /// <param name="strOrder">排序字段，如id desc，不包含order by</param>
        /// <returns></returns>
        public DataTable GetDataTable(string strWhere, string strOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select USER_ID, USER_NAME, USER_PWD, USER_EMAIL, USER_BIRTHDAY, USER_PHOTO, USER_PHOTOS2, USER_NATION, USER_COUNTRY, USER_PROVINCE from [USER]");
            if(strWhere.Trim().Length > 0)
                strSql.Append(" where " + strWhere);

            if (strOrder.Trim().Length > 0)
                strSql.Append(" order by " + strOrder);

    
    
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    
            {
                return dbManager.GetDataTable(strSql.ToString());
            }
        }
        #endregion 
    }
}