SQL Server との接続とデータの取得

ここではマイクロソフトの SQL Server に接続してデータを取得(表示) するだけの簡単なプログラムの書き方を学びます。

SQL Server は高性能、高機能なデータベースサーバーですが、Express Edition は無償で利用可能です。

またテストで利用している "Northwind" データベースというのは昔から MS の環境で利用されてきている有名なサンプルデータベースです。

データベースとの接続文字列は構成ファイルに

データベースへの接続情報は接続文字列 (connection string) に記載します。接続する方法、認証情報などを書きます。

また、接続文字列はアプリケーションの設定ファイル (*.exe.config) に次のように connectionStrings のエントリーを作成します。

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
  <add 
    name="DB" 
    connectionString="Server=.;Integrated Security=false;Database=Northwind;UID=sa;PWD=yourpassword here"/>
</connectionStrings>
<startup> 
  <supportedRuntime 
    version="v4.0" 
    sku=".NETFramework,Version=v4.5" />
</startup>
</configuration>

接続文字列のエントリー意味

接続文字列内のエントリーの意味は次のようになります。

"Server" を "." (ドット) としているのは、ローカルホストの意味です。localhost と書いても同じです。

"Integrated Security" を "false" としているのは、データベースへアクセスするときに現在のユーザーのコンテキストでデータベースにアクセスするのではないことを示しています。逆に現在のユーザーとして、アクセスするならばこれを "true" とします。

"Database" はその名の通り、利用するデータベース名です。"Initial Catalog" と書いても同じです。ここでは "Northwind" データベースを選んでいます。

"UID" と "PWD" はそれぞれユーザー名とパスワードです。前述の Integrated Security が false である場合、この認証情報が利用されます。

このように接続文字列内では各種情報を ";" (セミコロン) で区切って入力します。

ちなみに、パスワードについては接続文字列の最後に入力すると変な問題を避けることができることが多いようです。一部のドライバーでは ";" までをパスワードの一部と認識するためです。

ConfigurationManager の利用

この接続文字列は ConfigurationManager の ConnectionStrings コレクションからアクセス可能です。

ここでは構成ファイル内の connectionString の name 属性として "DB" という名前をつけています。この場合、プログラムでは ConfigurationManager.ConnectionStrings["DB"].ConnectionString とすると、接続文字列を取得可能です。

尚、ConfigurationManager を利用するには、System.Configuration アセンブリを参照する必要があります。Visual Studio のプロジェクト設定では、参照の追加として次のようにします。

System.Configuration の参照設定

.NET Framework のアセンブリから System.Configuration を選択します。

System.Configuration の参照設定

以上で準備はできました。データベースから情報を読み出してみましょう。

データベーステーブルからの情報の読み出し

まず、動作を確認します。

Northwind データベースにある Employees テーブルの内容を読み出します。

SQL Management Studio で Employees テーブルの内容を見ると、次のようになっています。

9件のレコードがあります。この FirstName と LastName を読み出し、コンソールに出力してみましょう。

コードは次の通り。

using System;
using System.Configuration;
using System.Data.SqlClient;

namespace SelectTest1 {
  class Program {
    static void Main( string[] args ) {
      using( var conn = new SqlConnection(
        ConfigurationManager.ConnectionStrings["DB"].ConnectionString) ) {
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT * FROM Employees";
        conn.Open();
        using( var sdr = cmd.ExecuteReader() ) {
          if( sdr.HasRows ) {
            while( sdr.Read() ) {
              var firstName = sdr["FirstName"].ToString();
              var lastName = sdr["LastName"].ToString();
              Console.WriteLine( "{0} {1}", firstName, lastName );
            }
          }
        }
      }
    }
  }
}

SQL Server へアクセスするには通常、 System.Data.SqlClient 名前空間にあるクラスを利用します。他にも方法がないわけではありませんが、 SQL Server 向けに用意された .NET Framework データプロバイダはここにある、ということです。

手順は次の通りです。

SqlConnection オブジェクトを作成。このときに ConfigurationManager を介して構成ファイルからファイルから接続文字列を取得。
SqlConnection オブジェクトの CreateCommand メソッドで、その SqlConnection オブジェクトに関連付く SqlCommand オブジェクトを取得。
SqlCommand オブジェクトの CommandText プロパティに Employees テーブルから全データを取得する SQL 文をセットする。(SELECT * FROM Employees)
SqlConnection オブジェクトの Open メソッドを呼び出し、接続をオープンする
SqlCommand オブジェクトの ExecuteReader メソッドで SqlDataReader オブジェクトを取得する。
SqlDataReader の HasRows プロパティをチェック。True の場合、レコードが読み出せる状態にある。
SqlDataReader の Read メソッドで一行毎にレコードを取得する。Read メソッドが true を返す限り繰り返す。
SqlDataReader にカラム名を渡してそのカラムの内容を読み出す。
内容を Console に出力する。
using を抜けることで SqlDataReader が閉じる。
using を抜けることで SqlConnection が閉じる。

上に書いたとおり、using を利用することでデータベース接続を閉じ忘れる問題がなくなるので便利ですね。

ちなみに、SqlDataReader は DbDataReader (System.Data.Common) から派生しているのですが、ここで実装されている Dispose も Close を呼びます。