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アドレス)",
       Initial Catalog=(データベース名);
       Integrated Security = false, // SQL Server認証なら不要
       UserID = "(ユーザー名)",
       Password = "(パスワード)"
   };

   return builder.ToString();
}

• app.configまたはweb.configから取得

テスト環境、本番環境でソースコードを変更せずに接続先を変更することができる。

ただし、ユーザ名とパスワードを平文で記載する場合は、リリースフローや運用でパスワード管理方法を考える必要がある。

app.configまたはweb.configのadd[@name]で指定された名前は、接続文字列を取得する際のキーになる。

ソースコード上でこのキーを指定することで接続文字列を取得する。

ここでは"sqlsvr"を利用しているが、特定できれば任意の文字列で問題ない。

---ソースコード---
using System.Configuration;

public string GetConnectionString()
{
    return ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
}

---app.configまたはweb.config---
<configuration>
  <connectionStrings>
    <add name="sqlsvr"  // 接続文字列
        connectionString="Data Source=(サーバー名/IPアドレス);
        Initial Catalog=(データベース名); // SQL Server認証なら不要
        Persist Security Info=True;
        User ID=(ユーザー名);
        Password=(パスワード)"
        providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

データベース接続

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

1. usingとtry-catchを用いた実装例
2. トランザクションを用いた実装例

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