【Unit Test】針對Repository做單元測試 (二)

曾上一篇，開始在我們的Production專案寫一段跟DB要資料的程式吧。(註 : 這邊不考慮分層與物件導向問題，一切專注在單元測試上)

首先先在專案中加入EntityFramework，並把Northwind的Employees Table加進來

[](https://4.bp.blogspot.com/-rgwWYtv-ueQ/V5rwi6Q1TII/AAAAAAAAHyQ/uRS4J_82bxsDX5wZNtqjqoA8hjVtRUjcQCLcB/s1600/1.png)

[](https://2.bp.blogspot.com/-yQ-t7YWi-qI/V5rwi006AII/AAAAAAAAHyY/1ftZ1r6NWgQVEmiKy4uhK84XBGa4le3TQCLcB/s1600/2.png)

[](https://2.bp.blogspot.com/-eUqI2lewHEY/V5rwi_fRLXI/AAAAAAAAHyU/D-s4l_BrxWE2XOmsvflt-8s-Uo2arn95gCLcB/s1600/3.png)

[](https://2.bp.blogspot.com/-dbz2xrn6Veo/V5rwjJrehdI/AAAAAAAAHyc/zTVkWvQnVoMUaEVjPiL9TwqtZhTwYxeyQCLcB/s1600/4.png)

[](https://3.bp.blogspot.com/-gY9k8ego5EE/V5rwjPDRHwI/AAAAAAAAHyg/7ICoznJNU74dBS7VPVp56-He_BOoF6HxwCLcB/s1600/5.png)

[](https://2.bp.blogspot.com/-4HqmVn15BgY/V5rw-Q4Ow3I/AAAAAAAAHyk/kQKue8geuyUfymRmy-mPAhsTIKt_-iPSwCLcB/s1600/6.png)

[](https://3.bp.blogspot.com/-xa7G6erk2fM/V5rxkJIbLSI/AAAAAAAAHys/sTsfBXgEjCAPXHa1C4YXqdZ4VZLEHeSIgCLcB/s1600/1.png)

接著寫一段程式，讓我們帶入ID能順利取回該筆員工資料

[](https://2.bp.blogspot.com/-iG0Vl0uUc0I/V5ryyt4eQoI/AAAAAAAAHy8/DsdR1LF6JycomPbTS1zop1NvFhb1C8olwCLcB/s1600/1.png)

public class EmployeesRepository :IDisposable
    {
        NorthwindEntities DBContext;
        bool disposedValue;
        public EmployeesRepository()
        {
            DBContext = new NorthwindEntities();
            disposedValue = false;
        }

        public Employees Get(int id)
        {
            return DBContext.Employees.FirstOrDefault(x => x.EmployeeID == id);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (DBContext != null)
                    DBContext.Dispose();

                disposedValue = true;
            }
        }

        public void Dispose()
        {
            Dispose(disposedValue);
             GC.SuppressFinalize(this);
        }
    }

改寫HomeController的Index Action，依據帶入的ID取回員工，並顯示於葉面上

[](https://3.bp.blogspot.com/-nqGw1xVRRX0/V5r20E6fPAI/AAAAAAAAHzc/qRcvr7Lr_Os9IrcMr_1XqYnNJYTxKfVsgCLcB/s1600/1.png)

View的部分(一切從簡 XDD)

[](https://4.bp.blogspot.com/-Z3oYtUvXjqo/V5r3L10zYiI/AAAAAAAAHzg/RH5ZJQSpyHk5X8NVMhOpk7JA0rJT95nkQCLcB/s1600/1.png)

來執行看看這段Code有沒有用!!!

[](https://1.bp.blogspot.com/-6bOfbEOFZ_M/V5r3beLjkWI/AAAAAAAAHzo/Q1YmS32wgeMfNPkUeJqFVVQDjRRmu-iKQCLcB/s1600/1.png)

[](https://3.bp.blogspot.com/-NfsCSeUstAo/V5r3ooighWI/AAAAAAAAHzs/R94VplAsQn8XeC3QaeL9euzBNLf8NvPjACLcB/s1600/1.png)

所以這段Code的確可以正常運作，確定之後我們來寫單元測試驗證這件事情。

首先在單元測試專案建立EmployeesRepositoryTests

[](https://3.bp.blogspot.com/-LVviiAdNxxs/V5r4xF2a5NI/AAAAAAAAHz8/8H5uWifglgYlr2hqA9WDZ5aageApPJrYACLcB/s1600/1.png)

上一篇有提到，這邊做的單元測試是每個測試前將準備好的CSV資料匯入LocalDB，做完測試後把資料全部砍掉，所以我們得先寫一段TestInitial讓每次測試之前先跑匯入資料的部分，先在單元測試專把CSVHelper安裝起來。

[](https://2.bp.blogspot.com/--r9VuX4WFbQ/V5r5fTcTc_I/AAAAAAAAH0E/to6r2WGGomsVjlG3sQhFFworO3dAasVqgCLcB/s1600/1.png)

接著寫程式把資料從CSV讀出來，並寫入LocalDB，但首先先將測試專案安裝EntityFramwork套件

[](https://4.bp.blogspot.com/-CbTTIt0AEHI/V5scdqAt-FI/AAAAAAAAH0U/ZiSP3YNeYMwuoUwkZ8dC6iTIq7EMZ8mkACLcB/s1600/1.png)

然後記得把連線字串加到單元測試的專案之中

<connectionStrings>
      <add name="NorthwindEntities" connectionString="metadata=res://*/Models.Northwind.csdl|res://*/Models.Northwind.ssdl|res://*/Models.Northwind.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\TestDB.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

寫以下程式

using System.Text;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using WebApplication4.Models;
using System.IO;
using CsvHelper;
using System.Linq;
using System.Reflection;
using System.Linq;
namespace WebApplication4.Tests
{
    [TestClass]
    public class EmployeesRepositoryTests
    {

        [TestInitialize]
        public void Initial()
        {
            //讀取檔案
            using (StreamReader reader = new StreamReader(
                string.Concat(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), @"\CSVs\Employees.csv"), 
                new UTF8Encoding()))
            using (var csvReader = new CsvReader(reader))
            {
                csvReader.Configuration.WillThrowOnMissingField = false;
                var Employees = csvReader.GetRecords<Employees>().ToList();

                //將資料寫入DB
                var DBContext = new NorthwindEntities();
                DBContext.Employees.AddRange(Employees);
                DBContext.SaveChanges();
            }
        }

        [TestMethod]
        public void Get_帶入ID_應取回該ID的Employee()
        {
            //arrange
            var ID = 0;
            var Sut = new EmployeesRepository();

            var Expected = "Nancy";
            //act
            var actual = Sut.Get(ID);

            //assert
            Assert.AreEqual(Expected, actual.FirstName);
        }
    }
}

執行看看會發現爆掉了!!!!!! 果然事情不是憨人我想的那麼簡單

[](https://1.bp.blogspot.com/--GWQvH-BzPo/V5srR-gwv8I/AAAAAAAAH0k/P2uj3DhlLwI1Q5ZTEPa0_E7CdwyQvMNngCLcB/s1600/1.png)

原因出在於Employee這個Table的ID欄位為流水自動編號，透過EF是無法寫入的，這時候只好透過Dapper下指令解決了，先安裝Dapper

[](https://3.bp.blogspot.com/-pST5pb51uh0/V5srvCR_TVI/AAAAAAAAH0o/rH5mdCRr574aqTB8_sQIQ6S2clZ4bcU1QCLcB/s1600/1.png)

將剛剛從CSVHelper的資料用Dapper塞進去，而且要將Identity Insert打開，先補上要給Dapper用的連線字串

<add name="NorthwindString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\TestDB.mdf;Persist Security Info=True;" providerName="System.Data.SqlClient" />

把程式改成如下

using System.Text;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using WebApplication4.Models;
using System.IO;
using CsvHelper;
using System.Linq;
using System.Reflection;
using System.Data.SqlClient;
using Dapper;
namespace WebApplication4.Tests
{
    [TestClass]
    public class EmployeesRepositoryTests
    {

        [TestInitialize]
        public void Initial()
        {
            //讀取檔案
            using (StreamReader reader = new StreamReader(
                string.Concat(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), @"\CSVs\Employees.csv"), 
                new UTF8Encoding()))
            using (var csvReader = new CsvReader(reader))
            {
                csvReader.Configuration.WillThrowOnMissingField = false;
                var Employees = csvReader.GetRecords<Employees>().ToList();

                //將資料寫入DB
                //var DBContext = new NorthwindEntities();
                //DBContext.Employees.AddRange(Employees);
                //DBContext.SaveChanges();
                using (var cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindString"].ConnectionString))
                {
                    cn.Execute(@"SET IDENTITY_INSERT Employees ON 
                            INSERT INTO Employees (EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,Address,City,Region,PostalCode,Country,HomePhone,Extension,Notes,ReportsTo,PhotoPath) 
                            VALUES (@EmployeeID,@LastName,@FirstName,@Title,@TitleOfCourtesy,@BirthDate,@HireDate,@Address,@City,@Region,@PostalCode,@Country,@HomePhone,@Extension,@Notes,@ReportsTo,@PhotoPath)
                            SET IDENTITY_INSERT Employees OFF",
                          Employees);
                }

            }
        }

        [TestMethod]
        public void Get_帶入ID_應取回該ID的Employee()
        {
            //arrange
            var ID = 1;
            var Sut = new EmployeesRepository();

            var Expected = "Nancy";
            //act
            var actual = Sut.Get(ID);

            //assert
            Assert.AreEqual(Expected, actual.FirstName);
        }
    }
}

執行看看，就會發現終於成功了…….

[](https://1.bp.blogspot.com/-XvPJF-Zf0u8/V5su65wLx5I/AAAAAAAAH04/D4u0LHDen-EHvjOweb7yfV2r9aLUehYmACLcB/s1600/1.png)

最後別忘了前面提到的，每個單元測試應該都是獨立的，所以既然每次都會寫資料進去，當然每次測試完也都要把資料砍掉啦

所以在單元測試的最後補上這個Method，讓他每個單元測試執行完畢後都會清掉資料

```csharp [TestCleanup] public void CleanUp() { using (var cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindString"].ConnectionString)) { cn.Execute(@"Delete Employees"); } }

```

這樣就大功告成了!!!!