「Open棟梁 wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。
目次 †
パラメタライズド・クエリ †
動的パラメタライズド・クエリの仕様
- Like検索は標準のパラメタライズド・クエリ、動的パラメタライズド・クエリでサポートされています(パラメタにワイルドカードを含めたLikeの検索条件を渡す)。
- パラメタライズド・クエリのパラメタにnullを渡したい場合、DBNullを使用します(動的パラメタライズド・クエリでは、nullは特殊な制御パラメタとして利用されます)。
パラメタライズド・クエリのバインド変数への †
バインド方法は? †
名前バインドにのみ対応しています。このため、どのデータプロバイダであっても、順番バインドをサポートしません。
- ODP.NETのバインドは名前バインド固定になります。
- OLEDB、ODBC、HiRDBのデータプロバイダは
「?」パラメタの順番バインドにのみ対応しているためフレームワーク内部で名前バインド可能になる様に処理を加えています。
なお、制限事項として、コメント中に パラメタ名と同じ文字列があると正しく動作しませんのでご注意ください。
パラメタ属性指定方法は? †
- 後工程でのPG修正が難しい場合は、SQL定義ファイル中に型キャストを明示すること型指定可能です。
ストアド プロシジャ、無名PL/SQLブロックの実行や、戻り値の取得などは可能か? †
ストアド プロシジャでも動的パラメタライズド・クエリ機能を使用できます。
ストアド プロシジャの実行方法 †
ストアド プロシージャの実行も可能である。
- ポイント
- CommandType?にStoredProcedure?を設定する。
- 戻り値用のパラメタを指定する場合、
- Parameter.Directionプロパティに、
- ParameterDirection?.ReturnValue?を指定。
- アウトプット・パラメタを指定する場合、
- Parameter.Directionプロパティに、
- ParameterDirection?.Outputを指定。
- 実行には、this.ExecSelect_DR()メソッドを使用する。
- 結果セットが複数ある場合
- ループなどを使用して複数の結果セットを取得できる。
- DataTable?.Load()メソッドでは、結果セットが自動的に次に送られる。
- DataTable?.Load()メソッドを使用しない場合、
DataReader?.NextResult?()メソッドを使用して、次に読む結果セットに進む。
- サンプル
ストアド プロシージャの実行については下記のコードを参考のこと。
/// <summary>「sp_help」ストアドプロシージャを実行する。</summary>
/// <param name="objectName">「sp_help」ストアドプロシージャの「objname」パラメタに指定する文字列</param>
/// <param name="ds">「sp_help」ストアドプロシージャが返す結果セット(複数返る)</param>
/// <returns>「sp_help」ストアドプロシージャの戻り値(成功:「」、失敗:「」)</returns>
public int sp_help(string objectName, out DataSet ds)
{
// ストアドプロシージャを指定する。
this.SetSqlByCommand("sp_help", CommandType.StoredProcedure);
// 引数パラメタを設定する。
this.SetParameter("objname", objectName);
// 戻り値パラメタを設定する。
this.SetParameter("ret", null, null, -1, ParameterDirection.ReturnValue);
// ストアドプロシージャを実行し、データリーダを戻す。
SqlDataReader drd = (SqlDataReader)this.ExecSelect_DR();
// 結果セットを取得する
ds = new DataSet();
// 結果セットは複数取得可能
do
{
// DataTableを生成
DataTable dt = new DataTable();
// データリーダから結果をロード
dt.Load(drd);
// DataTableを格納
ds.Tables.Add(dt);
} while (!drd.IsClosed);
// データリーダを閉じる。
drd.Close();
// ストアドプロシージャの戻り値パラメタの値を取得。
return (int)((DamSqlSvr)this.GetDam()).DamSqlCommand.Parameters["ret"].Value;
}
デッドロック、ロックタイムアウト、「ユニークキーの重複」などの例外をリトライしたい。 †
B層ベースクラス2の例外処理をカスタマイズして、 リトライ対象例外を業務例外に振り替え、P層に正常系の戻り値が戻るようにします。
- SetUserParameter()メソッドは、SQLインジェクションの可能性があるものの、DB部品の(僅かな)制約 / 制限などを解決するAPIでもあります。
- このため、SetUserParameter()メソッドの利用を制限せず、D層のベースクラス2に実装するSQLトレースをカスタマイズして
SQLインジェクションの可能性があるSQLをチェック & Warningログを出力するなどの対応をした方がプロジェクトとしては、スムーズに開発を遂行できると考えます。
- D層自動生成ツールでのDao生成時にタイムスタンプ列名を設定し、
タイムスタンプ必須オプションONにして生成することで楽観排他を実現できます。
- 自動生成Daoクラスを利用しないケースに関しては別途排他に関する実装が必要となりますので、
更新系は自動生成Daoを必ず利用するような運用とすることをお勧めします。
OracleのCLOBをODP.NET経由でどう扱うのか? †
サンプル †
- Insert(VB.NET)
Dim sr As New System.IO.StreamReader("C:\temp\input.txt",System.Text.Encoding.Default)
Dim ts as String
ts = sr.ReadToEnd()
sr.Close()
Dim fs As New System.IO.FileStream("C:\temp\input.gif",System.IO.FileMode.Open,System.IO.FileAccess.Read)
Dim bs(fs.Length) As Byte
fs.Read(bs,0,fs.Length)
cmd.CommandText = "INSERT INTO TLOB(NO,TEXT,IMG) VALUES(:v1 ,:v2 ,:v3) "
cmd.CommandType = CommandType.Text
Dim p1 As New OracleParameter()
p1.OracleDbType = OracleDbType.Decimal
p1.Value = 3
cmd.Parameters.Add(p1)
Dim p2 As New OracleParameter()
p2.OracleDbType = OracleDbType.Clob
p2.Value = ts
cmd.Parameters.Add(p2)
Dim p3 As New OracleParameter()
p3.OracleDbType = OracleDbType.Blob
p3.Value = bs
cmd.Parameters.Add(p3)
cmd.ExecuteNonQuery()
tx.Commit()
- Update(C#)
cmd.CommandText = "SELECT XXXX FROM YYYY FOR UPDATE";
reader = cmd.ExecuteReader();
reader.Read();
OracleClob clob = reader.GetOracleClob(0);
clob.Append(ending.ToCharArray(), 0, ending.Length);
txn.Commit();
参考 †
性能 †
コマンド タイムアウト値を設定するにはどうすれば良いか? †
共通のコマンド タイムアウト値をconfigファイルに設定できます。
DataTable?を利用している場合に処理時間が遅い †
概要 †
100,000件 selectの処理を調査したところ、
単体でのSQL実行(SQL Server上でのselect文発行)のレスポンスが1秒程度で、
データ取得後のDataTable?へのデータ設定で、8秒を使用しているようでした。
対策 †
Datatableは少々重いので、大量データには不向きかもしれません。
DataTable?ではなくDataReader?を使用するAPIを使用すると良いかと思います。
自動生成Daoで使用する場合は、テンプレートを修正すればいけると思います。
https://github.com/OpenTouryoProject/OpenTouryo/blob/develop/root/files/tools/DGenTemplates/DaoTemplate2.cs#L347
ODP.NET、HiRDBの配列バインドをサポートしているか? †
- ODP.NET、HiRDBの配列バインドをサポートしています。
- また、配列バインドをサポートしていないデータプロバイダでの
大量データ処理実装のために、バッチクエリ作成支援機能を提供しています。
ポイント †
ODP.NET配列バインドの場合、
- パラメタ
- ArrayBindCount?の指定が必要。
- OracleDbType?の型指定が必要。
- Dam取得方法
- B層でDamを取得する場合は、((DamOraOdp?)this.GetDam())
- 「自作Daoクラス」でDamを取得する場合は、((DamOraOdp?)this._dam)
- 「Dao集約クラス」でDamを取得する場合は、((DamOraOdp?)this.Dam)
サンプル †
以下、ODP.NETのサンプル(自作Daoクラス)。
// SQLを設定
this.SetSqlByCommand("INSERT INTO XXX(AAA, BBB, CCC) VALUES(:P1, :P2, :P3);");
// ODP.NETの配列バインドの場合は、ArrayBindCountを指定
((DamOraOdp)this._dam).DamOracleCommand.ArrayBindCount = temp.Length;
// 配列データを作成
object[] temp1 = new string[] { "aaa", "bbb", "ccc" };
object[] temp2 = new string[] { "aaa", "bbb", "ccc" };
object[] temp3 = new string[] { "aaa", "bbb", "ccc" };
// 配列データをバインド(型情報が必要)
this._dam.SetParameter("P1", temp1, OracleDbType.Varchar2);
this._dam.SetParameter("P2", temp2, OracleDbType.Varchar2);
this._dam.SetParameter("P3", temp3, OracleDbType.Varchar2);
SQLで暗黙の型変換が発生し、検索処理性能が大幅に劣化する。 †
対策 †
- 後工程でのPG修正が難しい場合は、SQL定義ファイル中に型キャストを明示すること型指定可能です。
動的パラメタライズド・クエリを記述するXMLファイルのサイズなどにより性能が劣化しないか? †
概要 †
1つのXMLファイル中に200-300タグを超えてくると、オンライン処理でも性能が劣化が始まりますので、
不要なタグは記述しないようにお願いします(XMLファイル サイズについては、問題ないようです)。
詳細 †
上記のgist.github.comの性能測定の結果は、
- タグがフラットに並んでいないケースでは、
以下のように計算量が変わってきます。
- なお、動的パラメタライズド・クエリ分析ツールを使用した、
テストのために記述するPARAMタグ、DIVタグなどXMLドキュメント オブジェクトの
更新処理を伴わないタグについては、再起処理数に影響を与えません。
測定 †
具体的な性能は、動的パラメタライズド・クエリ分析ツールで測定可能です。
解決方法 †
大量データ処理(バッチ更新処理)をサポートしているか? †
概要 †
大量データ処理(バッチ更新処理)で
性能が出ません。
対策 †
従って、大量データ処理の場合、バッチ処理方式を参照し、
などの技法を活用して、パフォーマンスを出す必要があります。
エラー †
動的パラメタライズド・クエリで、[<]、[>]などの文字が含まれると適切に実行されない。 †
XML内部で使用不可能な文字列は、HTMLエンコーディングが必要になります。
これらの文字列が含まれる場合は、下記の現象が発生します。
- DB部品の最も初めのチェック処理にXmlDocument?.Load()メソッドを使用しているため、
として実行されるため、この現象は、不正なフォーマットのXMLを投げている事に起因します。
して下さい。
自動生成Daoの Update処理で「ORA-00972 "識別子が長すぎます"」が発生する。 †
- D層自動生成ツールは、Update時に使用するパラメタ識別子に
1文字以上のプレフィックス・サフィックスを追加する必要があります。
- この際、パラメタ識別子が30文字を越えてしまい、エラーが発生するケースがあります。
- この再設定と、再生成が面倒な場合は、文字列一括置換ツールなどを使ってパラメタ識別子を
置換するなどして下さい(DaoとUpdate処理のSQL定義(XML)が置換対象ファイルになります)。
検索条件のIN句(副問合せ)を動的化したいがエラーとなる。 †
検索条件のIN句(副問合せ)を動的化する場合、
動的パラメタライズド・クエリの
- IFタグ ではなく、
- タグのネストが可能なSUBタグを
使用して下さい。
Daoの使い分け †
更新系処理 †
自動生成Dao
単一テーブルからのSELECT †
自動生成Dao
複数テーブルなど複雑なSELECT †
汎用Dao(CmnDao)
制限事項 †
- 動的パラメタライズド・クエリのパラメタで、
XMLタグの編集処理に使用されるパラメタ(タグ内パラメタ)の
パラメタ名は、(同じ値を使用するパラメタであっても、)
別々に(1つのXMLタグ毎、ユニークに)定義する必要があります。
アルゴリズム †
含むXMLタグを「再帰的」に検索し、
- パラメタ設定されていれば、
- テキスト内パラメタのあるXMLタグを有効化
- タグ内パラメタのあるXMLタグを有効化(その際、パラメタは消す)
としています。
結論 †
XMLタグの検索方法が再帰的なので、
- タグ内パラメタに同じパラメタがあっても、
パラメタを消す・消さないの判断がつきません。
- しかし、テキスト内パラメタであれば、
パラメタを消さないので、同一名称のパラメタも付与できます。
<?xml version="1.0"?>
<ROOT>
SELECT * FROM Shippers
<WHERE>
WHERE
<IF>ShipperID =@P1<ELSE>IS NULL</ELSE></IF>
<IF>and ShipperID =@P1<ELSE>IS NULL</ELSE></IF>
</WHERE>
<PARAM>
P1, Int32, 1<DIV/>
</PARAM>
</ROOT>
↓↓↓
SELECT * FROM Shippers
WHERE
ShipperID =@p1 and ShipperID =@p1
サポート †
Like句を使用した曖昧検索をサポートしているか? †
- サポートしています。
- パラメタライズド・クエリ自体がLike句を使用した曖昧検索をサポートしているため
文字列連結やSetUserParameter()メソッドを使用する必要はありません。
LINQ や Entity Frameworkをサポートしているか? †
任意のDLL(Assembly)からの埋込SQLのロードをサポートしているか? †
- サポートしています。
- ベースクラス2のSetSqlByFile2メソッドを用いてEntryAssembly?の埋込リソースから SQLをロードすることができますが、
任意のDLL(Assembly)から埋込SQLリソースをロードを行う場合は、SetSqlByFile2メソッドのカスタマイズが必要になります。
PaaSなど、EntryAssembly?が別になる様な場合では、Azureスイッチを指定することで対応できます。
カスタマイズ方法 †
(Azureスイッチを使用すると、他のリソースの埋め込み先にも影響するので)
埋込SQLリソースのみを任意のDLL(Assembly)からロードする場合。
- カスタマイズ方法は、SetSqlByFile2メソッドで、
ようにします。
どのように渡すか?になりますが、この部分はプロジェクト毎に検討ください。
デバッグ †
以下のサイトのコードを参考にして、対象のAssemblyを取得し、
Assembly.GetManifestResourceNames?プロパティで、
Resource一覧が取得して、リソースが埋め込まれているか確認します。
※ 環境によって、フォルダ階層が名前空間に反映されないことがある。
コード †
参考 †
型キャスト †
doc †
- 4章:「D層に関するトピック」も参考にして下さい。
- 7章:「FAQ」> 7.3節:「D層フレームワーク」も参考にして下さい。