ADO.NET の DbProviderFactory クラスと DbConnection クラス, DbCommand クラスなどの抽象クラスを使ってデータベースにアクセスして操作するアプリの例を備忘録として書いておきます。

上の画像は、SQLite のテーブルのレコード一覧を DataGridView に表示し、ユーザーがそれを見て編集した後、編集結果をデータベースに書き戻す .NET Framework 4.8 の Windows Forms アプリです。

対象としたデータベースは以下の内容の SQLite の Movie テーブルです。

普通に作ると、SQLite 専用の SQLiteConnection, SQLiteCommand などを使うと思いますが、それらに代えて DbConnection, DbCommand などの抽象クラスと、DbProviderFactory の CreateConnection、CreateCommand メソッドなど使うようにします。

そうすると何のメリットがあるのかと言うと、例えば SQLite を SQL Server に変更する場合、ハードコーディングした SQLiteConnection, SQLiteCommand などを SqlConnection, SqlCommand などに書き換える必要はなく、app.config の接続文字列だけを SQL Server 用に書き換えれば移行できます。

まず DbProviderFactory の登録を行う必要がありますが、SQLite の場合は NuGet から System.Data.SQLite をインストールすると app.config に以下の DbProviderFactories 要素が追加されるのでこれを利用します。

<DbProviderFactories>
  <remove invariant="System.Data.SQLite.EF6" />
  <add name="SQLite Data Provider (Entity Framework 6)" 
    invariant="System.Data.SQLite.EF6" 
    description=".NET Framework Data Provider for SQLite (Entity Framework 6)" 
    type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
  <remove invariant="System.Data.SQLite" />
  <add name="SQLite Data Provider" 
    invariant="System.Data.SQLite" 
    description=".NET Framework Data Provider for SQLite" 
    type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
</DbProviderFactories>

接続文字列とプロバイダ名も app.config から取得できるように、以下のように connectionStrings 要素を設定しておきます。

<connectionStrings>
  <add name="ConnectionInfo"
    connectionString="SQLite 用の接続文字列"
    providerName="System.Data.SQLite"/>
</connectionStrings>

Visual Studio のデザイン画面で、ツールボックスから Form に Button 2 つ、DataGridView、BindingSource をドラッグ＆ドロップした後、以下のコードを記述します。コメントアウトしたコードが SQLiteConnection, SQLiteCommand, SQLiteDataAdapter, SQLiteCommandBuilder などを使ったもので、その下が DbProviderFactory を使用したものです。下のコードを実行した結果がこの記事の一番上の画像です。

using System;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;
using System.Configuration;
using System.Data.Common;
using System.Configuration.Provider;

namespace WindowsFormsSQLite
{
    public partial class Form1 : Form
    {
        //private SQLiteDataAdapter adapter;
        // ↓↓↓
        private DbDataAdapter adapter;

        private DataTable table;

        public Form1()
        {
            InitializeComponent();

            this.dataGridView1.DataSource = this.bindingSource1;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            var connString = ConfigurationManager
                             .ConnectionStrings["ConnectionInfo"]
                             .ConnectionString;            
            var selectQuery = 
                "SELECT Id, Title, ReleaseDate, Genre, Price FROM Movie";

            //var connection = new SQLiteConnection(connString);
            //var command = new SQLiteCommand(selectQuery, connection);
            //this.adapter = new SQLiteDataAdapter();
            //this.adapter.SelectCommand = command;
            //_ = new SQLiteCommandBuilder(this.adapter);
            // ↓↓↓
            var providerName = ConfigurationManager
                               .ConnectionStrings["ConnectionInfo"]
                               .ProviderName;
            var factory = DbProviderFactories.GetFactory(providerName);
            var connection = factory.CreateConnection();
            connection.ConnectionString = connString;
            var command = connection.CreateCommand();
            command.CommandText = selectQuery;
            command.Connection = connection;
            this.adapter = factory.CreateDataAdapter();
            this.adapter.SelectCommand = command;
            var builder = factory.CreateCommandBuilder();
            builder.DataAdapter = this.adapter;

            this.table = new DataTable();
            this.adapter.Fill(this.table);
            this.bindingSource1.DataSource = this.table;

            this.components.Add(connection);
            this.components.Add(command);
        }
               
        private void Update_Click(object sender, EventArgs e)
        {            
            this.adapter.Update(this.table);
        }

        private void Remove_Click(object sender, EventArgs e)
        {
            this.bindingSource1.RemoveCurrent();
        }
    }
}

次に、SQLite のテーブルを下の画像の SQL Server のテーブルに変更する場合、どのようにするかを書きます。

