WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

SQL Server の数値型を LIKE 句を使ってあいまい検索

by WebSurfer 19. June 2021 12:55

SQL Server の数値型は、文字列型と同様に、そのまま直接 LIKE 句を使ってあいまい検索ができるようです。下の画像を見てください。UnitPrice 列は money 型ですが、[UnitPrice] LIKE '%2%' という条件が有効になっています。

SSMS での検索結果

知ってました? 実は何を隠そう自分は最近まで知らなかったです。(汗) 数字型はまず文字列型に変換してから、それに LIKE 句を使うものだと思ってました。

調べてみると、Microsoft のドキュメント「LIKE (Transact-SQL)」に、

"引数が文字列データ型でない場合、SQL Server データベース エンジン は可能であれば引数を文字列データ型に変換します。 If any one of the arguments isn't of character string data type, the SQL Server Database Engine converts it to character string data type, if it's possible."

・・・と書いてあります。実際に試してみると、上の画像のように money 型の UnitPrice 列も LIKE 句を使ってあいまい検索ができました。

Microsoft のドキュメントが言う「可能であれば」がどこまでの範囲か調べ切れていませんが、自分が SQL Server 2012 で試した限りでは int 型と money 型は可能な範囲に入るようです。

ADO.NET + SqlClient を使った .NET Framework のアプリケーションでも同じことができます。パラメータ化も可能です。ただし、パラメータ化する場合は、パラメータは文字列型として扱う必要がありますが。

上の画像と同様な LIKE 句を使って検索を行う .NET Framework コンソールアプリのサンプルコードを以下に載せておきます。ADO.NET + SqlClient を使い、SQL 文はパラメータ化しています。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleAppLIKE
{
    public class Product
    {
        public int ProductID { get; set; }

        public string ProductName { set; get; }

        // UnitPrice 列は NULL 可なので Nullable とした
        public decimal? UnitPrice { set; get; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string connString = "接続文字列";
            string selectQuery =
                "SELECT [ProductID] ,[ProductName] ,[UnitPrice] FROM [Products] " +
                "WHERE [ProductName] LIKE N'%' + @ProductName + N'%' AND " +
                "[UnitPrice] LIKE N'%' + @UnitPrice + N'%'";

            string productName = "ch";
            string unitPrice = "2";            
            List<Product> productList = new List<Product>();

            using (var connection = new SqlConnection(connString))
            {
                using (var command = new SqlCommand(selectQuery, connection))
                {
                    var p1 = new SqlParameter("@ProductName", SqlDbType.NVarChar);
                    p1.Value = productName;

                    // UnitPrice 列は money 型だが LIKE 句を使ってあいまい検索
                    // する場合はパラメータの型は文字列とする
                    var p2 = new SqlParameter("@UnitPrice", SqlDbType.NVarChar);
                    p2.Value = unitPrice;

                    command.Parameters.Add(p1);
                    command.Parameters.Add(p2);

                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var product = new Product
                            {
                                ProductID = reader.GetInt32(0),
                                ProductName = reader.GetString(1),

                                // UnitPrice 列は NULL 可なのでその対応
                                UnitPrice = reader.IsDBNull(2) ?
                                            null : (decimal?)reader.GetDecimal(2)
                            };

                            productList.Add(product);
                        }
                    }
                }
            }

            foreach (Product p in productList)
            {
                Console.WriteLine($"PriductID: {p.ProductID}, " +
                    $"ProductName: {p.ProductName}, UnitPrice: {p.UnitPrice}");
            }
        }
    }
}

サンプルコード中のコメントにも書きましたが、UnitPrice 列は money 型ですが LIKE 句を使ってあいまい検索する場合はパラメータの型は文字列とする必要がありますので注意してください (例えば、SqlDbType.NVarChar を SqlDbType.Deciaml にするとエラーになります)。

上のコードの実行結果は以下の通りで、上の画像の SSMS での実行結果と同じになっています。

ADO.NET での検索結果

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

try - catch を書かないでロールバック

by WebSurfer 29. July 2020 13:45

ADO.NET + SqlClient を使ってトランザクション処理を行う場合、明示的に Rollback ���ソッドを書かなくてもロールバックできるという話を書きます。

