'Création automatique de la commande update à partir de la source SDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SDA).GetUpdateCommand 'Récuperation de la commande et conservation du strict minimum Dim updCmd As String = SDA.UpdateCommand.CommandText Dim idx_where As Integer = updCmd.IndexOf("where", 0, StringComparison.CurrentCultureIgnoreCase) Dim strCmd As String = updCmd.Substring(0, updCmd.IndexOf(")", idx_where)) While strCmd.LastIndexOf("(") > idx_where strCmd = strCmd.Remove(strCmd.LastIndexOf("("), 1) End While 'Récuperation des paramètres de mise à jour créer util par rapport à la commande filtrée Dim lstParam As Generic.List(Of SqlClient.SqlParameter) = New Generic.List(Of SqlClient.SqlParameter) For Each p As SqlClient.SqlParameter In SDA.UpdateCommand.Parameters If strCmd.Contains(p.ParameterName) Then lstParam.Add(CType(CType(p, ICloneable).Clone, SqlClient.SqlParameter)) End If Next cnx.Open() Using trans As SqlClient.SqlTransaction = cnx.BeginTransaction Using cmd As SqlClient.SqlCommand = cnx.CreateCommand cmd.CommandText = strCmd cmd.Transaction = trans For Each p As SqlClient.SqlParameter In lstParam cmd.Parameters.Add(p) Next Dim nbParam As Integer = cmd.Parameters.Count dtable.DefaultView.RowStateFilter = DataViewRowState.ModifiedCurrent For i As Integer = 0 To dtable.DefaultView.Count - 1 For j As Integer = 0 To nbParam - 1 cmd.Parameters.Item(j).Value = dtable.DefaultView(i)(cmd.Parameters.Item(j).SourceColumn) Next cmd.ExecuteNonQuery() Next End Using trans.Commit() End Using cnx.Close()
SDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SDA).GetUpdateCommand(True) ... End While 'Recuperation des noms de colones Dim lstColDtable As Generic.List(Of String) = New Generic.List(Of String) For i As Integer = 0 To dtable.Columns.Count - 1 lstColDtable.Add(dtable.Columns(i).ColumnName) Next cnx.Open() Using trans As SqlClient.SqlTransaction = cnx.BeginTransaction Using cmd As SqlClient.SqlCommand = cnx.CreateCommand cmd.Transaction = trans strCmd = strCmd.Replace("@Original_", "@") Dim nbParam As Integer = cmd.Parameters.Count dtable.DefaultView.RowStateFilter = DataViewRowState.ModifiedCurrent For i As Integer = 0 To dtable.DefaultView.Count - 1 cmd.CommandText = strCmd For j As Integer = 0 To lstColDtable.Count - 1 With cmd If dtable.Columns(lstColDtable(j)).DataType.Name GetType(System.String).Name OrElse dtable.Columns(lstColDtable(j)).DataType.Name GetType(System.DateTime).Name Then .CommandText = .CommandText.Replace("@" & lstColDtable(j), "'" & dtable.DefaultView(i)(lstColDtable(j).Replace("'", "''")) & "'") Else .CommandText = .CommandText.Replace("@" & lstColDtable(j), dtable.DefaultView(i)(lstColDtable(j))) End If End With Next cmd.ExecuteNonQuery() Next End Using trans.Commit() End Using cnx.Close()
SDATempo.UpdateCommand = New SqlClient.SqlCommandBuilder(SDATempo).GetUpdateCommand SDATempo.UpdateCommand.Connection.Open() SDATempo.UpdateCommand.Transaction = SDATempo.UpdateCommand.Connection.BeginTransaction tpsUpd.Start() SDATempo.Update(dtableParticulier) SDATempo.UpdateCommand.Transaction.Commit() tpsUpd.Stop() SDATempo.UpdateCommand.Connection.Close() SDATempo.Dispose()
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionDim sqlP As SqlClient.SqlParameter For i As Integer = 0 To dtableParticulier.Rows.Count If dtableParticulier.Rows(i).RowState = DataRowState.Modified Then For Each sqlP In SDATempo.UpdateCommand.Parameters sqlP.Value = dtableParticulier.Rows(i)(sqlP.SourceColumn) Next SDATempo.UpdateCommand.ExecuteNonQuery() End If Next
UPDATE [tParticulier] SET [idTitreParticulier] = @p1, [strNomParticulier] = @p2, [strPrenomParticulier] = @p3, [strNomConjoint] = @p4, [strPrenomConjoint] = @p5, [strNomCourrier] = @p6, [strAdresse] = @p7, [strCP] = @p8, [strCI] = @p9, [strCommune] = @p10, [strTelPersonnel] = @p11, [strTelPortable] = @p12, [strTelTravail] = @p13, [strFax] = @p14, [strEmail] = @p15, [ltxtObservation] = @p16, [idSession] = @p17, [idAgence] = @p18, [dtEnrCrea] = @p19, [idUserEnrCrea] = @p20, [dtEnrModif] = @p21, [idUserEnrModif] = @p22, [idSwitchDataBase] = @p23 WHERE (([idParticulier] = @p24) AND ((@p25 = 1 AND [idTitreParticulier] IS NULL) OR ([idTitreParticulier] = @p26)) AND ((@p27 = 1 AND [strNomParticulier] IS NULL) OR ([strNomParticulier] = @p28)) AND ((@p29 = 1 AND [strPrenomParticulier] IS NULL) OR ([strPrenomParticulier] = @p30)) AND ((@p31 = 1 AND [strNomConjoint] IS NULL) OR ([strNomConjoint] = @p32)) AND ((@p33 = 1 AND [strPrenomConjoint] IS NULL) OR ([strPrenomConjoint] = @p34)) AND ((@p35 = 1 AND [strNomCourrier] IS NULL) OR ([strNomCourrier] = @p36)) AND ((@p37 = 1 AND [strAdresse] IS NULL) OR ([strAdresse] = @p38)) AND ((@p39 = 1 AND [strCP] IS NULL) OR ([strCP] = @p40)) AND ((@p41 = 1 AND [strCI] IS NULL) OR ([strCI] = @p42)) AND ((@p43 = 1 AND [strCommune] IS NULL) OR ([strCommune] = @p44)) AND ((@p45 = 1 AND [strTelPersonnel] IS NULL) OR ([strTelPersonnel] = @p46)) AND ((@p47 = 1 AND [strTelPortable] IS NULL) OR ([strTelPortable] = @p48)) AND ((@p49 = 1 AND [strTelTravail] IS NULL) OR ([strTelTravail] = @p50)) AND ((@p51 = 1 AND [strFax] IS NULL) OR ([strFax] = @p52)) AND ((@p53 = 1 AND [strEmail] IS NULL) OR ([strEmail] = @p54)) AND ((@p55 = 1 AND [ltxtObservation] IS NULL) OR ([ltxtObservation] = @p56)) AND ((@p57 = 1 AND [idSession] IS NULL) OR ([idSession] = @p58)) AND ((@p59 = 1 AND [idAgence] IS NULL) OR ([idAgence] = @p60)) AND ((@p61 = 1 AND [dtEnrCrea] IS NULL) OR ([dtEnrCrea] = @p62)) AND ((@p63 = 1 AND [idUserEnrCrea] IS NULL) OR ([idUserEnrCrea] = @p64)) AND ((@p65 = 1 AND [dtEnrModif] IS NULL) OR ([dtEnrModif] = @p66)) AND ((@p67 = 1 AND [idUserEnrModif] IS NULL) OR ([idUserEnrModif] = @p68)) AND ((@p69 = 1 AND [idSwitchDataBase] IS NULL) OR ([idSwitchDataBase] = @p70)))