～ SQL Server にアクセスしてみよう! - ADO.NET プログラミングにチャレンジ!! - ～

NEC

Eラーニング事業部

山崎 明子

( このストリーミングはブロードバンド対応です。それ以外の環境からご利用の場合、快適にご覧いただけないことがあります。 )

2003 年 4 月 21 日

目次

1. データプロバイダ
2. 接続文字列
3. 接続してみよう
4. データの参照
5. データを参照してみよう
6. コマンドの実行
7. 接続プーリング
ワンポイントレッスン！（インターフェイス）

1. データプロバイダ

データベースにアクセスするために最初に行うことは、データプロバイダの選択です。

データプロバイダにはいくつかの種類があるため、データソースに合わせて適切なデータプロバイダを選択する必要があります。

.NET Framework 1.0（Visual Studio .NET の最初のバージョン）では、2 種類のデータプロバイダが提供されています。

• SQL Server .NET データプロバイダ

• OLE DB .NET データプロバイダ

また、マイクロソフトのサイトより ODBC 経由でアクセスする次のデータプロバイダをダウンロードすることができます。

• ODBC .NET データプロバイダ

これらのプロバイダのうちいずれを使うかの選択は、非常にシンプルです。

データソース毎のピュアなデータプロバイダがあるのであれば、パフォーマンス的にそれが最もお勧めです。

つまり、SQL Server を利用している場合は、SQL Server .NET データプロバイダを選択することになります。

ただし、SQL Server .NETデータプロバイダは、SQL Server 7.0 以降用のものですので、それ以前のバージョンを使っている場合や、Access など他のデータソースにアクセスする場合は使えません。その場合汎用的な OLE DB データプロバイダを利用します。これを利用すると、従来の OLE DB に対応するデータソースにアクセスすることができます。ただ、OLE DB Provider for ODBC 経由で、ODBC 対応データにアクセスするとはできません。そのため、ODBC 経由でのアクセスは、ODBC .NET データプロバイダを利用します。

データプロバイダの選択は図 2-1 を参考にしてください。

また、これらの名前空間は、それぞれ次の通りです。

• SQL Server .NET データプロバイダ

  System.Data.SqlClient

• OLE DB .NET データプロバイダ

  System.Data.OleDb

• ODBC .NET データプロバイダ

  Microsoft.Data.ODBC

この名前空間には、それぞれ同様のオブジェクトが定義されています。たとえば、データソースに接続するための Connection オブジェクトがそれぞれの名前空間にあります。

そしてそれらには、プロバイダ名に関連するプリフィックスがついています。SQL Server .NET データプロバイダの場合は、Sql、OLE DB .NET データプロバイダは、OleDb で始まります。

たとえば、次のとおりです。

SqlConnection

OleDbConnetion

OdbcConnection

データプロバイダの主なクラスは図 2-2 の通りで、この図は SQL Server のデータプロバイダを例に主なオブジェクトとその用途、そしてオブジェクト間の関連を示しています。なお、この図には、すべてのオブジェクト間の関係は表しきれていません。たとえば Adapter と他のオブジェクトの関連ですが、これは次回以降にご紹介する予定です。

すでに触れたように、SqlConnection と OleDbConnection のように、プリフィックスが異なっていても XxxConnection はいずれも接続を実現するために利用されますし、また同じようなプロパティやメソッドをもっています。これには理由があります、SqlConnection も OleDbConnection も同じ IDbConnection というインターフェイスを継承しているからです。詳しくは、ワンポイントを参照してください。

ワンポイントレッスン！（インターフェイス）

2. 接続文字列

データに接続するためには、どのデータソースに接続するか？を指定する必要があります。

この文字列を接続文字列といいます。

たとえば、接続先のサーバー名、デフォルトデータベース、セキュリティ情報などをセミコロンを区切りとして指定します。たとえば、

Dim cn As New SqlConnection
cn.ConnectionString = "Data Source=DBServer;Initial Catlog=pubs;Integrated Security=SSPI"

ここでは、SQL Server がインストールされている DBServer を指定しています。またローカルサーバーの場合、(local) と指定することも可能です。また、接続後、データベースを指定しなかった場合の規定のデータベースも指定できます。ここでは、pubs を指定しています。また Integrated Security を SSPI または true に設定することで Windows 統合認証でアクセスすることができます。

また、サーバー名の指定を Data Source とする代わりに、Server と指定することもできます。

SQL Server の代表的な接続文字列を示します。

名前	説明
Server または Data Source	サーバー名
Database または Initial Catalog	規定のデータベース
Integrated Security または Trusted_Connection	Windows 統合認証
User ID または UID	SQL Server のアカウント名
Password または PWD	SQL Serer アカウントのパスワード

その他のものはマニュアルを参考にしてください。

