redwarrior’s diary

C#, ASP.NET, WEB, G*などの雑多な情報

SSRSのデータセットにRedmineのチケット情報を読み込む

SQL Server レポート出力 Redmine

前の記事で、データソースにRedmine Rest Api を指定したので、これを使用して、データセットにチケット情報を読み込む方法を調査しました。Visual Studio上でプレビューして動作を確認しています。

検証環境

データセットの読み込み

実は、データソースに指定したURLが、チケット番号を指定して情報を取得するAPIのURLなので、データセットではデータソースを選択するだけで、いくらかの情報は取得できます。

しかし、余分な情報はデータセットの時点で削っておきたい！XMLデータの場合は、SQLの代わりに要素パス構文というのを使用します。

docs.microsoft.com

詳しい内容は、リンク先を読んでもらうとして、今回は「カスタムフィールドの一覧を取得する」データセットと「チケットのステータスを取得する」データセットの二つを作成しました。

カスタムフィールドの一覧を取得する

要素パス構文で記述したクエリ

issue {}/custom_fields {}/custom_field {@id,@name}/value

実行結果（カスタムフィールド数分表示される）

id name value
1 カスタムフィールド名A カスタムフィールド値１
2 カスタムフィールド名B カスタムフィールド値２
チケットのステータスを取得する

要素パス構文で記述したクエリ

issue {id,subject,created_on,updated_on}/status {@name}

実行結果

id subject created_on updated_on name
100 チケット名１ チケット作成日時 チケット更新日時 ステータス値

データの絞り込み

チケットのステータスを取得するクエリは、1レコードしか返ってこないけれど、カスタムフィールドの一覧を取得するクエリは複数レコード返ってきます。１つのカスタムフィールドの値を取得したい場合もあるでしょう。

要素パス構文には、条件による絞り込みの機能は無さそうなので、この場合、データセットのフィルターを使用します。データセットのプロパティからフィルターを開いて「id = 数値」のように指定して絞り込む事が出来ます。

以上です。

.NET CoreのコンソールアプリケーションでEntity Framework Coreを使用しSQL Serverに接続する

EF Core SQL Server .NET Core

Visual Studio 2019 も出てきて、これからは.NET Coreでの開発が進んで行くと思うのでキャッチアップのために.NET Coreプロジェクトを使い始めています。

.NET CoreプロジェクトでDBを扱うとなったら、当然Entity Framework Coreを使います。公式サイトはそれなりに充実しています。

docs.microsoft.com

しかし、コンソールアプリケーションでEntity Framework Coreを使用して新しいデータベースを作成する方法が、SQLiteを使った例になっているため、.NET CoreのコンソールアプリケーションでEntity Framework Coreを使用し、SQL Server LocalDBに接続する方法を調べました。

色々と参照しながら試したので、参考サイトはまとめていません。

パッケージのインストール

まずは、NuGetパッケージのインストールです。Entity Framework CoreでSQL Serverを使用するには、Microsoft.EntityFrameworkCore.SqlServerをインストールします。SQL Server LocalDBもこのパッケージで対応しています。

必要なクラスの作成

次に、DbContextクラスを継承したクラス（コンテキストクラス）と、EntityとなるPOCOクラスを作成します。これは公式の例と一緒です。
コンテキストクラスでOnConfiguringメソッドをオーバーライドして、パラメータとして受け取ったDbContextOptionsBuilderクラスのUseSqlServerメソッドを呼び出し、パラメータとしてDBへの接続文字列を渡します。

SQL Server LocalDBへの接続文字列

Entity Frameworkとは接続文字列の書式が異なっていて、Server, Database, Trusted_Connectionを設定するようです。以下は、SQL Server LocalDBへの接続文字列の例です。

Server=(localdb)\mssqllocaldb;Database=(データベース名);Trusted_Connection=True

Entity Frameworkで使えたAttachDbFilenameは、Entity Framework Coreでも使えるようです。

Server=(localdb)\mssqllocaldb;Database=(データベース名);Trusted_Connection=True;AttachDbFilename=(mdfファイルを作成するパス)
データベースの新規作成

今回はマイグレーションを使用して、データベースを作成します。公式の例はコマンドプロンプト？で作業をしていますが、ここではVisual Studioを使います。
Visual Studioを使うと言っても、GUIなメニューはないため、パッケージマネージャーコンソールを開いてコマンドを実行します。

Microsoft.EntityFrameworkCore.ToolsをNuGetでインストールします。これによってパッケージマネージャーコンソールで必要なコマンドが追加されます。

以下のコマンドを実行すると、マイグレーション用のクラスが作成されます。

Add-Migration InitialCreate

