删除表依然占内存 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

公司用的ERP后台是Progress数据库，Sqlserver无法直接读取该数据库。只能用Access的ODBC把数据从Progress 用计划任务夜间下载下来。然后再把Access数据用foxtable同步到SqlServer中。大概有十几张表，少的数据5-6万行，多的有200万行。

思路是把Access的表import到foxtable中，然后用sqlbulkcopy上传到Sqlserver，上传结束后，删除foxTable中导入的表，循环导入下张表。带度还是蛮快的。但有一个问题无法解决，发现foxTable占用的内存越来越大，多达1G多，删除导入的表后，再用gc.collect也没有用。不知道有没有什么更好的方法。

导入的日志如下：

-- generated on 2017-12-19 00:50:04

2017-12-19 00:50:04 执行导入前SQL语句: EXEC proc_sqlBefore_syncData

-- generated on 2017-12-19 00:50:04

2017-12-19 00:50:04 开始读取数据源中的BOM_usage_tbl_new表

2017-12-19 00:50:15 数据源中的BOM_usage_tbl_new 表读取完成, 共289605条记录, 耗时 10.6362913秒

2017-12-19 00:50:17 完成写入目标数据库中的rbcBom表, 耗时 2.3020035秒

-- generated on 2017-12-19 00:50:18

2017-12-19 00:50:18 开始读取数据源中的ld_det表

2017-12-19 00:50:20 数据源中的ld_det 表读取完成, 共24847条记录, 耗时 2.4214889秒

2017-12-19 00:50:21 完成写入目标数据库中的stkDet表, 耗时 0.4407661秒

-- generated on 2017-12-19 00:50:22

2017-12-19 00:50:22 开始读取数据源中的mrp_det表

2017-12-19 00:50:26 数据源中的mrp_det 表读取完成, 共81506条记录, 耗时 4.4119058秒

2017-12-19 00:50:27 完成写入目标数据库中的moDemand表, 耗时 1.1707972秒

-- generated on 2017-12-19 00:50:28

2017-12-19 00:50:28 开始读取数据源中的openPO表

2017-12-19 00:50:29 数据源中的openPO 表读取完成, 共2828条记录, 耗时 0.6403498秒

2017-12-19 00:50:29 完成写入目标数据库中的qadOpenPO表, 耗时 0.060521秒

-- generated on 2017-12-19 00:50:30

2017-12-19 00:50:30 开始读取数据源中的psmstr1表

2017-12-19 00:50:39 数据源中的psmstr1 表读取完成, 共248986条记录, 耗时 9.4762974秒

2017-12-19 00:50:41 完成写入目标数据库中的psMstr表, 耗时 2.131464秒

-- generated on 2017-12-19 00:50:42

2017-12-19 00:50:42 开始读取数据源中的psmstr2表

2017-12-19 00:50:52 数据源中的psmstr2 表读取完成, 共215596条记录, 耗时 9.3853046秒

2017-12-19 00:50:54 完成写入目标数据库中的psMstr表, 耗时 1.7618462秒

-- generated on 2017-12-19 00:50:55

2017-12-19 00:50:55 开始读取数据源中的psmstr3表

2017-12-19 00:51:05 数据源中的psmstr3 表读取完成, 共231252条记录, 耗时 10.2979805秒

2017-12-19 00:51:07 完成写入目标数据库中的psMstr表, 耗时 2.0610042秒

-- generated on 2017-12-19 00:51:09

2017-12-19 00:51:09 开始读取数据源中的psmstr4表

2017-12-19 00:51:19 数据源中的psmstr4 表读取完成, 共227069条记录, 耗时 10.4679775秒

2017-12-19 00:51:21 完成写入目标数据库中的psMstr表, 耗时 1.9615127秒

-- generated on 2017-12-19 00:51:23

2017-12-19 00:51:23 开始读取数据源中的psmstr5表

2017-12-19 00:51:29 数据源中的psmstr5 表读取完成, 共144076条记录, 耗时 5.7833423秒

2017-12-19 00:51:30 完成写入目标数据库中的psMstr表, 耗时 1.2105572秒

-- generated on 2017-12-19 00:51:31

2017-12-19 00:51:31 开始读取数据源中的pt_mstr表

2017-12-19 00:51:35 数据源中的pt_mstr 表读取完成, 共58737条记录, 耗时 3.8414675秒

2017-12-19 00:51:36 完成写入目标数据库中的ptMstr表, 耗时 0.8402505秒

