WebSurfer's Home

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

異なるデータソースの結合と表示

by WebSurfer 2017年11月26日 13:34

異なるデータソース(例えば SQL Server と CSV ファイル)のレコードを内部結合または左外部結合して GridView などに一覧表示する例を書きます。

GridView に表示

上の画像はこの記事で紹介するサンプルの実行結果で、SQL Server と CSV ファイルをデータソースに使い、左側が内部結合、右側が左外部結合した結果一覧を ASP.NET Web Forms アプリの GridView に表示したものです。

この記事で使用したデータソースは、Microsoft が提供しているサンプルデータベース Northwind の Orders テーブルと、Customers テーブルから一部のフィールド / レコードを抜き出して作った以下の画像の CSV ファイルです。

CSV ファイル内容

データソースが両方とも SQL Server のサンプルデータベース Northwind にあれば、SELECT クエリで JOIN 句を使って結合し、その結果を DataTable などに取得するのが簡単ですが、一方が CSV ファイルではそうはいきません。

ではどうするかと言うと、SQL Server のテーブルと CSV ファイルそれぞれから List<T> 型のオブジェクトを作り、それを Linq で結合した結果を GridView のデータソースとしてバインドしてやるのがよさそうです。

Linq を使って結合する例は Microsoft の文書「join 句 (C# リファレンス)」やそれからリンクが張ってある記事が参考になりました。

上の画像を表示したサンプルコードは以下の通りです。説明はコメントとして書きましたので、それを見てください。(手抜きでスミマセン)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

// SQL Server の Northwind サンプルデータベース Orders
// テーブルのレコードを格納するクラス定義
public class Order
{
    public int OrderID { get; set; }
    public string CustomerID { get; set; }
    public int& EmployeeID { get; set; }
    public DateTime& OrderDate { get; set; }
    public DateTime& RequiredDate { get; set; }
    public DateTime& ShippedDate { get; set; }
    public int& ShipVia { get; set; }
    public decimal& Freight { get; set; }
    public string ShipName { get; set; }
    public string ShipAddress { get; set; }
    public string ShipCity { get; set; }
    public string ShipRegion { get; set; }
    public string ShipPostalCode { get; set; }
    public string ShipCountry { get; set; }
}

// CSV ファイルのレコードを格納するためのクラス定義
public class Customer
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
}

// 結合後の結果を格納するためのクラス定義
public class Result
{
    public int OrderID { get; set; }
    public string CompanyName { get; set; }
    public DateTime& OrderDate { get; set; }
    public decimal& Freight { get; set; }
}

