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 成员方法
}
}