C Sharpとデータベース - データベースの接続

概要

ADO.NETは、C#からSQL Server、Oracle Database、MySQL等の主要なデータベース管理システム (DBMS) に接続するための標準ライブラリである。

各DBMSに対応したデータプロバイダを使用することにより、同一のプログラミングモデルで異なるデータベースに接続できる。

主要なデータプロバイダは以下の通りである。

主要なデータプロバイダ一覧

データベース	プロバイダ名	備考
SQL Server	System.Data.SqlClient	旧来のプロバイダ
	Microsoft.Data.SqlClient	System.Data.SqlClient の後継 NuGetパッケージとして提供
Oracle Database	Oracle.ManagedDataAccess.Client	NuGetパッケージとして提供
MySQL	MySqlConnector	推奨 MITライセンス
	MySql.Data	Oracle提供

データベース接続の基本的な流れは以下の通りである。

1. 接続文字列の準備 (コード埋め込み または app.config から取得)
2. 接続オブジェクトの作成 (各DBMSに対応した接続クラスを使用)
3. Open() メソッドによる接続の開始
4. SQLの実行
5. Close() メソッドによる接続の終了

リソース管理には using ステートメントを活用することを推奨する。
スコープを外れた時点で接続オブジェクトが自動的に破棄されるため、リソースリークを防止できる。

接続プーリングは各プロバイダでデフォルトで有効になっており、接続の再利用によりパフォーマンスを向上させる。

Microsoft.Data.SqlClient はSystem.Data.SqlClientの後継であり、新規開発では Microsoft.Data.SqlClient を使用することを推奨する。

接続文字列の設定

ソースコード上に埋め込む方法

プロパティ値を設定して、ソースコード上で接続先を変更する。
ただし、動的な切り替えができないため本番環境では使用すべきではない。

 using System.Data.SqlClient;
 
 public string GetConnectionString2()
 {
    var builder = new SqlConnectionStringBuilder()
    {
        DataSource         = "サーバ名 / IPアドレス",
        InitialCatalog     = "データベース名",
        IntegratedSecurity = false, // SQL Server認証なら不要
        UserID             = "ユーザ名",
        Password           = "パスワード"
    };
 
    return builder.ToString();
 }

アプリケーション構成ファイル (app.config / web.config) から取得する方法

app.config / web.config は、アプリケーション構成ファイルとも呼ばれている。
これは、Microsoft .NET標準の設定値の保存場所である。

アプリケーション構成ファイルのメリット

• 接続情報をソースコードから分離できる。
• 環境ごとに設定が変更できる。

  例えば、環境別 (開発環境、テスト環境、本番環境) ごとに接続文字列を設定できる。

• パスワードの暗号化も可能である。

app.config / web.configは通称であり、実際には <アプリケーション名>.exe.Config という名前の場合が多い。
一般的に、実行ファイルと同階層に生成されるが、設定によっては一部ユーザフォルダに配置されることもある。

app.configには2種類のクラス (PropertiesおよびConfiguration) が用意されており、それぞれ役割とスコープを持つ。

app.config / web.configの connectionStrings タグは、C#でデータベース接続文字列を管理するための重要な設定セクションである。
ただし、ユーザ名とパスワードを平文で記述する場合は、リリース時や運用でパスワード管理方法を考える必要がある。

app.config / web.configのadd[@name]で指定された名前は、接続文字列を取得する時のキーになる。
ソースコード上でこのキーを指定することで接続文字列を取得する。

app.config / web.configのadd要素の属性

属性名	説明
name	接続文字列の識別子
connectionString	実際の接続文字列
providerName	使用するデータプロバイダ

※注意
パスワードは平文で保存せずに暗号化することを推奨する。

 <?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>

SQLiteを使用する場合、Data Sourceの |DataDirectory| は、変更することができる。

 AppDomain.CurrentDomain.SetData("DataDirectory", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Data"));

 // 接続文字列の取得と接続
 
 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();
 
       // ...略
    }
 }

SQL Serverへの接続

SQL Serverに接続する実装方法はいくつ存在するが、ここでは以下に示すパターンを記載する。

• usingとtry-catchを用いる方法
• トランザクションを用いる方法

usingとtry-catchを用いる方法

一部のオブジェクトは破棄を保証する必要があるので、ソースコードに記述するのではなく、usingで担保するような実装を行う。

スコープから外れた時点で破棄が必要なオブジェクトは自動的に破棄されるが、 usingを用いるとオブジェクトの破棄が明示的になる。

 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();
        }
    }
 }

トランザクションを用いる方法

上記のusingとtry-catchに加えて、トランザクション処理を行う場合の実装例である。

 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();
        }
    }
 }

Oracle Databaseへの接続

Oracle Databaseに接続するには、Oracle公式が提供する Oracle.ManagedDataAccess.Core パッケージを使用する。

