  |
 |
2008年10月12日     |
.NET データ処理に役立つ26のヒント（前編） 海外internet.com発の記事はじめに開発者は、さまざまなレベルで、さまざまなツールを使って、さまざまな形態でデータを扱わなければならないことがよくあります。この記事では、データを扱う際に直面しがちな問題について、前編と後編の2回に分けて解説します。前編となる今回は、ADO.NET 2.0、ASP.NET 2.0、T-SQL 2005の機能について取り上げます。後編では、T-SQL 2005に関するその他の話題や、.NET 2.0のジェネリックの使用方法について取り上げます。 目的：データ処理の包括的なヒント集先日私は、CodeCampという集まりの中で、データ処理に関するさまざまなトピックについて、いくつかのセッションで話す機会がありました。あるセッションでは、開発者の目を引くT-SQLの新機能について話し、別のセッションでは、ADO.NET 2.0による新手法について取り上げ、さらに別のセッションでは、Webアプリケーションにおけるデータ処理について解説する、といった具合です。そのとき、参加者の1人が、セッション終了後に私のところにやって来て、こんなことを言ってくれました。「C#のMVPとして紹介されている人が、データやその処理方法についてこんなに詳しく解説してくれるとは嬉しい驚きだった」、というのです。私は笑顔で答えました。私は今度、データ処理テクニックについての記事を『CoDe Magazine』誌に書くことになっており、あなたからいただいた質問も含め、今日の経験は、そのアイデアを練るうえで役に立ちましたと。 使用している言語がC#にせよVisual Basic .NETにせよ、あるいはそれ以外にせよ、開発者たちが日がな1日処理しているのは「データ」です。データの検索、集計、表示といった処理です。アプリケーション開発者の主たる仕事は、データの処理なのです。誰が何と言おうと、その点は確かです。 1つのツールだけを使ってデータ処理を行う開発者はあまりいません。データベース、レポート作成プログラム、サードパーティ製のユーティリティなどを使って、プレゼンテーション層を処理したり、他の部分もまとめて処理したりします。使用できるツールや手法は数限りなくあります。.NETにおけるデータ処理のさまざまな手法をテーマにして、1冊の本が書けるくらいです。 そこでこの記事は、前編と後編に分けることにします。「The Baker’s Dozen」シリーズとしては初の「ダブルヘッダー」です。「第1戦」となる前編では、以下の内容を取り上げます。
ダブルヘッダーの「第2戦」では、.NETのジェネリックを活用したデータ処理や、新しい ヒント1：型指定されたデータセットの基礎 型指定されたデータセットとは、ビジネスエンティティデータのコレクションを取りまとめる、厳密に型指定されたコンテナです。 型指定されたデータセットでは、IntelliSenseによる入力支援や、コンパイル時の型チェックが可能です。また、レポートという観点では、型指定されたデータセットを使用するとデータ駆動型のレポート作成を簡便化でき、ストアドプロシージャの結果セットのセルフドキュメンテーションを的確に実現できます。型指定されたデータセットを利用したコーディングでは、効率性や構造のレベルが上がります（この点については、後でいくつか例を示します）。 型指定されたデータセットの作成方法は2つあります。スキーマが既にある場合（または簡単に作成できる場合）には、次のような方法が使えます。 DataSet DsReturn = this.RunStoredProc("GetAgingReceivables"); // Define a DataSet name and table names // (we could use Table Mappings as well) DsReturn.DataSetName = "dsAgingReport"; DsReturn.Tables[0].TableName = "dtAgingDetails"; DsReturn.Tables[1].TableName = "dtAgingSummary"; DsReturn.Tables[2].TableName = "dtAgingBrackets"; DsReturn.Tables[3].TableName = "dtClients"; // Write out the schema DsReturn.WriteXml(@"c:MyXmlDsAgingReport.xsd", XmlWriteMode.WriteSchema); このテストコード（これを基にしたスキーマ生成ユーティリティも可）を実行し、生成されたスキーマを、型指定されたデータセットとしてVisual Studio 2005に追加します。ソリューションエクスプローラで右クリックし、［Add（追加）］の［Existing Item（既存の項目）］をクリックして、XSDファイルが保存されているフォルダに移動します。型指定されたデータセットがVisual Studioによって作成されたら、それを参照および編集する方法はいくつかあります。標準のXMLスキーマエディタを使用する場合は、ソリューションエクスプローラでデータセットを右クリックし、［Open With（ファイルを開くアプリケーションの選択）］をクリックします。 スキーマがまだなく、型指定されたデータセットの定義を一から作成する場合は、データセットデザイナを利用して手動で作成できます。しかし、その方法ばかり多用し、型指定されたデータセットを既存のスキーマから作成する方法をあまり使用していない人は、方法を考え直した方が良いかもしれません。 ともあれ、型指定されたデータセットの作り方は分かりました。ではこれで、何が実現されるのでしょうか。型指定されたデータセットの機能や特徴について見ていくことにしましょう。 1つ目は、型指定されたデータセットでは、厳密な型指定が可能で、IntelliSenseによる入力支援機能も利用できるという点です（図1を参照）。型指定されていないデータセットの場合は、列を番号で参照したり、式として指定しなければなりません。また、図1の例では、列を適切なデータ型にキャストする必要がありません。私見では、列オブジェクトをしかるべき型にキャストしなくてよいというのは、型指定されたデータセットの最も嬉しい機能の1つと言えます。 図1 型指定されたデータセットでは入力支援を利用可能  2つ目は、列の既定値をデータセットデザイナで簡単に定義できるという点です。 3つ目は、データセットデザイナで厳密に型指定されたDataTableに主キーを定義した場合、その列名に対応する 4つ目は、型指定されたデータセットにテーブルのリレーションが含まれている場合、XMLスキーマ定義ツールによって、階層型のデータをたどるためのメソッドが、型指定されたデータセットクラスに用意されるという点です。例えば、「DtOrderHeader」と「DtOrderDetail」というテーブルに対して、親テーブル／子テーブルのコードを作成する場合は、.NETのスキーマツールによって 5つ目は、型指定されたデータセットの方がnull値を扱いやすいという点です。厳密に型指定されたDataRowには、列値がnullかどうかをチェックするメソッドと、列値をnullに設定するメソッドが用意されます。 // Set value to NULL oRow.SetHoursWorkedNull(); // Check for null value if(oRow.IsHoursWorkedNull()==true) 6つ目は、Visual Studio .NET 2003の型指定されたデータセットに対する批判の声にも応える機能です。Visual Studio 2005の新しいパーシャルクラス機能によって、型指定されたデータセットを継承しやすくなりました。ただ、パーシャルクラスは確かに素晴らしい新機能ですが、VS2003でも、型指定されたデータセットのサブクラスを作成して検証コードやその他の機能を追加することは不可能ではありません。『CoDe Magazine』誌の2006年1月／2月号で私が執筆した記事では、型指定されたデータセットのサブクラスを作成して、XMLのインポート機能を追加するという例を紹介しています。 型指定されたデータセットは完全無欠ではありません。その使用にはオーバーヘッドが伴います。型指定されたデータセットは、型指定されていないデータセットより高速な面もあるものの、データセットが複雑な場合には、インスタンス化が負担となる可能性があるのです。型指定された同じデータセットのインスタンスを頻繁に作成するアプリケーションでは、その作成処理でかなりの時間を食われてしまうかもしれません。Uri N.氏が開発した、型指定されたデータセット用のプロキシクラスを使用すると、実際の作成は一度だけで済むようになります。このクラスの詳細については、CodeProjectのページを参照してください。 開発者の中には、型指定されたデータセットクラスで、DataTable／DataRowオブジェクトなどの項目やメソッド／イベントに適用される既定の名前付け規則について、不満を抱く人もいます。自動的に付けられる名前が、自分の開発プロジェクトで使用したい名前付け規則と合致しない場合もあります。幸い、名前付けに関してよくある問題のいくつかは、型指定されたデータセットの注釈を使用することで解決できます。注釈では、型指定されたデータセットで使用される要素の名前を開発者が変更できます。実際のスキーマは変更する必要がありません。 また、注釈では、列がDbNullの場合の対応方法を指定することも可能です。それには、nullValueという注釈を使用します。空文字列や既定の代替値を返すよう、オプションで指定できます。型指定されたデータセットの注釈の使用方法については、Shawn Wildermuth氏の素晴らしい記事がネット上で読めます（Shawnは、ADO.NETに関する文章を多数執筆している敏腕ライターです）。 ヒント2：日付の演算.NET Frameworkには、2つの日付の差を計算したり、未来の日付を求めたりなど、日付に関する各種の演算機能を持つクラスがあります。いくつかの例を見てみましょう。 // Create two dates, Nov 1 2005 and Oct 1, 2005 DateTime dAgingDate = new DateTime(2005,11,1); DateTime dInvDate = new DateTime(2005, 10, 1); TimeSpan ts = dAgingDate.Subtract(dInvoiceDate); int nDiffDays = ts.Days; // Determine future dates DateTime dNextDay = dtInvoiceDate.AddDays(1); DateTime dNextMonth = dtInvoiceDate.AddMonths(1); DateTime dNextYear = dtInvoiceDate.AddYears(1); // Determine # of days in October 2002 int nDaysInMonth = DateTime.DaysInMonth(2002, 10); // Is the year a leap year? if(DateTime.IsLeapYear(2006)==true) // Determine if a string represents a valid date public bool IsValidDate(string cText) { bool lIsGoodDate = true; // Convert in a try/catch try { DateTime dt = Convert.ToDateTime(cText); } catch (Exception) { lIsGoodDate = false; } return lIsGoodDate; } // Gets today (time will be midnight) DateTime dToday = DateTime.Today; // Get the current date/time DateTime dNow = DateTime.Now; Webで調べると、日付の計算機能は他にもいろいろと見つかります。私が最近見つけた中では、このような素晴らしいものがありました。 ヒント3：データのリレーション ヒント1でも述べたように、型指定されたデータセットを定義すると、リレーションがあるDataTableを扱いやすくなります。データセットデザイナでリレーションシップを定義することにより、Visual Studioによってリレーションが自動的に生成され、親子関係の子の行を取得するための既定のメソッドが用意されます。次の例では、厳密に型指定された注文ヘッダーの行オブジェクトが dsOrders odsOrders = new dsOrders(); // Run some process to fill the DataSet foreach(dsOrders.dtOrderHdrRow oHdrRow in odsOrders.dtOrderHdr.Rows) foreach(dsOrders.dtOrderDtlRow oDtlRow in oHdrRow.GetdtOrderDtlRows()) nProductPK = oDetailRow.ProductPK; ヒント4：データのフィルタリング ADO.NETでデータをフィルタ（抽出）する方法は2つあります。1つはDataViewとRowFilterを使う方法、もう1つは 一般的には、DataViewを使用して、フィルタした結果をバインドします。そして、DataRowの配列を使用して、フィルタした結果をたどり、処理や計算などを実行します。そもそも、DataViewとは、特定のフィルタと並べ替え順序が適用された、DataTableのビューの一つにすぎません。次のコードは、さまざまなフィルタや並べ替えの例です。 string cFilter; // Examples of RowFilter cFilter = " Amount > 1000 "; cFilter = "FirstName = ’Ken’ OR EmpFlag = true"; cFilter = " City LIKE ’%whatever%’ "; cFilter = "EmployeeID IN (12,144,54)"; // This assumes a Parent Relation cFilter = "Parent(RelName) = ’National’"; string cSort = "Location ASC, Salary DESC"; DataView Dv = new DataView(MyTable, cFilter, cSort, DataViewRowState.CurrentRows); 上記のコードでは、DataViewのオーバーロードの1つを使用して、フィルタと並べ替えをまとめて指定しています。開発者によっては、DataTableだけを指定するオーバーロードを使用し、後続のコードで それともう1点、DataViewは、同じDataTableに対して複数作成でき、それぞれ別個のフィルタと並べ替えを適用できます。一方、 DataRow[] aRows = MyTable.Select(cFilter,cSort); それでは、DataViewを使用するか否かはどのように判断すればよいのでしょうか。答えは簡単です。フィルタの結果をバインドしたいときにはDataViewを使用します。DataViewは、「 しかしその一方で、DataRowのコレクションでは可能なのに、DataViewでは不可能な処理もあります。型指定されたデータセットの中の、厳密に型指定されたオブジェクトのいずれかとして、各行をキャストするという処理です。 dsOrders odsOrder = new dsOrders(); // Run some process to populate orders // Now perform a select and filter DataRow[] aRows = odsOrder.dtOrderHdr.Select(cFilter, cOrder); // We can cast the collection of rows // as the strongly-typed rows from our typed DS foreach (dsOrders.dtOrderHdrRow oRow in aRows) { cOrderNumber = oHeaderRow.OrderNumber; } 従って、一般論としては、フィルタしたデータをバインドする場合にはDataViewを使用し、各行をたどって計算などの処理を実行する場合には行の配列を使用するのが良いでしょう。 時として、DataViewをDataTableにコピーし戻すことが必要になる場合があります。DataViewをXMLにコピーすることや、DataViewを認識しない.NETライブラリや製品（Crystal Reportsなど）にDataViewの結果をバインドすることが必要になるかもしれません。Visual Studio .NET 2003には、DataViewをDataTableに変換する機能は備わっていませんが、幸い、小粒ながらも便利な汎用的関数を作成することで、その処理を実現できます。 public DataTable ViewToTable(DataView dv) { // Create a new table structure from the table // object of the view, then loop through the view // and import the rows into the new table DataTable DtReturn = dv.Table.Clone(); foreach (DataRowView drv in dv) DtReturn.ImportRow(drv.Row); // Note that we have to use ImportRow // There’s no such thing as Row.Copy return DtReturn; } このコードに手を加えれば、DataRowの配列からDataTableを作成する処理も簡単に実現できます。 最後にもう1つ、重要な情報です。Visual Studio 2005には、 DataTable dtFromView = dv.ToTable(); bool lDistinct = true; DataTable dtFromView = dv.ToTable(lDistinct, "Column1", "Column2"); // You can even create a unique list from the // DefaultViewof a DataTable DataTable dtUnique = dtMyTable.DefaultView.ToTable(true, "Column1", "Column2"); ヒント5：DataSetでの演算データセットの演算は2つのレベルで実装できます。1つは行ごとの演算で、その場合は集計列としてDataColumnを定義します。もう1つはDataTable全体（または行のコレクション全体）を対象とした演算で、その場合は「SUM」または「COUNT」を計算します。 集計列は次のように作成します。 string cExpr = "HourlyRate * Hours"; Dt.Columns.Add("Pay", typeof(Decimal), cExpr); 型指定されたデータセットを使用している場合は、データセットデザイナで DataTableのすべての行に対する計算を実行する場合は、 string cExpr = " SUM(Hours)"; string cCond = " OTFlag = true"; decimal nTotHrs = (decimal)Dt.Compute(cExpr,cCond); また、親子関係に基づく集計値を自動的に計算することもできます。例えば、顧客データのDataTableと注文データのDataTableがあり、顧客IDに基づく親子関係があるとします。この場合、顧客データのテーブル「dtClients」に、各顧客に対応する注文の合計を反映する集計列を持たせることができます。そのためには、親テーブルで ヒント6：ASP.NET 2.0のGridViewの概要ブラウザベースのデータベースアプリケーションでは、多くの場合、GridViewコントロールの機能を利用します。GridViewコントロールは、DataGridコントロールの後継にあたる、ASP.NET 2.0の新機能です。Windowsフォーム環境からWebフォーム環境に移ってきた開発者は、両プラットフォーム間でGridViewコントロールが大きく異なることに気付くはずです。ヒント6〜9では、その一般的な機能について説明します。 図2は、ASP.NETの基本的なGridViewの例です。この例では、Northwindデータベースからデータを取得し、結果のデータセットにGridViewをバインドします。そして、列、配置、書式を定義して、運賃が100.00ドルを超える行を黄色で表示します。 図2 ASP.NET 2.0の基本的なGridView  この実装には、次の5つの手順を踏みます。 まず、WebフォームのツールボックスからWebフォームページにGridViewコントロールをドラッグ＆ドロップします。WebフォームデザイナでGridViewコントロールのインスタンスがページ上に作成されたら、GridViewを右クリックしてプロパティページを表示します。名前（ 2つ目は、列を手動で定義する処理です。これが必要なのは、 図3 ASP.NET 2.0のGridViewの列の定義  図2のGridViewの表示では、一部の列が右揃えになっています。また、2つの日付列（「Order Date」と「Ship Date」）の書式が異なっています。一方は日付のみなのに対し、もう一方は日付に加えてHH:MM形式の時刻も表示されています。 図3で、バインドされる個々の列を追加したら、 GridViewのデザインが済んだら、このGridViewをバインドするためのコードを記述します。 DataSet dsData = this.GetData(); // Run whatever process you want, for the query // SELECT OrderID, CustomerID, OrderDate, // ShippedDate, Freight FROM Orders ORDER BY // OrderID this.grdOrders.DataSource = dsData.Tables[0]; this.grdOrders.DataBind(); 最後に、運賃が100ドルを超える行を強調表示する機能を実装します。それには、GridViewの protected void grdOrders_RowCreated(object sender, GridViewRowEventArgs e) { if (e.Row.DataItem != null) { DataRowView drv = (DataRowView)e.Row.DataItem; decimal nFreight = Convert.ToDecimal(drv["Freight"]); if (nFreight> 100) e.Row.BackColor = System.Drawing.Color.Yellow; } } なお、本番環境用のコードでは、運賃のロジックはビジネス層にまとめるのが良いでしょう。また、交互表示の色は、ハードコーディングするのではなくCSSの定義を使用するとよいでしょう。 ヒント7：GridViewでの選択処理GridViewを使い始めて日が浅い人から、GridViewの特定の行にリンクを配置する方法を聞かれることがあります。例えば、その行の詳細情報が掲載された別のWebページを開くなどの処理を行う場合です（図4を参照）。 図4 各注文を開くリンクが設定されたASP.NET 2.0のGridView  これを実装する方法はいくつかあります。ここでは、3つの簡単な手順で済む方法を紹介します。手順1では、GridViewの列デザイナを開き、使用可能なフィールドのリストから 手順2は後回しにして、先に手順3を説明します。手順3では、GridViewの protected void grdOrders_SelectedIndexChanged (object sender, EventArgs e) { int nOrderID = (int) this.grdOrders.SelectedDataKey.Values ["OrderID"]; // Now we can load another page with the ID } この方法に関して、1つ補足しておきます。テキストリンクではなく、「開く」や「編集」を意味するアイコンを表示したい場合、基本的な手順は上記と同じですが、 ヒント6では、条件に応じて行の色を変える方法を紹介しました。場合によっては、条件に応じてGridViewにアイコンを表示するという処理が必要になるかもしれません。それを実現するには、GridViewに特別なTemplateField列を定義する必要があります。それには、WebページのHTMLソースを開き、GridViewの先頭（または末尾）の列として次のコードを追加します。 <asp:TemplateField > <ItemTemplate> <img src= ’<%# GetPic(Container.DataItem) %>’/> </ItemTemplate> <ItemStyle Width="3px" /> <HeaderStyle Width="3px" /> </asp:TemplateField> 関数 protected string GetPic(object dataitem) { string cIcon = ""; // evaluate the row from the GridView bool lFlag = Convert.ToBoolean(DataBinder.Eval (dataitem, "ShowPictureFlag")); if (lFlag == true) cIcon = "SpecialPic.gif"; else cIcon = "Blank.gif"; return cIcon; } ヒント8：ASP.NET 2.0のGridViewでの並べ替え ヒント6で作成した基本的なGridViewでは、 しかし悲しいかな、実際のところは、これらのリンクのどれをクリックしても、期待どおりには動いてくれず、実行時エラーのメッセージが表示されてしまいます（図5と図6を参照）。 図5 Sortingイベントの処理前のエラーメッセージ  図6 PageIndexChangingイベントの処理前のエラーメッセージ  何がいけないかと言うと、メッセージからも分かるように、処理を正常に進めるためには、GridViewの 方法の1つは、GridViewの
DataTable dtOrders
{
get { return (DataTable)ViewState["dtOrders"]; }
set { ViewState["dtOrders"] = value; }
}
しかしこれでは、結果セットが巨大な場合でも、注文のリスト全体をページのViewStateに持たせることになってしまいます（これを確認するには、Webページをセットアップし、結果セットをViewStateに格納したうえで、ページを実行し、［表示］の［ソース］をクリックしてみましょう。データの量は予想よりずっと多いかもしれません）。それに、データ型によってはシリアル化できないものもあります。例えば、 別の方法としては、データのバインドを実行する時点で DataSet dsData = this.GetData(); // Run whatever process you want, for the query // SELECT OrderID, CustomerID, OrderDate, // ShippedDate, Freight FROM Orders ORDER BY // OrderID this.grdOrders.DataSource = dsData.Tables[0]; // Store the datatable as a Session Variable Session["dtOrders"] = dsData.Tables[0]; this.grdOrders.DataBind(); こうして、 protected void grdOrders_Sorting (object sender, GridViewSortEventArgs e) { DataTable dtOrders = (DataTable)Session["dtOrders"]; if (dtOrders != null) { dtOrders.DefaultView.Sort = e.SortExpression this.grdOrders.DataSource = dtOrders.DefaultView; Session["dtOrders"] = dtOrders; this.grdOrders.DataBind(); } } これは、前代未聞の画期的な手法というわけではありません。ASP.NETに関するあちこちのフォーラムで、同様の手法を目にすることができます。 ヒント9：ASP.NET 2.0のGridViewでのページング 並べ替えの問題が片づいたので、次はページングの処理に移りましょう。ユーザーがページ下部のページリンクのいずれかをクリックすると、ポストバックが生じ、 protected void grdOrders_PageIndexChanging (object sender, GridViewPageEventArgs e) { // must set new PageIndex and rebind this.grdOrders.PageIndex = e.NewPageIndex; this.grdOrders.DataSource = ((DataTable)Session["dtOrders"]).DefaultView; this.grdOrders.DataBind(); } さて、次の話題へ進む前に、セッション変数についてひとこと。セッション変数を使うかどうかは、人によってさまざまです。愛用して頼りきりになる人もいるし、スケーラビリティの問題から異議を唱える人もいるし、その中間の人もいます。 セッション変数を使うのは、簡単すぎるくらい簡単です。そして言うまでもなく、簡単すぎるものは、乱用される傾向が大です。『Visual Studio Magazine』誌の2003年10月号に、セッション状態の管理について、Leonard Lobel氏による素晴らしい記事が掲載されています。その中では、サーバー側で状態管理を行う7つの手法が取り上げられており、セッション情報をSQL Serverに格納する方法も紹介されています。このトピックについて理解を深めたい方には、Lobel氏の記事は大いに役立つものと思います。 ヒント10：SQL Server 2005でのTOP N SQLの -- Dynamic SQL to implement variable TOP N DECLARE @nTop int SET @nTop = 5 DECLARE @cSQL nvarchar(100) SET @cSQL = N’SELECT TOP ’ + CAST(@nTop AS VARCHAR(4)) + ’ * FROM ORDERS ORDER BY Freight DESC’ EXECUTE SP_EXECUTESQL @cSQL -- Setting ROWCOUNT to implement variable TOP N DECLARE @nTop int SET @nTop = 5 SET ROWCOUNT @nTop -- Get the top 5 with the most Freight SELECT * FROM Orders ORDER BY Freight DESC -- Set back to 0 SET ROWCOUNT 0 幸い、SQL Server 2005では、 -- New implementation in SQL Server 2005 DECLARE @nTop int SET @nTop = 5 SELECT TOP (@nTop) * FROM Orders ORDER BY Freight DESC -- The N can even be the result -- of another function SELECT TOP( SELECT COUNT(*) FROM SHIPPERS) * FROM ORDERS また、可変の DECLARE @nTop int SET @nTop = 100 UPDATE TOP (@nTOP) Orders SET Freight = Freight * 1.1 ヒント11：SQL Server 2005でのPIVOT 私は、『CoDe Magazine』誌の2005年3月／4月号で、T-SQL 2000でのデータベース開発に役立つヒント集を、「The Baker’s Dozen」シリーズの記事として執筆しました。その中で取り上げた例の一つに、口座年齢表の結果セットを作成するというものがあり、 リスト1は新しいPIVOT機能の使用例です。この例では、各請求書間の経過日数と、何日付けかという日付を計算したうえで、PIVOTステートメントを使用して、対応する区分に請求額を配置します。 リスト1 T-SQL 2005のPIVOTを使用して行を列に変換する
-- First, create some test data. DECLARE @tInv TABLE (CustomerID char(15), InvoiceNo Char(20), InvoiceDate DateTime, InvoiceAmount decimal(14,2), ReceivedAmount decimal(14,2)) INSERT INTO @tInv VALUES (’Cust 1’,’ABC’,’09-01-2005’, 1000, 0) INSERT INTO @tInv VALUES (’Cust 1’, ’DEF’,’10-01-2005’, 2000, 100) INSERT INTO @tInv VALUES (’Cust 1’, ’GHI’,’11-01-2005’, 3000, 3000) INSERT INTO @tInv VALUES (’Cust 1’, ’JKL’,’12-01-2005’, 4000, 175) INSERT INTO @tInv VALUES (’Cust 1’, ’MNO’,’12-18-2005’, 4000, 175) INSERT INTO @tInv VALUES (’Cust 2’, ’PQR’,’05-01-2005’, 500, 250) INSERT INTO @tInv VALUES (’Cust 2’, ’STU’,’08-01-2005’, 12000, 0) INSERT INTO @tInv VALUES (’Cust 2’, ’WYX’,’10-01-2005’, 7000, 70) INSERT INTO @tInv VALUES (’Cust 2’, ’YYZ’,’12-01-2005’, 3200, 1750) -- Second, every aging report has an "as of" date. DECLARE @dAgingDate DATETIME SET @dAgingDate = ’12-15-2005’ -- Third, create a table of aging brackets and their ranges. -- This demonstrates how you can have configurable date ranges -- (i.e. 1-45 days, 46-90, etc.). DECLARE @tAgingBrackets TABLE ( StartDay int, EndDay int, BracketNum int, BracketLabel char(20)) INSERT INTO @tAgingBrackets VALUES (0, 30,1, ’1-30 Days’) INSERT INTO @tAgingBrackets VALUES (31, 60,2, ’31-60 Days’) INSERT INTO @tAgingBrackets VALUES (61, 90,3, ’61-90 Days’) INSERT INTO @tAgingBrackets VALUES (91, 120,4, ’91-120 Days’) INSERT INTO @tAgingBrackets VALUES (121,99999,5, ’> 120 Days’) -- Note: The BracketNum column is especially critical. -- When we match up each amount due with the date range, -- we’ll place the amount due into that bracket. -- Fourth, create a table variable to hold the result set. DECLARE @tAgingDetails TABLE (CustomerID char(15), InvoiceNo char(20), InvoiceDate DateTime, Bracket1 decimal(14,2), Bracket2 decimal(14,2), Bracket3 decimal(14,2), Bracket4 decimal(14,2), Bracket5 decimal(14,2)) -- Fifth, run the query. -- In the WHERE clause, use DateParts to determine the # of days -- between the invoice date and the "as of date", grab the -- corresponding bracket number…and at the end, PIVOT on -- the sum of AmountOwed for the BracketNumber being in -- one of the five brackets. INSERT INTO @tAgingDetails SELECT * FROM (SELECT CustomerID,InvoiceNo, Invoicedate, InvoiceAmount - ReceivedAmount AS AmountOwed, BR.BracketNum FROM @tInvoices TI, @tAgingBrackets BR WHERE InvoiceAmount - ReceivedAmount <> 0 AND DATEDIFF(dd,Invoicedate,@dAgingDate) BETWEEN BR.StartDay AND BR.EndDay ) as Temp PIVOT ( SUM(AmountOwed) FOR BracketNumber In ( [1],[2],[3],[4],[5])) As X SELECT * FROM @tAgingBrackets SELECT * FROM @tAgingDetails ORDER BY CustomerID, InvoiceNo ヒント12：SQL Server 2005の再帰クエリと共通テーブル式SQL Server 2005で目を引く新機能の1つが、再帰クエリを作成できる機能です。再帰クエリとは、その名が示すとおり、そのクエリ自身の結果に対してクエリを行うことで処理全体を構成するというものです。例えば、階層型のデータに対するクエリを行って、可変個の親子関係を抽出することができます。 再帰クエリと共通テーブル式の簡単な例を紹介します。リスト2は、階層を上と下にクエリする2つの簡単な例です。処理は2つの部分で構成されます。メインとなる「アンカー」クエリでは、最初の結果セットを共通テーブル式に取り込みます。共通テーブル式はさまざまな点で派生テーブルに似ています。2番目の部分では、共通テーブル式を再帰的にクエリします。そして、クエリの作成方法に応じて、親または子のいずれかを取得します。SQL Server 2005の既定では、100レベルまでの再帰が可能ですが、この値は設定により変更できます。 リスト2 再帰クエリと共通テーブル式
-- Let’s create some hierarchical data. -- A hierarchy of music information -- music genres, instruments, and musicians. -- This could be a hierarchy of company product lines, -- or a Bill of Material structure, or -- even a company’s organizational database. -- Each row contains a PK and a reference to it’s parent. DECLARE @tMusicData TABLE (MainPK int, ParentPK int, Name char(50)) INSERT INTO @tMusicData VALUES (1,NULL,’Musicians’) INSERT INTO @tMusicData VALUES (2,1,’Jazz’) INSERT INTO @tMusicData VALUES (3,1, ’Rock’) INSERT INTO @tMusicData VALUES (4,1, ’Classical’) INSERT INTO @tMusicData VALUES (5,2,’Saxophone’) INSERT INTO @tMusicData VALUES (6,2,’Trumpet’) INSERT INTO @tMusicData VALUES (7,3,’Guitar’) INSERT INTO @tMusicData VALUES (8,4,’Piano’) INSERT INTO @tMusicData VALUES (9, 5,’Charlie Parker’) INSERT INTO @tMusicData VALUES (10,5,’John Coltrane’) INSERT INTO @tMusicData VALUES (11, 6,’Miles Davis’) INSERT INTO @tMusicData VALUES (12, 7,’Eddie Van Halen’) INSERT INTO @tMusicData VALUES (13, 6,’Franz Liszt’) DECLARE @cSearch char(50) SET @cSearch = ’Charlie Parker’ -- You want to query on a single row, and get every parent -- to which it belongs. -- MusicTree is the CTE (similar to a derived table). WITH MusicTree (ResultName, PKValue) -- First, the main, or "anchor" query AS (SELECT Name, ParentPK FROM @tMusicData WHERE Name = @cSearch -- This pulls out Charlie UNION ALL -- Second, the recursive query. -- Note that it queries from the CTE, MusicTree, for -- all the parents. SELECT Name, parentPK FROM @tMusicData INNER JOIN MusicTree ON PKValue = MainPK ) SELECT * FROM MusicTree -- Results: -- Charlie Parker -- Saxophone -- Jazz -- Musicians -- Let’s try again, but this time, query for all the children. SET @cSearch = ’Saxophone’ WITH MusicTree (ResultName, PKValue) -- This time, we reverse the searches on MainPK and ParentPK. AS (SELECT Name, MainPK FROM @tMusicData WHERE Name = @cSearch UNION ALL SELECT Name, MainPK FROM @tMusicData INNER JOIN MusicTree ON PKValue = ParentPK) -- Results: -- Saxophone -- Charlie Parker -- John Coltrane ヒント13：SQL Server 2005でのテーブル値ユーザー定義関数の結果の適用 正直言って、この機能は、T-SQL 2005の新機能の中でも特に気に入っています。APPLY（適用）という名が示すとおり、この機能は、テーブル値ユーザー定義関数の結果を、一時テーブルを介さずに、SQLの T-SQLは、プログラムのモジュール性のレベルという点では、C#やVisual Basicでの開発にはかないませんが、新たに導入されたこの 次のテーブル値ユーザー定義関数で考えてみましょう。特定の顧客に対応する注文について、注文数に基づくTOP Nをテーブル変数で返す関数です。 CREATE FUNCTION [dbo].[GetTopNOrders] (@CustomerID AS varchar(10), @nTOP AS INT) RETURNS TABLE AS RETURN SELECT TOP(@N) OH.OrderID, CustomerID, OrderDate, (UnitPrice * Quantity) as Orderamount FROM Orders OH JOIN [dbo].[Order Details] OD ON OH.OrderID = OD.OrderID WHERE CustomerID = @CustomerID ORDER BY ORDERAMOUNT DESC GO ここで、顧客データベース全体に対するクエリを行い、各顧客について上記のユーザー定義関数を実行して、TOP Nの注文を取得したいとしましょう。SQL Server 2005では、顧客データのクエリに対してユーザー定義関数の結果を DECLARE @nTopCount int SET @nTopCount = 5 SELECT TOPOrd.CustomerID, TOPOrd.OrderID, TOPOrd.OrderDate, TOPOrd.OrderAmount FROM Customers CROSS APPLY DBO.TopNOrders(Customers.CustomerID, @nTopCount) AS TOPOrd ORDER BY TOPOrd.CustomerID,TOPOrd.OrderAmount DESC 次回予告冒頭でも述べたように、今回の記事は、データ処理機能について取り上げる全2回の記事の前編です。次回の後編では、.NETのジェネリックや、ASP.NETの新しいObjectDataSource機能、T-SQL 2005のその他の機能について解説します。そちらもお楽しみに。 最後に私のWebサイトに、すべてのソースコードが掲載してあります。最新情報や補足情報については、私のブログでご確認ください。 文章、書類、コードなどを提出したときに、その後になって、もっと良いアイデアが浮かんだという経験はありませんか。実のところ、私は後知恵の帝王です。幸い、そうした場合の対応にはブログがうってつけです。「The Baker’s Dozen」に関連する補足情報や追加のヒントについては、私のブログをぜひチェックしてください。他にも、お得な情報が載っているかもしれませんよ。 著者紹介Kevin S. Goff（Kevin S. Goff）
.NET、Visual FoxPro、SQL Server、Crystal Reportsによる独自のWebソリューション／デスクトップソリューションを提供するコンサルティンググループ「Common Ground Solutions」の創業者兼主任コンサルタント。ソフトウェアアプリケーションの開発経験は17年に及ぶ。米農務省からシステムオートメーション関連の賞をいくつか受賞。また、6桁の投資見返りを実現するソリューションを開発したことによりFortune 500企業から特別表彰を受ける。保険、会計、環境衛生、不動産、出版、広告、製造、金融、日用品、貿易振興など多様な業界に携わった経験を持ち、さまざまな形態で独自のトレーニングも行っている。
|
Kevin S. Goff
