.NET アプリでデータベースに接続するの続き

# はじめに

データベースに接続するプログラムの作り方を確認しておきたいと思いました。

[.NET アプリでデータベースに接続する #.NET - Qiita](https://qiita.com/tinymouse/items/ac9a0c798d3f36cf02a3)

その続きです。

# System.Data.Common を使ってデータベースに接続する

前回は、接続先のデータベースに合わせて、それぞれ `SqlClient` 、`MySqlClient` 、`OracleClient` を使いました。
`System.Data.Common` を使った書き方が紹介されています。これを試してみます。

[DbProviderFactoryを使用したプロバイダに依存しないコード](https://itpfdoc.hitachi.co.jp/manuals/3020/3020635643/W3560393.HTM)

## .NET Framework＋System.Data.Common で接続する

Visual Studio で「新しいプロジェクトの作成」して「コンソールアプリ (.NET Framework）」を選択します。

Visual Studio の「Nuget パッケージの管理」を開いて `Microsoft.Data.SqlClient` をインストールします。

以下のコードを実行してみます。

```csharp
using System.Data.Common;

string conStr = "Data Source=127.0.0.1; User ID=sa; Password=......; Encrypt=false; Initial Catalog=AdventureWorks2022;";

try
{
    DbProviderFactory fc = DbProviderFactories.GetFactory("Microsoft.Data.SqlClient");

    using (DbConnection cn = fc.CreateConnection())
    {
        cn.ConnectionString = conStr;
        cn.Open();
        Console.WriteLine("Connected.");
    }
}
catch (Exception ex)
{
    Console.WriteLine("Error: " + ex.Message);
}
Console.ReadKey();
```

実行時にエラー発生しました。↓

```
要求された .Net Framework データ プロバイダーが見つかりません。これは、インストールされていない可能性があります。
```

`Microsoft.Data.SqlClient` が OS 上の .NET Framework の GAC に登録されているか、そうでなければ、アプリの構成ファイル `App.config` に設定を加える必要があるようです。

[ADO.NET入門記-016 (データプロバイダファクトリの手動追加について)(machine.config, app.config, system.data, DbProviderFactories) - いろいろ備忘録日記](https://devlights.hatenablog.com/entry/20080227/p1)

構成ファイル `App.config` に以下の設定を追記します。

```xml
前略
<configuration>
    中略
    <system.data>
        <DbProviderFactories>
            <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"
            />
        </DbProviderFactories>
    </system.data>
</configuration>
```

エラー発生しなくなりデータベースに接続できました。

## .NET＋System.Data.Common で接続する

Visual Studio で「新しいプロジェクトの作成」して「コンソールアプリ」を選択します。

Visual Studio の「Nuget パッケージの管理」を開いて `Microsoft.Data.SqlClient` をインストールします。

上記と同じコードを実行してみます。
上記と同じエラー発生しました。
上記と同様に `App.config` に設定を加えてみました。それでもエラー発生します。
.NET Framework と違って GAC や構成ファイルが使えないようです。

[SqlClientFactory の取得 - ADO.NET Provider for SQL Server | Microsoft Learn](https://learn.microsoft.com/ja-jp/sql/connect/ado-net/obtain-sqlclientfactory) 

`DbProviderFactories.RegisterFactory()` を使って設定を登録します。

```csharp
DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance);
DbProviderFactory fc = DbProviderFactories.GetFactory("Microsoft.Data.SqlClient");
```

エラー発生しなくなりデータベースに接続できました。

上記の箇所は以下のように書いても動作しました。

```csharp
DbProviderFactory fc = SqlClientFactory.Instance;
```

# .NET アプリでデータベースに接続する仕組を整理する

`System.Data.SqlClient` などは、`ADO.NET` のライブラリです。

> ADO.NETはマイクロソフトが提供する、.NET Frameworkで様々な形式のデータへアクセスする機能を提供するソフトウェアコンポーネントの集合である[1]。
> https://ja.wikipedia.org/wiki/ADO.NET 
> Windows上で提供されてきたADO（ActiveX Data Objects）と同様の環境を.NET上で提供するもので、API仕様などに共通点はあるが、技術的な新たに設計・開発された。
> データベースを利用する側のプログラムを「コンシューマ」（consumer：消費者）、各データベース管理システム（DBMS）などが提供するデータベースへの接続や操作を行うためのソフトウェア部品を「データプロバイダ」（proviver：提供者）という。
> データプロバイダとしてOracle Database用やMicrosoft SQL Server用といった個別のDBMS用だけでなく、OLE DBやODBCとの接続用のコンポーネントが標準で用意されているため、ADO.NETに直接対応していなくてもこれらのインターフェースに対応したDBMS等であれば間接的に接続・操作することができる。
> https://e-words.jp/w/ADO.NET.html 

`ADO.NET` のベースになったのは `ADO` です。

> ActiveX Data Objects (ADO)はデータソースアクセスを目的としたCOMオブジェクトのセットである。
> ADOはRDOやDAOの後継に位置づけられている。1996年の冬、マイクロソフトにより発表された。後年、ADOの後継として.NET Frameworkに組み込まれたADO.NETがリリースされた。
> https://ja.wikipedia.org/wiki/ActiveX_Data_Objects 
> OLE DB仕様に基いて開発されたデータアクセスのためのソフトウェア部品（OLE DBプロバイダ）をActiveXコントロールとして利用できるようにしたもの
> OLE DBインターフェイスを介して、その各種プロバイダの呼び出しを行なう。つまり、ユーザープログラム ⇒ADO ⇒ OLE DBインターフェイス ⇒ OLE DBプロバイダ ⇒ データソースといった階層を経て接続されている。
> https://e-words.jp/w/ADO.html 

`ADO` は、前身である `RDO` や `DAO` の機能を継承しています。

> Remote Data Objects（RDO）は、Windows 95 以降のマイクロソフトのオペレーティングシステムでの Visual Basic アプリケーションでかつて使われていたデータアクセス用APIである[1]。
> Visual Basic のバージョン4、5、6に含まれていた。最終バージョンは RDO 2.0。
> https://ja.wikipedia.org/wiki/Remote_Data_Objects 
> ODBCによるデータベースアクセスをOLEインターフェイスでラップしたもので、ODBCのAPI呼び出しの最適化やキャッシュにより、ODBCを直接利用するよりもスループットを向上させている。OLEオートメーションであることから、他の言語からでも容易に利用できる。
> https://kotobank.jp/word/RDO-13350

> Data Access Objects（DAO）とは、Microsoft Windows でのデータベースアクセスのための汎用APIである。オブジェクト指向設計で使う。
> DAO 1.0 は1992年11月に登場した。バージョン3.5では、Jet Engine をバイパスして直接ODBCデータソース（Microsoft SQL Server などのデータベース）にアクセスできるようになった。マイクロソフトは DAO 3.6 を最後のバージョンとしている。
> https://ja.wikipedia.org/wiki/Data_Access_Objects 
> DAOは、Microsoft社のRDBアプリAccessのデータベース接続インタフェースでもあるJetデータベースエンジンを利用した.mdbファイルへの接続や、汎用データベース接続インタフェースであるODBCを経由したRDBデータベースサーバへの接続、また、Excelやテキストファイルに直接接続することができます。
> https://webzoit.net/hp/it/internet/homepage/env/cs/develop/connect/dao/

`ADO` は `OLE DB` を経由してデータベースと接続します。

> OLE DB (Object Linking and Embedding, Database) はマイクロソフトの設計したAPIであり、一様な形で格納されている様々な種類のデータへのアクセスを行うものである。
> ODBCをより抽象化した形で置換するもので、関係データベース以外の各種データベースもサポートするよう拡張されている。オブジェクトデータベース、表計算ソフトなどSQLをサポートしていないものにも対応している。
> https://ja.wikipedia.org/wiki/OLE_DB 
> COM（Component Object Model）を利用して提供される仕組みで、データの管理を行うソフトウェアを「OLE DBプロバイダ」（provider）、プロバイダの機能を利用してデータの取得や保存を行うソフトウェアを「OLE DBコンシューマ」（consumer）という。
> 主要なリレーショナルデータベース管理システム（RDBMS）製品の多くが独自のOLE DBプロバイダを提供しているほか、RDBMSに接続する標準的な手段を提供するODBC（Open Database Connectivity）もOLE DBプロバイダの一つとして実装されている。
> https://e-words.jp/w/OLE_DB.html 

`DAO` は `ODBC` を経由してデータベースと接続します。

> Open Database Connectivity (ODBC) は、関係データベース管理システム (RDBMS) にアクセスするための共通インタフェース (API)である。
> データへのアクセスを統一化することを目的としており、たとえばクライアント／サーバ型ではないMicrosoft Accessの管理するデータベースファイル (MDB) や、そもそもRDBMSではないCSVファイルへのアクセスなども、それに対応するODBCドライバがあれば、他の一般的なデータベースへのアクセスするのと同様な方法で利用することが可能になる。
> https://ja.wikipedia.org/wiki/Open_Database_Connectivity 
> ODBCを介してデータベースを操作するには、そのDBMSをODBCによるアクセスに対応させるソフトウェア（ODBCドライバ）が必要で、著名なDBMS開発元・販売元は標準でODBCドライバを添付したり配布したりしている。
> https://e-words.jp/w/ODBC.html 

参考：[DAO、ADO、ODBC、OLE DBの違いを簡単にまとめる | YukiPress](https://hmjp.net/archive2019/blog/dao-ado-odbc-oledb/)

上記の内容をまとめてみます。↓

![説明.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/114460/a5c2b4b9-2dc8-3e3d-ca22-75677202e079.png)

`ADO.NET` が `OLE DB` を経由してデータベースに接続している図を見ることがあるのですが、.NET だけで直接接続していると思われます。というのも、`MySQL Connector` や `Oracle Client` をインストールしていなくて OLE DB プロバイダがないと思われる環境でも、`MySql.Data` や `Oracle.ManagedDataAccess` を組込したアプリがデータベースに接続できたからです。また、`System.Data.SqlClient` は独自のプロトコルで直接 SQL Server にアクセスする、との記述を見つけました。

参考：[.NET Framework データ プロバイダー - ADO.NET | Microsoft Learn](https://learn.microsoft.com/ja-jp/dotnet/framework/data/adonet/data-providers)

メーカーが提供していれば `OLE DB プロバイダ` や `ODBC ドライバ` は追加できます。

参考：[Oracle Provider for OLE DBの概要](https://docs.oracle.com/cd/F39414_01/oledb/introduction-to-oracle-provider-for-oledb.html)

参考：[MySQL ODBC ドライバのインストール（Windows 上）](https://www.kkaneko.jp/cc/mysql/mysqlodbcvc.html)

# System.Data.OleDb を使ってデータベースに接続する

前述したように `ADO` は `OLE DB` を経由してデータベースに接続します。`ADO.NET` でも `OLE DB` を明示的に使ってデータベースに接続できるようです。これを試してみます。

[プログラミング講座 C# 【データベース操作編】](https://businessskillupseminar.jp.net/program/csharp/csharp06.html)

`System.Data.OleDb` を使います。
接続文字列で `Provider` を指定します。

SQL Server に接続するための `OLE DB プロバイダ` は複数用意されているようです。
・SQLNCLI（SQL Server Native Client）
・SQLOLEDB（Microsoft OLE DB Provider for SQL Server）
・MSOLEDBSQL（Microsoft OLE DB Driver for SQL Server）

参考：[SQLServerの接続方法を整理（OLEDB／ODBC） #ADO - Qiita](https://qiita.com/cloud-solution/items/1b89a7fba4d4ab78ceaf)

以下のコードを実行してみます。

```csharp
using System.Data.OleDb;

string conStr = "Provider=SQLNCLI; Data Source=127.0.0.1; User ID=sa; Password=......; Initial Catalog=AdventureWorks2022;";

try
{
    using (OleDbConnection cn = new OleDbConnection())
    {
        cn.ConnectionString = conStr;
        cn.Open();
        Console.WriteLine("Connected.");
    }
}
catch (Exception ex) {
    Console.WriteLine("Error: " + ex.Message);
}
Console.ReadKey();
```

実行時エラーになりました。

```
Error: 'SQLNCLI' プロバイダーはローカルのコンピューターに登録されていません。
```

`OLE DB プロバイダ` は Windows OS にインストールされているプログラムで、.NET アプリは実行時に呼出します。
以下のコマンドで、OS にインストールされているプロバイダを一覧できるようです。

```powershell
(New-Object data.oledb.oledbenumerator).getElements() | select SOURCES_NAME, SOURCES_DESCRIPTION
```

参考：[接続文字列一覧｜OLEDB ODBC 32bit 64bitによって違います | 工場エンジニアのAccessスキル](https://access-skill.com/ado-connectionstring-list/)

プロバイダ `SQLNCLI` は Win 11 にインストールされていないようです。
プロバイダの指定を `SQLOLEDB` に変更してみます。

```csharp
string conStr = "Provider=SQLOLEDB; Data Source=127.0.0.1; User ID=sa; Password=......; TrustServerCertificate=True; Initial Catalog=AdventureWorks2022;";
```

データベースに接続できました。

# System.Data.Odbc を使ってデータベースに接続する

さらに、`ADO.NET` でも `ODBC` を経由してデータベースに接続できるようです。これを試してみます。

[.NET/ODBCを使用してデータベースに接続しSQLを実行するサンプル - Windowsソフトウェア開発技術情報](https://windev.just4fun.biz/?.NET/ODBC%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%AB%E6%8E%A5%E7%B6%9A%E3%81%97SQL%E3%82%92%E5%AE%9F%E8%A1%8C%E3%81%99%E3%82%8B%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB)

`System.Data.Odbc` を使います。
接続文字列で `Driver` を指定します。

参考：[接続文字列一覧｜OLEDB ODBC 32bit 64bitによって違います | 工場エンジニアのAccessスキル](https://access-skill.com/ado-connectionstring-list/)

以下のコードを実行してみます。

```csharp
using System.Data.Odbc;

string conStr = "Driver={SQL Server}; Server=127.0.0.1; Uid=sa; Pwd=......; Database=AdventureWorks2022;";

try
{
    using (OdbcConnection cn = new OdbcConnection())
    {
        cn.ConnectionString = conStr;
        cn.Open();
        Console.WriteLine("Connect Succeeded.");
    }
}
catch (Exception ex)
{
    Console.WriteLine("Error: " + ex.Message);
}
Console.ReadKey();
```

データベースに接続できました。

# おわりに

`Microsoft.Data.SqlClient` 、`MySql.Data` 、`Oracle.ManagedDataAccess` のような `ADO.NET` のライブラリがあれば、`OLE DB` や `ODBC` を使ってデータベースに接続する必要はないように思います。
逆に、`OLE DB プロバイダ` や `ODBC ドライバ` は提供されているが `ADO.NET` のライブラリは提供されていないときは、`ADO.NET` から `OLE DB` や `ODBC` を経由してデータベースに接続できるのは便利と言えますね。