今回は、データベースを操作するクラスを作成しておくととても便利なので紹介していきます。
あくまでサンプルなので、自分なりに改造して使いやすくしていくのをおすすめします。
初心者の方はコピーして使ってみてください。
今回使用するデータベースはこちらになります。
SELECT * FROM hironimoTable
id |nickName|animal|height|weight
-----+--------+------+------+------
00001|ひろにも|きりん|150 |55.5
00002|ひろにも|きりん|160 |60.0
00003|ひろにも|きりん|170 |70.0
00004|ひろにも|きりん|180 |80.0
00005|ひろにも|きりん|150 |90.0
今回のサンプルは下記３つのファイルを作成しています。
- ClsDataBase.vb
- ClsHironimoTableDB.vb
- frmClsDB.vb
各ファイルの役割
- ClsDataBase.vbは、データベースを操作するクラス
- ClsHironimoTableDB.vbは、SQLServerのテーブル(HironimoTable)を操作するクラス
- frmClsDB.vbは、画面(フォーム)を作成
そもそもクラスがわからない、SQLServerの接続方法がわからない、トランザクションがわからない、という方はまずこちらを参考にしてみてください。
こちらの事前知識を先に身に着けておきましょう。
クラスの作成 DB操作
はじめに、ClsDataBase.vbのソースを紹介します。
データベースを操作するクラスなので作成しておくと、
新しいプロジェクトを作成した時などファイルをコピーすれば使えるのでとても便利です。
Imports System.Data.SqlClient
''' <summary>
''' DBを操作するクラス
''' </summary>
Public Class ClsDataBase
Implements IDisposable
Public Property conStr As String
Public Property sqlCon As SqlConnection
Public Property tran As SqlTransaction
Public Property command As SqlCommand
Public Property adapter As SqlDataAdapter
''' <summary>
''' コンストラクタ(初期処理)
''' </summary>
''' <param name="connectionString"></param>
Sub New(ByVal connectionString As String)
conStr = connectionString
End Sub
''' <summary>
''' DBオープン
''' </summary>
Public Sub DBOpen()
If sqlCon Is Nothing Then
sqlCon = New SqlConnection(conStr)
sqlCon.Open()
End If
End Sub
''' <summary>
''' DBクローズ
''' </summary>
Public Sub DBClose()
If Not sqlCon Is Nothing Then
sqlCon.Close()
End If
End Sub
''' <summary>
''' トランザクションスタート
''' </summary>
Public Sub DBTranSt()
If tran Is Nothing Then
tran = sqlCon.BeginTransaction
End If
End Sub
''' <summary>
''' コミット
''' </summary>
Public Sub DBCommit()
If Not tran Is Nothing Then
tran.Commit()
End If
End Sub
''' <summary>
''' ロールバック
''' </summary>
Public Sub DBRollback()
If Not tran Is Nothing Then
tran.Rollback()
End If
End Sub
''' <summary>
''' SQL実行
''' </summary>
''' <param name="sql">SQL文</param>
''' <returns>Datatable型</returns>
Public Function GetDtSql(ByVal sql As String) As DataTable
Dim returnDt As New DataTable
Try
'SQL実行
command = New SqlCommand(sql, sqlCon, tran)
adapter = New SqlDataAdapter(command)
adapter.Fill(returnDt)
Catch ex As Exception
Throw
Finally
command.Dispose()
adapter.Dispose()
End Try
Return returnDt
End Function
''' <summary>
''' SQL実行_更新
''' </summary>
''' <param name="sql">SQL文</param>
''' <returns>Datatable型</returns>
Public Function ExecuteSql(ByVal sql As String) As Integer
Dim cnt As Integer
Try
'SQL実行
command = New SqlCommand(sql, sqlCon, tran)
cnt = command.ExecuteNonQuery()
Catch ex As Exception
Throw
Finally
command.Dispose()
End Try
Return cnt
End Function
''' <summary>
''' 解放
''' </summary>
Public Sub Dispose() Implements IDisposable.Dispose
sqlCon.Close()
If Not tran Is Nothing Then
tran.Dispose()
End If
If Not command Is Nothing Then
command.Dispose()
End If
If Not adapter Is Nothing Then
adapter.Dispose()
End If
End Sub
End Class
クラスの作成 テーブル操作
次に、操作したいテーブルのクラスを作成していきます。
私はいつも操作するテーブルの数だけ、このようなクラスを作っています。
いろんな画面から同じSQLを呼び出すことがよくあるので、毎回同じような記述をするのではなく、
あらかじめクラスを作成しておき、部品のように使っています。
Public Class ClsHironimoTableDB
Public Property id As String
Public Property nickName As String
Public Property animal As String
Public Property height As Double
Public Property weight As Double
Public Property clsdb As ClsDataBase
''' <summary>
''' コンストラクタ
''' </summary>
Sub New(ByVal db As ClsDataBase)
clsdb = db
End Sub
Public Function GetAll() As DataTable
Try
Dim sql As New System.Text.StringBuilder()
sql.AppendLine("SELECT")
sql.AppendLine(" *")
sql.AppendLine("FROM")
sql.AppendLine(" hironimoTable")
GetAll = clsdb.GetDtSql(sql.ToString)
Catch ex As Exception
Throw
End Try
End Function
Public Function UpdateSql() As Integer
Try
Dim sql As New System.Text.StringBuilder()
sql.AppendLine("UPDATE hironimoTable")
sql.AppendLine("SET")
sql.AppendLine(" nickName = '" & nickName & "'")
sql.AppendLine("WHERE")
sql.AppendLine(" id = '" & id & "'")
UpdateSql = clsdb.ExecuteSql(sql.ToString)
Catch ex As Exception
Throw
End Try
End Function
End Class
フォーム作成
作成したクラスを呼び出す、画面を作成しましょう。
テーブルの更新と取得をする処理を記載してみましょう。
Public Class frmClsDB
Private Sub FrmClsDB_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'データベースクラス
Dim db As New ClsDataBase(My.Settings.sqlserver)
Try
'データベース接続
db.DBOpen()
'トランザクション開始
db.DBTranSt()
'update文実行
Dim hironimoDB_upd As New ClsHironimoTableDB(db)
hironimoDB_upd.id = "00001"
hironimoDB_upd.nickName = "ひろにもさん"
Dim updateCnt As Integer = hironimoDB_upd.UpdateSql()
'更新件数表示
MsgBox(updateCnt)
'テーブルのデータ取得
Dim hironimoDB As New ClsHironimoTableDB(db)
Dim result As DataTable = hironimoDB.GetAll()
'取得したデータを出力
For rowindex As Integer = 0 To result.Rows.Count - 1
For colindex As Integer = 0 To result.Columns.Count - 1
Console.Write(result.Rows(rowindex).Item(colindex).ToString & " ")
Next
Console.WriteLine()
Next
'更新をコミット
db.DBCommit()
Catch ex As Exception
'エラー時
MsgBox(ex.ToString)
'更新をロールバック
db.DBRollback()
Finally
'データベース接続を閉じる
db.Dispose()
End Try
End Sub
End Class結果
00001 ひろにもさん きりん 150 55.5
00002 ひろにも きりん 160 60.0
00003 ひろにも きりん 170 70.0
00004 ひろにも きりん 180 80.0
00005 ひろにも きりん 150 90.0
まとめ
今回は、DBを操作するクラスとテーブルを操作するクラスを紹介しました。
自分なりに理解して、使ってみてください。
