この記事は、SQL Server 上でC# などで書かれたマネージドコードを実行できるSQL CRL 機能を使い、SQL Server 上で、CData ADO.NET Provider を直接実行するという中々にマニアックなネタに関する記事です。
初めに
CData とは？
CData Software はデータ連携の課題を解決するソリューションを提供している企業で、あらゆるデータソースに対してデータベースの形でアクセスできる仕組みを提供しています。
SQL Server とは？
SQL Server はMicrosoft が提供しているデータベースソフトです。
最新版は、SQL Server 2022 です。
Microsoft データ プラットフォーム | Microsoft
SQL CLR とは？
SQL Server 2005 で導入された、.NET Framework 言語を使用して、ストアド プロシージャ、トリガー、ユーザー定義型、ユーザー定義関数、ユーザー定義集計、およびストリーミング テーブル値関数を作成できる仕組みです。
SQL Server の共通言語ランタイム統合
今回参考にさせていただいた記事
今回作るもの
始めに、SQL CLR を使うための環境整備を行った後
実用的な使用例ということで、kintone に対してSQL Server からデータを取得してみます。
用意したもの
- Visual Studio 2022 Community
- SQL Server 2022
- SQL Server Management Studio 19(以下SSMS)
- CData ADO.NET Provider for Kintone 2023
1. SQL Server の設定
- CLR 有効化
デフォルトの設定では、CLR 機能は無効になっているので、CLR 機能を有効にします。
SSMS で以下のようにコマンドを実行します。
EXEC sp_configure 'clr enabled'; --現在の設定値表示
EXEC sp_configure 'clr enabled' , '1'; --設定の変更
RECONFIGURE;
EXEC sp_configure 'clr enabled'; --変更後の設定値表示
プロパティを確認するとこのようになっています。
ここで表示されるdirectory は後で使います。
SELECT * FROM sys.dm_clr_properties;
デフォルトでは、CLR の厳密なセキュリティが有効になっているのでこれを無効に変更します。
本来であれば、有効にしたまま実行したいところですが、諦めました。
ALTER DATABASE master SET TRUSTWORTHY ON;
CLR の厳密なセキュリティ
もしチャレンジしたい場合は参考リンクを貼っておきます
SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
2. プロジェクトの作成
-
「新しいプロジェクトの作成」をクリックします
-
「SQL Server データベースプロジェクト」を選択します
-
「プロジェクト名(J)」を入力します
3. Hello,World!
始めに、一番シンプルな固定値を返すスカラー関数(レコードではなく値を返す関数)を作ってみます。
- プロジェクトを右クリックして、「追加(D)」-「新しい項目(W)...」をクリックします
- 「SQL CLR C# ユーザー定義関数」を追加します
- 追加したクラスに処理を記述します。
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SqlFunctionHelloWorld()
{
return new SqlString ("Hello, World!");
}
}
-
署名を追加します
プロジェクトプロパティのSQLCLR で「署名(S)...」を追加します。
-
データベースへの接続設定
プロジェクトプロパティのデバッグで、「編集(D)...」をクリックし、SQL Server へ接続する際に必要な設定を行います。
-
配置
「ビルド(B)」-「ソリューションの配置(D)」をクリックすると、SQL Server にビルドが配置されます。
もし配置されない場合は、プロジェクトのプロパティで「プロジェクトの設定」-「スクリプトを作成する(.sql ファイル)(E)」がチェックされているか確認しましょう。
-
実行
配置が行われたら、SSMS で確認するとこのようにスカラー値関数が追加されているので、呼び出すとC# で記述した処理が実行されることを確認できます。
SELECT dbo.SqlFunctionHelloWorld();
4. SQL CLR のデバッグ
デバッグができることは非常に重要です。
幸い、SQL CLR はVisual Studio からデバッグを行うことができます。
デバッグ手順
- Visual Studio を管理者として実行
- プロジェクトの追加で、「スクリプト(ビルド内にありません)」を追加
- 追加したスクリプトにデバッグで実行するスクリプト(SQL) を記述
SELECT dbo.SqlFunctionHelloWorld();
-
C# のコードにブレークポイントをセット
-
SQL Server オブジェクトエクスプローラーから対象サーバーを右クリックし、「SQL/CLR のデバッグの許可(L)」をクリック
-
確認画面が表示されるので、「はい(Y)」をクリック
-
スクリプトで「デバッガーで実行(D)」をクリック
-
アタッチのセキュリティ警告が表示されるので、「アタッチ(A)」をクリック
初回だと、ファイアウォールの警告メッセージが表示される場合もあるので、それも許可
-
C# のブレークポイントで停止してデバッグが行える
注意点として、ブレークポイントで停止中は、サーバーが停まるので、デバッグ作業は開発サーバーで行うこと
5. CData ADO.NET Provider をSQL CLR 上でロードする
ここまでで、基本的な開発・実行とデバッグ環境を構築することができたので、いよいよCData ADO.NET Provider を使っていきます。
1. ADO.NET Provider をダウンロード
CData のWeb ページから接続したいデータソース(今回はKintone) のADO.NET Provider をダウンロードします。
下記URL から、今回使用するADO.NET Provider for kintone の30日間の無償トライアル版をダウンロードできます。
Kintone にADO.NET で連携 | ADO.NET Provider | CData Software Japan
2. ADO.NET Provider をインストール
インストールは、「次へ」をクリックしていくだけです。
|
 |
 |
 |
 |
 |
 |
 |
 |