System.Data.SqlClient を使う場合、そのプロパイダ情報は machine.config に登録済みのはずです。もし、登録されてなければ app.config の DbProviderFactories 要素に以下のように SQL Server 用のプロパイダ情報を追加してください。

<remove invariant="System.Data.SqlClient" />
<add name="SqlClient Data Provider"
  invariant="System.Data.SqlClient"
  description=".Net Framework Data Provider for SqlServer"
  type="System.Data.SqlClient.SqlClientFactory, System.Data,
    Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

あとは、以下のように接続文字列とプロバイダ名の設定を SQL Server 用に差し替えるだけで済みます。connectionString のみでなく、providerName も SQL Server 用に変更しているところに注意してください。

<connectionStrings>
  <add name="ConnectionInfo"
    connectionString="SQL Server 用の接続文字列"
    providerName="System.Data.SqlClient"/>
</connectionStrings>

その上でアプリを実行すれば、SQL Server に接続されて以下の画像の通り Movie テーブルのレコード一覧が表示され、編集操作も同様に可能になります。

SQLite と SQL Server のテーブルにはデータの型の違いがありますが、DbDataAdapter と DataTable を使う非接続型のアプリの場合は、その違いはプロバイダと DataTable が吸収してくれるようです。

ただ、上のようなことを考えなくても、SQL Server の場合は Visual Studio のデータソース構成ウィザードが利用できますので、それを使って作り直した方が簡単かつ確実かもしれません。ドラッグ＆ドロップ操作だけで自力では一行もコードを書かずにアプリを作成できますので。

【2023/7/5 追記】

プロバイダを、System.Data.SqlClient に代えて Microsoft.Data.SqlClient とする場合について以下に追記します。

Microsoft.Data.SqlClient 用のプロバイダは machine.config には登録されてないので、Microsoft のドキュメント「SqlClientFactory の取得」に書いてあるように、app.config に DbProviderFactory の登録を行う必要があります。以下の通りです。

<add name="Microsoft SqlClient Data Provider" 
  invariant="Microsoft.Data.SqlClient" 
  description="Microsoft SqlClient Data Provider for SQL Server" 
  type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient, 
        Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5" />

上の Version は、使用する Microsoft.Data.Sqlclient のバージョンと合わせる必要があるので注意してください。Microsoft.Data.Sqlclient は NuGet からインストールしますが、インストール後 Visual Studio のソリューションエクスプローラーの「参照」に Microsoft.Data.Sqlclient が追加されるので、そのバージョンに合わせてください。

あとは、接続文字列の設定の内 providerName を Microsoft.Data.Sqlclient 用に変更すれば OK です。

<add name="ConnectionInfo" 
  connectionString="SQL Server 用の接続文字列" 
  providerName="Microsoft.Data.SqlClient" />

SQL Server の decimal 型のフィールドから、.NET アプリで ADO.NET を使って .NET の Decimal 型として値を取得する際、SQL Server と .NET では扱える値の大きさが異なる (SQL Server の方が大きい) ことにより、OverflowException が発生することがあるという話を書きます。

.NET の Decimal 型は、Microsoft のドキュメント「Decimal 構造体」によると、128 ビットで構成され、その内 96 ビットが整数部で、残り 32 ビットが正負の符号と小数点の位置を指定するのに使われているそうです。

従い、.NET の Decimal 型の最大値 (MaxValue) は、

2^96 - 1 = 79,228,162,514,264,337,593,543,950,335

ということになります。

一方、SQL Server の decimal 型は、Microsoft のドキュメント「decimal 型と numeric 型 (Transact-SQL)」によると、38 桁まで表すことができるそうです。例えば、decimal(38,0) は小数部の無い 38 桁の整数を表すことができるということになります。

上の画像では、.NET の Decimal.MaxValue より 1 大きい SQL Server の decimal(38,0) 型の値を、.NET のコンソールアプリで ADO.NET + System.Data.SqlClient を使って取得しようとして OverflowException が発生しています。

上のようなケースではオバーフローするのが当たり前と思えるのですが、自分がハマったのは SQL Server の decimal 型のフィールドで小数点が指定してある場合でした。そのことを以下に書きます。

なお、以下の話は System.Data.SqlClient を使った場合ですので注意してください。（Microsoft.Data.SqlClient の場合は後述します）

例えば、decimal(38,26) と言うように小数部の桁数も指定して、上の画像のコード例の内 query2 を以下のように変更したとします。

