C Sharpとデータベース - データベースの接続のソースを表示

== 概要 ==
ADO.NETは、C#からSQL Server、Oracle Database、MySQL等の主要なデータベース管理システム (DBMS) に接続するための標準ライブラリである。<br>
<br>
各DBMSに対応したデータプロバイダを使用することにより、同一のプログラミングモデルで異なるデータベースに接続できる。<br>
<br>
主要なデータプロバイダは以下の通りである。<br>
<br>
<center>
{| class="wikitable"
|+ 主要なデータプロバイダ一覧
|-
! データベース !! プロバイダ名 !! 備考
|-
| rowspan="2" | SQL Server || <code>System.Data.SqlClient</code> || 旧来のプロバイダ
|-
| <code>Microsoft.Data.SqlClient</code> || <code>System.Data.SqlClient</code> の後継<br>NuGetパッケージとして提供
|-
| Oracle Database || <code>Oracle.ManagedDataAccess.Client</code> || NuGetパッケージとして提供
|-
| rowspan="2" | MySQL || <code>MySqlConnector</code> || 推奨<br>MITライセンス
|-
| <code>MySql.Data</code> || Oracle提供
|}
</center>
<br>
データベース接続の基本的な流れは以下の通りである。<br>
# 接続文字列の準備 (コード埋め込み または app.config から取得)
# 接続オブジェクトの作成 (各DBMSに対応した接続クラスを使用)
# <code>Open()</code> メソッドによる接続の開始
# SQLの実行
# <code>Close()</code> メソッドによる接続の終了
<br>
リソース管理には <code>using</code> ステートメントを活用することを推奨する。<br>
スコープを外れた時点で接続オブジェクトが自動的に破棄されるため、リソースリークを防止できる。<br>
<br>
接続プーリングは各プロバイダでデフォルトで有効になっており、接続の再利用によりパフォーマンスを向上させる。<br>
<br>
<code>Microsoft.Data.SqlClient</code> はSystem.Data.SqlClientの後継であり、新規開発では <code>Microsoft.Data.SqlClient</code> を使用することを推奨する。<br>
<br><br>

== 接続文字列の設定 ==
===== ソースコード上に埋め込む方法 =====
プロパティ値を設定して、ソースコード上で接続先を変更する。<br>
ただし、動的な切り替えができないため本番環境では使用すべきではない。<br>
<br>
 <syntaxhighlight lang="c#">
 using System.Data.SqlClient;
 
 public string GetConnectionString2()
 {
    var builder = new SqlConnectionStringBuilder()
    {
        DataSource         = "サーバ名 / IPアドレス",
        InitialCatalog     = "データベース名",
        IntegratedSecurity = false, // SQL Server認証なら不要
        UserID             = "ユーザ名",
        Password           = "パスワード"
    };
 
    return builder.ToString();
 }
 </syntaxhighlight>
