WebSurfer's Home

Filter by APML

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

by WebSurfer 21. August 2010 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

PIVOT の使用

by WebSurfer 7. August 2010 18:18

SQL Server の PIVOT 関係演算子を利用してデータをまとめ、クロス集計レポートを生成し、GridView に表示する例です。

ほとんどの処置はストアドプロシージャで行うので、ASP.NET というよりは SQL Server の機能になりますが。

ここの例では、Microsoft が提供しているサンプルデータベース Northwind の Orders テーブル使用しました。

Orders テーブルは、個々の注文の ID (OrderID, int)、注文した顧客の ID (CustomerID, nchar(5))、注文を取り扱った従業員の ID (EmployeeID, int)、注文を受けた年月日 (OrderDate, datetime) などのデータを約 800 レコード持っています。

DropDownList に顧客名一覧を表示し、その中から顧客を選択すると、その注文を取り扱った従業員と取り扱った注文数を、注文年月ごとに表示するようにしました。以下のような感じです。

PIVOT によるクロス集計レポート

上記のクロス集計を行うためのストアドプロシージャは以下の通りです。

ALTER PROCEDURE dbo.StoredProcedure1
(
@CustomerID nchar(5)
)
AS
DECLARE @sqlstr nvarchar(max), @sqldata nvarchar(max), @employee nvarchar(50); 
DECLARE cur CURSOR FOR 

SELECT DISTINCT EmployeeID 
  FROM Orders 
  WHERE EmployeeID IS NOT NULL AND [CustomerID]=@CustomerID 
  ORDER BY EmployeeID; 

SET @sqldata = N''; 
SET @sqlstr = N'SELECT Year, Month '; 

OPEN cur; 
FETCH NEXT FROM cur INTO @employee; 
WHILE (@@FETCH_STATUS <> - 1) 
  BEGIN 
    IF LEN(@sqldata) > 0 
      BEGIN
        SET @sqldata = @sqldata + N',' 
      END; 
    SET @sqldata = @sqldata + N'[' + @employee + N']'
    SET @sqlstr = @sqlstr + N',[' + @employee + N'] AS [Emp-' + @employee + N']'; 
    FETCH NEXT FROM cur INTO @employee; 
  END; 
CLOSE cur;
DEALLOCATE cur; 

