人気ブログランキング |
<   2009年 03月 ( 23 )   > この月の画像一覧
レンタルサーバ アイルでのMySQL4.0→5.1へのバージョンアップ
アイルというレンタルサーバ(iCLUSTAプラン)の MySQL のバージョンアップ作業を依頼されました。
現状は MySQL4.0 を使ってるっぽいんですが、 MySQL5 に移行するようアイル側から通知があったそうです。
バージョン4は夏ごろに利用不可になるとか。。(そもそもパッチ提供がとっくに終わっている4.0をここまで引っ張るのも問題ですが...)

で、アイルのWEBサイトでDBバージョンアップについての情報を探してみましたが、ほとんど見当たりません。
仕方ないので、問い合わせることに。。


Q1:バージョンアップ時に、既存のテーブル構造、データを移行するようなシステムはあるのか?
A: ない。DBManagerから、ダンプ・リストアをユーザ側でやって。

Q2:ストレージエンジンは選べる?
A: MyISAM,InnoDBが選べる。

Q3:テーブルの文字コードは選べる?
A :MySQL5側のデフォルト文字コードは ujis。文字コードの変更は不可。

Q4:DBへの接続情報は変わる? 変わる場合は前もってわかるの?
A :データベースサーバ名、ポート番号が変更。DB名は変わらない。バージョンアップ後に新しいDB名、ポート名を表示するよ。

なんというかアイル結構不親切だなと思った今日この頃です。
まず、DBのデータ移行くらいはしてほしかったですね。
また、文字コードが選べないのも問題です。特に、アイルのシステムはWEBサーバ,DBともに EUC を強要するんですが、配置してるHTMLやWEBアプリケーションはすべて UTF-8 で書いてます。
(よって、MySQL4.0 の時は無理やり UTF-8 のデータを放り込んでます。アイル付属のDB管理ツールDBManagerから見たら文字化けしまくりです。)
(でも、よく考えたら、MySQL4.1移行は SET NAMES が使えるので文字コード選べなくても問題ないですね。。。。)
それから、新しい接続情報を前もって教えてくれないってどゆことですか。これじゃ、バージョンアップしてから接続情報確認して、phpのソース書き換えてしないといけなくなり、その間データ移行も必要で、結局WEBアプリ停止時間が長くなってしまいます。

まあ、愚痴ばかり言ってもしかたないので、実作業のメモ。
まず、DBManager → databaseのバックアップ から既存のデータをダンプしてローカルに保存します。(このとき、ちゃんとUTF-8の情報も文字化けせずダンプできました。これが、テーブル毎で文字コード異なってるとおおごとなんでしょうね。)

この後、DBを使ってるページにメンテナンス中であることを表示させます。

次に、iCLUSTAのPlanManager → ユーティリティ → データベース設定 で MySQL のバージョンアップを実行します。
これで、データは全部消えちゃいます。

この時にパスワードの変更が要求されたので、前と同じパスワードを設定します。
それで、新DBサーバ名と、ポート番号が表示されるので忘れずにメモします。

ダンプしておいたファイルを DBManager → databaseのリストア から復元さしたのですが、案の定データが文字化けしてしまいました。

ということで一旦文字化けしたテーブル消し、phpMyAdmin を使用することとしました。(これも config.inc.php でDB接続先を新しいのに変更する必要あります)

phpMyAdminをutf-8でログインし、インポートからダンプファイルを選択し、文字セットを utf8 にします。
これでインポートすると、ujisのテーブルに、ujisに変換してインポートしてくれます。

最後はDBにつなぐPHPファイルでDB接続情報の変更です。今回、DBにつなぐ部分はCakePHPだけだったので、database.php を修正するだけで済みました。
下記のような感じです。
var $default = array(
'driver' => 'mysql',
'persistent' => false,
'host' => '新サーバ名:ポート番号',
'login' => 'ログイン名は変わらず',
'password' => 'パスワード',
'database' => 'DB名も変わらず',
'encoding' => 'utf8', //これ重要。WEBアプリ側はUTF8の文字を要求。
'prefix' => '',
);

