原理:

　　MiniBlog僅支援XML資料存取，沒有支持SQL Server，但是提供IBlogService介面，可以簡單實作其他類型資料存取。

public interface IBlogService  
{  
    //略...  
    Task SavePost(Post post);  
    //略...  
}  

【第一步】建立SQL Server實作類別(MSSqlBlogService)繼承介面:

public class MSSqlBlogService : IBlogService  

【第二步】實作IBlogService的SavePost方法
邏輯:假如文章已經存在資料庫做更新動作，反之做新增動作。
注意點:
1.因為牽扯到多張表格交易，一定要記得包Transation(因為非同步關係，記得TransactionScopeAsyncFlowOption.Enabled)
2.時間需要使用UTC避免伺服器不同時區問題
3.判斷資料是否存在使用top 1 1方式 (原理可以看這篇【C#、SQL】 : 省略count判斷資料有無,直接查詢1 or Not返回boolean結果(select top 1 1))

public async Task SavePost(Post post)  
{  
    post.LastModified = DateTime.UtcNow;  
    using (var conn = SqlHelper.CreateDefaultConnection())  
    using (var ts = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))  
    {  
        if (conn.Query<bool>("select top 1 1 from post where id = @id", new { @id = post.ID }).SingleOrDefault())  
        {  
            await conn.ExecuteAsync(@"  
                UPDATE Post  
                SET Title = @Title,Slug = @Slug,Excerpt = @Excerpt,Content = @Content,PubDate = @PubDate,LastModified = @LastModified,IsPublished = @IsPublished  
                WHERE ID = @ID  
            ", post);  
            await conn.ExecuteAsync(@"  
                Delete Categories where PostID = @ID   
            ", post);  
            var cats = post.Categories.Select(s => new { PostID = post.ID, Name = s }).ToList();  
            await conn.ExecuteAsync(@"  
               INSERT INTO Categories (PostID ,Name) VALUES (@PostID ,@Name);  
            ", cats);  
        }  
        else  
        {  
            await conn.ExecuteAsync(@"  
                INSERT INTO Post(ID,Title,Slug,Excerpt,Content,PubDate,LastModified,IsPublished)  
                VALUES (@ID,@Title,@Slug,@Excerpt,@Content,@PubDate,@LastModified,@IsPublished)  
            ", post);  
            var cats = post.Categories.Select(s => new { PostID = post.ID, Name = s }).ToList();  
            await conn.ExecuteAsync(@"  
                INSERT INTO Categories (PostID ,Name)   
                VALUES (@PostID ,@Name)  
            ", cats);  
        }  
        ts.Complete();  
    }  
  
    //略...  
}  

【第三步】建立SqlHelper抽象類別方便取得Connection
1.注意這邊使用抽象方法，避免可以被new，直接呼叫方法就可以。

2.假如有大量建立連線的情況，請使用連線池，避免浪費連線資源。

public abstract class SqlHelper  
{  
    public static readonly string connectionString =  
        @"連線字串"  /*可以改成從appsettings.json取得連線字串*/
        ;  
    private static readonly System.Data.Common.DbProviderFactory dbProviderFactory = System.Data.SqlClient.SqlClientFactory.Instance;  
  
    public static IDbConnection CreateDefaultConnection()  
    {  
        var conn = dbProviderFactory.CreateConnection();  
        conn.ConnectionString = connectionString;  
        conn.Open();  
        return conn;  
    }  
}  

【第四步】運行建立表格Script
需要的表格結構很簡單，三張table:文章、留言、Tag類別，以下是ER圖跟DDL:

DDL Script:

CREATE TABLE [Post]([ID] [nvarchar](50) NOT NULL,[Title] [nvarchar](250) NOT NULL,[Slug] [nvarchar](250) NOT NULL,[Excerpt] [nvarchar](250) NOT NULL,[Content] [nvarchar](max) NOT NULL,[PubDate] [datetime] NULL,[LastModified] [datetime] NULL,[IsPublished] [bit] NULL,[IsMarkDown] [bit] NULL,[MarkDownContent] [nvarchar](max) NULL,CONSTRAINT [PK_Post] PRIMARY KEY CLUSTERED ([ID] ASC));  
  
CREATE TABLE [Categories]( [PostID] [nvarchar](50) NOT NULL, [Name] [nvarchar](250) NOT NULL,CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED (  [PostID] ASC,  [Name] ASC));  
  
CREATE TABLE [Comment](  [ID] [nvarchar](50) NOT NULL,  [Author] [nvarchar](50) NOT NULL,  [Email] [nvarchar](50) NOT NULL,  [Content] [nvarchar](250) NOT NULL,  [PubDate] [datetime] NOT NULL,  [IsAdmin] [bit] NOT NULL,  [PostID] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Comment] PRIMARY KEY CLUSTERED (  [ID] ASC));  

【最後一步】接著將實作的類別在Startup.cs的ConfigureServices方法內，使用.NET Core AddSingleton方法依賴注射SQL Server的實作類別

public void ConfigureServices(IServiceCollection services){  
    //略..  
    services.AddSingleton<IBlogService, MSSqlBlogService>();/*SQL-Server*/     
    //略..  
}