// 上の画像の query2 で、
// 79228162514264337593543950336 ⇒ 10000000
// decimal(38,0) ⇒ decimal(38,26)
// に変更
var query2 = "select cast(10000000 as decimal(38,26)) val";

この場合も OverflowException がスローされます。上の 10000000 という値は、上の回答の画像の例 MaxValue + 1 よりはるかに小さいのになぜオーバーフローするのでしょうか？

そこが分からなくてハマったのですが、どうやら以下のようなことらしいです。

上のコードの例では、10000000 の後に 26 個の 0 を続けた整数値を表現できなければならず、.NET の Decimal の整数部 96 ビットでは表現できない (MaxValue を超える) のでオバーフローするということのようです。

試しに上のコードの 10000000 を 792 および 793 に代えて実行してみました。

79200000000000000000000000000 < MaxValue < 79300000000000000000000000000

ということで 792 は OK でしたが、793 はオバーフローするという結果になりました。

Microsoft.Data.SqlClient を使った場合は話が違ってきます。

System.Data.SqlClient も Microsoft.Data.SqlClient も整数部が 96 ビットというのは同じなので、一番上の画像のコード例のように MaxValue を超える整数を設定した場合はオバーフローするのは同じです。

ただし、Microsoft.Data.SqlClient の場合は、上に書いた例と同様に SQL Server の decimal 型に小数部の桁数を指定してもオーバーフローとはなりませんでした。

System.Data.SqlClient とは実装が変わったようです。ただ、どのように変わったのか、どこまで大丈夫なのかは調べ切れてません。時間があったら調べて、何か分かったら追記します。

SQL Server の Multiple Active Result Sets (MARS) というのは何で、どのような場合に必要かという話を書きます。

MARS は SQL Server 2005 以降で利用できる機能で、Microsoft のドキュメント「複数のアクティブな結果セットの有効化」によると "複数のバッチを単一の接続で実行することができます" ということです。（分かりやすい説明を下の方に追記）

接続文字列で MultipleActiveResultSets を true に設定することにより利用できます。

.NET Framework 2.0 からサポートされていたのですが、昔はそのような設定を見かけることは無かったです。それが ADO.NET Entity Data Model ウィザードで自動生成される接続文字列などに設定されるのを見かけて、なぜだろうと疑問に思っていました。

接続文字列で MultipleActiveResultSets=True として「複数のアクティブな結果セットの有効化」を行った例を以下に書きます。

string connString1 = "Data Source=lpc:(local)\\sqlexpress;" +
                     "Initial Catalog=NORTHWIND;" +
                     "Integrated Security=True;" +
                     "MultipleActiveResultSets=True";

string query1 = "SELECT CategoryID, CategoryName FROM Categories;";
string query2 = "SELECT ProductID, ProductName FROM Products;";

using (var connection = new SqlConnection(connString1))
{
    connection.Open();
    using (var command = new SqlCommand(query1, connection))
    {
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("\t{0}\t{1}",
                reader.GetInt32(0), reader.GetString(1));
        }
    }

    using (var command = new SqlCommand(query2, connection))
    {
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("\t{0}\t{1}",
                reader.GetInt32(0), reader.GetString(1));
        }
    }
}

MARS はデフォルトでは無効になっており、その場合はアプリケーションはバッチごとにすべての結果セットを処理またはキャンセルしないと、同じ接続で他のバッチを実行できません。

どういうことかと言うと、接続文字列の MultipleActiveResultSets=True を削除すると 2 つ目の command.ExecuteReader() で以下のように例外がスローされます。

上のコードでは、MARS の設定なしでも、DataReader を閉じるコードを書けば例外は回避できるのですが、Entity Framework が問題です。

例えば、Entity Framework の「遅延読み込み」を行ったりすると、MARS の設定無しでは以下の画像のように例外がスローされます。

ADO.NET Entity Data Model ウィザードなどで自動生成される接続文字列に MARS の設定が含まれているのはそういう理由であろうと思われます。

2022/9/28 追記

MSDN ライブラリ Visual Studio 2008 の SqlCommand.ExecuteReader メソッドの解説に MARS の分かりやすい説明があるのを見つけましたので以下に書いておきます。

"SQL Server 2005 より前のバージョンの SQL Server の場合、SqlDataReader が使用されている間、関連付けられている SqlConnection は SqlDataReader によって使用されるため、ビジー状態になります。この状態では、SqlConnection に対して、閉じる以外の操作を実行できません。SqlDataReader の Close メソッドを呼び出すまでこの状態が続きます。SQL Server 2005 では、MARS (Multiple Active Result Set) 機能がサポートされ、同一接続を使用して複数の処理を実行できるようになりました"