架构B/S四 SQLServerDAL 数据访问层(数据操作层)

2008-04-10 10:47

using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using CoalTraffic.IDAL;
using CoalTraffic.DBUtility;

namespace CoalTraffic.SQLServerDAL
{
///

/// 数据访问类T_User。
///

public class T_User:IT_User
{
   public T_User()
   {}
   #region 成员方法

   ///

   /// 得到最大ID
   ///

   public int GetMaxId()
   {
    return DbHelperSQL.GetMaxID("UserCode", "T_User");
   }
   ///

   /// 是否存在该记录
   ///

   public bool Exists(string UserCode)
   {
    StringBuilder strSql=new StringBuilder();
    strSql.Append("select count(1) from T_User");
    strSql.Append(" where UserCode= @UserCode");
    SqlParameter[] parameters = {
      new SqlParameter("@UserCode", SqlDbType.VarChar)
     };
    parameters[0].Value = UserCode;
    return DbHelperSQL.Exists(strSql.ToString(),parameters);
   }

   ///

   /// 增加一条数据
   ///

   public string Add(CoalTraffic.Model.T_User model)
   {
    //model.UserCode=GetMaxId();
    StringBuilder strSql=new StringBuilder();
    strSql.Append("insert into T_User(");
    strSql.Append("UserCode,UserName,Passwd,Sex,UserType,Position,Telephone,Email,Address,DepartCode,SysType,IsForbid,Remark)");
    strSql.Append(" values (");
    strSql.Append("@UserCode,@UserName,@Passwd,@Sex,@UserType,@Position,@Telephone,@Email,@Address,@DepartCode,@SysType,@IsForbid,@Remark)");
    SqlParameter[] parameters = {
      new SqlParameter("@UserCode", SqlDbType.VarChar,20),
      new SqlParameter("@UserName", SqlDbType.NVarChar),
      new SqlParameter("@Passwd", SqlDbType.NVarChar),
      new SqlParameter("@Sex", SqlDbType.NVarChar),
      new SqlParameter("@UserType", SqlDbType.VarChar,1),
      new SqlParameter("@Position", SqlDbType.NVarChar),
      new SqlParameter("@Telephone", SqlDbType.NVarChar),
      new SqlParameter("@Email", SqlDbType.NVarChar),
      new SqlParameter("@Address", SqlDbType.NVarChar),
      new SqlParameter("@DepartCode", SqlDbType.VarChar,10),
      new SqlParameter("@SysType", SqlDbType.VarChar,10),
      new SqlParameter("@IsForbid", SqlDbType.VarChar,1),
      new SqlParameter("@Remark", SqlDbType.NVarChar)};
    parameters[0].Value = model.UserCode;
    parameters[1].Value = model.UserName;
    parameters[2].Value = model.Passwd;
    parameters[3].Value = model.Sex;
    parameters[4].Value = model.UserType;
    parameters[5].Value = model.Position;
    parameters[6].Value = model.Telephone;
    parameters[7].Value = model.Email;
    parameters[8].Value = model.Address;
    parameters[9].Value = model.DepartCode;
    parameters[10].Value = model.SysType;
    parameters[11].Value = model.IsForbid;
    parameters[12].Value = model.Remark;

    int iAffected = DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);

            if (iAffected > 0)
            {
                return model.UserCode;
            }
            else
            {
                return string.Empty ;
            }
   }
   ///

   /// 更新一条数据
   ///

   public bool Update(CoalTraffic.Model.T_User model)
   {
    StringBuilder strSql=new StringBuilder();
    strSql.Append("update T_User set ");
    strSql.Append("UserName=@UserName,");
    strSql.Append("Passwd=@Passwd,");
    strSql.Append("Sex=@Sex,");
    strSql.Append("UserType=@UserType,");
    strSql.Append("Position=@Position,");
    strSql.Append("Telephone=@Telephone,");
    strSql.Append("Email=@Email,");
    strSql.Append("Address=@Address,");
    strSql.Append("DepartCode=@DepartCode,");
    strSql.Append("SysType=@SysType,");
    strSql.Append("IsForbid=@IsForbid,");
    strSql.Append("Remark=@Remark");
    strSql.Append(" where UserCode=@UserCode");
    SqlParameter[] parameters = {
      new SqlParameter("@UserCode", SqlDbType.VarChar,10),
      new SqlParameter("@UserName", SqlDbType.NVarChar),
      new SqlParameter("@Passwd", SqlDbType.NVarChar),
      new SqlParameter("@Sex", SqlDbType.NVarChar),
      new SqlParameter("@UserType", SqlDbType.VarChar,1),
      new SqlParameter("@Position", SqlDbType.NVarChar),
      new SqlParameter("@Telephone", SqlDbType.NVarChar),
      new SqlParameter("@Email", SqlDbType.NVarChar),
      new SqlParameter("@Address", SqlDbType.NVarChar),
      new SqlParameter("@DepartCode", SqlDbType.VarChar,10),
      new SqlParameter("@SysType", SqlDbType.VarChar,10),
      new SqlParameter("@IsForbid", SqlDbType.VarChar,1),
      new SqlParameter("@Remark", SqlDbType.NVarChar)};
    parameters[0].Value = model.UserCode;
    parameters[1].Value = model.UserName;
    parameters[2].Value = model.Passwd;
    parameters[3].Value = model.Sex;
    parameters[4].Value = model.UserType;
    parameters[5].Value = model.Position;
    parameters[6].Value = model.Telephone;
    parameters[7].Value = model.Email;
    parameters[8].Value = model.Address;
    parameters[9].Value = model.DepartCode;
    parameters[10].Value = model.SysType;
    parameters[11].Value = model.IsForbid;
    parameters[12].Value = model.Remark;

    int iAffected = DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);

