form1.vb

SQL Server 业已成为最为流行的几大数据库之一

Imports System.Runtime.InteropServices
Imports System
Public Class Form1

    Dim odbcEnv As IntPtr = IntPtr.Zero
    Dim odbcConnect As IntPtr = IntPtr.Zero
    Dim odbcStmtHandle As IntPtr = IntPtr.Zero
    Dim ODBCInit As Boolean

    Private Declare Function SQLDataSources Lib "ODBC32.DLL" _
       (ByVal henv&, ByVal fDirection%, ByVal szDSN$, ByVal cbDSNMax%, _
    ByVal pcbDSN%, ByVal szDescription$, ByVal cbDescriptionMax%, _
    ByVal pcbDescription%) As Int32

    <DllImport("odbc32.dll")> _
   Private Shared Function SQLAllocHandle(ByVal hType As Short, ByVal inputHandle As IntPtr, ByRef outputHandle As IntPtr) As Short
    End Function
    <DllImport("odbc32.dll")> _
        Private Shared Function SQLSetEnvAttr(ByVal henv As IntPtr, ByVal attribute As Integer, ByVal valuePtr As IntPtr, ByVal strLength As Integer) As Short
    End Function

    <DllImport("odbc32.dll")> _
        Private Shared Function SQLExecDirect(ByVal outputHandle As IntPtr, ByVal sqlString As String, _
                               ByVal sqlstrlen As Integer) As Short
    End Function
    <DllImport("odbc32.dll")> _
            Private Shared Function SQLDriverConnect(ByVal hDBC As IntPtr, ByVal hwnd As IntPtr, _
    ByVal szCSIn As String, ByVal cbCSIn As Integer, ByVal szCSOut As String, _
    ByVal cbCSMax As Integer, ByVal cbCSOut As Integer, ByVal f As Integer) As Short
    End Function

    '
    Declare Function SQLAllocEnv Lib "odbc32.dll" (ByVal env As Int32) As Int32

    Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal env As Int32, ByVal ldbc As Int32) As Int32

    'Private Declare Function SQLDriverConnect Lib "ODBC32.DLL" (ByVal hDBC As IntPtr, ByVal hwnd As IntPtr, _
    '  ByVal szCSIn As String, ByVal cbCSIn As Int32, ByVal szCSOut As String, _
    '  ByVal cbCSMax As Integer, ByVal cbCSOut As Integer, ByVal f As Integer) As Short
    '
    Declare Function SQLFetch Lib "odbc32.dll" (ByVal lStmt As IntPtr) As Short
    Declare Function SQLGetData Lib "odbc32.dll" (ByVal lStmt As IntPtr, ByVal col As Int32, _
                             ByVal wConvType As Int32, ByVal lpbBuf As String, _
                             ByVal dwbuflen As Int32, ByVal lpcbout As Int32) As Int32
    Declare Function SQLFreeEnv Lib "odbc.dll" (ByVal env As Int32) As Int32
    Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal lStmt As Int32, ByVal EndOption As Int32) As Int32





    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If SQLAllocHandle(1, 0, odbcEnv) <> 0 Then
            ' If SQLAllocEnv(odbcEnv) <> 0 Then
            MsgBox("无法创建ODBC驱动!")
            End
        End If
        If SQLSetEnvAttr(odbcEnv, 200, New System.IntPtr(3), 0) <> 0 Then
            MsgBox("无法创建环境属性!")
            End
        End If

        If SQLAllocHandle(2, odbcEnv, odbcConnect) <> 0 Then
            MsgBox("无法为连接句柄分配空间!")
            End
        End If


        Dim connectResult(256) As Char
        Dim iSize As Int32
        Dim dsnStr As String

        dsnStr = "DSN=testdb;UID=sa;PWD=tuohzzhout;db=testdb"

        If SQLDriverConnect(odbcConnect, 0, dsnStr, Len(dsnStr), connectResult, 256, iSize, 0) <= 0 Then
            MsgBox("无法与ODBC驱动建立连接!")
            End
        End If



        If SQLAllocHandle(3, odbcConnect, odbcStmtHandle) <> 0 Then

            MsgBox("无法为查询语句创建句柄")
            End

        End If

       


    End Sub

    Private Sub editSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles editSearch.Click

        Dim SqlStr As String
        SqlStr = "SELECT ItemID, ItemName FROM TestTable where ItemID='" & Trim(textSearch.Text.ToString) & "'"
        If SQLExecDirect(odbcStmtHandle, SqlStr, Len(SqlStr)) <> 0 Then
            MsgBox("执行查询语句失败！")
            End
        End If



        Dim ColContent(255) As Char
        Dim ContentLen As Int32
        Dim RecordStr As String
        Dim bPerform As Short

        Do
            bPerform = SQLFetch(odbcStmtHandle)
            bPerform = SQLGetData(odbcStmtHandle, 1, 1, ColContent.ToString, 256, ContentLen)
            RecordStr = ColContent
            bPerform = SQLGetData(odbcStmtHandle, 2, 1, ColContent.ToString, 256, ContentLen)
            RecordStr = RecordStr + ColContent
            textResult.Text = RecordStr
        Loop While Not bPerform
        SQLFreeStmt(odbcStmtHandle, 1)

    End Sub
End Class