WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

数字と他の文字混在の列のソート

by WebSurfer 22. May 2021 19:44

以下のように数字とその他の文字が混在している列がある SQL Server のテーブルを ASP.NET Web Forms アプリの GridView に表示し、数字だけの行とその他の行を分けてソートする方法を書きます。

SQL Server のテーブル

元の話は Teratail のスレッド「gridviewで数値と文字列が混在している際に数値の大小順に並べ替えたい」のものです。それは Access を使った場合ですが、SQL Server で同様なことを行います。

昇順降順のソートだけでなく、GridView + SqlDataSource に組み込まれている選択、ページング、データの編集・更新機能も働くようにするのが条件です。

例えば、上の画像のテーブルの Name フィールドを、数字だけの行とその他の行を分けて昇順にソートするには、ISNUMERIC (Transact-SQL)CAST (Transact-SQL) を ORDER BY 句に使って以下のようにすることで可能です。

ORDER BY によるソート

まず、ORDER BY ISNUMERIC([Name]) DESC で数字だけの行が先に来るように並び替え(数字の場合は 1、それ以外は 0 になるので DESC を付与しています)、その結果を CAST WHEN ... END ASC で数字の場合は FLOAT に変換しそれ以外は 0 にしてから昇順 (ASC) に並べ替え、さらにその結果を [Name] ASC で普通に文字列として昇順 (ASC) に並べ替えを行っています。

(上に紹介した ISNUMERIC のドキュメントの説明に "有効な数値データ型は次のとおりです・・・" とあって、その中に文字列型はありません。しかし、上の画像の通り、自分の開発環境にある SQL Server 2012 で試した限りは nvarchar でも判定してくれました。公式ドキュメントに nvarchar でもよいと書いてないのは不安要素ではありますが)

GridView + SqlDataSource を使ってテーブルのレコード一覧を表示する場合、単純に文字列としてソーティングを行うなら GridView の AllowPaging プロパティを true に設定するだけで、GridView + SqlDataSource に組み込まれた機能を使って一行もコードを書かずにソート機能を実装可能です。

ちなみに、GridView + SqlDataSource に備わっているデフォルトのソート機能というのは以下のようになっています。

  1. GridView の AllowPaging プロパティを true に設定するとヘッダがリンクボタンになる。
  2. リンクボタンのクリックによってポストバックが起こる。
  3. サーバー側ではそれを受けて SQL Server から SqlDataSource 経由でデータを取得。(その時、SqlDataSource の SelectCommand プロパティに設定した SELECT クエリがそのまま SQL Server に発行されます。ORDER BY 句を付与して発行されるわけではありません。リンクボタンをクリックするたび毎回ポストバックが発生し、その都度 SQL Server にクエリが発行される点にも注意してください)
  4. 取得したデータから DataView を作って、その Sort プロパティにクリックされたリンクボタンのある列の SortExpression を設定。(SortExpression はデフォルトではその列の列名すなわち DataView の列名と同じになっています)
  5. それによりソートされた結果を GirdView に表示する。
  6. 同じリンクボタンを再度クリックすると、上記 2 ~ 4 と同じプロセスが実行されるが、昇順降順が切り替わる。(上記 4 で ASC, DESC を切り替えて付与しているようです)

なので、例えば、上の 2 つ目の画像の ORDER BY 句つき SELECT クエリを SqlDataSource の SelectCommand プロパティに設定すると、初期画面ではレコード一覧は 2 つ目の画像のとおり表示されますが、ヘッダのリンボタンをクリックすると上の 4 の結果、すなわち単純に文字列としてソーティングした結果が表示されます。

それでは要件を満足できず、全ての列のヘッダのリンクボタンのクリックで上の 2 つ目の画像のように数字だけの行とその他の行を分けてソートし、さらにクリックするたびに昇順降順を切り替えたり、ページングや編集操作を行ってもソーティング結果に破綻が無いようにするにはかなり手を加える必要があります。