            if (iAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
   }
        //修改密码
        public bool UpdatePW(CoalTraffic.Model.T_User model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_User set ");
            strSql.Append("Passwd=@Passwd ");
            strSql.Append(" where UserCode=@UserCode");
            SqlParameter[] parameters = {
      new SqlParameter("@UserCode", SqlDbType.VarChar,10),
      new SqlParameter("@Passwd", SqlDbType.NVarChar)};
            parameters[0].Value = model.UserCode;
            parameters[1].Value = model.Passwd;

            int iAffected = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);

            if (iAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
   ///

   /// 删除一条数据
   ///

   public void Delete(string UserCode)
   {
    StringBuilder strSql=new StringBuilder();
    strSql.Append("delete T_User ");
    strSql.Append(" where UserCode=@UserCode");
    SqlParameter[] parameters = {
      new SqlParameter("@UserCode", SqlDbType.VarChar)
     };
    parameters[0].Value = UserCode;
    DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
   }

   ///

   /// 得到一个对象实体
   ///

   public CoalTraffic.Model.T_User GetModel(string UserCode)
   {
    StringBuilder strSql=new StringBuilder();
    strSql.Append("select * from T_User ");
    strSql.Append(" where UserCode=@UserCode");
    SqlParameter[] parameters = {
      new SqlParameter("@UserCode", SqlDbType.VarChar)};
    parameters[0].Value = UserCode;
    CoalTraffic.Model.T_User model=new CoalTraffic.Model.T_User();
    DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
    model.UserCode=UserCode;
    if(ds.Tables[0].Rows.Count>0)
    {
     model.UserName=ds.Tables[0].Rows[0]["UserName"].ToString();
     model.Passwd=ds.Tables[0].Rows[0]["Passwd"].ToString();
     model.Sex=ds.Tables[0].Rows[0]["Sex"].ToString();
     model.UserType=ds.Tables[0].Rows[0]["UserType"].ToString();
     model.Position=ds.Tables[0].Rows[0]["Position"].ToString();
     model.Telephone=ds.Tables[0].Rows[0]["Telephone"].ToString();
     model.Email=ds.Tables[0].Rows[0]["Email"].ToString();
     model.Address=ds.Tables[0].Rows[0]["Address"].ToString();
     model.DepartCode=ds.Tables[0].Rows[0]["DepartCode"].ToString();
     model.SysType=ds.Tables[0].Rows[0]["SysType"].ToString();
     model.IsForbid=ds.Tables[0].Rows[0]["IsForbid"].ToString();
     model.Remark=ds.Tables[0].Rows[0]["Remark"].ToString();
     return model;
    }
    else
    {
    return null;
    }
   }
   ///

   /// 获得数据列表
   ///

   public DataSet GetList(string strWhere)
   {
    StringBuilder strSql=new StringBuilder();
    strSql.Append("select [UserCode],[UserName],[Passwd],[Sex],[UserType],[Position],[Telephone],[Email],[Address],[DepartCode],[SysType],[IsForbid],[Remark] ");
    strSql.Append(" FROM T_User ");
    if(strWhere.Trim()!="")
    {
     strSql.Append(" where "+strWhere);
    }
    return DbHelperSQL.Query(strSql.ToString());
   }

        public DataSet GetList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select [UserCode],[UserName],[Passwd],[Sex],[UserType],[Position],[Telephone],[Email],[Address],[DepartCode],[SysType],[IsForbid],[Remark] ");
            strSql.Append(" FROM T_User");

            return DbHelperSQL.Query(strSql.ToString());
        }

   #endregion 成员方法
}
}