WebSurfer's Home

トップ > Blog 1   |   ログイン
APMLフィルター

Access の更新

by WebSurfer 2010年9月4日 20:01

Visual Studio のウィサードを使うと、SQL Server や Access のテーブルを表示して、レコードを INSERT, DELETE, UPDATE するプログラムが簡単に作れます。

ただし、Access でオートナンバー(SQL Server で言うと IDENTITY)を使っている場合、INSERT した時に DB 側で設定したオートナンバー値を DataSet に書き込むところまでは面倒を見てくれません。(SQL Server の場合は面倒見てくれます)

これは、JET データベースエンジン(Microsoft Access およびその他の小規模アプリケーションで使用されている)では、単一バッチで複数のステートメントを実行できないということが理由のようです。

詳しくは、MSDN ライブラリの @@IDENTITY クライシスを管理する の「Microsoft Access/JET の問題」のセクションを参照してください。

ここでは、DataAdapter の RowUpdated イベントを使用して自動生成されたコードとは別のクエリを実行し、オートナンバー値を DataSet に書き込む具体的な例を紹介します。

まず、いつもの手順で型付 DataSet を作り、データソースウィンドウからテーブルを Form にドラッグ&ドロップしてアプリケーションを作ります。以下の画像がその例です。ここまではコードは一行も書く必要がありません。

Visual Studio でのアプリ作成

ただし、ここまでの実装では、新しいレコードを INSERT したとき、オートナンバーとなっている ID の値が DataSet に書き戻されていないので、DataGridView に表示されている ID 値は正しくありません。

そこで、INSERT 直後に発生する DataAdapter の RowUpdated イベントのハンドラで、"SELECT @@IDENTITY" クエリを使って新規 ID 値を取得し、それを DataSet に書き込んでやります。

具体的には、TableAdapter を partial class を使って拡張します。ソリューションにクラスファイルを追加して、以下のようなコードを実装します。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace AccessWithAutoNumberAndDataSet.DataSet1TableAdapters 
{
  public partial class XXXTableAdapter
  {
    public void SetHandler()
    {
      this.Adapter.RowUpdated += 
        new OleDbRowUpdatedEventHandler(Adapter_RowUpdated);
    }

    private OleDbCommand cmd = 
      new OleDbCommand("SELECT @@IDENTITY", null);

    private void Adapter_RowUpdated(Object sender, OleDbRowUpdatedEventArgs e)
    {
      cmd.Connection = e.Command.Connection;
      cmd.Transaction = e.Command.Transaction;

      if (e.StatementType == StatementType.Insert && 
        e.Status == UpdateStatus.Continue)
      {
        object obj = cmd.ExecuteScalar();

        if (obj != null && obj.GetType() != typeof(DBNull))
        {
          e.Row["ID"] = (int)obj;
          e.Row.AcceptChanges();
        }
      }
    }
  }
}

これだけでは、ハンドラがイベントにアタッチされていないので、自動生成された Form のコードのコンストラクタに、上記 partial class で定義した SetHandler メソッドを追記します。以下のような感じです。

namespace AccessWithAutoNumberAndDataSet
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();

      // これを追加。
      this.xXXTableAdapter.SetHandler();
    }

これで、新規 ID 値が DataSet に書き込まれ、DataGridView に表示されるようになります。

Tags: , ,

ADO.NET

固定長パラメータの LIKE 比較

by WebSurfer 2010年9月3日 22:35

Transact-SQL の LIKE 句を使用して文字列の比較を行うときは、パターン文字列と比較対象文字列の中の空白の取り扱いに注意が必要です。

詳しくは MSDN ライブラリの LIKE (Transact-SQL) を見てもらうとして、ここでは ADO.NET や Visual Studio のウィザードで作る TableAdapter を利用する場合の注意事項を、例をあげて書いておきます。

例として、SQL Server DB に TestTable という名前のテーブルがあり、以下のフィールドを持っているとします。

FieldA  nchar(5)
FieldB  nchar(20)

