应用SqlHelper例子（userService）
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OA.Model;
using System.Data.SqlClient;
using System.Data;
namespace OA.Dal
{
public class userService
{
/// <summary>
/// 判断用户是否存在
/// </summary>
/// <param name="loginName">登录名</param>
/// <returns></returns>
public bool Exists(string loginName)
{
string sql = "select count(*) from users where loginname=@name";
SqlParameter[] pars ={
new SqlParameter("@name",SqlDbType .VarChar ,50)
};
pars[0].Value = loginName;
if ((int)SqlHelper.ExecuteScalar(sql,pars) > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 管理员添加用户的基本信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddBaseUser(users model)
{
StringBuilder strsql = new StringBuilder();
strsql.Append("insert into users(");
strsql.Append("deptid,loginname,loginpwd,name)");
strsql.Append(" values (");
strsql.Append("@deptid,@loginname,@loginpwd,@name)");
SqlParameter[] parameters = {
new SqlParameter("@deptid", SqlDbType.Int,4),
new SqlParameter("@loginname", SqlDbType.NVarChar,50),
new SqlParameter("@loginpwd", SqlDbType.NVarChar,50),
new SqlParameter("@name", SqlDbType.NVarChar,20) };
parameters[0].Value = model.Deptid;
parameters[1].Value = model.Loginname;
parameters[2].Value = model.Loginpwd;
parameters[3].Value = model.Name;
int result = SqlHelper.ExecuteNonQuery(strsql.ToString(), parameters);
return result;
}
/// <summary>
/// 删除用户信息
/// </summary>
/// <param name="id"></param>
public int Delete(users model)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("delete from users ");
strSql.Append(" where id=@id ");
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.Int,4)};
parameters[0].Value = model.Id;
int result=SqlHelper .ExecuteNonQuery(strSql.ToString(),parameters);
return result;
}
/// <summary>
/// 根据登录名id获取部门类型id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string GetDeptid(int id)
{
string sql = "select deptid from users where id=@id";
SqlParameter[] pars ={
new SqlParameter("@id",SqlDbType.Int,4)
};
pars[0].Value = id;
object result = SqlHelper.ExecuteScalar(sql, pars);
if (result != null)
return result.ToString();
return null;
}
/// <summary>
/// 批量删除用户信息
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int Deletes(string[] ids)
{
if (ids.Length == 0)
{
return 0;
}
else
{
int result = 0;
for (int i = 0; i < ids.Length; i++)
{
string sql = "delete from users where id=@id ";
SqlParameter[] pars ={
new SqlParameter("@id",SqlDbType.Int)
};
pars[0].Value = ids[i];
result = SqlHelper.ExecuteNonQuery(sql, pars);
if (result != 1)
{
return 0;
}
}
return 1;
}
}
/// <summary>
/// 修改用户信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(users model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update users set ");
strSql.Append("deptid=@deptid,");
strSql.Append("loginname=@loginname,");
strSql.Append("name=@name,");
strSql.Append(" where id=@id ");
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.Int,4),
new SqlParameter("@deptid", SqlDbType.Int,4),
new SqlParameter("@loginname",SqlDbType.NVarChar,50),
new SqlParameter("@name", SqlDbType.NVarChar,20)};
parameters[0].Value = model.Id;
parameters[1].Value = model.Deptid;
parameters[2].Value = model.Loginname;
parameters[3].Value = model.Name;
int result = SqlHelper.ExecuteNonQuery(strSql.ToString(), parameters);
return result;
}
/// <summary>
/// 修改用户密码
/// </summary>
/// <param name="loginname"></param>
/// <param name="user"></param>
/// <returns></returns>
public int UpdatePwd(string loginname, users user)
{
string sql = "update users set loginpwd=@pwd where loginname=@name";
SqlParameter[] pars ={
new SqlParameter("@pwd",SqlDbType.NVarChar,50),
new SqlParameter("@name",SqlDbType.NVarChar,50)
};
pars[0].Value = user.Loginpwd;
pars[1].Value = loginname;
int result= SqlHelper.ExecuteNonQuery(sql, pars);
return result;
}
/// <summary>
/// 记住上次登入的时间
/// </summary>
/// <param name="muser"></param>
/// <returns></returns>
public int GetLastTime(Model.users model)
{
StringBuilder sql = new StringBuilder();
sql.Append("update users set lastlogintime=@time where id=@id");
SqlParameter[] pars ={
new SqlParameter("@time",SqlDbType.DateTime,16),
new SqlParameter("@id",SqlDbType.Int,4)
};
pars[0].Value = model.Lastlogintime;
pars[1].Value = model.Id;
int result = SqlHelper.ExecuteNonQuery(sql.ToString(), pars);
return result;
}
/// <summary>
/// 获取用户列表
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public List<users> getAllUser(string where)
{
string sql = "select * from users where 1=1";
if (where.Length > 0)
{
sql += where;
}
List<users> list = new List<users>();
SqlDataReader dr = SqlHelper.ExecuteReader(sql);
lock (dr)
{
if (dr.HasRows)
{
while (dr.Read())
{
users model = new users()
{
Id = dr.GetInt32(0),
Deptid =dr[1]==System.DBNull.Value?0: dr.GetInt32(1),
Loginname = dr[2] == System.DBNull.Value ? "" : dr.GetString(2),
Loginpwd = dr[3] == System.DBNull.Value ? "" : dr.GetString(3),
Name =dr[4]==System.DBNull.Value?"": dr.GetString(4)
};
list.Add(model);
}
dr.Close();
}
dr.Dispose();
return list;
}
}
/// <summary>
/// 分页获取列表
/// </summary>
/// <param name="curpage"></param>
/// <param name="pagecount"></param>
/// <returns></returns>
public List<users> getUserByPage(int curpage, int pagecount)
{
List<users> list = new List<users>();
string sql = null;
SqlParameter[] pars ={
new SqlParameter("@curpage",SqlDbType.Int),
new SqlParameter("@pagecount",SqlDbType.Int)
};
pars[0].Value = curpage;
pars[1].Value = pagecount;
SqlDataReader dr = null;
users user = null;
sql = "exec proc_page_user " + curpage + "," + pagecount;
dr = SqlHelper.ExecuteReader(sql, pars);
if (dr.HasRows)
{
while (dr.Read())
{
user = new users()
{
Id = dr.GetInt32(0),
Deptid = dr[1] == System.DBNull.Value ? 0 : dr.GetInt32(1),
Loginname = dr[2] == System.DBNull.Value ? "" : dr.GetString(2),
Loginpwd = dr[3] == System.DBNull.Value ? "" : dr.GetString(3),
Name = dr[4] == System.DBNull.Value ? "" : dr.GetString(4)
};
list.Add(user);
}
dr.Close();
}
dr.Dispose();
return list;
}
/// <summary>
/// 传值
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string GetNameById(int id)
{
string sql = "select [name] from users where id=@id";
SqlParameter[] pars ={
new SqlParameter("@id",SqlDbType.Int,4)
};
pars[0].Value = id;
object result = SqlHelper.ExecuteScalar(sql, pars);
if (result != null)
return result.ToString();
return null;
}
/// <summary>
/// 根据用户名获取部门id
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public string GetDeptID(string name)
{
string sql = "select deptid from users where name=@name";
SqlParameter[] pars ={
new SqlParameter("@name",SqlDbType.VarChar,50)
};
pars[0].Value = name;
object result = SqlHelper.ExecuteScalar(sql, pars);
if (result != null)
return result.ToString();
return null;
}
}
}
转自：http://hi.baidu.com/wjr_loves/blog/item/cd64b26614aaf2cae6113a05.html
