WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

NULL 条件でのレコード抽出

by WebSurfer 18. June 2017 18:02

Visual Studio のデータソース構成ウィザードを使って、@IT の記事「Microsoft Visual Studio 2005 による Web アプリケーション構築技法」の「D. テーブルアダプタへのクエリ追加」のセクションに書いてあるように、特定の条件で DataSet / DataTable を生成するメソッドを作ることができます。

以下の画像はサンプルデータベース Northwind の Orders テーブルから ShippedDate が NULL のレコードを抽出して DataGridView に表示したものですが、このようなことができるメソッドを作るにはどうすればよいかということを書きます。

ShippedDate が NULL のレコードを抽出

SELECT クエリで WHERE ShippedDate = @ShippedDate という条件で TableAdapter にメソッドを追加すると、そのメソッドの引数に null が渡された場合は、メソッド内部でパラメータ @ShippedDate に DBNull.Value が代入されるコードが生成されます。

しかしながら、それでは ShippedDate が NULL のレコードを抽出することはできません。ShippedDate が NULL のレコードを抽出するためには WHERE ShippedDate IS NULL とする必要があります。

ではどうすればいいかですが、クエリビルダで元となる SELECT クエリを作成する際、@IsNull_ShippedDate(名前は任意)というような引数に null が渡されたか否かを判定するパラメータを追加し、以下の画像のように WHERE 句を組み立ててそれをベースにメソッドを生成します。

クエリビルダ

これにより、DataTable を返す GetDataByNullableShippedDate メソッド(名前は任意)の例ですが、以下のようなコードが生成されます。(注:自動生成されたコードそのものではなく、読みやすくするため改行等を行っています)

public virtual OrdersDataTable GetDataByNullableShippedDate(
               int? IsNull_ShippedDate, DateTime? ShippedDate)
{
  this.Adapter.SelectCommand = this.CommandCollection[1];
  if (IsNull_ShippedDate.HasValue == true) 
  {
    this.Adapter.SelectCommand.Parameters[0].Value = 
                                (int)IsNull_ShippedDate.Value;
  }
  else
  {
    this.Adapter.SelectCommand.Parameters[0].Value = 
                                                DBNull.Value;
  }

  if (ShippedDate.HasValue == true)
  {
    this.Adapter.SelectCommand.Parameters[1].Value = 
                                 (DateTime)ShippedDate.Value;
  }
  else
  {
    this.Adapter.SelectCommand.Parameters[1].Value = 
                                                DBNull.Value;
  }
  OrdersDataTable dataTable = new OrdersDataTable();
  this.Adapter.Fill(dataTable);
  return dataTable;
}

この GetDataByNullableShippedDate メソッドを以下のように呼び出して DataTable を取得し DataGridView に表示したのが一番上の画像です。

public partial class Form14 : Form
{
  private OrdersTableAdapter adapter = new OrdersTableAdapter();
  private BindingSource bindingSource1 = new BindingSource();
  private NorthwindDataSet.OrdersDataTable table;
        
  public Form14()
  {
    InitializeComponent();

    DateTime? shippedDate = null;

    table = adapter.GetDataByNullableShippedDate(
                shippedDate.HasValue ? 0 : 1, shippedDate);

    bindingSource1.DataSource = table;
    this.dataGridView1.DataSource = bindingSource1;
  }
}    

もちろん、NULL でない特定の日付の ShippedDate のレコードも上のコードで抽出できます。

Tags: ,

ADO.NET

DropDownList での NULL の処置

by WebSurfer 23. October 2015 17:19

下の画像のように、GridView などでデータベースの更新操作を行う際 DropDownList を利用してユーザー入力に便宜を図ることがあると思います。その時、NULL を DropDownList でどう対処するかという話を書きます。

GridView 上の DropDownList

ここで紹介する例には Microsoft が提供している Northwind サンプルデータベースの Products テーブルと Categories テーブルを使用しています。

Products テーブルの中の ProductName, CategoryID フィールドを GridView 上で更新する際、CategoryID の列に DropDownList を表示するようにします。

DropDownList には、ユーザーが見ても何だか分からない数字 (CategoryID) を表示するのでははなくて、ユーザーが読んで理解できる名前 (CategoryName) を Categories テーブルから取得して表示します。

