2012/11/30

[VB]SQL serverをADO.NETで使う

VB.NETでSQL serverを使う場合は、ADO.NETという.NETのクラスを使う事で簡単にDBと通信できるけれど、そのデータを色々加工して・・・とかそういう風な事が詳しく載っているサイトがなかったものだから、ADO.NETを利用してデータグリッドビューにデータソースとして突っ込んで、劇的に簡単にコーディングできる方法をまとめてみます。



とりあえずは、SQL serverに接続する下準備をします。
下準備と言っても、サーバーに接続するための接続文字列をSqlConnectionをNewする時に指定するだけです。

接続文字列は、Data Sourceの場所やDBを指定してあるもので、以下の様な形式になっています。
Data Source=Local-PC\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=True
テスト用だったのでuser/passが設定されていない状態です。本番でこのような事をしてはいけません:-p

続いてSQLクエリーの発行準備を行います。
クエリーの発行は、SqlCommandクラスをNewしてオブジェクトを作り、その中で行います。

SqlCommandオブジェクトにクエリーをセットしたら、SqlDataAdapterオブジェクトにSqlCommandオブジェクトを投入して、結果を入れるDataSetオブジェクトを指定しつつ一気にデータを引っ張ります。

あとはDataGridViewオブジェクトのDataSourceにDataSetを代入したら、自動的にカラムヘッダーまで自動生成してくれます。

以上です。
コードにすると、言葉で説明する以上に簡単です。

' 先頭の方でインポートしておくと便利かも。
Imports System.Data.SqlClient

' selectする部分を簡単な関数にまとめた
private sub select()
  Dim con as New SqlConnection
  Dim cmd As New SqlCommand
  Dim dataAdapter As New SqlDataAdapter
  Dim dataSet As New DataSet

  Try
    ' SQL serverと接続
    con = New SqlConnection(connection)
    con.open()
    cmd.Connection = con

    ' クエリーをセット
    cmd.CommandText = "SELECT * FROM table1"
    dataAdapter.SelectCommand = cmd

    ' SQL serverに問い合わせてDataGridViewに結果を表示する
    dataAdapter.Fill(dataSet)
    DataGridView1.DataSource = dataSet.Tables(0)
  Catch ex As Exception
    Console.WriteLine("error: {0}", ex.ToString)
  Finally
    ' 後始末
    con.Close()
    con.Dispose()
  End Try
End Sub

10行程でSQLとの通信が表示も含めて終わるのは楽ですね。
しかし、よく見ると、データグリッドビューのカラムヘッダーには、DBテーブルのカラム名がそのまま表示されています。
テーブルのカラム名は英語で命名しているけれど、カラムのヘッダーは日本語で表示して欲しい事はよくあります。

こういう時は、DataGridView1.Columns(0).HeaderText = "ヘッダー"としてもいいですが、せっかくSqlDataAdapterを使っているのだから、データグリッドビューを後からいじるのは無粋というものです。

SQLのカラム名がそのまま表示されるのであれば、カラム名を変更してしまうと、後から変更する必要はありませんし、ヘッダーを変更するカラムのインデックスをいちいち確認する必要もありません。

Dim query as String = "SELECT col1 AS 名前, col2 AS 性別, col3 AS 住所 FROM table1"
    cmd.CommandText = query 

これでヘッダーテキストの変更が簡単に行えます。

あと、テーブルへINSERTやUPDATEする時は、プレースホルダーを利用するとインジェクション対策にもなります。
慣れかも知れませんが、SQLクエリーも見通し良くなる気がします。

' 接続準備はSELECTする時と共通
    cmd.CommandText = "INSERT INTO table1 (name, sex, address, up_date) values (@name, @sex, @address, @now)"
    cmd.Parameters.Add("@name", SqlDbType.NVarChar)
    cmd.Parameters("@name").Value = name
    cmd.Parameters.Add("@address", SqlDbType.NVarChar)
    cmd.Parameters("@address").Value = address
    cmd.Parameters.Add("@now", SqlDbType.DateTime)
    cmd.Parameters("@now").Value = Now
    Dim count As Integer = cmd.ExecuteNonQuery()
    ' INSERTの場合、変数countは常に1

SQL serverのTipsとしては
  • nchar型はnchar(100)とした場合に、20文字でも5文字でも、スペースでパディングしてDBに格納するので異様な数のスペースが付いてくる事があります。いちいちトリムするのは頭悪いので、nchar型は使わず、nvarchar型でカラムを定義すれば、不要なスペースは付きません。
  • MySQLに慣れきってしまった人に言えるのは、Auto IncrementはIdentityと名前が変わっております。データ型をIntにして、Is Identityの項目をはいにすれば、同様の機能が実現できます。


0 件のコメント:

コメントを投稿