HomeVisual Basic技術項目 データベース処理

データベース処理

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



ページトップへ

データベース

サーバ

Copyright (C) MadCap. All Rights Reserved.