ロールバックを行うコードで自分がよく目にするのは、try - catch 文を使って try 句の中に処理をまとめ、catch 句に Rollback メソッドを書いて、処理の途中で例外が発生したらロールバックできるようにするというものです。

具体例は Microsoft のドキュメント「SqlTransaction.Rollback メソッド」にありますので見てください。

そのドキュメントにある catch 句で Rollback するというコードは、一見して例外発生でロールバックされるということが分かって良いと思うのですが、Exception を catch してそのままにしてしまっている所が気になります。

そこは、catch するのを SqlException のみにするとか Rollback した後で再 throw することで対応できるのですが、意地でも try - catch 文は書きたくないという場合もあるかもしれません。ないかもしれませんが。(笑)

Microsoft の Blog に書いてあったことですが(今はリンク切れで読めません)、未コミットのトランザクションは SqlConnection を Dispose(Close と同じ)する際ロールバックされるそうです。

ということは、catch 句に Rollback メソッドを書くというようなことはしなくても、try - finally または using 句を使って確実に SqlConnection が Dispose / Close されるようにしておけば例外発生でロールバックされるはずです。

実際「Microsoft Visual Studio 2005 による Web アプリケーション構築技法」という本にも Rollback メソッドを使わないコード例は紹介されていました。

具体例を以下の画像の SQL Server のテーブルと ADO.NET + SqlClient のコードを使って説明します。

SQL Server のテーブル

テーブルは「エラー発生時のトランザクションのロールバック - SET XACT_ABORT ON と TRY...CATCH」という記事から借用しました。フィールド StudentID には FK 制約が設定してあり 1 ~ 6 または NULL 以外を入力しようとするとエラーになります。

コード例は下の方にアップします。using 句を使って確実に SqlConnection が Dispose されるようにしています。Rollback メソッドは書いていません。例外が発生するとアプリは終了しますが、その前に using 句により SqlConnection が Dispose され、Commit メソッドは実行されませんのでロールバックされるはずです。

コード例では、上に紹介した記事にならって、3 つの UPDATE 文を実行しようとしています。最後の UPDATE 文で StudentID を 7 に設定し(1 ~ 6 または NULL 以外は FK 制約違反)そこで FK 制約違反となるようにして試してみました。エラーメッセージは以下の通りです。

ハンドルされていない例外: System.Data.SqlClient.SqlException: UPDATE ステートメントは FOREIGN KEY 制約 "FK__TestResul__Stude__22AA2996" と競合しています。競合が発生したのは、データベース "TestDatabase"、テーブル "dbo.Student", column 'StudentID' です。

static void Main(string[] args)
{
    string connString = @"接続文字列";
    string query = "UPDATE [TestDatabase].[dbo].[TestResult] " +
                   "SET [StudentID]=@StudentID " +
                   "WHERE [TestResultID]=@TestResultID";

    using (var connection = new SqlConnection(connString))
    {
        using (var command = new SqlCommand(query, connection))
        {
            command.Parameters.Add(
                new SqlParameter("@StudentID", SqlDbType.Int));
            command.Parameters.Add(
                new SqlParameter("@TestResultID", SqlDbType.Int));
            connection.Open();
            var sqltx = connection.BeginTransaction();
            command.Transaction = sqltx;

            command.Parameters["@StudentID"].Value = 5;
            command.Parameters["@TestResultID"].Value = 1;
            command.ExecuteNonQuery();

            command.Parameters["@StudentID"].Value = 6;
            command.Parameters["@TestResultID"].Value = 2;
            command.ExecuteNonQuery();

            // FK 制約違反
            command.Parameters["@StudentID"].Value = 7;
            command.Parameters["@TestResultID"].Value = 3;
            command.ExecuteNonQuery();

            sqltx.Commit();
        }
    }
}

結果、期待通りロールバックされて TestResult テーブルには 3 つの UPDATE 文のいずれも反映されないことを確認しました。

上に紹介した記事の Transact-SQL を使った場合の例では、SET XACT_ABORT ON を設定しない場合、TRY...CATCH を使って CATCH で明示的に ROLLBACK する必要があるそうです。しかし、ADO.NET + SqlClient では上のコード例のように SqlConnection を Dispose / Close すればロールバックされるようです。

Tags: , , ,

ADO.NET

About this blog

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

Calendar

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

View posts in large calendar