MSDN ライブラリで接続文字列を参照

このように直接コーディングで接続文字列を設定する代わりに、本コラムの第 1 回のデモで紹介したように、ツールを使用して接続文字列を指定することもできます。

	ご参考 ADO.NET アプリケーション作成デモ の 「接続先データソースの指定」
	(このストリーミングはブロードバンド対応です。それ以外の環境からご利用の場合、快適にご覧いただけないことがあります。)

いずれかの方法で接続文字列を設定した後、接続を開始します。

開始は、Open メソッド、接続の解放は Close メソッドです。

3. 接続してみよう

では実際にアプリケーションを作成し、接続してみましょう。

(1) プロジェクトの作成

Visual Studio .NET を起動し、新しいプロジェクトを作成します。

Step 1

[ファイル]-[新規作成]-[プロジェクト]を選択します。

Step 2

表示される[新しいプロジェクト]ダイアログで、

－プロジェクトの種類：[VBプロジェクト]

－テンプレート：[ASP.NET Webアプリケーション]

－場所：「http://localhost/ConnectionDemo」（任意）

をそれぞれ選択、または入力し、[OK]ボタンをクリックします。

(2) 名前空間の指定

フォームをダブルクリックして、表示されるページの一番上の行に次の名前空間をインポートするコードを追加します。

Imports System.Data.SqlClient 

(3) コントロールの配置

再度、フォーム （WebForm1.aspx） を表示し、そこにツールボックスから Button コントロールを配意します。

(4) コーディング

次に、そのボタンをダブルクリックして、ButtonのClickイベントハンドラを追加します。

                   Dim cn As New SqlConnection("server=(local);Integrated Security=SSPI;Database=pubs;")
        cn.Open
        Button1.Text = "Connection Success!!"
        cn.Close

接続文字列は、すでに説明した情報を参考に変更してみましょう。

(5) 作成したアプリケーションの動作確認

ページをビルドし、実行します。

ボタンをクリックしたとき、Connection Success!! とボタンに表示されれば、接続に成功したことになります。

（接続に失敗すると、例外が発生するため Connection Success!! は表示されません。）

さて、接続できましたか？

※ここでうまく実行できない場合、セキュリティの問題が考えられます。 1 章のワンポイントレッスン を参考にしてください。

4. データの参照

データソースに接続できたら、次にデータを参照してみましょう。

(1) Command オブジェクト

データを参照するためには、Command オブジェクトを利用します。Command オブジェクトの CommandText プロパティとして、

• SELECT ステートメント

• SELECT クエリを含むストアドプロシージャ

• テーブル名

のいずれかを指定します。テーブル名を指定するとテーブルのデータをすべて参照できるのですが、SQL Server .NET データプロバイダではテーブル名を指定するという機能は利用できません。（業務では、テーブル全体を参照するようなことは少ないと思われますので、問題ないでしょう。）

また、このときどのタイプのコマンドを指定したかを CommandType プロパティで指定します。

CommandType.Text	SQL ステートメント（既定値）
CommandType.StoredProcedure	ストアドプロシージャ
CommandType.TableDirect	テーブル名

「Text」が既定値ですので、それ以外（つまりストアドプロシージャやテーブル名）を CommandText として指定した場合は、CommandType の指定も必須です。

(例) SQL ステートメントを指定

                   Dim cn As New SqlConnection("server=(local);Integrated Security=SSPI;Database=pubs;")
        Dim cm As SqlCommand = cn.CreateCommand
        cm.CommandText = "SELECT * FROM authors" 

(例)ストアドプロシージャ（パラメータ付き）を指定

                   Dim cn As New SqlConnection("server=(local);Integrated Security=SSPI;Database=pubs;")
        Dim cm As SqlCommand = cn.CreateCommand
        cm.CommandText = "ByRoyalty"　'ロイヤリティを条件に著者IDを取得するストアドプロシージャ
        cm.CommandType = CommandType.StoredProcedure
        cm.Parameters.Add("@percentage", 100)
　　　　　　　:
　　　　　　　:

(2) DataReader オブジェクト

データを参照するために DataReader を利用することができます。このオブジェクトは、読み取り専用、前方スクロール専用ですが、いくつかあるデータ参照方法のうち、もっとも高速です。

Read メソッドを利用して、末尾に向かってデータを 1 件 1 件読み進みます。Read メソッドはデータがある間は True を返し、末尾まで読み進んでデータがなくなると False を返します。これをループの条件として利用するとデータを最後まで読むことができます。

読み込んだデータの各フィールドの値を参照するために、

• フィールド名を指定

  例) dr("au_fname")

• インデックスを指定

  例) dr(2)

• GetXxxメソッド

  例) dr.GetString("au_fname")、GetInt32("count")

