none
Mise a jours d'une Base.Table Dest depuis une Base.Table Srce RRS feed

  • Question

  • Bonjour
    J'essaye depuis 2 jours de trouver une solution "performante" pour copier/recuperer les diff(Update) d'une table paradox vers une table SQL 2005.
    J'ai essayer les fonction merge, copy ... de dataset et/ou datatable, puis update mais les donnée ne sont pas insérer ds SQL 2005, par contre si juste avant l'update je fais dsSrc.writexml("file") puis DsDestReadxml("file") la c'est ok mais la lecture ecriture xml prends 1 minute (pour 20000 enregistrements) et le update prends 2 a 3 minutes !

    Voici pour info la partie de mon code pour "l'import", en sachant que j'ai des fonctions pour crée la base et la table de destination si elle n'existe pas :

    Code Snippet

        Private Sub Import()
            Try
                OleDbCntSrce = New OleDb.OleDbConnection(UserCfg.GetXmlValue("CntString1"))
                Dim OlDbCntBuild As New OleDb.OleDbConnectionStringBuilder(OleDbCntSrce.ConnectionString)
                OleDbCntDest = New SqlClient.SqlConnection(UserCfg.GetXmlValue("CntString2"))
                Dim DsSrce As DataSet = New DataSet("Srce")
                Dim DsDest As DataSet = New DataSet("Dest")
                Dim DsMap As DataSet = New DataSet("DsMap")
                If IO.File.Exists(ImportMap) = True Then DsMap.ReadXml(ImportMap)
                For Each CurRow As DataRow In DsMap.Tables(0).Rows
                    OleDbCntDest.ConnectionString = DataBaseExist(OleDbCntDest, CurRow.ItemArray(0))
                    OlDbCntBuild.DataSource = CurRow.ItemArray(1)
                    Dim FInfo As New IO.FileInfo(CurRow.ItemArray(1))
                    Dim TblName As String = FInfo.Name.Replace(FInfo.Extension, String.Empty)
                    Dim DtAdptSrce As New OleDbDataAdapter()
                    DtAdptSrce.SelectCommand = New OleDbCommand("SELECT * FROM " & TblName, OleDbCntSrce)
                    Dim DtAdptDest As New SqlClient.SqlDataAdapter()
                    DtAdptDest.SelectCommand = New SqlClient.SqlCommand(DtAdptSrce.SelectCommand.CommandText, OleDbCntDest)
                    DsSrce.Tables.Clear()
                    DsDest.Tables.Clear()
                    DtAdptDest.SelectCommand.CreateParameter()
                    DtAdptDest.SelectCommand.Prepare()
                    Dim OleDbSrceCmdBuild As New OleDbCommandBuilder(DtAdptSrce)
                    OleDbCntSrce.Open()

                    DtAdptSrce.AcceptChangesDuringFill = True
                    DtAdptSrce.AcceptChangesDuringUpdate = True
                    DtAdptSrce.ContinueUpdateOnError = True
                    DtAdptSrce.MissingMappingAction = MissingMappingAction.Passthrough
                    DtAdptSrce.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    DtAdptSrce.ReturnProviderSpecificTypes = True

                    DtAdptSrce.Fill(DsSrce, TblName)

                    FindIdx(DsSrce, TblName)

                    DataTableExist(OleDbCntDest, TblName, DsSrce.GetXmlSchema())
                    Dim OleDbDestCmdBuild As New SqlClient.SqlCommandBuilder(DtAdptDest)
                    OleDbCntDest.Open()

                    DtAdptDest.AcceptChangesDuringFill = True
                    DtAdptDest.AcceptChangesDuringUpdate = True
                    DtAdptDest.ContinueUpdateOnError = True
                    DtAdptDest.MissingMappingAction = MissingMappingAction.Passthrough
                    DtAdptDest.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    DtAdptDest.ReturnProviderSpecificTypes = True

                    DtAdptDest.Fill(DsDest, TblName)

                    'FindIdx(DsDest, TblName)

                    ' DataColumn array to set primary key.
                    Dim keyCol(0) As DataColumn

                    ' Set primary key column.
                    keyCol(0) = DsDest.Tables(TblName).Columns(0)
                    'DsDest.Tables(TblName).PrimaryKey = keyCol

                    FindIdx(DsSrce, TblName)

                    'DtAdptDest.DeleteCommand = OleDbDestCmdBuild.GetDeleteCommand(True)
                    'DtAdptDest.InsertCommand = OleDbDestCmdBuild.GetInsertCommand(True)
                    'DtAdptDest.UpdateCommand = OleDbDestCmdBuild.GetUpdateCommand(True)
                    Dim TmpDt As String = IO.Path.GetTempFileName
                    DsDest.Tables.Clear()
                    DsDest.Tables.Add(DsSrce.Tables(TblName).Copy())
                    'DsDest.Tables(TblName).Merge(DsSrce.Tables(TblName))
                    FindIdx(DsDest, TblName)
                    DtAdptDest.Fill(DsDest, TblName)
                    FindIdx(DsDest, TblName)
                    DsDest.AcceptChanges()
                    DsSrce.WriteXml(TmpDt, XmlWriteMode.WriteSchema)
                    DsDest.ReadXml(TmpDt, XmlReadMode.Auto)
                    DtAdptDest.Update(DsDest, TblName)
                    OleDbCntSrce.Close()
                    OleDbCntDest.Close()

                Next
            Catch ex As Exception
                Trace.WriteLine(Date.Now & ": " & ex.Message & vbCrLf & ex.StackTrace)
                MessageBox.Show(ex.Message, "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub



    jeudi 14 février 2008 16:01