Announcement

Collapse
No announcement yet.

Close auf DataReader dauert zu lange

Collapse
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
  • #1

    Close auf DataReader dauert zu lange

    Hallo zusammen,

    close auf Datareader benötigt sehr lange.

    in dem folgendem Beispiel werden 10000 Records
    in MSSQL Datenbank asyncron in einem BackgroudWorker
    gelesen.

    Das vorzeitige Beenden wird über einen cancel Button initiert.

    Die Subroutine SelectAsyn() bleibt jedoch sehr lange
    beim close des Datenreaders hängen ?

    Warum ?

    Mfg
    Jürgen Bartels

    #Region "BackGroundWorker mit Asyncroner DatenbankSelection"

    Private Sub BackgroundWorker2_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker2.DoWork

    SelectAsync()

    End Sub

    Private Sub SelectAsync()

    cancel = False
    Dim aCon As New SqlConnection
    aCon.ConnectionString = "Data Source=192.168.1.20;Initial Catalog=TESTDB;Persist Security Info=True;User ID=Bill;Password=Gates; Asynchronous Processing=true"

    aCon.Open()
    Dim aCmd As New SqlCommand("Select Top 10000 * from Adressen", aCon)
    Dim myresult As IAsyncResult = aCmd.BeginExecuteReader

    Dim i As Integer = 0
    Do While (Not myresult.IsCompleted)
    i += 1
    If Me.cancel = True Then
    aCmd.Cancel()
    cancel = False
    Exit Do
    End If
    Loop
    Dim myReader As SqlDataReader = aCmd.EndExecuteReader(myresult)
    i = 0
    Do While myReader.Read()
    i += 1
    If Me.cancel = True Then Exit Do
    Loop
    '------------------------------------------------------
    ' Der reader.close benötigt für 10000 Records 10 Sec
    ' wenn die Selection vorzeitig abgebrochen wurde
    ' warum ?
    '
    '------------------------------------------------------
    myReader.Close()
    aCon.Close()
    Me.cancel = True
    End Sub

    #End Region
    Tags: None
  • #2
    Hallo,
    damit der SqlDataReader jeden Datensatz über FETCH-Aufrufe einzeln beim SQL Server abholt, muss die ExecuteReader-Option <b>CommandBehavior.SingleRow</b> angegeben werden. Das folgende Testprogramm verdeutlicht das unterschiedliche Verhalten:

    Datenbank vorbereiten

    <div style="font-family: Courier New; font-size: 10pt; color: black; background: white; border-top: windowtext 1pt solid; padding-top: 0pt; border-left: windowtext 1pt solid; padding-left: 0pt; border-right: windowtext 1pt solid; padding-right: 0pt; border-bottom: windowtext 1pt solid; padding-bottom: 0pt;"><p style="margin: 0px;"><span style="color: blue;">USE </span>tempdb</p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: blue;">CREATE TABLE </span>Quelle</p><p style="margin: 0px;">(</p><p style="margin: 0px;">&nbsp; recid&nbsp;&nbsp; <span style="color: blue;">INT </span>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">NOT NULL IDENTITY PRIMARY KEY</span>,</p><p style="margin: 0px;">&nbsp; wert1&nbsp;&nbsp; <span style="color: blue;">NVARCHAR</span>(55) <span style="color: blue;">NOT NULL DEFAULT </span><span style="color: maroon;">'Das ist ein Test für eine relativ lange Zeichenkette.'</span>,</p><p style="margin: 0px;">&nbsp; wert2&nbsp;&nbsp; <span style="color: blue;">NVARCHAR</span>(25) <span style="color: blue;">NOT NULL</span>,</p><p style="margin: 0px;">&nbsp; datum&nbsp;&nbsp; <span style="color: blue;">DATETIME </span>&nbsp;&nbsp;&nbsp; <span style="color: blue;">NOT NULL DEFAULT CURRENT_TIMESTAMP</span></p><p style="margin: 0px;">)</p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: green;">-- Testdatensätze einfügen</span></p><p style="margin: 0px;"><span style="color: blue;">BEGIN TRANSACTION</span></p><p style="margin: 0px;"><span style="color: blue;">SET NOCOUNT ON</span></p><p style="margin: 0px;"><span style="color: blue;">DECLARE </span>@iCount <span style="color: blue;">INT</span></p><p style="margin: 0px;"><span style="color: blue;">DECLARE </span>@Wert&nbsp;&nbsp; <span style="color: blue;">VARCHAR</span>(99)</p><p style="margin: 0px;"><span style="color: blue;">SET </span>@iCount = 1 </p><p style="margin: 0px;"><span style="color: blue;">WHILE </span>@iCount &lt; 200000</p><p style="margin: 0px;"><span style="color: blue;">BEGIN</span></p><p style="margin: 0px;">&nbsp; <span style="color: blue;">SET </span>@Wert = <span style="color: maroon;">'Testdatensatz ' </span>+ <span style="color: blue;">CAST</span>(@iCount <span style="color: blue;">AS VARCHAR</span>)</p><p style="margin: 0px;">&nbsp; <span style="color: blue;">INSERT INTO </span>dbo.Quelle (wert2) <span style="color: blue;">VALUES </span>(@Wert)</p><p style="margin: 0px;">&nbsp; <span style="color: blue;">SET </span>@iCount = @iCount + 1</p><p style="margin: 0px;"><span style="color: blue;">END</span></p><p style="margin: 0px;"><span style="color: blue;">COMMIT TRANSACTION</span></p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: green;">-- Abfrage kann im Abfrageeditor von SQL Server Management Studio </span></p><p style="margin: 0px;"><span style="color: green;">-- jederzeit abgebrochen werden, wobei nur die Datensätze sichtbar</span></p><p style="margin: 0px;"><span style="color: green;">-- sind, die bis dahin vom Server abgeholt wurden</span></p><p style="margin: 0px;"><span style="color: blue;">SELECT </span>* <span style="color: blue;">FROM </span>dbo.Quelle </p></div>

    Testprogramm:

    <div style="font-family: Courier New; font-size: 10pt; color: black; background: white; border-top: windowtext 1pt solid; padding-top: 0pt; border-left: windowtext 1pt solid; padding-left: 0pt; border-right: windowtext 1pt solid; padding-right: 0pt; border-bottom: windowtext 1pt solid; padding-bottom: 0pt;"><p style="margin: 0px;"><span style="color: blue;">Imports</span> System.Data.SqlClient</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: blue;">Public</span> <span style="color: blue;">Class</span> Form1</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; <span style="color: blue;">Private</span> <span style="color: blue;">Sub</span> Button1_Click(<span style="color: blue;">ByVal</span> sender <span style="color: blue;">As</span> System.Object, <span style="color: blue;">ByVal</span> e <span style="color: blue;">As</span> System.EventArgs) <span style="color: blue;">Handles</span> Button1.Click</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> iStart <span style="color: blue;">As</span> <span style="color: blue;">Integer</span> = 0</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> sText <span style="color: blue;">As</span> <span style="color: blue;">String</span> = <span style="color: blue;">String</span>.Empty</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> aCon <span style="color: blue;">As</span> <span style="color: blue;">New</span> SqlConnection</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aCon.ConnectionString = <span style="color: maroon;">"Data Source=192.168.10.1;Initial Catalog=tempdb;User ID=sa;Password=2b!n2b;"</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aCon.Open()</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Try</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> aCmd <span style="color: blue;">As</span> <span style="color: blue;">New</span> SqlCommand(<span style="color: maroon;">"SELECT * FROM dbo.Quelle"</span>, aCon)</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> aDR <span style="color: blue;">As</span> SqlDataReader</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">If</span> CheckBoxSingleRow.Checked <span style="color: blue;">Then</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aDR = aCmd.ExecuteReader(CommandBehavior.SingleRow)</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Else</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aDR = aCmd.ExecuteReader()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">If</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> iCnt <span style="color: blue;">As</span> <span style="color: blue;">Integer</span> = 0</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Try</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; iStart = Environment.TickCount</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Do</span> <span style="color: blue;">While</span> aDR.Read()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: green;">' Wert der aktuellen Zeile des SqlDataReader-Datensatzes auslesen</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; sText = aDR(1).ToString()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; iCnt += 1</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">If</span> iCnt = 1000 <span style="color: blue;">Then</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">If</span> CheckBoxCancel.Checked <span style="color: blue;">Then</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Exit</span> <span style="color: blue;">Do</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">If</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">If</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Loop</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox1.Items.Add(sText)</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox1.Items.Add(iCnt.ToString())</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Finally</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox2.Items.Add(<span style="color: blue;">String</span>.Format(<span style="color: maroon;">"Tickcount vor SqlDataReader.Close: {0}"</span>, _</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Environment.TickCount - iStart))</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aDR.Close()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox2.Items.Add(<span style="color: blue;">String</span>.Format(<span style="color: maroon;">"Tickcount nach SqlDataReader.Close: {0}"</span>, _</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Environment.TickCount - iStart))</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">Try</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Finally</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox2.Items.Add(<span style="color: blue;">String</span>.Format(<span style="color: maroon;">"Tickcount vor SqlConnection.Close: {0}"</span>, _</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Environment.TickCount - iStart))</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aCon.Close()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox2.Items.Add(<span style="color: blue;">String</span>.Format(<span style="color: maroon;">"Tickcount nach SqlConnection.Close: {0}"</span>, _</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Environment.TickCount - iStart))</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">Try</span></p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">Sub</span></p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: blue;">End</span> <span style="color: blue;">Class</span></p></div&gt

    Comment

    • #3
      Hallo,
      die Frage kommt mir bekannt vor :-)

      In der Tat "rettet" der SqlDataReader die Ergebnismenge der SELECT-Abfrage, wenn die Methode <b>Close</b> aufgerufen wird. Dies führt dazu, dass auch dann alle Datensätze zum Client transportiert werden, wenn die Read-Schleife vor dem Erreichen des letzten Datensatzes verlassen wird. Der einzige Weg, um dieses Verhalten zu ändern besteht darin, vor dem Schließen des SqlDataReaders die ausgeführte Anweisung über die Methode <b>Cancel</b> abzubrechen:

      1. SqlCommand.Cancel
      2. SqlDataReader.Close

      Mit dem folgenden Beispiel lässt sich das unterschiedliche Verhalten nachweisen. Wenn die Checkbox <i>CheckBoxCancel</i> angekreuzt wird, gibt es nur dann keine Verzögerung, wenn die Cancel-Methode des SqlCommand-Instanz aufgerufen wird.

      A) Vorbereitung in der MS SQL Server-Datenbank

      <div style="font-family: Courier New; font-size: 10pt; color: black; background: white; border-top: windowtext 1pt solid; padding-top: 0pt; border-left: windowtext 1pt solid; padding-left: 0pt; border-right: windowtext 1pt solid; padding-right: 0pt; border-bottom: windowtext 1pt solid; padding-bottom: 0pt;"><p style="margin: 0px;"><span style="color: blue;">USE </span>tempdb</p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: blue;">CREATE TABLE </span>Quelle</p><p style="margin: 0px;">(</p><p style="margin: 0px;">&nbsp; recid&nbsp;&nbsp; <span style="color: blue;">INT </span>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">NOT NULL IDENTITY PRIMARY KEY</span>,</p><p style="margin: 0px;">&nbsp; wert1&nbsp;&nbsp; <span style="color: blue;">NVARCHAR</span>(55) <span style="color: blue;">NOT NULL DEFAULT </span><span style="color: maroon;">'Das ist ein Test für eine relativ lange Zeichenkette.'</span>,</p><p style="margin: 0px;">&nbsp; wert2&nbsp;&nbsp; <span style="color: blue;">NVARCHAR</span>(25) <span style="color: blue;">NOT NULL</span>,</p><p style="margin: 0px;">&nbsp; datum&nbsp;&nbsp; <span style="color: blue;">DATETIME </span>&nbsp;&nbsp;&nbsp; <span style="color: blue;">NOT NULL DEFAULT CURRENT_TIMESTAMP</span></p><p style="margin: 0px;">)</p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: green;">-- Testdatensätze einfügen</span></p><p style="margin: 0px;"><span style="color: blue;">BEGIN TRANSACTION</span></p><p style="margin: 0px;"><span style="color: blue;">SET NOCOUNT ON</span></p><p style="margin: 0px;"><span style="color: blue;">DECLARE </span>@iCount <span style="color: blue;">INT</span></p><p style="margin: 0px;"><span style="color: blue;">DECLARE </span>@Wert&nbsp;&nbsp; <span style="color: blue;">VARCHAR</span>(99)</p><p style="margin: 0px;"><span style="color: blue;">SET </span>@iCount = 1 </p><p style="margin: 0px;"><span style="color: blue;">WHILE </span>@iCount &lt; 200000</p><p style="margin: 0px;"><span style="color: blue;">BEGIN</span></p><p style="margin: 0px;">&nbsp; <span style="color: blue;">SET </span>@Wert = <span style="color: maroon;">'Testdatensatz ' </span>+ <span style="color: blue;">CAST</span>(@iCount <span style="color: blue;">AS VARCHAR</span>)</p><p style="margin: 0px;">&nbsp; <span style="color: blue;">INSERT INTO </span>dbo.Quelle (wert2) <span style="color: blue;">VALUES </span>(@Wert)</p><p style="margin: 0px;">&nbsp; <span style="color: blue;">SET </span>@iCount = @iCount + 1</p><p style="margin: 0px;"><span style="color: blue;">END</span></p><p style="margin: 0px;"><span style="color: blue;">COMMIT TRANSACTION</span></p><p style="margin: 0px;">GO</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: green;">-- Abfrage kann im Abfrageeditor von SQL Server Management Studio </span></p><p style="margin: 0px;"><span style="color: green;">-- jederzeit abgebrochen werden, wobei nur die Datensätze sichtbar</span></p><p style="margin: 0px;"><span style="color: green;">-- sind, die bis dahin vom Server abgeholt wurden</span></p><p style="margin: 0px;"><span style="color: blue;">SELECT </span>* <span style="color: blue;">FROM </span>dbo.Quelle </p><p style="margin: 0px;">&nbsp;</p></div>

      B) Testprogramm:

      <div style="font-family: Courier New; font-size: 10pt; color: black; background: white; border-top: windowtext 1pt solid; padding-top: 0pt; border-left: windowtext 1pt solid; padding-left: 0pt; border-right: windowtext 1pt solid; padding-right: 0pt; border-bottom: windowtext 1pt solid; padding-bottom: 0pt;"><p style="margin: 0px;"><span style="color: blue;">Imports</span> System.Data.SqlClient</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: blue;">Public</span> <span style="color: blue;">Class</span> Form1</p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; <span style="color: blue;">Private</span> <span style="color: blue;">Sub</span> Button1_Click(<span style="color: blue;">ByVal</span> sender <span style="color: blue;">As</span> System.Object, <span style="color: blue;">ByVal</span> e <span style="color: blue;">As</span> System.EventArgs) <span style="color: blue;">Handles</span> Button1.Click</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox1.Items.Clear()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox2.Items.Clear()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> iStart <span style="color: blue;">As</span> <span style="color: blue;">Integer</span> = 0</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> sText <span style="color: blue;">As</span> <span style="color: blue;">String</span> = <span style="color: blue;">String</span>.Empty</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> aCon <span style="color: blue;">As</span> <span style="color: blue;">New</span> SqlConnection</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aCon.ConnectionString = <span style="color: maroon;">"Data Source=192.168.10.1;Initial Catalog=tempdb;User ID=sa;Password=2b!n2b;"</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aCon.Open()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Try</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> aCmd <span style="color: blue;">As</span> <span style="color: blue;">New</span> SqlCommand(<span style="color: maroon;">"SELECT * FROM dbo.Quelle"</span>, aCon)</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> aDR <span style="color: blue;">As</span> SqlDataReader</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aDR = aCmd.ExecuteReader()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Dim</span> iCnt <span style="color: blue;">As</span> <span style="color: blue;">Integer</span> = 0</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Try</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; iStart = Environment.TickCount</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Do</span> <span style="color: blue;">While</span> aDR.Read()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: green;">' Wert der aktuellen Zeile des SqlDataReader-Datensatzes auslesen</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; sText = aDR(1).ToString()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; iCnt += 1</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">If</span> iCnt = 1000 <span style="color: blue;">Then</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">If</span> CheckBoxCancel.Checked <span style="color: blue;">Then</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">If</span> CheckBoxSqlCommandCancel.Checked <span style="color: blue;">Then</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aCmd.Cancel()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">If</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Exit</span> <span style="color: blue;">Do</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">If</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">If</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Loop</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox1.Items.Add(iCnt.ToString())</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Finally</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox2.Items.Add(<span style="color: blue;">String</span>.Format(<span style="color: maroon;">"Tickcount vor SqlDataReader.Close: {0}"</span>, _</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Environment.TickCount - iStart))</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aDR.Close()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ListBox2.Items.Add(<span style="color: blue;">String</span>.Format(<span style="color: maroon;">"Tickcount nach SqlDataReader.Close: {0}"</span>, _</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Environment.TickCount - iStart))</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">Try</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">Finally</span></p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; aCon.Close()</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">Try</span></p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;">&nbsp;&nbsp;&nbsp; <span style="color: blue;">End</span> <span style="color: blue;">Sub</span></p><p style="margin: 0px;">&nbsp;</p><p style="margin: 0px;"><span style="color: blue;">End</span> <span style="color: blue;">Class</span></p></div&gt

      Comment

      • #4
        Hallo Andreas ,

        Danke für Deinen Lösungsvorschlag.

        Funktioniert !

        Mfg
        Jürgen Bartel

        Comment

        Previous template Next
        Working...
        X