SQL Server改元和暦対応ソース無料公開!

SQL Server改元和暦対応ソース無料公開!
Windowsのレジストリにある和暦情報を取得しSQL Serverユーザ定義関数作成方法を公開!

SQL Serverユーザ定義関数・和暦レジストリの使用方法を公開!

2019.5.1改元対応。

T-SQLでハードコーディングされている皆様に、Windows OSレジストリから取得した和暦をSQL Server側で使用できます。

実績10年以上の安定したクラスソースを使用して、SQL Serverユーザ定義関数の設定方法公開!

公開ユーザ定義関数の使用イメージ

SQL Server改元和暦対応!和暦OSレジストリから取得方法公開!-ユーザ定義関数使用結果

使用テーブル(西暦列)

SQL Server改元和暦対応!和暦OSレジストリから取得方法公開!-使用テーブル

ユーザ定義関数ソースを無料公開!

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
using System.Reflection;
using System.Collections.Generic;

public partial class Wareki
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString WLargeFormat(SqlDateTime sqlDate)
    {
        var lReki = cRekis.GetRekiList();
        var iIndex = lReki.Count - 1;

        for (int iLoop = 0; iLoop < lReki.Count; iLoop++) { if(lReki[iLoop].EraDate > sqlDate) {
            iIndex = iLoop - 1;
            break;
            }
        }

        var iYear = sqlDate.Value.Year;
        var sRekiName = "西暦";

        if ( iYear >= lReki[0].EraYear)
        {
            iYear = sqlDate.Value.Year - lReki[iIndex].EraYear + 1;
            sRekiName = lReki[iIndex].EraName;
        }

        var sDate = string.Format("{0}{1}年{2}月{3}日", sRekiName, iYear, sqlDate.Value.Month, sqlDate.Value.Day);
        return new SqlString(sDate);
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString WShortFormat(SqlDateTime sqlDate)
    {
        var lReki = cRekis.GetRekiList();
        var iIndex = lReki.Count - 1;

        for (int iLoop = 0; iLoop < lReki.Count; iLoop++) { if (lReki[iLoop].EraDate > sqlDate)
            {
                iIndex = iLoop - 1;
                break;
            }
        }

        var iYear = sqlDate.Value.Year;
        var sRekiName = "";

        if (iYear >= lReki[0].EraYear)
        {
            iYear = sqlDate.Value.Year - lReki[iIndex].EraYear + 1;
            sRekiName = lReki[iIndex].EraName;
        }

        var sDate = string.Format("{0}{1}/{2}/{3}", sRekiName, iYear, sqlDate.Value.Month, sqlDate.Value.Day);
        return new SqlString(sDate);
    }
}

public class cReki
{
    public string EraName { get; set; }
    public int EraYear { get; set; }
    public DateTime EraDate { get; set; }
    public int EraRange { get; set; }
    public string EraShortName { get; set; }

    public cReki(string eraName, string eraShortName, int eraYear, DateTime eraDate, string eraRange)
    {
        this.EraName = eraName;
        this.EraShortName = eraShortName;
        this.EraYear = eraYear;
        this.EraDate = eraDate;
        if (eraRange != "")
        {
            this.EraRange = int.Parse(eraRange);
        }
        else
        {
            this.EraRange = DateTime.MaxValue.Year - DateTime.Today.Year;
        }
    }
}

public class cRekis
{
    private CultureInfo culture = new CultureInfo("ja-JP", true);

    public cRekis()
    {
        culture.DateTimeFormat.Calendar = new System.Globalization.JapaneseCalendar();
    }

    public static List GetRekiList()
    {
        return (new cRekis()).GetRekis();
    }

    public List GetRekis()
    {

        var lReki = new List();
        var iLength = culture.DateTimeFormat.Calendar.Eras.Length;

        for (int iLoop = 1; iLoop <= iLength; iLoop++)
        {
            lReki.Add(_getRekiClass(iLoop));
        }

        return lReki;
    }