そのあたりを考えてサンプルコードを書いてみました。そのコードを下にアップしておきます。ポイントは、コード内のコメントにも書きましたのでそれを見てください。(手抜きでスミマセン)

.aspx.cs

using System;
using System.Web.UI.WebControls;

namespace WebApplication1
{
    public partial class GridViewSorting : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // ポストバック前の SELECT クエリを ViewState に保持しておき、
            // Load イベントで SqlDataSource.SelectCommand に設定する。
            // これをしないとページングなどでポストバックしたときソート
            // されなくなってしまう
            string selectQuery = (string)ViewState["CurrectSelectQuery"];
            if (!string.IsNullOrEmpty(selectQuery))
            {
                SqlDataSource1.SelectCommand = selectQuery;
            }
        }

        // GridView で AllowPaging="True" とし、CommandName="Sort" と設定
        // されているヘッダの LinkButton をクリックすると Sorting イベント
        // が発生する。そのハンドラで SqlDataSource.SelectCommand の書き換
        // えを行う。        
        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            GridView gv = (GridView)sender;
            string exp = e.SortExpression;
            string dir = "";

            // ISNUMERIC(exp) は exp が数字の場合 1 に、それ以外は 0 になる。
            // 昇順で数字の行が先に来るようにするには ORDER BY ISNUMERIC(exp)
            // に DESC を、降順の場合は ASC を付与するために使う
            string dirRev = "";

            // GridView に独自属性 CurrentSortField, CurrentSortDir が
            // 追加してある。それにポストバック前のソート対象フィールドと昇順
            // 降順の情報を保持しておく。それと、e.SortExpression の情報を元
            // に SELECT クエリの ORDER BY 句を組み立てる
            if (gv.Attributes["CurrentSortField"] != null &&
                gv.Attributes["CurrentSortDir"] != null)
            {
                // ユーザーが同じヘッダの LinkButton を繰り返しクリックした
                // 場合は昇順降順を切り替える
                if (exp == gv.Attributes["CurrentSortField"])
                {
                    if (gv.Attributes["CurrentSortDir"] == "ASC")
                    {
                        dir = "DESC";
                        dirRev = "ASC"; 
                        gv.Attributes["CurrentSortDir"] = "DESC";
                    }
                    else
                    {
                        dir = "ASC";
                        dirRev = "DESC";
                        gv.Attributes["CurrentSortDir"] = "ASC";
                    }
                }
                // 初期画面から LinkButton クリック、または前と違う LinkButton
                // をクリックした場合
                else
                {
                    gv.Attributes["CurrentSortField"] = exp;
                    dir = "ASC";
                    dirRev = "DESC";
                    gv.Attributes["CurrentSortDir"] = "ASC";
                }
            }

            // SELECT クエリの ORDER BY 句の組み立て、
            // SqlDataSource.SelectCommandプロパティ への設定
            SqlDataSource1.SelectCommand = "SELECT [ID], [Name], [Note] FROM [TestTable] " +
                $"ORDER BY ISNUMERIC([{exp}]) {dirRev}, " +
                $"CASE WHEN ISNUMERIC([{exp}]) = 1 THEN CAST([{exp}] AS FLOAT) ELSE 0 END {dir}, " +
                $"[{exp}] {dir}";

            // SELECT クエリを ViewState に保持
            ViewState["CurrectSelectQuery"] = SqlDataSource1.SelectCommand;

            // 組み込みのソーティングをキャンセルする
            e.Cancel = true;

            // 編集モードに入ったままページングすると編集する行がズレてしまうので
            // ここで編集モードを解除
            gv.EditIndex = -1;
        }

        // 上の Sorting イベントのハンドラでソーティングをキャンセルすると
        // SortedAscendingHeaderStyle, SortedDescendingHeaderStyle
        // プロパティを使ってヘッダに ↑ とか ↓ の画像などの CSS を設定し
        // ても適用されない。なので、以下のようなコードを書いてソート対象
        // にしたヘッダに動的に css を適用する
        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {
            string field = GridView1.Attributes["CurrentSortField"];

            if (e.Row.RowType == DataControlRowType.Header &&
                !string.IsNullOrEmpty(field))
            {
                LinkButton lb = e.Row.FindControl(field) as LinkButton;

                if (lb != null)
                {
                    if (GridView1.Attributes["CurrentSortDir"] == "ASC")
                    {
                        lb.CssClass = "sortasc-header";
                    }
                    else if (GridView1.Attributes["CurrentSortDir"] == "DESC")
                    {
                        lb.CssClass = "sortdesc-header";
                    }
                }
            }
        }
    }
}

