VB.NET 将 DataGridView 内容插入数据库
问题:
我需要将 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)
相关文章