Dapper 的使用

 

以下範例示範 Dapper 基本的使用

一、資料準備

-- create table
CREATE TABLE [dbo].[student] (
	[student_id] [NVARCHAR](50) NOT NULL
	,[student_name] [NVARCHAR](50) NOT NULL
)

CREATE TABLE [dbo].[score] (
	[score_id] [NVARCHAR](50) NOT NULL
	,[student_id] [NVARCHAR](50) NOT NULL
	,[subject] [NVARCHAR](50) NOT NULL
	,[score] [int] NOT NULL
)

-- create data
INSERT [dbo].[student] ([student_id], [student_name]) VALUES ('9527', N'Bill')
INSERT [dbo].[student] ([student_id], [student_name]) VALUES ('0204', N'Tom')
INSERT [dbo].[score] ([score_id], [student_id], [subject], [score]) VALUES ('1', N'9527', 'english', 90)
INSERT [dbo].[score] ([score_id], [student_id], [subject], [score]) VALUES ('2', N'9527', 'math', 50)
INSERT [dbo].[score] ([score_id], [student_id], [subject], [score]) VALUES ('3', N'0204', 'english', 60)
INSERT [dbo].[score] ([score_id], [student_id], [subject], [score]) VALUES ('4', N'0204', 'math', 30)

二、開一個 .net core ConsoleApp 專案

三、於 NuGet 安裝 dapper 與其他相關套件，指令如下

$ Install-Package Dapper
$ Install-Package Microsoft.Extensions.Configuration.Json

四、程式實作使用 Dapper 技術讀取資料庫

1. 建立資料庫連線

   using Microsoft.Extensions.Configuration;
   using System.Data.SqlClient;
   
   namespace ConsoleApp1
   {
       public class DatabaseContext
       {
           private readonly string _connectionString;
   
           public DatabaseContext(IConfiguration configuration)
           {
               _connectionString = configuration.GetConnectionString("DefaultConnection");
           }
   
           public SqlConnection GetConnection()
           {
               return new SqlConnection(_connectionString);
           }
       }
   }

   而連線參數的 appsettings.json 設定檔內容大致如下

   {
     "ConnectionStrings": {
       "DefaultConnection": "Data Source=instance;Database=test_db;Trusted_Connection=False;MultipleActiveResultSets=true;User ID=sa;Password=pwd"
     }
   }

2. 準備對應到資料表的類別

   namespace ConsoleApp1
   {
       public class StudentScore
       {
           public string student_name { get; set; }
           public string subject { get; set; }
           public int score { get; set; }
       }
   }

3. 建立一 repository ，並使用 Dapper 在資料庫查詢相關資料

   using Dapper;
   
   namespace ConsoleApp1
   {
       public class StudentRepository
       {
           private readonly DatabaseContext _context;
   
           public StudentRepository(DatabaseContext context)
           {
               _context = context;
           }
   
           public IEnumerable<StudentScore> GetStudentScores()
           {
               using (var connection = _context.GetConnection())
               {
                   connection.Open();
                   string query = @"select std.student_name, ss.subject, ss.score 
                                    from student std
                                    join score ss on std.student_id = ss.student_id";
                   return connection.Query<StudentScore>(query);
               }
           }
       }
   }

4. 將該 repository 註冊進服務中

   using ConsoleApp1;
   using Microsoft.Extensions.Configuration;
   using Microsoft.Extensions.DependencyInjection;
   
   class Program
   {
       static void Main(string[] args)
       {
           var configuration = new ConfigurationBuilder()
               .AddJsonFile("appsettings.json")
               .Build();
   
           var services = new ServiceCollection()
               .AddSingleton<IConfiguration>(configuration)
               .AddScoped<DatabaseContext>()
               .AddScoped<StudentRepository>()
               .BuildServiceProvider();
   
           var repository = services.GetRequiredService<StudentRepository>();
           var results = repository.GetStudentScores();
   
           foreach (var s in results)
           {
               Console.WriteLine($"student_name: {s.student_name}, subject: {s.subject}, score: {s.score}");
           }
       }
   }

參考資料：
Dapper 筆記
Dapper ORM