WebSurfer's Home

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

MySQL と SqlDataSource の問題

by WebSurfer 2014年11月24日 17:10

Visual Studio 2010 Professional 上で MySQL と SqlDataSource を使って ASP.NET Web Forms アプリを作った時に遭遇した問題を書きます。

クエリのテスト結果

なお、使ったのは 3 年以上前にインストールした MySQL Community Server 5.5.11 と Connector/Net 6.3.6 ですので、最新版では様子が違うかもしれません。ご注意ください。

問題は、簡単に書くと、Visual Studio の「データソースの構成」ウィザードを使ってクエリを自動生成すると、識別子がスクエアブラケット( [ ] )で囲まれますが、それが MySQL では使えないので、上の画面のようなエラーが出るということです。

具体的な再現手順を書くと、(1) Visual Studio のデザイン画面で SqlDataSource の > 印をクリック、(2) 表示される「SqlDataSource タスク」メニューの中の[データ ソースの構成...]をクリック、(3) ウィザードの「Select ステートメントの構成」画面で[テーブルまたはビューから列を指定します(T)]を選択、(4) 次の画面「クエリのテスト」に進んでテストを実行、(5) 上の画面のようなエラーが出る・・・ということです。

上のエラーメッセージで、テーブル名 employees がスクエアブラケット( [ ] )で囲まれているのが分かるでしょうか? ちなみに、ウィザードで生成されたソースは以下のようになります。(注:この例では列に * を選択しています。実際の列を指定すると列名もスクエアブラケットで囲まれます)

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  ConnectionString="<%$ ConnectionStrings:MySQL %>" 
  ProviderName="<%$ ConnectionStrings:MySQL.ProviderName %>" 
  SelectCommand="SELECT * FROM [employees]">
</asp:SqlDataSource>

もちろんこの状態で実行しても以下のようにサーバーエラーになります。

サーバーエラー

解決策は、生成されたソースのクエリからスクエアブラケットを削除するか、スクエアブラケットに換えてバッククォート( ` )で囲むことです(識別子が予約語と同じ、または特殊文字を含む場合)。

または、上の再現手順の (3) のステップで[カスタム SQL ステートメントまたはストアド プロシージャを指定する(S)]を選んで、自力でクエリを組み立ててもいいです。

ただし、SELECT だけならそれでもいいですが、INSERT, DELETE, UPDATE も必要な場合は少々面倒です。

Visual Studio のデータスース構成ウィザードを使って型付 DataSet + TableAdapter を作った場合は、識別子は正しくバッククォート( ` )で囲まれ上記のような問題はありませんので、それと ObjectDataSource を組み合わせて使う方が良さそうです。

Tags:

MySQL

SqlDataSource とトランザクション

by WebSurfer 2010年11月16日 22:52

SqlDataSource + DetailsView による更新が成功したら、変更されたフィールド名、更新前/後の値、変更日時をログとして SQL Server データベースに残す処置をトランザクションを切って行う方法です。

更新前/後の値の取得と、SqlDataSorce コントロールによる UPDATE 操作と ADO.NET のコードによる INSERT 操作のトランザクションをどのように設定するかがポイントだと思います。

まず、更新前/後の値ですが、これらは DetailsView.ItemUpdating イベントのハンドラにおいて、引数の DetailsViewUpdateEventArgs オブジェクトから Keys, OldValues, NewValues プロパティによって取得できます。

トランザクションの開始および SqlTranscation オブジェクトの取得、SqlCommand.Transaction プロパティのへの SqlTranscation オブジェクトの設定は、SqlDataSource.Updating イベントで行います。イベントハンドラの引数 SqlDataSourceCommandEventArgs から SqlConnection, SqlCommand オブジェクトへの参照を取得できますので、それらを用いて設定します。

ログを残すための INSERT 操作は、SqlDataSource.Updated イベントのハンドラで行います。イベントハンドラの引数 SqlDataSourceStatusEventArgs オブジェクトの Exception, AffectedRows プロパティを用いて、UPDATE 操作で例外が発生しなかったことと更新が行われたことが判定できます。

