DBConnect・DBClose・DBGet・DBEof（ADO） - おのれ鍋奉行が！

DBConnect・DBClose・DBGet・DBEof（ADO）

概要

ADO（ADO.NETではない）によるDB操作関数群。

参照

• SQLの窓 / Web アプリケーションクラス / ASP標準化 / dbMySQL.inc

参考

• SQLの窓 / イマドキの Multi Language / 各種データベース接続

前提条件

• 新しいwebサイトの作成

手順

default.aspx.vb を以下のように変更。

※インスタンス名等、環境に応じて適宜変更。

Partial Class _Default
    Inherits System.Web.UI.Page
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
        Dim conn As Object = Nothing
        Dim Rs As Object = Nothing
 
        Dim DBType As String = "DBの種類"                  ' DBの種類
        Dim strTarget As String = "インスタンス名"         ' インスタンス名
        Dim strDB As String = "DB名"                       ' DB名
        Dim strUser As String = "ユーザ名"                 ' ユーザ名
        Dim strPass As String = "パスワード"               ' パスワード
 
 
        If DBConnect(DBType, conn, strTarget, strDB, strUser, strPass) Then
 
            Response.Write("接続成功")
 
        End If
 
        Dim sqlQuery As String = "SQL文"
 
        DBGet(conn, Rs, sqlQuery, True)
 
        Do While Not DBEof(Rs)
 
            Response.Write(Rs.Fields("列名").value & "<br>")
 
            Rs.movenext()
        Loop
 
 
        DBClose(conn)
 
    End Sub
 
    ' ******************************************************
    ' DB接続
    ' ******************************************************
    Function DBConnect( _
    ByVal DBType As String, _
    ByRef Connection As Object, _
    ByVal strTarget As String, _
    ByVal strDB As String, _
    ByVal strUser As String, _
    ByVal strPass As String _
    )
        Dim ConnectionString As String = ""
 
        If IsNothing(Connection) Then
            If IsNothing(Server) Then
                Connection = Server.CreateObject("ADODB.Connection")
            Else
                Connection = CreateObject("ADODB.Connection")
            End If
        End If
 
        Select Case DBType
            Case "Excel"
                ConnectionString = _
                 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & strTarget & ";" & _
                 "Extended Properties=""Excel 8.0;IMEX=1;"""
            Case "MDB"
                ConnectionString = _
                 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & strTarget & ";"
            Case "MySQL"
                ConnectionString = _
                 "Provider=MSDASQL" & _
                 ";DSN=" & strTarget & _
                 ";DATABASE=" & strDB & _
                 ";UID=" & strUser & _
                 ";PWD=" & strPass & _
                 ";"
            Case "SQLServer"
                ConnectionString = _
                 "Provider=SQLOLEDB;" & _
                 "Data Source=" & strTarget & ";" & _
                 "Initial Catalog=" & strDB & ";" & _
                 "User ID=" & strUser & ";" & _
                 "Password=" & strPass & ";"
        End Select
 
        On Error Resume Next
        Connection.Open(ConnectionString)
        If Err.Number <> 0 Then
            Return False
        End If
        On Error GoTo 0
 
        Return True
 
    End Function
 
    ' ******************************************************
    ' DB終了処理(接続を閉じる)
    ' ******************************************************
    Function DBClose( _
    ByRef CnRs As Object _
    )
 
        On Error Resume Next
        If CnRs.State >= 1 Then
            CnRs.Close()
        End If
        On Error GoTo 0
 
        Return True
 
    End Function
 
    ' ******************************************************
    ' DB読込み
    ' 【戻り値】: True(データ有り),False(データ無し)
    ' ******************************************************
    Function DBGet( _
    ByRef Connection As Object, _
    ByRef Record As Object, _
    ByVal SqlQuery As String, _
    ByVal bUpadateFlg As Boolean _
    )
        Dim flag_return As Boolean
 
        If IsNothing(Record) Then
            Record = Server.CreateObject("ADODB.Recordset")
        End If
 
        ' 閉じていない時は閉じる
        If Record.State >= 1 Then
            Record.Close()
        End If
 
        ' 更新処理に使用する場合は、レコード単位の共有的ロック
        If bUpadateFlg Then
            Record.LockType = 3
        End If
 
        ' レコードセット作成
        On Error Resume Next
 
        Record.Open(SqlQuery, Connection)
 
        If Err.Number <> 0 Then
            Response.Write(Err.Description)
        End If
 
        If Record.EOF Then
            flag_return = False
        Else
            flag_return = True
        End If
 
        On Error GoTo 0
 
        Return flag_return
 
    End Function
 
    ' ******************************************************
    ' EOF
    ' 【戻り値】: True(EOF),False(データ有り)
    ' ******************************************************
    Function DBEof(ByRef Record As Object)
 
        Dim bRet
 
        On Error Resume Next
        bRet = Record.EOF
        If Err.Number <> 0 Then
            Return True
            Exit Function
        End If
        On Error GoTo 0
 
        Return bRet
 
    End Function
 
 
End Class