从excel导入大量数据到DataGridView并保存到数据库中
问题描述:
我有大约12万个excel数据,我需要导入到DataGridView然后将其保存到SQL Server数据库。从excel导入大量数据到DataGridView并保存到数据库中
将excel数据分解为1000组数据并将其保存到数据库的问题显而易见的是,UI冻结。现在我甚至无法想象一次导入所有的一万二千条数据,那么上帝保佑我!
我该如何实现这个任务而不会冻结问题?我试着搜索并发现了关于DataGridView virtualmode属性。我如何正确实施这一点,帮助我找到正确的方向。
答
'图书馆
Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.VisualBasic.FileIO
Imports System.Data
Imports System.Data.Odbc
Imports System.Data.OleDb
' LOAD DATA网格视图
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
Dim cnn As SqlConnection
Dim connectionString As String
Dim sql As String
connectionString = "data source=Excel-PC\SQLEXPRESS;" & _
"initial catalog=NORTHWIND;Trusted_Connection=True"
cnn = New SqlConnection(connectionString)
cnn.Open()
sql = "SELECT * FROM Orders"
Dim dscmd As New SqlDataAdapter(sql, cnn)
Dim ds As New DataSet
dscmd.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
cnn.Close()
End Sub
“从数据网格视图到SQL Server
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim headers = (From header As DataGridViewColumn In DataGridView1.Columns.Cast(Of DataGridViewColumn)() Select header.HeaderText).ToArray
Dim rows = From row As DataGridViewRow In DataGridView1.Rows.Cast(Of DataGridViewRow)() Where Not row.IsNewRow Select Array.ConvertAll(row.Cells.Cast(Of DataGridViewCell).ToArray, Function(c) If(c.Value IsNot Nothing, c.Value.ToString, ""))
Dim str As String = ""
Using sw As New IO.StreamWriter("C:\Users\Excel\Desktop\OrdersTest.csv")
sw.WriteLine(String.Join(",", headers))
'sw.WriteLine(String.Join(","))
For Each r In rows
sw.WriteLine(String.Join(",", r))
Next
sw.Close()
End Using
End Sub
我不明白为什么加载过程会导致您的机器冻结。我从来没有遇到过这个问题。
如果您想要一个非常快速的方式将数据加载到SQL Server,只需使用批量插入并摆脱整个数据网格视图的事情。
BULK INSERT EmployeeDB.dbo.Employees
FROM 'C:\Data\EmployeeData_c.dat'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r\n'
);
https://www.simple-talk.com/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/
您是否尝试过使用的BackgroundWorker?它会帮助你的用户界面保持响应。 [看看](https://msdn.microsoft.com/en-us/library/system.componentmodel.backgroundworker(v = vs.110).aspx) –
@SDP,如果你使用线程for它。 –
@MAdeelKhalid我会研究它,谢谢:) – Sdp