以下のコマンドを実行すると、データベースが作成されます。

PM> Update-Database
データベースに接続する

ここまで来れば、Entity Frameworkと同じように使うことが出来ます。公式の例そのままで動きます。

以上です。

SQL Server Reporting Services（SSRS）のデータソースで外部サービスを設定する

SQL Server レポート出力 Redmine

うかうかしていたら、前回の投稿から3ヶ月近くも空いてしまいました。気を取り直して記述します。

最近、SQL Server Reporting Services（SSRS）を触っていて、帳票出力には結構便利だという事がわかって来たので、それについて書きます。

データソースに外部サービスを設定する

SSRSは、SQL Serverに接続してデータを取得し、表示するのが基本的な使い方だと思います。このデータの取得元をデータソースと呼びます。

データソースには、外部のWebサービスを指定することが出来ます。データソースのプロパティ画面を開いて、[全般]メニューの型でXMLを選択すれば良いです。

参考ページ：Reporting Services でサポートされるデータ ソース (SSRS) - SQL Server Reporting Services (SSRS) | Microsoft Docs

注意点としては、返り値がXMLである事*1、リクエストがGETである事、Expressエディションでは使えない事です。

参考ページ：レポートサーバーの設定 - クリエイティブWeb *2

今回は、Redmine Rest Apiを外部サービスとして設定してみます。

データソースで式を使用する

データソースの取得の時点で、パラメータによって絞り込んだデータがほしい場合があると思います。その場合はデータソースで式を使用します。

式の例（Redmineのチケットと注記データの取得。issueIdはSSRSのパラメータ）

="http://(RedmineのURL)/issues/" & Parameters!issueId.Value & ".xml?key=(APIキー)&include=journals"

注意点としては、データソースで式を使用し、データセットのクエリ（クエリデザイナー）を開くと、以下のエラーが発生して実行が出来ない事です。

TITLE: Microsoft SQL Server Report Designer

データ ソースへの接続中にエラーが発生しました。テキスト ベースのクエリ デザイナーのみを使用できます。

ADDITIONAL INFORMATION:

接続文字列の式はデザイン時に評価できません。レポートをプレビューするか配置して、接続を検証する必要があります。 (Microsoft.ReportingServices.Designer.Controls)

レポートウィンドウのプレビューは出来ますが、やはり面倒なので開発中はデータソースは固定値にしておいた方が良さそうです。

以上です。

*1:Jsonは無理そうです

*2:「2. Express 版 SSRS の制限」を参照。SQL Server 2008 R2の話ですが、2017でもダメでした

（小ネタ）WindowsフォームのDataBindingに関するリンク集

.NET Framework WinForms

Windowsフォームでデータバインディングを行う場合に、BindingSource、IDataErrorInfo、ErrorProvider、null値の制御についての有用なリンク集。

サイト

WindowsFormsでの単項データバインドまわり | tocsworld

IDataErrorInfo Interface (System.ComponentModel) | Microsoft Docs

双方向データバインドによる単体入力エラーチェック – とあるコンサルタントのつぶやき

書籍

ひと目でわかる Visual C# 2010 アプリケーション開発入門　日経BP社 (2010/11/8)

BindingSourceを使用したTextBoxのデータ入力時におけるメリット

WinForms .NET Framework

以前にWindowsフォームで作成したアプリケーションを修正する機会があり、BindingSourceについて理解が深まったので、記録を残しておく。

「TextBoxに入力した値をバリデーションして、正しい値ならば紐づいたDBのレコードを更新する」というよくある処理を実装する場合、BindingSourceを使用してBindingを行うと以下のようなケースで便利だった。

バリデーションでエラーになったら、変更前の値を復元したい

バリデーション処理は、Validatingイベントを使用すると思うが、パラメータから取得できる値、TextBoxフィールドの値も変更後の値になっている。
このままでは変更前の値を取得できないため、変更前の値を格納するフィールドを用意すると思うが、実はBindingSourceを使っていると変更前のフィールドがいらなくなる。

それは、BindingSourceから名前付きDataRowを取得でき、それが入力前の値を持っているからだ。名前付きDataRowの取得、入力前の値の取得は、以下のように行う。

var dataRowView = bindingSource.Current as DataRowView;
var row = dataRowView?.Row as 名前付きDataRowクラス名;
if (row != null)
{
    originalText = row[nameof(row.プロパティ名), DataRowVersion.Original].ToString();
}
//originalTextを使用して変更前の値を代入する処理など

レコードを更新する時に、値が変更されている場合のみ更新したい

