1.[ASP.NET wbfom 三层： 小牛之路-1] ：三层架构基于JQuery Ui实现增删改查完整设计（完整1）
1、数据访问层的抽象公共类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Common; using System.Data; namespace ThreeLevelDAL { /// <summary> /// 数据访问层的抽象公共类 /// </summary> public abstract class DataAccess { private string connectionString = ""; /// <summary> /// 连接数据库字符串 /// </summary> protected string ConnectionString { get { return connectionString; } set { connectionString = value; } } protected int ExecuteNonQuery(DbCommand cmd) { return cmd.ExecuteNonQuery(); } protected IDataReader ExecuteReader(DbCommand cmd) { return cmd.ExecuteReader(); } protected object ExecuteScalar(DbCommand cmd) { return cmd.ExecuteScalar(); } } }
2、用户类的抽象基类
using System; using System.Collections.Generic; using System.Text; using System.Configuration; using System.Data; namespace ThreeLevelDAL { /// <summary> /// 用户类的抽象基类 /// </summary> public abstract class UsersDataAccess:DataAccess { /// <summary> /// 用户类的构造函数 /// </summary> public UsersDataAccess() { this.ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString; } /// <summary> /// 得到所有的用户集合 /// </summary> /// <returns></returns> public abstract List<ThreeLevelMODEL.Users> GetUsers(); /// <summary> /// 根据编号得到用户的信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public abstract ThreeLevelMODEL.Users GetUserId(int id); /// <summary> /// 增加用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public abstract int UserInsert(ThreeLevelMODEL.Users user); /// <summary> /// 修改用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public abstract int UserUpdate(ThreeLevelMODEL.Users user); /// <summary> /// 删除用户 /// </summary> /// <param name="id"></param> /// <returns></returns> public abstract int UserDelete(int id); /// <summary> /// 分页得到用户信息 /// </summary> /// <param name="PageSize"></param> /// <param name="PageCount"></param> /// <param name="PageNo"></param> /// <param name="RecordCount"></param> /// <returns></returns> public abstract List<ThreeLevelMODEL.Users> UserGetList(int PageSize, ref int PageCount, int PageNo, ref int RecordCount); /// <summary> /// 得到用户信息 /// </summary> /// <param name="reader"></param> /// <returns></returns> protected virtual ThreeLevelMODEL.Users GetUsersReader(IDataReader reader) { return new ThreeLevelMODEL.Users( int.Parse(reader["Id"].ToString()), reader["UserName"].ToString(), reader["PassWord"].ToString(), bool.Parse(reader["Sex"].ToString())); } /// <summary> /// 得到用户信息的集合 /// </summary> /// <param name="reader"></param> /// <returns></returns> protected virtual List<ThreeLevelMODEL.Users> GetUsersCollectionReader(IDataReader reader) { List<ThreeLevelMODEL.Users> users = new List<ThreeLevelMODEL.Users>(); while (reader.Read()) users.Add(GetUsersReader(reader)); return users; } } }
3、用户类数据处理
using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Data; namespace ThreeLevelDAL { /// <summary> /// 用户类数据处理 /// </summary> public class User:UsersDataAccess { /// <summary> /// 得到所有的用户集合 /// </summary> /// <returns></returns> public override List<ThreeLevelMODEL.Users> GetUsers() { using (SqlConnection cn = new SqlConnection(this.ConnectionString)) { SqlCommand cmd = new SqlCommand("Users_GetAll", cn); cmd.CommandType = CommandType.StoredProcedure; cn.Open(); return GetUsersCollectionReader(ExecuteReader(cmd)); } } /// <summary> /// 根据编号得到用户的信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public override ThreeLevelMODEL.Users GetUserId(int id) { using (SqlConnection cn = new SqlConnection(this.ConnectionString)) { SqlCommand cmd = new SqlCommand("Users_GetId", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id; cn.Open(); IDataReader reader = ExecuteReader(cmd); if (reader.Read()) return GetUsersReader(reader); else return null; } } /// <summary> /// 增加用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public override int UserInsert(ThreeLevelMODEL.Users user) { using (SqlConnection cn = new SqlConnection(this.ConnectionString)) { SqlCommand cmd = new SqlCommand("Users_Insert", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserName", SqlDbType.VarChar,50).Value = user.UserName; cmd.Parameters.Add("@PassWord", SqlDbType.VarChar,50).Value = user.PassWord; cmd.Parameters.Add("@Sex", SqlDbType.Bit, 1).Value = user.Sex; cn.Open(); return ExecuteNonQuery(cmd); } } /// <summary> /// 修改用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public override int UserUpdate(ThreeLevelMODEL.Users user) { using (SqlConnection cn = new SqlConnection(this.ConnectionString)) { SqlCommand cmd = new SqlCommand("Users_Update", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = user.Id; cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = user.UserName; cmd.Parameters.Add("@PassWord", SqlDbType.VarChar, 50).Value = user.PassWord; cmd.Parameters.Add("@Sex", SqlDbType.Bit, 1).Value = user.Sex; cn.Open(); return ExecuteNonQuery(cmd); } } /// <summary> /// 删除用户 /// </summary> /// <param name="id"></param> /// <returns></returns> public override int UserDelete(int id) { using (SqlConnection cn = new SqlConnection(this.ConnectionString)) { SqlCommand cmd = new SqlCommand("Users_Delete", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id; cn.Open(); return ExecuteNonQuery(cmd); } } /// <summary> /// 分页得到用户信息 /// </summary> /// <param name="PageSize"></param> /// <param name="PageCount"></param> /// <param name="PageNo"></param> /// <param name="RecordCount"></param> /// <returns></returns> public override List<ThreeLevelMODEL.Users> UserGetList(int PageSize, ref int PageCount, int PageNo, ref int RecordCount) { using (SqlConnection cn = new SqlConnection(this.ConnectionString)) { using (SqlDataAdapter adapter = new SqlDataAdapter("Users_PageList", cn)) { adapter.SelectCommand.CommandType = CommandType.StoredProcedure; adapter.SelectCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize; adapter.SelectCommand.Parameters.Add("@PageCount", SqlDbType.Int).Value = PageCount; adapter.SelectCommand.Parameters["@PageCount"].Direction = ParameterDirection.Output; adapter.SelectCommand.Parameters.Add("@PageNo", SqlDbType.Int).Value = PageNo; adapter.SelectCommand.Parameters.Add("@RecordCount", SqlDbType.Int).Value = RecordCount; adapter.SelectCommand.Parameters["@RecordCount"].Direction = ParameterDirection.Output; cn.Open(); adapter.SelectCommand.ExecuteNonQuery(); PageCount = int.Parse(adapter.SelectCommand.Parameters["@PageCount"].Value.ToString()); RecordCount = int.Parse(adapter.SelectCommand.Parameters["@RecordCount"].Value.ToString()); using (DataTable table = new DataTable()) { adapter.Fill(table); List<ThreeLevelMODEL.Users> user = new List<ThreeLevelMODEL.Users>(); for (int i = 0; i < table.Rows.Count; i++) { user.Add(new ThreeLevelMODEL.Users(int.Parse(table.Rows[i]["Id"].ToString()),table.Rows[i]["UserName"].ToString(),table.Rows[i]["PassWord"].ToString(),bool.Parse(table.Rows[i]["Sex"].ToString()))); } return user; } } } } } }
4、业务逻辑层
using System; using System.Collections.Generic; using System.Text; namespace ThreeLevelBLL { /// <summary> /// 用户类业务逻辑 /// </summary> public class Users { private readonly ThreeLevelDAL.User DAL = new ThreeLevelDAL.User(); /// <summary> /// 得到所有的用户集合 /// </summary> /// <returns></returns> public List<ThreeLevelMODEL.Users> GetUsers() { return DAL.GetUsers(); } /// <summary> /// 根据编号得到用户的信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public ThreeLevelMODEL.Users GetUserId(int id) { return DAL.GetUserId(id); } /// <summary> /// 增加用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public int UserInsert(ThreeLevelMODEL.Users user) { return DAL.UserInsert(user); } /// <summary> /// 修改用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public int UserUpdate(ThreeLevelMODEL.Users user) { return DAL.UserUpdate(user); } /// <summary> /// 删除用户 /// </summary> /// <param name="id"></param> /// <returns></returns> public int UserDelete(int id) { return DAL.UserDelete(id); } /// <summary> /// 分页得到用户信息 /// </summary> /// <param name="PageSize"></param> /// <param name="PageCount"></param> /// <param name="PageNo"></param> /// <param name="RecordCount"></param> /// <returns></returns> public List<ThreeLevelMODEL.Users> UserGetList(int PageSize, ref int PageCount, int PageNo, ref int RecordCount) { return DAL.UserGetList(PageSize, ref PageCount, PageNo, ref RecordCount); } } }
5、实体层
using System; using System.Collections.Generic; using System.Text; namespace ThreeLevelMODEL { /// <summary> /// 用户类实体层 /// </summary> public class Users { /// <summary> /// 默认构造函数 /// </summary> public Users() { } /// <summary> /// 构造函数重载实现带参 /// </summary> /// <param name="id">编号</param> /// <param name="username">用户名</param> /// <param name="password">密码</param> /// <param name="sex">性别</param> public Users(int id,string username,string password,bool sex) { this.id = id; this.username = username; this.password = password; this.sex = sex; } private int id = 0; /// <summary> /// 编号 /// </summary> public int Id { get { return id; } set { id = value; } } private string username = ""; /// <summary> /// 用户名 /// </summary> public string UserName { get { return username; } set { username = value; } } private string password = ""; /// <summary> /// 密码 /// </summary> public string PassWord { get { return password; } set { password = value; } } private bool sex = false; /// <summary> /// 性别 /// </summary> public bool Sex { get { return sex; } set { sex = value; } } } }
6、表
CREATE TABLE [dbo].[Users]( [Id] [int] IDENTITY(1,1) NOT NULL, [UserName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, [PassWord] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, [Sex] [bit] NULL, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自动增加ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users', @level2type=N'COLUMN', @level2name=N'Id' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户名' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users', @level2type=N'COLUMN', @level2name=N'UserName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users', @level2type=N'COLUMN', @level2name=N'PassWord' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users', @level2type=N'COLUMN', @level2name=N'Sex'
7、存储过程
CREATE PROCEDURE [dbo].[Users_GetAll] AS SELECT * FROM USERS CREATE PROCEDURE [dbo].[Users_Delete] @Id int AS DELETE FROM Users WHERE Id=@Id CREATE PROCEDURE [dbo].[Users_GetId] @Id int AS SELECT * FROM USERS WHERE Id = @Id CREATE PROCEDURE [dbo].[Users_Insert] @UserName Varchar(50), @PassWord Varchar(50), @Sex bit AS INSERT INTO USERS(UserName,PassWord,Sex) VALUES(@UserName,@PassWord,@Sex) CREATE PROCEDURE [dbo].[Users_PageList] @PageSize int, @PageCount int output, @PageNo int , @RecordCount int output AS SELECT @RecordCount = COUNT(*) FROM Users IF(@RecordCount%@PageSize = 0) BEGIN SET @PageCount = @RecordCount/@PageSize END ELSE BEGIN SET @PageCount = @RecordCount/@PageSize+1 END SELECT TOP (@PageSize) * FROM Users WHERE Id NOT IN(SELECT TOP((@PageNo-1)*(@PageSize)) Id FROM Users) SET NOCOUNT ON CREATE PROCEDURE [dbo].[Users_Update] @Id Int, @UserName Varchar(50), @PassWord Varchar(50), @Sex bit AS UPDATE USERS SET UserName=@UserName,PassWord=@PassWord,Sex=@Sex WHERE Id=@Id
8、表示层
