VB.NET 将 DataGridView 内容插入数据库

Problem:

我需要将 DataGridView 的内容转储到 SQL Server 数据库表中。我的 datagridview 加载正常，没有问题。我只是对 VB.NET 不够熟悉，无法理解如何将这些数据放入数据库表中。

代码：（到目前为止）

    Dim connection As New Data.SqlClient.SqlConnection
    Dim dataAdapter As New Data.SqlClient.SqlDataAdapter
    Dim command As New Data.SqlClient.SqlCommand
    Dim dataSet As New Data.DataSet

    connection.ConnectionString = "Server= server; Database= DB; integrated security=true"
    command.CommandText = "INSERT INTO <table> (Col1, Col2, Col3, Col4) VALUES (@Name, @Property, @Value, @Date)"

    dataAdapter.InsertCommand.Parameters.Add("@ServerName", SqlDbType.VarChar)
    dataAdapter.InsertCommand.Parameters.Add("@Property", SqlDbType.VarChar)
    dataAdapter.InsertCommand.Parameters.Add("@Value", SqlDbType.VarChar)
    dataAdapter.InsertCommand.Parameters.Add("@CaptureDate", SqlDbType.DateTime)

    For i As Integer = 0 To DataGridView.Rows.Count - 1
        dataAdapter.InsertCommand.Parameters(0).Value = dgvServerConfig.Rows(i).Cells(0).Value
        dataAdapter.InsertCommand.Parameters(1).Value = dgvServerConfig.Rows(i).Cells(1).Value
        dataAdapter.InsertCommand.Parameters(2).Value = dgvServerConfig.Rows(i).Cells(2).Value
        dataAdapter.InsertCommand.Parameters(3).Value = dgvServerConfig.Rows(i).Cells(3).Value
    Next

    connection.Open()
    command.Connection = connection
    dataAdapter.SelectCommand = command

我在这里缺少什么？没有任何内容被插入到我的表中。任何帮助，将不胜感激。就像我说的，我对 VB 不太熟悉，所以别着急。

嗯，你需要执行命令，而不是简单地添加到DataAdapter
另外，正如现在编码的那样，您根本不需要 DataAdapter。

Dim connection As New Data.SqlClient.SqlConnection
Dim command As New Data.SqlClient.SqlCommand

connection.ConnectionString = "Server= server; Database= DB; integrated security=true"
command.CommandText = "INSERT INTO <table> (Col1, Col2, Col3, Col4) VALUES (@Name, @Property, @Value, @Date)"

command.Parameters.Add("@ServerName", SqlDbType.VarChar)
command.Parameters.Add("@Property", SqlDbType.VarChar)
command.Parameters.Add("@Value", SqlDbType.VarChar)
command.Parameters.Add("@CaptureDate", SqlDbType.DateTime)
connection.Open()
command.Connection = connection

For i As Integer = 0 To DataGridView.Rows.Count - 1
    command.Parameters(0).Value = dgvServerConfig.Rows(i).Cells(0).Value
    command.Parameters(1).Value = dgvServerConfig.Rows(i).Cells(1).Value
    command.Parameters(2).Value = dgvServerConfig.Rows(i).Cells(2).Value
    command.Parameters(3).Value = dgvServerConfig.Rows(i).Cells(3).Value
    command.ExecuteNonQuery()
Next

然而，这会调用数据库一次插入一行。我认为最好看看SqlDataAdapter.更新 http://msdn.microsoft.com/en-us/library/z1z2bkx2.aspx只需一次调用即可解决插入/更新工作的方法。

使用 SqlDataAdapter.Update 方法，需要将填充 DataGridView 时使用的适配器保存在全局变量中，并添加为您生成 InsertCommand、UpdateCommand 和 DeleteCommand 的 SqlCommandBuilder

    ' At form loading'
    Dim adapter As New OleDbDataAdapter()
    adapter.SelectCommand = New OleDbCommand("SELECT COL1, COL2,COL3,COL4 FROM TABLE", connection)
    Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)
    connection.Open()
    Dim myTable As DataTable = New DataTable
    adapter.Fill(myTable)
    DataGridView.DataSource = myTable
    ....

    ' at grid save'
    Dim myTable = CType(DataGridView.DataSource, DataTable)
    adapter.Update(myTable)