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

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

导入的日志如下：

\' 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