前編では
前編では、MonoとPostgreSQL、MySQLを使用したDBアクセスについて解説を行いました。
引き続き、MonoとSQL Server、Oracleを使用したDBアクセスについて解説します。
6.SQL Server編
SQL Server用データプロバイダ「System.Data.SqlClient」を使用したDBアクセスについて解説します。SQL ServerについてはWikipediaなどを参照してください。
「System.Data.SqlClient」はC#コードだけで実装されたSQL Server用データプロバイダで、他のネイティブライブラリを必要としません。SQL Server 7、2000、2005 に対応しています。
セットアップ
ホストOS側で動作確認用SQL Server DBを作成します。SQL Server用データプロバイダ「System.Data.SqlClient」についてはゲストOS側で「mono-data」パッケージを導入するだけです。
「SQL Server 2005 Express Edition SP2」はダウンロードセンターからダウンロードし、インストールします。本稿ではインストール自体の解説は割愛します。
リモート接続が許可されるようにSQL Serverの設定を行う必要があります。詳細についてはサポートページを参考にして下さい。なお、今回の接続、動作確認においては、「SQL Server Browserサービスを有効にする」の設定は必要ありません。
また、サーバーの認証モードを「SQL Server認証モードとWindows認証モード」に設定する必要があります。認証モードの変更方法についてはMSDNを参考にして下さい。
SQL Server DB
動作確認用DB（TestData）を作成します。sqlcmdユーティリティを使用したDB操作を行います。
テーブル作成用SQLファイルの準備
「D:\Dev\SqlServer\sql」（任意）に以下のテーブル作成用SQLファイルを用意します。
--sqlcmd -S localhost\SQLEXPRESS -i sql\Products.sql
--
USE TestData
GO
-- Products テーブル削除
--DROP TABLE dbo.Products
--GO
-- Products テーブル作成
CREATE TABLE dbo.Products(
ProductID int PRIMARY KEY NOT NULL,
ProductName varchar(25) NOT NULL,
Price money NULL,
ProductDescription nvarchar(128) NULL)
GO
-- INSERT 実行
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
VALUES (0, 'SQL Server', NULL, 'SQLServer 2005 Express Edition')
GO
-- INSERT 実行
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO
-- INSERT 実行 カラムの順番を変えたパターン
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
VALUES ('Screwdriver', 50, 3.17, 'Flat head')
GO
-- INSERT 実行 カラムリスト指定なし、入力値リストはカラム順パターン
INSERT dbo.Products
VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
GO
-- INSERT 実行 ProductDescription カラムの指定なしパターン
INSERT Products (ProductID, ProductName, Price)
VALUES (3000, '3mm Bracket', .52)
GO
-- UPDATE 実行
UPDATE dbo.Products
SET ProductName = 'Flat Head Screwdriver'
WHERE ProductID = 50
GO
-- SELECT 実行 確認用
SELECT * FROM Products
GO
もともと、MSDNライブラリ「Transact-SQLステートメントの作成チュートリアル」内のレッスン 1: データベース オブジェクトの作成を参考にして、DBの作成を行っていますが、チュートリアルでは
CREATE TABLE dbo.Products(
ProductID int PRIMARY KEY NOT NULL,
ProductName varchar(25) NOT NULL,
Price money NULL,
ProductDescription text NULL)
GO
として、テーブルの作成を行っているところ、今回の動作確認では
ProductDescription text NULL → ProductDescription nvarchar(128) NULL
と、データ型を変更しています。「text」型のままだと、
UPDATE Products
SET ProductDescription = @P1
WHERE (ProductID = @P2) AND
(ProductDescription IS NULL OR ProductDescription = @P3)
のような「text」型の項目を比較対象にしたSQL文を実行した場合、
データ型 text と nvarchar は equal to 演算子では互換性がありません。
という例外が発生します。SQL文を
UPDATE Products
SET ProductDescription = @P1
WHERE (ProductID = @P2) AND
(ProductDescription IS NULL OR CAST(ProductDescription as nvarchar) = @P3)
に変更することで対応は可能ですが、汎用的とは言えないので、テーブル定義の変更を行うことで対応しました。
SQL Serverの起動
「SQL Server 構成マネージャ」から「SQL Server」（SQL Server（SQLEXPRESS））サービスの開始、停止が行えます。SQL Serverのインストール後、「SQL Server」サービスは自動で開始されるので、手動で操作する場合以外は必要ありません。
「SQL Server 構成マネージャ」は、［スタート］ボタン-［すべてのプログラム］-［Microsoft SQL Server 2005］-［構成ツール］-［SQL Server 構成マネージャ］を選択すると、起動できます。
データベース作成
> sqlcmd -S localhost\SQLEXPRESS 1> CREATE DATABASE TestData 2> GO
テーブル作成
> D: > cd Dev\SqlServer > sqlcmd -S localhost\SQLEXPRESS -i sql\Products.sql
ログイン作成
SQL Server認証を使用するログインの作成を行います。
> sqlcmd -S localhost\SQLEXPRESS 1> USE master; 2> CREATE LOGIN sta 3> WITH PASSWORD = 'passW0rd' 4> GO
データベースへのアクセス権の付与
データベースにユーザーを作成します。
> sqlcmd -S localhost\SQLEXPRESS 1> USE TestData; 2> CREATE USER sta FOR LOGIN sta 3> GO
データベースオブジェクトへのアクセス権の付与
ユーザーに権限（例：CONTROL）の付与を行います。
> sqlcmd -S localhost\SQLEXPRESS 1> USE TestData; 2> GRANT CONTROL ON TestData TO sta 3> GO