-- generated on 2017-12-19 00:51:38

2017-12-19 00:51:38 开始读取数据源中的rps_mstr表

2017-12-19 00:51:39 数据源中的rps_mstr 表读取完成, 共3065条记录, 耗时 0.77098秒

2017-12-19 00:51:39 完成写入目标数据库中的moMstr表, 耗时 0.0500079秒

-- generated on 2017-12-19 00:51:41

2017-12-19 00:51:41 开始读取数据源中的rpsHist表

2017-12-19 00:51:41 数据源中的rpsHist 表读取完成, 共3065条记录, 耗时 0.7543039秒

2017-12-19 00:51:42 完成写入目标数据库中的rpsHist表, 耗时 0.1365967秒

-- generated on 2017-12-19 00:51:44

2017-12-19 00:51:44 开始读取数据源中的vdmstr表

2017-12-19 00:51:45 数据源中的vdmstr 表读取完成, 共2336条记录, 耗时 0.660353秒

2017-12-19 00:51:45 写入目标数据库中的vdMstr表发生异常

Cannot insert the value NULL into column 'vdValid', table 'LeanAMD.dbo.vdMstr'; column does not allow nulls. INSERT fails.

The statement has been terminated.

-- generated on 2017-12-19 00:52:01

2017-12-19 00:52:01 执行导入后SQL语句: EXEC proc_sqlAfter_syncData

导入的程序如下：

Public Function importAllTables(optional IntPopMsg As Integer =0)

If IntPopMsg <> 0 Then Forms("进度窗口").show()

Application.DoEvents

Dim st As Date = Date.Now

Dim cmd As new SQLCommand

Dim sourcetbs As new List (of String)

Dim targettbs As new List (of String)

If FileSys.DirectoryExists(projectPath & "Logs") =False Then FileSys.CreateDirectory(projectPath & "Logs")

Vars("_logFileName") = "log" + CSTR(Format(Date.Now, "yyyyMMddHHmmss")) + ".txt"

targettbs = Connections("dbMain").GetTableNames()

For i As Integer = 0 To targettbs.Count - 1

Dim field As String = targettbs(i)

field = field.ToLower

targettbs(i) = field

cmd.C

Dim DocType As String

cmd.CommandText = "sel ect [Value] fr om dbo.CfgConstant WHERE [Key]= 'ImportDataAccessFile'"

DocType = cmd.ExecuteScalar()

DocType = DocType.ToLower()

cmd.CommandText = "sel ect Value fr om dbo.CfgConstant WHERE [Key] = 'ImportDataFilePath'"

Dim path As String = cmd.ExecuteScalar()

