对于异构数据库表的导入导出方法（Access与SQL Server）

      如果工作中遇到需要从SQL Server中导出一些表到Access数据库。那么可使用的方法有很多，如果只是偶尔需要导出，则可以使用SQL Server的导入导出功能。还可以使用DTS（数据传输服务）包，SQL Server 2005也可以使用SSIS。但如果这个导入导出操作需要在程序中作为一个功能操作，这就有各种方法实现了。

      这里跟大家分享一下Access与SQL Server的表导入导出操作。每个解决方案需要根据实际情况来确定是否可行。

情况1：Access中的表与SQL Server中的表架构完全相同（列的数据类型与数目一致）

          这种情况应该最为容易解决，可以将整个导入导出的操作提交给SQL Server执行，而代码量则比较少，比较容易维护：

     Access 的表 tblTest(c1,c2,c3)

     SQL     的表 tblTest(c1,c2,c3)

     代码可以这样写：

     dim sqlOut as String using sqlCon as New SqlConnection(Cnstr) dim sqlCmd as SqlCommand dim iRtn as integer sqlOut = "insert into OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=c:/test.mdb;')...[tblTest] select * from tblTest" sqlCmd = sqlCon.CreateCommand() sqlCmd.CommandText = sqlOut iRtn = sqlCmd.ExecuteNoQuery() end using

 

我们把访问Access数据库的操作丢给SQL Server做了，这样做的好处是代码维护简单。但要注意，代码里并没有明确指定表的列顺序，如果列的排列顺序不一样或者是列的数目不一样就需要明确指定导出那些列了。而且还有一个危险，OpenDataSource只能在32位的Windows环境下使用，如果是64位的Vista或Windows Server就不行，因为在64位的环境下SQL Server 找不到 OLEDB.Jet 这个驱动。但是如果你在代码里用OleDBConnection来链接的话则可以访问这个Access数据库（如果不能访问需打补丁）。

 

上面说的是最简单的情况，如果Access中的表与SQL Server中的表在结构上不对应怎么导出？结构不同表现为列的数据类型不同或者是列的数目不同，或者两者皆有。如果列的数据类型相似的话也是可以用Opendatasource解决的，比如 Access中使用文本类型，而SQL Server中使用varchar，nvarchar,Text类型的话SQL会进行最大可能的转换尝试，一般这些数据类型都可以成功转换。但要注意，Access中的文本类型只能存255个文字，如果SQL Server中的varchar类型保存的内容超过这个数目，就会有数据丢失的可能。所以在实际应用时要充分考虑清楚。

 

如果有些表不能直接从SQL里导出，而是需要先进行一些操作然后再导入Access，这种情况需要根据实际来确定，如果在SQL Server里能完成这些操作，就直接写SQL语句扔给SQL Server执行就可以了，如果不能，那我们可以先把要处理的数据加载到DataTable里，然后执行必要的操作，最后再根据情况看是写入SQL Server还是写入Access。例如：

dim sql as String = "select c1, c2, c3 from tblTest" dim dtTest as DataTable = LoadSQLRecord(sql,conString) if isnothing(dtTest) = false then for each dr in dtTest.Rows dr("c1") = "这里要处理" next endif '--------------------------- '这里写入Acccess或SQL Server '--------------------------- ' ----------------------------- ' 函数功能：从SQL Server中读取记录到DataTable中 ' 参数意义：sqlString : 访问SQL Server的Select 语句 ' connectionString: 链接SQL Server的字符串 ' 返回值域：成功返回DataTable，失败返回nothing ' 程序作者：申志远 ' 编写时间：2009-06-19 22:22 ' 注意事项：不能使用该函数做更新数据库操作。 ' ------------------------------ Public Function LoadSQLRecord(ByVal sqlString As String, ByVal connectionString As String) As DataTable Dim con As New SqlClient.SqlConnection Dim dt As New DataTable Try con.ConnectionString = connectionString Dim sda As New SqlClient.SqlDataAdapter(sqlString, con) sda.Fill(dt) sda.Dispose() Return dt Catch ex As Exception Return nothing Finally con.Close() con.Dispose() End Try End Function

 

这种情况的处理逻辑可能会根据情况复杂一些。不过总体还是容易理解的。

 

还有一种情况，如果在64位的系统上，OpenDataSource不能用，而且要导出的表也很多，部分表需要做特殊处理，那怎么办？ 还有一点，就是SQL Server中的GUID在Access中表现位同步复制ID。但如果你直接导入的话就会发生类型转换错误，因为 一个GUID在SQL Server里不必用大括号“{}”包起来，而Access需要这样做。这种情况我的解决方法是一条条记录导出。但不必为每个表都写不同的导出操作，可以编写一个函数模仿Opendatasouce功能。这个方法无论是导入或导出都是相通的，而且也不必担心64位平台的问题，可以说在通用性和灵活性上都有不错的表现。下面是我写的代码：