例外の発生がなく更新が行われたと判定されたら、ログを残すための操作(データベースへの INSERT 操作)を行います。INSERT 操作は、更新されたフィールドが複数の場合、同じ数だけ行うことになります。 INSERT 操作の途中で失敗した場合は、SqlDataSource による UPDATE 操作まで巻き戻すことになります。

そのために、引数 SqlDataSourceStatusEventArgs オブジェクトから、SqlDataSource が UPDATE 操作に用いた SqlConnection, SqlCommand, SqlTransaction オブジェクトへの参照を取得し、それらを用いて、SqlDataSource が行う UPDATE 操作と同じコネクション/トランザクションで INSERT 操作を行うように設定します。

サンプルコードは以下のとおりです。

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

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

<script runat="server">

  IOrderedDictionary keys;
  IOrderedDictionary oldValues;
  IOrderedDictionary newValues;
    
  protected void SqlDataSource2_Updating(object sender, 
    SqlDataSourceCommandEventArgs e)
  {
    SqlCommand command = (SqlCommand)e.Command;
    SqlConnection connection = command.Connection;
    connection.Open();
    SqlTransaction tx = connection.BeginTransaction();
    command.Transaction = tx;
  }
    
  protected void SqlDataSource2_Updated(object sender, 
    SqlDataSourceStatusEventArgs e)
  {
    SqlCommand command = (SqlCommand)e.Command;
    SqlConnection connection = command.Connection;
    SqlTransaction tx = command.Transaction;
    if (e.Exception == null && e.AffectedRows == 1)
    {
      string insertQuery = 
        "INSERT INTO History " + 
        "(FieldName, NewValue, OldValue, DateAndTime) " +
        "VALUES (@FieldName, @NewValue, @OldValue, @DateAndTime)";
      SqlCommand sqlCom = 
       new SqlCommand(insertQuery, (SqlConnection)connection);
      sqlCom.Transaction = (SqlTransaction)tx;
      sqlCom.Parameters.Add("@FieldName", SqlDbType.NVarChar);
      sqlCom.Parameters.Add("@NewValue", SqlDbType.NVarChar);
      sqlCom.Parameters.Add("@OldValue", SqlDbType.NVarChar);
      sqlCom.Parameters.Add("@DateAndTime", SqlDbType.DateTime);
      try
      {
        foreach (DictionaryEntry entry in oldValues)
        {
          if ((string)entry.Value != 
            (string)newValues[entry.Key])
          {
            sqlCom.Parameters["@FieldName"].Value = 
              (string)entry.Key;
            if (newValues[entry.Key] == null)
            {
              sqlCom.Parameters["@NewValue"].Value = DBNull.Value;
            }
            else
            {
              sqlCom.Parameters["@NewValue"].Value = 
                (string)newValues[entry.Key];
            }
            if (entry.Value == null)
            {
              sqlCom.Parameters["@OldValue"].Value = DBNull.Value;
            }
            else
            {
              sqlCom.Parameters["@OldValue"].Value = 
                (string)entry.Value;
            }
            sqlCom.Parameters["@DateAndTime"].Value = DateTime.Now;
            sqlCom.ExecuteNonQuery();
          }
        }
        tx.Commit();
      }
      catch (Exception)
      {
        if (tx != null)
        {
          tx.Rollback();
        }
        throw;
      }
      finally
      {
        connection.Close();
      }            
    }
    else
    {
      if (tx != null)
      {
        tx.Rollback();
      }
      connection.Close();
    }

    DropDownList1.DataBind();
  }

  protected void DetailsView1_ItemUpdating(object sender, 
    DetailsViewUpdateEventArgs e)
  {
    keys = e.Keys;
    oldValues = e.OldValues;
    newValues = e.NewValues;
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:SqlDataSource ID="SqlDataSource1" 
      runat="server" 
      ConnectionString="<%$ ConnectionStrings:MyDB %>" 
      SelectCommand="SELECT [id], [name] FROM [table]">
    </asp:SqlDataSource>
    <asp:DropDownList ID="DropDownList1" 
      runat="server" 
      DataSourceID="SqlDataSource1" 
      DataTextField="name" 
      DataValueField="id" 
      AutoPostBack="True">
    </asp:DropDownList>
    <hr />
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
      ConnectionString="<%$ ConnectionStrings:MyDB %>" 
      SelectCommand=
        "SELECT [id], [name], [price], [memo] 
        FROM [table] 
        WHERE ([id] = @id)"             
      UpdateCommand=
        "UPDATE [table] 
        SET [name] = @name, [price] = @price, [memo] = @memo 
        WHERE [id] = @original_id" 
      OnUpdated="SqlDataSource2_Updated" 
      OnUpdating="SqlDataSource2_Updating" 
      OldValuesParameterFormatString="original_{0}">
      <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" 
          Name="id" 
          PropertyName="SelectedValue" 
          Type="Int32" />
      </SelectParameters>
      <UpdateParameters>
        <asp:Parameter Name="name" Type="String" />
        <asp:Parameter Name="price" Type="Decimal" />
        <asp:Parameter Name="memo" Type="String" />
        <asp:Parameter Name="original_id" Type="Int32" />
      </UpdateParameters>
    </asp:SqlDataSource>
    <asp:DetailsView ID="DetailsView1" 
      runat="server" 
      AutoGenerateRows="False" 
      DataKeyNames="id" 
      DataSourceID="SqlDataSource2" 
      OnItemUpdating="DetailsView1_ItemUpdating" 
      EnableModelValidation="True">
      <Fields>
        <asp:BoundField DataField="id" 
          HeaderText="id" 
          InsertVisible="False" 
          ReadOnly="True" 
          SortExpression="id" />
        <asp:BoundField DataField="name" 
          HeaderText="name" 
          SortExpression="name" />
        <asp:BoundField DataField="price" 
          HeaderText="price" 
          SortExpression="price" />
        <asp:BoundField DataField="memo" 
          HeaderText="memo" 
          SortExpression="memo" />
        <asp:CommandField ShowEditButton="True" />
      </Fields>
    </asp:DetailsView>    
  </div>
  </form>
</body>
</html>

Tags: ,

ASP.NET

SqlDataSource などのパラメータ

by WebSurfer 2010年9月26日 20:16

データバウンドコントロール (GridView など) からデータソースコントロール (SqlDataSource など) へのパラメータの渡し方について調べたことを書いておきます。

(1) データバウンドコントロール

簡単に言えば、データバウンドコントロールは、DataKeyNames プロパティ、子コントロール、ビューステートなど(他に DataKey などもあるかも)からパラメータ名と値を取得し、Keys, Values, OldValues, NewValues という IDictionary コレクションを作成して、それをデータソースコントロールのパラメータに渡すということだそうです。

詳しくは、MSDN ライブラリ の「データソースコントロールがデータ連結フィールドのパラメーターを作成する方法」に書いてあります。 これによると、IDictionary コレクションは、以下のように各操作(のパラメータ)に渡されているそうです。

IDictionary
コレクション
内容 操作
Values 新しい値(主キー含む) Insert
Keys 元の主キー値 Update, Delete
NewValues 新しい値(主キー含む) Update
OldValues 元の値(主キー除く) Update, Delete

主キーも Update する場合、新しい主キー値は NewValues に含まれるとイベントハンドラのドキュメントに書いてありました。 Insert の時の新しい主キー値がどれに含まれるか、はっきり書いてあるドキュメントは見つけられませんでしたが、Values 以外にはなさそうです。

Keys, Values, OldValues, NewValues には、データバインドコントロールの Deleting, Deleted, Inserting, Inserted, Updating, Updated イベントのハンドラの引数を通じてアクセスできます。 それを使って、データベースに書き込む前の値の検証や HTML エンコードを行ったり、新旧データをログに残したりできるということです。

イベントハンドラのドキュメントで、Deleting, Deleted イベントのハンドラの引数から IDictionary コレクションにアクセスするためのプロパティ名が Keys と Values になっていることがちょっと気になります。 Keys と OldValues のはずなんですが・・・

この Values というのは単なるプロパティ名であって、中身は OldValues であろうと勝手に解釈し、上にリンクを張った MSDN ライブラリの記述は正しいと信じることにしました。(笑)

(2) データソースコントロール

データソースコントロール (SqlDataSource, ObjectDataSource など) 側では、DeleteParameters, InsertParameters, UpdateParameters パラメータコレクションに必要なパラメータが設定され、それにデータバウンドコントロールからのデータが渡されるのは間違いないようです。

ただ、それらのパラメータがどのように作られるか、Keys, Values, OldValues, NewValues との対応がどうなっているかは正直言ってよく分かりません。 想像も含みますが、自分が調べた範囲で分かったことを書いておきます。

(2.1) SqlDataSource

SqlDataSource の場合は、SELECT クエリだけは自分で組み立てるものの、後はウィザード任せで自動生成することがほとんどなので、結果として出来上がったパラメータしか見えないです。

ですが、楽観的同時実行制御を行うか否かによって、ConflictDetection, OldValuesParameterFormatString の設定が変わってきて、それによってできるパラメータが変わってくるだけのようです。

例えば、ウィザードで作った SqlDataSource のパラメータコレクションにあるパラメータと、たぶん内部的に作られる ADO.NET の SqlCommand のパラメータの関係は以下ようになっていると思われます。

<asp:Parameter Name="xxx" Type="Int32" />
    ↓
SqlCommand.Parameters.Add(new SqlParameter("@xxx", SqlDbType.Int))

上記の SqlParameter の Value に、該当する Keys, Values, OldValues, NewValues から自動的に値が渡されるようです(このあたりは外から見えないので、確証はないのですが)。

楽観的同時実行制御なしの場合、デフォルトでは OldValuesParameterFormatString が設定されない(デフォルトで "{0}" になる)ので、パラメータ名が original_xxx にはなりません。この点ちょっと紛らわしいのですが、各パラメータには、Keys, Values, NewValues から正しく値が渡されるようです。例えば、主キーが id、その他のパラメータが name とすると、以下のようになるはずです。

パラメータコレクション IDictionary
コレクション
DeleteParameters id ← Keys
InsertParameters id ← Values
name ← Values
UpdateParameters id ← Keys
name ← NewValues

楽観的同時実行制御オプションを有効にすると、ConflictDetection が "CompareAllValues" に、OldValuesParameterFormatString が "original_{0}" に自動的に設定されます。結果、DELETE, INSERT, UPDATE クエリの WHERE 句に、元の値との相違をチェックするための条件が追加され(例えば、WHERE [xxx] = @original_xxx ... のように)、それに応じてパラメータにも original_xxx が追加されます。この場合は、元の値は original_xxx に、新しい値は xxx に代入されるので分かりやすいです。例えば、主キーが id、その他のパラメータが name とすると、以下のようになるはずです。

2012/6/23 注記追加:下の表の DeleteParameters 列で original_name ← OldValues と書きましたが、Deleting, Deleted イベントのハンドラの引数から OldValues 相当の IDictionary コレクションにアクセスするためのプロパティ名は Values(OldValues ではなくて)になりますので注意してください。

パラメータコレクション IDictionary
コレクション
DeleteParameters original_id ← Keys
original_name ← OldValues
InsertParameters id ← Values
name ← Values
UpdateParameters original_id ← Keys
id ← NewValues
original_name ← OldValues
name ← NewValues

(2.2) ObjectDataSource

SQL Server のテーブルを基に、型付 DataSet + TableAdapter を作った場合、その TableAdapter クラスの中に Select, Insert, Delete, Update メソッドが定義されます。それらのメソッドをベースに ObjectDataSource をウィザードベースで作った場合は、そのメソッドに定義されている引数に合わせてパラメータが作られます。

データバウンドコントロールの Keys, Values, OldValues, NewValues コレクションは、パラメータを通じて、該当する引数に渡されます。ウィザードに任せて TableAdapter と ObjectDataSource を作れば、たぶんほとんどのケースで、渡される Keys, Values, OldValues, NewValues コレクションと、それを受け取るパラメータの間に不整合を生ずることはないはずです。

ただし、自力でメソッドのコードを書く場合や、データソースに主キーがない場合は、以下の点に注意が必要です。

(2.2.1) OldValuesParameterFormatString の設定

ウィザードでデフォルト設定のまま ObjectDataSource を作っていくと、どういう条件になっているのか分かりませんが、楽観的同時実行制御なしの場合でも OldValuesParameterFormatString="original_{0}" の設定がされることがあります。

そうすると、更新もしくは削除操作の際、主キーについては xxx と original_xxx の両方を引数に加えて Update, Delete メソッドを呼び出すようです。

楽観的同時実行制御なしで自力でメソッドのコードを書いた場合、引数に新旧両方の値を定義する必要はなく、普通はたぶん xxx のみしか定義しないと思います。 それゆえ、更新もしくは削除操作の際、"... original_xxx を含む非ジェネリックメソッド 'Update/Delete' が見つかりませんでした。" という例外がスローされ、悩むことになります。

ちょっとくどいかもしれませんが、具体的に、Microsoft ASP.NET の チュートリアル "Creating a Business Logic Layer" のコードを例にとって説明します。

サンプルコードを見ると、DeleteProduct, UpdateProduct メソッドの引数には、主キーとしては productID のみしか定義されていません。

このサンプルコードをベースにして、ObjectDataSource をウィザードで作っていった場合、引数に original_pruductID は定義されていないにもかかわらず、 OldValuesParameterFormatString="original_{0}" が設定されます(自分で試した限りですが)。

そうすると、例えば更新操作を行った場合、"ObjectDataSource 'ObjectDataSource1' では、パラメータ ..., productID, original_productID を含む非ジェネリック メソッド 'UpdateProduct' が見つかりませんでした。" という InvalidOperationException 例外がスローされます。

対応策としては、OldValuesParameterFormatString="original_{0}" を削除するか、使わなくても、xxx と original_xxx の両方を Update, Delete の引数には、定義しておくことです。

(2.2.2) データソースに主キーがない場合

データソースが xml ファイルなどで、主キーが設定されておらず、しかも更新や削除操作のため自力でそのためのクラスを書くような場合は特に注意が必要です。

例としては MSDN ライブラリの「GridView で XML ファイルをデータ ソースとして使いレコードを編集する方法」のような場合です。(そもそも、そのサンプルコードは間違っているようですが)

上記のページの例のように、「XML ファイル操作用のクラス」を作って、それをベースに GridView と ObjectDataSource をウィザードベースで作成するとうまくいきません。

id と name の両方を更新する場合、以下の変更が必要です。(サンプルは id は更新しないという前提で書かれているようですが)

  • GridView に DataKeyNames="id" を追加。そもそも DataKeyNames を定義しないと、主キーが ObjectDataSource に渡されません。
  • ObjectDataSource に OldValuesParameterFormatString="original_{0}" を追加。これがないと id の新旧の区別ができません。(上に、「楽観的同時実行制御なしの場合でも、デフォルトで OldValuesParameterFormatString="original_{0}" の設定がされます。」と書きましたが、何故かこの場合は定義されません。主キーがないから?)
  • GridView で AutoGenerateColumns="False" とし、id と name の BoundField を定義。こうしないと、id 列が TextBox にならず、更新できません。
  • 「XML ファイル操作用のクラス」の UpdateDataSet の引数に original_id を追加。original_id で DataSet の行を検索し、ヒットした行の当該項目を id に書き換えるよう修正する。

上記のリンク先の MSDN ライブラリのコードを修正し、Delete もできるようにしたサンプルを、後日、作成して書き込んでおくことにします。

Tags: ,

ASP.NET

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  2017年1月  >>
25262728293031
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar