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

Question

问题:

我需要将 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

我在这里错过了什么？没有任何东西被插入我的 table 。任何帮助，将不胜感激。就像我说的，我对 VB 不是很熟悉，所以别着急。

Answer 1

嗯，你需要执行命令，而不是简单地添加到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.Update只需一次调用即可解决插入/更新工作的方法。

使用 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)