.aspx

<%@ Page Language="C#" AutoEventWireup="true" 
    CodeBehind="GridViewSorting.aspx.cs" 
    Inherits="WebApplication1.GridViewSorting" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title>GridView Sorting</title>
    <style type="text/css">
        .sortasc-header {
            background: url(img/sort-asc.png) right center no-repeat;
        }

        .sortdesc-header {
            background: url(img/sort-desc.png) right center no-repeat;
        }

        th a
        {
            padding-right: 20px;
        } 
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TestDatabaseConnectionString %>" 
            DeleteCommand="DELETE FROM [TestTable] WHERE [ID] = @ID" 
            InsertCommand="INSERT INTO [TestTable] ([Name], [Note]) VALUES (@Name, @Note)" 
            SelectCommand="SELECT [ID], [Name], [Note] FROM [TestTable]" 
            UpdateCommand="UPDATE [TestTable] SET [Name] = @Name, [Note] = @Note WHERE [ID] = @ID">
            <DeleteParameters>
                <asp:Parameter Name="ID" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="Note" Type="String" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="Note" Type="String" />
                <asp:Parameter Name="ID" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>

        <%--GridView に独自属性 CurrentSortField, CurrentSortDir 
            を追加。それにも ViewState は有効で、ポストバック前のソート
            対象フィールドと昇順降順の情報を保持できる--%>
        <asp:GridView ID="GridView1" runat="server" 
            AllowPaging="True" 
            AllowSorting="True" 
            AutoGenerateColumns="False" 
            DataKeyNames="ID" 
            DataSourceID="SqlDataSource1" 
            CurrentSortField=""
            CurrentSortDir=""
            OnRowCreated="GridView1_RowCreated" 
            OnSorting="GridView1_Sorting">
            <Columns>
                <asp:CommandField ShowDeleteButton="True" 
                    ShowEditButton="True" ShowSelectButton="True" />
                <asp:TemplateField HeaderText="ID" 
                    InsertVisible="False" SortExpression="ID">
                    <EditItemTemplate>
                        <asp:Label ID="Label1" runat="server" 
                            Text='<%# Eval("ID") %>'></asp:Label>
                    </EditItemTemplate>
                    <HeaderTemplate>
                        <asp:LinkButton ID="ID" runat="server" 
                            CommandName="Sort" CommandArgument="ID">
                            ID
                        </asp:LinkButton>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" 
                            Text='<%# Bind("ID") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Name" SortExpression="Name">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" 
                            Text='<%# Bind("Name") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <HeaderTemplate>
                        <asp:LinkButton ID="Name" runat="server" 
                            CommandName="Sort" CommandArgument="Name">
                            Name
                        </asp:LinkButton>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" 
                            Text='<%# Bind("Name") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Note" SortExpression="Note">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" 
                            Text='<%# Bind("Note") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <HeaderTemplate>
                        <asp:LinkButton ID="Note" runat="server" 
                            CommandName="Sort" CommandArgument="Note">
                            Note
                        </asp:LinkButton>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" 
                            Text='<%# Bind("Note") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </form>
</body>
</html>

上のコードを実行し、ヘッダの Name をクリックすると以下のようになります。

ソート結果

Tags: , , , ,

