SQL Datenbank Thread sicher updaten

  • VB.NET
  • .NET 4.5

    SQL Datenbank Thread sicher updaten

    Hallo Liebe Gemeinde,

    ist es möglich eine SQL Datenbank bzw. mehrere Tabellen gleichzeitig neue Rows zu adden bzw. vorhandene Daten upzudaten?
    Ich habe versucht mit mehren Threads auf verschiedene Tabellen zuzugreifen und neue Datensätze zu adden und bekomme diese Fehlermeldung.

    "Diesem Command ist bereits ein geöffneter DataReader zugeordnet, der zuerst geschlossen werden muss."

    Zur Info es besteht nur eine SQL connection!

    VB.NET-Quellcode

    1. Public Sub UpdateOldDatabaseData()
    2. For Each Instrument_DS As KeyValuePair(Of String, DataSet) In Instrument_History_DS
    3. If Instrument_DS.Key = "EUR_USD" Or Instrument_DS.Key = "USD_JPY" _
    4. Or Instrument_DS.Key = "GBP_USD" Or Instrument_DS.Key = "USD_CHF" _
    5. Or Instrument_DS.Key = "EUR_CHF" Or Instrument_DS.Key = "AUD_USD" _
    6. Or Instrument_DS.Key = "USD_CAD" Or Instrument_DS.Key = "NZD_USD" _
    7. Or Instrument_DS.Key = "EUR_GBP" Or Instrument_DS.Key = "EUR_JPY" _
    8. Or Instrument_DS.Key = "GBP_JPY" Or Instrument_DS.Key = "CHF_JPY" _
    9. Or Instrument_DS.Key = "GBP_CHF" Or Instrument_DS.Key = "EUR_AUD" _
    10. Or Instrument_DS.Key = "EUR_CAD" Or Instrument_DS.Key = "AUD_CAD" _
    11. Or Instrument_DS.Key = "AUD_JPY" Or Instrument_DS.Key = "CAD_JPY" Then
    12. Dim LogTime As Date = System.DateTime.Now
    13. Dim SQL_Instrument_TableAdapter_M5 As SqlDataAdapter = New SqlDataAdapter("Select * From " & Instrument_DS.Key & "_M5", SQL_Connection)
    14. Dim SQL_Instrument_TableAdapter_M15 As SqlDataAdapter = New SqlDataAdapter("Select * From " & Instrument_DS.Key & "_M15", SQL_Connection)
    15. Dim SQL_Instrument_TableAdapter_M30 As SqlDataAdapter = New SqlDataAdapter("Select * From " & Instrument_DS.Key & "_M30", SQL_Connection)
    16. Dim SQL_Instrument_TableAdapter_M60 As SqlDataAdapter = New SqlDataAdapter("Select * From " & Instrument_DS.Key & "_M60", SQL_Connection)
    17. Dim Temp_Instrument_DS As New DataSet
    18. Dim TempTable_M5 As DataTable = Temp_Instrument_DS.Tables.Add(Instrument_DS.Key & "_M5")
    19. Dim TempTable_M15 As DataTable = Temp_Instrument_DS.Tables.Add(Instrument_DS.Key & "_M15")
    20. Dim TempTable_M30 As DataTable = Temp_Instrument_DS.Tables.Add(Instrument_DS.Key & "_M30")
    21. Dim TempTable_M60 As DataTable = Temp_Instrument_DS.Tables.Add(Instrument_DS.Key & "_M60")
    22. SQL_Instrument_TableAdapter_M5.FillSchema(TempTable_M5, SchemaType.Source)
    23. SQL_Instrument_TableAdapter_M15.FillSchema(TempTable_M15, SchemaType.Source)
    24. SQL_Instrument_TableAdapter_M30.FillSchema(TempTable_M30, SchemaType.Source)
    25. SQL_Instrument_TableAdapter_M60.FillSchema(TempTable_M60, SchemaType.Source)
    26. SQL_Instrument_TableAdapter_M5.Fill(TempTable_M5)
    27. SQL_Instrument_TableAdapter_M15.Fill(TempTable_M15)
    28. SQL_Instrument_TableAdapter_M30.Fill(TempTable_M30)
    29. SQL_Instrument_TableAdapter_M60.Fill(TempTable_M60)
    30. 'TODO: Hier neue BerechnungsFormeln hinzufügen für Pre-Daten
    31. LogCreater_Log.AddNewLogInfo("Alte Daten Bearbeiten Laden", "ReworkOldDatabaseData", LogCreater_ClassName, LogTime, Nothing, Instrument_DS.Key)
    32. Trading_Calc_TimeSteps.AddOldCalculation(Instrument_DS.Key, Temp_Instrument_DS, TempTable_M5)
    33. Dim M15Thread = New System.Threading.Thread(Sub() UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M15, TempTable_M15, False, True, False))
    34. Dim M30Thread = New System.Threading.Thread(Sub() UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M30, TempTable_M30, False, True, False))
    35. Dim M60Thread = New System.Threading.Thread(Sub() UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M60, TempTable_M60, False, True, False))
    36. M15Thread.Start()
    37. M30Thread.Start()
    38. M60Thread.Start()
    39. M15Thread.Join()
    40. M30Thread.Join()
    41. M60Thread.Join()
    42. 'UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M15, TempTable_M15, False, True, False)
    43. 'UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M30, TempTable_M30, False, True, False)
    44. 'UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M60, TempTable_M60, False, True, False)
    45. Trading_Calc_Direction.AddOldCalculation(Temp_Instrument_DS, Instrument_DS.Key)
    46. Trading_Calc_PIPCalculator.AddOldCalculation(Temp_Instrument_DS, Instrument_DS.Key)
    47. Trading_Calc_DMICalculator.AddOldCalculation(Temp_Instrument_DS, Instrument_DS.Key)
    48. LogTime = System.DateTime.Now
    49. UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M5, TempTable_M5, True, False, False)
    50. UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M15, TempTable_M15, True, False, False)
    51. UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M30, TempTable_M30, True, False, False)
    52. UpdateInstrumentInSQLDatabase(SQL_Instrument_TableAdapter_M60, TempTable_M60, True, False, False)
    53. LogCreater_Log.AddNewLogInfo("SQL Daten update", "ReworkOldDatabaseData", LogCreater_ClassName, LogTime, Nothing, Instrument_DS.Key)
    54. End If
    55. Next
    56. MessageBox.Show("Fertig")
    57. End Sub


    VB.NET-Quellcode

    1. Private Sub UpdateInstrumentInSQLDatabase(ByVal SQL_DataAdapter_Temp As SqlDataAdapter, ByVal Instrument_Tabel As DataTable, ByVal Modification As Boolean, ByVal Adding As Boolean, ByVal Deleted As Boolean)
    2. Dim LogTime As Date = System.DateTime.Now
    3. Try
    4. Dim mycb As New SqlCommandBuilder(SQL_DataAdapter_Temp)
    5. If Deleted Then
    6. SQL_DataAdapter_Temp.Update(Instrument_Tabel.Select("", "", DataViewRowState.Deleted))
    7. LogCreater_Log.AddNewLogInfo("SQL Tabelle Datensätze gelöscht.", "UpdateInstrumentInSQLDatabase", LogCreater_ClassName, LogTime, Nothing, Instrument_Tabel.TableName)
    8. End If
    9. If Modification Then
    10. SQL_DataAdapter_Temp.Update(Instrument_Tabel.Select("", "", DataViewRowState.ModifiedCurrent))
    11. LogCreater_Log.AddNewLogInfo("SQL Tabelle Update.", "UpdateInstrumentInSQLDatabase", LogCreater_ClassName, LogTime, Nothing, Instrument_Tabel.TableName)
    12. End If
    13. If Adding Then
    14. SQL_DataAdapter_Temp.Update(Instrument_Tabel.Select("", "", DataViewRowState.Added))
    15. LogCreater_Log.AddNewLogInfo("SQL Tabelle Datensätze hinzugefügt.", "UpdateInstrumentInSQLDatabase", LogCreater_ClassName, LogTime, Nothing, Instrument_Tabel.TableName)
    16. End If
    17. Catch ex As Exception
    18. LogCreater_Log.AddNewLogFailure(ex.Message, "UpdateInstrumentInSQLDatabase", LogCreater_ClassName, LogTime, Nothing, Instrument_Tabel.TableName)
    19. End Try
    20. End Sub