TeatTable は以下の内容になっています。(下の画像では TestTable2 になっていますが、気にしないでください。(笑))

TeatTable の内容

この Table を、以下の SELECT クエリを用いて、FieldA の 前 2 文字を指定して検索を行うとします。

SELECT FieldA, FieldB
FROM   TestTable
WHERE  (FieldA LIKE @FieldA + N'%')

まず、Visual Studio のウィザードを利用して、上記の SELECT クエリをベースに型付 DataSet + TableAdapter を作ります。デザイン画面で見ると、以下のようになります。

型付 DataSet + TableAdapter

TableAdapter 作成された GetData メソッドを使って、その引数を "01" としてデータを抽出してみます。

結果は "01" しか抽出されません。FieldA = "01" で、"01", "01111", "012", "01234", "01457" がマッチするように思えますが、"01" しか抽出されないのは何故でしょう?

理由は以下のとおりです。

自動生成される TableAdapter のコードは、nchar(5) の場合、パラメータの追加部分は以下のようになります。

this._commandCollection[0].Parameters.Add(
  new global::System.Data.SqlClient.SqlParameter(
    "@FieldA", global::System.Data.SqlDbType.NChar, 5, 
    global::System.Data.ParameterDirection.Input, 0, 0, 
    "FieldA", global::System.Data.DataRowVersion.Current, 
    false, null, "", "", ""
  )
);

この ... SqlDbType.NChar, 5, ... というところが問題のようです。SqlParameter.Size に 5 が設定されるので、FieldA = "01" の場合、クエリは LIKE '01   %'(01 と % の間に 3 文字空白)となって、"01"(実際は 01 の後に空白 3 文字あり) 以外はマッチしないということのようです。

MSDN ライブラリの SqlParameter.Size プロパティ の解説には "固定長データ型では、Size の値は無視されます。" と書いてあったんですが・・・

解決策は以下のとおりです。

  1. FieldA の型を変更できるのであれば、nchar(5) を nvarchar(5) に変更して、型付 DataSet + TableAdapter を作り直す。
  2. FieldA の型を変更できなければ、ウィザードベースで GetData メソッドを作るのは諦めて、自力でコードを書いて TableAdapter を拡張する。

上記 2 ですが、パラメータの追加の部分を以下のようにすると期待通りの結果になります。自力で書くのは面倒だからといって、自動生成されたコードを書き直すと、思わぬところで不具合を生じる可能性がありますので止めた方がいいです。

command1.Parameters.Add(
  new SqlParameter("@FieldA", SqlDbType.NChar));
command1.Parameters["@FieldA"].Value = "01";

または、

command1.Parameters.AddWithValue("@FieldA", "01");

なお、先の記事 SqlParameter の Size 指定 で、長さ(SqlParameter.Size)の指定は 0 が良いと書きましたが、それは今回のケースでも当てはまっていました。以下のようにしても OK です。

command1.Parameters.Add(
  new SqlParameter("@FieldA", SqlDbType.NChar, 0, "FieldA"));
command1.Parameters["@FieldA"].Value = "01";

------------ 2010/9/4 追記 ------------

パターン文字列の生成に使う型(パラメータ設定のデータ型)と比較対象文字列の型(テーブル定義のデータ型)を合わせる必要はないということに気がつきました。

パラメータ設定で SqlDbType.NChar を SqlDbType.NVarChar に変更しても OK です。すなわち以下のようにすれば、nvarchar(5) で文字列を組み立てるので、パターン文字列は '01%' となるはずです。

command1.Parameters.Add(
  new SqlParameter("@FieldA", SqlDbType.NVarChar, 5, "FieldA"));
command1.Parameters["@FieldA"].Value = "01";

要するに、パラメータ設定で、SqlDbType.NChar とすると MSDN ライブラリの LIKE (Transact-SQL) の解説のセクションにある 2 つの SQL の前者、SqlDbType.NVarChar とすると後者のようになるということのようです。

Tags: , ,

ADO.NET

2 つのテーブルの同時更新