ASP.NET

ListView でソート(行の並び替え)

by WebSurfer 26. May 2012 15:35

ListView で、ヘッダのリンクボタンをクリックして、その列に設定した SortExpression に従って行の並べ替えを行う例です。

ListView でソート(列の並べ替え)を行う例

GridView の場合は、ウィザードを使ってコードは一行も書かないで簡単にできますが、ListView では少しですが自力でコードを書かなければなりません。

GridView の場合は AllowSorting プロパティを true に設定すれば、ヘッダが自動的に LinkButton になって、それをクリックすれば、その列に設定した SortExpression に従って列の並べ替えが行われます。

ところが、ListView には AllowSorting プロパティという便利なものはありません。ウィザードで作っていくと、ヘッダは普通のテキストになりますので、それを自力で LinkButton に置き換えて、Text、CommandName、CommandArgument の各プロパティを設定してやる必要があります。

Text にはヘッダに表示するテキスト、CommandName は Sort、CommandArgument は SortExpression を設定します。以下のコード例を参考にしてください。それだけで、GridView と同様な列の並べ替えが可能になります。

昇順/降順を示すイメージをヘッダのテキストに追加して表示する場合は、MSDN ライブラリの ListView.Sorting イベント のページのコードが参考になると思います。

以下、余談かもしれませんが、気がついたポイントを書いておきます。

SortExpression は複数の列を指定することもできます。例えば、A, B, C 列があるとすると、C 列の SortExpression を A, B, C のようにすることも可能です。LinkButton のクリックを繰り返すと、昇順/降順が切り替わりますが、ASC/DESC は SortExpression の末尾に追加されます(A, B, C DESC のように)。

SQL Server に投げられるクエリに ORDER BY xxxxx が追加されることはありません(プロファイラで見ると分かりますが、ヘッダの LinkButton をクリックすると、SqlDataSource の SelectCommand に設定されたクエリが毎回そのまま投げられます)。ListView と SqlDataSource の中でどのような操作が行われているかは見えないので定かではないですが、たぶん、取得したデータから DataView を作って、その Sort プロパティ に SortExpression を設定し、ソートした結果を ListView に表示しているようです。(これは GridView の場合も同じです)

SqlDataSource.DataSourceMode は DataSet でなければなりません。DataReader では、ソートを行うために LinkButton をクリックすると、System.NotSupportedException がスローされます。

以下にコード例をアップしておきます。上の画像の ListView を表示したコードです。