public partial class _0019_GridViewJoinedList : 
    System.Web.UI.Page
{
  // SQL Server のサンプルデータベース Northwind の
  // Orders テーブルからデータを取得して List<Order>
  // オブジェクトを生成。Entity Framework を使う方が簡単
  // だが、ここではプリミティブに ADO.NET の SqlDataReader 
  // を使用した。
  protected List<Order> CreateOrderList()
  {
    List<Order> orders = new List<Order>();

    string connString = WebConfigurationManager.
            ConnectionStrings["NORTHWINDConnectionString"].
            ConnectionString;

    string query = "SELECT [OrderID], [CustomerID]," +
            "[EmployeeID], [OrderDate], [RequiredDate]," +
            "[ShippedDate], [ShipVia], [Freight]," +
            "[ShipName], [ShipAddress], [ShipCity]," +
            "[ShipRegion], [ShipPostalCode], [shipCountry]" +
            "FROM [Orders]";

    using (SqlConnection conn = new SqlConnection(connString))
    {
      conn.Open();
      using (SqlCommand cmd = new SqlCommand(query, conn))
      {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
          if (reader != null)
          {
            while (reader.Read())
            {
              Order record = new Order();

              record.OrderID = reader.GetInt32(0);
              record.CustomerID = reader.IsDBNull(1) ?
                      null : reader.GetString(1);
              record.EmployeeID = reader.IsDBNull(2) ?
                      null : (int&)reader.GetInt32(2);
              record.OrderDate = reader.IsDBNull(3) ?
                      null : (DateTime&)reader.GetDateTime(3);
              record.RequiredDate = reader.IsDBNull(4) ?
                      null : (DateTime&)reader.GetDateTime(4);
              record.ShippedDate = reader.IsDBNull(5) ?
                      null : (DateTime&)reader.GetDateTime(5);
              record.ShipVia = reader.IsDBNull(6) ?
                      null : (int&)reader.GetInt32(6);
              record.Freight = reader.IsDBNull(7) ?
                      null : (decimal&)reader.GetDecimal(7);
              record.ShipName = reader.IsDBNull(8) ?
                      null : reader.GetString(8);
              record.ShipAddress = reader.IsDBNull(9) ?
                      null : reader.GetString(9);
              record.ShipCity = reader.IsDBNull(10) ?
                      null : reader.GetString(10);
              record.ShipRegion = reader.IsDBNull(11) ?
                      null : reader.GetString(11);
              record.ShipPostalCode = reader.IsDBNull(12) ?
                      null : reader.GetString(12);
              record.ShipCountry = reader.IsDBNull(13) ?
                      null : reader.GetString(13);

              orders.Add(record);
            }
          }
        }
      }
    }
    return orders;
  }

  // CSV ファイルからデータを取得して List<Customer> オブ
  // ジェクトを生成。
  protected List<Customer> CreateCustomerList()
  {
    List<Customer> customers = new List<Customer>();

    string csvFile = Server.MapPath("~/App_Data/TextFile.csv");

    using (Microsoft.VisualBasic.FileIO.TextFieldParser tfp =
      new Microsoft.VisualBasic.FileIO.TextFieldParser(
        csvFile,
        System.Text.Encoding.GetEncoding("Shift_JIS")))
    {
      //フィールドがデリミタで区切られている
      tfp.TextFieldType =
        Microsoft.VisualBasic.FileIO.FieldType.Delimited;
      // デリミタを , とする
      tfp.Delimiters = new string[] { "," };
      // フィールドを " で囲み、改行文字、デリミタを
      // 含めることができるか
      tfp.HasFieldsEnclosedInQuotes = true;
      // フィールドの前後からスペースを削除
      tfp.TrimWhiteSpace = true;

      while (!tfp.EndOfData)
      {
        string[] fields = tfp.ReadFields();

        Customer customer = new Customer()
        {
          CustomerID = fields[0],
          CompanyName = fields[1],
          ContactName = fields[2],
          ContactTitle = fields[3]
        };
        customers.Add(customer);
      }
    }
    return customers;
  }

  protected void Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack)
    {
      List<Order> orders = CreateOrderList();
      List<Customer> customers = CreateCustomerList();

      // 内部結合
      var innerJoin = from o in orders
                      join c in customers
                      on o.CustomerID equals c.CustomerID
                      select new Result
                      {
                        OrderID = o.OrderID,
                        CompanyName = c.CompanyName,
                        OrderDate = o.OrderDate,
                        Freight = o.Freight
                      };

      // シーケンスが空の場合に返すデフォルト値
      // 下の DefaultIfEmpty メソッドの引数に設定する
      Customer defaultValue = new Customer() {
                CustomerID = string.Empty,
                CompanyName = string.Empty,
                ContactName = string.Empty,
                ContactTitle = string.Empty };

      // 左外部結合
      var leftOuterJoin = 
          from o in orders
          join c in customers
          on o.CustomerID equals c.CustomerID into cGroup
          from item in cGroup.DefaultIfEmpty(defaultValue)
          select new Result
          {
              OrderID = o.OrderID,
              CompanyName = item.CompanyName,
              OrderDate = o.OrderDate,
              Freight = o.Freight
          };

      // 上の画像の左側の GridView(内部結合)
      GridView1.DataSource = innerJoin;
      GridView1.DataBind();

      // 上の画像の右側の GridView(左外部結合)
      GridView2.DataSource = leftOuterJoin;
      GridView2.DataBind();
    }
  }
}

Tags: , ,

ASP.NET

IE11 と Referer

by WebSurfer 2017年8月13日 17:02

ASP.NET 4 の Web Forms アプリで、クエリ文字列に日本語を含めて要求をかけた場合、Windows 10 の IE11 と Edge ではポストバックの際 Referer が送信されないという話を書きます。(ASP.NET 4.5 では問題は解消されています。ASP.NET 3.5 以下は未確認です)

Fiddler によるキャプチャ

そもそもの原因は Windows 10 の IE11 や Edge ではなく、ASP.NET 4(3.5 以前も?・・・未確認です)が応答の from 要素に設定する action 属性にあるようです。

例えば、IE11 のアドレスバーに直接以下の URL(クエリ文字列は "日本語" を UTF-8 のパーセントエンコードしたものです)を入力して直接ページを GET 要求したとします。

http://aspnet4site/test.aspx?P1=%e6%97%a5%e6%9c%ac%e8%aa%9e

そうすると、ASP.NET により応答の form 要素の action 属性に設定される URL は以下のようになります。