    private cReki _getRekiClass(int iEra)
    {
        var calendar = new System.Globalization.JapaneseCalendar();
        var fgStatus = BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Static;

        var tpFormat = Type.GetType("System.Globalization.DateTimeFormatInfo");
        var piEraName = tpFormat.GetProperty("AbbreviatedEnglishEraNames", fgStatus);
        var shortEraNames = ((string[])(piEraName.GetValue(culture.DateTimeFormat, null)))[iEra - 1];

        var tpCalender = Type.GetType("System.Globalization.JapaneseCalendar");
        var fiHelper = tpCalender.GetField("helper", fgStatus);
        var tpHelper = fiHelper.FieldType;

        var fiEraInfo = tpHelper.GetField("m_EraInfo", fgStatus);
        var alEraInfo = (Array)(fiEraInfo.GetValue(fiHelper.GetValue(calendar)));

        var tpEraInfo = Type.GetType("System.Globalization.EraInfo");
        var fiTicks = tpEraInfo.GetField("ticks", fgStatus);
        var dteMin = new DateTime((long)(fiTicks.GetValue(alEraInfo.GetValue(alEraInfo.Length - iEra))));

        var fiEraYear = tpEraInfo.GetField("maxEraYear", fgStatus);
        var iEraYear = (int)(fiEraYear.GetValue(alEraInfo.GetValue(alEraInfo.Length - iEra)));

        return new cReki(culture.DateTimeFormat.GetEraName(iEra), shortEraNames, dteMin.Date.Year, dteMin.Date, iEraYear.ToString());
    }
}

上記クラスの使用方法

  1. Visual StudioでDLLを作成
  2. SQL Serverにアセンブリ登録
  3. SQL Serverの関数登録

Visual StudioでDLLを作成

プロジェクト設定値【SqlClrWarekiで設定してね】
SQL Server改元和暦対応!和暦OSレジストリから取得方法公開!-プロジェクト設定

SQL Serverにアセンブリ登録

DLLのローカル位置を適時置換えて下さい
※レジストリアクセスしますので、PERMISSON_SET=UNSAFEで。

ALTER DATABASE TEST SET TRUSTWORTHY ON;

CREATE ASSEMBLY SqlClrWareki
FROM 'C:\Temp\SqlClrWareki.dll'
WITH PERMISSION_SET = UNSAFE;

SQL Serverの関数登録

CREATE FUNCTION WLargeFormat(@WarekiDate DATETIME)
RETURNS NVARCHAR(MAX) 
AS EXTERNAL NAME SqlClrWareki.Wareki.WLargeFormat;
CREATE FUNCTION WShortFormat(@WarekiDate DATETIME)
RETURNS NVARCHAR(MAX) 
AS EXTERNAL NAME SqlClrWareki.Wareki.WShortFormat;
SQL Server改元和暦対応!和暦OSレジストリから取得方法公開!-実行イメージ

SQL CLRでの基本情報

Windows OSに保有する和暦レジストリ値を取得し(.NETクラス)、SQL Server側で使用できるようにします。
SQL CLRの基本的な使用方法は下の記事で解説しています。

SQL CLR

.NETで開発したクラスをSQL Serverで使用するにはSQL CLRを使用します。
SQL CLRの使用方法の詳しい説明てはこちら

SQL CLRーVS/SQL Server 2017で使ってみた! この記事は、SQL Serverで.NETで開発した関数が使えるって聞いたけど、どうやってやるの?に答えます。 .NET側での開発...

和暦レジストリアクセスクラスについてはこちら

https://ssit.jp/tooltip/reformer/

注意点

本クラスは.NETカレンダークラスを使用しています。
.NETカレンダークラスはコントロールパネルの設定値を反映します。

仕様変更にご留意ください

和暦レジストリクラスは10年の本場環境にて運用実績があります。

カレンダークラスのプライベートクラスのプロパティへアクセスしております。

今後の仕様変更の可能性もありますので自己責任にてご利用下さい。

ご利用時はツイート拡散してもらえるとうれしいです。