从未绑定的数据网格视图创建数据表 [英] Create datatable from unbound datagridview

问题描述

我想要一个创建数据表的按钮，然后将其批量复制到SQL数据库.我已经能够合并从SQL表创建的数据表开始的数据表，但是我不能仅仅从头开始创建数据表.

I want a button that creates a datatable to then bulk copy to a SQL database. I have been able to merge datatables starting with a datatable created from the SQL table, but I have not been able to just make a datatable from scratch.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

    Dim table As New DataTable
    Dim row As DataRow

    Dim TableName As String = "SQLLocation_"

    table.Columns.Add(TableName & "Number", GetType(Int64))
    table.Columns.Add(TableName & "AnotherNumber", GetType(Int16))
    table.Columns.Add(TableName & "Name", GetType(String))
    table.Columns.Add(TableName & "Port", GetType(Int32))
    table.Columns.Add(TableName & "OnOff", GetType(Boolean))

    For Each drthree As DataGridViewRow In DataGridView3.Rows
        row = table.NewRow 'Create new row
        table.Rows.Add(row)
    Next

End Sub

此代码仅创建一堆空白行.最终，我将添加此代码以创建新表.

This code just creates a bunch of blank rows. Eventually I will add on this code to create the new table.

Using destinationConnection As SqlConnection = _
                   New SqlConnection(sConnectionString)
        destinationConnection.Open()

        ' Set up the bulk copy object.  
        ' The column positions in the source data reader  
        ' match the column positions in the destination table,  
        ' so there is no need to map columns. 

        Using bulkCopy As SqlBulkCopy = _
          New SqlBulkCopy(destinationConnection)
            bulkCopy.DestinationTableName = _
            "dbo.SQLLocation_Tbl"

            Try
                ' Write from the source to the destination.
                bulkCopy.WriteToServer(table)

            Catch ex As Exception
                MessageBox.Show(ex.ToString, _
            "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                System.Threading.Thread.CurrentThread.Abort()
            Finally
                ' Close the SqlDataReader. The SqlBulkCopy 
                ' object is automatically closed at the end 
                ' of the Using block.
            End Try
        End Using

    End Using
End Sub

推荐答案

这将允许您遍历datagridview并根据其列和行创建表.它将在数据表中创建具有相同名称的列，而不是看起来像您尝试这样做.

This will allow you to iterate through a datagridview and create a table based on its columns and rows. It would create columns in the datatable with the same names, rather than it looks like you were trying to do.

        Dim table As New DataTable()
        For Each col As DataGridViewColumn In dgv.Columns
            table.Columns.Add(col.Name, col.ValueType)
            table.Columns(col.Name).Caption = col.HeaderText
        Next

        For Each row As DataGridViewRow In dgv.Rows
            Dim drNewRow As DataRow = table.NewRow()
            For Each col As DataColumn In table.Columns
                drNewRow(col.ColumnName) = row.Cells(col.ColumnName).Value
            Next
            table.Rows.Add(drNewRow)
        Next

如果要求使用其他名称，则可以保留当前创建列的方法，但是当您遍历创建每一行时，您将必须知道哪些列值与所需的列匹配并映射它们，而不是使用它们内部的每个人.

If a different name is a requirement you can retain your current method of creating the columns, but then when you loop through creating each row you will have to know which columns values match the ones you want and map them instead of using that inner For Each.

类似的东西:

  drNewRow("SQLLocation_Number") = row.cell(dgv.Columns(0).ColumnName).Value

这篇关于从未绑定的数据网格视图创建数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！