バリデーション後の更新では、Validatedイベントを使用すると思うが、値が変更されていないのに毎回DBに接続して更新するのもムダなので、値が変わった場合のみ更新をしたい。
DBから取得した値と比較する方法があるが、対象が１つの画面からしか更新されない場合、取得のために毎回DBに接続すると、DB接続回数が増えてしまう。
上記の変更前の値を使用して、入力値と比較する事もできるが、実はBindingSourceを使っているともっと簡単な方法がある。

それは、BindingSourceから取得できる名前付きDataRowが、変更情報を持っているからだ。変更されたかどうかの判定は以下のように行う。

//rowは、名前付きDataRowクラス名のインスタンス。取得方法は上記と一緒。
if (row.RowState == DataRowState.Modified)
{
  //データが変更された場合に行う処理（DB更新処理など）
}

以上。

NPOIでExcelのセルの値を取得する（関数も対応）

.NET Framework NPOI

NPOIを使用してExcelを読み込む処理を作成したので、後から見返せるように気づいたことをメモしておく。

確認したバージョン：.NET Framework 4.6、NPOI 2.3.0

Excelのセルの値を取得する場合は、以下のようにまずICellインスタンスを取得する。

//sheetは、ISheetインターフェイスのインスタンス
ICell cell = sheet.GetRow(行番号).GetCell(列番号);

数値や文字列を取得する

ICellインターフェイスで宣言されているプロパティから値を取得することが出来る。セルが空の場合も対応している。

ICellインターフェイス

やりたい事 プロパティ セルが空の場合の返り値
数値を取得する NumericCellValue double型の0
文字列を取得する StringCellValue string型の""(空文字)

関数の結果を取得する

NumericCellValueプロパティや、StringCellValueプロパティは、セルの値が関数の場合、関数を評価した結果を返してくれる。

ICellインターフェイス

やりたい事 プロパティ 関数の場合の返り値
数値を取得する NumericCellValue 関数を評価した結果の数値
文字列を取得する StringCellValue 関数を評価した結果の文字列

関数を評価した結果を取得するのに、IFormulaEvaluatorインターフェイスを使用する方法もある。
Evaluateメソッドに対象セルのICellインスタンスを渡して実行し、CellValueインスタンスを取得する

//workbookは、IWorkbookインターフェイスのインスタンス
IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
//cellは、ICellインターフェイスのインスタンス（値を取得したいセル）
CellValue cellValue = evaluator.Evaluate(cell);

CellValueインスタンスに関数の評価結果が入るので、プロパティから値を取得することが出来る。
セルの値が空の場合は、CellValueインスタンスにnullが入るため、プロパティを参照すると例外が発生する。

CellValueクラス

やりたい事 プロパティ セルが空の場合の返り値
数値を取得する NumberValue NullReferenceExceptionが発生
文字列を取得する StringValue NullReferenceExceptionが発生

CellValueクラスでは、関数の評価結果のデータ型とプロパティが異なっていても例外にはならない。

CellValueクラス

関数を評価した結果 プロパティ 返り値
数値 NumberValue 数値
数値 StringValue null
文字列 NumberValue double型の0
文字列 StringValue 文字列

関数を評価した結果のデータ型が、条件によって変わるセルの値を取得する

ICellインターフェイスのプロパティでは、関数の評価結果とプロパティの型が異なると例外が発生するため使えない。

ICellインターフェイス

関数を評価した結果 プロパティ 返り値
数値 StringCellValue InvalidOperationExceptionが発生
文字列 NumericCellValue InvalidOperationExceptionが発生

関数を評価した結果の型が条件によって変わる場合は、CellValueクラスを使用する。
CellValueクラスのCellTypeプロパティにはデータ型が格納されているため、データ型で分岐して処理が出来る。

//cellValueは、CellValueクラスのインスタンス
var value = string.Empty;
if (cellValue.CellType == CellType.String) {
    value = cellValue.StringValue;
}

以上。

jQueryのfloatTheadプラグインを使用したテーブルを印刷する方法

jQuery

少し前ですが、レコード数の多いテーブルを表示した時に、スクロールでExcelみたいに先頭行を固定して表示したい時に、jQueryのfloatTheadプラグインを使用しました。*1

さらにfloatTheadプラグインを使用したテーブルを印刷したいという要件が出た時に、そのままでは印刷レイアウトが崩れてしまったので、対処方法を調べました。

作者のGitHubのissueページに載っていました。

Support for printing tables · Issue #68 · mkoryak/floatThead · GitHub

内容を大まかにまとめると、 メディアクエリを使用してブラウザ表示か印刷かを判別し、 印刷の場合はtable要素のfloatThead('destroy')を呼び出し、 画面表示に戻ったら再度table要素のfloatThead()を呼び出す。 という感じです。

以上

*1:https://github.com/mkoryak/floatThead