action="./test.aspx?P1=%u65e5%u672c%u8a9e"

つまり、要求 URL のクエリ文字列の "日本語" は UTF-8 のパーセントエンコーディングなのに、応答の form 要素の action 属性に設定されるのは Unicode のパーセントエンコーディングになってしまいます。そこに問題があるようです。

具体的には下にアップした簡単なサンプルのページ test.aspx で再現できます。

上の画像は、UTF-8 でパーセントエンコーディングした "日本語" をクエリ文字列に含めた URL を IE11 のアドレスバーに直接入力して test.aspx を要求し、表示されたページで 2 回ポストバックを行ったときの Fiddler によるキャプチャ画像です。

上の画像の #1 が初回の要求・応答で、#3 が 1 回目のポストバック、#4 が 2 回目のポストバックの際の要求・応答です。

問題は 2 回目のポストバックのときで、ブラウザからリファラが送信されません。Edge も同様で 2 回目のポストバックの際はリファラは送信されません。

どのような動きになるかと言うと・・・

#1 の初回要求に対して ASP.NET が返す応答の form 要素の action 属性に同じページの URL が設定されますが、URL に含まれるクエリ文字列の "日本語" は Unicode のパーセントエンコーディング %u65e5%u672c%u8a9e となってしまいます。

なので、1 回目のポストバックの時は要求 URL に含まれるクエリ文字列は %u65e5%u672c%u8a9e となります。上の画像の #3 を見てください。

ただし、1 回目のポストバックの際にブラウザから送信されるリファラは、最初の要求の URL(すなわちアドレスバーに直接入力した URL)になります。つまり、クエリ文字列の "日本語は" UTF-8 のパーセントエンコーディングなので問題なくリファラとして送信されます。問題は 2 回目以降です。

