VB.NET 将 DataGridView 内容插入数据库 [英] VB.NET Insert DataGridView contents into Database

问题描述

问题:

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

I need to dump the contents of my DataGridView into a SQL Server Database Table. I've got the datagridview loading fine, no problems there. I'm just not familiar enough with VB.NET to understand how to get that data into a DB table.

代码:(到目前为止)

    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 不是很熟悉，所以放轻松.

What am I missing here? Nothing is getting inserted into my table. Any help would be appreciated. Like I said, I'm not very familiar with VB so take it easy on me.

推荐答案

嗯，需要执行命令，而不是简单的添加到DataAdapter
此外，按照现在的编码，您根本不需要 DataAdapter.

Well, you need to execute the command, not simply add to the DataAdapter
Also, as it coded now, you don't need the DataAdapter at all.

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 方法插入/更新只需一次调用.

However this calls the database to insert one row at a time. I think it is better to look at the SqlDataAdapter.Update method that resolves the insert/update work with just one call.

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

Using the SqlDataAdapter.Update method, requires that you save in a global variable the Adapter used at the moment in which you have filled the DataGridView and add a SqlCommandBuilder that generates for you the InsertCommand, UpdateCommand and DeleteCommand

    ' 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)

这篇关于VB.NET 将 DataGridView 内容插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！