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

原创已于 2023-03-13 13:07:41 修改 · 651 阅读

本内容遵循CC 4.0 BY-SA版权协议

于 2023-03-13 13:06:24 首次发布

#Region "DataSet操作"

    ''' <summary>
    ''' SqlCommandBuilder动态生成，带事务处理
    ''' </summary>
    ''' <param name="dataset"></param>
    ''' <param name="datatablename"></param>
    ''' <param name="selectStr"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' 常见问题：
    ''' 1、当违反 "违反了 PRIMARY KEY 约束" 时，猜测datatable()中必须有主键
    ''' 2、Update 无法找到 TableMapping['test'] 或 DataTable“test”，原因可以检查 XX.TableName 这里，是否复制错误
    ''' 注意：这里需要拼接成跟DATATABLE的 order_body所选择的列 一样
    ''' 否则：对于不返回任何键列信息的selectcommand
    ''' </remarks>
    Public Shared Function TransDataSetUpdate(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

            'dataset.AcceptChanges()

            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

#End Region

上面我放在了sqlhelper里，下面是DAL与BLL层的引用

Imports System.Windows.Forms

Public NotInheritable Class Dal
    Inherits EZSystem.sqlhelper

    ''' <summary>
    ''' 数据集
    ''' </summary>
    ''' <param name="ds">数据集变量</param>
    ''' <param name="dtNameArray">表名数组变量数组</param>
    ''' <returns>
    ''' 功能：把窗体所需要的数据表一次性添加到数据集
    ''' </returns>
    Public Shared Function EZDataSet(ByVal ds As DataSet, ByVal dtNameArray() As String) As DataSet

        Try

            For i As Integer = 0 To dtNameArray.Count - 1
                Dim str As String = SqlScriptString(AppDomain.CurrentDomain.BaseDirectory & "\Sqlscripts\" & dtNameArray(i) & ".sql")
                Dim sqlstring As String = str
                Dim dt As DataTable = FillDataTabe(sqlstring)
                dt.TableName = dtNameArray(i)
                ds.Tables.Add(dt)
            Next

            Return ds
        Catch ex As Exception
            Return Nothing
            'MsgShow(ex.ToString)
        End Try

    End Function

    ''' <summary>
    ''' 提交更新数据集
    ''' </summary>
    ''' <param name="ds">数据集变量</param>
    ''' <param name="dtNameArray">表名数组变量</param>
    ''' <returns>
    ''' 提示：数据集里的表名已经固定，表名默认值与文件夹里的sql脚本文件名相同
    ''' </returns>
    Public Shared Function EZDataSetUpdate(ByVal ds As DataSet, ByVal dtNameArray() As String) As Boolean

        Try
            '定义sql语句数组
            Dim sqlstr() As String
            '向数组中动态添加元素
            Dim strList As New List(Of String)()
            For i As Integer = 0 To dtNameArray.Count - 1
                Dim str As String = SqlScriptString(AppDomain.CurrentDomain.BaseDirectory & "\Sqlscripts\" & dtNameArray(i) & ".sql")
                strList.Add(str) '循环添加元素
            Next
            sqlstr = strList.ToArray()

            If TransDataSetUpdate(ds, dtNameArray, sqlstr) = True Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Return False
        End Try

    End Function

End Class

Public NotInheritable Class Bll

    ''' <summary>
    ''' 数据集
    ''' </summary>
    ''' <param name="ds">数据集变量</param>
    ''' <param name="dtNameArray">表名数组变量数组</param>
    ''' <returns>
    ''' 功能：把窗体所需要的数据表一次性添加到数据集
    ''' </returns>
    Public Shared Function EZDataSet(ByVal ds As DataSet, ByVal dtNameArray() As String) As DataSet

        Return Dal.EZDataSet(ds, dtNameArray)

    End Function

    ''' <summary>
    ''' 提交更新数据集
    ''' </summary>
    ''' <param name="ds">数据集变量</param>
    ''' <param name="dtNameArray">表名数组变量</param>
    ''' <returns>
    ''' 提示：数据集里的表名已经固定，表名默认值与文件夹里的sql脚本文件名相同
    ''' </returns>
    Public Shared Function EZDataSetUpdate(ByVal ds As DataSet, ByVal dtNameArray() As String) As Boolean

        Return Dal.EZDataSetUpdate(ds, dtNameArray)

    End Function

End Class

在窗体使用

xml文件中存储该窗体所要操作的DataSet中的表名，表名默认与sqlscript脚本名相同，这样只需要一个表名数组参数即可完成增删改查批量操作。

#Region "窗体事件"
    Public Sub New()

        ' 此调用是设计器所必需的。
        InitializeComponent()

        ' 在 InitializeComponent() 调用之后添加任何初始化。
        DataGridView1.DoubleBufferedDataGirdView(True)

        xmlpath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase & "formSettings\医书.xml"

    End Sub

    Private Sub LoadData()
        ds1.Clear()
        ds1.ReadXml(xmlpath)
        'DataGridView1.DataSource = ds.Tables("TreeTable")
    End Sub

#End Region

    Private Sub BaseMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '如果是继承窗体，请加上以下代码
        '服务容器中已存在服务(System.Windows.Forms.Design.IEventHandlerService) '
        If (DesignMode) Then Exit Sub

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'xmlpath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase & "formSettings\病案.xml"
        LoadData()

        Me.Size = New System.Drawing.Size(ds1.Tables("FormSetting").Rows(0)("宽").ToString, ds1.Tables("FormSetting").Rows(0)("高").ToString)
        Me.Name = ds1.Tables("FormSetting").Rows(0)("窗体名").ToString
        Me.Text = ds1.Tables("FormSetting").Rows(0)("标题").ToString

        '定义表名List
        Dim strList As New List(Of String)()
        For i As Integer = 0 To ds1.Tables("TreeNode").Rows.Count - 1

            Dim str As String = ds1.Tables("TreeTable").Rows(i)("病案表").ToString
            strList.Add(str) '循环添加元素，把表名添加到表名List中

        Next
        dtNameArray = strList.ToArray() '把表名List转换为表名数组
        ds = Bll.EZDataSet(ds, dtNameArray)

        Me.StartPosition = FormStartPosition.CenterScreen
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        DataGridView1.DataSource = ds.Tables(0)

    End Sub

窗体启动后显示画面如下

添加与更新按钮功能如下：

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If Bll.EZDataSetUpdate(ds, dtNameArray) Then
            MsgBox("True")
        Else
            MsgBox("False")
        End If
End Sub

下面是一个防止DataGridView滚动条拖动时卡顿的扩展，要放到模块里：

Public Module DoubleBufferDataGridView
    ''' <summary>
    ''' 双缓冲，解决闪烁问题
    ''' </summary>
    <System.Runtime.CompilerServices.Extension>
    Public Sub DoubleBufferedDataGirdView(ByVal dgv As DataGridView, ByVal flag As Boolean)
        Dim dgvType As Type = dgv.GetType()
        Dim pi As PropertyInfo = dgvType.GetProperty("DoubleBuffered", BindingFlags.Instance Or BindingFlags.NonPublic)
        pi.SetValue(dgv, flag, Nothing)
    End Sub
End Module

结束，以上是主要代码

标签