2 回目のポストバックの時(上の画像の #4)は、1 回目のポストバックの際の要求 URL(すなわちクエリ文字列の "日本語" が %u65e5%u672c%u8a9e)をリファラとして送信するはずですが、Windows 10 の IE11 と Edge では送信されません。3 回目、4 回目も同様です。

Microsoft の公式文書が見つけられないので想像ですが、Windows 10 の IE11 と Edge ではセキュリティ対策が強化され、URL に UTF-8 として解釈できないコードがあるとリファラを送信しないということではないかと思っています。

ちなみに、Windows 7 の IE11 では %u65e5%u672c%u8a9e となってしまっていてもリファラは送信されるそうです(聞いた話で未検証・未確認)。Chrome 60.0.3112.90, Firefox 54.0.1 では %u65e5%u672c%u8a9e でも送信されることは確認しました。

やはり、URL にはクエリ文字列を含めて ASCII 文字のみ使用することで徹底するのがよさそうです。

サンプルページ test.aspx

<%@ 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 Page_Load(object sender, EventArgs e)
    {
        if (Request.UrlReferrer != null)
        {
            Label1.Text = Request.UrlReferrer.ToString();
        }
        else
        {
            Label1.Text = "無し";
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <h1>Page B</h1>
    <asp:Button ID="Button1" runat="server" Text="Button" />
    <br />
    UrlReferrer: 
    <asp:Label ID="Label1" runat="server"></asp:Label>
    </form>
</body>
</html>

Tags: , ,

ASP.NET

プロファイル情報を ClaimsIdentity へ追加

by WebSurfer 2017年4月16日 14:13

ASP.NET Identity 2.0 でプロファイル情報を Claim として ClaimsIdentity オブジェクトに追加し、拡張メソッドを使って ClaimsIdentity オブジェクトからプロファイル情報を取得・表示する方法を書きます。

プロファイル情報の表示

プロファイル情報とは、ユーザーのメールアドレス、電話番号などのユーザー固有の情報です。ASP.NET プロファイル機能は、フォーム認証のためのクレデンシャル情報(ユーザー名とパスワード)と共に、プロファイル情報を個々のユーザーに関連付けてデータベースに格納します。

ASP.NET Identity 2.0 では、プロファイル情報として Email, PhoneNumber が IdentityUser クラスに定義済みです。それらに加えて、CodeZine の記事 ASP.NET Identityのプロファイル情報のカスタマイズにあるように、姓、名、誕生日などの任意の情報を追加することができます。

プロファイル情報の標準的な設定方法や取得方法は CodeZine の記事を読んでいただければわかるのでここでは書きません。(手抜きでスミマセン。上にリンクを張った CodeZine の記事は ASP.NET Identity 1.0 のもので、2.0 のものとは AspNetUsers テーブルの内容などが異なりますが、基本は同じです)

ここでは、CodeZine の記事のようにその都度データベースから情報を取得するのではなく、User.Identity プロパティから取得できる ClaimsIdentity オブジェクトにプロファイル情報を含めておき、それから取得する方法を書きます。

そのような方法を取る理由は、例えば上の画像のようにマスターページの右上に常にユーザー情報を表示するような場合、ページを描画するたびにデータベースにクエリを発行してプロファイル情報を取得するのは負荷が重そうに感じたからです。

ClaimsIdentity オブジェクトにプロファイル情報を含めれば、ユーザー認証後は認証クッキーに含まれたプロファイル情報がクライアントから送信されてきて、それをベースに ClaimsIdentity オブジェクトを再生成するのだと思います。(それが書いてある Microsoft の公式文書が見つからないので想像の域を出ませんが、実際にいろいろ試した結果からその想像は合っていると思います)

であれば、再生成された ClaimsIdentity オブジェクトからプロファイル情報を取得する方が、データベースから取得するより、負荷は軽そうです。(実は気にするほどの差はないのかもしれませんが)

以下に、例として、PhoneNumber という定義済みのプロファイル情報を Claim として ClaimsIdentity へ追加するコード、ClaimsIdentity からプロファイル情報を取得するための拡張メソッドのコードを載せておきます。

ベースは ASP.NET Web Forms の Web アプリケーションプロジェクトを Visual Studoi 2015 Community のテンプレートを使って自動生成した IdentityModel.cs です。それに Claim を追加するコードと拡張メソッドを追加しています。(Web サイトプロジェクトでは、自動生成される IdentityModel.cs がかなり異なり、同じようにできるかどうかは未確認です。)

using System;
using System.Security.Claims;
using System.Threading.Tasks;
using System.Web;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using Microsoft.AspNet.Identity.Owin;
using Microsoft.Owin.Security;
using WebFormsApp.Models;

// 拡張メソッドで FirstOrDefault を使うため追加
using System.Linq;

namespace WebFormsApp.Models
{
  public class ApplicationUser : IdentityUser
  {
    public ClaimsIdentity GenerateUserIdentity(
            ApplicationUserManager manager)
    {
      var userIdentity = manager.CreateIdentity(
        this, DefaultAuthenticationTypes.ApplicationCookie);

      // ここでは例として PhoneNumber を Claim として追加。
      // 未登録(DB 上で NULL)の場合 this.PhoneNumber プロ
      // パティは null を返す。null の場合は追加しても意味
      // がないので追加しない。 (null のまま追加しようとす
      // ると Claim コンストラクタで例外がスローされる)
      if (!string.IsNullOrEmpty(this.PhoneNumber))
      {
        // ClaimTypes クラスは System.Security.Claims 名前
        // 空間に定義済みなのでそれを利用。PhoneNumber プロ
        // パティは IdentityUser クラスに定義済み。
        userIdentity.AddClaim(
          new Claim(ClaimTypes.HomePhone, this.PhoneNumber));
      }

      return userIdentity;
    }
    // ・・・中略・・・
  }

  // ClaimsIdentity から PhoneNumber を取得する拡張メソッド
  // PhoneNumber が Claims にない場合は null を返す。
  public static class MyExtensions
  {
    public static string GetPhoneNumber(
        this System.Security.Principal.IIdentity identity)
    {
      var claimsIdentity = identity as ClaimsIdentity;
      if (claimsIdentity != null)
      {
        var claim = claimsIdentity.Claims.
          FirstOrDefault(c => c.Type == ClaimTypes.HomePhone);
        if (claim != null)
        {
          return claim.Value;
        }
      }
      return null;
    }
  }
  // ・・・中略・・・
}

MVC5 アプリでは、テンプレートで自動生成される IdentityModel.cs のコードが上の Web Forms アプリのものとは少々異なりますが、自力で書いて追加する部分のコードは上記と全く同じになります。

上記の拡張メソッドは名前空間をインポートすればスコープの中に取り込むことができます。例えば、上の画像のようにマスターページの右上に表示する場合は以下のようにします。

<%@ Import Namespace="WebFormsApp.Models" %>

<a runat="server" href="~/Account/Manage" 
  title="Manage your account">
  Hello, <%: Context.User.Identity.GetUserName()  %> !
  Phone: <%: Context.User.Identity.GetPhoneNumber() %>
</a>

Tags: ,

ASP.NET

About this blog

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

Calendar

<<  2017年12月  >>
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar