使用SqlCommandBuilder动态生成SQL语句，实现增删改

最新推荐文章于 2024-06-16 09:39:43 发布

llwzjf

最新推荐文章于 2024-06-16 09:39:43 发布

阅读量541

点赞数

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

本文链接：https://blog.youkuaiyun.com/llwzjf/article/details/128503504

版权

VB.NET 专栏收录该内容

10 篇文章

订阅专栏

该代码示例展示了如何使用SqlCommandBuilder动态生成SQL语句并结合事务处理进行数据库的增删改操作。针对SQLite、Access和SQL Server三种数据库，通过创建DataAdapter和CommandBuilder对象，自动构建更新命令，并在事务中执行，确保数据操作的原子性。当发生异常时，事务将回滚以保持数据一致性。

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

使用SqlCommandBuilder动态生成SQL语句时，并加上事务处理，可实现增、删、改。

使用Sqlite数据库代码如下：

    ''' <summary>
    ''' 事务处理
    ''' </summary>
    ''' <param name="dataset"></param>
    ''' <param name="datatablename"></param>
    ''' <param name="selectStr"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    Public Shared Function TransDataSet(ByVal dataset As Data.DataSet,
                                        ByVal datatablename() As String,
                                        ByVal selectStr() As String
                                        ) As Boolean
        Try

            If cn.State <> ConnectionState.Open Then
                cn.Open()
            End If

            trans = cn.BeginTransaction

            For i As Integer = 0 To datatablename.Length - 1
                Dim adapter(datatablename.Length) As SQLiteDataAdapter
                adapter(i) = New SQLiteDataAdapter(selectStr(i), cnstring)

                Dim cmdBuilder As New SQLiteCommandBuilder(adapter(i))

                '创建 SqlDataAdapter 对像的 Command 对像，并将连接对像及事务对像绑定到 Command 对像上
                adapter(i).DeleteCommand = New SQLiteCommand("", cn, trans)
                adapter(i).InsertCommand = New SQLiteCommand("", cn, trans)
                adapter(i).UpdateCommand = New SQLiteCommand("", cn, trans)
                adapter(i).SelectCommand = New SQLiteCommand(selectStr(i), cn, trans)

                '使用 GetDeleteCommand 将相对应的 SQLCOMMAND 对像传入
                adapter(i).DeleteCommand = cmdBuilder.GetDeleteCommand()
                adapter(i).InsertCommand = cmdBuilder.GetInsertCommand()
                adapter(i).UpdateCommand = cmdBuilder.GetUpdateCommand()

                '调用 RefreshSchema 方法会有效地移除 DbCommand 由引用的所有对象 SelectCommand
                cmdBuilder.RefreshSchema()

                '更新数据库，返回所影响的行数
                Dim val As Integer = adapter(i).Update(dataset, datatablename(i))

                '更新DataSet对象所包含的表中的数据
                dataset.Tables(datatablename(i)).AcceptChanges()
            Next

            trans.Commit()
            Return True

        Catch sqliteex As SQLiteException
            Return False
            trans.Rollback()
            MessageBox.Show("sqlex操作多表数据出错，所有操作已回滚！" & vbNewLine & "错误代码：" & sqliteex.Message, _
                            System.Windows.Forms.Application.ProductName, _
                            MessageBoxButtons.OK, MessageBoxIcon.Stop, _
                            MessageBoxDefaultButton.Button1)
        Finally
            Close(cn)
        End Try
    End Function

