WebSurfer's Home

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

固定長パラメータの 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

SQL Server の IN 演算子の利用

by WebSurfer 2010年8月5日 12:31

DB のテーブルから一つの項目を選び、その項目の詳細を表示するというケースはよくあると思います。そのようなサンプルは Web から容易に見つかると思います。

では、一度に複数の項目を選んで、複数の項目の詳細を同時に表示するにはどうすればいいでしょうか?

SQL Server には IN 演算子というものがあって、それを利用するのがよさそうです。以下は SQL Server 2005 Online Book のサンプルです。

SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c 
    ON e.ContactID = c.ContactID
WHERE e.Title IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');

ASP.NET のアプリでは、CheckBoxList で複数の項目を選んで、Button クリックで GridView に選んだ項目の詳細を表示することを考えます。

ユーザーの選択結果によって動的にクエリ(上記で言うと、IN (...) の ... の部分)を変える必要があります。

他にもっとスマートなやり方があるかもしれませんが、プログラムで SELECT クエリを組み立てて処置するサンプルを書いておきます。

DB は Microsoft が無償提供している Northwind サンプルデータベースの Orders テーブルを使用しました。

<%@ Page Language="C#" %>

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

<script runat="server">

  protected void Button1_Click(object sender, EventArgs e)
  {
    string selectedItems = "(";
    int count = 0;

    for (int i = 0; i < CheckBoxList1.Items.Count; i++)
    {
      if (CheckBoxList1.Items[i].Selected)
      {
        selectedItems += CheckBoxList1.Items[i].Value + ", ";
        count++;
      }
    }

    if (count > 0)
    {
      selectedItems = 
        selectedItems.TrimEnd(new char[] { ',', ' ' });
      selectedItems += ")";
      SqlDataSource2.SelectParameters.Clear();
      SqlDataSource2.SelectCommand = 
        "SELECT * FROM [orders] WHERE [EmployeeID] IN "
        + selectedItems + " ORDER BY [EmployeeID]";
    }
    else
    {
      SqlDataSource2.SelectParameters.Clear();
    }       
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title>無題のページ</title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:SqlDataSource ID="SqlDataSource1" 
      runat="server"
      ConnectionString="<%$ ConnectionStrings:Northwind %>"
      SelectCommand="SELECT DISTINCT [EmployeeID] FROM [Orders] ORDER BY [EmployeeID]">
    </asp:SqlDataSource>
    <asp:CheckBoxList ID="CheckBoxList1" 
      runat="server"
      DataSourceID="SqlDataSource1" 
      DataTextField="EmployeeID"
      DataValueField="EmployeeID">
    </asp:CheckBoxList>
   
    <asp:Button ID="Button1" 
      runat="server" 
      Text="Show Selected Data"
      onclick="Button1_Click" />
       
    <asp:SqlDataSource ID="SqlDataSource2" 
      runat="server"
      ConnectionString="<%$ ConnectionStrings:Northwind %>" >
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" 
      runat="server" 
      DataSourceID="SqlDataSource2">
    </asp:GridView>   
  </div>
  </form>
</body>
</html>

Tags: ,

ASP.NET

SqlDataSource と楽観的同時実行制御

by WebSurfer 2010年8月5日 12:28

VS2005 (Visual Studio 2005) で作る SqlDataSource の楽観的同時実行制御用クエリは、NULL に対応できないという問題があります。

すでに VS2010 がリリースされている 2010 年 8 月現在、2 世代前の VS2005 を使っている方はそれほど多くないと思いますが、念のため書いておきます。

VS2005 の SqlDataSource 作成ウィザードで、楽観的同時実行制御オプションを有効にしてクエリを自動生成した際、UPDATE, DELETE クエリが NULL に対応できていません。

例えば、memo というフィールドが NULL 許容の場合、以下のようになるべきところ、

WHERE (([memo] = @original_memo) OR ([memo] IS NULL AND @original_memo IS NULL)) ...

VS2005 では以下のようになってしまいます。

WHERE [memo] = @original_memo ...

SQL Server では、= や != などの比較演算子では、引数のいずれかまたは両方が NULL の場合、UNKNOWN が返されます。

従って、DB のフィールドに NULL があると、WHERE 句の条件が成立せず、クエリが実行されない(更新/削除に失敗する)という問題があります。

この問題に対応するには、手作業で、上記のように IS NULL を使って、クエリを書き直してやる必要があります。

VS2008 ではこのあたりは改善されています。VS2010 は未確認です(まさか元に戻っているようなことはないと思いますが)。

なお、型付 DataSet + TableAbapter を、Visual Studio のウィザードを利用して作る場合は、VS2005 でも上記のような問題はありません。

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