現在作成中のアプリケーションはデータベースに MDB を使っているのですが、これを複数人でクライアントサーバとして利用できるようにDBに SQL Server にするかつ、設定ファイルのパラメタでDBを MDB か SQL Serverかを変更できるようにという要件が上がりました。
で、DataSetやTableAdapterはVisual Studioのデザイナを使って定義してます。
工数が限られてるので、既存の DataSet を使って SQL Server ・ MDB 共通で使えるように変更する方法にしました。
(MDB は OLEDB 経由でつないでます。そのため、同じDataSetを使うため SQL Server にもOLE DB経由となります。)
DetaSet で使ってる SQL は基本的に単純なものばかりなので、SQL Server でも使えるはずです。
(これが複雑な SQL を使ってると DataSet は別に分けるべきでしょう。)
また、各TableAdapterはMDBファイルを参照しています。
まず、デザイナの TableAdapter で今設定してる SQL で文法に問題がないか調べます。
あれば、修正します。
そして、TableAdapter で自動的で作れる Insert , Update , Delete 文を、自動生成しないようにします。
TetbleAdapter を右クリックして、｢構成｣から｢詳細オプション｣で｢INSERT、UPDATE、および DELETE ステートメントの生成｣チェックを外します。
理由は現 TableAdapter は MDB ファイルを接続先としているのですが、接続先のデータプロバイダによって、自動生成されるSQLは最適化されるようです。
そのせいで自動生成された SQL はフィールドの区切り文字がつけられてしまいます。
MDB だとテーブル、列名の区切り文字が ｢`｣(バッククォート)になってしまいます。
(最初、TableAdapter の Insert や Update , Delete の CommandText プロパティから区切り文字を除去したのですが、DataSet 保存時に CommandText が再構成されるらしく、元に戻ってしまうのでダメでした。)
こんな感じです。
/* TableAdapter で自動生成されるSQLの例 */INSERT INTO `ZipCode` (`ZipCode`, `PrefName`, `CityName`, `TownName`) VALUES (?, ?, ?, ?)
このテーブル・列名の区切り文字が本当に必要になるのは予約語や空白を含む名前をつけたときになるので、もし、そのような名前を使っていれば害がない名前に変更します。
ということで、各 TableAdaper の Partial クラスを ○○DataSet.vb につくり OleDbCommandBuilder から UpdateCommand , InsertCommandオブジェクトを生成します。
(もしくは、TableAdapterのクエリの追加で INSERT , UPDATE , DELETE を作ってもいいかもしれません。その場合は下記は不要です。)
OleDbCommandBuilder で動的に SQL を生成する場合は、そのテーブルの SELECT用 SQL が必要になります。で、SQL文を手で書くのはDBのフィールドに変更がると面倒なので、TableAdapte の Fill メソッドのSQLを使うことにします。
その Fill メソッドのSELECT文は ○○DataSet.Designer.vb の ○○TableAdapter.InitCommandCollection() メソッドをみると、CommandCollection配列の０番目にあるようなので、それを用いて OleDbCommandBuilder を使います。
(ただし、必ずしも CommandCollection(0) が Fill メソッドの SQL というわけでもないので注意が必要です。また、OLE DBは(パラメタ)プレス―ホルダーの順序が重要なので、CommandCollection(0) のSELECT文を使用しないと｢同時実行違反 : UpdateCommand によって、処理予定の 1 レコードのうち 0 件が処理されました。｣という例外が発生する場合があります)
下記が Datset の 各 TableAdapter 内で OleDbCommandBuilder を用い SQLを動的に生成するコードです。
とりあえずUpdateメソッドしか使ってません。DELETE,INSERT(INSERTはUpdateメソッドを使っても可能です)は、一旦DataSetをINSERT,DELETE自動生成モードにし ○○DataSet.Designer.vb で TableAdapterの各該当メソッド(Delete()やInsert())を ○○DataSet.vb の各 Partial クラスにコピペするといいと思います。ただ、引数にフィールド値が入るので、DBのフィールドを変更すると引数も変更しないといけないのが注意点です。
Partial Public Class ZipCodeTableAdapter
'''
''' Update,Insert用コマンド作成処理
'''
'''
Private Sub InitCommandText()
If Me.Adapter.UpdateCommand Is Nothing OrElse Me.Adapter.InsertCommand Is Nothing Then
Dim da As New OleDb.OleDbDataAdapter(Me.CommandCollection(0).CommandText, Me.Connection)
Dim cb As New OleDb.OleDbCommandBuilder(da)
Me.Adapter.UpdateCommand = cb.GetUpdateCommand()
Me.Adapter.InsertCommand = cb.GetInsertCommand()
End If
End Sub
'''
''' テーブル更新処理(引数の種類によってオーバーロードしたほうがいいかも)
'''
'''
'''
'''
Public Function Update(ByVal dataTable As RhythmSalesDataSet.ZipCodeDataTable) As Integer
Me.InitCommandText()
Return Me.Adapter.Update(dataTable)
End Function
End Class
まあこう考えると DataSet はやはり接続するDB毎に分けた方が理想ですね。
この構成でOKなのは結構限られた条件となりそうです。
ちなみに、テーブル・フィールド名の区切り文字はOleDbCommandBuilder.QuotePrefix 、OleDbCommandBuilder.QuoteSuffix プロパティで変更できます。
CommandBuilder関係はMSDN CommandBuilder でのコマンドの生成 (ADO.NET)参照。
また、第10章 データベースの更新の実行ではADO.Netでのデータ更新について下記の３つのアプローチについて解説されてます。
非常に参考になります。(.Net2.0より前なのでちと古いですが)
・コードを使ってDataAdapterオブジェクトを手作業で設定する方法
・ 実行時にCommandBuilderを使用する方法
・ デザイン時にデータアダプタ構成ウィザードを使用する方法