<br>
===== アプリケーション構成ファイル (app.config / web.config) から取得する方法 =====
app.config / web.config は、アプリケーション構成ファイルとも呼ばれている。<br>
これは、Microsoft .NET標準の設定値の保存場所である。<br>
<br>
アプリケーション構成ファイルのメリット<br>
* 接続情報をソースコードから分離できる。
* 環境ごとに設定が変更できる。
*: 例えば、環境別 (開発環境、テスト環境、本番環境) ごとに接続文字列を設定できる。
* パスワードの暗号化も可能である。
<br>
app.config / web.configは通称であり、実際には <u><アプリケーション名>.exe.Config</u> という名前の場合が多い。<br>
一般的に、実行ファイルと同階層に生成されるが、設定によっては一部ユーザフォルダに配置されることもある。<br>
<br>
app.configには2種類のクラス (PropertiesおよびConfiguration) が用意されており、それぞれ役割とスコープを持つ。<br>
<br>
app.config / web.configの <code>connectionStrings</code> タグは、C#でデータベース接続文字列を管理するための重要な設定セクションである。<br>
ただし、ユーザ名とパスワードを平文で記述する場合は、リリース時や運用でパスワード管理方法を考える必要がある。<br>
<br>
app.config / web.configのadd[@name]で指定された名前は、接続文字列を取得する時のキーになる。<br>
ソースコード上でこのキーを指定することで接続文字列を取得する。<br>
<br>
<center>
{| class="wikitable"
|+ app.config / web.configのadd要素の属性
|-
! 属性名 !! 説明
|-
| name || 接続文字列の識別子
|-
| connectionString || 実際の接続文字列
|-
| providerName || 使用するデータプロバイダ
|}
</center>
<br>
<u>※注意</u><br>
<u>パスワードは平文で保存せずに暗号化することを推奨する。</u><br>
<br>
 <syntaxhighlight lang="xml">
 <?xml version="1.0" encoding="utf-8" ?>
 <!-- app.config または web.config -->
 
 <configuration>
   <connectionStrings>
     <!-- SQL Server (通常) - SQL Server認証 -->
     <!-- SQL Server認証の場合はUser IDとPasswordを指定 -->
     <add name="<任意の接続文字列>"
          connectionString="Data Source=<サーバ名 / IPアドレス>;
          Initial Catalog=<データベース名  ※ただし、SQL Server認証なら不要>;
          Persist Security Info=True;
          User ID=<DBユーザ名>;
          Password=<DBユーザのパスワード>"
          providerName="System.Data.SqlClient"/>
 
     <!-- SQL Server (通常) - Windows認証 -->
     <!-- Windows認証の場合はIntegrated Security=Trueを指定 -->
     <add name="<任意の接続文字列>"
          connectionString="Data Source=<サーバ名 / IPアドレス>;
          Initial Catalog=<データベース名>;
          Integrated Security=True"
          providerName="System.Data.SqlClient"/>
 
     <!-- SQL Server (LocalDB) -->
     <add name="<任意の接続文字列>"
          connectionString="Data Source=(LocalDB)\MSSQLLocalDB;
          Initial Catalog=<データベース名>;
          Integrated Security=True"
          providerName="System.Data.SqlClient"/>
 
     <!-- MySQL -->
     <!-- NuGetでMySql.Dataパッケージのインストール -->
     <!-- Uidでユーザ名、Pwdでパスワードを指定 -->
     <!-- ServerをData Sourceに変更してもよい -->
     <add name="MySQLConnection"
          connectionString="Server=<サーバ名 / IPアドレス>;
          Database=<データベース名>;
          Uid=<DBユーザ名>;
          Pwd=<DBユーザのパスワード>"
          providerName="MySql.Data.MySqlClient"/>
 
     <!-- SQLite 3 -->
     <!-- NuGetでSystem.Data.SQLiteパッケージのインストール -->
     <!-- ファイルベースのDBなので、Data SourceにDBファイルのパスを指定 -->
     <!-- |DataDirectory|はアプリケーションのデータディレクトリを示す特殊なプレースホルダ (省略可能) -->
     <!-- デスクトップアプリケーションの場合、既定では実行可能ファイルのディレクトリを指す -->
     <!-- Webアプリケーションの場合、App_Dataディレクトリを指す -->
     <add name="SQLiteConnection"
          connectionString="Data Source=|DataDirectory|<SQLiteファイルのパス>.db;
          Version=3;"
          providerName="System.Data.SQLite"/>
 
     <!-- Oracle -->
     <!-- NuGetでOracle.ManagedDataAccessパッケージのインストール -->
     <add name="OracleConnection"
          connectionString="Data Source=<TNS名>;
          User Id=<DBユーザ名>;
          Password=<DBユーザのパスワード>;"
          providerName="Oracle.ManagedDataAccess.Client"/>
   </connectionStrings>
 </configuration>
 </syntaxhighlight>
