チュートリアル : 型プロバイダーを使用した SQL データベースへのアクセス (F#)
このチュートリアルでは、データベースへの有効な接続が存在する場合、SQL データベースのデータの型を生成するために F# 3.0 で使用できる SqlDataConnection (LINQ to SQL) 型プロバイダーを使用する方法について説明します。 データベースへの有効な接続はないが、LINQ to SQL スキーマ ファイル (DBML ファイル) がある場合は、「チュートリアル : DBML ファイルからの F# 型の生成 (F#)」を参照してください。
このチュートリアルでは、次の作業について説明します。 このチュートリアルを正しく行うには、以下の作業を順に行ってください。
Preparing a test database
Creating the project
Setting up a type provider
Querying the data
Working with nullable fields
Calling a stored procedure
Updating the database
Executing Transact-SQL code
Using the full data context
Deleting data
Create a test database (as needed)
テスト データベースの準備
SQL Server を実行しているサーバーで、データベースをテスト用に作成します。 それには、このページの下部にあるセクション「MyDatabase Create Script」のデータベース作成スクリプトを使用できます。
テスト データベースを準備するには
- MyDatabase Create Scriptを実行するには、[表示] メニューを開き、[SQL Server オブジェクト エクスプローラー] をクリックするか、Ctrl+\, Ctrl+S キーを押します。 [SQL Server オブジェクト エクスプローラー] ウィンドウで、適切なインスタンスのショートカット メニューを開き、[新しいクエリ] をクリックし、このページの下部にあるスクリプトをコピーして、エディターにスクリプトを貼り付けます。 SQL スクリプトを実行するには、三角形の記号が表示されたツール バー アイコンをクリックするか、Ctrl+Q キーを押します。 SQL Server オブジェクト エクスプローラーの詳細については、「接続されているデータベース開発」を参照してください。
プロジェクトの作成
次に、F# アプリケーション プロジェクトを作成します。
プロジェクトを作成し、設定するには
新しい F# アプリケーション プロジェクトを作成します。
.FSharp.Data.TypeProviders、System.Data、および System.Data.Linq への参照を追加します。
F# コード ファイル Program.fs の先頭に次のコード行を追加することによって、適切な名前空間を開きます。
open System open System.Data open System.Data.Linq open Microsoft.FSharp.Data.TypeProviders open Microsoft.FSharp.LinqF# のほとんどのプログラムと同様に、このチュートリアルのコードをコンパイルされたプログラムとして実行することも、スクリプトとして対話形式で実行することもできます。 スクリプトを使用する場合は、プロジェクト ノードのショートカット メニューを開き、[新しい項目の追加] をクリックして、F# スクリプト ファイルを追加し、手順ごとにスクリプトにコードを追加します。 アセンブリ参照を読み込むために、ファイルの一番上に次の行を追加する必要があります。
#r "System.Data.dll" #r "FSharp.Data.TypeProviders.dll" #r "System.Data.Linq.dll"次にコードの各ブロックを選択して追加し、Alt+Enter キーを押して F# Interactive で実行します。
型プロバイダーの設定
この手順では、データベース スキーマの型プロバイダーを作成します。
直接データベース接続から型プロバイダーを設定するには
型プロバイダーを使用して SQL データベースを照会するときに使用できる型を作成するために必要な重要なコード行は 2 つあります。 最初に、型プロバイダーをインスタンス化します。 これを行うためには、静的ジェネリック パラメーターを持つ SqlDataConnection の型略称に似たものを作成します。 SqlDataConnection は SQL 型プロバイダーです。ADO.NET プログラミングで使用される SqlConnection 型と混同しないでください。 接続するデータベースと接続文字列がある場合は、次のコードを使用して型プロバイダーを呼び出します。 例の文字列を使用する接続文字列で置き換えます。 たとえば、サーバーが MYSERVER で、データベース インスタンスが INSTANCE である場合、データベース名は MyDatabase であり、データベースにアクセスするために Windows 認証を使用する場合、接続文字列は次のサンプル コードに示すようになります。
type dbSchema = SqlDataConnection<"Data Source=MYSERVER\INSTANCE;Initial Catalog=MyDatabase;Integrated Security=SSPI;"> let db = dbSchema.GetDataContext() // Enable the logging of database activity to the console. db.DataContext.Log <- System.Console.Outこれで、dbSchema 型を作成できました。これはデータベース テーブルを表すすべての生成された型を含む親の型です。 また、db オブジェクトも作成できました。これはそのメンバーとしてデータベースのすべてのテーブルを持つオブジェクトです。 テーブル名はがプロパティで、これらのプロパティの型は、F# コンパイラによって生成されます。 型自体は、dbSchema.ServiceTypes の下に入れ子になった型として表されます。 したがって、これらのテーブルの行に対して取得されるデータはどれも、そのテーブルに対して生成された適切な型のインスタンスです。 型の名前は ServiceTypes.Table1 です。
F# 言語が SQL クエリに対してクエリをどのように解釈するかを理解するために、データ コンテキストの Log プロパティを設定する行を確認します。
さらに型プロバイダーによって作成された型を調べるため、次のコードを追加します。
let table1 = db.Table1table1 の上にポインターを合わせると型が表示されます。 その型は System.Data.Linq.Table<dbSchema.ServiceTypes.Table1> であり、汎用引数は各行の種類が生成された型 dbSchema.ServiceTypes.Table1 であることを暗黙的に示します。 コンパイラは、データベースの各テーブルに同様の型を作成します。
データの照会
この手順では、F# のクエリ式を使用してクエリを作成します。
データを照会するには
データベース内のテーブルのクエリを作成します。 次のコードを追加します。
let query1 = query { for row in db.Table1 do select row } query1 |> Seq.iter (fun row -> printfn "%s %d" row.Name row.TestData1)query という語が表示され、これがクエリ式であり、通常のデータベース クエリと同様の結果のコレクションを生成するコンピュテーション式の型であることを示します。 クエリの上にポインターを合わせると、Linq.QueryBuilder クラス (F#) のインスタンスであり、クエリのコンピュテーション式を定義する型であることがわかります。 query1 の上にポインターを合わせると、IQueryable のインスタンスであることがわかります。 名前が示すように、IQueryable は、クエリの結果ではなく、照会される可能性があるデータを表します。 クエリには遅延評価が適用されます。つまり、クエリが評価される場合にのみデータベースは照会されます。 最終行はクエリを Seq.iter を介して渡します。 クエリは列挙可能で、シーケンスのように繰り返される場合があります。 詳細については、「クエリ式 (F#)」を参照してください。
ここでクエリにクエリ演算子を追加します。 より複雑なクエリを作成するために使用できる多くのクエリ演算子があります。 また、この例では、クエリ変数を削除し、代わりにパイプライン演算子を使用できることも示しています。
query { for row in db.Table1 do where (row.TestData1 > 2) select row } |> Seq.iter (fun row -> printfn "%d %s" row.TestData1 row.Name)2 つのテーブルの結合により、より複雑なクエリを追加します。
query { for row1 in db.Table1 do join row2 in db.Table2 on (row1.Id = row2.Id) select (row1, row2) } |> Seq.iteri (fun index (row1, row2) -> if (index = 0) then printfn "Table1.Id TestData1 TestData2 Name Table2.Id TestData1 TestData2 Name" printfn "%d %d %f %s %d %d %f %s" row1.Id row1.TestData1 row1.TestData2 row1.Name row2.Id (row2.TestData1.GetValueOrDefault()) (row2.TestData2.GetValueOrDefault()) row2.Name)実際のコードでは、クエリのパラメーターは、通常、値または変数で、コンパイル時の定数ではありません。 パラメーターを受け取る関数でクエリをラップし、値 10 でその関数を呼び出す次のコードを追加します。
let findData param = query { for row in db.Table1 do where (row.TestData1 = param) select row } findData 10 |> Seq.iter (fun row -> printfn "Found row: %d %d %f %s" row.Id row.TestData1 row.TestData2 row.Name)
null 許容フィールドの使用
データベースのフィールドでは、多くの場合、null 値を使用できます。 .NET 型システムでは null を許容するデータに対して通常の数値データ型を使用できません。これは、これらの型では null が有効な値ではないためです。 したがって、これらの値は、Nullable 型のインスタンスによって表されます。 このようなフィールドの値にはフィールド名を使用して直接アクセスするのではなく、いくつかの手順を追加する必要があります。 null 許容型の基になる値に Value プロパティを使用してアクセスできます。 Value プロパティは、オブジェクトが値を持たず null である場合は例外をスローします。 HasValue ブール値メソッドを使用して値が存在するかどうかを判断することも、GetValueOrDefault を使用してすべての場合に確実に実際の値があるようにすることもできます。 GetValueOrDefault を使用したときにデータベースに null がある場合、文字列型では空の文字列、整数型では 0、浮動小数点型では 0.0 などの値に置き換えられます。
クエリの where 句で null 許容値の等値テストまたは比較を実行する必要がある場合は、Linq.NullableOperators モジュール (F#) 内にある null 許容の演算子を使用できます。 これらは標準の比較演算子 =、><= などです。ただし、疑問符は null 許容値 がある演算子の左または右側に含まれます。 たとえば、演算子 >? は、右側に null 許容値を持つ大なり演算子です。 これらの演算子は、式のどちら側かが null である場合にその式が false に評価されることで動作します。 where 句ではこれは通常、null フィールドを含む行が選択されておらず、クエリ結果で返されないことを意味します。
null 許容フィールドを使用するには
次のコードは null 許容値の使用を示します。ここでは、TestData1 は unll を許容する整数フィールドであると仮定します。
query { for row in db.Table2 do where (row.TestData1.HasValue && row.TestData1.Value > 2) select row } |> Seq.iter (fun row -> printfn "%d %s" row.TestData1.Value row.Name) query { for row in db.Table2 do // Use a nullable operator ?> where (row.TestData1 ?> 2) select row } |> Seq.iter (fun row -> printfn "%d %s" (row.TestData1.GetValueOrDefault()) row.Name)
ストアド プロシージャの呼び出し
データベースのストアド プロシージャを F# から呼び出すことができます。 静的パラメーター StoredProcedures を型プロバイダーのインスタンス化で true に設定する必要があります。 型プロバイダー SqlDataConnection には、生成された型を構成するために使用できるいくつかの静的メソッドが含まれています。 これらの詳細については、「SqlDataConnection 型プロバイダー (F#)」を参照してください。 データ コンテキスト型のメソッドは各ストアド プロシージャに対して生成されます。
ストアド プロシージャを呼び出すには
ストアド プロシージャが null 許容であるパラメーターを受け取る場合は、適切な Nullable 値を渡す必要があります。 スカラーまたはテーブルを返すストアド プロシージャ メソッドの戻り値は ISingleResult であり、返されたデータにアクセスするためのプロパティが含まれています。 ISingleResult の型引数は特定のプロシージャに依存し、型プロバイダーが生成する型のいずれかです。 Procedure1 という名前のストアド プロシージャでは、型は Procedure1Result です。 型 Procedure1Result は返されるテーブルの列の名前を含みます。また、スカラー値を返すストアド プロシージャの場合は戻り値を表します。
次のコードは、データベースにパラメーターとして 2 個の null 許容整数を受け取る Procedure1 プロシージャがあると仮定し、TestData1 という名前の列を返すクエリを実行し、整数を返します。
type schema = SqlDataConnection<"Data Source=MYSERVER\INSTANCE;Initial Catalog=MyDatabase;Integrated Security=SSPI;", StoredProcedures = true> let testdb = schema.GetDataContext() let nullable value = new System.Nullable<_>(value) let callProcedure1 a b = let results = testdb.Procedure1(nullable a, nullable b) for result in results do printfn "%d" (result.TestData1.GetValueOrDefault()) results.ReturnValue :?> int printfn "Return Value: %d" (callProcedure1 10 20)
データベースの更新
LINQ DataContext 型には、生成された型で完全に型指定することで、トランザクションされたデータベースの更新を容易にするメソッドが含まれています。
データベースを更新するには
次のコードでは、いくつかの行がデータベースに追加されます。 1 行のみ追加する場合、InsertOnSubmit を使用して追加する新しい行を指定できます。 複数の行を挿入する場合は、これらをコレクションに入れ、InsertAllOnSubmit``1 を呼び出す必要があります。 これらのメソッドのいずれかを呼び出すと、データベースはすぐに変更されません。 実際に変更をコミットするには、SubmitChanges を呼び出す必要があります。 既定では、SubmitChanges を呼び出す前に行うことはすべて暗黙的に同じトランザクションに含まれています。
let newRecord = new dbSchema.ServiceTypes.Table1(Id = 100, TestData1 = 35, TestData2 = 2.0, Name = "Testing123") let newValues = [ for i in [1 .. 10] -> new dbSchema.ServiceTypes.Table3(Id = 700 + i, Name = "Testing" + i.ToString(), Data = i) ] // Insert the new data into the database. db.Table1.InsertOnSubmit(newRecord) db.Table3.InsertAllOnSubmit(newValues) try db.DataContext.SubmitChanges() printfn "Successfully inserted new rows." with | exn -> printfn "Exception:\n%s" exn.Messageここで削除操作を呼び出して行をクリーンアップします。
// Now delete what was added. db.Table1.DeleteOnSubmit(newRecord) db.Table3.DeleteAllOnSubmit(newValues) try db.DataContext.SubmitChanges() printfn "Successfully deleted all pending rows." with | exn -> printfn "Exception:\n%s" exn.Message
Transact-SQL コードの実行
ExecuteCommand クラスで DataContext メソッドを使用して、Transact-SQL を直接指定することもできます。
カスタム SQL コマンドを実行するには
次のコードは、SQL コマンドを送信してテーブルにレコードを挿入する方法と、テーブルからレコードを削除する方法を示します。
try db.DataContext.ExecuteCommand("INSERT INTO Table3 (Id, Name, Data) VALUES (102, 'Testing', 55)") |> ignore with | exn -> printfn "Exception:\n%s" exn.Message try //AND Name = 'Testing' AND Data = 55 db.DataContext.ExecuteCommand("DELETE FROM Table3 WHERE Id = 102 ") |> ignore with | exn -> printfn "Exception:\n%s" exn.Message
完全データ コンテキストの使用
前の例では、GetDataContext メソッドを使用してデータベース スキーマの簡易データ コンテキストと呼ばれるものを取得しました。 使用できるメンバーが少ないため、クエリを構築する際は簡易データ コンテキストが使用しやすくなります。 したがって、IntelliSense のプロパティを参照するとき、テーブルやストアド プロシージャなどのデータベース構造に集中できます。 ただし、簡易データ コンテキストを使用して実行できる処理には制限があります。 その他の操作を実行する機能を提供する完全データ コンテキストも 使用できます。これは ServiceTypes にあり、指定した場合は DataContext 静的パラメーターの名前があります。 指定していない場合、データ コンテキスト型の名前は、他の入力に基づいて SqlMetal.exe によって生成されます。 完全データ コンテキストは DataContext から継承され、基本クラスのメンバーを公開します。これには、Connection オブジェクトなどの ADO.NET データ型への参照、SQL でクエリを作成するときに使用できる ExecuteCommand や ExecuteQuery``1 などのメソッド、およびトランザクションを明示的に使用する手段も含まれます。
完全データ コンテキストを使用するには
次のコードは、完全データ コンテキスト オブジェクトを取得し、それを使用してデータベースに対してコマンドを直接実行する方法を示します。 この場合、2 個のコマンドが同じトランザクションの一部として実行されます。
let dbConnection = testdb.Connection let fullContext = new dbSchema.ServiceTypes.MyDatabase(dbConnection) dbConnection.Open() let transaction = dbConnection.BeginTransaction() fullContext.Transaction <- transaction try let result1 = fullContext.ExecuteCommand("INSERT INTO Table3 (Id, Name, Data) VALUES (102, 'A', 55)") printfn "ExecuteCommand Result: %d" result1 let result2 = fullContext.ExecuteCommand("INSERT INTO Table3 (Id, Name, Data) VALUES (103, 'B', -2)") printfn "ExecuteCommand Result: %d" result2 if (result1 <> 1 || result2 <> 1) then transaction.Rollback() printfn "Rolled back creation of two new rows." else transaction.Commit() printfn "Successfully committed two new rows." with | exn -> transaction.Rollback() printfn "Rolled back creation of two new rows due to exception:\n%s" exn.Message dbConnection.Close()
データの削除
この手順では、データ テーブルから行を削除する方法を説明します。
データベースから行を削除するには
次に、指定されたテーブルや Table クラスのインスタンスから行を削除する関数を作成して追加された行をクリーンアップします。 その後、削除する行をすべて見つけるクエリを作成し、deleteRows 関数に対してクエリの結果をパイプ処理します。 このコードは、関数引数の部分的な適用を行う機能を利用します。
let deleteRowsFrom (table:Table<_>) rows = table.DeleteAllOnSubmit(rows) query { for rows in db.Table3 do where (rows.Id > 10) select rows } |> deleteRowsFrom db.Table3 db.DataContext.SubmitChanges() printfn "Successfully deleted rows with Id greater than 10 in Table3."
テスト データベースの作成
ここでは、このチュートリアルで使用するテスト データベースを設定する方法について説明します。
データベースをなんらかの方法で変更した場合、型プロバイダーをリセットする必要があることに注意してください。 型プロバイダーをリセットするには、型プロバイダーを含むプロジェクトをリビルドするかクリーンアップします。
テスト データベースを作成するには
サーバー エクスプローラーで、[データ接続] ノードのショートカット メニューを開いて、[接続の追加] をクリックします。 [接続の追加] ダイアログ ボックスが表示されます。
[サーバー名] ボックスに、管理者アクセス権を持つ SQL Server のインスタンス名を指定するか、サーバーにアクセスできない場合は「(localdb\v11.0)」と指定します。 SQL Express LocalDB は、開発およびテスト用の軽量のデータベース サーバーをコンピューターに提供します。 新しいノードが、サーバー エクスプローラーの [データ接続] の下に作成されます。 LocalDB の詳細については、「チュートリアル: Visual Studio でのローカル データベース ファイルの作成」を参照してください。
新しい接続ノードのショートカット メニューを開き、[新しいクエリ] を選択します。
次の SQL スクリプトをコピーしてクエリ エディターに貼り付け、ツール バーの [実行] をクリックするか、Ctrl キーと Shift キーを押しながら E キーを押します。
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO USE [master]; GO IF EXISTS (SELECT * FROM sys.databases WHERE name = 'MyDatabase') DROP DATABASE MyDatabase; GO -- Create the MyDatabase database. CREATE DATABASE MyDatabase; GO -- Specify a simple recovery model -- to keep the log growth to a minimum. ALTER DATABASE MyDatabase SET RECOVERY SIMPLE; GO USE MyDatabase; GO -- Create the Table1 table. CREATE TABLE [dbo].[Table1] ( [Id] INT NOT NULL, [TestData1] INT NOT NULL, [TestData2] FLOAT (53) NOT NULL, [Name] NTEXT NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) ); --Create Table2. CREATE TABLE [dbo].[Table2] ( [Id] INT NOT NULL, [TestData1] INT NULL, [TestData2] FLOAT (53) NULL, [Name] NTEXT NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) ); -- Create Table3. CREATE TABLE [dbo].[Table3] ( [Id] INT NOT NULL, [Name] NVARCHAR (50) NOT NULL, [Data] INT NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) ); GO CREATE PROCEDURE [dbo].[Procedure1] @param1 int = 0, @param2 int AS SELECT TestData1 FROM Table1 RETURN 0 GO -- Insert data into the Table1 table. USE MyDatabase INSERT INTO Table1 (Id, TestData1, TestData2, Name) VALUES(1, 10, 5.5, 'Testing1'); INSERT INTO Table1 (Id, TestData1, TestData2, Name) VALUES(2, 20, -1.2, 'Testing2'); --Insert data into the Table2 table. INSERT INTO Table2 (Id, TestData1, TestData2, Name) VALUES(1, 10, 5.5, 'Testing1'); INSERT INTO Table2 (Id, TestData1, TestData2, Name) VALUES(2, 20, -1.2, 'Testing2'); INSERT INTO Table2 (Id, TestData1, TestData2, Name) VALUES(3, NULL, NULL, 'Testing3'); INSERT INTO Table3 (Id, Name, Data) VALUES (1, 'Testing1', 10); INSERT INTO Table3 (Id, Name, Data) VALUES (2, 'Testing2', 100);
参照
処理手順
チュートリアル : DBML ファイルからの F# 型の生成 (F#)
関連項目
SqlDataConnection 型プロバイダー (F#)