宇宙仮面の
C# Programming

 
Image

非接続型データベースアクセスで注意すべきこと

開発環境: Visual Studio 2003 

1.目次

2.目的

データセットによるデータベースアクセスは、比較的簡単にできますが、非接続型のためのデメリットが存在します。
つまり、データセットには、Fill した時点でのデータのコピーがあり、それを使うと言う形になりますが、
その間に誰かがデータベースを更新している可能性があります。

このような、自分が編集中に誰かが変更するかもしれないことを許し、データを更新する瞬間だけロックする方法を
オプティミスティックロック(楽観的排他)と言います。
このメリットは、ロックの期間が少なくてすむので、パフォーマンスを稼げる点です。
デメリットは、更新しようと思ったら、もうすでに誰かが更新しているばあい、もう一度最新のデータを読み直して編集しなければならない点です。

このように、データセットによるデータベースアクセスは、複数の同時アクセスがある場合に
データセットとデータベース間のコンシステンシーが失われるため、処理をちゃんと考えておかないと、
例外が上がったり、間違った処理になる可能性があるので、注意が必要です。

3.参考書

(1) ZEBRA さんに感謝!

4.UPDATE 時のコンフリクト

 

テストプログラムを作って、同時更新を行って、どのような動作になるのか確認してみます。

(1) まず、テスト用のデータベースとして、次のような Test テーブルを作ります。
主キー ID は、IDENTITY =”はい”にして、オートナンバー型にします。

注意
.NET Technology を読んでいたら、 インクリメント=1では問題があることがわかりました。

例えば、データセットで ID = 1, ID = 2 を挿入したところへ、別のクライアントがID = 1 を INSERT した場合、
データセットを INSERT しようとすると、ID=1 はすでにふられているので、ID = 2 をふろうとしますが、
ID =2はすでに使われているので、

System.Data.ConstraintException
追加情報 : 列 'ID' は一意であるように制約されています。値 xxxxx は既に存在します。
という例外が上がります。

これを避けるためには、
IDENTITY シードを -1
IDENTITY インクリメントを -1

にすることにより、避けられるはず???でも、確率的には0ではないような。。。。



Image

(2) データベースに、次のようにデータを入力します。
Image

(3) 4章のテストツールを起動します。

この段階で、Form1 のDataSet にデータベースからのデータが読み込まれ、データベースとの接続は切断されます。
Image

(4) 次にサーバエクスプローラでデータベースを次のように書き換えます。

このとき、コンテキストメニューから ! 実行(R)を実行しないと、データベースは更新されないので注意してください。
Image

(5) この時点で、Form1 のDataSetのデータと、データベースのデータは一致していない状態になります。
この時、Form1 から DataSet を更新して、Update をかけてみましょう。
次のように、”同時実行違反 : UpdateCommand によって 0件処理されました。”というエラーになります。
このエラーダイアログは、DBConcurrencyException 例外を捕まえて、メッセージボックスを出すようにプログラムしています。
Image

このように、同時実行違反に関しては、.NET フレームワークが検出してくれますが、その例外処理を正しく行う必要があります。

5.DELETE 時のコンフリクト


同様に、Form1 を起動後に、サーバエクスプローラによって行を削除します。
データベースではすでに削除された行は、Form1 のデータグリッドではまだ残っています。
これを Form1 から削除すると、次のように "同時実行違反 : DeleteCommand によって 0件処理されました。"
このように、DELETE 時でも同じことが起こります。

Image

6.INSERT 時のコンフリクト


同様に、INSERT時の動作を調べてみます。

(1) サーバエクスプローラで次のようなデータを作ります。

Image

(2) Form1 を起動します。
Image

(3) サーバエクスプローラで 1行追加します。
Image

すると 主キー ID は、IDENTITY (オートナンバー)になっているので、自動的にID=16が振られます。

(4) この状態では、データベースでは ID = 16 の行が追加されていますが、Form1のデータセットは15行までしかない状態です。
この状態で Form1 のdata に追加してみます。この時点では、更新ボタンを押さないでください。
すると、次のように ID = 16 で行が追加されます。

Image

(5) ここで、Form1 の更新ボタンを押してみます。
すると、ID が17に変更されているのがわかります。
つまり、ID に関しては自動的に処理してくれることがわかります。
しかし、ID = 16 は表示されていません。
Image

