fc2ブログ

記事一覧

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


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



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



1.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

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



2.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 が可能です。



3.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 に関する記事 をご紹介しておりますのでご参考頂ければ幸いです。



関連記事

コメント

Re: 参考になりました
TableTypeの作成方法は前回の記事でご紹介していますと、冒頭に記載していますよ。
参考になりました
とても参考になりました。
ただNo3についてはこれだけでは動かず、もう少し調べてみると、
SQL Server側で同フィールドのユーザ定義テーブルも作成しておく必要がありそうですね。
コメントの投稿

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

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

※ニックネームでも良いので必ずご入力ください。

    

※必ずご入力ください。

    
    

※必ずご入力ください。

※技術的な質問には環境やエラーについて正確かつ詳細にお教えください。

・正確なエラーの内容

・Windowsのバージョン番号

・Visual Studioのバージョン

・機器の型番

・アプリやソフトのバージョン

    

カテゴリ別記事一覧

広告

プロフィール

石河 純


著者名 :石河 純
自己紹介:素人上がりのIT技術者。趣味は卓球・車・ボウリング

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Catalina 10.15 / Mojave 10.14 / High Sierra 10.13 / Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
【データベース】
Oracle 10g/9i
SQLServer 2016/2008R2/2005/2000
SQLAnywhere 16/11/8
【BI/レポートツール】
Cognos ReportNet (IBM)
Microsoft PowerBI
ActiveReport (GrapeCity)
CrystalReport
【OCX関連】
GrapeCity InputMan SPREAD MultiRow GridView
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS jQuery IIS6/7 apache2

休みの日は卓球をやっています。
現在、卓球用品通販ショップは休業中です。