VB.Net的SQL 2005数据库类

最新推荐文章于 2025-08-06 16:23:50 发布

原创最新推荐文章于 2025-08-06 16:23:50 发布 · 763 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #vb.net #sql #function #exception #string

VB.Net 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个使用Visual Basic .NET实现的数据库操作类，包括连接数据库、执行SQL命令、填充数据集、调用存储过程等功能，并提供了错误处理机制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Public Class ClassDB

    Public cn As System.Data.SqlClient.SqlConnection
    Public da As System.Data.SqlClient.SqlDataAdapter
    Public ds As New DataSet
    Public dt As New DataTable
    Public cmd As System.Data.SqlClient.SqlCommand
    '****************************************************************************************
    '连接数据库
    Public Function ConnectionOpen(ByVal uid As String, ByVal pwd As String, ByVal DBName As String, ByVal server As String) As Boolean
        Dim cnstr As String = _
                          "Persist Security Info=False;" _
                        & "User ID=" & uid & ";Password=" & pwd & ";" _
                        & "Initial Catalog=" & DBName & ";" _
                        & "Server=" & server
        cn = New System.Data.SqlClient.SqlConnection(cnstr)
        Try
            cn.Open()
        Catch ex As System.Data.SqlClient.SqlException
            MsgBox(ex.Message.ToString())
        End Try
    End Function
    '断开数据库的连接
    Public Function ConnectionClose() As Boolean
        Try
            cn.Close()
        Catch ex As System.Data.SqlClient.SqlException
            MsgBox(ex.Message.ToString())
        End Try
    End Function
    '插入记录、删除记录、更新记录到指定的数据库->数据表
    Public Function InsertDelUpdateRecord(ByVal cmdText As String) As Boolean
        Try
            cmd = New System.Data.SqlClient.SqlCommand(cmdText, cn) '指定sql命令及该命令用于哪一个数据库连接
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        End Try
    End Function
    '填充数据集，他会根据cmdtext参数说指定的查询返回数据库里特定的表
    Public Function DataSetFill(ByVal cmdText As String, ByVal tabName As String) As Boolean
        Try
            cmd = New System.Data.SqlClient.SqlCommand(cmdtext, cn) '指定sql查询命令及该命令用于哪一个数据库连接
            da = New System.Data.SqlClient.SqlDataAdapter(cmd)
            da.Fill(ds, tabName) '表名可以映射为数据库中实际的表名称，但是他和数据库实际的表名称不是一回事
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        End Try
    End Function
    '调用存储过程
    Public Function ExecProc(ByVal procName As String, ByVal tabName As String) As Boolean
        Try
            cmd = New System.Data.SqlClient.SqlCommand(procName, cn)
            cmd.CommandType = CommandType.StoredProcedure
            da = New System.Data.SqlClient.SqlDataAdapter(cmd)
            da.Fill(ds, tabName)
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        End Try
    End Function
    '这个基本不用，因为可以直接通过ds来调用
    Public Function DataTableFill(ByVal index As Integer) As Boolean
        Try
            dt = ds.Tables(index)
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        End Try
    End Function
    '这个基本不用，因为可以直接通过ds来调用
    Public Function DataTableFill(ByVal name As String) As Boolean
        Try
            dt = ds.Tables(name)
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        End Try
    End Function
    '释放资源
    Public Function DBDispos() As Boolean
        Try
            cn.Dispose()
            'da.Dispose()
            ds.Dispose()
            dt.Dispose()
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        End Try

    End Function
End Class