by WebSurfer 2010年8月21日 15:21

SQL Server DB の 2 つのテーブルから、INNER JOIN 句を用いてデータを抽出して GridView に表示し、それを編集・更新 (UPDATE) する Web アプリを考えます。

テーブルが 1 つなら、コードは一行も書かずに SqlDataSource を利用してウィザードベースでアプリを作成できますが、2 つのテーブルを一括更新する場合はそう簡単にはいきません。

それでも、できるだけ自力でコードを書かないで、ObjectDataSource と型付 DataSet + TableAdapter をウィザードベースで作って実現できないか考えて見ました。

ベースは MSDN ライブラリのチュートリアル「Walkthrough: Performing Bulk Updates to Rows Bound to a GridView Web Server Control 」で、そのテーブルを以下の 2 つのテーブルに変更することにします。(チュートリアルには日本語版もあったのですが、リンク切れになってしまいました)

CREATE TABLE [dbo].[table-1](
    [no] int NOT NULL,
    [name] nvarchar(50) NOT NULL,
 CONSTRAINT [PK_table-1] PRIMARY KEY CLUSTERED ([no] ASC)
)

CREATE TABLE [dbo].[table-2](
	[no] int NOT NULL,
	[tel] nvarchar(50) NOT NULL,
	[ban] nvarchar(50) NOT NULL,
 CONSTRAINT [PK_table-2] PRIMARY KEY CLUSTERED ([no] ASC)
)

まず、Visual Studio のウィザードで、型付 DataSet + TableAdapter(xsd ファイル)をつくります。以下のように、クエリビルダを使えば SELECT クエリは簡単に作成でき、それを基にした型付 DataSet も自動生成されます。

クエリビルダ

ただし、ここまでで自動生成された TableAdapter のコードには、更新に必要なメソッドは含まれていません。この先、以下のような方法で、2 つのテーブルを同時に UPDATE するコードを実装します。できるだけ自力でコードを書かないというのが条件です。

xsd ファイルを開いて、その TableAdapter にツールボックスから Query をドラッグ&ドロップ。クエリビルダで tablle-1 を UPDATE するクエリを作り、適当なメソッド名(例: UpdateQuery1)をつけて保存します。

同様に、table-2 を UPDATE するクエリを作り、適当なメソッド名(例:UpdateQuery2)をつけて保存します。結果は、以下のようになるはずです。

xsd ファイル

App_Code フォルダにクラスファイルを追加。UpdateQuery1 と UpdateQuery2 を使って2つのテーブルを UPDATE するメソッドを TableAdapter の partial class として実装します。ここは自力でコードを書く必要があります。以下のようになります。

using System;
using System.Data;
using System.Collections.Generic;
using System.Web;
using System.Transactions;
using System.ComponentModel;

namespace TwoTableDataSetTableAdapters
{
  public partial class DataTable1TableAdapter
  {
    [DataObjectMethod(DataObjectMethodType.Update)]
    public int UpdateTwoTables(int original_no, string name, string tel)
    {
      int returnValue;

      using (TransactionScope scope = 
        new TransactionScope(TransactionScopeOption.RequiresNew))
      {
        returnValue = this.UpdateQuery1(name, original_no);
        returnValue += this.UpdateQuery2(tel, original_no);
        scope.Complete();
      }
      return returnValue;
    }
  }
}

ObjectDataSource の「データソースの構成」で UPDATE メソッドに上記の partial class に作ったメソッドを選択します。