3.プロジェクトに参照を追加
プロジェクト-「追加(D)」-「参照(R)...」で「System.Data.CData.Kintone.dll」を追加します。
dll は、.NET Framework 用とnetstandard2.0 用がありますが、今回使用するのは.NET Framework 用です。
4.kintone にアクセスする関数を追加
まずは、ドライバー自体が動作することを確認するため、システムテーブルにアクセスする処理を作成してみます。
.NET Framework プロジェクトなので、デフォルトだと若干使える構文が古いですが、プロジェクトファイルのプロパティで
<PropertyGroup>
<LangVersion>11</LangVersion>
</PropertyGroup>
とすると、ランタイムに依存しないものに限られますが新しい構文が使えるようになります。
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.CData.Kintone;
using System.Data.SqlTypes;
public partial class UserDefinedFunctions
{
private class SysInformationResult(SqlString product, SqlString version, SqlString datasource, SqlString nodeId, SqlString helpURL, SqlString license, SqlString location, SqlString environment, SqlString dataSyncVersion, SqlString dataSyncCategory)
{
public SqlString Product = product;
public SqlString Version = version;
public SqlString Datasource = datasource;
public SqlString NodeId = nodeId;
public SqlString HelpURL = helpURL;
public SqlString License = license;
public SqlString Location = location;
public SqlString Environment = environment;
public SqlString DataSyncVersion = dataSyncVersion;
public SqlString DataSyncCategory = dataSyncCategory;
}
[SqlFunction(DataAccess = DataAccessKind.Read,
FillRowMethodName = nameof(SysInformation_FillRow),
TableDefinition = "Product nvarchar(255),Version nvarchar(255),Datasource nvarchar(255),NodeId nvarchar(255),HelpURL nvarchar(255),License nvarchar(255),Location nvarchar(255),Environment nvarchar(255),DataSyncVersion nvarchar(255),DataSyncCategory nvarchar(255)")]
public static IEnumerable SysInformation()
{
var resultCollection = new ArrayList();
var cns = new KintoneConnectionStringBuilder();
using var cn = new KintoneConnection(cns);
cn.Open();
var query = "SELECT * FROM sys_Information;";
using (var cmd = new KintoneCommand(query, cn))
{
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
resultCollection.Add(new SysInformationResult(reader["Product"].ToString(),
reader["Version"].ToString(),
reader["Datasource"].ToString(),
reader["NodeId"].ToString(),
reader["HelpURL"].ToString(),
reader["License"].ToString(),
reader["Location"].ToString(),
reader["Environment"].ToString(),
reader["DataSyncVersion"].ToString(),
reader["DataSyncCategory"].ToString()));
}
}
return resultCollection;
}
public static void SysInformation_FillRow(object result,
out SqlString Product,
out SqlString Version,
out SqlString Datasource,
out SqlString NodeId,
out SqlString HelpURL,
out SqlString License,
out SqlString Location,
out SqlString Environment,
out SqlString DataSyncVersion,
out SqlString DataSyncCategory)
{
var item = (SysInformationResult)result;
Product = item.Product;
Version = item.Version;
Datasource = item.Datasource;
NodeId = item.NodeId;
HelpURL = item.HelpURL;
License = item.License;
Location = item.Location;
Environment = item.Environment;
DataSyncVersion = item.DataSyncVersion;
DataSyncCategory = item.DataSyncCategory;
}
}
5. SQL Server に「System.Data.CData.Kintone.dll」を登録する
ここが今回最大の難所です。
SQL CLR では、マネージドDLLアセンブリは、SQL Server に登録する必要があるため、「System.Data.CData.Kintone.dll」を登録する必要があるのですが、そのためには「System.Data.CData.Kintone.dll」が参照している他のDLL も登録する必要があります。
そんなわけでひたすらDLL を探して登録する作業を繰り返していきます。
アセンブリの作成
いろいろごちゃごちゃやってしまったので参考ですが、最終的に動作した状態ではこんな感じになりました。
CREATE ASSEMBLY "System.Drawing" FROM 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll' WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY "Accessibility" FROM 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Accessibility\v4.0_4.0.0.0__b03f5f7f11d50a3a\Accessibility.dll' WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY "System.Runtime.Serialization.Formatters.Soap" FROM 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.Runtime.Serialization.Formatters.Soap\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Runtime.Serialization.Formatters.Soap.dll' WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY "System.Windows.Forms" FROM 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.Windows.Forms\v4.0_4.0.0.0__b77a5c561934e089\System.Windows.Forms.dll' WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY "System.DirectoryServices" FROM 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.DirectoryServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.DirectoryServices.dll' WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY "Microsoft.JScript" FROM 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.JScript\v4.0_10.0.0.0__b03f5f7f11d50a3a\Microsoft.JScript.dll' WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY "System.Management" FROM 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.Management\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Management.dll' WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY "System.Data.CData.Kintone" FROM 'C:\Program Files\CData\CData ADO.NET Provider for Kintone 2023J\lib\System.Data.CData.Kintone.dll' WITH PERMISSION_SET = UNSAFE;
SELECT * FROM sys.assembly_files;
6. sys_information テーブルを取得
無事、sys_information テーブルの情報が取得できました。
ここで一点注意して欲しいのは、上記で表示されている情報の中で、「License」カラムの値が「No License」と表示されている点です。
通常の.NET アプリを作って実行してみると、トライアル版なら「Limited Trial Version - EXPIRING TRIAL [30 DAYS LEFT]」みたいな値になります。
これは、ドライバーがPC 上にあるライセンス情報を参照できていないことを意味します。
このため、ドライバーのロードはできましたが、このままではライセンスが認識されていないので、実際に使用することはできません。
そこで、RTK という接続文字列に指定できるタイプのライセンス情報を使用します。
RTK ライセンスは、CData に問い合わせて発行してもらいます。
(RTK ライセンスもトライアル版があります)
7. Apps テーブルでkintone アプリの一覧を取得
いよいよ最終段階として、実際にkintone と通信してkintone アプリの一覧を取得してみます。
DataRow を代入する処理を簡略化するためにマッピング処理を作ってみました。
今回は試していませんが、多分Dapper も使えるはず
DataRow で取得される型はC# のint やstring ですが、SQL CLR のクラスで扱える型はSqlInt32 やSqlString といった型を使用する必要があるため、マッピング処理ではそれらのキャストを行う必要があります。
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data;
using System.Data.CData.Kintone;
using System.Data.SqlTypes;
using static SQLCLRKintone.Mapping;
public partial class UserDefinedFunctions
{
private class AppsResult
{
public SqlInt32 AppId;
public SqlString Code;
public SqlString Name;
public SqlString Description;
public SqlString SpaceId;
public SqlString ThreadId;
public SqlDateTime CreatedAt;
public SqlString CreatorCode;
public SqlString CreatorName;
public SqlDateTime ModifiedAt;
public SqlString ModifierCode;
public SqlString ModifierName;
public SqlString Alias;
public AppsResult()
{
}
public AppsResult(SqlInt32 appId, SqlString code, SqlString name, SqlString description, SqlString spaceId, SqlString threadId, SqlDateTime createdAt, SqlString creatorCode, SqlString creatorName, SqlDateTime modifiedAt, SqlString modifierCode, SqlString modifierName, SqlString alias)
{
AppId = appId;
Code = code;
Name = name;
Description = description;
SpaceId = spaceId;
ThreadId = threadId;
CreatedAt = createdAt;
CreatorCode = creatorCode;
CreatorName = creatorName;
ModifiedAt = modifiedAt;
ModifierCode = modifierCode;
ModifierName = modifierName;
Alias = alias;
}
}
[SqlFunction(DataAccess = DataAccessKind.Read,
FillRowMethodName = nameof(Apps_FillRow),
TableDefinition = "AppId int,Code nvarchar(255),Name nvarchar(255),Description nvarchar(1000),SpaceId nvarchar(255),ThreadId nvarchar(255),CreatedAt datetime,CreatorCode nvarchar(255),CreatorName nvarchar(255),ModifiedAt datetime,ModifierCode nvarchar(255),ModifierName nvarchar(255),Alias nvarchar(255)")]
public static IEnumerable Apps()
{
var resultCollection = new ArrayList();
var cns = new KintoneConnectionStringBuilder
{
URL = "https://xxxx.cybozu.com",
User = "user",
Password = "password",
RTK = "*********"
};
using var cn = new KintoneConnection(cns);
var dataTable = new DataTable();
var dataAdapter = new KintoneDataAdapter("SELECT * FROM [Apps];", cn);
dataAdapter.Fill(dataTable);
foreach (DataRow row in dataTable.Rows)
{
resultCollection.Add(row.ToObject<AppsResult>());
}
return resultCollection;
}
public static void Apps_FillRow(object result,
out SqlInt32 AppId,
out SqlString Code,
out SqlString Name,
out SqlString Description,
out SqlString SpaceId,
out SqlString ThreadId,
out SqlDateTime CreatedAt,
out SqlString CreatorCode,
out SqlString CreatorName,
out SqlDateTime ModifiedAt,
out SqlString ModifierCode,
out SqlString ModifierName,
out SqlString Alias)
{
var item = (AppsResult)result;
AppId = item.AppId;
Code = item.Code;
Name = item.Name;
Description = item.Description;
SpaceId = item.SpaceId;
ThreadId = item.ThreadId;
CreatedAt = item.CreatedAt;
CreatorCode = item.CreatorCode;
CreatorName = item.CreatorName;
ModifiedAt = item.ModifiedAt;
ModifierCode = item.ModifierCode;
ModifierName = item.ModifierName;
Alias = item.Alias;
}
}
using System;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.SqlTypes;
using System.Linq;
using System.Reflection;
namespace SQLCLRKintone;
internal static class Mapping
{
public static T ToObject<T>(this DataRow dataRow) where T : new()
{
T item = new T();
foreach (DataColumn column in dataRow.Table.Columns)
{
var field = GetField(typeof(T), column.ColumnName);
if (field != null && dataRow[column] != DBNull.Value && dataRow[column].ToString() != "NULL")
{
field.SetValue(item, field.GetValue(item) switch
{
SqlInt32 _ => (SqlInt32)(int)dataRow[column],
SqlString _ => (SqlString)(string)dataRow[column],
SqlDateTime _ => (SqlDateTime)(DateTime)dataRow[column],
_ => ChangeType(dataRow[column], field.FieldType),
});
}
}
return item;
}
private static FieldInfo GetField(Type type, string attributeName)
{
var field = type.GetField(attributeName);
return field ?? type.GetFields()
.Where(p => p.IsDefined(typeof(DisplayAttribute), false)
&& p.GetCustomAttributes(typeof(DisplayAttribute), false)
.Cast<DisplayAttribute>().Single().Name == attributeName)
.FirstOrDefault();
}
public static object ChangeType(object value, Type type)
{
return type.IsGenericType && type.GetGenericTypeDefinition().Equals(typeof(Nullable<>))
? value == null ? null : Convert.ChangeType(value, Nullable.GetUnderlyingType(type))
: Convert.ChangeType(value, type);
}
}
実装されたコードを見るとADO.NET Provider を使って実際にkintone からデータを取得している部分が、通常のデータベースにアクセスする処理とまったく同じ感覚で扱えていることが分かります。
SQL CLR のメリット・デメリット
メリット
- パフォーマンスに優れる
- メインのデータ処理をSQL で記述し、SQLでは難しいロジックを C# で組める
- SQL Serverのトリガーやスケジュールと連携させられる
- SQL Server内で処理を完結できる
デメリット
- 学習コストが高め
- 情報が少ない
- データ処理のフローを組むなら、SSIS という選択肢もあるので、使いどころが難しい
(CData 製品には、SSIS Component もあります)
まとめ
今回、実際に試してみるにあたって、SQL CLR に関するまともな資料があまりなかったことから、SSIS 以上になじみのない人も多いのではないかと感じました。
個人的にSQL CLR を使ってメリットがありそうな場面としては、SQL Server で画像などのバイナリデータを扱うようなケースでしょうか。
また、すでにSQL Server を使っていて、複数のデータソース間で連携させたりする際に、サーバーやサービスプロセスを増やしたくないという場合には、検討の価値があるのではないかと思います。