問題描述

問題:

我需要將 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 內容插入數據庫的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！