WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

Employees の Photo

by WebSurfer 4. June 2021 14:51

Microsoft のサンプル SQL Server データベース Northwind の Employees テーブルの Photo フィールドの画像データを取得して表示する方法を書きます。

Employees の Photo

アプリから Photo フィールドのバイト列データを取得するのは ADO.NET + SqlClient を使って可能です。問題はそのバイト列を加工して jpg とか png 形式にすることで、それをどうしたらよいかという話です。

Photo フィールドの画像データは Bitmap 形式のバイト列なのですが、ネットの記事 Displaying images in SAPUI5 received from the Northwind OData service によると "The 78 superfluous bytes are a proprietary OLE header that Access creates when saving bitmaps." という余計なものが付いています。(Microsoft のドキュメントもそのような情報があったのですが行方不明)

実際、Photo フィールドのバイト列を取得してそれをそのまま stream に変換し、new Bitmap(stream) で Bitmap オブジェクトを取得しようとすると ArgumentException がスローされます。下のサンプルコードでコメントアウトした部分を見てください。

ということで、Photo フィールドのバイト列を stream に変換するとき最初の 78 バイトをスキップしてやる必要があります。それは MemoryStream(Byte[], Int32, Int32) コンストラクタを使って可能です。

以下にそのサンプルコードを載せておきます。上の画像の Photo を表示するのに使った HTTP ジェネリックハンドラで、Employees テーブルの Photo データを取得して、最初の 78 バイトをスキップして MemoryStream を作り、new Bitmap(stream) で Bitmap オブジェクトを取得し、Bitmap.Save メソッドで jpg 形式にして HTTP 応答ストリームに書き込んでいます。

using System.Web;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;

namespace WebApplication1
{
    public class EmployeePhotoHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            string id = context.Request.QueryString["id"];
            context.Response.ContentType = "image/jpeg";
            byte[] phote;
            int employeeId;
            if (!string.IsNullOrEmpty(id) && int.TryParse(id, out employeeId))
            {
                var connString = WebConfigurationManager
                    .ConnectionStrings["NORTHWINDConnectionString"]
                    .ConnectionString;
                var query = "SELECT photo FROM Employees WHERE EmployeeID = @ID";
                using (var connection = new SqlConnection(connString))
                {
                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@ID", employeeId);
                        connection.Open();
                        phote = (byte[])command.ExecuteScalar();
                    }
                }

                // これはダメ
                // new Bitmap(stream) で ArgumentException がスローされる
                // "使用されたパラメーターが有効ではありません。"
                //using (var stream = new MemoryStream(phote))
                //{
                //    using (var bitmap = new Bitmap(stream))
                //    {
                //        bitmap.Save(context.Response.OutputStream, ImageFormat.Jpeg);
                //    }
                //}

                // これは OK
                // The 78 superfluous bytes are a proprietary OLE header that Access
                // creates when saving bitmaps. ・・・ということで最初の 78 バイトは
                // スキップしないと有効な Bitmap 形式とはみなされないらしい
                using (var stream = new MemoryStream(phote, 78, phote.Length - 78))
                {
                    using (var bitmap = new Bitmap(stream))
                    {
                        bitmap.Save(context.Response.OutputStream, ImageFormat.Jpeg);
                    }
                }
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

Tags: , ,

SQL Server

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

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

JavaScript で Html Escape

by WebSurfer 18. May 2021 11:25

ユーザー入力文字列のサニタイジング(無害化)のためのエスケープ処理を JavaScript で行うためのコードを備忘録として書いておきます。以下のコードは ES6 をサポートしてない IE11 でも動くことは確認しました。

<script type="text/javascript">
    //<![CDATA[

    function htmlEscape(str) {
        if (!str) return;
        return str.replace(/[<>&"'`]/g, function (match) {
            const escape = {
                '<': '&lt;',
                '>': '&gt;',
                '&': '&amp;',
                '"': '&quot;',
                "'": '&#39;',
                '`': '&#x60;'
            };
            return escape[match];
        });
    }

    // 以下は検証用
    var str = '& < > ` " ' + "'";
    console.log(htmlEscape(str));

    // 結果 (IE11 も同様):
    // &amp; &lt; &gt; &#x60; &quot; &#39;

    //]]>
</script>

そんな情報はググればすぐ出てくると言われるかもしれませんが、基本的にネットの記事は自分で検証して確認するまで信用できない疑り深い性格なものですから。(笑)

クロスサイトスクリプティング(XSS)対策の一つに、ユーザー入力文字列のサニタイジング(無害化)がありますが、その目的で上記コードは文字列内の < > & " ' ` の 6 文字を &lt; &gt; &amp; &quot; &#39; &#x60; にエスケープ(変換)しています。

ASP.NET MVC などではフレームワーク組み込みでサニタイジング機能が含まれていますので、Html ヘルパーや Tag ヘルパーを使っている限り上のスクリプトの使い道はないです。

では、どういうときに使うかと言うと、例えば以下のように、Web API などから取得した文字列で html ソースを組み立てる場合でしょうか。

function getCars() {
  $.ajax({
    type: "POST", 
    url: "Api/GetCarsByDoors", 
    data: '{"doors":' + $("#ddlDoors").val() + '}', 
    contentType: "application/json; charset=utf-8",
 
    success: function (cars) {      
      $('#output').empty();
      $.each(cars, function (index, car) {
        $('#output').append(
          '<p><strong>' + car.Make + ' ' +
          car.Model + '</strong><br />Description: ' +
          htmlEscape(car.Description) + '</p>');
        });
      }
  });
}

Tags: , ,

JavaScript

About this blog

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

Calendar

<<  September 2021  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar