使用 LINQPad 快速產生 Table 的 Insert Script

去年曾經向各位介紹如何使用 LINQPad 快速產生相對映 SQL Command 查詢結果的類別，連結如下：

http://kevintsengtw.blogspot.tw/2015/10/dapper-linqpad-sql-command.html

對於使用 Dapper 做為資料讀取異動操作的開發者來說，應該是相當方便，目前我目前的工作環境裡，幾乎新開發的專案裡都已經使用 Dapper 以及快速產生相對映類別的方法。

另外就是有關於單元測試的部分，公司裡的各個開發團隊也陸續在專案裡導入單元測試，不管是應用展示層或是商業邏輯層都會嘗試去做單元測試，唯獨資料存取層的單元測試卻是比較少人會去做，當然資料存取層的單元測試是否要做，這並沒有一定的標準答案，而我所認知的是，只要程式是開發人員去編寫出來的就有必要做測試，但是資料存取層的測試所牽涉到的技術會比較多一些，也關係到資料存取所使用的方式，都是影響要不要做測試的因素。

而這一篇所要介紹的內容也就是在做資料存取層的單元測試時的其中一個環節。

 

---

資料存取層的單元測試，真的很麻煩，要考慮的事情有很多，要做的事前準備工作也很多，如果專案是使用 ORM ( ex: Entity Framework)，那麼就會比較輕鬆一些，可以使用注入資料的方式來處理，而不需要真的與資料庫做到實際的連接就可以完成測試，但如果是使用 ADO.NET 或 Dapper 這類的資料存取的方式進行資料讀取操作的話，做測試就會需要真的與資料庫去做連接。

講到測試所使用的資料庫就會引發一些問題，不管各位的工作環境裡有沒有提供共用的測試或開發資料庫，或者開發人員在自己本機裡去準備開發用的資料庫，這邊必須說的是，前面所說的那些資料庫都無法做為單元測試所用的資料庫。

因為共用的測試或開發資料庫是會有多人不時的去做資料的異動，不管是刪除、更新、新增資料還是對表格、資料庫作結構的更動，當這些行為無時無刻的在進行時，要怎麼做單元測試呢？做出來的單元測試又怎麼能夠穩定與持續的運作呢？

而開發人員的本機資料庫，雖然只有做為開發人員一個人開發時來使用，但也同樣有一樣的問題，資料與資料庫本身的不穩定都是會影響著單元測試。

所以我在專案裡對於資料存取層的單元測試則是會在進行單元測試時用指令碼去建立 LocalDB，甚至於建立 Table、匯入資料等都是在進行單元測試前會先去做的，所以資料存取層的事先準備工作就會相當繁雜與瑣碎。

 

在 SSMS 裡產生 Insert Script

前面囉唆的講了一大堆，直接進入正題，雖然 Table 的 Insert Script 可以在 SSMS 裡用功能選項去取得，但是相信我，你絕對不會想用的，操作如下：

所產生的 Insert Script 內容如下，

USE [Northwind]

GO

INSERT INTO [dbo].[Customers]

           ([CustomerID]

           ,[CompanyName]

           ,[ContactName]

           ,[ContactTitle]

           ,[Address]

           ,[City]

           ,[Region]

           ,[PostalCode]

           ,[Country]

           ,[Phone]

           ,[Fax])

     VALUES

           (<CustomerID, nchar(5),>

           ,<CompanyName, nvarchar(40),>

           ,<ContactName, nvarchar(30),>

           ,<ContactTitle, nvarchar(30),>

           ,<Address, nvarchar(60),>

           ,<City, nvarchar(15),>

           ,<Region, nvarchar(15),>

           ,<PostalCode, nvarchar(10),>

           ,<Country, nvarchar(15),>

           ,<Phone, nvarchar(24),>

           ,<Fax, nvarchar(24),>)

GO

