SQL Datenbank Thread sicher updaten

  • VB.NET
  • .NET (FX) 4.5–4.8

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


    VB.NET-Quellcode

    1. ​
    2. 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)
    4. Dim LogTime As Date = System.DateTime.Now
    6. Try
    8. Dim mycb As New SqlCommandBuilder(SQL_DataAdapter_Temp)
    10. If Deleted Then
    11. SQL_DataAdapter_Temp.Update(Instrument_Tabel.Select("", "", DataViewRowState.Deleted))
    12. LogCreater_Log.AddNewLogInfo("SQL Tabelle Datensätze gelöscht.", "UpdateInstrumentInSQLDatabase", LogCreater_ClassName, LogTime, Nothing, Instrument_Tabel.TableName)
    13. End If
    15. If Modification Then
    16. SQL_DataAdapter_Temp.Update(Instrument_Tabel.Select("", "", DataViewRowState.ModifiedCurrent))
    17. LogCreater_Log.AddNewLogInfo("SQL Tabelle Update.", "UpdateInstrumentInSQLDatabase", LogCreater_ClassName, LogTime, Nothing, Instrument_Tabel.TableName)
    18. End If
    20. If Adding Then
    21. SQL_DataAdapter_Temp.Update(Instrument_Tabel.Select("", "", DataViewRowState.Added))
    22. LogCreater_Log.AddNewLogInfo("SQL Tabelle Datensätze hinzugefügt.", "UpdateInstrumentInSQLDatabase", LogCreater_ClassName, LogTime, Nothing, Instrument_Tabel.TableName)
    23. End If
    25. Catch ex As Exception
    27. LogCreater_Log.AddNewLogFailure(ex.Message, "UpdateInstrumentInSQLDatabase", LogCreater_ClassName, LogTime, Nothing, Instrument_Tabel.TableName)
    29. End Try
    31. End Sub

Facebook
Telefonspende