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

GridView, ListView で特定の行をハイライト

by WebSurfer 2016年10月13日 23:35

GridView, ListView でデータを一覧表示する際、それにデータバインドされたデータソースに含まれる各行の特定の列のデータの値を調べ、その値に応じて当該行をハイライトする方法について書きます。

元は MSDN フォーラムのスレッド「ListViewでの一覧表示で、取得したデータに応じて背景色を変えたい」での話です。

そのスレッドで自分が回答したことなのですが、忘れかけていたので自分のブログに備忘録として書いておくことにしました。また、ついでに GridView での実装例も書いておきます。

このブログの別の記事「GridView, ListView に合計表示」のコードをベースにしていますので、そちらも見てください。

その記事では、Microsoft が提供するサンプルデータベース Northwind の Orders というテーブルのレコードのうち、[CustomerID] が ALFKI という顧客の注文の [OrderID], [OrderDate], [Freight] フィールド一覧を表示しています。

Orders テーブルには出荷日を示す [ShippedDate] というフィールドが含まれています。ここでは、[ShippedDate] を調べて、ある日付より後に出荷されたレコードの行の背景色をハイライトしてみます。なお、ListView / GridView には [ShippedDate] フィールドは表示しません。

ListView での例

以下の画像は 1998/1/1 より後に出荷された注文のレコードをハイライトしたものです。

ListView

紹介した MSDN フォーラムのスレッドで書いたとおりですが、上のように表示するための具体的な方法を以下に書きます。

  1. SqlDataSource の SELECT クエリに [ShippedDate] を追加します。
  2. ListView の ItemTemplate 内の tr 要素をサーバーコントロールに変更して id を付与します。具体的には runat="server" id="datarow" という 2 つの属性を追加します(id 名は任意)。
  3. 例えば、1998/1/1 より後に出荷されたレコードを黄色でハイライトする場合、[ShippedDate] が 1998/1/1 より後のアイテムの中から FindControl メソッドで上記 2 でサーバーコントロールにした tr 要素を探し、それに style 属性を追加します。コードは以下のようになります。
protected void ListView1_ItemDataBound(object sender, 
        ListViewItemEventArgs e)
{
  if (e.Item.ItemType == ListViewItemType.DataItem)
  {
    ListViewDataItem lvdi = (ListViewDataItem)e.Item;
    DataRowView drv = (DataRowView)lvdi.DataItem;
    total = total + (decimal)drv["Freight"];

    // 以下が追加した部分
    DateTime date = new DateTime(1998, 1, 1, 0, 0, 0);
    if ((DateTime)drv["ShippedDate"] > date)
    {
      HtmlControl tr = (HtmlControl)lvdi.FindControl("datarow");
      if (tr != null)
      {
        tr.Attributes.Add("style", "background-color:yellow;");
      }
    }
  }
}

GridView での例

上の ListView の場合と同様に、1998/1/1 より後に出荷された注文のレコードをハイライトしたものです。

GridView

上のように表示するための具体的な方法は以下の通りです。

  1. SqlDataSource の SELECT クエリに [ShippedDate] を追加します。(ListView の場合と同様)
  2. ListView の場合とは異なり、GridView のコードには手を加える必要はありません。GridView の RowDataBound イベントで GridViewRow を取得し、それの CssClass プロパティにスタイルを設定できますので。
  3. 上の ListView の場合と同様に、1998/1/1 より後に出荷されたレコードを黄色でハイライトする場合、[ShippedDate] を調べて 1998/1/1 より後であれば、当該行の GridViewRow オブジェクトを取得し、その CssClass プロパティを設定します。コードは以下のようになります。
protected void GridView1_RowDataBound(object sender, 
        GridViewRowEventArgs e)
{        
  if (e.Row.RowType == DataControlRowType.DataRow)
  {
    DataRowView drv = (DataRowView)e.Row.DataItem;
    total = total + (decimal)drv["Freight"];

    // 以下が追加した部分
    DateTime date = new DateTime(1998, 1, 1, 0, 0, 0);
    if ((DateTime)drv["ShippedDate"] > date)
    {
      e.Row.CssClass = "style1";
    }
    // ここまで
  }
  else if (e.Row.RowType == DataControlRowType.Footer)
  {
    e.Row.Cells[1].Text = "Freight Total";
    e.Row.Cells[2].Text = String.Format("${0:N2}", total);

    e.Row.Cells[1].ColumnSpan = 2;
    e.Row.Cells.RemoveAt(0);            
  }        
}