GridView を上記の ObjectDataSource に接続し、参考にした MSDN ライブラリのチュートリアルに従って、Template を編集し、コードを実装します。以下のようになります。

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
  // http://msdn.microsoft.com/ja-jp/library/aa992036(VS.80).aspx を参照
    
  private bool tableCopied = false;
  private DataTable originalDataTable;

  // 最初の行のバインディング中に、元のデータベース値のコピーが 
  // DataTable オブジェクトに格納され、さらにこのオブジェクトが 
  // ViewState に格納されます。
  protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
  {
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
      if (!tableCopied)
      {
        originalDataTable = 
          ((DataRowView)e.Row.DataItem).Row.Table.Copy();
        ViewState["originalValuesDataTable"] = originalDataTable;
        tableCopied = true;
      }
    }
  }

  protected void UpdateButton_Click(object sender, EventArgs e)
  {
    originalDataTable = (DataTable)ViewState["originalValuesDataTable"];

    // GridView コントロールの行を反復処理し、各行に対してカスタムの 
    // IsRowModified 関数を呼び出します。
    foreach (GridViewRow r in GridView1.Rows)
    {
      if (IsRowModified(r))
      {
        GridView1.UpdateRow(r.RowIndex, false);
      }
    }

    // Rebind the Grid to repopulate the original values table.
    tableCopied = false;
    GridView1.DataBind();
  }

  // このプロシージャは、編集可能な各 TextBox コントロールの値と、
  // キャッシュされた DataTable オブジェクトに格納された値の文字列
  // 比較を実行します。行が変更されている場合は true を返します。
  protected bool IsRowModified(GridViewRow r)
  {
    int currentNo = Convert.ToInt32(GridView1.DataKeys[r.RowIndex].Value);
    string currentName = ((TextBox)r.FindControl("nameTextBox")).Text;
    string currentTel = ((TextBox)r.FindControl("telTextBox")).Text;
    // フィルタ基準と一致するすべての DataRow オブジェクトを主
    // キーの順に(主キーがない場合は追加された順に) 配列として
    // 取得します。
    DataRow row = 
      originalDataTable.Select(String.Format("no = {0}", currentNo))[0];

    if (!currentName.Equals(row["name"].ToString()))
    {
      return true;
    }
    if (!currentTel.Equals(row["tel"].ToString()))
    {
      return true;
    }

    return false;
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title>無題のページ</title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:ObjectDataSource ID="ObjectDataSource1" 
      runat="server" 
      OldValuesParameterFormatString="original_{0}" 
      SelectMethod="GetData" 
      TypeName="TwoTableDataSetTableAdapters.DataTable1TableAdapter" 
      UpdateMethod="UpdateTwoTables">
      <UpdateParameters>
        <asp:Parameter Name="original_no" Type="Int32" />
        <asp:Parameter Name="name" Type="String" />
        <asp:Parameter Name="tel" Type="String" />
      </UpdateParameters>
    </asp:ObjectDataSource>
    <asp:GridView ID="GridView1" 
      runat="server" 
      AutoGenerateColumns="False" 
      DataKeyNames="no" 
      DataSourceID="ObjectDataSource1" 
      onrowdatabound="GridView1_RowDataBound">
      <Columns>
        <asp:BoundField 
          DataField="no" 
          HeaderText="no" 
          ReadOnly="True" 
          SortExpression="no" />
        <asp:TemplateField 
          HeaderText="name" 
          SortExpression="name">
          <ItemTemplate>
            <asp:TextBox ID="nameTextBox" 
              runat="server" 
              Text='<%# Bind("name") %>'>
            </asp:TextBox>
          </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField 
          HeaderText="tel" 
          SortExpression="tel">
          <ItemTemplate>
            <asp:TextBox ID="telTextBox" 
              runat="server" 
              Text='<%# Bind("tel") %>'>
            </asp:TextBox>
          </ItemTemplate>
        </asp:TemplateField>
      </Columns>
    </asp:GridView>    
    <asp:Button ID="UpdateButton" 
      runat="server" 
      onclick="UpdateButton_Click" 
      Text="Update" />    
  </div>
  </form>
</body>
</html>

上記のコードおよび TableAdapter の partial class は自力でコードを書く必要がありますが、その他はすべて ウィザードベースでコードを書かずに実装できるはずです。

実行結果は、以下の画像にようになります。

実行時の画面

Tags: ,

ASP.NET

About this blog

2010年5月にこのブログを立ち上げました。主に ASP.NET Web アプリ関係の記事です。

Calendar

<<  2024年3月  >>
252627282912
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar