一些记录，已备后用

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];        }    }}