<br>
SQLiteを使用する場合、Data Sourceの <code>|DataDirectory|</code> は、変更することができる。<br>
<br>
 <syntaxhighlight lang="c#">
 AppDomain.CurrentDomain.SetData("DataDirectory", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Data"));
 </syntaxhighlight>
<br>
 <syntaxhighlight lang="c#">
 // 接続文字列の取得と接続
 
 using System.Configuration;
 
 public string GetConnectionString()
 {
     return ConfigurationManager.ConnectionStrings["<addタグのname属性の値>"].ConnectionString;
 }
 
 // データベースへ接続
 public string Connect()
 {
    var connectionString = GetConnectionString();
    using (var connection = new SqlConnection(connectionString))
    {
       connection.Open();
 
       // ...略
    }
 }
 </syntaxhighlight>
<br><br>

== SQL Serverへの接続 ==
SQL Serverに接続する実装方法はいくつ存在するが、ここでは以下に示すパターンを記載する。<br>
* usingとtry-catchを用いる方法
* トランザクションを用いる方法
<br>
===== usingとtry-catchを用いる方法 =====
一部のオブジェクトは破棄を保証する必要があるので、ソースコードに記述するのではなく、usingで担保するような実装を行う。<br>
<br>
スコープから外れた時点で破棄が必要なオブジェクトは自動的に破棄されるが、 usingを用いるとオブジェクトの破棄が明示的になる。<br>
<br>
 <syntaxhighlight lang="c#">
 using System;
 using System.Configuration;
 using System.Data.SqlClient;
 
 public void Connect3()
 {
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        try
        {
            // データベースの接続開始
            connection.Open();
 
            // SQLの実行
            command.CommandText = @"SELECT count(*) FROM T_USER";
            command.ExecuteNonQuery();
        }
        catch (Exception exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            // データベースの接続終了
            connection.Close();
        }
    }
 }
 </syntaxhighlight>
<br>
===== トランザクションを用いる方法 =====
上記のusingとtry-catchに加えて、トランザクション処理を行う場合の実装例である。<br>
 <syntaxhighlight lang="c#">
 using System;
 using System.Configuration;
 using System.Data.SqlClient;
 
 public void Create(string id, string password)
 {
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    {
        try
        {
            // データベースの接続開始
            connection.Open();
 
            using (var transaction = connection.BeginTransaction())
            using (var command = new SqlCommand() { Connection = connection, Transaction = transaction })
            {
                try
                {
                    // 実行するSQLの準備
                    command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD) VALUES (@ID, @PASSWORD)";
                    command.Parameters.Add(new SqlParameter("@ID", id));
                    command.Parameters.Add(new SqlParameter("@PASSWORD", password));
 
                    // SQLの実行
                    command.ExecuteNonQuery();
                }
                catch
                {
                    // ロールバック
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    // コミット
                    transaction.Commit();
                }
            }
        }
        catch (Exception exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            // データベースの接続終了
            connection.Close();
        }
    }
 }
 </syntaxhighlight>
<br><br>

== Oracle Databaseへの接続 ==
Oracle Databaseに接続するには、Oracle公式が提供する <code>Oracle.ManagedDataAccess.Core</code> パッケージを使用する。<br>
<br>
==== NuGetパッケージのインストール ====
NuGetパッケージマネージャーから <code>Oracle.ManagedDataAccess.Core</code> をインストールする。<br>
<br>
 <syntaxhighlight lang="powershell">
 dotnet add package Oracle.ManagedDataAccess.Core
 </syntaxhighlight>
<br>
ソースコードの先頭に以下の名前空間を追加する。<br>
<br>
 <syntaxhighlight lang="c#">
 using Oracle.ManagedDataAccess.Client;
 </syntaxhighlight>
<br>
==== 接続文字列の形式 ====
Oracleの接続文字列には、<u>EZ Connect形式</u> と <u>TNS名指定形式</u> の2種類がある。<br>
<br>
EZ Connect形式は、TNSNAMESファイルを必要とせずに直接接続先を指定できる形式である。<br>
<br>
 <syntaxhighlight lang="c#">
 // EZ Connect形式
 // User Id=ユーザ名;Password=パスワード;Data Source=ホスト名:ポート番号/サービス名
 string connectionString = "User Id=scott;Password=tiger;Data Source=hostname:1521/service_name";
 
 // TNS名指定形式
 // tnsnames.oraに定義されたTNS名を使用して接続する
 string connectionString = "User Id=scott;Password=tiger;Data Source=ORCL";
 </syntaxhighlight>
