以下のように数字とその他の文字が混在している列がある SQL Server のテーブルを ASP.NET Web Forms アプリの GridView に表示し、数字だけの行とその他の行を分けてソートする方法を書きます。
元の話は Teratail のスレッド「gridviewで数値と文字列が混在している際に数値の大小順に並べ替えたい」のものです。それは Access を使った場合ですが、SQL Server で同様なことを行います。
昇順降順のソートだけでなく、GridView + SqlDataSource に組み込まれている選択、ページング、データの編集・更新機能も働くようにするのが条件です。
例えば、上の画像のテーブルの Name フィールドを、数字だけの行とその他の行を分けて昇順にソートするには、ISNUMERIC (Transact-SQL) と CAST (Transact-SQL) を 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 に備わっているデフォルトのソート機能というのは以下のようになっています。
-
GridView の AllowPaging プロパティを true に設定するとヘッダがリンクボタンになる。
-
リンクボタンのクリックによってポストバックが起こる。
-
サーバー側ではそれを受けて SQL Server から SqlDataSource 経由でデータを取得。（その時、SqlDataSource の SelectCommand プロパティに設定した SELECT クエリがそのまま SQL Server に発行されます。ORDER BY 句を付与して発行されるわけではありません。リンクボタンをクリックするたび毎回ポストバックが発生し、その都度 SQL Server にクエリが発行される点にも注意してください）
-
取得したデータから DataView を作って、その Sort プロパティにクリックされたリンクボタンのある列の SortExpression を設定。（SortExpression はデフォルトではその列の列名すなわち DataView の列名と同じになっています）
-
それによりソートされた結果を GirdView に表示する。
-
同じリンクボタンを再度クリックすると、上記 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 をクリックすると以下のようになります。
