以下我单条插入到数据库的代码,大家能不能修改使其批量插入:
If MsgBox("是否要更新到数据库?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
If sqlconn.State <> ConnectionState.Open Then
sqlconn.Open()
End If
Dim MyAdapter As New SqlDataAdapter
Dim sql As String = "insert into attend(empid,in_date,in_time) values" _
& "( '" & Me.DataGrid1.Item(Me.DataGrid1.CurrentRowIndex, 0) & "'," _
& "'" & Me.DataGrid1.Item(Me.DataGrid1.CurrentRowIndex, 1) & "'," _
& "'" & Me.DataGrid1.Item(Me.DataGrid1.CurrentRowIndex, 2) & "')"
MyAdapter.InsertCommand = New SqlCommand(sql, sqlconn)
MyAdapter.InsertCommand.ExecuteNonQuery()
End If
可以先用一個FOR循環把表格裡的數據寫到一個臨時表,然後再把臨時表的記錄批次INSERT到數據庫中。
如:
Create Table #A(A int,B Nvarchar(50)).....
For i=0 to DataTable.rows.count-1
....
Insert Into #A
Next
这是我自己做的一个例子,但执行不下:例数第三行有错
Dim dt As DataTable = New DataTable
Dim dr As DataRow
dt.Columns.Add(New DataColumn("name"))
Dim j As Integer
For j = 0 To 10
dr = dt.NewRow()
dr(0) = "name" + j.ToString
dt.Rows.Add(dr)
Next
Me.DataGrid1.DataSource = dt
Dim MyAdapter As New SqlDataAdapter
Dim cmd As New SqlCommand("Insert Into attend values (@name)", sqlconn)
cmd.Parameters.Item("@name").SourceColumn = dt.Columns("name").ColumnName
MyAdapter.Update(dt)
sqlconn.Close()