001-ADO.NET

Web.config

  <connectionStrings>
    <add name="connStr" connectionString="server=.;database=MyWeb;uid=sa;pwd=123"/>
  </connectionStrings>

SqlHelper.cs

    /// <summary>
    /// SqlHelper(数据库帮助类)
    /// </summary>
    public static class SqlHelper
    {
        /// <summary>
        /// SQL连接字符串
        /// </summary>
        private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        /// <summary>
        /// 获取数据库列表数据
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="ps">SQL参数</param>
        /// <returns>返回DataTable</returns>
        public static DataTable GetList(string sql, params SqlParameter[] ps)
        {
            //创建Sql连接对象(参数:连接字符串)
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //填充DataSet和更新SQL Server数据库(一组数据命令,一个数据库连接)
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                //设置存储过程,在数据源中选择记录
                sda.SelectCommand.Parameters.AddRange(ps);
                //表示内存中数据的一个表
                DataTable dt = new DataTable();
                //添加或刷新指定范围中的行DataSet,以匹配中使用数据源的那些DataTable名称
                sda.Fill(dt);
                //返回表数据
                return dt;
            }
        }
        /// <summary>
        /// 执行SQL查询,并返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="ps">SQL参数</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)
        {
            //创建Sql连接对象(参数:连接字符串)
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //表示对SQL Server数据库执行Sql语句或存储过程
                SqlCommand cmd = new SqlCommand(sql, conn);
                //对命令传入参数
                cmd.Parameters.AddRange(ps);
                //打开数据库连接
                conn.Open();
                //执行Sql语句,返回受影响的行数
                return cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 执行查询,返回结果集中第一行的第一列
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="ps">SQL参数</param>
        /// <returns>返回结果集第一行的第一列</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] ps)
        {
            //创建Sql连接对象(参数:连接字符串)
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //对SQL Server数据库执行Sql语句
                SqlCommand cmd = new SqlCommand(sql, conn);
                //传入命令参数
                cmd.Parameters.AddRange(ps);
                //打开数据库
                conn.Open();
                //返回结果集第一行的第一列
                return cmd.ExecuteScalar();
            }
        }
    }

CantusDal.cs

    /// <summary>
    /// Lrc歌词数据层
    /// </summary>
    public class CantusDal : ICantusDal
    {
        /// <summary>
        /// 获取数据库列表数据.Dal
        /// </summary>
        /// <returns></returns>
        public List<Cantus> GetList()
        {
            //sql查询语句
            string sql = "select * from cantus";
            //创建内存数据库表,接收查询到的数据
            DataTable dt = SqlHelper.GetList(sql);
            //创建List<Cantus>泛型集合
            List<Cantus> list = new List<Cantus>();
            //循环遍历(DataTable每一行数据)
            foreach (DataRow row in dt.Rows)
            {
                //list集合添加数据单元
                list.Add(new Cantus()
                {
                    Id = Convert.ToInt32(row["Id"]),//Id(id)
                    Serial = Convert.ToByte(row["Serial"]),//Serial(曲目)
                    Song = row["Song"].ToString(),//Song(歌词)
                    Album = row["Album"].ToString(),//Album(专辑)
                    Number = (short)row["Number"],//Number(张数)
                    Date = Convert.ToDateTime(row["Date"]),//Date(年份)
                    Writer = row["Writer"].ToString(),//Writer(作词)
                    Composer = row["Composer"].ToString(),//Composer(作曲)
                    Arranger = row["Arranger"].ToString(),//Arranger(编曲)
                    Singing = row["Singing"].ToString(),//Singing(原唱)
                    Lyric = row["Lyric"].ToString()//Lyric(歌词)
                });
            }
            return list;
        }
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <returns></returns>
        public bool Add(Cantus ct)
        {
            string sql = "insert into cantus values(@Serial,@Song,@Album,@Number,@Date,@Writer,@Composer,@Arranger,@Singing,@Lyric)";
            SqlParameter[] ps = {
                new SqlParameter("@Serial",ct.Serial),
                new SqlParameter("@Song",ct.Song),
                new SqlParameter("@Album",ct.Album),
                new SqlParameter("@Number",ct.Number),
                new SqlParameter("@Date",ct.Date),
                new SqlParameter("@Writer",ct.Writer),
                new SqlParameter("@Composer",ct.Composer),
                new SqlParameter("@Arranger",ct.Arranger),
                new SqlParameter("@Singing",ct.Singing),
                new SqlParameter("@Lyric",ct.Lyric),
            };
            return SqlHelper.ExecuteNonQuery(sql, ps) > 0;
        }
    }

CantusBll.cs

    /// <summary>
    /// 业务逻辑层.BLL
    /// </summary>
    public class CantusBll : ICantusBll
    {
        /// <summary>
        /// 声明Dal操作对象
        /// </summary>
        private CantusDal ctDal;
        /// <summary>
        /// 构造函数()
        /// </summary>
        public CantusBll()
        {
            //创建Dal操作对象
            ctDal = new CantusDal();
        }
        /// <summary>
        /// 返回数据层获取的数据
        /// </summary>
        /// <returns></returns>
        public List<Cantus> GetList()
        {
            return ctDal.GetList();
        }
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="ct"></param>
        /// <returns></returns>
        public bool Add(Cantus ct)
        {
            return ctDal.Add(ct);
        }
    }

BackController.cs

    public class BackController : Controller
    {
        CantusBll ctBll = new CantusBll();
        // GET: Back
        public ActionResult Index()
        {
            List<Cantus> ct = ctBll.GetList();
            JavaScriptSerializer js = new JavaScriptSerializer();
            string result = js.Serialize(ct);
            Response.Write(result);
            return View();
        }
    }