- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- 高速导入数据库报错：provider (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=122885)
-- 发布时间：2018/8/3 16:08:00
-- 高速导入数据库报错：provider
导入按钮代码如下：
此主题相关图片如下：qq截图20180803160521.png
此主题相关图片如下：qq截图20180803160521.pngDim count As Integer = e.Form.Controls("cboPer").value
If count = 0 Then
msgbox("请输入一个大于0 的合适批数.")
Return
End If
Output.Show("count=" & count)
Dim allCount As Integer = e.Form.Controls("cboAllCount").value
If allCount = 0 OrElse allCount > 1000000 Then
msgbox("请输入一个大于0 的合适总数(最大100W).")
Return
End If
Output.Show("allCount=" & allCount)
Dim ccbCols As WinForm.CheckedComboBox = e.Form.Controls("ccbCols")
Dim lst As new List(of String)
If ccbCols.Text > "" Then
lst.AddRange( ccbCols.text.Split(","))
Else
lst.AddRange(new String() {"ID","CreatedTime","UserName","Phone","Account","Balance","Description"})
End If
Dim cols() As String = lst.ToArray
Dim dt As new System.Data.DataTable
Dim dlg As New OpenFileDialog \'定义一个新的OpenFileDialog
dlg.Filter= "Excel2007文件|*.xlsx" \'设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then \'如果用户单击了确定按钮
Dim d1 As Date = Date.Now
Dim constring As String = Vars("c1")
using conn As System.Data.SqlClient.SqlConnection = new System.Data.SqlClient.SqlConnection(constring)
conn.Open() \'打开链接
Output.Show("打开数据库链接")
using copy = new System.Data.SqlClient.SqlBulkCopy(conn)
copy.Destinati \'指定服务器上目标表的名称
Dim cls() As String = {"ID","CreatedTime","UserName","Phone","Account","Balance","Description"}
For Each cl As String In cls
copy.ColumnMappings.Add(cl,cl)
Next
copy.BatchSize = 10000
copy.BulkCopyTimeout = 60
Output.Show("开始导入数据")
Dim _execl As New ExOpenXml.ExOXExecl()
try
Dim page As Integer = Math.Ceiling(allCount / count) - 1
Dim lastcount As Integer = allCount Mod count
For i As Integer = 0 To page
Dim idx As Integer = i*count
Output.Show("idx=" & idx)
_execl.Open(dlg.FileName,True) \'不需要保存文档,第二个参数设置为True
dt = _execl.ReadToDataTable( ,cols , idx , IIF(i<page OrElse lastcount=0,count,lastcount))
Output.Show(i & "readend=" & Format(Date.Now,"mm:ss.ffff"))
If dt IsNot Nothing Then
Output.Show("dt.Rows.count=" & dt.Rows.count)
copy.WriteToServer(dt)
Output.Show(i & "saveend=" & Format(Date.Now,"mm:ss.ffff"))
End If
Next
Finally
_execl.dispose
End Try
End using
End using
Dim tp As TimeSpan = now() - d1
msgbox("合并导入成功,用时: " & tp.TotalSeconds & "秒.")
GC.Collect()
End If
[此贴子已经被作者于2018/8/3 16:08:56编辑过]
-- 发布时间：2018/8/3 17:28:00
--
你这个代码的值是什么？
Dim constring As String = Vars("c1")
msgbox(constring) 弹出什么？
用下面红色的字符串
Dim conn As new System.Data.SqlClient.SqlConnection("server=voyo;uid=sa;pwd=hailun.;database=Test")
-- 发布时间：2018/8/3 17:56:00
--
这个是开发文件，包含了6万条的导入数据excl进行测试。您看下
[此贴子已经被作者于2018/8/3 17:58:03编辑过]
-- 发布时间：2018/8/3 18:00:00
--
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:不含收银插件快捷菜单已修改text.zip |
-- 发布时间：2018/8/3 18:23:00
--
1、认认真真看2楼；
2、用下面红色的字符串
Dim conn As new System.Data.SqlClient.SqlConnection("server=UHUQA8ATNZW7Q3Q;uid=sa;pwd=sa1;database=demo1")
-- 发布时间：2018/8/3 21:22:00
--
谢谢，问题解决了。我修改的c1变量直接从外部数据库连接器代码复制的。