基于ajax的三层，实现数据库增删改查基础（一DAL）

  三层的组成就是DAL,BLL,UI层和Model层。其中的DAL层是与数据库链接，需要引用Model层，进行对数据的操作，但我们一般在此层不进行数据的处理。BLL层负责引用DAL和MODEL层，在此层对从数据层所得到的数据进行处理，例如，利用ExecuteReader得到的SqlDataReader对象，传到BLL层，我们就需要在此层对其进行遍历。UI层负责界面，引用BLL层和MODEL层，将数据展示或者向BLL层传送用户操作。这就是三层的各层之间的作用。

 
首先，我们先对DAL曾编写，先创建一个空的解决方案，向其中加入一个类库，命名为DAL，现在创建完DAL层，我们就需要对DAL层进行编写了。那么需要实现的常见方法有如下几个（1）查询所有（2）登陆验证（3）查询单个（4）更改数据（5）添加数据

数据库的两个表结构如下

usermoney表的useridm是userMes的USERID的外键，查询不涉及两表查询。下边是DAL查询的方法，有的地方没有在BLL处理，查询时我直接在DAL层将数据处理，返回。

string con = "Data Source=.;Initial Catalog=FirstBig;Integrated Security=True";//声明链接字符串

删除一条数据，传入的参数为Id
        public int deleteOne(int id)
        {
            string sql = "delete from userMes where userid=@userid";
            using (SqlConnection conn=new SqlConnection(con))
            {
                using (SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    conn.Open();
                    SqlParameter p1 = new SqlParameter("@userid", id);
                    cmd.Parameters.Add(p1);
                    int res= cmd.ExecuteNonQuery();
                    return res;
                }

            }
        }

//添加一个用户 参数为用户名和密码
        public int addone(string username,string userpass)
        {
            string insert = "insert into userMes(username,userpass) values(@username,@userpass)";
            using (SqlConnection conn=new SqlConnection(con))
            {
                using (SqlCommand cmd=new SqlCommand(insert,conn))
                {
                    conn.Open();
                    SqlParameter sql1 = new SqlParameter("@username", username);
                    SqlParameter sql2 = new SqlParameter("@userpass", userpass);
                    cmd.Parameters.Add(sql1);
                    cmd.Parameters.Add(sql2);
                    int res= cmd.ExecuteNonQuery();
                    return res;
                }
            }
        }

//修改一个用户
        public int changeOnew(userMes li,int id)
        {
            string sql="update userMes set UserName=@username, userSex=@usersex where UserId=@userid ";
            using (SqlConnection conn=new SqlConnection(con))
            {
                using (SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    SqlParameter[] sq = new SqlParameter[]
                    {
                        new SqlParameter("@username",System.Data.SqlDbType.NVarChar,50)
                        {
                            Value=li.username
                        },
                        new SqlParameter("@usersex",System.Data.SqlDbType.NVarChar,50)
                        {
                            Value=li.userSex
                        },
                        new SqlParameter("@userid",System.Data.SqlDbType.NVarChar,50)
                        {
                            Value=id
                        }
                    };
                    conn.Open();
                    cmd.Parameters.AddRange(sq);
                   int res= cmd.ExecuteNonQuery();
                   return res;
                }
            }
        
        }

//查询页数
        public int selectCount()
        {
            string sql = "select COUNT(*) from userMes";
            using (SqlConnection conn=new SqlConnection(con))
            {
                using (SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    conn.Open();
                   int res= cmd.ExecuteNonQuery();
                   return res;
                }
            }
        }

//查询所有数据
        public List<userMes> selectAll()
        {
            List<userMes> li = new List<userMes>();
            string sql="select * from userMes";
            using (SqlConnection conn=new SqlConnection(con))
            {
                using (SqlCommand cmd =new SqlCommand(sql,conn))
                {
                    conn.Open();
                   SqlDataReader sqr= cmd.ExecuteReader();
                   userMes u = null;
                   if (sqr.HasRows)
                   {
                       while (sqr.Read())
                       {
                           u=new userMes();
                           u.id =Convert.ToInt32(sqr["userid"]);
                           u.username =sqr["username"].ToString();
                           u.userSex = sqr["userSex"].ToString();
                           li.Add(u);
                       }
                   }   
                   return li;
                }
            }
        }

//这个暂时没用上，还没有时间去做分页

 public List<userMes> selectMes(int pageCount, int pageSize)
        {
            string sql = "select top(@pageSize) * from userMes where userid not in (select top(5*(@pageCount-1)) UserId from userMes order by UserId) order by UserId";
            using (SqlConnection conn=new SqlConnection(con))
            {
                using (SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    conn.Open();
                    SqlParameter sq1 = new SqlParameter("@pageSize", pageSize);
                    SqlParameter sq2 = new SqlParameter("@pageCount", pageCount);
                    cmd.Parameters.Add(sq1);
                    cmd.Parameters.Add(sq2);
                    SqlDataReader sqr= cmd.ExecuteReader();
                    userMes u = null;
                    List<userMes> li = new List<userMes>();
                    if (sqr.HasRows)
                    {
                        while (sqr.Read())
                        {
                            u = new userMes();
                            u.id = Convert.ToInt32(sqr["id"]);
                            u.username = sqr["username"].ToString();
                            u.userSex = sqr["userSex"].ToString();
                            li.Add(u);
                        }
                    }
                    return li;
                }
            }
        }

以上为DAL层中的代码，其实可以去创建一个sqlHelper类，调用这个类进行操作，下一篇文章会写这个类的具体代码。

Model层的代码很简单，对应数据库里的列名去创建就好。这个是对应于第一个表的一个Model

  public class userMes
    {
        public int id { get; set; }
        public string username { get; set; }
        public string userSex { get; set; }
    }