VS2005環境でのNAgile - Identity Field (^o^)

今日はIdentity Fieldの実装。SQL Server 2005でIDENTITYの指定は行わずにキーテーブルを使用した実装を行うことにした。

まずデータベース上にキーテーブルを作成する。DDLはこんな感じ。

USE [Library]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrimaryKeys](
　[TableName] [nvarchar](20) COLLATE Japanese_CI_AS NOT NULL,
　[NextId] [int] NOT NULL,
　CONSTRAINT [PK_PrimaryKeys] PRIMARY KEY CLUSTERED 
　(
　　[TableName] ASC
　)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Library].[dbo].[PrimaryKeys]
　([TableName], [NextId])
　VALUES
　('Users', 1)
GO
INSERT INTO [Library].[dbo].[PrimaryKeys]
　([TableName], [NextId])
　VALUES
　('Categories', 1)
GO
INSERT INTO [Library].[dbo].[PrimaryKeys]
　([TableName], [NextId])
　VALUES
　('Rentals', 1)
GO

＃初期値として各テーブル毎のNextIdに1を入れておくのを忘れないように。

まずはテストコードから。

using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using System.Configuration;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Library.DataAccessLayer;

namespace Library.Tests.DataAccessLayer
{
　[TestClass]
　public class IdGeneratorTest
　{
　　private TransactionScope ts_;

　　[TestInitialize]
　　public void CreateTransactionScope()
　　{
　　　ts_ = new TransactionScope();
　　}

　　[TestCleanup]
　　public void DisposeTransactionScope()
　　{
　　　ts_.Dispose();
　　}

　　private int GetNextIdFromDatabase(string tableName)
　　{
　　　int nextId = 0;

　　　ConnectionStringSettings settings =
　　　　ConfigurationManager.ConnectionStrings["Library.DataAccessLayer.Properties.Settings.LibraryConnectionString"];

　　　using (SqlConnection connection = new SqlConnection(settings.ConnectionString))
　　　{
　　　　connection.Open();
　　　　SqlCommand selectCommand = new SqlCommand(
　　　　　"SELECT NextId FROM PrimaryKeys WHERE TableName = @TableName", connection);
　　　　selectCommand.Parameters.Add("@TableName", SqlDbType.NVarChar, 20).Value = tableName;

　　　　nextId = (int)selectCommand.ExecuteScalar();
　　　}

　　　return nextId;
　　}

　　[TestMethod]
　　public void データベースのNextIdフィールドは初期値として１が設定されているべき()
　　{
　　　Assert.AreEqual<int>(1, GetNextIdFromDatabase("Users"));
　　　Assert.AreEqual<int>(1, GetNextIdFromDatabase("Categories"));
　　　Assert.AreEqual<int>(1, GetNextIdFromDatabase("Rentals"));
　　}

　　[TestMethod]
　　public void 次のId取得後はデータベースのNextIdフィールドはインクリメントされるべき()
　　{
　　　int nextIdFromDatabase = GetNextIdFromDatabase("Users");
　　　int nextIdFromGenerator = IdGenerator.GetNextId("Users");

　　　Assert.AreEqual<int>(nextIdFromDatabase, nextIdFromGenerator);

　　　nextIdFromDatabase = GetNextIdFromDatabase("Users");

　　　Assert.AreEqual<int>(nextIdFromDatabase, nextIdFromGenerator + 1);
　　}
　}
}

テストをパスした実装コードはこうなった。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using Library.DataAccessLayer.LibraryDataSetTableAdapters;

namespace Library.DataAccessLayer
{
　public static class IdGenerator
　{
　　public static int GetNextId(string tableName)
　　{
　　　int nextId = 0;

　　　using (TransactionScope ts = new TransactionScope())
　　　{
　　　　using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.LibraryConnectionString))
　　　　{
　　　　　connection.Open();
　　　　　SqlCommand selectCommand = new SqlCommand(
　　　　　　"SELECT NextId FROM PrimaryKeys WITH (UPDLOCK) WHERE TableName = @TableName", connection);
　　　　　selectCommand.Parameters.Add("@TableName", SqlDbType.NVarChar, 20).Value = tableName;

　　　　　nextId = (int)selectCommand.ExecuteScalar();

　　　　　SqlCommand updateCommand = new SqlCommand(
　　　　　　"UPDATE PrimaryKeys SET NextId = @NextId WHERE TableName = @TableName", connection);
　　　　　updateCommand.Parameters.Add("@NextId", SqlDbType.Int).Value = nextId + 1;
　　　　　updateCommand.Parameters.Add("@TableName", SqlDbType.NVarChar, 20).Value = tableName;
　　　　　updateCommand.ExecuteNonQuery();
　　　　}

　　　　ts.Complete();   
　　　}

　　　return nextId;
　　}
　}
}

NextIdの読み取りは更新ロック読み取りでTransactionScopeで囲ってやる。

＃id:afukuiさんにいろいろと教えてもらって助かった。

次にキーテーブルのトランザクションをキーテーブルを使用するテーブルの更新トランザクションと分離するために各DataTableのパーシャルクラスで行挿入メソッドをオーバーロードした。これらのメソッドは通常データベースに接続することなくインメモリで実行されるので更新トランザクションとは分離できるだろう。

using System;

namespace Library.DataAccessLayer {

　partial class LibraryDataSet
　{
　　partial class UsersDataTable
　　{
　　　public UsersRow AddUsersRow(string userName)
　　　{
　　　　int userId = IdGenerator.GetNextId(this.TableName);
　　　　UsersRow user = AddUsersRow(userId, userName);

　　　　return user;
　　　}
　　}
　
　　partial class RentalsDataTable
　　{
　　　public RentalsRow AddRentalsRow(BooksRow parentBooksRow, UsersRow parentUsersRow, DateTime rentalDate, DateTime returnDate, int isComplete)
　　　{
　　　　int rentalId = IdGenerator.GetNextId(this.TableName);
　　　　RentalsRow rental = AddRentalsRow(rentalId, parentBooksRow, parentUsersRow, rentalDate, returnDate, isComplete);

　　　　return rental;
　　　}
　　}
　
　　partial class CategoriesDataTable
　　{
　　　public CategoriesRow AddCategoriesRow(string categoryName)
　　　{
　　　　int categoryId = IdGenerator.GetNextId(this.TableName);
　　　　CategoriesRow category = AddCategoriesRow(categoryId, categoryName);

　　　　return category;
　　　}
　　}
　}
}