重要なのは 'encoding' => 'utf8' でしょうかね。
これを指定しないと、MySQLが勝手に考えた別の文字コードで返してくるので、文字化けしてしまいます。(HTML,PHPはUTF-8で作ってるので。。)

これで、なんとか無事に MySQL のバージョンアップが完了しました。
by jehoshaphat | 2009-03-30 00:37 | Webがらみ
(.Net,SQL)JET(MDB)でINSERT直後のオートナンバー型IDを取得する
MDB でオートナンバー型の列を用意し、ADO.Net から TableAdapter を使って新規行を挿入します。
このとき、TableAdapter.Update() 前は DataTable でオートナンバーに対応する列の値は -1 です。
しかし、TableAdapter.Update() 後も値は -1 のままで、DB側でどの新しい値が振られたのかプログラム側はわかりません。

このDB側で振られた新しい値を.Net側から取得する方法です。

参考にしたのは、MSDN:@@IDENTITY クライシスを管理するです。
上記MSDNの記事では、JETのほかに、SQLサーバで取得する方法も取り上げられています。

JETでは新規IDの取得にSELECT @@IDENTITYが使えるようなので、これを用います。
このクエリを使うタイミングは、対象 TableAdpter の RowUpdated イベント時です。

DataSet で Table1TableAdapter が作成されてるものとします。

/// <summary>メンバ変数 TableAdapter</summary>
private DataSetTableAdapters.Table1TableAdapter tblAdp = new DataSetTableAdapters.Table1TableAdapter();
 
private void frmMain_Load(object sender, EventArgs e)
{
//Diaryテーブルのアダプターのイベントハンドラ定義
tblAdp.Adapter.RowUpdated += new OleDbRowUpdatedEventHandler(Adapter_RowUpdated);
}
 
private void button1_Click(object sender, EventArgs e)
{

DataSet.Table1DiaryDataTable tbl = new DataSet.Table1DiaryDataTable();
DataRow row = tbl.NewRow();
row["field1"] = "test";
tbl.Rows.Add(row);
//DBに保存(このとき新規行のID列の値は -1 )
tblAdp.Update(tbl);
}
 
/// <summary>
/// [イベントハンドラ]テーブルのRowUpdateイベント( tblAdp.Update(tbl) 直後に走る)
/// 新規挿入した行のIDを取得する
/// </summary>
void Adapter_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
{
if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
{
OleDbDataAdapter adp = (OleDbDataAdapter)sender ;
//DBが割り当てたIDの値を取得するクエリ
OleDbCommand cmdNewID = new OleDbCommand("SELECT @@IDENTITY", adp.SelectCommand.Connection);
//取得した新規IDをDataTableのID列に入れる。
e.Row["ID"] = (int)cmdNewID.ExecuteScalar();
}
}

これで、DataTable の ID 列にDB側で振られた新しい値が入ってるはずです。
by jehoshaphat | 2009-03-22 23:35 | .Net開発
(.Net)MemoryStreamのデータをGZipStreamで圧縮するときの注意点...
MDBにデータを格納するときに、圧縮して格納しようと思い、GZipStream で圧縮をかけたのですが、その時にハマったことです。
(MDBで圧縮したデータを保存する列はバイナリ型にしてます。また、DBに圧縮したデータを入れる方法は、ITpro .NETの関数を使用してSQL Server 2005にデータを圧縮格納を参考にしてます。)