のいずれかを使用します。パフォーマンスが優れているのは GetXxx を利用する方法です。ただし、NULL のデータを読み込むときには例外が発生しますので IsDBNULL などを活用して事前にデータの有無を調べる必要があります。

                   Dim cn As New SqlConnection("server=(local);Integrated Security=SSPI;Database=pubs;")
        Dim cm As SqlCommand = cn.CreateCommand
        cm.CommandText = "SELECT * FROM authors"
        Dim dr As SqlDataReader

        cn.Open()

        dr = cm.ExecuteReader

        Do While dr.Read
            ListBox1.Items.Add(dr("au_fname"))
        Loop

        dr.Close()
        cn.Close() 

このプログラムにはいくつかポイントがあります。

まず、Open の位置に注目してください。接続を Open したときから Close までの間、データソースに接続しています。

ですので、この時間をできるだけ短くすることでデータソースの共有性が高めることができます。

このプログラムのように、できるだけ遅いタイミングで Open し、使い終わったらできるだけすぐに Close するようにしましょう。

5. データを参照してみよう

「3. 接続してみよう」で作成したアプリケーションに機能を追加します。

(1) コントロールの追加

ツールボックスから ListBox コントロールをドラッグ ＆ ドロップし、フォームに配意します。

(2) コードの変更

ボタンをクリックしたときのコードを次のように変更します。

                   Dim cn As New SqlConnection("server=(local);Integrated Security=SSPI;Database=pubs;")

        Dim cm As SqlCommand = cn.CreateCommand        '追加
        cm.CommandText = "SELECT city FROM authors"        '追加
        Dim dr As SqlDataReader                '追加

        cn.Open()
        Button1.Text = "Connection Success!!"

        dr = cm.ExecuteReader            '追加

        Do While dr.Read               '追加
            If Not dr.IsDBNull(0) Then        '追加
                ListBox1.Items.Add(dr.GetString(0))     '追加
            End If                '追加
        Loop                    '追加

        dr.Close()                '追加
        cn.Close() 

(3) 作成したアプリケーションの動作確認

ページをビルドし、実行します。

ボタンをクリックしたとき、Connection Success!! とボタンに表示され、リストに著者名が表示されれば成功です。

さて、結果は表示できましたか？

6. コマンドの実行

ExecuteReader 以外のコマンドの実行メソッドとして、

• ExecuteNonQuery

• ExecuteScalar

• ExecuteXMLReader

があります。

(1) ExecuteNonQuery

結果セットを返さないクエリの実行には、このメソッドを利用します。

　　(例)

        cm.CommandText = "DELETE FROM authors WHERE au_ｆname = 'John' 
        iCount = cm.ExecuteNonQuery

このメソッドは「結果セット（データの取得結果）」は、返しませんが「影響を与えた行数」の情報は戻り値として返します。

(2) ExcecuteScalar

結果セットの最初の行の最初の列を取得できます。

　　(例)

        cm.CommandText = "SELECT AVG(price) From titles"
        Dim x As Decimal = cm.ExecuteScalar()

これを利用することで、そのためのオブジェクトを用意したり、そのメソッドを利用して取得する必要がなくなり、スマートなプログラミングができます。

(3) ExecuteXmlReader

SQL Server 2000では、結果セットをXML形式で返すことができます。そのようなXML形式で取得したデータを参照するために、このメソッドを活用できます。

　　(例)

        Dim cn As New SqlConnection("server=(local);Integrated Security=SSPI;Database=pubs;Min Pool Size=1")
        Dim cm As SqlCommand = cn.CreateCommand 

        cm.CommandText = "SELECT au_fname FROM authors For XML AUTO"

        cn.Open()

        Dim xr As XmlReader = cm.ExecuteXmlReader()

        Do While xr.Read
            ListBox1.Items.Add(xr("au_fname"))
        Loop

        xr.Close()
        cn.Close() 

以上のように、Command の実行には、それぞれ最適化された Execute メソッドを利用することができます。

7. 接続プーリング

皆さんもご存知のとおり、データ接続は、非常に貴重なリソースです。そのためこまめに接続、解放を行う必要があります。

でも、毎回新たな接続を開始すると、「時間がかかる」というデメリットがあります。そこで、接続リソースを適切にプーリングし、使いまわすことでシステム全体のパフォーマンスをあげることができます。なぜなら、プーリングするということは、次の接続に備えて接続を維持しているということですから。

特に Web アプリケーションでは、

• 1つのアプリケーションを複数のユーザーで利用する

• 同じデータへの接続を行うことが多い

などの理由から、Windows アプリケーションに比べ、接続リソースをプーリングしそれを再利用することによるメリットは大きいといえます。

SQL Server .NET データプロバイダでは、接続文字列を利用して、接続リソースのプーリングを簡単に制御できます。

