/// <summary>
/// 验证用户名和密码
/// </summary>
/// <param name="userID">用户名</param>
/// <param name="password">密码</param>
/// <returns></returns>
public static Model.UserInfo ValidateUserAndPwd(string userID, string password)
{
//string sql = string.Format("select * from UserInfo where UserID='{99}' and Password='******'",userID,password,,,,,);
//参数化sql语句 防止sql注入式攻击
string strSql = "select * from UserInfo where UserID=@UserID and Password=@Password";
SqlParameter[] para =
{
new SqlParameter("UserID", userID),
new SqlParameter("Password", password)
};
DataTable dt = DBHelper.ExecuteSelect(strSql, para);
Model.UserInfo u;
if (dt.Rows.Count > 0)//用户名和密码正确
{
u = new Model.UserInfo();
DataRow dr = dt.Rows[0]; //得到DataTable里面的第一行
u.Cellphone = (string)dr["Cellphone"];
if (dr["DeptID"] != DBNull.Value) //表示在表里面不是null值
{
u.DeptID = (int)dr["DeptID"];
}
u.Password = (string)dr["Password"];
u.UserID = (string)dr["UserID"];
u.UserName = (string)dr["UserName"];
u.UserType = (byte)dr["UserType"];
}
else
{
u = null;
}
return(u);
}
public int Update(Model.UserInfo user)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "update useinfo set password=@passWord,qq=@qq,email=@email,type=@type,userimg=@userImg where username=@userName";
}
}
/// <summary>
/// 登录验证
/// </summary>
/// <param name="UserID">被验证的用户名</param>
/// <param name="Password">被验证的密码</param>
/// <returns></returns>
public static Model.UserInfo UserLogin(string UserID, string Password)
{
string sql = "select * from UserInfo where UserID=@UserID and Password=@Password";
SqlParameter[] para =
{
new SqlParameter("UserID", UserID),
new SqlParameter("Password", Password)
};
DataTable dt = DBHelper.ExecuteSelect(sql, para);
Model.UserInfo u;
if (dt.Rows.Count > 0)
{
u = new Model.UserInfo(); //表示用户名和密码正确
DataRow dr = dt.Rows[0];
u = new Model.UserInfo();
u.Cellphone = (string)dr["Cellphone"];
if (dr["DeptID"] != DBNull.Value)
{
u.DeptID = (int)dr["DeptID"];
}
u.Password = (string)dr["Password"];
u.UserID = (string)dr["UserID"];
u.UserName = (string)dr["UserName"];
u.UserType = (byte)dr["UserType"];
}
else
{
u = null;
}
return(u);
}
public int Add(Model.UserInfo user)
{
using (IDbConnection cn = new MySqlConnection(cns)) {
string sql = "insert into userinfo values(@userName,@passWord,@qq,@email,@type,@userImg);";
return(cn.Execute(sql, user));
}
}
public static int?UserCheck(Model.UserInfo user)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "select 1 from userinfo where username=@username and password=@password";
return(cn.ExecuteScalar <int?>(sql, user));
}
}
public static int Update(Model.UserInfo user)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "update UserInfo set password=@Password,qq=@QQ,email=@Email,type=@Type,userimg=@UserImg where username=@UserName";
return(cn.Execute(sql, user));
}
}
public int Update(Model.UserInfo userInfo user)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "delete from userinfo where username=@username";
return(cn.Execute(sql, new { username = username }));
}
}
public int Update(Model.UserInfo user)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "update userinfo set password,qq=@qq,email=@email,type=@type,userumg=@userimg where username=@username";
return(cn.Execute(sql, user));
}
}
public static int Add(Model.UserInfo user)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "insert into UserInfo values(@UserName,@Password,@QQ,@Email,@Type,@UserImg);";
return(cn.Execute(sql, user));
}
}
//根据用户ID删除用户信息
public static bool DelUserInfo(Model.UserInfo u)
{
string sql = "delete from UserInfo where UserID=@UserID";
SqlParameter[] para =
{
new SqlParameter("UserID", u.UserID)
};
return(DBHelper.ExecuteNonQuery(sql, para));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(Model.UserInfo userInfo)
{
SqlParameter[] parameters =
{
new SqlParameter("@UserName", SqlDbType.NVarChar, 50),
new SqlParameter("@UserPassword", SqlDbType.NVarChar, 50)
};
parameters[0].Value = userInfo.UserName;
parameters[1].Value = userInfo.UserPassword;
return(DAL.DBHelper.SelectToDS("Pro_UserSelectByUserName", CommandType.StoredProcedure, parameters));
}
/// <summary>
/// 实现对用户密码的修改
/// </summary>
/// <param name="u"></param>
/// <returns></returns>
public static bool UpdateUserPwd(Model.UserInfo u)
{
//参数化sql语句
string strSql = "UPDATE [UserInfo] SET [Password] = @Password,[Cellphone] = @Cellphone WHERE [UserID] = @UserID";
SqlParameter[] para =
{
new SqlParameter("UserID", u.UserID),
new SqlParameter("Password", u.Password),
new SqlParameter("Cellphone", u.Cellphone)
};
return(DBHelper.ExecuteNonQuery(strSql, para));
}
/// <summary>
/// 实现对用户管理的修改
/// </summary>
/// <param name="u"></param>
/// <returns></returns>
public static bool UpdateUserInfo(Model.UserInfo u)
{
//参数化sql语句
string strSql = "UPDATE [UserInfo] SET [UserName] = @UserName, [DeptID] = @DeptID,[Cellphone] = @Cellphone, [UserType] = @UserType WHERE [UserID] = @UserID";
SqlParameter[] para =
{
new SqlParameter("UserID", u.UserID),
new SqlParameter("UserName", u.UserName),
new SqlParameter("DeptID", u.DeptID),
new SqlParameter("Cellphone", u.Cellphone),
new SqlParameter("UserType", u.UserType),
};
return(DBHelper.ExecuteNonQuery(strSql, para));
}
//=======================UserManageEdit=====================
/// <summary>
/// 实现对用户信息的添加
/// </summary>
/// <param name="u"></param>
/// <returns></returns>
public static bool InsertUserInfo(Model.UserInfo u)
{
//参数化sql语句
string strSql = "INSERT INTO [UserInfo] ([UserID], [UserName], [DeptID], [Password], [Cellphone], [UserType]) VALUES (@UserID, @UserName, @DeptID, @Password, @Cellphone, @UserType)";
SqlParameter[] para =
{
new SqlParameter("UserID", u.UserID),
new SqlParameter("UserName", u.UserName),
new SqlParameter("DeptID", u.DeptID),
new SqlParameter("Password", u.Password),
new SqlParameter("Cellphone", u.Cellphone),
new SqlParameter("UserType", u.UserType),
};
return(DBHelper.ExecuteNonQuery(strSql, para));
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(Model.UserInfo userInfo)
{
SqlParameter[] parameters =
{
new SqlParameter("@UserName", SqlDbType.NVarChar, 50)
};
parameters[0].Value = userInfo.UserName;
int result = 0;
bool re = false;
result = DAL.DBHelper.ExecuteSql("Pro_UserDelete", CommandType.StoredProcedure, parameters);
if (result > 0)
{
re = true;
}
return(re);
}
/// <summary>
/// 根据userID获取单个用户对象
/// </summary>
/// <param name="userID">用户ID</param>
/// <returns></returns>
public static Model.UserInfo GetSingleUserInfo(string userID)
{
string strSql = "select * from userInfo where UserID=@userID";
SqlParameter[] para =
{
new SqlParameter("UserID", userID)
};
DataTable dt = DBHelper.ExecuteSelect(strSql, para);
DataRow dr = dt.Rows[0]; //得到内存表的第一行数据
Model.UserInfo u = new Model.UserInfo();
u.Cellphone = (string)dr["Cellphone"];
u.DeptID = (int)dr["DeptID"];
u.UserID = (string)dr["UserID"];;
u.UserName = (string)dr["UserName"];;
u.UserType = (byte)dr["UserType"];;
return(u);
}
public bool Update(Model.UserInfo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update UserInfo set ");
strSql.Append(string.Format("U_username='******',U_power='{1}',U_nickname='{2}',", model.U_username, model.U_power, model.U_nickname));
strSql.Append(string.Format("U_mailbox='{0}',U_Role='{1}',U_Comments='{2}'", model.U_mailbox, model.U_Role, model.U_Comments));
strSql.Append(string.Format(" where U_ID={0};", model.U_ID));
int UpdateRes = DbHelperSQL.ExecuteSql(strSql.ToString());
if (UpdateRes > 0)
{
return(true);
}
else
{
return(false);
}
}
public int Add(Model.UserInfo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into UserInfo (U_username,U_password,U_power,U_nickname,U_mailbox,U_Role,U_Comments,U_CreateDate,U_IsDelete) ");
strSql.Append(" values (@U_username,@U_password,@U_power,@U_nickname,@U_mailbox,@U_Role,@U_Comments,@U_CreateDate,@U_IsDelete) ");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@U_username", SqlDbType.VarChar, 50),
new SqlParameter("@U_password", SqlDbType.VarChar, 200),
new SqlParameter("@U_power", SqlDbType.VarChar, 10),
new SqlParameter("@U_nickname", SqlDbType.VarChar, 100),
new SqlParameter("@U_mailbox", SqlDbType.VarChar, 50),
new SqlParameter("@U_Role", SqlDbType.VarChar, 50),
new SqlParameter("@U_Comments", SqlDbType.VarChar),
new SqlParameter("@U_CreateDate", SqlDbType.VarChar, 20),
new SqlParameter("@U_IsDelete", SqlDbType.Int, 4)
};
parameters[0].Value = model.U_username;
parameters[1].Value = model.U_password;
parameters[2].Value = model.U_power;
parameters[3].Value = model.U_nickname;
parameters[4].Value = model.U_mailbox;
parameters[5].Value = model.U_Role;
parameters[6].Value = model.U_Comments;
parameters[7].Value = model.U_CreateDate;
parameters[8].Value = model.U_IsDelete;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
public Model.UserInfo selectuser(string uname, string upsd)
{
Model.UserInfo user = null;
mydb.sqlcom(mydb.openconn());
mydb.com.CommandText = "select * from userinfo where uname=@uname and upsd=@upsd";
mydb.com.Parameters.Add(new SqlParameter("@uname", uname));
mydb.com.Parameters.Add(new SqlParameter("@upsd", upsd));
mydb.sqlread(mydb.com);
if (mydb.read.Read())
{
user = new Model.UserInfo();
user.uid = mydb.read.GetInt32(0);
user.uname = mydb.read.GetString(1);
user.upsd = mydb.read.GetString(2);
user.umail = mydb.read.GetString(3);
}
return(user);
}
public Model.UserInfo selectuser(string name, string password)
{
Model.UserInfo ui = null;
mydb.sqlcom(mydb.openconn());
mydb.com.CommandText = "select * from users where name=@name and password=@password";
mydb.com.Parameters.Add(new SqlParameter("@name", name));
mydb.com.Parameters.Add(new SqlParameter("@password", password));
if (mydb.sqlread(mydb.com).Read())
{
ui = new Model.UserInfo();
ui.id = mydb.read.GetInt32(0);
ui.name = mydb.read.GetString(1);
ui.password = mydb.read.GetString(2);
ui.role = mydb.read.GetString(3);
}
return(ui);
}
/// <summary>
/// 删除用户信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool DelUserInfo1(Model.UserInfo model)
{
string sql = " delete UserInfo where UserID in(" + model.UserID + ")";
return(DBHelper.ExecuteNonQuery(sql, null));
}
/// <summary>
/// 新增用户信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool AddUserInfo(Model.UserInfo model)
{
string sql = "insert into UserInfo values('" + model.UserID + "','" + model.UserName + "'," + model.DeptID + ",'" + model.Password + "','" + model.Cellphone + "'," + model.UserType + ")";
return(DBHelper.ExecuteNonQuery(sql, null));
}
/// <summary>
/// 修改用户信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool UpdateUserInfo(Model.UserInfo model)
{
string sql = "update UserInfo set UserName='******',DeptID='" + model.DeptID + "',CellPhone='" + model.Cellphone + "',UserType='" + model.UserType + "' where UserID ='" + model.UserID + "'";
return(DBHelper.ExecuteNonQuery(sql, null));;
}
/// <summary>
/// 修改部分用户信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool UpdateUserInfo1(Model.UserInfo model)
{
string sql = "update UserInfo set Password='******', CellPhone='" + model.Cellphone + "' where UserID ='" + model.UserID + "'";
return(DBHelper.ExecuteNonQuery(sql, null));;
}