<br>
<code>OracleConnectionStringBuilder</code> を使用すると、接続文字列をプロパティで設定できる。<br>
<br>
 <syntaxhighlight lang="c#">
 var builder = new OracleConnectionStringBuilder()
 {
    UserID   = "scott",
    Password = "tiger",
    DataSource = "hostname:1521/service_name"
 };
 
 string connectionString = builder.ToString();
 </syntaxhighlight>
<br>
==== usingとtry-catchを用いる方法 ====
<code>OracleConnection</code> を使用した基本的な接続例を以下に示す。<br>
<br>
 <syntaxhighlight lang="c#">
 using Oracle.ManagedDataAccess.Client;
 
 public void Connect()
 {
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        try
        {
            connection.Open();
 
            command.CommandText = @"SELECT count(*) FROM T_USER";
            command.BindByName = true;
            command.ExecuteNonQuery();
        }
        catch (OracleException exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            connection.Close();
        }
    }
 }
 </syntaxhighlight>
<br>
==== トランザクションを用いる方法 ====
<code>OracleTransaction</code> を使用したトランザクション処理の実装例を以下に示す。<br>
<br>
 <syntaxhighlight lang="c#">
 public void Create(string id, string password)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    {
        try
        {
            connection.Open();
 
            using (var transaction = connection.BeginTransaction())
            using (var command = new OracleCommand() { Connection = connection })
            {
                try
                {
                    command.BindByName = true;
                    command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD) VALUES (:ID, :PASSWORD)";
                    command.Parameters.Add(new OracleParameter(":ID", id));
                    command.Parameters.Add(new OracleParameter(":PASSWORD", password));
                    command.ExecuteNonQuery();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    transaction.Commit();
                }
            }
        }
        catch (OracleException exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            connection.Close();
        }
    }
 }
 </syntaxhighlight>
<br>
Oracle Database接続時の注意事項を以下に示す。<br>
* <code>BindByName = true</code> を設定することで、パラメータを名前でバインドできる。
*: デフォルトは位置バインドであるため、パラメータの順序に依存した不具合を防ぐために設定することを推奨する。
*: <br>
* Oracleのパラメータプレースホルダはコロン (<code>:</code>) を使用する。
*: SQL Serverのアットマーク (@) とは異なるため注意が必要である。
*: <br>
* <code>OracleException</code> を使用することで、Oracle固有のエラーコードやメッセージを取得できる。
<br><br>

== MySQLへの接続 ==
MySQLに接続するには、<code>MySqlConnector</code> パッケージを使用することを推奨する。<br>
<br>
==== NuGetパッケージのインストール ====
MySQLへの接続には、以下の2つのパッケージから選択できる。<br>
<br>
* <code>MySqlConnector</code> (推奨)
*: MITライセンスで提供されるオープンソースパッケージ
*: 真の非同期I/Oをサポートしており、高パフォーマンスな接続処理が可能
*: <br>
* <code>MySql.Data</code>
*: Oracle社が提供する公式パッケージ
<br>
NuGetパッケージマネージャーから <code>MySqlConnector</code> をインストールする。<br>
<br>
 <syntaxhighlight lang="powershell">
 dotnet add package MySqlConnector
 </syntaxhighlight>
<br>
ソースコードの先頭に以下の名前空間を追加する。<br>
<br>
 <syntaxhighlight lang="c#">
 using MySqlConnector;
 </syntaxhighlight>
<br>
==== 接続文字列の形式 ====
MySQLの接続文字列の基本形式を以下に示す。<br>
<br>
 <syntaxhighlight lang="c#">
 // 基本形式
 // Server=ホスト名;User ID=ユーザ名;Password=パスワード;Database=データベース名;Port=ポート番号
 
 string connectionString = "Server=localhost;User ID=root;Password=mypassword;Database=mydatabase;Port=3306";
 </syntaxhighlight>
<br>
<code>MySqlConnectionStringBuilder</code> を使用すると、接続文字列をプロパティで設定できる。<br>
<br>
 <syntaxhighlight lang="c#">
 var builder = new MySqlConnectionStringBuilder()
 {
    Server   = "localhost",
    UserID   = "root",
    Password = "mypassword",
    Database = "mydatabase",
    Port     = 3306,
    SslMode  = MySqlSslMode.Preferred,
    AllowPublicKeyRetrieval = true
 };
 
 string connectionString = builder.ToString();
 </syntaxhighlight>
