redwarrior’s diary

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

SQL ServerのCHECK制約の設定方法

SQL Server EF6 Database

簡単な入力制限であれば、テーブルにCHECK制約をつける手もあるかなと思い、SQL ServerのCHECK制約の設定方法について調査しました。

SSDT（サーバーエクスプローラー）
  • SSDTは、テーブル定義画面でCHECK 制約を追加できる。
  • CHECK制約の対象カラムは、カラムを選択した時に、CHECK制約がハイライトされる。
  • CHECK制約は、CREATE TABLEのCONSTRAINT句で記述する。
    SQLにALTER TABLEを書いても実行できたが、テーブル定義画面を閉じて開くとCREATE TABLE文に戻ってしまった。
SSDT（オブジェクトエクスプローラー）
  • テーブル定義画面を開く前に、制約だけを開くと、ALTER TABLE形式のSQLが表示される。
SSMS
  • デザイン画面では、CHECK制約は表示されない。
  • 制約を開くと、制約編集用の画面が表示される。
  • 「テーブルをスクリプト化」で、ALTER TABLE形式のSQLが表示される。
  • 制約に対して「制約をスクリプト化」をしてもALTER TABLE形式のSQLが表示される。
Code First(Entity Framework 6.2)
  • CHECK 制約を記述出来ないため、DBを再作成するとCHECK 制約は消える。
  • CHECK 制約を設定したい場合は、DBInitilizerクラスで、DB再作成後にALTER TABLE形式のSQLを実行する必要がある。

以上

（まとめ）Redmineチケットの帳票出力にSSRSを使用する

Redmine SQL Server レポート出力

これまで書いてきた記事を読めば、Redmineチケットの帳票出力にSQL Server Report Services（SSRS）を使用する事が出来ます。

出来る事ならRedmineのワークフローを使用して、Web上（Redmine上）で完結する事が望ましいのでしょうが、そうも行かない事もあると思います。

そんな時に、Redmineを使用するのをあきらめてしまうのではなく、紙と併用して使っても良いのではないかと思いました。

RedmineのPDF作成機能や、ブラウザによる印刷では、レイアウトの調整は出来ないので（view customize pluginで印刷用にゴリゴリ画面を書き換えたくは無いですよね？）、何か良い方法はないかと思いたどり着きました。

後から参照しやすいように、各記事へのリンクをまとめます。

  1. SQL Server Reporting Services（SSRS）のデータソースで外部サービスを設定する - redwarrior’s diary

  2. SSRSのデータセットにRedmineのチケット情報を読み込む - redwarrior’s diary

  3. SSRSのコンポーネントで条件を指定して、同じデータセットからレコード別の値を取得する - redwarrior’s diary

チケット内容の出力であれば、テキストボックスが主になるので上記で済むと思いますが、一覧を使用したい場合は以下を参照してください。

SSRSで表や、一覧を使用してデータセットの内容を一覧表示する - redwarrior’s diary

以上。

SSRSで表や、一覧を使用してデータセットの内容を一覧表示する

SQL Server レポート出力

前回の記事で、テキストボックスにデータセットの値を表示しました。

今回は、データセットのレコードを繰り返し表示する方法として、SQL Server Reporting Services（SSRS）のコンポーネントである表（テーブル）と一覧の 使い方を紹介します。

公式サイトは以下の通りです。

docs.microsoft.com

上記を既に読んでいるのであれば、ここから先を読んでもあまり得るものはないでしょう。ただ公式は情報量が多くて読み通すのに時間がかかるため、最低限のざっくりした内容を書きたいと思います。

SSRSの表（テーブル）の使い方について

表（テーブル）は、データセットのレコードを最も簡単に繰り返し表示できるコンポーネントです。

表（テーブル）を配置したらセルの右上のアイコンをクリックして、データソース、データセット、カラムを順番に選択します。列を追加した場合は、データセットのカラムを選択するだけです。

f:id:redwarrior:20190712114159p:plain
表コンポーネント

列の挿入・削除、見出し行（ヘッダー）や行の挿入・削除も右クリックメニューで行う事ができます。

これでデータセットのレコード数だけ行が表示されます。

SSRSの一覧の使い方について

名前だけで考えると表よりも一覧の方が単純そうに感じますが、SSRSではそうでもありません。配置しただけでは、表コンポーネントのようにカラムを選択するアイコンは出てきません。

一覧は、繰り返し表示する中身を自由にレイアウトする事ができるコンポーネントです。一覧で表示されたエリアに、テキストボックスや表を自由に配置した後、テキストボックス/表の値としてデータセットのカラムの選択ができます。

f:id:redwarrior:20190712120110p:plain
一覧コンポーネント

選択したデータセットのレコード数だけレイアウトした内容で行が表示されます。

まとめ

データセットのレコードを繰り返し表示する方法として、「表」と「一覧」を紹介しました。 集計機能が不要で、一覧形式で表示したいだけであれば「表」と「一覧」だけで十分なので、「マトリックス」は使用していません。

以上です。

（追伸）記事を書くために調べた所、「表」のセル内に「表」や「一覧」や「テキストボックス」を埋め込んで自由にレイアウトができる事がわかりました。

SSRSのコンポーネントで条件を指定して、同じデータセットからレコード別の値を取得する

SQL Server レポート出力 Redmine

前回の記事では、データセットのフィルターを使用してカスタムフィールドを絞り込むという方法を紹介しましたが、

カスタムフィールドの値を表示するテキストボックスを複数用意して、それぞれ別のカスタムフィールドの値を表示したい

f:id:redwarrior:20190629113135p:plain
複数カスタムフィールド

という場合は、カスタムフィールドの数だけデータセットが必要になってしまいます。

そしてデータセット毎にデータを取得するため、データセットが外部サービスを参照している場合、データセットの数だけリクエストが発生します。

これは出来れば避けたい。どうすれば良いでしょうか？

データセットでフィルターを使用せず（複数レコードのまま）に、コンポーネントで式を使用し、条件を指定して値を取得すると良いです。

具体例

例えばテキストボックスの場合、テキストボックスの中ではなく、テキストボックス全体を選択すると右クリックメニューに式が表示されます。そこで式を選択すると式の入力画面になります。*1

式では関数を使用して、レコードを絞り込むことが出来ます。主に使うのは、First関数とLookup関数。

First関数

データセットにレコードが１件しか含まれていない場合や、先頭のレコードだけで良い場合は、First関数を使用します。

=First(Fields!value.Value, "データセット名")

第1引数＝値に使用するカラム(value)、第2引数＝対象のデータセット名

Lookup関数

条件を指定してレコードを取得するのには、LookUp関数を使用します。カスタムフィールドをidで絞り込み、入力値(value)を表示したい場合は、以下のように記述します。

=Lookup("1",Fields!id.Value,Fields!value.Value, "データセット名")

第1引数＝条件値(idカラムの値)、第2引数＝条件に使用するカラム(id)、第3引数＝値に使用するカラム(value)、第4引数＝対象のデータセット名

データセット名を同じ値にして、テキストボックスごとにidの値を変更することで、同じデータセットを使用しつつ、それぞれ別のカスタムフィールド値を表示することが出来ました。

以上。

*1:テキストが既に入力されていると式を入力する事が出来ないのでテキストは空にしておきます。

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でもダメでした