とりあえず、.Netについてる GZipStream での圧縮と解凍の方法が、@IT GZIP形式でファイルを圧縮/解凍するには?に載ってたので、これを参考にして下記のようなコードにしました。
上記@ITの例ではファイルに対しての圧縮解凍操作ですが、今回はDBに入れるデータに対してなので、FileStream の代わりに MemoryStream を使ってます。(最近 C# 本格的に始めました。ということで、コードも C# です。)

//圧縮を行う
private Byte[] CompressBytes(Byte[] bytUnComp)
{
int num;
byte[] buf = new byte[1024]; // 1Kbytesずつ処理する
// 入力ストリーム
MemoryStream inStream = new MemoryStream(bytUnComp);
// 出力ストリーム
MemoryStream outStream = new MemoryStream();
// 圧縮ストリーム
GZipStream compStream = new GZipStream(outStream, CompressionMode.Compress);
 
while ((num = inStream.Read(buf, 0, buf.Length)) > 0)
{
compStream.Write(buf, 0, num);
}
return outStream.ToArray();
}
 
//解凍を行う
public static Byte[] UnCompressBytes(byte[] bytComp)
{
int num;
byte[] buf = new byte[1024]; // 1Kbytesずつ処理する
// 入力ストリーム
MemoryStream inStream = new MemoryStream(bytComp);
// 解凍ストリーム
GZipStream decompStream = new GZipStream(inStream, CompressionMode.Decompress);
// 出力ストリーム
MemoryStream outStream = new MemoryStream();
 
while ((num = decompStream.Read(buf, 0, buf.Length)) > 0)
{
outStream.Write(buf, 0, num);
}
return outStream.ToArray();
}

このメソッドを使って、少ないデータ(数バイト)で、圧縮をかけて解凍しようとすると、解凍が正常にできませんでした。
num = decompStream.Read(buf, 0, buf.Length) のところで num = 0 となってしまい、 outStream.Write() が走らないのです。
いろいろ調べてみると、ここここに解決法が載ってました。

どうやら、圧縮時に、outStream.ToArray() を動かす前に、GZipStream が close されてないといけないようです。

ということで、下記のように書き直したらうまくいきました。

//圧縮を行う
private Byte[] CompressBytes(Byte[] bytUnComp)
{
int num;
byte[] buf = new byte[1024]; // 1Kbytesずつ処理する
// 入力ストリーム
MemoryStream inStream = new MemoryStream(bytUnComp);
// 出力ストリーム
MemoryStream outStream = new MemoryStream();
// 圧縮ストリーム
GZipStream compStream = new GZipStream(outStream, CompressionMode.Compress);
 
while ((num = inStream.Read(buf, 0, buf.Length)) > 0)

{
compStream.Write(buf, 0, num);
}
compStream.Close();
byte[] output = outStream.ToArray();
inStream.Close();
outStream.Close();
 
return output;
}
 
//解凍を行う
public static Byte[] UnCompressBytes(byte[] bytComp)
{
int num;
byte[] buf = new byte[1024]; // 1Kbytesずつ処理する
// 入力ストリーム
MemoryStream inStream = new MemoryStream(bytComp);
// 解凍ストリーム
GZipStream decompStream = new GZipStream(inStream, CompressionMode.Decompress);
// 出力ストリーム
MemoryStream outStream = new MemoryStream();
 
while ((num = decompStream.Read(buf, 0, buf.Length)) > 0)
{
outStream.Write(buf, 0, num);
}
 
byte[] output = outStream.ToArray();
inStream.Close();
outStream.Close();
 
return output;
}

by jehoshaphat | 2009-03-21 18:06 | .Net開発
(VisualStudio)VisualStudio2008(2005)で提供されているアイコン

アプリケーションを作ってて悩むのが、アイコンです。
特に汎用的なボタンやツールバー(切り取り、コピー、貼り付け、保存とか)に表示するアイコンはまとめて提供されててほしいですよね。

VisualStudio2008にはそんな汎用的アイコンが提供されてました。
下記ZIPファイルに数多くのアイコンイメージが入ってます。
\Common7\VS2008ImageLibrary\1041\VS2008ImageLibrary.zip

これで、フリーなアイコン探したり、自分で書いたりしなくてすみますね。

VS2005にも同様のアイコン入ったZIPファイルがあるっぽいです。

こんな感じです。
e0091163_19172260.jpg

by jehoshaphat | 2009-03-20 19:18 | .Net開発
(.Net)DataGridView でソートをかけると DataGridViewRow の Tag プロパティが null になる

DataGridView.Sort メソッドで DataGridView 内部の DataGridViewRow に対してソートをかけると、DataGridViewRow.Tagプロパティが null になってしまうようです。
なので、下記のようなコードを書くと Console.WriteLine メソッドのところで例外が発生します。

'DataTable
Private mTable1Dtbl As New Database1DataSet.table1DataTable()
'TableAdapter
Private mTable1Adp As New Database1DataSetTableAdapters.table1TableAdapter()

'DataTableにDBの情報を取得し、DataGridViewにバインドさせる
mTable1Adp.Fill(mTable1Dtbl)
Me.DataGridView1.DataSource = mTable1Dtbl

'DataGridViewRow.Tag プロパティに値代入
Dim j As Integer = 0
For Each row As DataGridViewRow In DataGridView1.Rows
row.Tag = j
j += 1
Next
'ソート
DataGridView1.Sort(DataGridView1.Columns("name2"), System.ComponentModel.ListSortDirection.Descending)

Console.WriteLine("")
For Each row As DataGridViewRow In DataGridView1.Rows
'ここで例外が発生する!!!!!!!!
Console.WriteLine(row.Tag.ToString())
Next

もしかしたら、DataGridView.Sort メソッドは内部的に新しい DataGridViewRow を作ってるのかもしれません。

DataGridView にバインドするオブジェクトを DataView にして、 DataView 上でソートをかけてみましたが、これも結果は同じでした。

まあ、暇があれば、MSから .Net Framework のソース落としてきて調べてみようと思います。
この現象は、知らないとバグの元になっちゃいますね。

こう考えると、Control.Tag はあんまり使わない方がいいんでしょうかね。
(複数人で開発してると、ちゃんと取り決めを設けずに好き勝手に Tag にデータ入れるとわけわからなくなる場合もありますしね。)
by jehoshaphat | 2009-03-15 23:27 | .Net開発
(SQL)Access(JET)で FULL OUTER JOIN を行う

日別の集計データを取る SQL の作成を行うことになったのですが、データの種別毎に SQL を発行するとレスポンス的にもまずいかなと思い、一つのクエリで何とか取得出るようにしてみました。

たとえば下記のようなテーブルがあるとします。

Sales テーブル(主キー(CustomerID,LineNo)と集計に必要な列以外は省略)
┌────────────────────────┐
│CustomerID LineNo SalesDate Quantity Money │
│22 4 20090301 1 105 │
│22 1 20090302 2 2520 │
│21 1 20090302 5 10050 │
│22 2 20090310 1 1050 │
│22 3 20090310 1 1260 │
│23 1 20090310 1 14700 │
│16 1 20090310 1 10500 │
│9 1 20090310 1 105 │
│11 1 20090310 2 1050 │
│11 2 20090310 1 105 │
└────────────────────────┘

UseHistory テーブル(主キー(CustomerID,LineNo)と集計に必要な列以外は省略)
┌─────────────────┐
│CustomerID LineNo UseDate Sex │
│21 2 20090301 0 │
│10 2 20090310 1 │
│10 1 20090310 1 │
│22 1 20090310 2 │
│8 1 20090310 1 │
│16 1 20090310 2 │
│23 1 20090310 0 │
└─────────────────┘
※UseDate,SalesDateは文字列 男性:Sex=1 女性:Sex=2 その他:Sex=0,null


これで求めたいのは、日別の販売個数合計(Sales.Quantity)、日別販売額合計(Sales.Money)、日別利用男性合計、日別利用女性合計、日別利用その他合計(UseHistory.Sex)です。
上記のテーブルデータから言うと、出力イメージはちょうど下記のようになります。

┌───────────────────────────────────────┐
│日付 販売個数合計 販売額合計 男性利用合計 女性利用合計 その他利用合計│
│20090301 1 105 1 │
│20090302 7 12,570 │
│20090310 8 28,770 3 2 1 │
└───────────────────────────────────────┘

つまり、Sales,UseHistroy の両テーブルに存在するすべてのレコードを取得し、集計後(合計)に日付を条件として結合したいのです。

このような結合条件を指定し、両方のテーブルに存在するものを組み合わせるのは、たしか FULL OUTER JOIN (完全外部結合)とか使うんだったっけと思いながら調べると、JET では FULL OUTER JOIN サポートされてないんですね。。

でも、UNION と LEFT , RIGHT OUTER JOIN を組み合わせることで、完全外部結合できるようです。

参考にさせてもらったのは、Microsoft Accessで完全外部結合をするです。
2通りのやり方が、紹介されてますが、自分はその2の方が理解しやすかったのでそっちを使ってみました。(ちなみに、このやり方はかなりマイナーだとここの筆者が書いてました。)

要はまず、UNION を使って、結合条件となる値を重複なしで取得し、それに対して、各テーブルに左外部結合をかけていくというやり方みたいです。
UNION 句はあんまり使ったことがないのですが、こういうときに使えるんですね。

ということで、ほしいデータを出すクエリはこんな感じになりました。

SELECT DateAll.UseSalesDate AS 日付 ,
SalesData.SalesNum AS 販売個数合計 ,
SalesData.SalesMoney AS 販売額合計 ,
UseDataMale.UseMaleNum AS 男性利用合計 ,
UseDataWoman.UseWomanNum AS 女性利用合計 ,
UseDataOther.UseOtherNum AS その他利用合計
FROM ((((
/*この副問い合わせで、Sales,UseHistory両テーブルの重複しない日付を取得する(この値が結合条件となる)*/
( SELECT SalesDate AS UseSalesDate
FROM Sales

UNION

SELECT UseDate AS UseSalesDate
FROM UseHistory
) AS DateAll
LEFT OUTER JOIN
/*販売個数合計、販売金額合計を求める*/
( SELECT Sales.SalesDate ,
SUM(Sales.Quantity) AS SalesNum,
SUM(Sales.Money) AS SalesMoney
FROM Sales
GROUP BY SalesDate
) AS SalesData
ON DateAll.UseSalesDate = SalesData.SalesDate)
LEFT OUTER JOIN
/*男性利用合計を求める*/
( SELECT COUNT(*) AS UseMaleNum ,
UseDate
FROM UseHistory
WHERE Sex = 1
GROUP BY UseDate
) AS UseDataMale
ON DateAll.UseSalesDate = UseDataMale.UseDate)
LEFT OUTER JOIN
/*女性利用合計を求める*/
( SELECT COUNT(*) AS UseWomanNum ,
UseDate
FROM UseHistory
WHERE Sex = 2
GROUP BY UseDate
) AS UseDataWoman
ON DateAll.UseSalesDate = UseDataWoman.UseDate)
LEFT OUTER JOIN
/*その他利用合計を求める*/
( SELECT COUNT(*) AS UseOtherNum ,
UseDate
FROM UseHistory
WHERE Sex = 0
OR Sex IS NULL
GROUP BY UseDate
) AS UseDataOther
ON DateAll.UseSalesDate = UseDataOther.UseDate)
ORDER BY DateAll.UseSalesDate

いずれにせよ、日付は1つの列にまとめたかったので、副問い合わせ内で UNION することは必要だったようです。
なので、今回の要件を満たす SQL は SQL Server で FULL OUTER JOIN が使えたとしても、LEFT OUTER JOIN と書き方はたいして変わりそうにないようです。

これが、結合条件の列を別々の列として出すのなら、副問い合わせの UNION 使わず、ただ単に FULL OUTER JOIN すればいいだけなんですが。。
ちょうど下記の感じです。( SQL Server で FULL OUTER JOIN 使った場合。日付列はテーブル毎に出力されるが…)

SELECT SalesDate ,/*Salesテーブルの日付と,UseHistoryテーブルの日付は別々にでてしまう*/
UseDate ,
SalesNum ,
SalesMoney ,
UseMaleNum
FROM
( SELECT Sales.SalesDate ,
SUM(Sales.Quantity) AS SalesNum,
SUM(Sales.Money) AS SalesMoney
FROM Sales
GROUP BY SalesDate
) AS SalesData
FULL OUTER JOIN
( SELECT COUNT(*) AS UseMaleNum ,
UseDate
FROM UseHistory
WHERE Sex = 1
GROUP BY UseDate
) AS UseDataMale
ON UseDataMale.UseDate = SalesData.SalesDate
/*女性、その他はめんどくさいので省略…*/

by jehoshaphat | 2009-03-14 04:45 | SQL
(.Net)ActiveReports Datail で最終行の Line を表示
(.Net)ActiveReports でグループヘッダセクションをページ毎に出力させるの続きになります。
この記事でやったように、GroupHeader,GroupFooter を PageHeader,PageFooter 代わりに使っており、5行おきに横方向のLineを引くという仕様にしていると、最終行が5行毎ではないときに、横方向の Line が表示できません。

こんな感じです。
e0091163_18374856.jpg


ということで最終行の Line を引くにはどうしたらいいかいろいろ悩みましたが、結局 DataSourceChanged イベントが起こったときに、データソースの行件数をメンバ変数に持ち、Datail セクションで、最終行のときだけ最終行用 Line を表示するということにしました。
コードはこんな感じです。
Imports DataDynamics.ActiveReports 
Imports DataDynamics.ActiveReports.Document
 
Public Class ActiveReportsTest
' 件数カウンタ
Private m_intRowNumber As Integer
'データの件数
Private m_intNumData As Integer
 
''' <summary>
''' [イベントハンドラ] 詳細セクションのフォーマットイベント
''' </summary>
Private Sub Detail_Format(ByVal sender As Object, ByVal e As System.EventArgs) Handles Detail.Format
 
m_intRowNumber = m_intRowNumber + 1
 
'最終行ならLineを引く
If m_intRowNumber = m_intNumData Then
Me.PageLastLine.Visible = True
End If
 
If m_intRowNumber < 45 Then
' 件数が45件に満たない場合、改ページは行いません。
Me.Detail.NewPage = NewPage.None
Else
' 45件出力した後、改ページを行い、カウンタをリセットします。
Me.Detail.NewPage = NewPage.After
m_intRowNumber = 0
End If
 
'5行おきにLine
If m_intRowNumber Mod 5 = 0 Then
Me.SeparatorLineFive.Visible = True
Else
Me.SeparatorLineFive.Visible = False
End If
 
End Sub
 
''' <summary>
''' [イベントハンドラ]データソースが変更されたとき
''' </summary>
Private Sub UseReport_DataSourceChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.DataSourceChanged
 
If TypeOf Me.DataSource Is DataTable Then
'行数をメンバ変数に持つ
m_intNumData = DirectCast(Me.DataSource, DataTable).Rows.Count
End If
 
End Sub
End Class

ただ、この例だと DataSource に渡すオブジェクトは必ず DataTable でないといけないです。その他のオブジェクトを渡す可能性があるのなら、それ毎の処理が必要になります。

結果はこうなります。
e0091163_1838546.jpg



追記:
最初、最終行の横線が表示されてないとき、GourpFooter の PrintAtBottom プロパティが True でした。
このプロパティを False にすると、GroupFototer 上の横線で、要件が達成できます。
ということに気付き、結局上記の作業は無駄に。。。
by jehoshaphat | 2009-03-13 18:38 | .Net開発
(.Net)DataTable でソートをする(DataTable.Select)

DataTable自体にはソートをしてくれる機能がありませんが、DataTable.Select を応用することでソート処理ができます。
DataTable.Select() メソッドが DataTable の中でフィルタをかけるのに使えるということは(VB.Net)DataTableで特定の列の最大値を含む行を取得したい。(DataTable.Select)で紹介しました。


今回は純粋にソートだけを行う方法です。
問題は、DataTable.Select() メソッドは引数で指定された条件にあった DataRow 配列を返すという点です。DataTable 自体の内部のソートはしてくれません。

ということで、返り値の DataRow 配列を元に、新しい DataTable を作成するという方法をとりました。
コードはこんな感じです。
'ソート対象のテーブル作成
Dim dtbl As New DataTable()
Dim col(1) As DataColumn
col(0) = New DataColumn("ID", Type.GetType("System.Int32"))
col(1) = New DataColumn("Age", Type.GetType("System.Int32"))
dtbl.Columns.AddRange(col)
 
'データ作成
For i As Integer = 0 To 5
Dim row As DataRow = dtbl.NewRow()
row("ID") = i
dtbl.Rows.Add(row)
Next
dtbl.Rows(0)("Age") = 30
dtbl.Rows(1)("Age") = 60
dtbl.Rows(2)("Age") = 40
dtbl.Rows(3)("Age") = 50
dtbl.Rows(4)("Age") = 20
dtbl.Rows(5)("Age") = 60
 
'ソート前確認
For Each row As DataRow In dtbl.Rows
Console.WriteLine("ID:" & row("ID") & " Age:" & row("Age"))
Next
Console.WriteLine("")
 
'DataTable.Select()を使いソート(第二引数にソート条件を書く)
Dim rows As DataRow() = dtbl.Select(Nothing, "Age DESC , ID ASC").Clone()
 
'ソート後の DataTable を用意
Dim dtblSrt As New DataTable()
'ソート前テーブルの情報をクローン
dtblSrt = dtbl.Clone()
 
'ソートされてる DataRow 配列をソート後の DataTable に追加
For Each row As DataRow In rows
dtblSrt.ImportRow(row)
Next
 
'ソート後確認
For Each row As DataRow In dtblSrt.Rows
Console.WriteLine("ID:" & row("ID") & " Age:" & row("Age"))
Next

上記例だとソート前にコンソール出力した結果はこうなります。
ID:0 Age:30
ID:1 Age:60
ID:2 Age:40
ID:3 Age:50
ID:4 Age:20
ID:5 Age:60

そして、ソート後にコンソール出力するとこうなります。
ID:1 Age:60
ID:5 Age:60
ID:3 Age:50
ID:2 Age:40
ID:0 Age:30
ID:4 Age:20

しゃんと指定したとおりにソートされてますね。

ただし、この方法だと新しい DataTable を作ったり、ループを使ってるため大量のデータのときに処理速度の問題や大量メモリ消費の問題がでてきます。
もっといい方法はないですかねぇ。。

ちなみに、@IT:[ADO.NET]データテーブル(DataTable)内のレコードをソートするには?では、DataRowView.Sort メソッドを使った方法が載せられてました。ソートの方法に DataRowView.Sort メソッドを使っているという点以外は、今回紹介した方法と似たようなことになってます。

また、DataTable.Select でのソートは値に NULL があった場合、Oracle での ORDER BY と結果が異なることがあるようなので、注意が必要です。詳しくは、DataTableおよびDataViewの注意点で取り上げられてました。
by jehoshaphat | 2009-03-12 00:43 | .Net開発
(SQL)SELECT句に副問い合わせを含める
SELECT句の副問い合わせはJOIN内の副問い合わせとは違い、外のフィールドが使えるので便利です。

たとえば、下記のように Customer テーブルと利用履歴を残した UseHistroy テーブルがあるとします。

Customer テーブル
┏━━━━━━━━━━━━━━━━━┓
┃CustomerID CustomerName Birthday┃
┃1 安部礼司 19711010┃
┃2 倉橋優 19810831┃
┃3 飯野平太 19800210┃
┃4 刈谷勇 19710401┃
┗━━━━━━━━━━━━━━━━━┛

UseHistory テーブル
┏━━━━━━━━━━━━━━┓
┃CustomerID LineNo UseDate ┃
┃1 1 20090101┃
┃1 2 20090220┃
┃1 3 20090310┃
┃2 1 20090225┃
┃2 2 20090310┃
┃3 1 20090310┃
┃4 1 20090311┃
┗━━━━━━━━━━━━━━┛
(Birthday,UseDateは文字列型の列)

上記のテーブルデータを使い、20090301 に利用した人のデータと、その人が 20090301 以前の直近の利用日(前回利用日)を抽出したいSQLはこうなります。
SELECT Customer.CustomerID   ,
Customer.CustomerName ,
/*年齢を出す(詳しくはhttp://jehupc.exblog.jp/9075644/)*/
INT((INT(Format(DATE(),'yyyymmdd')) - INT(Customer.Birthday) )/ 10000) AS Age ,
/*前回利用日を出す副問い合わせ*/
( SELECT MAX(UseDate)
FROM UseHistory AS UseHistory2
WHERE UseHistory2.UseDate < '20090310'
/*副問い合わせの外のフィールドの値を条件に用いる*/
AND UseHistory2.CustomerID = Customer.CustomerID
) AS LastUseDate
FROM UseHistory
INNER JOIN Customer
ON UseHistory.CustomerID = Customer.CustomerID
WHERE UseHistory.UseDate = '20090310'



結果はこうなります。

┏━━━━━━━━━━━━━━━━━━━━━━┓
┃CustomerID CustomerName Age LastUseDate ┃
┃1 安部礼司 37 20090220 ┃
┃2 倉橋優 27 20090225 ┃
┃3 飯野平太 29 ┃
┗━━━━━━━━━━━━━━━━━━━━━━┛

実は最初、上記SQL内の副問い合わせは下記のSQLのようにしてました。
(SELECT   TOP 1 UseDate
FROM UseHistory AS UseHistory2
WHERE UseHistory2.UseDate < '20090310'
AND UseHistory2.CustomerID = Customer.CustomerID
ORDER BY UseDate DESC ) AS LastUseDate

しかし、この方法だと一旦 ORDER BY で並び変えるため、大量のデータを扱う場合にレスポンスに問題が出るという点を先輩から指摘されました。言われてみれば確かにソートは処理速度のリスクが高いですね。(なんで気付かなかったんだろう…)
ということで、MAX 関数を利用することにしました。
by jehoshaphat | 2009-03-11 23:35 | SQL
(.Net)DictionaryEntryを使ってコンボボックスに表示文字列と処理用の値をセットする
ComboBox.Items.Add() や ComboBox.Items.AddRange() を使うとコンボボックスのリストに出てくる項目を追加することができますが、1つ(表示用)しか追加できません。なので、リストに表示するデータとそれに対応した処理用の値とを入れることができません。

で、ComboBox.DataSource プロパティを用い DataSet や DataTable 等をバインドさせて、ComboBox.DisplayMember プロパティと ComboBox.ValueMember プロパティで表示用の列と処理用の値の列を指定できることは知っていました。

しかし、簡単なデータをコンボボックスにセットするのに、いちいち DataTable を作成し、列を定義し…とするのは面倒だなと思っていたら、.Net にはキーと値を持てる便利な構造体があったんですね。

それが、System.Collections.DictionaryEntry 構造体です。
これは "Key" と "Value" という Object 型のプロパティを持っているので、Key プロパティに表示用データ、Value プロパティに値を入れ、DictionaryEntry を ArrayList でコレクションにすることで、手軽にコンボボックスに表示データと値をセットできそうです。

こんな感じになります。
'cmbTest は ComboBox オブジェクト
Dim lstCmbItems As New ArrayList()
lstCmbItems.Add(New DictionaryEntry("データ1", 0))
lstCmbItems.Add(New DictionaryEntry("データ2", 1))
lstCmbItems.Add(New DictionaryEntry("データ3", 2))
cmbTest.DataSource = lstCmbItems
cmbTest.DisplayMember = "Key"
cmbTest.ValueMember = "Value"

by jehoshaphat | 2009-03-11 22:02 | .Net開発