DataTableのデータを一括でデータベースに更新する方法 | VB.Net

2016/12/08
23:05

今回は DataTable の内容を一括で SQL Server に更新する方法をご紹介いたします。
DataTable のRowをループして毎行に対して INSERT 文や UPDATE 文を発行することはとてもIO負荷が高く、パフォーマンス的にお勧めできません。
そこで SQL Server の TableType を使用することにより一括での更新が可能となります。
※TableTypeの作成方法については前回ご紹介しておりますので、そちらをご覧ください。

前提条件
Visual Studio 2005 以降
SQL Server 2005 以降
.Net Framework 2.0 以降

１．IO負荷が高い推奨できない例

Sub Test(ByVal dt As DataTable)

    Dim con As New SqlConnection("connection string")
    Dim cmd As New SqlCommand
    Dim sql As New StringBuilder
    con.Open()
    cmd.Connection = con
    cmd.Parameters.Clear()

    'データテーブルの行分だけループしている悪い例
    For i As Integer = 0 To dt.Rows.Count - 1

        sql.AppendLine("INSERT INTO dbo.T_TEST ")
        sql.AppendLine("    ( ")
        sql.AppendLine("     CODE ")
        sql.AppendLine(") VALUES ( ")
        sql.AppendLine("     @Code ")
        sql.AppendLine("); ")

        'パラメータの作成
        Dim param = New SqlParameter("@Code", SqlDbType.Int, ParameterDirection.Input)
        param.Value = dt.Rows(i).Item(0).ToString()
        'パラメータを渡します。
        cmd.Parameters.Add(param)

        'SQLの実行
        cmd.CommandType = CommandType.Text
        cmd.CommandText = sql.ToString()
        cmd.ExecuteNonQuery
    Next i

End Sub

上記の方法を採用しているソースを非常に多く見かけますが、それは件数が少なければ問題が無いように感じますが、１万件を超えてくるとそのパフォーマンスの悪さが目立ってきます。１万件のデータをループしてINSERTすると１万回のIO処理となり、オーバーヘッドが高いです。
それよりも次の方法でINSERTすると１回のIOで済みます。ただ、データの重さは変わりませんが、かなりパフォーマンスが改善されることが見込めます。

２．DataTableを引数に渡す方法（VBのみ）

VB側のみの記述

Sub Test(ByVal dt As DataTable)
 
    Dim con As New SqlConnection("connection string")
    Dim cmd As New SqlCommand
    Dim sql As New StringBuilder
    con.Open()
    cmd.Connection = con
    cmd.Parameters.Clear()

    sql.AppendLine("INSERT INTO dbo.T_TEST ")
    sql.AppendLine("      ( ")
    sql.AppendLine("        CODE ")
    sql.AppendLine("      ) ")
    sql.AppendLine(" SELECT TestCode ")
    sql.AppendLine("   FROM @TestTable ")
    sql.AppendLine("  WHERE TestCode = @Code ")
    sql.AppendLine("); ")
 
    'パラメータの作成
    Dim params(1) As SqlParameters
    params(0) = New SqlParameter("@Code", SqlDbType.Int, ParameterDirection.Input)
    params(0).Value = 1
    params(1) = New SqlParameter("@TestTable", SqlDbType.Structured, ParameterDirection.Input)  'TypeにSqlDbType.Structuredを渡します。
    params(1).TypeName = "dbo.TestTableType"  'テーブルタイプの名称を渡します。
    params(1).Value = dt    '編集したデータテーブルを渡します。
    'パラメータ配列を渡します。
    cmd.Parameters.AddRange(params)

    'SQLの実行
    cmd.CommandType = CommandType.Text
    cmd.CommandText = sql.ToString()
    cmd.ExecuteNonQuery
 
End Sub

TableType を使用することでテーブルみたいにデータを渡すことができるので、一括で SELECT してINSERT または UPDATE が可能です。

３．StoredProcedureの引数にDataTableを渡す方法

VB側の記述

Sub Test(ByVal dt As DataTable)

    Dim con As New SqlConnection("connection string")
    Dim cmd As New SqlCommand
    con.Open()
    cmd.Connection = con
    cmd.Parameters.Clear()

    'パラメータの作成
    Dim params(1) As SqlParameters
    params(0) = New SqlParameter("@Code", SqlDbType.Int, ParameterDirection.Input)
    params(0).Value = 1
    params(1) = New SqlParameter("@TestTable", SqlDbType.Structured, ParameterDirection.Input)  'TypeにSqlDbType.Structuredを渡します。
    params(1).TypeName = "dbo.TestTableType"  'テーブルタイプの名称を渡します。
    params(1).Value = dt    '編集したデータテーブルを渡します。
    'パラメータ配列を渡します。
    cmd.Parameters.AddRange(params)

    'プロシージャの実行
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "dbo.TestProcedure"
    cmd.ExecuteNonQuery

End Sub

SQLServerのプロシージャの記述

CREATE PROCEDURE dbo.TestProcedure
    @Code INT,
    @TestTable dbo.TestTableType READONLY
AS
BEGIN    
    SET NOCOUNT ON;
    INSERT INTO T_TEST
        (
         CODE
        )
    SELECT  TestCode
      FROM  @TestTable
     WHERE  TestCode = @Code
    ;
END;
GO

注意事項
・DataTable のフィールドは TableType のフィールドと一致している必要があります。
・TableType を渡す場合は、SqlDbType.Structured で渡します。
・Procedure に渡す場合は、READONLY を付けないとエラーとなります。

少し気を付けてコーディングする必要はありますが、慣れてしまえばこちらの方法の方が実行速度が圧倒的に速く、パフォーマンス改善の切り札として活用できます。ループせずに一括で更新することから、プログレスバーなどで進捗をカウントすることができない点だけがデメリットでしょうか。

最後までお読みいただき、ありがとうございます。
他にも VB.net に関する記事や SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。

関連記事

FTP接続時に Cannot access a disposed object. のエラーが発生する件 | FtpWebRequest
LinqでDictionary型から条件を指定してフィルターし、ToDictionaryで元の型に変換する方法 | VB.NET
DataTableのデータを一括でデータベースに更新する方法 | VB.Net
WindowsでGrep検索 | VB.net
VB.NET 複数ソリューションのコンパイル

このコンテキストではサブクエリは許可されません。スカラ式だけが許可されます。 | SQL Server ホーム TableTypeの作成方法 | SQL Server

Re: 参考になりました

TableTypeの作成方法は前回の記事でご紹介していますと、冒頭に記載していますよ。

2022/06/17(12:13)
石河　純
編集

参考になりました

とても参考になりました。
ただNo3についてはこれだけでは動かず、もう少し調べてみると、
SQL Server側で同フィールドのユーザ定義テーブルも作成しておく必要がありそうですね。

2022/06/17(11:59)
yossy
編集

コメントの投稿

※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。

　入力されていないコメントには返信しませんのであらかじめご了承くださいませ。

ITブログ時々なんでもブログ