path = path.Trim("\") + "\"

'如果为Access

Dim conAccess As String = "conAccess" '新增的Access链接名称

If DocType.Contains("mdb") Then

If Connections.Contains(conAccess) Then Connections.Delete(conAccess)

Connections.Add(conAccess, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + DocType + ";Persist Security Info=False")

sourcetbs.AddRange(Connections(conAccess).GetTableNames())

sourcetbs.AddRange(Connections(conAccess).GetViewNames())

Connections.Delete(conAccess)

Else If DocType.Contains("accdb")

If Connections.Contains(conAccess) Then Connections.Delete(conAccess)

Connections.Add(conAccess, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + DocType + ";Persist Security Info=False")

sourcetbs.AddRange(Connections(conAccess).GetTableNames())

sourcetbs.AddRange(Connections(conAccess).GetViewNames())

Connections.Delete(conAccess)

Else

sourcetbs.AddRange(FileSys.GetFiles(path))

End If

'删除表

Dim Builder As New ADOXBuilder

Dim tb As String

For i As Integer = 0 To sourcetbs.Count -1

tb = sourcetbs(i)

tb = tb.Replace(path,"")

If tb.IndexOf(".")>=0 Then tb = tb.SubString(0, tb.IndexOf("."))

tb = tb.ToLower()

sourcetbs(i) = tb

If DataTables.Contains(tb) Then

DataTables.Unload(tb)

Builder.Open()

builder.DeleteTable(tb)

Builder.Close()

End If

'导入前处理语句

cmd.CommandText = "SEL ECT [Value] fr om dbo.CfgConstant WHERE [Key] = 'sqlBeforeImport'"

cmd.CommandTimeOut = 600

Dim sql As String = cmd.ExecuteScalar

Dim flag As Integer = 100

Dim k As Integer = 0

If sql IsNot Nothing Then

sql = sql.Trim()

k = sql.Length

End If

If k > 0 Then

cmd.CommandText = sql

cmd.CommandTimeOut = 600

flag = cmd.ExecuteNonQuery

'写入日志

FileSys.WriteAllText(projectPath & "Logs\" + Vars("_logFileName"), vbcrlf + vbcrlf + "-- generated on " + cstr(Date.Now) + vbcrlf + cstr(Date.Now) + " 执行导入前SQL语句: " + sql, True, Encoding.Default)

cmd.CommandText = "INSERT INTO dbo.cfgLogs(logType, logContent, logUser, logTime ) VALUES ( N'dataSync', N'" + "-- generated on " + cstr(Date.Now) + " 执行导入前SQL语句: " + sql + "', N'admin', GETDATE()) "

cmd.ExecuteNonQuery

End If

If flag <>100 Then

'开始导入

cmd.CommandText = "SEL ECT DISTINCT TableName1 AS sourceTable, TableName2 AS targetTable fr om dbo.CfgDataMapping"

Dim dt As DataTable = cmd.ExecuteReader()

Dim dr As DataRow

For i As Integer = 0 To dt.DataRows.Count -1

dr = dt.DataRows(i)

If sourcetbs.Contains(dr("sourceTable").Tolower()) = True AndAlso targettbs.Contains(dr("targetTable").ToLower()) = True Then

foxSqlBulkcopy(dr("sourceTable"), dr("targetTable"))

tb = dr("sourceTable")

If DataTables.Contains(tb) Then

DataTables.Unload(tb)

Builder.Open()

builder.DeleteTable(tb)

Builder.Close()

End If

Syscmd.Project.Compact(False)

GC.Collect

End If

'导入后处理语句

flag = 100

cmd.CommandText = "SEL ECT [Value] fr om dbo.CfgConstant WHERE [Key] = 'sqlAfterImport'"

cmd.CommandTimeOut = 600

sql = cmd.ExecuteScalar

k = 0

If sql IsNot Nothing Then

sql = sql.Trim()

k = sql.Length

End If

If k > 0 Then

cmd.CommandText = sql

cmd.CommandTimeOut = 600

flag = cmd.ExecuteNonQuery

'写入日志

FileSys.WriteAllText(projectPath & "Logs\" + Vars("_logFileName"), vbcrlf + vbcrlf + "-- generated on " + cstr(Date.Now) + vbcrlf + cstr(Date.Now) + " 执行导入后SQL语句: " + sql, True, Encoding.Default)

cmd.CommandText = "INSERT INTO dbo.cfgLogs(logType, logContent, logUser, logTime ) VALUES ( N'dataSync', N'" + "-- generated on " + cstr(Date.Now) + " 执行导入后SQL语句: " + sql + "', N'admin', GETDATE()) "

cmd.ExecuteNonQuery

End If

If IntPopMsg <>0 Then

Forms("进度窗口").Close()

MessageBox.Show("导入结束,耗时: " & (Date.Now - st).TotalSeconds & "秒")

End If

End Function

占用内存的图片上传不上来。从几十M开始，最后一直占用1G左右。如有更多表要导的话，则会报System.OutOfMemoryException错。

[此贴子已经被作者于2017/12/19 1:34:47编辑过]

' Bulkcopy 函数
Public Function foxSqlBulkCopy (sourceTable As String, targetTable As String) As Integer
' DocType : "Excel", "Text", "Access"
foxSqlBulkCopy = 0 '初始化值为0, 表示开始, 结束导入后置值为1, 表示结束
Dim sheetName As String = ""
Dim s As String = Connections("dbMain").ConnectionString
s = s.SubString(s.IndexOf("Password"))
s = s.Replace("Persist Security Info=True;","")
Dim _server As String = s.Substring(s.lastIndexOf("=")+1)
Dim _uid As String = s.SubString(s.IndexOf("User ID=") + len("User ID="), s.IndexOf(";Initial Catalog")-s.IndexOf("User ID=") - len("User ID=") )
Dim _pwd As String = s.SubString(s.IndexOf("Password=") + len("Password="), s.IndexOf(";User ID=")-s.IndexOf("Password=") - len("Password=") )
Dim _database As String = s.SubString(s.IndexOf("Catalog=") + len("Catalog="), s.IndexOf(";Data Source")-s.IndexOf("Catalog=") - len("Catalog=") )
s = "server=" + _server + ";uid=" + _uid + ";pwd=" + _pwd + ";database=" + _database + ";timeout= 600"

Dim cmd As new SQLCommand
cmd.C
cmd.CommandText = "SELECT DISTINCT TableName1 fr om dbo.CfgDataMapping WHERE TableName1 = '" + sourceTable + "' and TableName2 = '" + targetTable + "' GROUP BY TableName1"
Dim sTable As String = cmd.ExecuteScalar()
Dim st As Date = Date.Now
Dim Builder As New ADOXBuilder
If DataTables.Contains(sTable) Then
    DataTables.Unload(sTable)
    Builder.Open()
    Builder.DeleteTable(sTable)
    Builder.Close()
End If

FileSys.WriteAllText(projectPath & "Logs\" + Vars("_logFileName"), vbcrlf + vbcrlf + "-- generated on " + cstr(Date.Now) + vbcrlf + cstr(Date.Now) + " 开始读取数据源中的" + sTable + "表", True, Encoding.Default)
cmd.CommandText = "INSERT INTO dbo.cfgLogs(logType, logContent, logUser, logTime ) VALUES ( N'dataSync', N'" + "-- generated on " + cstr(Date.Now) + vbcrlf + cstr(Date.Now) + " 开始读取数据源中的" + sTable + "表" + "', N'admin', GETDATE()) "
cmd.ExecuteNonQuery

Dim DocType As String
cmd.CommandText = "SELECT [Value] fr om dbo.CfgConstant WHERE [Key]= 'ImportDataAccessFile'"
DocType = cmd.ExecuteScalar()
DocType = DocType.ToLower()

Dim DocPath As String
cmd.CommandText = "SELECT [Value] fr om dbo.CfgConstant WHERE [Key]= 'ImportDataFilePath'"
DocPath = cmd.ExecuteScalar()
DocPath = DocPath.Trim("\") + "\"

'如果为文本型,先导入到foxtable内部
If DocType.Contains("text") OrElse DocType.Contains("txt") Then
    Dim ip As New Importer
    ip.SourcePath = DocPath '文本路径
    ip.SourceTableName = sTable '指定要导入的表
    ip.NewTableName = sTable '导入后的表名
    ip.Format = "Delimited" '指定导入格式
    ip.Import()
End If

'如果为Excel型,先导入到foxtable内部
If DocType.Contains("excel") OrElse DocType.Contains("xls") OrElse DocType.Contains("xlsx") Then
    Dim sFile As String = DocPath + sTable + ".xls"
    sFile = sFile.ToLower
    For Each File As String In FileSys.GetFiles(DocPath)
        File = File.ToLower
        If file.Contains(sFile) Then DocPath = file
    Next
    cmd.CommandText = "SELECT DISTINCT Parameter fr om dbo.CfgDataMapping WHERE TableName1 = '" + sTable + "' AND Parameter IS NOT NULL"
    sheetName = cmd.ExecuteScalar()
    If sheetName IsNot Nothing Then
        sheetName = sheetName.Trim() + "$"
    Else
        sheetName = "sheet1$"
    End If
    Dim ip As New Importer
    ip.SourcePath = DocPath '指定数据文件
    ip.SourceTableName = sheetName '指定要导入的表
    ip.NewTableName = sTable '导入后的表名
    ip.Format = "Excel" '指定导入格式
    ip.Import()
End If

'如果为Access
If DocType.Contains("mdb") OrElse DocType.Contains("accdb") Then
    Dim ip As New Importer
    ip.SourcePath = DocPath + DocType '指定数据文件
    ip.SourceTableName = sTable '指定要导入的表
    ip.NewTableName = sTable '导入后的表名
    ip.Import()
End If

'规范列标题
Dim c As DataCol
Dim cName As String
For ci As Integer = 0 To DataTables(sTable).DataCols.Count - 1
    c = DataTables(sTable).DataCols(ci)
    cName = c.Name
    cName = cName.Replace(" ","")
    c.Caption = cName
Next

Dim dt As DataTable = DataTables(sTable)
Dim readTime As Double = (Date.Now - st).TotalSeconds '读取数据时间
st = Date.Now '重置时间,用以计算写入sql server的时间
'读取完成
FileSys.WriteAllText(projectPath & "Logs\" + Vars("_logFileName"), vbcrlf + cstr(Date.Now) + " 数据源中的" + sTable + " " + sheetName + "表读取完成, 共" + cstr(dt.DataRows.Count) + "条记录, 耗时 " + cstr(readTime) + "秒", True, Encoding.Default)
cmd.CommandText = "INSERT INTO dbo.cfgLogs(logType, logContent, logUser, logTime ) VALUES ( N'dataSync', N'" + cstr(Date.Now) + " 数据源中的" + sTable + " " + sheetName + "表读取完成, 共" + cstr(dt.DataRows.Count) + "条记录, 耗时 " + cstr(readTime) + "秒" + "', N'admin', GETDATE()) "
cmd.ExecuteNonQuery

Dim tran As System.Data.SqlClient.SqlTransaction
try
    Dim conn As new System.Data.SqlClient.SqlConnection(s)
    conn.Open() '打开链接
    tran = conn.BeginTransaction()
    'Dim cmd As new SQLCommand
    cmd.C
    cmd.CommandText = "SELECT * fr om dbo.CfgDataMapping WHERE TableName1 = '" + sTable + "' and TableName2 = '" + targetTable + "' ORDER BY ID"
    Dim dt1 As DataTable = cmd.ExecuteReader()
    Dim dr As DataRow

    cmd.C
    cmd.CommandText = "IF OBJECTPROPERTY(OBJECT_ID('" + targetTable + "'),'TableHasIdentity')=1" + vbcrlf + "SET IDENTITY_INSERT " + targetTable + " ON"
    cmd.ExecuteNonQuery

    Dim copy As new System.Data.SqlClient.SqlBulkCopy(conn, System.Data.SqlClient.SqlBulkCopyOptions.Default, tran)
    For i As Integer = 0 To dt1.DataRows.Count - 1
        dr = dt1.DataRows(i)
        Copy.ColumnMappings.Add(dr("FieldName1"),dr("FieldName2") )
        'Copy.ColumnMappings.Add(getTableColName(Tables(sTable),dr("FieldName1")),dr("FieldName2") )
    Next
    copy.DestinationTableName = targetTable '指定服务器上目标表的名称
    copy.BatchSize = 1000
    copy.WriteToServer(dt.basetable)   '你的datatable名字,执行把DataTable中的数据写入DB

    cmd.C
    cmd.CommandText = "IF OBJECTPROPERTY(OBJECT_ID('" + targetTable + "'),'TableHasIdentity')=1" + vbcrlf + "SET IDENTITY_INSERT " + targetTable + " OFF"
    cmd.ExecuteNonQuery


    tran.Commit()   '提交事务
    'conn.Close()
    FileSys.WriteAllText(projectPath & "Logs\" + Vars("_logFileName"), vbcrlf + cstr(Date.Now) + " 完成写入目标数据库中的" + targetTable + "表, 耗时 " + cstr((Date.Now - st).TotalSeconds) + "秒", True, Encoding.Default)
    cmd.CommandText = "INSERT INTO dbo.cfgLogs(logType, logContent, logUser, logTime ) VALUES ( N'dataSync', N'" + cstr(Date.Now) + cstr(Date.Now) + " 完成写入目标数据库中的" + targetTable + "表, 耗时 " + cstr((Date.Now - st).TotalSeconds) + "秒" + "', N'admin', GETDATE()) "
    cmd.ExecuteNonQuery
catch ex As exception
    FileSys.WriteAllText(projectPath & "Logs\" + Vars("_logFileName"), vbcrlf + cstr(Date.Now) + " 写入目标数据库中的" + targetTable + "表发生异常" + vbcrlf + ex.message, True, Encoding.Default)
    cmd.CommandText = "INSERT INTO dbo.cfgLogs(logType, logContent, logUser, logTime ) VALUES ( N'dataSync', N'" + cstr(Date.Now) + " 写入目标数据库中的" + targetTable + "表发生异常" + vbcrlf + ex.message + "', N'admin', GETDATE()) "
    cmd.ExecuteNonQuery
    tran.Rollback()    '返回False 执行失败!
End try
DataTables.Unload(dt.Name)
Builder.Open()
Builder.DeleteTable(dt.Name)
Builder.Close()
GC.Collect
GC.Collect
foxSqlBulkCopy = 0 '初始化值为0, 表示开始, 结束导入后置值为1, 表示结束
End Function

[此贴子已经被作者于2017/12/19 10:05:33编辑过]

临时表怎么用？

该程序考虑了导入Excel、txt, Access等不同数据源的通用性，所以用了先导入

用DataTableBuilder可以生成临时表，但excel和txt的数据怎么加载到临时表？ Access可以用sqlcommand加载数据到临时表。

主题：删除表依然占内存