不是說不能用，而是要將這個 Insert Script 能夠應用在程式裡是需要做修改的，在欄位不多的情況下，其實少少的幾次修改並不會覺得累人，但如果一個 Table 的欄位數量是超過 30 個以上，而且有很多 Table 都是同樣的情況時，你就會覺得煩～

 

使用 LINQPad 產生對映 Table 的 Insert Script

這段產生 Insert Script 的執行結果就是讓使用 Dapper 的開發人員可以方便的直接使用，產生 Script 的程式是參考產生相對映查詢 SQL Command 的類別的程式，下面的程式是要在 LINQPad 裡執行的。

程式如下：

void Main()

{

    // 這邊修改為你要執行的 SQL Command

    var sqlCommand = @"SELECT top 1 * FROM dbo.Customers WITH (NOLOCK);";

    this.Connection.GenerateInsertCommand(sqlCommand.ToString(), "Customer").Dump();

}

public static class LINQPadExtensions

{

    public static string GenerateInsertCommand(this IDbConnection connection, string sql, string tableName = "TableName")

    {

        if (connection.State != ConnectionState.Open)

        {

            connection.Open();

        }

        var cmd = connection.CreateCommand();

        cmd.CommandText = sql;

        var reader = cmd.ExecuteReader();

        var builder = new StringBuilder();

        do

        {

            if (reader.FieldCount <= 1)

            {

                continue;

            }

            builder.AppendFormat("INSERT INTO [dbo].[{0}]{1}", tableName, Environment.NewLine);

            builder.AppendLine("(");

            var schema = reader.GetSchemaTable();

            var columnNames = new List<string>();

            foreach (DataRow row in schema.Rows)

            {

                var columnName = (string)row["ColumnName"];

                columnNames.Add(columnName);

            }

            foreach (var columnName in columnNames)

            {

                builder.AppendFormat("    [{0}]{1}{2}",

                    columnName,

                    columnNames.IndexOf(columnName).Equals(columnNames.Count - 1) ? "" : ",",

                    Environment.NewLine);

            }

            builder.AppendLine(")");

            builder.AppendLine("VALUES");

            builder.AppendLine("(");

            foreach (var columnName in columnNames)

            {

                builder.AppendFormat("    @{0}{1}{2}",

                    columnName,

                    columnNames.IndexOf(columnName).Equals(columnNames.Count - 1) ? "" : ",",

                    Environment.NewLine);

            }

            builder.AppendLine(");");

            builder.AppendLine();

        }

        while (reader.NextResult());

        return builder.ToString();

    }

}

程式執行的結果如下：

INSERT INTO [dbo].[Customer]

(

  [CustomerID],

  [CompanyName],

  [ContactName],

  [ContactTitle],

  [Address],

  [City],

  [Region],

  [PostalCode],

  [Country],

  [Phone],

  [Fax]

)

VALUES

(

  @CustomerID,

  @CompanyName,

  @ContactName,

  @ContactTitle,

  @Address,

  @City,

  @Region,

  @PostalCode,

  @Country,

  @Phone,

  @Fax

);

相較於 SSMS 所產生的 Insert Script 內容，是不是現在這個產生的 Insert Script 不需要額外再去做修改的處理，光是這一點就可以省下一大堆的時間了，程式開發人員的時間是相當寶貴的呀！

 

---

資料存取層的單元測試事前準備工作有很多，例如：指令碼建立 LocalDB、建立 Table、產生 Insert Script、產生與新增測試資料、清空與移除 Table 指令碼、指令碼移除 LocalDB… etc

如果要說的話，最麻煩的在於產生與新增測試資料的這一個部分，其實之前就已經有一篇文章在說明如何使用 Dapper 一次新增多筆或大量資料，那個就是在做資料存取層單元測試的其中一個環節。

這一篇就到這邊，以後有機會再將整串的資料存取層單元測試的準備工作跟大家說明。

 

相關連結

mrkt 的程式學習筆記: Dapper - 使用 LINQPad 快速產生相對映 SQL Command 查詢結果的類別

mrkt 的程式學習筆記: Dapper 練習題 - 新增多筆或大量資料

 

以上