DB接続
ken
SQLServer2008Ex
VB2008Exを使用してます。
ADO.NETではかつてのADOのにあったrs.open rs.addnew rs.edit rs.update rs.close
のような、SQLを使わない方法でテーブルに対しての操作は不可能なのでしょうか。
可能なのであれば接続文字列等一通り教えてください。
よろしくお願いします。
Mio Yoshida
>ADO.NETではかつてのADOのにあったrs.open rs.addnew rs.edit rs.update rs.close
>のような、SQLを使わない方法でテーブルに対しての操作は不可能なのでしょうか。
DataSet や DataTable を使用すればできないこともないです。
addnew -> DataTableクラスのNewRowメソッド、DataTableクラスのImportRowメソッド
delete -> DataRowCollection のRemoveメソッド
edit -> 編集したい対象行のRowとColumnを選択し値を代入する、
また、特定行更新中、途中キャンセルしたい処理を行いたい場合は、
BeginEdit、EndEdit、CancelEdit を使用する
ただ、SQL Server から取得したデータをDataSetやDataTableに格納するには
・System.Data.Odbc、System.Data.OleDb、System.Data.SqlClient のいずれ１つのデータアクセス方法を選択
・SQL構文やストアドプロシージャを「コマンド」に設定しデータベースにアクセスする
・DataAdapterを使ってDataSetやDataTableにFillする
といった手順が必要になるので、結局はSQLやストアドを使用する処理を記述しなければなりません。
なので、ADO.NETをADO時代の時と同じように考えないほうが良いです。
詳しいことはMSDNにも掲載されていますので、ご確認ください。
DataRowメソッド：
http://msdn.microsoft.com/ja-jp/library/system.data.datarow_methods(VS.80).aspx
DataTableメソッド：
http://msdn.microsoft.com/ja-jp/library/system.data.datatable_methods(VS.80).aspx
また、MSDNの内容が難しいと感じられる場合は、10行シリーズにわかりやすい
サンプルがありますので、参考にしてみては如何でしょうか？
http://msdn.microsoft.com/ja-jp/events/dd283146.aspx
以上ご確認ください。
それから、逆に質問ですが、そもそもSQL構文を使用したくない理由はなんでしょうか？
もし明確な理由があるなら補足してください。宜しくお願いします。
--
けろ-みお（Mio Yoshida）
Microsoft MVP for ASP/ASP.NET - Jan.2008 - Dec.2010
ken
> といった手順が必要になるので、結局はSQLやストアドを使用する処理を記述しなければなりません。
> なので、ADO.NETをADO時代の時と同じように考えないほうが良いです。
>
やっぱりだいぶ違うんですね。
>
> 以上ご確認ください。
> それから、逆に質問ですが、そもそもSQL構文を使用したくない理由はなんでしょうか？
> もし明確な理由があるなら補足してください。宜しくお願いします。
>
１レコードのフィールド数が多いんでテキストデータを構造体に放り込んで
ループで回せば簡単で、SQLも長文で分かりづらくなる事もないかと思いまして。
Mio Yoshida
> １レコードのフィールド数が多いんでテキストデータを構造体に放り込んで
> ループで回せば簡単で、SQLも長文で分かりづらくなる事もないかと思いまして。
なるほど。お気持ちはよくわかります。
ADO.NETになると、下記の方法を使えば、列数が多くても簡単にSQL構文を組み立てられます。
手順１：構造体をエンティティという概念のクラスにする
（構造体でもOKなんですが、できればクラスで）
Public Class RecordEntity
Private _Field1 As Integer
Private _Field2 As String
Private _Field3 As Nullable(Of Decimal)
Public Property Field1 As Integer
Get
Return Me._Field1
End Get
Set ( ByVal value As Integer )
Me._Field1 = value
End Set
End Property
Public Property Field2 As String
Get
Return Me._Field2
End Get
Set ( ByVal value As String)
Me._Field2 = value
End Set
End Property
Public Property Field3 As Nullable(Of Decimal)
Get
Return Me._Field3
End Get
Set ( ByVal value As Nullable(Of Decimal))
Me._Field3 = value
End Set
End Property
End Class
手順２：作成したエンティティに対しデータを格納する
（格納方法は構造体の概念とだいたい同じです）
Dim record As New RecordEntity()
With record ' VB.NET ではWithは使わない方が良いんですが、説明のため使用しています
.Field1 = 100
.Field2 = "Hoge Hoge"
.Field3 = 10.13
End With
手順３：
Reflectionという概念を使い、エンティティから各プロパティ名とプロパティ値をループしながら１つずつ取得。
その中でSQLを組み立てる
' クラスの外側
Imports System.Reflection
Imports System.Text
............
............
............
' メソッド
Dim sql As New StringBuilder()
Dim recordType As Type = GetType(RecordEntity)
Dim recordPropertyInfo As PropertyInfo() = recordType.GetProperties((BindingFlags.Public Or BindingFlags.Instance))
Dim fieldSQL As New List(Of String)
sql.AppendLine("UPDATE TABLE1 SET ")
For Each recField As PropertyInfo In recordPropertyInfo
Select Case recField.PropertyType
Case GetType(Nullable(Of Decimal))
Dim fieldValue As Nullable(Of Decimal) = recField.GetValue(record, Nothing)
fieldSQL.Add(String.Format("{0} = {1} ", recField.Name.ToString(), IIf(fieldValue.HasValue, fieldValue.Value,
DBNull.Value)))
Case GetType(String)
fieldSQL.Add(String.Format("{0} = '{1}' ", recField.Name.ToString(), recField.GetValue(record, Nothing)))
Case Else
fieldSQL.Add(String.Format("{0} = {1} ", recField.Name.ToString(), recField.GetValue(record, Nothing)))
End Select
Next
sql.Append(String.Join(",", fieldSQL.ToArray()))
sql.AppendLine("WHERE field0 = @field0")
手順４：後は生成したSQLを実行するだけ
' クラスの外側
Imports System.Transactions
Imports System.Data.SqlClient
............
............
............
' メソッドの中
Dim tranOption As New TransactionOptions()
tranOption.IsolationLevel = Transactions.IsolationLevel.ReadCommitted
tranOption.Timeout = New TimeSpan(0, 1, 0)
Using tran As New TransactionScope(TransactionScopeOption.RequiresNew, tranOption)
Using conn As New SqlConnection("接続文字列。通常はconfigファイルから取得するコードを書く")
conn.Open()
' SQL実行
Using command As New SqlCommand(String.Empty, conn)
command.CommandText = sql.ToString()
Dim keyParam As New SqlParameter("@field0", SqlDbType.Int)
keyParam.Value = 1234
command.Parameters.Add(keyParam)
' 更新実行
command.ExecuteNonQuery()
End Using
conn.Close()
End Using
' コミット
tran.Complete()
End Using
こういう風にすれば別に列数が多いSQLを組み立て、
SQLコマンド実行しても苦にならないのではないでしょうか。
ただ、ご存じの通り、SQL文字列を組み立てながらデーターベースを操作すると
インジェクションの問題があるので、上記のようなプログラムの書き方は
正直お勧めできないというのが本音です。
データベースで処理するものはすべてSQL Serverにストアドプロシージャにて定義し、
手順４のコードにある「CommandTextプロパティ」にストアドプロシージャー名を
セットする実装が理想です。
また、ストアドプロシージャーに対するパラメータの渡し方も上記サンプルコードと
まったく同じやり方で渡すことができます。
参考になるかわかりませんが、お役立て下さい。
＃ニュースグループは長文禁止なのは承知しておりますが、
サンプルコード掲載のためお許しください。
--
けろ-みお（Mio Yoshida）
Microsoft MVP for ASP/ASP.NET - Jan.2008 - Dec.2010
技術Blog：http://techbank.jp/mymio/
けろ-みお
一旦、ニュースグループに返答したんですが、メッセージ削除されてしまったので
再投稿します。
> １レコードのフィールド数が多いんでテキストデータを構造体に放り込んで
> ループで回せば簡単で、SQLも長文で分かりづらくなる事もないかと思いまして。
なるほど。お気持ちは良くわかります。
.NET には、Reflection（リフレクション）と呼ばれる概念があり、
Reflectionを使用すると列数の多い構造体やクラスに対する
SQLの生成も列をループさせながら生成することができます。
（Reflectionを使うことで列数の多さにおけるSQL生成の手間は省けます）
サンプルコードを私のBlogに公開したので宜しければご確認ください。
http://techbank.jp/Community/blogs/mymio/archive/2010/02/21/24804.aspx
＃PropertyInfoを使っているところがReflectionになります。
.NET でプログラミング慣れしないとVB6以前やVBAばかりをやっている人には
結構難しい概念かもしれません。
（根本的なオブジェクト指向への理解が必要ですね）
また、Visual Studio 2008 Expressをご利用ということなので、
Visual Studio 2008 SP1をインストールして頂くと、
「ADO.NET Entity Framework」を使用することができますので、
SQLを書かなくてもまとまった処理を実装することができます。
ADO.NET Entity については、@IT に ASP.NET MVPのナオキさんが
書かれている記事がわかりやすいのでご参考にしてみると良いかもしれません。
http://www.atmarkit.co.jp/fdotnet/special/vs2008sp1ef/vs2008sp1ef_01.html
いずれにしてもADOやDAOと同じような発想を引きずったまま
ADO.NETやVB.NET を扱わない方が良いということだけは間違いないですね。
どうぞ宜しくお願いします。
--
けろ-みお（Mio Yoshida）
Microsoft MVP for ASP/ASP.NET - Jan.2008 - Dec.2010
技術Blog：http://techbank.jp/mymio/
ken
なんかすごく大変ですね・・・。
私は今回自分で使うだけですので結局
INFORMATION_SCHEMA.COLUMNS という システム ビューの（こんなビューがあるとは知らなかった）
[DATA_TYPE]・,[CHARACTER_MAXIMUM_LENGTH]フィールドを利用してSQLを
作成することにしました。（ソースも短くて分かりやすいし開発時間も短い）
今回はセキュリティ等はあまり気にしなくていいのですが、その必要があった場合
やはりこうゆうやり方はあまり良くないのでしょうか？
けろ-みお
テーブルの構成をクラス化（エンティティ化）するのは、
マイクロソフトも推奨しているアーキテクチャ手法で、
将来的にWebサービスやWindows Azure等のクラウドを使ってデータ公開したいのであれば
あのような書き方は.NET の世界では常識です。
大規模システムではなく個人的にやっている範疇であれば
そこまで意識しなくてもいいですが、時代が時代ですので
少しは意識して頂きたいですね。
> INFORMATION_SCHEMA.COLUMNS という システム ビューの（こんなビューがあるとは知らなかった）
> [DATA_TYPE]・,[CHARACTER_MAXIMUM_LENGTH]フィールドを利用してSQLを
> 作成することにしました。（ソースも短くて分かりやすいし開発時間も短い）
INFORMATION_SCHEMA.COLUMNS でもいいですし、
「sp_help テーブル名」を実行させ、実行結果からSQLを組み立てたり、
もしくは、sys.syscolumns、sys.systypes といったシステムビューで列情報をとることもできます。
PowerShellやストアド（こっちはSQL Server 2000用で作ったのでsyscolumnsのスキーマ名が違いますが）
での例でよろしければ、以前、こんなの作ったので参考にしてみてください。
http://techbank.jp/Community/blogs/mymio/archive/2009/02/05/4720.aspx
http://techbank.jp/Community/blogs/mymio/archive/2008/04/15/544.aspx
後は、VB.NET側でSQL実行させるのか、ストアド側で実行させるのかについては
やりやすい方でどうぞってところでしょうか
> 今回はセキュリティ等はあまり気にしなくていいのですが、その必要があった場合
> やはりこうゆうやり方はあまり良くないのでしょうか？
SQL文字列を組み立てるような作りは、インジェクションの問題があるので、
本来であればお勧めしないところですが、そうは言ってもなかなか難しいケースもありますので、
なんとも言えないです。
ストアドプロシージャをSQL Serverに作成し、VB.NETからはそのストアドをコールする
作りにしておくことが最善だと私は思っています。
（ INFORMATION_SCHEMA.COLUMNSやsys.syscolumns、sys.systypes も
作成したストアドから参照できますし）
ご検討下さい。
ken
また何かあった時はよろしくお願いします。