Public Function SQLToAccess(ByVal conAccess As OleDb.OleDbConnection, ByVal AccessSchemaSQL As String, ByVal accessInsertSQL As String, ByVal selectSQL As String) As Boolean Dim dt As DataTable Dim dt2 As New DataTable Dim dtAce As New DataTable Dim cmdSQL As OleDb.OleDbCommand Dim insertSQL As String Dim cols As String Dim i As Integer Dim colNum As Integer Dim colType As String Try dt = LoadSQLRecord(selectSQL, Cnstr) dtAce = LoadAccessRecord(AccessSchemaSQL, conAccess.ConnectionString) 'MsgBox(dtAce.Columns.Count) colNum = dtAce.Columns.Count For Each dr As DataRow In dt.Rows For i = 0 To colNum - 1 'MsgBox(dr(i).GetType().ToString) 'MsgBox(dtAce.Columns(i).DataType.ToString()) colType = dtAce.Columns(i).DataType.ToString() If colType.Compare(colType, "System.Decimal", True) = 0 Then If dr(i).ToString().Length = 0 Then cols = cols & "0," Else cols = cols & dr(i) & "," End If ElseIf colType.Compare(colType, "System.Double", True) = 0 Then If dr(i).ToString().Length = 0 Then cols = cols & 0 & "," Else cols = cols & dr(i) & "," End If ElseIf colType.Compare(colType, "System.Byte", True) = 0 Then If dr(i).ToString().Length = 0 Then cols = cols & 0 & "," Else cols = cols & dr(i) & "," End If ElseIf colType.Compare(colType, "System.Int32", True) = 0 Then If dr(i).ToString().Length = 0 Then cols = cols & 0 & "," Else cols = cols & dr(i) & "," End If ElseIf colType.Compare(colType, "System.GUID", True) = 0 Then If dr(i).ToString().Length = 0 Then cols = cols & "''," Else cols = cols & dr(i).ToString() & "," End If ElseIf colType.Compare(colType, "System.Boolean", True) = 0 Then If dr(i).ToString().Length = 0 Then cols = cols & "0," Else ' 在Access中，0 = false，-1 = true If dr(i).ToString().Compare(dr(i).ToString(), "True") = 0 Then cols = cols & "-1," Else cols = cols & "0," End If End If ElseIf colType.Compare(colType, "System.DateTime", True) = 0 Then If dr(i).ToString().Length = 0 Then cols = cols & "'1900-01-01'," Else cols = cols & "'" & dr(i) & "'" & "," End If Else 'MsgBox(dtAce.Columns(i).DataType.ToString()) cols = cols & "'" & dr(i) & "'" & "," End If Next cols = cols.Remove(cols.Length - 1, 1) insertSQL = accessInsertSQL & " values(" & cols & ")" cmdSQL = conAccess.CreateCommand() cmdSQL.CommandText = insertSQL cmdSQL.ExecuteNonQuery() cols = "" Next Return True Catch ex As Exception Return False End Try End Function

 

我先说明一下，conAccess是Access数据库链接，AccessSchemaSQL是代表Access数据表架构的select语句，accessInsertSQL 是具体Access表的Insert语句，而selectSQL是需要导出的SQL Server表的select语句。函数的主要功能是根据Access的表架构来确定预插入Access数据表的列的数据类型和数目。这样可以做到各表通用这个函数。实际的调用代码是这样的：

'------------------------------------------ ' TblGC_GYS_KCTZ 表 schema = "select top 1 ID,GZNR,TZXS,BZ,XZ,XZ2,LX from TblGC_GYS_KCTZ" mdbSQL = "insert into TblGC_GYS_KCTZ( ID,GZNR,TZXS,BZ,XZ,XZ2,LX )" sql = "select ROWID,GZNR,TZXS,BZ,XZ,XZ2,'0' as LX from TblGC_GYS_KCTZ " & _ "where GCXH=" & GCXH If SQLToAccess(AccessDB, schema, mdbSQL, sql) = False Then log.WriteLogToDB("错误语句：" & mdbSQL) AccessDB.Close() AccessDB.Dispose() Return -1 End If

 

这样做不要担心性能会比OpenDataSource差，我的实际操作是比OpenDataSource要快，而且各种不同的表都可以调用SQLToAccess函数来统一输出到Access中。但是大家要注意，SQLToAccess函数存在安全隐患，并不是很健壮，这里我就不提示了，大家讨论一下吧。