Products テーブルの CategoryID には以下のような外部キー制約がかかっていますので、Categories テーブルの CategoryID の値(Northwind サンプルデータベースでは 1 から 8)以外のものは入力できません。

ALTER TABLE [dbo].[Products]  
  ADD CONSTRAINT [FK_Products_Categories] 
  FOREIGN KEY([CategoryID])
  REFERENCES [dbo].[Categories] 
  ([CategoryID])

ただし、NULL は許可されていますので、1 から 8 以外に NULL が入っていることがあります。また、更新の際に NULL を入力したいというケースもあるかもしれません。

先の記事 GridView 上の DropDownList に ToolTip でも同様に DropDownList を使用していますが、そこでは NULL 対応は考えていませんでした。それを以下ように修正して NULL に対応します。

  1. SELECT クエリで INNER JOIN 句に替えて LEFT OUTER JOIN 句を使い CategoryID が NULL のレコードも抽出する。
  2. DropDownList で AppendDataBoundItems="True" とし、以下のような NULL 用の ListItem を追加する。

    <asp:ListItem Value="">NULL</asp:ListItem>

    Value は空白("")にしてください。DropDownList に表示される文字列 NULL は任意のものに変えていいです。データベース上での NULL と ASP.NET コントロール上での空白("")の変換は、ASP.NET の組み込み機能を利用します。その機能についての説明は MSDN ライブラリの記事データ ソース コントロールを使用した、データベースの Null 値の処理を見てください。

具体的なコードは以下の通りです。(分かりやすくするため、先の記事 GridView 上の DropDownList に ToolTip のコードの ToolTip を設定する部分は省いています)

<%@ 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">
    
</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:Northwind2 %>" 
      SelectCommand=
        "SELECT TOP 10 
        p.[ProductID], p.[ProductName], 
        p.[CategoryID], c.[CategoryName] 
        FROM [Products] AS p 
        LEFT OUTER JOIN [Categories] AS c 
        ON p.[CategoryID] = c.[CategoryID]
        ORDER BY p.[ProductID]" 
      UpdateCommand=
        "UPDATE [Products] 
        SET [ProductName] = @ProductName, 
        [CategoryID] = @CategoryID 
        WHERE [ProductID] = @ProductID">
      <UpdateParameters>
        <asp:Parameter Name="ProductID" Type="Int32" />
        <asp:Parameter Name="ProductName" Type="String" />
        <asp:Parameter Name="CategoryID" Type="Int32" />
      </UpdateParameters>
    </asp:SqlDataSource>

    <asp:SqlDataSource ID="SqlDataSource2" 
      runat="server" 
      ConnectionString="<%$ ConnectionStrings:Northwind2 %>" 
      SelectCommand=
        "SELECT [CategoryID], [CategoryName], [Description] 
        FROM [Categories]">
    </asp:SqlDataSource>
        
    <asp:GridView ID="GridView1" 
      runat="server" 
      AutoGenerateColumns="False" 
      DataKeyNames="ProductID" 
      DataSourceID="SqlDataSource1">
      <Columns>
        <asp:CommandField ShowEditButton="True" />
        <asp:BoundField DataField="ProductID" 
          HeaderText="ID" 
          InsertVisible="False" 
          ReadOnly="True" 
          SortExpression="ProductID" />
        <asp:BoundField DataField="ProductName" 
          HeaderText="Product Name" 
          SortExpression="ProductName" />
        <asp:TemplateField HeaderText="Category" 
          SortExpression="CategoryName">
          <EditItemTemplate>                        
            <asp:DropDownList ID="DropDownList1" 
              runat="server" 
              DataSourceID="SqlDataSource2" 
              DataTextField="CategoryName" 
              DataValueField="CategoryID" 
              SelectedValue='<%# Bind("CategoryID") %>' 
              AppendDataBoundItems="True">
              <asp:ListItem Value="">NULL</asp:ListItem>
            </asp:DropDownList>
          </EditItemTemplate>
         <ItemTemplate>
            <asp:Label ID="Label1" 
              runat="server" 
              Text='<%# Bind("CategoryName") %>'>
            </asp:Label>
          </ItemTemplate>
        </asp:TemplateField>
      </Columns>
    </asp:GridView>
  </div>
  </form>
</body>
</html>

Tags: , ,

ASP.NET

About this blog

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

Calendar

<<  December 2019  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar