public Model.ui_user Login(string acc, string pwd)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ID,account,password,depid,depname,xingming,sex,birth,sfz,tel,dizhi,email,qq,crdate from ui_user ");
strSql.Append(" where account=@account and password=@password ");
SqlParameter[] parameters =
{
new SqlParameter("@account", SqlDbType.NVarChar, 30),
new SqlParameter("@password", SqlDbType.NVarChar, 30)
};
parameters[0].Value = acc;
parameters[1].Value = pwd;
YJUI.Model.ui_user model = new YJUI.Model.ui_user();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(YJUI.Model.ui_user model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update ui_user set ");
strSql.Append("account=@account,");
strSql.Append("password=@password,");
strSql.Append("xingming=@xingming,");
strSql.Append("sex=@sex,");
strSql.Append("birth=@birth,");
strSql.Append("sfz=@sfz,");
strSql.Append("tel=@tel,");
strSql.Append("dizhi=@dizhi,");
strSql.Append("email=@email,");
strSql.Append("qq=@qq ");
// strSql.Append("crdate=@crdate");
strSql.Append(" where ID=@id ");
SqlParameter[] parameters =
{
new SqlParameter("@account", SqlDbType.NVarChar, 30),
new SqlParameter("@password", SqlDbType.NVarChar, 36),
new SqlParameter("@xingming", SqlDbType.NVarChar, 30),
new SqlParameter("@sex", SqlDbType.NVarChar, 1),
new SqlParameter("@birth", SqlDbType.NVarChar, 10),
new SqlParameter("@sfz", SqlDbType.VarChar, 20),
new SqlParameter("@tel", SqlDbType.VarChar, 15),
new SqlParameter("@dizhi", SqlDbType.NVarChar, 100),
new SqlParameter("@email", SqlDbType.VarChar, 50),
new SqlParameter("@qq", SqlDbType.VarChar, 15),
// new SqlParameter("@crdate", SqlDbType.DateTime),
new SqlParameter("@id", SqlDbType.UniqueIdentifier, 16)
};
parameters[0].Value = model.account;
parameters[1].Value = model.password;
parameters[2].Value = model.xingming;
parameters[3].Value = model.sex;
parameters[4].Value = model.birth;
parameters[5].Value = model.sfz;
parameters[6].Value = model.tel;
parameters[7].Value = model.dizhi;
parameters[8].Value = model.email;
parameters[9].Value = model.qq;
// parameters[10].Value = model.crdate;
parameters[10].Value = model.id;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(YJUI.Model.ui_user model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into ui_user(");
strSql.Append("ID,account,password,xingming,sex,birth,sfz,tel,dizhi,email,qq,crdate)");
strSql.Append(" values (");
strSql.Append("@id,@account,@password,@xingming,@sex,@birth,@sfz,@tel,@dizhi,@email,@qq,@crdate)");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.UniqueIdentifier, 16),
new SqlParameter("@account", SqlDbType.NVarChar, 30),
new SqlParameter("@password", SqlDbType.NVarChar, 36),
new SqlParameter("@xingming", SqlDbType.NVarChar, 30),
new SqlParameter("@sex", SqlDbType.NVarChar, 1),
new SqlParameter("@birth", SqlDbType.NVarChar, 10),
new SqlParameter("@sfz", SqlDbType.VarChar, 20),
new SqlParameter("@tel", SqlDbType.VarChar, 15),
new SqlParameter("@dizhi", SqlDbType.NVarChar, 100),
new SqlParameter("@email", SqlDbType.VarChar, 50),
new SqlParameter("@qq", SqlDbType.VarChar, 15),
new SqlParameter("@crdate", SqlDbType.DateTime)
};
parameters[0].Value = Guid.NewGuid();
parameters[1].Value = model.account;
parameters[2].Value = model.password;
parameters[3].Value = model.xingming;
parameters[4].Value = model.sex;
parameters[5].Value = model.birth;
parameters[6].Value = model.sfz;
parameters[7].Value = model.tel;
parameters[8].Value = model.dizhi;
parameters[9].Value = model.email;
parameters[10].Value = model.qq;
parameters[11].Value = model.crdate;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public YJUI.Model.ui_user GetModel(Guid id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ID,account,password,xingming,sex,birth,sfz,tel,dizhi,email,qq,crdate from ui_user ");
strSql.Append(" where id=@id ");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.UniqueIdentifier, 16)
};
parameters[0].Value = id;
YJUI.Model.ui_user model = new YJUI.Model.ui_user();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(YJUI.Model.ui_user model, List <Model.ui_user_role> T_ui_role, List <Model.ui_user_org> T_ui_org)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into ui_user(");
strSql.Append("ID,account,password,xingming,sex,birth,sfz,tel,dizhi,email,qq,crdate)");
strSql.Append(" values (");
strSql.Append("@id,@account,@password,@xingming,@sex,@birth,@sfz,@tel,@dizhi,@email,@qq,@crdate)");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.UniqueIdentifier, 16),
new SqlParameter("@account", SqlDbType.NVarChar, 30),
new SqlParameter("@password", SqlDbType.NVarChar, 36),
new SqlParameter("@xingming", SqlDbType.NVarChar, 30),
new SqlParameter("@sex", SqlDbType.NVarChar, 1),
new SqlParameter("@birth", SqlDbType.NVarChar, 10),
new SqlParameter("@sfz", SqlDbType.VarChar, 20),
new SqlParameter("@tel", SqlDbType.VarChar, 15),
new SqlParameter("@dizhi", SqlDbType.NVarChar, 100),
new SqlParameter("@email", SqlDbType.VarChar, 50),
new SqlParameter("@qq", SqlDbType.VarChar, 15),
new SqlParameter("@crdate", SqlDbType.DateTime)
};
parameters[0].Value = model.id;
parameters[1].Value = model.account;
parameters[2].Value = model.password;
parameters[3].Value = model.xingming;
parameters[4].Value = model.sex;
parameters[5].Value = model.birth;
parameters[6].Value = model.sfz;
parameters[7].Value = model.tel;
parameters[8].Value = model.dizhi;
parameters[9].Value = model.email;
parameters[10].Value = model.qq;
parameters[11].Value = model.crdate;
Hashtable SQLStringList = new Hashtable();
SQLStringList.Add(strSql, parameters);
foreach (Model.ui_user_role model_user_role in T_ui_role)
{
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("insert into ui_user_role(");
strSql1.Append("ui_user_id,ui_role_id,crdate)");
strSql1.Append(" values (");
strSql1.Append("@ui_user_id,@ui_role_id,@crdate)");
strSql1.Append(";select @@IDENTITY");
SqlParameter[] parameters1 =
{
new SqlParameter("@ui_user_id", SqlDbType.UniqueIdentifier, 16),
new SqlParameter("@ui_role_id", SqlDbType.UniqueIdentifier, 16),
new SqlParameter("@crdate", SqlDbType.DateTime)
};
parameters1[0].Value = model_user_role.ui_user_id;
parameters1[1].Value = model_user_role.ui_role_id;
parameters1[2].Value = model.crdate;
SQLStringList.Add(strSql1, parameters1);
}
foreach (Model.ui_user_org model_user_org in T_ui_org)
{
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("insert into ui_user_org(");
strSql2.Append("ui_user_id,ui_org_id,crdate)");
strSql2.Append(" values (");
strSql2.Append("@ui_user_id,@ui_org_id,@crdate)");
strSql2.Append(";select @@IDENTITY");
SqlParameter[] parameters2 =
{
new SqlParameter("@ui_user_id", SqlDbType.UniqueIdentifier, 16),
new SqlParameter("@ui_org_id", SqlDbType.UniqueIdentifier, 16),
new SqlParameter("@crdate", SqlDbType.DateTime)
};
parameters2[0].Value = model_user_org.ui_user_id;
parameters2[1].Value = model_user_org.ui_org_id;
parameters2[2].Value = model.crdate;
SQLStringList.Add(strSql2, parameters2);
}
try
{
DbHelperSQL.ExecuteSqlTran(SQLStringList);
return(true);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public YJUI.Model.ui_user DataRowToModel(DataRow row)
{
YJUI.Model.ui_user model = new YJUI.Model.ui_user();
if (row != null)
{
if (row["ID"] != null && row["ID"].ToString() != "")
{
model.id = new Guid(row["ID"].ToString());
}
if (row["account"] != null)
{
model.account = row["account"].ToString();
}
if (row["password"] != null)
{
model.password = row["password"].ToString();
}
if (row["xingming"] != null)
{
model.xingming = row["xingming"].ToString();
}
if (row["sex"] != null)
{
model.sex = row["sex"].ToString();
}
if (row["depid"] != null)
{
model.depid = row["depid"].ToString();
}
if (row["depname"] != null)
{
model.depname = row["depname"].ToString();
}
if (row["birth"] != null)
{
model.birth = row["birth"].ToString();
}
if (row["sfz"] != null)
{
model.sfz = row["sfz"].ToString();
}
if (row["tel"] != null)
{
model.tel = row["tel"].ToString();
}
if (row["dizhi"] != null)
{
model.dizhi = row["dizhi"].ToString();
}
if (row["email"] != null)
{
model.email = row["email"].ToString();
}
if (row["qq"] != null)
{
model.qq = row["qq"].ToString();
}
if (row["crdate"] != null && row["crdate"].ToString() != "")
{
model.crdate = DateTime.Parse(row["crdate"].ToString());
}
}
return(model);
}
