データベース処理
SQL Server
SQL Server認証で接続
Dim St As String
Dim Cn As New System.Data.SqlClient.SqlConnection
Dim SQL As System.Data.SqlClient.SqlCommand
Dim ServerName As String = "(local)" 'サーバー名(またはIPアドレス)
Dim UserID As String = "sa" 'ユーザーID
Dim Password As String = "" 'パスワード
Dim DatabaseName As String = "MadCap" 'データベース
St = "Server=" & ServerName & ";"
St &= "User ID=" & UserID & ";"
St &= "Password=" & Password & ";"
St &= "Initial Catalog=" & DatabaseName
Cn.ConnectionString = St
SQL = Cn.CreateCommand
SQL.CommandText = "SELECT FirstName FROM Employees"
Cn.Open()
MsgBox(SQL.ExecuteScalar)
Cn.Close()
SQL.Dispose()
Cn.Dispose()
Windows認証で接続
Dim St As String
Dim Cn As New System.Data.SqlClient.SqlConnection
Dim SQL As System.Data.SqlClient.SqlCommand
Dim ServerName As String = "(local)" 'サーバー名(またはIPアドレス)
Dim DatabaseName As String = "MadCap" 'データベース
St = "Server=""(local)"";"
St &= "integrated security=SSPI;"
St &= "initial catalog = Northwind"
Cn.ConnectionString = St
SQL = Cn.CreateCommand
SQL.CommandText = "SELECT FirstName FROM Employees"
Cn.Open()
MsgBox(SQL.ExecuteScalar)
Cn.Close()
SQL.Dispose()
Cn.Dispose()
MDB
Dim St As String
Dim Cn As New System.Data.OleDb.OleDbConnection
Dim SQL As System.Data.OleDb.OleDbCommand
Dim UserID As String = "Admin"
Dim Password As String = ""
Dim MDBFile As String = "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
St = "Provider=""Microsoft.Jet.OLEDB.4.0"";"
St &= "Data Source=""" & MDBFile & """;"
St &= "User ID=" & UserID & ";"
St &= "Jet OLEDB:Database Password=" & Password
Cn.ConnectionString = St
SQL = Cn.CreateCommand
SQL.CommandText = "SELECT 氏名 FROM 社員"
Cn.Open()
'SQL実行し返却値をReaderへセット
Dim reader As System.Data.OleDb.OleDbDataReader = SQL.ExecuteReader
While reader.Read
MessageBox.Show(reader(0))
End While
Cn.Close()
SQL.Dispose()
Cn.Dispose()
insert文作成メソッド
Public Function Insert(ByVal tableName As String, ByVal hashTable As Hashtable) As String
Dim strQuery As String = "insert into " & tableName & "("
Dim index As Integer
index = 1
Dim Item As DictionaryEntry
For Each Item In hashTable
strQuery &= Item.Key.ToString()
If index <> hashTable.Count Then
strQuery &= ","
End If
index = index + 1
Next
strQuery &= ") values("
index = 1
For Each Item In hashTable
strQuery &= Item.Value.ToString()
If index <> hashTable.Count Then
strQuery &= ","
End If
index = index + 1
Next
strQuery &= ")"
Return strQuery
End Function
udpate文作成メソッド
Public Function Update(ByVal tableName As String, _
ByVal hashTable As Hashtable, _
ByVal where As String) As String
Dim strQuery As String = "update " & tableName & " set "
Dim Item As DictionaryEntry
Dim index As Integer = 1
For Each Item In hashTable
strQuery &= Item.Key.ToString() & " = " & Item.Value.ToString()
If index <> hashTable.Count Then
strQuery &= ","
End If
index = index + 1
Next
strQuery &= "where " & where
Return strQuery
End Function