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

概要

ADO.NETを用いてC\#でSQL Serverに接続するには、下記のような流れで処理を行う。

1. 接続文字列の準備(コード埋め込みまたはapp.configから取得)
2. データベース接続準備(usingとtry-catchを用いた実装例)
3. SQLの実行

• 備考1
  

Initial CatalogをTrueにすると、SQL Server Expressだとmdfファイルへの接続のみ許可されて、接続ユーザ毎に新しいプロセスが立ち上がる。

開発時にはTrueで問題ないが、本番環境ではセキュリティ的な問題でFalseにすること。

• 備考2
  

データソースの指定は下記の表を参照すること。ポート番号は省略できる。

例えば、ローカルのSQL Server Expressのインスタンス名SQLEXPRESSに接続する場合には、下記のように指定すればよい。

Data Source=.\SQLEXPRESS;...

既定のインスタンス	(コンピュータ名、サーバアドレス),(ポート番号)
名前付きインスタンス	(コンピュータ名、サーバアドレス)\(インスタンス名),(ポート番号)

接続文字列の設定

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

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

 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]で指定された名前は、接続文字列を取得する時のキーになる。
ソースコード上でこのキーを指定することで接続文字列を取得する。

• 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に接続する実装方法はいくつ存在するが、ここでは以下のパターンを記載する。

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