作用Access代码如下：

    ''' <summary>
    ''' 事务处理
    ''' </summary>
    ''' <param name="dataset"></param>
    ''' <param name="datatablename"></param>
    ''' <param name="selectStr"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    Public Shared Function TransDataSet(ByVal dataset As Data.DataSet,
                                             ByVal datatablename() As String,
                                             ByVal selectStr() As String
                                             ) As Boolean
        Try

            If cn.State <> ConnectionState.Open Then
                cn.Open()
            End If

            trans = cn.BeginTransaction

            For i As Integer = 0 To datatablename.Count - 1
                Dim adapter(datatablename.Count) As OleDbDataAdapter
                adapter(i) = New OleDbDataAdapter(selectStr(i), cnstring)

                Dim cmdBuilder As New OleDbCommandBuilder(adapter(i))

                '创建 SqlDataAdapter 对像的 Command 对像，并将连接对像及事务对像绑定到 Command 对像上
                adapter(i).DeleteCommand = New OleDbCommand("", cn, trans)
                adapter(i).InsertCommand = New OleDbCommand("", cn, trans)
                adapter(i).UpdateCommand = New OleDbCommand("", cn, trans)
                adapter(i).SelectCommand = New OleDbCommand(selectStr(i), cn, trans)

                '使用 GetDeleteCommand 将相对应的 SQLCOMMAND 对像传入
                adapter(i).DeleteCommand = cmdBuilder.GetDeleteCommand()
                adapter(i).InsertCommand = cmdBuilder.GetInsertCommand()
                adapter(i).UpdateCommand = cmdBuilder.GetUpdateCommand()

                '调用 RefreshSchema 方法会有效地移除 DbCommand 由引用的所有对象 SelectCommand
                cmdBuilder.RefreshSchema()

                '更新数据库，返回所影响的行数
                Dim val As Integer = adapter(i).Update(dataset, datatablename(i))

                '更新DataSet对象所包含的表中的数据
                dataset.Tables(datatablename(i)).AcceptChanges()
            Next

            trans.Commit()
            Return True

        Catch oledbex As OleDbException
            Return False
            trans.Rollback()
            MessageBox.Show("sqlex操作多表数据出错，所有操作已回滚！" & vbNewLine & "错误代码：" & oledbex.Message, _
                            System.Windows.Forms.Application.ProductName, _
                            MessageBoxButtons.OK, MessageBoxIcon.Stop, _
                            MessageBoxDefaultButton.Button1)
        Catch ex As Exception
            Return False
            trans.Rollback()
            MessageBox.Show("ex操作多表数据出错，所有操作已回滚！" & vbNewLine & "错误代码：" & ex.Message, _
                            System.Windows.Forms.Application.ProductName, _
                            MessageBoxButtons.OK, MessageBoxIcon.Stop, _
                            MessageBoxDefaultButton.Button1)
        Finally
            Close(cn)
        End Try
    End Function

mssql数据库如下：

    ''' <summary>
    ''' 事务处理
    ''' </summary>
    ''' <param name="dataset"></param>
    ''' <param name="datatablename"></param>
    ''' <param name="selectStr"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    Public Shared Function TransDataSet(ByVal dataset As Data.DataSet,
                                             ByVal datatablename() As String,
                                             ByVal selectStr() As String
                                             ) As Boolean
        Try

            If cn.State <> ConnectionState.Open Then
                cn.Open()
            End If

            trans = cn.BeginTransaction

            For i As Integer = 0 To datatablename.Count - 1
                Dim adapter(datatablename.Count) As SqlClient.SqlDataAdapter
                adapter(i) = New SqlClient.SqlDataAdapter(selectStr(i), cnstring)

                Dim cmdBuilder As New SqlClient.SqlCommandBuilder(adapter(i))

                '创建 SqlDataAdapter 对像的 Command 对像，并将连接对像及事务对像绑定到 Command 对像上
                adapter(i).DeleteCommand = New SqlClient.SqlCommand("", cn, trans)
                adapter(i).InsertCommand = New SqlClient.SqlCommand("", cn, trans)
                adapter(i).UpdateCommand = New SqlClient.SqlCommand("", cn, trans)
                adapter(i).SelectCommand = New SqlClient.SqlCommand(selectStr(i), cn, trans)

                '使用 GetDeleteCommand 将相对应的 SQLCOMMAND 对像传入
                adapter(i).DeleteCommand = cmdBuilder.GetDeleteCommand()
                adapter(i).InsertCommand = cmdBuilder.GetInsertCommand()
                adapter(i).UpdateCommand = cmdBuilder.GetUpdateCommand()

                '调用 RefreshSchema 方法会有效地移除 DbCommand 由引用的所有对象 SelectCommand
                cmdBuilder.RefreshSchema()

                '更新数据库，返回所影响的行数
                Dim val As Integer = adapter(i).Update(dataset, datatablename(i))

                '更新DataSet对象所包含的表中的数据
                dataset.Tables(datatablename(i)).AcceptChanges()
            Next

            trans.Commit()
            Return True

        Catch sqlex As SqlException
            Return False
            trans.Rollback()
            MessageBox.Show("sqlex操作多表数据出错，所有操作已回滚！" & vbNewLine & "错误代码：" & sqlex.Message, _
                            System.Windows.Forms.Application.ProductName, _
                            MessageBoxButtons.OK, MessageBoxIcon.Stop, _
                            MessageBoxDefaultButton.Button1)
        Catch ex As Exception
            Return False
            trans.Rollback()
            MessageBox.Show("ex操作多表数据出错，所有操作已回滚！" & vbNewLine & "错误代码：" & ex.Message, _
                            System.Windows.Forms.Application.ProductName, _
                            MessageBoxButtons.OK, MessageBoxIcon.Stop, _
                            MessageBoxDefaultButton.Button1)
        Finally
            Close(cn)
        End Try
    End Function