(6) 再読み込みをしてみます。
次のように、正しく INSERT されていることがわかります。
ただ、IDが勝手に変更されてしまうので注意が必要です。
Image

7.排他処理するには?

上記の方法だと、いつの間にか誰かが変更していて、せっかくデータを入力したデータが無効になってしまいます。
それを避けるには、排他処理をするしかありません。

具体的には、、、、、

排他用のカラムに時間を持たせておいて、一人のユーザがそのデータにアクセスする場合は、ロックをかけます。
他のユーザがデータを更新しようとした場合に、その排他用のカラムがある一定時間内の場合に限り、更新できないような仕組みにします。
ここで、ある一定時間といっているのは、最初に読み込んだユーザがシステムダウンする場合を考慮しています。
排他用のカラムに時間を採用しているのは、ユーザがシステムダウンした場合を考慮しています。
排他用のカラムが単なるビットだと、システムダウンした場合は半永久にロックがかかったままになってしまうのです。

8.テスト用ソースコード

変更履歴
2003/7/28   初版作成

Form1.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace DBTest
{
        /// <summary>
        /// Form1 の概要の説明です。
        /// </summary>
        public class Form1 : System.Windows.Forms.Form
        {
        private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
        private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
        private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
        private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
        private System.Data.SqlClient.SqlConnection sqlConnection1;
        private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
        private System.Windows.Forms.DataGrid dataGrid1;
        private DBTest.DataSet1 dataSet11;
        private System.Windows.Forms.Button btnUpdate;
        private System.Windows.Forms.Button btnRefresh;
                /// <summary>
                /// 必要なデザイナ変数です。
                /// </summary>
                private System.ComponentModel.Container components = null;

                public Form1()
                {
                        //
                        // Windows フォーム デザイナ サポートに必要です。
                        //
                        InitializeComponent();

                        //
                        // TODO: InitializeComponent 呼び出しの後に、コンストラクタ コードを追加してください。
                        //
                }

                /// <summary>
                /// 使用されているリソースに後処理を実行します。
                /// </summary>
                protected override void Dispose( bool disposing )
                {
                        if( disposing )
                        {
                                if (components != null) 
                                {
                                        components.Dispose();
                                }
                        }
                        base.Dispose( disposing );
                }

                #region Windows フォーム デザイナで生成されたコード 
                /// <summary>
                /// デザイナ サポートに必要なメソッドです。このメソッドの内容を
                /// コード エディタで変更しないでください。
                /// </summary>
                private void InitializeComponent()
                {
            this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
            this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
            this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
            this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
            this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
            this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
            this.dataGrid1 = new System.Windows.Forms.DataGrid();
            this.dataSet11 = new DBTest.DataSet1();
            this.btnUpdate = new System.Windows.Forms.Button();
            this.btnRefresh = new System.Windows.Forms.Button();
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
            this.SuspendLayout();
            // 
            // sqlSelectCommand1
            // 
            this.sqlSelectCommand1.CommandText = "SELECT ID, data FROM Test";
            this.sqlSelectCommand1.Connection = this.sqlConnection1;
            // 
            // sqlConnection1
            // 
            this.sqlConnection1.ConnectionString = "workstation id=UCHUKAMEN;packet size=
            4096;integrated security=SSPI;data source=UC" +
                "HUKAMEN;persist security info=True;initial catalog=test";
            // 
            // sqlInsertCommand1
            // 
            this.sqlInsertCommand1.CommandText = "INSERT INTO Test(data) VALUES (@data); 
            SELECT ID, data FROM Test WHERE (ID = @@ID" +
                "ENTITY)";
            this.sqlInsertCommand1.Connection = this.sqlConnection1;
            this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter(
            "@data", System.Data.SqlDbType.NVarChar, 50, "data"));
            // 
            // sqlUpdateCommand1
            // 
            this.sqlUpdateCommand1.CommandText = "UPDATE Test SET data = 
            @data WHERE (ID = @Original_ID) AND (data = @Original_data" +
                " OR @Original_data IS NULL AND data IS NULL); SELECT ID, data FROM Test WHERE (I" +
                "D = @ID)";
            this.sqlUpdateCommand1.Connection = this.sqlConnection1;
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter(
            "@data", System.Data.SqlDbType.NVarChar, 50, "data"));
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter(
            "@Original_ID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, 
            ((System.Byte)(0)), ((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter(
            "@Original_data", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, false, 
            ((System.Byte)(0)), ((System.Byte)(0)), "data", System.Data.DataRowVersion.Original, null));
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter(
            "@ID", System.Data.SqlDbType.Int, 4, "ID"));
            // 
            // sqlDeleteCommand1
            // 
            this.sqlDeleteCommand1.CommandText = "DELETE FROM Test WHERE (ID = @Original_ID) AND 
            (data = @Original_data OR @Origina" +
                "l_data IS NULL AND data IS NULL)";
            this.sqlDeleteCommand1.Connection = this.sqlConnection1;
            this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter(
            "@Original_ID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, 
            ((System.Byte)(0)), ((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
            this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter(
            "@Original_data", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, false, 
            ((System.Byte)(0)), ((System.Byte)(0)), "data", System.Data.DataRowVersion.Original, null));
            // 
            // sqlDataAdapter1
            // 
            this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
            this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
            this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
            this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
            new System.Data.Common.DataTableMapping("Table", "Test", new System.Data.Common.DataColumnMapping[] {
            new System.Data.Common.DataColumnMapping("ID", "ID"),
            new System.Data.Common.DataColumnMapping("data", "data")})});
            this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
            // 
            // dataGrid1
            // 
            this.dataGrid1.Anchor = ((System.Windows.Forms.AnchorStyles)
            ((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
                | System.Windows.Forms.AnchorStyles.Left) 
                | System.Windows.Forms.AnchorStyles.Right)));
            this.dataGrid1.DataMember = "Test";
            this.dataGrid1.DataSource = this.dataSet11;
            this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.dataGrid1.Location = new System.Drawing.Point(0, 48);
            this.dataGrid1.Name = "dataGrid1";
            this.dataGrid1.Size = new System.Drawing.Size(260, 112);
            this.dataGrid1.TabIndex = 0;
            // 
            // dataSet11
            // 
            this.dataSet11.DataSetName = "DataSet1";
            this.dataSet11.Locale = new System.Globalization.CultureInfo("ja-JP");
            // 
            // btnUpdate
            // 
            this.btnUpdate.Location = new System.Drawing.Point(16, 8);
            this.btnUpdate.Name = "btnUpdate";
            this.btnUpdate.Size = new System.Drawing.Size(56, 24);
            this.btnUpdate.TabIndex = 1;
            this.btnUpdate.Text = "更新";
            this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
            // 
            // btnRefresh
            // 
            this.btnRefresh.Location = new System.Drawing.Point(88, 8);
            this.btnRefresh.Name = "btnRefresh";
            this.btnRefresh.Size = new System.Drawing.Size(80, 24);
            this.btnRefresh.TabIndex = 2;
            this.btnRefresh.Text = "再読み込み";
            this.btnRefresh.Click += new System.EventHandler(this.btnRefresh_Click);
            // 
            // Form1
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 12);
            this.ClientSize = new System.Drawing.Size(256, 158);
            this.Controls.Add(this.btnRefresh);
            this.Controls.Add(this.btnUpdate);
            this.Controls.Add(this.dataGrid1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.Form1_Load);
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
            this.ResumeLayout(false);

        }
                #endregion

                /// <summary>
                /// アプリケーションのメイン エントリ ポイントです。
                /// </summary>
                [STAThread]
                static void Main() 
                {
                        Application.Run(new Form1());
                }

        private void Form1_Load(object sender, System.EventArgs e)
        {
            this.sqlDataAdapter1.Fill(this.dataSet11);        
        }

        private void btnUpdate_Click(object sender, System.EventArgs e)
        {
            try
            {
                this.sqlDataAdapter1.Update(this.dataSet11, "Test");
            }
            catch (DBConcurrencyException exc)
            {
                MessageBox.Show(exc.Message,
                    this.Text,  MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.dataSet11.Clear();
                this.sqlDataAdapter1.Fill(this.dataSet11);     
            }
        
        }

        private void btnRefresh_Click(object sender, System.EventArgs e)
        {
            this.dataSet11.Clear();
            this.sqlDataAdapter1.Fill(this.dataSet11);        
        }
        }
}