TransactionScope

TransactionScope 使用

在 C# 要進行交易時使用 TransactionScope 是一個不錯的選擇，在範圍內的資料都會獲得保護直到交易確認。
使用前須先設置【Windows】啟用 MSDTC 服務與相關設定

TrancsactionScope是.NET中System.Transactions下的一個類別，在範圍內直到執行 TransactionScope.Complete() 之前交易都不會被確認
不必再自行寫出 SqlTransaction 等等冗長的語法

使用範例:

using Dapper;
using Model;
using System.Data.SqlClient;
using System.Linq;
using System.Transactions;

namespace Service
{
    public class TransactionService
    {
        private readonly string sourceConnectionString;
        private readonly string targetConnectionString;

        public TransactionService(string sourceConnectionString, string targetConnectionString)
        {
            this.sourceConnectionString = sourceConnectionString;
            this.targetConnectionString = targetConnectionString;
        }

        public bool MoveStudent(int studentId)
        {
            try
            {
                using (var scope = new TransactionScope())
                {
                    var student = new Student();

                    using (var sqlConn = new SqlConnection(this.sourceConnectionString))
                    {
                        var students = sqlConn.Query<Student>(@"SELECT [Id], [Name] FROM [Student] WHERE [Id] = @Id", new { Id = studentId });

                        if (students == null || students.Count() != 1)
                        {
                            return false;
                        }

                        student = students.Single();

                        sqlConn.Execute(@"DELETE FROM [Student] WHERE [Id] = @Id AND [Name] = @Name", student);
                    }

                    using (var sqlConn = new SqlConnection(this.targetConnectionString))
                    {
                        sqlConn.Execute(@"INSERT INTO [Student]([Id], [Name]) VALUES (@Id, @Name)", student);
                    }

                    // 若在 complete 前發生例外，包含在 scope 內的交易將會被 roll back
                    scope.Complete();

                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
    }
}