VS连接SQL server2012的数据访问层的一些数据处理

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using ModaL;
namespace DaL
{
    public class UserDaL
    {
        //连接数据库
        public static string sdr = "Data Source=.;Initial Catalog=DATATABLES;Integrated Security=True";
        SqlConnection conn = new SqlConnection(sdr);
        /// <summary>
        /// 数据显示
        /// </summary>
        /// <returns></returns>
        public List<UserModaL> Show(int lenght,int start ,string name)
        {
            //打开数据库
            conn.Open();
            //建立桥接
            SqlDataAdapter dr = new SqlDataAdapter("select top " + lenght + " * from (select ROW_NUMBER() over (order by id) iid  ,u.* from DS u ) c  where iid>" + start,conn);
            //建立一个空的数据表
            DataTable dt = new DataTable();
            //将数据填充到空的数据表中
            dr.Fill(dt);
            //建立一个空的泛型集合
            List<UserModaL> LIst = new List<UserModaL>();
            //for循环取出数据
            for (var i=0;i<dt.Rows.Count;i++)
            {
                //实例化模型层
                UserModaL modal = new UserModaL();
                //取值赋值
                modal.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
                modal.Name = dt.Rows[i]["Name"].ToString();
                modal.Sex = dt.Rows[i]["Sex"].ToString();
                modal.Age = dt.Rows[i]["Age"].ToString();
                modal.S_id = Convert.ToInt32(dt.Rows[i]["S_id"]);
                //将数据保存到list泛型集合中
                LIst.Add(modal);
            }
            //关闭数据库
            conn.Close();
            //断开桥接
            dr.Dispose();
            //返回list集合到前台界面
            return LIst;

        }/// <summary>
        ///
        /// 获取数据的总行数
        /// </summary>
        /// <returns></returns>
        public int Hang()
        {
            //打开数据库
            conn.Open();
            //编写SQL语句
            string SQL = "SELECT COUNT(1) FROM DS";
            //实例化cmd对象那个
            SqlCommand cmd = new SqlCommand(SQL,conn);
            //接收数据库返回的数据
            int row =Convert.ToInt32(cmd.ExecuteScalar());
            //关闭数据库
            conn.Close();
            //返回数据到前台界面
            return row;
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="D_ID"></param>
        /// <returns></returns>
        public int Del(int D_ID)
        {
            //打开数据库
            conn.Open();
            //编写SQL语句
            string SQL = string.Format("Delete from DS where ID='{0}'", D_ID);
            //实例化cmd对象
            SqlCommand cmd = new SqlCommand(SQL, conn);
            //返回数据到前台
            int row = cmd.ExecuteNonQuery();
            //关闭数据库
            conn.Close();
            //返回
            return row;

        }
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="modal"></param>
        /// <returns></returns>
        public int Add(UserModaL modal)
        {
            //打开数据库
            conn.Open();
            //编写SQL语句
            string SQL = string.Format("insert into DS values('{0}',{1},'{2}','{3}')", modal.Name, modal.S_id, modal.Sex, modal.Age);
            //实例化cmd对象
            SqlCommand cmd = new SqlCommand(SQL,conn);
            //返回受影响的行数
            int row = cmd.ExecuteNonQuery();
            //关闭数据库
            conn.Close();
            //返回数据到前台
            return row;
        }
        /// <summary>
        /// 用户修改
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<UserModaL> Cha(int id)
        {
            //打开数据库
            conn.Open();
            //建立桥接
            SqlDataAdapter dr = new SqlDataAdapter(string.Format("select * from DS where ID={0}",id), conn);
            //建立一个空的数据表
            DataTable dt = new DataTable();
            //将数据填充到空的数据表中
            dr.Fill(dt);
            //建立一个空的泛型集合
            List<UserModaL> LIst = new List<UserModaL>();
            //for循环取出数据
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                //实例化模型层
                UserModaL modal = new UserModaL();
                //取值赋值
                modal.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
                modal.Name = dt.Rows[i]["Name"].ToString();
                modal.Sex = dt.Rows[i]["Sex"].ToString();
                modal.Age = dt.Rows[i]["Age"].ToString();
                modal.S_id = Convert.ToInt32(dt.Rows[i]["S_id"]);
                //将数据保存到list泛型集合中
                LIst.Add(modal);
            }
            //关闭数据库
            conn.Close();
            //断开桥接
            dr.Dispose();
            //返回list集合到前台界面
            return LIst;
        }
    }
}