<br>
下表に、接続文字列の主要なオプションを示す。<br>
<br>
<center>
{| class="wikitable"
|+ 接続文字列の主要なオプション
|-
! オプション名 !! 説明
|-
| <code>SslMode</code> || SSL接続モードを指定する。<br>本番環境では <code>Required</code> を使用することを推奨する。
|-
| <code>AllowPublicKeyRetrieval</code> || RSA公開鍵の自動取得を許可する。<br>MySQL 8.0以降で認証プラグインとして <code>caching_sha2_password</code> を使用する場合に必要となることがある。
|}
</center>
<br>
==== usingとtry-catchを用いる方法 ====
<code>MySqlConnection</code> を使用した基本的な接続例を以下に示す。<br>
<br>
 <syntaxhighlight lang="c#">
 using MySqlConnector;
 
 public void Connect()
 {
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        try
        {
            connection.Open();
 
            command.CommandText = @"SELECT count(*) FROM T_USER";
            command.ExecuteNonQuery();
        }
        catch (MySqlException exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            connection.Close();
        }
    }
 }
 </syntaxhighlight>
<br>
==== トランザクションを用いる方法 ====
<code>MySqlTransaction</code> を使用したトランザクション処理の実装例を以下に示す。<br>
<br>
 <syntaxhighlight lang="c#">
 public void Create(string id, string password)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    {
        try
        {
            connection.Open();
 
            using (var transaction = connection.BeginTransaction())
            using (var command = new MySqlCommand() { Connection = connection, Transaction = transaction })
            {
                try
                {
                    command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD) VALUES (@ID, @PASSWORD)";
                    command.Parameters.Add(new MySqlParameter("@ID", id));
                    command.Parameters.Add(new MySqlParameter("@PASSWORD", password));
                    command.ExecuteNonQuery();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    transaction.Commit();
                }
            }
        }
        catch (MySqlException exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            connection.Close();
        }
    }
 }
 </syntaxhighlight>
<br>
MySQL接続時の注意事項を以下に示す。<br>
* MySQLのパラメータプレースホルダはアットマーク (<code>@</code>) を使用する。
*: SQL Serverと同じ形式であるため、SQL Serverからの移行が容易である。
*: <br>
* <code>MySqlConnector</code> は真の非同期I/Oをサポートしており、<code>OpenAsync</code> や <code>ExecuteNonQueryAsync</code> 等の非同期メソッドを活用できる。
* トランザクションはInnoDBストレージエンジンでのみ有効である。
*: MyISAMストレージエンジンはトランザクションをサポートしていないため、テーブル作成時にストレージエンジンを確認する必要がある。
<br><br>

== 接続プーリング ==
接続プーリングは、データベース接続を再利用することでパフォーマンスを向上させる仕組みである。<br>
各データプロバイダでデフォルトで有効になっている。<br>
<br>
下表に、各DBMSの接続プーリング設定の比較を示す。<br>
<br>
<center>
{| class="wikitable"
|+ 接続プーリングの主要設定
! 設定項目 !! SQL Server !! Oracle !! MySQL
|-
| 有効/無効 || Pooling=true || Pooling=true || Pooling=true
|-
| 最小接続数 || Min Pool Size=0 || Min Pool Size=1 || MinimumPoolSize=0
|-
| 最大接続数 || Max Pool Size=100 || Max Pool Size=100 || MaximumPoolSize=100
|-
| 接続タイムアウト || Connection Timeout=15 || Connection Timeout=15 || ConnectionTimeout=15
|-
| 接続の有効期間 || Connection Lifetime=0 || Connection Lifetime=0 || ConnectionLifeTime=0
|}
</center>
<br>
接続プーリングを無効にする場合は、接続文字列に <code>Pooling=false</code> を追加する。<br>
<br>
ただし、通常の運用では接続プーリングを有効のままにすることを推奨する。<br>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:C_Sharp]]