<%@ 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">
  // ListView でソートするサンプル

  protected void Button1_Click(object sender, EventArgs e)
  {
    // 初期状態に戻すには SortExpression を "" にして
    // ListView.Sort メソッドを実行する。(GridView で
    // も同様)
    ListView1.Sort("", SortDirection.Ascending);
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
  <style type="text/css">
    table.style1
    {
      border-style: solid;
      border-width: 2px;
      border-color: Black;
      text-align: center;
      border-collapse: collapse;
    }
       
    table.style1 th
    {
      border-style: solid;
      border-width: 2px 1px 2px 1px;
      border-color: Black;
      padding: 0px 5px 0px 5px;
    }
        
    table.style1 td
    {
      border-style: solid;
      border-width: 1px;
      border-color: Black;
      padding: 0px 5px 0px 5px;
    }
  </style>

</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:Button ID="Button1" 
      runat="server" 
      Text="初期状態に戻す" 
      OnClick="Button1_Click" />

    <asp:SqlDataSource ID="SqlDataSource1" 
      runat="server" 
      ConnectionString="<%$ ConnectionStrings:Northwind2 %>" 
      SelectCommand=
        "SELECT SupplierID, CategoryID, UnitPrice, ProductName 
        FROM Products" 
      DataSourceMode="DataSet">
    </asp:SqlDataSource>

    <asp:ListView ID="ListView1" 
      runat="server" 
      DataSourceID="SqlDataSource1">
      <ItemTemplate>
        <tr>
          <td>
            <asp:Label ID="SupplierIDLabel" runat="server" 
              Text='<%# Eval("SupplierID") %>' />
          </td>
          <td>
            <asp:Label ID="CategoryIDLabel" runat="server" 
              Text='<%# Eval("CategoryID") %>' />
          </td>
          <td>
            <asp:Label ID="UnitPriceLabel" runat="server" 
              Text='<%# Eval("UnitPrice") %>' />
          </td>
          <td>
            <asp:Label ID="ProductNameLabel" runat="server" 
              Text='<%# Eval("ProductName") %>' />
          </td>
        </tr>
      </ItemTemplate>
      <LayoutTemplate>
        <table>
          <tr>
            <td>
              <table ID="itemPlaceholderContainer" 
                runat="server" 
                class="style1">
                <tr>
            <%--ソーティングのためヘッダーのテキストを LinkButton 
               に置き換える。CommandName を Sort に設定し、
              CommandArgument を SortExpression に設定。--%>
                  <th>
                    <asp:LinkButton ID="LinkButton1" 
                      runat="server" 
                      Text="SupplierID" 
                      CommandName="Sort" 
                      CommandArgument="SupplierID" />
                  </th>
                  <th>
                    <asp:LinkButton ID="LinkButton2" 
                      runat="server" 
                      Text="CategoryID" 
                      CommandName="Sort" 
                      CommandArgument="SupplierID,CategoryID" />
                  </th>
                  <th>
                    <asp:LinkButton ID="LinkButton3" 
                      runat="server" 
                      Text="UnitPrice" 
                      CommandName="Sort" 
                      CommandArgument=
                        "SupplierID,CategoryID,UnitPrice" />
                  </th>
                  <th>
                    <asp:LinkButton ID="LinkButton4" 
                      runat="server" 
                      Text="ProductName" 
                      CommandName="Sort" 
                      CommandArgument="ProductName" />
                  </th>
                </tr>
                <tr runat="server" ID="itemPlaceholder">
                </tr>
              </table>
            </td>
          </tr>
          <tr>
            <td>
            <%--以下はオマケのちょっと凝ったページャー。--%>
              <asp:DataPager ID="DataPager1" runat="server">
                <Fields>
                  <asp:TemplatePagerField>              
                    <PagerTemplate>
                      Page
                      <asp:Label runat="server" 
                        ID="CurrentPageLabel" 
                        Text="<%# Container.TotalRowCount>0 ? 
                          (Container.StartRowIndex / Container.PageSize) + 1 : 0 %>" />
                      of
                      <asp:Label runat="server" 
                        ID="TotalPagesLabel" 
                        Text="<%# Math.Ceiling (
                          (double)Container.TotalRowCount / Container.PageSize) %>" />
                      (
                      <asp:Label runat="server" 
                        ID="TotalItemsLabel" 
                        Text="<%# Container.TotalRowCount%>" />
                      records)
                      <br />
                    </PagerTemplate>
                  </asp:TemplatePagerField>
                                    
                  <asp:NextPreviousPagerField
                    ButtonType="Button"
                    ShowFirstPageButton="true"
                    ShowNextPageButton="false"
                    ShowPreviousPageButton="false" />
                  <asp:NumericPagerField 
                    PreviousPageText="< Prev 3"
                    NextPageText="Next 3 >"
                    ButtonCount="3" />
                  <asp:NextPreviousPagerField
                    ButtonType="Button"
                    ShowLastPageButton="true"
                    ShowNextPageButton="false"
                    ShowPreviousPageButton="false" />
                </Fields>
              </asp:DataPager>
            </td>
          </tr>
        </table>
      </LayoutTemplate>
    </asp:ListView>
  </div>
  </form>
</body>
</html>

Tags: ,

ASP.NET

About this blog

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

Calendar

<<  October 2021  >>
MoTuWeThFrSaSu
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567

View posts in large calendar