接続プーリングを制御するために、接続文字列として次のような設定ができます。

名前	説明
Pooling	プーリングを有効 (True) または無効 (Flase) にすることができます。デフォルトは有効です。
Max Pool Size	プーリングの最大数を指定できます。
Min Pool Size	プーリングの最小数を指定できます。これを利用して、常にある一定の接続を維持することができます。

このときプーリングされた接続が再利用されるのは、接続文字列が完全に一致している場合のみです。

なお、接続がプーリングされているかどうかをチェックするためにパフォーマンスモニタや、SQL Server プロファイラを活用できます。

(1) 実験：接続プーリングの利用

Step 1

今まで作成してきた Web アプリケーションにボタンをひとつ追加します。そして、そのボタンのクリックイベントハンドラに、次のようにコーディングします。

                   cn.ConnectionString = "server=(local);Integrated Security=SSPI;Database=pubs;"
        cn.Open()
        cn.Close() 

Step 2

パフォーマンスモニタ (perfmon.exe) を起動し、次のカウンタを追加 (図 2-3) します。

>

1. パフォーマンスオブジェクト：SQLServer:General Statics

2. 一覧からカウンタを選ぶ：チェック

3. UserConnection：選択

4. 追加：クリック

5. 閉じる：クリック

スケールが大きすぎてグラフの変化が見づらい場合、グラフを右クリックして表示されるプロパティメニューをクリックし、グラフページで垂直スケールの最大値を 10 くらいに設定します (図 2-4)。

Step 3

Step1 のコードを実行します。

すると、Open と Close をおこなっているにもかかわらず、データの接続は維持されている、つまりプーリングされていることがわかります (図 2-5)。

これは、デフォルトが接続プーリングが有効だからです。

この後、しばらくの間は、上記のコードを繰り返し実行しても、接続は増えたり減ったりしません。ひとつの接続を使いまわしているからです。

(2) 実験：接続プーリングの無効化

Step 1

ボタンのクリックイベントなどのコーディングを次のように変更します。

                   cn.ConnectionString = "server=(local);Integrated Security=SSPI;Database=pubs;Pooling=false"
        cn.Open()
        cn.Close() 

Step 2

先程と同様、パフォーマンスモニタでモニタリングします。

すると今度は、図 2-6 のように、接続し解放すると、プーリングされずにすぐに本当に解放されることがわかります (図 2-6)。つまり、毎回新しい接続を開始しているのです。

このように、接続文字列を利用して適切に接続リソースのプーリングがおこなえます。

もちろん、接続プーリングはそれ自体がリソースを必要としますので、不要であればプーリングしないという選択をする場合もあります。データアクセスでは、このような「状況に応じた適切な選択」をしていくことが必要です。

以上、今回はデータプロバイダを中心にご紹介しました。

次回は、ADO.NET の最大の肝である「DataSet」をご紹介します。

 
ワンポイントレッスン！（インターフェイス）
同じような機能を提供するクラスを実現するために、インターフェイスを活用します。ちなみに、インターフェイスの名前は一般に「I」で始まります。たとえば、接続を実現するための IDbConnetion には、接続のために必要なプロパティやメソッドが定義されています。ただし、実際の実装は含まれていません。それは、プロバイダ毎にそのプロパティやメソッドの役割は同じでも実行される処理は異なるからです。
IDbConnection の実装は、それぞれ SqlConnection、OleDbConnection でそれぞれ行われています。
これによって、開発者にとって、「異なるプロバイダであっても同じ役割を果たすオブジェクトは、同じように扱うことができる」というプログラミング上のメリットがあります。また、実際に次のようにプロバイダに依存しないプログラミングを行うことができます。
                   Dim cn As System.Data.IDbConnection
        cn = New System.Data.OleDb.OleDbConnection()　　
        cn.ConnectionString = "Provider=SQLOLEDB;Server=(local);Initial Catalog=pubs;Integrated Security=SSPI"
        cn.Open()
        cn.Close()
        cn = Nothing


        Dim cn As System.Data.IDbConnection
        cn = New System.Data.SqlClient.SqlConnection()
        cn.ConnectionString = "Server=(local);Initial Catalog=pubs;Integrated Security=SSPI"
        cn.Open()
        cn.Close()
        cn = Nothing

プロバイダに依存しているのは、New するオブジェクトの指定と接続文字列 （SQL Server データプロバイダの場合は、Provider の指定が不要）のみです。
たとえば、この例は、データプロバイダを変更する可能性のあるプログラムで活用することができます。ただし、インターフェイスとしては提供されず、継承したクラスで新しく追加されたメソッドやプロパティにはアクセスすることができないことに注意しておく必要があります。
「データプロバイダ」へ戻る