SET @sqlstr = @sqlstr +
  N' FROM 
     (
       SELECT DATEPART(mm, OrderDate) AS Month, DATEPART(yyyy, OrderDate) AS Year, EmployeeID 
       FROM Orders 
       WHERE CustomerID=@ID
     ) AS p 
     PIVOT 
     (
       COUNT(EmployeeID) 
       FOR EmployeeID 
       IN '; 
SET @sqlstr = @sqlstr + N'(' + @sqldata + N')) AS pvt'; 
EXECUTE sp_executesql @sqlstr, N'@ID nchar(5)', @CustomerID

顧客一覧を DropDownList に、クロス集計結果を GridView に表示するための ASP.NET のコードは以下の通りです。

<%@ 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="form2" runat="server">
  <div>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
      ConnectionString="<%$ ConnectionStrings:Northwind %>"             
      SelectCommand="
        SELECT DISTINCT Orders.CustomerID, Customers.CompanyName 
        FROM Orders 
        INNER JOIN Customers 
        ON Orders.CustomerID = Customers.CustomerID">
    </asp:SqlDataSource>

    <asp:DropDownList ID="DropDownList1" 
      runat="server" 
      DataSourceID="SqlDataSource1" 
      DataTextField="CompanyName" 
      DataValueField="CustomerID" 
      AutoPostBack="True">
    </asp:DropDownList>
        
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
      ConnectionString="<%$ ConnectionStrings:Northwind %>" 
      SelectCommand="StoredProcedure1"
      SelectCommandType="StoredProcedure">
      <SelectParameters>
        <asp:ControlParameter 
          ControlID="DropDownList1" 
          Name="CustomerID" 
          PropertyName="SelectedValue" 
          Type="String" />
      </SelectParameters>        
    </asp:SqlDataSource>

    <asp:GridView ID="GridView1" 
      runat="server" 
      DataSourceID="SqlDataSource2">
    </asp:GridView>
  </div>
  </form>
</body>
</html>

Tags: ,

SQL Server

GridView でクリックされたセル位置の取得

by WebSurfer 7. August 2010 15:04

ユーザーがブラウザ上で GridView のセルをクリックすると、ポストバックされて、サーバ側のプログラムでクリックされたセルの位置を取得する方法です。

それだけでは面白くない(?)ので、データソースの列が動的に変化するとし、ヘッダを含めて LinkButton または Button で表示し、ページングを有効にするというシナリオです。

以下の画像のような感じです。これはページャーの 3 をクリックして 3 ページに移動し、ヘッダーを含めて上から 2 行目の 10000 をクリックした結果です。

GridView でクリックされたセルの位置の取得

アプリは以下のようにして作成します。こんなシナリオは普通ないので、役に立たないと言われるかもしれませんが(苦笑)

ページング

ページングのためのコードを自力で書くのはかなり大変です。ページングの機能を持つデータソースコントロールとデータバインドコントロールを組み合わせて使うのが現実的と思います。

ここでは、データソースコントロールに ObjectDataSource を使用し、DataTable を作成するメソッドと GridView(データバインドコントロール)の間を仲介してやることにします。

GridView の列の設定

データソースの列が動的に変化するという前提があります。

テキストで表示するなら、AutoGenerateColumns を true にすれば自動的に必要な列を生成してくれますが、LinkButton または Button を使って表示するという要件があるのでそうはいきません。

AutoGenerateColumns="False" としておき、Page.Load のイベントハンドラで DataTable から列情報を取得し、その情報をもとに ButtonField を初期化して、GridView に設定してやるという操作が必要です。

各行へのデータバインドは、ButtonField.DataTextField プロパティに DataColumn.Caption を設定しておけば、ObjectDataSource と GridView が自動で行ってくれます。

セル位置の設定・取得

クリックされたセルの列・行位置は、GridView.RowCommand イベントハンドラで、引数の GridViewCommandEventArgs オブジェクトの CommandName, CommandArgument プロパティから取得します。

列番号の取得には CommandName を利用します。そのためには、事前に CommandName に列番号を設定しておく必要があります。上記の「GridView の列の設定」の際に DataColumn.Ordinal プロパティから DataTable 内での列位置を取得し、ButtonField.CommandName プロパティに設定します。

行番号の取得には CommandArgument を使用します。CommandArgument にはデフォルトで行番号が設定されるので、事前の設定は不要です。

取得できる行番号は、ページングした場合、例えば1ページ目の n 行と、2ページ目の n 行とで同じになりますので注意してください。

ヘッダー行の変更

ここまでの処置では、まだヘッダーが普通のテキストのままです。これを LinkButton または Button で表示するようにします。

それには、GridView.RowCreated イベントのハンドラで、ヘッダーの Cell の内容を LinkButton または Button に書き換えてやります。

サンプルコード


<%@ 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">
  protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
  {
    if (e.Row.RowType == DataControlRowType.Header)
    {
      TableCellCollection cells = e.Row.Cells;
      foreach (TableCell cell in cells)
      {
        string text = cell.Text;
        cell.Controls.Clear();
        //LinkButton button = new LinkButton();  // ハイパーリンク表示
        Button button = new Button();          // ボタン表示                
        button.Text = text;
        button.CommandArgument = "-1";
        button.CommandName = cells.GetCellIndex(cell).ToString();
        cell.Controls.Add(button);
      }
    }
  }

  protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
  {
    // ページャーがクリックされた場合
    if (e.CommandName == "Page")
    {
      Label1.Text = String.Empty;
    }
    else
    {
      Label1.Text = "Page: " + GridView1.PageIndex.ToString() + 
        ", Command Argument: " + e.CommandArgument.ToString() + 
        ", Command Name: " + e.CommandName;
    }
  }

  protected void Page_Load(object sender, EventArgs e)
  {
    if (!Page.IsPostBack)
    {
      DataView view = (DataView)ObjectDataSource1.Select();
      DataTable table = view.Table;
      foreach (DataColumn column in table.Columns)
      {
        ButtonField field = new ButtonField();
        //field.ButtonType = ButtonType.Link;     // ハイパーリンク表示
        field.ButtonType = ButtonType.Button;   // ボタン表示                
        field.HeaderText = column.Caption;
        field.DataTextField = column.Caption;
        field.CommandName = column.Ordinal.ToString();
        GridView1.Columns.Add(field);
      }
    }
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
    <h3>方法2: ButtonField を利用</h3>
    <asp:ObjectDataSource ID="ObjectDataSource1" 
      runat="server" 
      SelectMethod="CreateDataTable2" 
      TypeName="PagingTest">
    </asp:ObjectDataSource>
    <asp:GridView ID="GridView1" 
      runat="server" 
      AllowPaging="True" 
      DataSourceID="ObjectDataSource1" 
      onrowcreated="GridView1_RowCreated" 
      onrowcommand="GridView1_RowCommand"
      PageSize="5" 
      AutoGenerateColumns="False" 
      EnableViewState="True">
    </asp:GridView>
    <asp:Label ID="Label1" runat="server"></asp:Label>
  </div>
  </form>
</body>
</html>

ついでに、ObjectDataSource の TypeName, SelectMethod が参照しているクラス/メソッドもアップしておきます。


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

public class PagingTest
{
  public static DataTable CreateDataTable2()
  {
    DataTable wktbl = new DataTable();
    DataRow wkrow;
        
    wktbl.Columns.Add("y/x");
    wktbl.Columns.Add("x1.min/.max");
    wktbl.Columns.Add("x2.min/.max");
    wktbl.Columns.Add("x3.min/.max");
    wktbl.Columns.Add("x4.min/.max");

    for (int i = 0; i < 20; i++)
    {
      wkrow = wktbl.NewRow();
      wkrow["y/x"] = "y1.min/.max";
      wkrow["x1.min/.max"] = "10000";
      wkrow["x2.min/.max"] = "20000";
      wkrow["x3.min/.max"] = "30000";
      wkrow["x4.min/.max"] = "40000";
      wktbl.Rows.Add(wkrow);
    }

    return wktbl;
  }
}

Tags: ,

ASP.NET

About this blog

2010年5月にこのブログを立ち上げました。主に ASP.NET Web アプリ関係の記事です。ブログ2はそれ以外の日々の出来事などのトピックスになっています。

Calendar

<<  April 2026  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar