VB.NET SQLHelper（支持 MSSQL、MySQL、SQLite）

最新推荐文章于 2024-05-14 07:37:57 发布

刮骨剑

最新推荐文章于 2024-05-14 07:37:57 发布

阅读量724

点赞数 2

分类专栏： VB.NET 文章标签： vb.net 数据库

本文链接：https://blog.csdn.net/zijianll/article/details/118668792

版权

VB.NET 专栏收录该内容

1 篇文章 2 订阅

订阅专栏

直接上代码，相关扩展可移步（https://download.csdn.net/download/zijianll/20211476?spm=1001.2014.3001.5501）下载。

MSSQLHelper

'作者：刮骨剑
'日期：2021-07-06
Imports System.Data.SqlClient
''' <summary>
''' MSSQL 数据库操作类
''' </summary>
Public Class MSSQLHelper
    ''' <summary>
    ''' 数据库连接配置
    ''' </summary>
    Private dbCfgs As Dictionary(Of String, Object) = New Dictionary(Of String, Object)

    ''' <summary>
    ''' 数据库连接对象
    ''' </summary>
    Private ReadOnly sqlConn As SqlConnection

    ''' <summary>
    ''' SQl语句或存储过程对象
    ''' </summary>
    Private sqlCmd As SqlCommand

    ''' <summary>
    ''' 事务控制对象
    ''' </summary>
    Private sqlTran As SqlTransaction

    ''' <summary>
    ''' 数据库操作状态
    ''' </summary>
    Public Property SQLStatus As Boolean

    ''' <summary>
    ''' 影响行数
    ''' </summary>
    Public Property RowsAffected As Integer = -1

    ''' <summary>
    ''' 字段集数组
    ''' </summary>
    Public Property ColsArr() As String()

    ''' <summary>
    ''' 数据集数组
    ''' </summary>
    Public Property DatasArr() As String(,)

    ''' <summary>
    ''' 数据集（DataTable）
    ''' </summary>
    Public Property DatasDt As New DataTable

    ''' <summary>
    ''' 数据集（DataSet）
    ''' </summary>
    Public Property DatasDs As New DataSet

    ''' <summary>
    ''' 数据库连接配置初始化
    ''' </summary>
    Private Sub DBCfgInit()
        Dim dbCfgId As String    '数据库连接配置ID

        '配置示例
        dbCfgId = "test"
        dbCfgs(dbCfgId) = New Dictionary(Of String, String) From {
            {"dbAddress", "127.0.0.1,1433"},
            {"dbName", "test"},
            {"dbUser", "test"},
            {"dbPwd", "test"}
        }
    End Sub

    ''' <summary>
    ''' 创建 SQLHelper 实例
    ''' </summary>
    ''' <param name="dbCfgId">数据库连接配置ID</param>
    Public Sub New(ByVal dbCfgId As String)
        Dim sqlConnStr As String
        Dim dbAddress As String '数据库连接地址
        Dim dbName As String    '数据库名
        Dim dbUser As String    '数据库用户名
        Dim dbPwd As String     '数据库密码

        '数据库连接配置初始化
        Call DBCfgInit()

        '获取数据库配置
        If dbCfgs.ContainsKey(dbCfgId) Then
            dbAddress = dbCfgs(dbCfgId)("dbAddress")
            dbName = dbCfgs(dbCfgId)("dbName")
            dbUser = dbCfgs(dbCfgId)("dbUser")
            dbPwd = dbCfgs(dbCfgId)("dbPwd")
        Else
            SQLStatus = False
            MsgBox("数据库连接信息配置 " & dbCfgId & " 不存在！", 16, "错误")
            Exit Sub
        End If

        sqlConnStr =
        "Application Name=" & Application.ProductName & ";" _    '应用程序的名称，如果不提供应用程序名称，默认是：“.Net SqlClient Data Provider”
        & "Connect Timeout=5;" _    '在终止尝试连接并产生错误之前，等待与服务器的连接的时间长度（以秒为单位）。
        & "Data Source=" & dbAddress & ";" _    '要连接的 SQL Server 实例的名称或网络地址。
        & "Database=" & dbName & ";" _    '数据库的名称。
        & "Integrated Security=False;" _    '当为 false 时，将在连接中指定用户 ID 和密码。当为 true 时，将使用当前的 Windows 帐户凭据进行身份验证。可识别的值为 True、False、yes、no 以及与 True 等效的 sspi（强烈推荐）。
        & "User ID=" & dbUser & ";" _    'SQL Server 登录帐户（建议不要使用。为了维护最高级别的安全性，强烈建议改用 Integrated Security 或 Trusted_Connection 关键字）。
        & "Password=" & dbPwd & ";" _    'SQL Server 帐户登录的密码（建议不要使用。为了维护最高级别的安全性，强烈建议改用 Integrated Security 或 Trusted_Connection 关键字）。
        & "Encrypt=False;" _    '当该值为 true 时，如果服务器端安装了证书，则 SQL Server 将对所有在客户端和服务器之间传送的数据使用 SSL 加密。可识别的值为 true、false、yes 和 no。
        & "Persist Security Info=False;"    '当该值设置为 false 或 no（强烈推荐）时，如果连接是打开的或者一直处于打开状态，那么安全敏感信息（如密码）将不会作为连接的一部分返回。重置连接字符串将重置包括密码在内的所有连接字符串值。可识别的值为 true、false、yes 和 no。

        Try
            '连接并打开数据库
            sqlConn = New SqlConnection(sqlConnStr)
            sqlConn.Open()
            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False
            MsgBox("数据库连接错误！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")
            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL增删改语句
    ''' </summary>
    ''' <param name="sqlStrs">SQl语句文本列表</param>
    Public Sub IUD(ByVal sqlStrs As ArrayList)
        Dim sqlStr As String

        '变量初始化
        RowsAffected = -1

        Try
            '开启事务
            sqlTran = sqlConn.BeginTransaction()

            For Each sqlStr In sqlStrs
                sqlCmd = New SqlCommand(sqlStr, sqlConn, sqlTran)

                RowsAffected += sqlCmd.ExecuteNonQuery()
            Next

            RowsAffected += 1

            '提交事务
            sqlTran.Commit()

            SQLStatus = True
        Catch ex As Exception
            '回滚事务
            sqlTran.Rollback()

            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL查询语句并将结果集存入数组（仅返回第一个结果集）
    ''' </summary>
    ''' <param name="sqlStr">SQl语句文本</param>
    Public Sub SelectToArray(ByVal sqlStr As String)
        Dim sqlReader As SqlDataReader

        '变量初始化
        RowsAffected = -1
        ColsArr = Nothing
        DatasArr = Nothing

        Try
            sqlCmd = New SqlCommand(sqlStr, sqlConn)

            '执行SQL语句
            sqlReader = sqlCmd.ExecuteReader()

            '判断是否有结果
            If sqlReader.HasRows = False Then
                RowsAffected = 0
            End If

            '注：因为 SqlDataReader 是一条一条语句的读取，只能获取列数，不能获取行数，因此需要将总的记录除以列数才能获取行数。

            ' 定义函数返回数组的列数和行数
            Dim lstReader As New List(Of String)
            Dim intColumnCount As Integer = sqlReader.FieldCount
            Dim intRowsCount As Integer

            '将字段集存入数组
            ReDim ColsArr(intColumnCount - 1)
            For i = 0 To intColumnCount - 1
                ColsArr(i) = sqlReader.GetName(i)
            Next

            '将数据集存入 lstReader 列表
            While sqlReader.Read()
                For i = 0 To intColumnCount - 1
                    lstReader.Add(sqlReader.GetValue(i).ToString)   '如果字段值是 NULL，则返回空字符串
                Next
            End While

            '获取数据集的行数
            intRowsCount = lstReader.Count / intColumnCount

            '将数据集存入二维数组
            ReDim DatasArr(intRowsCount - 1, intColumnCount - 1)
            Dim lstIdx As Integer = 0
            For j = 0 To UBound(DatasArr, 1)
                For i = 0 To UBound(DatasArr, 2)
                    DatasArr(j, i) = lstReader.Item(lstIdx)
                    lstIdx += 1
                Next
            Next

            RowsAffected = intRowsCount

            sqlReader.Close()

            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL查询语句并将结果集存入DataTable对象（仅返回第一个结果集）
    ''' </summary>
    ''' <param name="sqlStr">SQl语句文本</param>
    Public Sub SelectToDt(ByVal sqlStr As String)
        Dim sqlReader As SqlDataReader

        '变量初始化
        RowsAffected = -1
        DatasDt = New DataTable

        Try
            sqlCmd = New SqlCommand(sqlStr, sqlConn)

            '执行SQL语句
            sqlReader = sqlCmd.ExecuteReader()

            '判断是否有结果
            If sqlReader.HasRows = False Then
                RowsAffected = 0
            End If

            DatasDt.Load(sqlReader)

            RowsAffected = DatasDt.Rows.Count

            sqlReader.Close()

            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL查询语句并将结果集存入DataSet对象（返回所有结果集）
    ''' </summary>
    ''' <param name="sqlStr">SQl语句文本</param>
    Public Sub SelectToDs(ByVal sqlStr As String)
        '变量初始化
        RowsAffected = -1
        DatasDs = New DataSet

        Try
            sqlCmd = New SqlCommand(sqlStr, sqlConn)

            Dim sda As SqlDataAdapter = New SqlDataAdapter(sqlCmd)

            sda.Fill(DatasDs)

            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 释放资源，关闭数据库连接
    ''' </summary>
    Public Sub Close()
        If IsNothing(sqlCmd) = False Then
            sqlCmd = Nothing
        End If

        If IsNothing(DatasDt) = False Then
            DatasDt = Nothing
        End If

        If IsNothing(DatasDs) = False Then
            DatasDs = Nothing
        End If

        If IsNothing(sqlConn) = False Then
            sqlConn.Close()
        End If
    End Sub
End Class

MSSQLHelper 示例

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.MinimumSize = New Size(1000, 500)
    End Sub
    
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'MSSQL 数据库操作示例：执行SQL查询语句并将结果集存入数组（仅返回第一个结果集）

        Dim sqlHelper As MSSQLHelper

        sqlHelper = New MSSQLHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        sqlHelper.SelectToArray("SELECT TOP 10 * FROM test")

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected & vbCrLf & "sqlHelper.ColsArr(0): " & sqlHelper.ColsArr(0))

        sqlHelper.Close()
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        'MSSQL 数据库操作示例：执行SQL查询语句并将结果集存入DataTable对象（仅返回第一个结果集）

        Dim sqlHelper As MSSQLHelper

        sqlHelper = New MSSQLHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        sqlHelper.SelectToDt("SELECT TOP 200 * FROM test ORDER BY id")

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        DataGridView1.DataSource = sqlHelper.DatasDt
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        'DataGridView1.Refresh()

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected)

        sqlHelper.Close()
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        'MSSQL 数据库操作示例：执行SQL增删改语句

        Dim sqlHelper As MSSQLHelper

        sqlHelper = New MSSQLHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        Dim sqlStrs As ArrayList = New ArrayList From {
            "UPDATE test SET dt = GETDATE() WHERE id = 1",
            "UPDATE test SET dt = GETDATE() WHERE id = 2"
        }
        sqlHelper.IUD(sqlStrs)

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected)

        sqlHelper.Close()
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        'MSSQL 数据库操作示例：执行SQL查询语句并将结果集存入DataSet对象（返回所有结果集）

        Dim sqlHelper As MSSQLHelper

        sqlHelper = New MSSQLHelper("test")

        If Not SQLHelper.SQLStatus Then
            Exit Sub
        End If

        SQLHelper.SelectToDs("SELECT TOP 2 * FROM test;SELECT TOP 2 * FROM test WHERE 1 = 11;SELECT TOP 3 * FROM test;")

        If Not SQLHelper.SQLStatus Then
            SQLHelper.Close()
            Exit Sub
        End If

        Dim tabPage As TabPage
        Dim dataGV As DataGridView
        For i = 0 To SQLHelper.DatasDs.Tables.Count - 1
            tabPage = New TabPage
            dataGV = New DataGridView
            With tabPage
                .Name = "Result" & i + 1
                .Text = .Name
                With dataGV
                    .Name = "dataGV" & i + 1
                    .Dock = DockStyle.Fill
                    .DataSource = SQLHelper.DatasDs.Tables(i)
                    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                End With
                .Controls.Add(dataGV)
            End With
            TabControl1.Controls.Add(tabPage)
        Next

        MsgBox("sqlHelper.DatasDs.Tables.Count: " & SQLHelper.DatasDs.Tables.Count)

        SQLHelper.Close()
    End Sub
End Class

MySQLHelper

'作者：刮骨剑
'日期：2021-07-07
'使用说明：
'1、在网站（https://dev.mysql.com/downloads/connector/net/）下载 .NET & Mono 版本的压缩包。
'2、把压缩包中的小于等于工程 .NET 版本号的文件夹中的 MySql.Data.dll 文件放进工程的生成输出路径下（如 bin\Debug\）。
'3、添加引用 MySql.Data.dll。
Imports MySql.Data.MySqlClient
''' <summary>
''' MySQL 数据库操作类
''' </summary>
Public Class MySQLHelper
    ''' <summary>
    ''' 数据库连接配置
    ''' </summary>
    Private dbCfgs As Dictionary(Of String, Object) = New Dictionary(Of String, Object)

    ''' <summary>
    ''' 数据库连接对象
    ''' </summary>
    Private ReadOnly sqlConn As MySqlConnection

    ''' <summary>
    ''' SQl语句对象
    ''' </summary>
    Private sqlCmd As MySqlCommand

    ''' <summary>
    ''' 事务控制对象
    ''' </summary>
    Private sqlTran As MySqlTransaction

    ''' <summary>
    ''' 数据库操作状态
    ''' </summary>
    Public Property SQLStatus As Boolean

    ''' <summary>
    ''' 影响行数
    ''' </summary>
    Public Property RowsAffected As Integer = -1

    ''' <summary>
    ''' 字段集数组
    ''' </summary>
    Public Property ColsArr() As String()

    ''' <summary>
    ''' 数据集数组
    ''' </summary>
    Public Property DatasArr() As String(,)

    ''' <summary>
    ''' 数据集（DataTable）
    ''' </summary>
    Public Property DatasDt As New DataTable

    ''' <summary>
    ''' 数据集（DataSet）
    ''' </summary>
    Public Property DatasDs As New DataSet

    ''' <summary>
    ''' 数据库连接配置初始化
    ''' </summary>
    Private Sub DBCfgInit()
        Dim dbCfgId As String    '数据库连接配置ID

        '配置示例
        dbCfgId = "test"
        dbCfgs(dbCfgId) = New Dictionary(Of String, String) From {
            {"dbAddress", "127.0.0.1"},
            {"dbPort", "3306"},
            {"dbName", "test"},
            {"dbUser", "test"},
            {"dbPwd", "test"}
        }
    End Sub

    ''' <summary>
    ''' 创建 MySQLHelper 实例
    ''' </summary>
    ''' <param name="dbCfgId">数据库连接配置ID</param>
    Public Sub New(ByVal dbCfgId As String)
        Dim sqlConnStr As String
        Dim dbAddress As String '数据库连接地址
        Dim dbPort As String    '数据库端口
        Dim dbName As String    '数据库名
        Dim dbUser As String    '数据库用户名
        Dim dbPwd As String     '数据库密码

        '数据库连接配置初始化
        Call DBCfgInit()

        '获取数据库配置
        If dbCfgs.ContainsKey(dbCfgId) Then
            dbAddress = dbCfgs(dbCfgId)("dbAddress")
            dbPort = dbCfgs(dbCfgId)("dbPort")
            dbName = dbCfgs(dbCfgId)("dbName")
            dbUser = dbCfgs(dbCfgId)("dbUser")
            dbPwd = dbCfgs(dbCfgId)("dbPwd")
        Else
            SQLStatus = False
            MsgBox("数据库连接信息配置 " & dbCfgId & " 不存在！", 16, "错误")
            Exit Sub
        End If

        sqlConnStr = "Server=" & dbAddress & ";" _
            & "Port=" & dbPort & ";" _
            & "Database=" & dbName & ";" _
            & "Uid=" & dbUser & ";" _
            & "Pwd=" & dbPwd & ";"

        Try
            '连接并打开数据库
            sqlConn = New MySqlConnection(sqlConnStr)
            sqlConn.Open()
            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False
            MsgBox("数据库连接错误！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")
            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL增删改语句
    ''' </summary>
    ''' <param name="sqlStrs">SQl语句文本列表</param>
    Public Sub IUD(ByVal sqlStrs As ArrayList)
        Dim sqlStr As String
        Dim rowsAff As Integer

        '变量初始化
        RowsAffected = -1

        Try
            '开启事务
            sqlTran = sqlConn.BeginTransaction()

            For Each sqlStr In sqlStrs
                sqlCmd = New MySqlCommand(sqlStr, sqlConn, sqlTran)

                rowsAff = sqlCmd.ExecuteNonQuery()

                Console.WriteLine(rowsAff)

                RowsAffected += rowsAff
            Next

            RowsAffected += 1

            '提交事务
            sqlTran.Commit()

            SQLStatus = True
        Catch ex As Exception
            '回滚事务
            sqlTran.Rollback()

            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL查询语句并将结果集存入数组（仅返回第一个结果集）
    ''' </summary>
    ''' <param name="sqlStr">SQl语句文本</param>
    Public Sub SelectToArray(ByVal sqlStr As String)
        Dim sqlReader As Object

        '变量初始化
        RowsAffected = -1
        ColsArr = Nothing
        DatasArr = Nothing

        Try
            sqlCmd = New MySqlCommand(sqlStr, sqlConn, sqlTran)

            '执行SQL语句
            sqlReader = sqlCmd.ExecuteReader()

            '判断是否有结果
            If sqlReader.HasRows = False Then
                RowsAffected = 0
            End If

            '注：因为 SqlDataReader 是一条一条语句的读取，只能获取列数，不能获取行数，因此需要将总的记录除以列数才能获取行数。

            ' 定义函数返回数组的列数和行数
            Dim lstReader As New List(Of String)
            Dim intColumnCount As Integer = sqlReader.FieldCount
            Dim intRowsCount As Integer

            '将字段集存入数组
            ReDim ColsArr(intColumnCount - 1)
            For i = 0 To intColumnCount - 1
                ColsArr(i) = sqlReader.GetName(i)
            Next

            '将数据集存入 lstReader 列表
            While sqlReader.Read()
                For i = 0 To intColumnCount - 1
                    lstReader.Add(sqlReader.GetValue(i).ToString)   '如果字段值是 NULL，则返回空字符串
                Next
            End While

            '获取数据集的行数
            intRowsCount = lstReader.Count / intColumnCount

            '将数据集存入二维数组
            ReDim DatasArr(intRowsCount - 1, intColumnCount - 1)
            Dim lstIdx As Integer = 0
            For j = 0 To UBound(DatasArr, 1)
                For i = 0 To UBound(DatasArr, 2)
                    DatasArr(j, i) = lstReader.Item(lstIdx)
                    lstIdx += 1
                Next
            Next

            RowsAffected = intRowsCount

            sqlReader.Close()

            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL查询语句并将结果集存入DataTable对象（仅返回第一个结果集）
    ''' </summary>
    ''' <param name="sqlStr">SQl语句文本</param>
    Public Sub SelectToDt(ByVal sqlStr As String)
        Dim sqlReader As Object

        '变量初始化
        RowsAffected = -1
        DatasDt = New DataTable

        Try
            sqlCmd = New MySqlCommand(sqlStr, sqlConn, sqlTran)

            '执行SQL语句
            sqlReader = sqlCmd.ExecuteReader()

            '判断是否有结果
            If sqlReader.HasRows = False Then
                RowsAffected = 0
            End If

            DatasDt.Load(sqlReader)

            RowsAffected = DatasDt.Rows.Count

            sqlReader.Close()

            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL查询语句并将结果集存入DataSet对象（返回所有结果集）
    ''' </summary>
    ''' <param name="sqlStr">SQl语句文本</param>
    Public Sub SelectToDs(ByVal sqlStr As String)
        '变量初始化
        RowsAffected = -1
        DatasDs = New DataSet

        Try
            sqlCmd = New MySqlCommand(sqlStr, sqlConn, sqlTran)

            Dim sda As MySqlDataAdapter = New MySqlDataAdapter(sqlCmd)

            sda.Fill(DatasDs)

            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 释放资源，关闭数据库连接
    ''' </summary>
    Public Sub Close()
        If IsNothing(sqlCmd) = False Then
            sqlCmd = Nothing
        End If

        If IsNothing(DatasDt) = False Then
            DatasDt = Nothing
        End If

        If IsNothing(DatasDs) = False Then
            DatasDs = Nothing
        End If

        If IsNothing(sqlConn) = False Then
            sqlConn.Close()
        End If
    End Sub
End Class

MySQLHelper 示例

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.MinimumSize = New Size(1000, 500)
    End Sub
    
    Private Sub Button21_Click(sender As Object, e As EventArgs) Handles Button21.Click
        'MySQL 数据库操作示例：执行SQL查询语句并将结果集存入数组（仅返回第一个结果集）

        Dim sqlHelper As MySQLHelper

        sqlHelper = New MySQLHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        sqlHelper.SelectToArray("SELECT * FROM test LIMIT 10;")

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected & vbCrLf & "sqlHelper.ColsArr(0): " & sqlHelper.ColsArr(0))

        sqlHelper.Close()
    End Sub

    Private Sub Button22_Click(sender As Object, e As EventArgs) Handles Button22.Click
        'MySQL 数据库操作示例：执行SQL查询语句并将结果集存入DataTable对象（仅返回第一个结果集）

        Dim sqlHelper As MySQLHelper

        sqlHelper = New MySQLHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        sqlHelper.SelectToDt("SELECT * FROM test ORDER BY id LIMIT 200;")

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        DataGridView1.DataSource = sqlHelper.DatasDt
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        'DataGridView1.Refresh()

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected)

        sqlHelper.Close()
    End Sub

    Private Sub Button23_Click(sender As Object, e As EventArgs) Handles Button23.Click
        'MySQL 数据库操作示例：执行SQL增删改语句

        Dim sqlHelper As MySQLHelper

        sqlHelper = New MySQLHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        Dim sqlStrs As ArrayList = New ArrayList From {
            "UPDATE test SET dt = NOW() WHERE id = 1;",
            "UPDATE test SET dt = NOW() WHERE id = 2;"
        }
        sqlHelper.IUD(sqlStrs)

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected)

        sqlHelper.Close()
    End Sub

    Private Sub Button24_Click(sender As Object, e As EventArgs) Handles Button24.Click
        'MySQL 数据库操作示例：执行SQL查询语句并将结果集存入DataSet对象（返回所有结果集）

        Dim sqlHelper As MySQLHelper

        sqlHelper = New MySQLHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        'sqlHelper.SelectToDs("SELECT * FROM test LIMIT 20;SELECT * FROM test WHERE 1 = 11;SELECT * FROM test LIMIT 30;")

        sqlHelper.SelectToDs("CALL p_get_multi_res();")

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        Dim tabPage As TabPage
        Dim dataGV As DataGridView
        For i = 0 To sqlHelper.DatasDs.Tables.Count - 1
            tabPage = New TabPage
            dataGV = New DataGridView
            With tabPage
                .Name = "Result" & i + 1
                .Text = .Name
                With dataGV
                    .Name = "dataGV" & i + 1
                    .Dock = DockStyle.Fill
                    .DataSource = sqlHelper.DatasDs.Tables(i)
                    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                End With
                .Controls.Add(dataGV)
            End With
            TabControl1.Controls.Add(tabPage)
        Next

        MsgBox("sqlHelper.DatasDs.Tables.Count: " & sqlHelper.DatasDs.Tables.Count)

        sqlHelper.Close()
    End Sub
