.Net Core Project 從零開始 — 資料庫存取利器Dapper

ORM: 全名為Object Relationship Mapping(物件關聯對應)，在進行物件上的操作使用的是強型別的模式，而非弱型別。

Dapper 在資料庫存取上幫了很大的忙，提供了新增、刪除、修改、查詢的Method，還有在查詢取出的資料時對資料進行對應轉換成強型別的物件。

Step 1 – add library Project: CakeMyBlog.DataAccessLayer Project

Step 2 – add dapper package in CakeMyBlog.DataAccessLayer Project

use .NET CLI

dotnet add package Dapper --version 2.0.30

Step 3 – add DataAccessService.cs for 管理資料庫連線字串(一些比較偏共用的東西)

namespace CakeMyBlog.Platform.DataAccess
{
    public class DataAccessService
    {
        /// <summary>
        /// 連線字串
        /// </summary>
        private static string _connectionStr = "Data Source=(Localdb)\\MSSQLLocalDB;Database=CakeMyBlog;Trusted_Connection=True;MultipleActiveResultSets=true;";

        /// <summary>
        /// 全域的資料庫連線字串
        /// </summary>
        public static string connectionStr { get { return _connectionStr; } }
    }
}

Step 4 – add MemberProvider.cs for get all me

using CakeMyBlog.Platform.DataAccess;
using Dapper;
using Model.DataAccessLayer;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace CakeMyBlog.DataAccessLayer
{
    public class MemberProvider
    {
        /// <summary>
        /// 查詢所有會員資料
        /// </summary>
        /// <returns></returns>
        public List<Member> GetAllMembers()
        {
            List<Member> members = null;

            string sqlCommand = @"
                    SELECT [Id]
                          ,[Name]
                          ,[Email]
                          ,[Gender]
                      FROM [Member]
                    ";

            using (var conn = new SqlConnection(DataAccessService.connectionStr)) {
                members = conn.Query<Member>(sqlCommand).ToList();
            }

            return members;
        }

    }

    
}

Step 5 – add Member.cs model for SQL command query and object relationship mapping

namespace Model.DataAccessLayer
{
    public class Member
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Gender { get; set; }
    }
}

Step 6 – Try and test GetAllMembers method