e.Row.CssClass = "style1"; のところの style1 は別途定義してソースに追加してください。インラインで <title> 要素の下に書くなら以下のようにします。

<style type="text/css">
    .style1
    {
        background-color: yellow;
    }
</style>

Tags: , ,

ASP.NET

GridView に overflow 適用

by WebSurfer 2015年11月10日 14:04

GridView の列に長い文字列を入れると、下の画像の「制限しない場合」のように文字列の長さに応じて列の幅が広がっていってしまいます。

それを「overflow:hidden で制限」のように、CSS の overflow を使って決まった範囲内に制限する方法について書きます。(テキストが領域をこえた場合に省略記号「…」を表示することもできます。詳しくはこの記事の下の方の 2015/11/12 追記を見てください)

GridView に overflow 適用

ASP.NET が GridView を html コードにレンダリングすると table, tr, th, td 要素になります。BoundField を使うと文字列は td 要素の中に直接配置されます。

td 要素はブロックレベル要素ではないのでそれに直接 overflow は適用できません。なので、td 要素の中に div 要素を 配置しそれに overflow 他のスタイルを適用した上で div 要素内に文字列を入れるのがよさそうです。

クライアント側だけで JavaScript を使っても可能ですが、ASP.NET のコードで対応した方が簡単そうです。

具体的には、当該列を TemplateField に変換し、それに div 要素を配置して overflow 他のスタイルを適用、div 要素の中に Literal コントロールを配置してその Text プロパティをデータバインドします。

そのコード例は以下の通りで、これを実行すると上の画像の結果になります。

<%@ 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 Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack)
    {
      // データソースとして DataTable を作成。
      DataTable dt = new DataTable();
      DataRow dr;

      dt.Columns.Add(new DataColumn("id", typeof(Int32)));
      dt.Columns.Add(
          new DataColumn("description", typeof(string)));

      dr = dt.NewRow();
      dr["id"] = 1;
      dr["description"] = "グリッドビューはデータソースの"+ 
                          "値を表に表示します。";
      dt.Rows.Add(dr);

      dr = dt.NewRow();
      dr["id"] = 2;
      dr["description"] = "The GridView displays the " + 
                      "values of a data source in a table.";
      dt.Rows.Add(dr);

      // 上で作成した DataTable を GridView にバインド。
      GridView1.DataSource = dt;
      GridView1.DataBind();

      GridView2.DataSource = dt;
      GridView2.DataBind();
    }
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title>GirdView に overflow 適用</title>
  <style type="text/css">
    div.style1
    {
      width: 150px;
      height: 1.1em;
      overflow: hidden;
    }
  </style>
</head>
<body>
  <form id="form1" runat="server">
  <h2>制限しない場合</h2>
  <asp:GridView ID="GridView1" runat="server">
  </asp:GridView>

  <h2>overflow:hidden で制限</h2>
  <asp:GridView ID="GridView2" 
    runat="server" 
    AutoGenerateColumns="False">
    <Columns>
      <asp:BoundField DataField="id" HeaderText="id" />
      <asp:TemplateField HeaderText="description">
        <ItemTemplate>
          <div class="style1">
            <asp:Literal ID="Literal1" 
              runat="server" 
              Text='<%# Eval("description") %>'>
            </asp:Literal>
          </div>
        </ItemTemplate>
      </asp:TemplateField>
    </Columns>
  </asp:GridView>
  </form>
</body>
</html>

------ 2015/11/12 追記 ------

テキストが領域をこえた場合に「…」を表示する text-overflow:ellipsis はもともと IE の独自拡張だそうですが、最近は他のブラウザでも取り入れられているようです。下の画像は、自分の環境 Vista SP2 32-bit で、Opera 12.17 で試した結果です。

text-overflow:ellipsis 適用

IE9, Chrome 46.0.2490.86 m, Firefox 42.0, Safari 5.1.7 でも同様に text-overflow:ellipsis は有効でした。(古いバージョン、その他のブラウザは未確認です) 上の画像の例ではスタイルを以下のように変更しています。
<style type="text/css">
    div.style1
    {
        width: 150px;
        overflow: hidden;
        white-space: nowrap;
        text-overflow: ellipsis;
    }
</style>

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