三层架构保存,更新,查询等一些列方法
业务逻辑层的方法
public class AdminManager { public static List<Admin> GetAdminByNameAndPhone(string name,string phone) { return AdminService.GetAdminByNameAndPhone(name,phone); } public static int UpdateAdmin(Admin admin) { return AdminService.UpdateAdmin(admin); } public static int DeleteAdmin(int id) { return AdminService.DeleteAdminById(id); }
public static List<Admin> GetAllAdmin() { return AdminService.GetAllAdmin(); } public static Admin GetAdminById(int id) { return AdminService.GetAdmin(id); } #region 添加管理员 /// <summary> /// 添加管理员 /// </summary> /// <param name="admin">管理员对象</param> /// <returns>添加之后此管理员的编号</returns> public static int AddAdmin(Admin admin) { //判断是否存在 if (AdminService.GetAdminByLoginId(admin.LoginId) > 0) { return 0; //存在 } return AdminService.AddAdmin(admin); } #endregion #region 管理员登陆 /// <summary> /// 管理员登录 /// </summary> /// <param name="admin">管理员对象</param> /// <returns>登录状态</returns> public static bool Login(ref Admin admin) { admin = AdminService.GetAdminByParas(admin); if (admin.Id != 0) { return true; } return false; } #endregion #region 根据登陆名查询管理员 /// <summary> /// 根据登陆名查询管理员 /// </summary> /// <param name="loginId">登陆名</param> /// <returns>符合条件的管理员个数</returns> public static bool GetAdminByLoginId(string loginId) { int count = AdminService.GetAdminByLoginId(loginId); if (count > 0) return true; return false; } #endregion }
数据访问层
public class AdminService { private const string INSERT = "INSERT INTO Admin VALUES (@LoginId,@LoginPwd,@Name,@Phone);SELECT @@IDENTITY"; private const string UPDATE = "UPDATE Admin SET LoginId=@LoginId,LoginPwd=@LoginPwd,Name=@Name,Phone=@Phone WHERE Id=@Id"; private const string DELETE_BY_ID = "DELETE FROM Admin WHERE Id=@Id"; private const string SELECT_ALL = "SELECT * FROM Admin"; private const string SELECT_BY_ID = "SELECT * FROM Admin WHERE Id=@Id"; private const string SELECT_BY_LOGIN = "SELECT * FROM Admin WHERE LoginId=@LoginId and LoginPwd=@LoginPwd"; private const string SELECT_COUNT_BY_LOGINID = "SELECT COUNT(*) FROM Admin WHERE LoginId=@LoginId"; #region public static List<Admin> GetAdminByNameAndPhone(string name,string phone) { //proc_GetAdminByNamePhone SqlParameter[] pars = new SqlParameter[]{ new SqlParameter("@name",name), new SqlParameter("@phone",phone) }; return GetAdminBySql(CommandType.StoredProcedure, "proc_GetAdminByNamePhone", pars); } #endregion public static int AddAdmin(Admin admin) { SqlParameter[] paras = new SqlParameter[]{ new SqlParameter("@LoginId", admin.LoginId), new SqlParameter("@LoginPwd", admin.LoginPwd), new SqlParameter("@Name", admin.Name), new SqlParameter("@Phone", admin.Phone) }; int id = Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, INSERT, paras)); return id; } public static int UpdateAdmin(Admin admin) { SqlParameter[] paras = new SqlParameter[]{ new SqlParameter("@Id", admin.Id), new SqlParameter("@LoginId", admin.LoginId), new SqlParameter("@LoginPwd", admin.LoginPwd), new SqlParameter("@Name", admin.Name), new SqlParameter("@Phone", admin.Phone) }; int rowCount = Convert.ToInt32(DBHelper.ExecuteNonQuery(CommandType.Text, UPDATE, paras)); return rowCount; } public static int DeleteAdminById(int id) { SqlParameter para = new SqlParameter("@Id", id); int rowCount = Convert.ToInt32(DBHelper.ExecuteNonQuery(CommandType.Text, DELETE_BY_ID, para)); return rowCount; } public static Admin GetAdmin(int id) { SqlParameter paras = new SqlParameter("@Id", id); List<Admin> list = GetAdminBySql(CommandType.Text, SELECT_BY_ID, paras); if (list.Count > 0) { return list[0]; } return new Admin(); } public static List<Admin> GetAllAdmin() { return GetAdminBySql(CommandType.Text, SELECT_ALL, null); } //查询语句中字段为 * 时的通用方法。 private static List<Admin> GetAdminBySql(CommandType commandType, string commandText, params SqlParameter[] paras) { List<Admin> list = new List<Admin>(); using (SqlDataReader reader = DBHelper.ExecuteGetReader(commandType, commandText, paras)) { while (reader.Read()) { Admin admin = new Admin { Id = (int)reader["Id"], LoginId = reader["LoginId"].ToString(), LoginPwd = reader["LoginPwd"].ToString(), Name = reader["Name"].ToString(), Phone = reader["Phone"].ToString() }; list.Add(admin); } } return list; } #region 根据登录名和密码查询管理员 /// <summary> /// 根据登录名和密码查询管理员 /// </summary> /// <param name="admin">用户对象</param> /// <returns>用户对象</returns> public static Admin GetAdminByParas(Admin admin) { SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LoginId",admin.LoginId), new SqlParameter("@LoginPwd",admin.LoginPwd) }; List<Admin> adminList = GetAdminBySql(CommandType.Text, SELECT_BY_LOGIN, paras); if (adminList.Count > 0) return adminList[0]; else return admin; } #endregion #region 根据登陆名查询管理员 /// <summary> /// 根据登陆名查询管理员 /// </summary> /// <param name="loginId">登陆名</param> /// <returns>符合条件的管理员个数</returns> public static int GetAdminByLoginId(string loginId) { SqlParameter para = new SqlParameter("@LoginId", loginId); return Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, SELECT_COUNT_BY_LOGINID, para)); } #endregion }
dephlpe
/// <summary> /// 执行Sql 命令的通用方法 /// </summary> public abstract class SqlHelper { //Database connection strings public static string ConnectionString = "Data Source=.;Initial Catalog=PB_BookShopDemo;Integrated Security=True"; #region ExecuteNonQuery /// <summary> /// 执行sql命令 /// </summary> /// <param name="connectionString"></param> /// <param name="commandType"></param> /// <param name="commandText">sql语句/参数化sql语句/存储过程名</param> /// <param name="commandParameters"></param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrepareCommand(cmd, commandType,conn, commandText, commandParameters); int val = cmd.ExecuteNonQuery(); return val; } } /// <summary> /// 执行Sql Server存储过程 /// </summary> /// <param name="connectionString"></param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues"></param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) { using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, conn, spName, parameterValues); int val = cmd.ExecuteNonQuery(); return val; } } #endregion #region ExecuteReader /// <summary> /// 执行sql命令 /// </summary> /// <param name="connectionString"></param> /// <param name="commandType"></param> /// <param name="commandText"></param> /// <param name="commandParameters"></param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(connectionString); try { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, commandType, conn, commandText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return rdr; } catch { conn.Close(); throw; } } public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues) { SqlConnection conn = new SqlConnection(connectionString); try { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, conn, spName, parameterValues); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return rdr; } catch { conn.Close(); throw; } } #endregion #region ExecuteDataset public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues) { using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, conn, spName, parameterValues); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); da.Fill(ds); return ds; } } } public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, commandType, conn, commandText, commandParameters); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); da.Fill(ds); return ds; } } } #endregion #region ExecuteScalar /// <summary> /// 执行Sql 语句 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="spName">Sql 语句/参数化的sql语句</param> /// <param name="parameterValues">参数</param> /// <returns>执行结果对象</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrepareCommand(cmd, commandType, conn, commandText, commandParameters); object val = cmd.ExecuteScalar(); return val; } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">存储过程参数</param> /// <returns>执行结果对象</returns> public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrepareCommand(cmd, conn, spName, parameterValues); object val = cmd.ExecuteScalar(); return val; } } #endregion #region Private Method /// <summary> /// 设置一个等待执行的SqlCommand对象 /// </summary> /// <param name="cmd">SqlCommand 对象,不允许空对象</param> /// <param name="conn">SqlConnection 对象,不允许空对象</param> /// <param name="commandText">Sql 语句</param> /// <param name="cmdParms">SqlParameters 对象,允许为空对象</param> private static void PrepareCommand(SqlCommand cmd, CommandType commandType, SqlConnection conn, string commandText, SqlParameter[] cmdParms) { //打开连接 if (conn.State != ConnectionState.Open) conn.Open(); //设置SqlCommand对象 cmd.Connection = conn; cmd.CommandText = commandText; cmd.CommandType = commandType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } /// <summary> /// 设置一个等待执行存储过程的SqlCommand对象 /// </summary> /// <param name="cmd">SqlCommand 对象,不允许空对象</param> /// <param name="conn">SqlConnection 对象,不允许空对象</param> /// <param name="spName">Sql 语句</param> /// <param name="parameterValues">不定个数的存储过程参数,允许为空</param> private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, string spName, params object[] parameterValues) { //打开连接 if (conn.State != ConnectionState.Open) conn.Open(); //设置SqlCommand对象 cmd.Connection = conn; cmd.CommandText = spName; cmd.CommandType = CommandType.StoredProcedure; //获取存储过程的参数 SqlCommandBuilder.DeriveParameters(cmd); //移除Return_Value 参数 cmd.Parameters.RemoveAt(0); //设置参数值 if (parameterValues != null) { for (int i = 0; i < cmd.Parameters.Count; i++) { cmd.Parameters[i].Value = parameterValues[i]; } } } #endregion }