using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MyBookShop.DAL;
using myRole.Models;
namespace myRole.DAL
{
public class UserService
{
/// <summary>
/// 获得所有的用户
/// </summary>
/// <returns></returns>
public static IList<User> GetAllUser()
{
DataTable tbl = DBHelper.GetDataSet("select*from Users");
IList<User> lis = new List<User>();
foreach (DataRow row in tbl.Rows)
{
User user = new User();
user.Uid = Int32.Parse(row["uid"].ToString());
user.Uname = row["uname"].ToString();
user.Upass = row["upass"].ToString();
user.Rid = Int32.Parse(row["rid"].ToString());
lis.Add(user);
}
return lis;
}
/// <summary>
/// 用户登陆
/// </summary>
/// <param name="pass"></param>
/// <param name="uname"></param>
/// <returns></returns>
public static User LoginUser(String pass, String uname)
{
String sql = "select*from Users where uname=@uname and upass=@upass";
SqlParameter[] prr = new SqlParameter[] {
new SqlParameter("@uname",uname),
new SqlParameter("@upass",pass)
};
SqlDataReader reader = DBHelper.GetReader(sql,prr);
User user = new User();
if (reader.Read())
{
user.Uid = Int32.Parse(reader["uid"].ToString());
user.Uname = reader["uname"].ToString();
user.Upass = reader["upass"].ToString();
user.Rid = Int32.Parse(reader["rid"].ToString());
}
reader.Close();
reader.Dispose();
return user;
}
/// <summary>
/// 根据id获取用户信息
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public static User GetUserByUId(int uid)
{
String sql = "select*from Users where Uid=@uid";
SqlParameter[] pp = new SqlParameter[] {
new SqlParameter("@uid",uid)
};
SqlDataReader reader = DBHelper.GetReader(sql, pp);
User user = new User();
if (reader.Read())
{
user.Uid = Int32.Parse(reader["uid"].ToString());
user.Uname = reader["uname"].ToString();
user.Upass = reader["upass"].ToString();
user.Rid = Int32.Parse(reader["rid"].ToString());
}
reader.Close();
reader.Dispose();
return user;
}
/// <summary>
/// 根据角色获取所有的角色所拥有的权限
/// </summary>
/// <param name="rid"></param>
/// <returns></returns>
public static IList<Rules> GetRuleByRoleId(int rid)
{
IList<MidRoleAndRule> midRule= MidServices.GetRoleByRoleId(rid);
IList<Rules> ruleList = new List<Rules>();
foreach (MidRoleAndRule mid in midRule)
{
int ruid=mid.Ruid;//权限信息id
Rules rule=RuleService.GetRuleByRuid(ruid);
ruleList.Add(rule);
}
return ruleList;
}
/// <summary>
/// 修改用户的角色
/// </summary>
/// <param name="roleId"></param>
/// <returns></returns>
public static int UpdateUserRole(int roleId,int uid)
{
String sql="update Users set rid=@rid where Uid=@uid";
SqlParameter[] prar = new SqlParameter[] {
new SqlParameter("@rid",roleId),
new SqlParameter("@uid",uid)
};
return DBHelper.ExecuteCommand(sql,prar);
}
}
}
using System;
using System.Data;
using System.Data.SqlClient;
namespace MyBookShop.DAL
{
public static class DBHelper
{
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = "Data Source=localhost;Initial Catalog=myRole; uid=sa;Password=123";
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static int GetScalar(params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = "Pro_InsertOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
}
}