Public Class OleToSql
Private _tableName As String
Private _oleCriteria, _sqlCriteria As String
Public Sub New()
End Sub
Public Sub New(ByVal tableName As String, ByVal oleCriteria As String, ByVal sqlCriteria As String)
_tableName = tableName
_oleCriteria = oleCriteria
_sqlCriteria = sqlCriteria
End Sub
Public Function Execute() As String
Return Me.Execute(_tableName, _oleCriteria, _sqlCriteria)
End Function
Public Function Execute(ByVal tableName As String, ByVal oleCriteria As String, ByVal sqlCriteria As String) As String
Dim sqlConn As New SqlConnection(ClsConnServer.ConnStr)
Dim oleConn As New OleDbConnection(ClsConnStr.ConnStr)
Dim sqlAdapter As SqlDataAdapter, oleAdapter As OleDbDataAdapter
Dim sqlCmdBuilder As SqlCommandBuilder
Dim sqlQry, oleQry As String
Dim sqlData, oleData As DataSet
Dim row As DataRow, rowInx As Integer, rowCount As Integer
Dim isValid As Boolean
Try
'get updating data sql db
sqlConn.Open()
Dim itime As Integer = sqlConn.ConnectionTimeout
sqlQry = "select * from " & tableName & Space(1) & sqlCriteria
sqlAdapter = New SqlDataAdapter(sqlQry, sqlConn)
sqlData = New DataSet()
sqlAdapter.Fill(sqlData, tableName)
'builde ins/del command
sqlCmdBuilder = New SqlCommandBuilder(sqlAdapter)
sqlQry = "delete from " & tableName & Space(1) & sqlCriteria
sqlAdapter.DeleteCommand = New SqlCommand(sqlQry, sqlConn)
'Select Case tableName
' Case "e_yjry", "d_supervisor", "d_superchange"
' Dim DataHelper As New ClsDataHelper()
' DataHelper.F_GrnInsertCom(tableName, sqlAdapter, sqlConn)
' Case Else
sqlAdapter.InsertCommand = sqlCmdBuilder.GetInsertCommand
'End Select
'get source data from ole db
oleConn.Open()
oleQry = "select * from " & tableName & Space(1) & oleCriteria
oleAdapter = New OleDbDataAdapter(oleQry, oleConn)
oleData = New DataSet()
oleAdapter.Fill(oleData, tableName)
isValid = (oleData.Tables(tableName).Rows.Count > 0)
If isValid Then
'delete from sql db
rowCount = sqlData.Tables(tableName).Rows.Count
If rowCount > 0 Then
For rowInx = rowCount - 1 To 0 Step -1
'sepecial condition check
Dim DataHelper As New ClsDataHelper()
If DataHelper.FillRowother(sqlData.Tables(tableName), rowInx) = True Then
sqlData.Tables(tableName).Rows(rowInx).Delete()
End If
Next
End If
rowCount = oleData.Tables(tableName).Rows.Count
For rowInx = 0 To rowCount - 1
'For Each row In oleData.Tables(tableName).Rows
Dim newRow As DataRow = sqlData.Tables(tableName).NewRow
Dim col As DataColumn
'sepecial condition check
Dim DataHelper As New ClsDataHelper()
If DataHelper.FillRow(oleData.Tables(tableName), rowInx) = True Then
For Each col In sqlData.Tables(tableName).Columns
newRow(col.ColumnName) = oleData.Tables(tableName).Rows(rowInx)(col.ColumnName)
Next
sqlData.Tables(tableName).Rows.Add(newRow)
End If
Next
sqlAdapter.ContinueUpdateOnError = True
sqlAdapter.Update(sqlData, tableName)
End If
Catch e As Exception
sqlConn.Close() : oleConn.Close()
ClsReturnData.strError += ClsDataHelper.GetTableRefName(tableName) & " 数据上传失败." + Chr(13)
Finally
sqlConn.Close() : oleConn.Close()
sqlConn.Dispose() : oleConn.Dispose()
End Try
End Function