NuGetパッケージのインストール

NuGetパッケージマネージャーから Oracle.ManagedDataAccess.Core をインストールする。

 dotnet add package Oracle.ManagedDataAccess.Core

ソースコードの先頭に以下の名前空間を追加する。

 using Oracle.ManagedDataAccess.Client;

接続文字列の形式

Oracleの接続文字列には、EZ Connect形式 と TNS名指定形式 の2種類がある。

EZ Connect形式は、TNSNAMESファイルを必要とせずに直接接続先を指定できる形式である。

 // 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";

OracleConnectionStringBuilder を使用すると、接続文字列をプロパティで設定できる。

 var builder = new OracleConnectionStringBuilder()
 {
    UserID   = "scott",
    Password = "tiger",
    DataSource = "hostname:1521/service_name"
 };
 
 string connectionString = builder.ToString();

usingとtry-catchを用いる方法

OracleConnection を使用した基本的な接続例を以下に示す。

 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();
        }
    }
 }

トランザクションを用いる方法

OracleTransaction を使用したトランザクション処理の実装例を以下に示す。

 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();
        }
    }
 }

Oracle Database接続時の注意事項を以下に示す。

• BindByName = true を設定することで、パラメータを名前でバインドできる。

  デフォルトは位置バインドであるため、パラメータの順序に依存した不具合を防ぐために設定することを推奨する。

  
  

• Oracleのパラメータプレースホルダはコロン (:) を使用する。

  SQL Serverのアットマーク (@) とは異なるため注意が必要である。

  
  

• OracleException を使用することで、Oracle固有のエラーコードやメッセージを取得できる。

MySQLへの接続

MySQLに接続するには、MySqlConnector パッケージを使用することを推奨する。

NuGetパッケージのインストール

MySQLへの接続には、以下の2つのパッケージから選択できる。

• MySqlConnector (推奨)

  MITライセンスで提供されるオープンソースパッケージ

  真の非同期I/Oをサポートしており、高パフォーマンスな接続処理が可能

  
  

• MySql.Data

  Oracle社が提供する公式パッケージ

NuGetパッケージマネージャーから MySqlConnector をインストールする。

 dotnet add package MySqlConnector

ソースコードの先頭に以下の名前空間を追加する。

 using MySqlConnector;

接続文字列の形式

MySQLの接続文字列の基本形式を以下に示す。

 // 基本形式
 // Server=ホスト名;User ID=ユーザ名;Password=パスワード;Database=データベース名;Port=ポート番号
 
 string connectionString = "Server=localhost;User ID=root;Password=mypassword;Database=mydatabase;Port=3306";

MySqlConnectionStringBuilder を使用すると、接続文字列をプロパティで設定できる。

 var builder = new MySqlConnectionStringBuilder()
 {
    Server   = "localhost",
    UserID   = "root",
    Password = "mypassword",
    Database = "mydatabase",
    Port     = 3306,
    SslMode  = MySqlSslMode.Preferred,
    AllowPublicKeyRetrieval = true
 };
 
 string connectionString = builder.ToString();

下表に、接続文字列の主要なオプションを示す。

接続文字列の主要なオプション

オプション名	説明
SslMode	SSL接続モードを指定する。 本番環境では Required を使用することを推奨する。
AllowPublicKeyRetrieval	RSA公開鍵の自動取得を許可する。 MySQL 8.0以降で認証プラグインとして caching_sha2_password を使用する場合に必要となることがある。

usingとtry-catchを用いる方法

MySqlConnection を使用した基本的な接続例を以下に示す。

 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();
        }
    }
 }

トランザクションを用いる方法

MySqlTransaction を使用したトランザクション処理の実装例を以下に示す。

 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();
        }
    }
 }

MySQL接続時の注意事項を以下に示す。

• MySQLのパラメータプレースホルダはアットマーク (@) を使用する。

  SQL Serverと同じ形式であるため、SQL Serverからの移行が容易である。

  
  

• MySqlConnector は真の非同期I/Oをサポートしており、OpenAsync や ExecuteNonQueryAsync 等の非同期メソッドを活用できる。
• トランザクションはInnoDBストレージエンジンでのみ有効である。

  MyISAMストレージエンジンはトランザクションをサポートしていないため、テーブル作成時にストレージエンジンを確認する必要がある。

接続プーリング

接続プーリングは、データベース接続を再利用することでパフォーマンスを向上させる仕組みである。
各データプロバイダでデフォルトで有効になっている。

下表に、各DBMSの接続プーリング設定の比較を示す。

接続プーリングの主要設定

設定項目	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

接続プーリングを無効にする場合は、接続文字列に Pooling=false を追加する。

ただし、通常の運用では接続プーリングを有効のままにすることを推奨する。