End Class

SQLiteHelper

'作者：刮骨剑
'日期：2021-07-07
'使用说明：
'1、在网站（http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki）下载对应工程 .NET 版本的预编译包，
'   建议使用 32 位版本，如：Precompiled Binaries For 32-bit Windows (.NET Framework 4.5.1) 列表下的
'   sqlite-netFx46-binary-Win32-2015-1.0.114.0.zip
'2、把压缩包中的 System.Data.SQLite.dll、SQLite.Interop.dll 文件放进工程的生成输出路径下（如 bin\Debug\）。
'3、添加引用 System.Data.SQLite.dll。
Imports System.Data.SQLite
''' <summary>
''' SQLite 数据库操作类
''' </summary>
Public Class SQLiteHelper
    ''' <summary>
    ''' 数据库连接配置
    ''' </summary>
    Private dbCfgs As Dictionary(Of String, Object) = New Dictionary(Of String, Object)

    ''' <summary>
    ''' 数据库连接对象
    ''' </summary>
    Private ReadOnly sqlConn As SQLiteConnection

    ''' <summary>
    ''' SQl语句对象
    ''' </summary>
    Private sqlCmd As SQLiteCommand

    ''' <summary>
    ''' 事务控制对象
    ''' </summary>
    Private sqlTran As SQLiteTransaction

    ''' <summary>
    ''' 数据库操作状态
    ''' </summary>
    Public Property SQLStatus As Boolean

    ''' <summary>
    ''' 影响行数
    ''' </summary>
    Public Property RowsAffected As Integer = -1

    ''' <summary>
    ''' 字段集数组
    ''' </summary>
    Public Property ColsArr() As String()

    ''' <summary>
    ''' 数据集数组
    ''' </summary>
    Public Property DatasArr() As String(,)

    ''' <summary>
    ''' 数据集（DataTable）
    ''' </summary>
    Public Property DatasDt As New DataTable

    ''' <summary>
    ''' 数据集（DataSet）
    ''' </summary>
    Public Property DatasDs As New DataSet

    ''' <summary>
    ''' 数据库连接配置初始化
    ''' </summary>
    Private Sub DBCfgInit()
        Dim dbCfgId As String    '数据库连接配置ID

        '配置示例
        dbCfgId = "test"
        dbCfgs(dbCfgId) = New Dictionary(Of String, String) From {
            {"dbAddress", IO.Directory.GetCurrentDirectory & "\sqlite.db"}
        }
    End Sub

    ''' <summary>
    ''' 创建 SQLiteHelper 实例
    ''' </summary>
    ''' <param name="dbCfgId">数据库连接配置ID</param>
    Public Sub New(ByVal dbCfgId As String)
        Dim sqlConnStr As String
        Dim dbAddress As String '数据库连接地址

        '数据库连接配置初始化
        Call DBCfgInit()

        '获取数据库配置
        If dbCfgs.ContainsKey(dbCfgId) Then
            dbAddress = dbCfgs(dbCfgId)("dbAddress")
        Else
            SQLStatus = False
            MsgBox("数据库连接信息配置 " & dbCfgId & " 不存在！", 16, "错误")
            Exit Sub
        End If

        sqlConnStr = "Data Source=" & dbAddress & ";"

        Try
            '连接并打开数据库
            sqlConn = New SQLiteConnection(sqlConnStr)
            sqlConn.Open()
            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False
            MsgBox("数据库连接错误！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")
            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL增删改语句
    ''' </summary>
    ''' <param name="sqlStrs">SQl语句文本列表</param>
    Public Sub IUD(ByVal sqlStrs As ArrayList)
        Dim sqlStr As String
        Dim rowsAff As Integer

        '变量初始化
        RowsAffected = -1

        Try
            '开启事务
            sqlTran = sqlConn.BeginTransaction()

            For Each sqlStr In sqlStrs
                sqlCmd = New SQLiteCommand(sqlStr, sqlConn, sqlTran)

                rowsAff = sqlCmd.ExecuteNonQuery()

                Console.WriteLine(rowsAff)

                RowsAffected += rowsAff
            Next

            RowsAffected += 1

            '提交事务
            sqlTran.Commit()

            SQLStatus = True
        Catch ex As Exception
            '回滚事务
            sqlTran.Rollback()

            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL查询语句并将结果集存入数组（仅返回第一个结果集）
    ''' </summary>
    ''' <param name="sqlStr">SQl语句文本</param>
    Public Sub SelectToArray(ByVal sqlStr As String)
        Dim sqlReader As Object

        '变量初始化
        RowsAffected = -1
        ColsArr = Nothing
        DatasArr = Nothing

        Try
            sqlCmd = New SQLiteCommand(sqlStr, sqlConn, sqlTran)

            '执行SQL语句
            sqlReader = sqlCmd.ExecuteReader()

            '判断是否有结果
            If sqlReader.HasRows = False Then
                RowsAffected = 0
            End If

            '注：因为 SqlDataReader 是一条一条语句的读取，只能获取列数，不能获取行数，因此需要将总的记录除以列数才能获取行数。

            ' 定义函数返回数组的列数和行数
            Dim lstReader As New List(Of String)
            Dim intColumnCount As Integer = sqlReader.FieldCount
            Dim intRowsCount As Integer

            '将字段集存入数组
            ReDim ColsArr(intColumnCount - 1)
            For i = 0 To intColumnCount - 1
                ColsArr(i) = sqlReader.GetName(i)
            Next

            '将数据集存入 lstReader 列表
            While sqlReader.Read()
                For i = 0 To intColumnCount - 1
                    lstReader.Add(sqlReader.GetValue(i).ToString)   '如果字段值是 NULL，则返回空字符串
                Next
            End While

            '获取数据集的行数
            intRowsCount = lstReader.Count / intColumnCount

            '将数据集存入二维数组
            ReDim DatasArr(intRowsCount - 1, intColumnCount - 1)
            Dim lstIdx As Integer = 0
            For j = 0 To UBound(DatasArr, 1)
                For i = 0 To UBound(DatasArr, 2)
                    DatasArr(j, i) = lstReader.Item(lstIdx)
                    lstIdx += 1
                Next
            Next

            RowsAffected = intRowsCount

            sqlReader.Close()

            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL查询语句并将结果集存入DataTable对象（仅返回第一个结果集）
    ''' </summary>
    ''' <param name="sqlStr">SQl语句文本</param>
    Public Sub SelectToDt(ByVal sqlStr As String)
        Dim sqlReader As Object

        '变量初始化
        RowsAffected = -1
        DatasDt = New DataTable

        Try
            sqlCmd = New SQLiteCommand(sqlStr, sqlConn, sqlTran)

            '执行SQL语句
            sqlReader = sqlCmd.ExecuteReader()

            '判断是否有结果
            If sqlReader.HasRows = False Then
                RowsAffected = 0
            End If

            DatasDt.Load(sqlReader)

            RowsAffected = DatasDt.Rows.Count

            sqlReader.Close()

            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 执行SQL查询语句并将结果集存入DataSet对象（返回所有结果集）
    ''' </summary>
    ''' <param name="sqlStr">SQl语句文本</param>
    Public Sub SelectToDs(ByVal sqlStr As String)
        '变量初始化
        RowsAffected = -1
        DatasDs = New DataSet

        Try
            sqlCmd = New SQLiteCommand(sqlStr, sqlConn, sqlTran)

            Dim sda As SQLiteDataAdapter = New SQLiteDataAdapter(sqlCmd)

            sda.Fill(DatasDs)

            SQLStatus = True
        Catch ex As Exception
            SQLStatus = False

            MsgBox("数据库操作失败！" & vbCrLf & "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description, 16, "错误")

            Exit Sub
        End Try
    End Sub

    ''' <summary>
    ''' 释放资源，关闭数据库连接
    ''' </summary>
    Public Sub Close()
        If IsNothing(sqlCmd) = False Then
            sqlCmd = Nothing
        End If

        If IsNothing(DatasDt) = False Then
            DatasDt = Nothing
        End If

        If IsNothing(DatasDs) = False Then
            DatasDs = Nothing
        End If

        If IsNothing(sqlConn) = False Then
            sqlConn.Close()
        End If
    End Sub
End Class

SQLiteHelper 示例

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.MinimumSize = New Size(1000, 500)
    End Sub

    Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click
        'SQLite 数据库操作示例：执行SQL查询语句并将结果集存入数组（仅返回第一个结果集）

        Dim sqlHelper As SQLiteHelper

        sqlHelper = New SQLiteHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        sqlHelper.SelectToArray("SELECT * FROM test LIMIT 10;")

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected & vbCrLf & "sqlHelper.ColsArr(0): " & sqlHelper.ColsArr(0))

        sqlHelper.Close()
    End Sub

    Private Sub Button12_Click(sender As Object, e As EventArgs) Handles Button12.Click
        'SQLite 数据库操作示例：执行SQL查询语句并将结果集存入DataTable对象（仅返回第一个结果集）

        Dim sqlHelper As SQLiteHelper

        sqlHelper = New SQLiteHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        sqlHelper.SelectToDt("SELECT * FROM test ORDER BY id LIMIT 200;")

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        DataGridView1.DataSource = sqlHelper.DatasDt
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        'DataGridView1.Refresh()

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected)

        sqlHelper.Close()
    End Sub

    Private Sub Button13_Click(sender As Object, e As EventArgs) Handles Button13.Click
        'SQLite 数据库操作示例：执行SQL增删改语句

        Dim sqlHelper As SQLiteHelper

        sqlHelper = New SQLiteHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        Dim sqlStrs As ArrayList = New ArrayList From {
            "UPDATE test SET dt = DATETIME('now', 'localtime') WHERE id = 1;",
            "UPDATE test SET dt = DATETIME('now', 'localtime') WHERE id = 2;"
        }
        sqlHelper.IUD(sqlStrs)

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected)

        sqlHelper.Close()
    End Sub

    Private Sub Button14_Click(sender As Object, e As EventArgs) Handles Button14.Click
        'SQLite 数据库操作示例：执行SQL查询语句并将结果集存入DataSet对象（返回所有结果集）

        Dim sqlHelper As SQLiteHelper

        sqlHelper = New SQLiteHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        sqlHelper.SelectToDs("SELECT * FROM test LIMIT 20;SELECT * FROM test WHERE 1 = 11;SELECT * FROM test LIMIT 30;")

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        Dim tabPage As TabPage
        Dim dataGV As DataGridView
        For i = 0 To sqlHelper.DatasDs.Tables.Count - 1
            tabPage = New TabPage
            dataGV = New DataGridView
            With tabPage
                .Name = "Result" & i + 1
                .Text = .Name
                With dataGV
                    .Name = "dataGV" & i + 1
                    .Dock = DockStyle.Fill
                    .DataSource = sqlHelper.DatasDs.Tables(i)
                    .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                End With
                .Controls.Add(dataGV)
            End With
            TabControl1.Controls.Add(tabPage)
        Next

        MsgBox("sqlHelper.DatasDs.Tables.Count: " & sqlHelper.DatasDs.Tables.Count)

        sqlHelper.Close()
    End Sub

    Private Sub Button15_Click(sender As Object, e As EventArgs) Handles Button15.Click
        'SQLite 数据库操作示例：执行SQL增删改语句，初始化数据库

        Dim sqlHelper As SQLiteHelper

        sqlHelper = New SQLiteHelper("test")

        If Not sqlHelper.SQLStatus Then
            Exit Sub
        End If

        Dim sqlStrs As ArrayList = New ArrayList From {
            "DROP TABLE IF EXISTS test;",
            "CREATE TABLE test (
	            id INTEGER PRIMARY KEY AUTOINCREMENT,
	            code TEXT,
	            dt DATETIME DEFAULT (DATETIME('now', 'localtime'))
            );",
            "INSERT INTO test (code) VALUES ('aaa'), ('bbb'), ('ccc'), ('ddd');",
            "INSERT INTO test (code) VALUES ('eee');INSERT INTO test (code) VALUES ('fff');"
        }
        sqlHelper.IUD(sqlStrs)

        If Not sqlHelper.SQLStatus Then
            sqlHelper.Close()
            Exit Sub
        End If

        MsgBox("sqlHelper.RowsAffected: " & sqlHelper.RowsAffected)

        sqlHelper.Close()
    End Sub
End Class

刮骨剑

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
VB.NET SQLHelper（支持 MSSQL、MySQL、SQLite）

MSSQLHelper'作者：刮骨剑'日期：2021-07-06Imports System.Data.SqlClient''' <summary>''' MSSQL 数据库操作类''' </summary>Public Class MSSQLHelper ''' <summary> ''' 数据库连接配置 ''' </summary> Private dbCfgs As Dictionary(Of Strin
复制链接

扫一扫

专栏目录