Threading a Create Dataset method

14 Apr 2006 2:30 PM

Lee Moore

I am trying to develop a threaded function to create a dataset from an
Oracle database. When querying an elaborate view, queries take a while. I
would like to place and animated wait dialog up while the query runs. I
don't know much about threading, so bear with me. The following code
contains a class that works fine when threading is not used, but fails to
return a dataset when I execute the threading code. Any help would be
appreciated.

Public Class QueryClass
        Public QueryString As String
        Public DSet As DataSet
        Public TblName As String

        Public Sub GetOracleDataset()
            SyncLock GetType(QueryClass)
                Dim connectionString As String =
"Provider=OraOLEDB.Oracle;Data Source=PRODUCTION;User
Id=myuser;Password=abc123;"
                Dim dbConnection As New
System.Data.OleDb.OleDbConnection(connectionString)
                Dim dbCommand As New System.Data.OleDb.OleDbCommand
                Dim dataSet As New System.Data.DataSet(TblName)
                Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter
                DSet = New DataSet
                Try
                    dbCommand.CommandText = QueryString
                    dbCommand.Connection = dbConnection
                    dataAdapter.SelectCommand = dbCommand
                    dataAdapter.Fill(DSet, TblName)
                Catch e As Exception
                    Dim logfile As New
System.IO.StreamWriter("c:\oracle.log")
                    logfile.WriteLine(e.Message & Chr(13) & "SQL: " &
QueryString)
                    logfile.Close()
                    MsgBox(e.Message & Chr(13) & "SQL: " & QueryString)
                    DSet = Nothing
                    Exit Sub
                End Try
            End SyncLock
        End Sub
End Class

Sub DoThis()

Dim WaitForm As New dlgWait

WaitForm.Show()
WaitForm.Refresh()

Dim Qry As New QueryClass
Dim t As System.Threading.Thread

t = New Threading.Thread(AddressOf Qry.GetOracleDataset)
t.Priority = Threading.ThreadPriority.Highest

Qry.QueryString = TextBox1.Text
Qry.TblName = "tbl"

t.Start()

If IsNothing(Qry.DSet) Then
    MsgBox("No Dataset")
    Exit Sub
End If

DataGrid1.DataSource = Qry.DSet.Tables(Qry.TblName)
WaitForm.Close()

End Sub

14 Apr 2006 9:04 PM

vbnetdev

What is the nature of the exception?

--
Get a powerful web, database, application, and email hosting with KJM
Solutions
http://www.kjmsolutions.com

Show quoteHide quote

"Lee Moore" <les_n***@hotmail.com> wrote in message
news:O3eUNA9XGHA.4388@TK2MSFTNGP03.phx.gbl...
>I am trying to develop a threaded function to create a dataset from an
>Oracle database. When querying an elaborate view, queries take a while. I
>would like to place and animated wait dialog up while the query runs. I
>don't know much about threading, so bear with me. The following code
>contains a class that works fine when threading is not used, but fails to
>return a dataset when I execute the threading code. Any help would be
>appreciated.
>
> Public Class QueryClass
>        Public QueryString As String
>        Public DSet As DataSet
>        Public TblName As String
>
>        Public Sub GetOracleDataset()
>            SyncLock GetType(QueryClass)
>                Dim connectionString As String =
> "Provider=OraOLEDB.Oracle;Data Source=PRODUCTION;User
> Id=myuser;Password=abc123;"
>                Dim dbConnection As New
> System.Data.OleDb.OleDbConnection(connectionString)
>                Dim dbCommand As New System.Data.OleDb.OleDbCommand
>                Dim dataSet As New System.Data.DataSet(TblName)
>                Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter
>                DSet = New DataSet
>                Try
>                    dbCommand.CommandText = QueryString
>                    dbCommand.Connection = dbConnection
>                    dataAdapter.SelectCommand = dbCommand
>                    dataAdapter.Fill(DSet, TblName)
>                Catch e As Exception
>                    Dim logfile As New
> System.IO.StreamWriter("c:\oracle.log")
>                    logfile.WriteLine(e.Message & Chr(13) & "SQL: " &
> QueryString)
>                    logfile.Close()
>                    MsgBox(e.Message & Chr(13) & "SQL: " & QueryString)
>                    DSet = Nothing
>                    Exit Sub
>                End Try
>            End SyncLock
>        End Sub
> End Class
>
>
> Sub DoThis()
>
> Dim WaitForm As New dlgWait
>
> WaitForm.Show()
> WaitForm.Refresh()
>
> Dim Qry As New QueryClass
> Dim t As System.Threading.Thread
>
>
> t = New Threading.Thread(AddressOf Qry.GetOracleDataset)
> t.Priority = Threading.ThreadPriority.Highest
>
> Qry.QueryString = TextBox1.Text
> Qry.TblName = "tbl"
>
> t.Start()
>
> If IsNothing(Qry.DSet) Then
>    MsgBox("No Dataset")
>    Exit Sub
> End If
>
> DataGrid1.DataSource = Qry.DSet.Tables(Qry.TblName)
> WaitForm.Close()
>
> End Sub
>
>
>

17 Apr 2006 7:10 PM

Lee Moore

No exception, the process just never starts and the value of the dataset
property of the class is null. Remove the threading, and everything works
fine. I'm lost

Show quoteHide quote

"vbnetdev" <vbnetdev@community.nospam> wrote in message
news:uc$tQcAYGHA.3532@TK2MSFTNGP05.phx.gbl...
> What is the nature of the exception?
>
> --
> Get a powerful web, database, application, and email hosting with KJM
> Solutions
> http://www.kjmsolutions.com
>
>
>
> "Lee Moore" <les_n***@hotmail.com> wrote in message
> news:O3eUNA9XGHA.4388@TK2MSFTNGP03.phx.gbl...
>>I am trying to develop a threaded function to create a dataset from an
>>Oracle database. When querying an elaborate view, queries take a while. I
>>would like to place and animated wait dialog up while the query runs. I
>>don't know much about threading, so bear with me. The following code
>>contains a class that works fine when threading is not used, but fails to
>>return a dataset when I execute the threading code. Any help would be
>>appreciated.
>>
>> Public Class QueryClass
>>        Public QueryString As String
>>        Public DSet As DataSet
>>        Public TblName As String
>>
>>        Public Sub GetOracleDataset()
>>            SyncLock GetType(QueryClass)
>>                Dim connectionString As String =
>> "Provider=OraOLEDB.Oracle;Data Source=PRODUCTION;User
>> Id=myuser;Password=abc123;"
>>                Dim dbConnection As New
>> System.Data.OleDb.OleDbConnection(connectionString)
>>                Dim dbCommand As New System.Data.OleDb.OleDbCommand
>>                Dim dataSet As New System.Data.DataSet(TblName)
>>                Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter
>>                DSet = New DataSet
>>                Try
>>                    dbCommand.CommandText = QueryString
>>                    dbCommand.Connection = dbConnection
>>                    dataAdapter.SelectCommand = dbCommand
>>                    dataAdapter.Fill(DSet, TblName)
>>                Catch e As Exception
>>                    Dim logfile As New
>> System.IO.StreamWriter("c:\oracle.log")
>>                    logfile.WriteLine(e.Message & Chr(13) & "SQL: " &
>> QueryString)
>>                    logfile.Close()
>>                    MsgBox(e.Message & Chr(13) & "SQL: " & QueryString)
>>                    DSet = Nothing
>>                    Exit Sub
>>                End Try
>>            End SyncLock
>>        End Sub
>> End Class
>>
>>
>> Sub DoThis()
>>
>> Dim WaitForm As New dlgWait
>>
>> WaitForm.Show()
>> WaitForm.Refresh()
>>
>> Dim Qry As New QueryClass
>> Dim t As System.Threading.Thread
>>
>>
>> t = New Threading.Thread(AddressOf Qry.GetOracleDataset)
>> t.Priority = Threading.ThreadPriority.Highest
>>
>> Qry.QueryString = TextBox1.Text
>> Qry.TblName = "tbl"
>>
>> t.Start()
>>
>> If IsNothing(Qry.DSet) Then
>>    MsgBox("No Dataset")
>>    Exit Sub
>> End If
>>
>> DataGrid1.DataSource = Qry.DSet.Tables(Qry.TblName)
>> WaitForm.Close()
>>
>> End Sub
>>
>>
>>
>
>

17 Apr 2006 9:14 PM

Jim Hughes

For a start, take out the MessageBox statements when running on a background
thread. Use tracing or progress events.

Show quoteHide quote

"Lee Moore" <les_n***@hotmail.com> wrote in message
news:OncjYKlYGHA.4836@TK2MSFTNGP05.phx.gbl...
> No exception, the process just never starts and the value of the dataset
> property of the class is null. Remove the threading, and everything works
> fine. I'm lost
>
>
> "vbnetdev" <vbnetdev@community.nospam> wrote in message
> news:uc$tQcAYGHA.3532@TK2MSFTNGP05.phx.gbl...
>> What is the nature of the exception?
>>
>> --
>> Get a powerful web, database, application, and email hosting with KJM
>> Solutions
>> http://www.kjmsolutions.com
>>
>>
>>
>> "Lee Moore" <les_n***@hotmail.com> wrote in message
>> news:O3eUNA9XGHA.4388@TK2MSFTNGP03.phx.gbl...
>>>I am trying to develop a threaded function to create a dataset from an
>>>Oracle database. When querying an elaborate view, queries take a while. I
>>>would like to place and animated wait dialog up while the query runs. I
>>>don't know much about threading, so bear with me. The following code
>>>contains a class that works fine when threading is not used, but fails to
>>>return a dataset when I execute the threading code. Any help would be
>>>appreciated.
>>>
>>> Public Class QueryClass
>>>        Public QueryString As String
>>>        Public DSet As DataSet
>>>        Public TblName As String
>>>
>>>        Public Sub GetOracleDataset()
>>>            SyncLock GetType(QueryClass)
>>>                Dim connectionString As String =
>>> "Provider=OraOLEDB.Oracle;Data Source=PRODUCTION;User
>>> Id=myuser;Password=abc123;"
>>>                Dim dbConnection As New
>>> System.Data.OleDb.OleDbConnection(connectionString)
>>>                Dim dbCommand As New System.Data.OleDb.OleDbCommand
>>>                Dim dataSet As New System.Data.DataSet(TblName)
>>>                Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter
>>>                DSet = New DataSet
>>>                Try
>>>                    dbCommand.CommandText = QueryString
>>>                    dbCommand.Connection = dbConnection
>>>                    dataAdapter.SelectCommand = dbCommand
>>>                    dataAdapter.Fill(DSet, TblName)
>>>                Catch e As Exception
>>>                    Dim logfile As New
>>> System.IO.StreamWriter("c:\oracle.log")
>>>                    logfile.WriteLine(e.Message & Chr(13) & "SQL: " &
>>> QueryString)
>>>                    logfile.Close()
>>>                    MsgBox(e.Message & Chr(13) & "SQL: " & QueryString)
>>>                    DSet = Nothing
>>>                    Exit Sub
>>>                End Try
>>>            End SyncLock
>>>        End Sub
>>> End Class
>>>
>>>
>>> Sub DoThis()
>>>
>>> Dim WaitForm As New dlgWait
>>>
>>> WaitForm.Show()
>>> WaitForm.Refresh()
>>>
>>> Dim Qry As New QueryClass
>>> Dim t As System.Threading.Thread
>>>
>>>
>>> t = New Threading.Thread(AddressOf Qry.GetOracleDataset)
>>> t.Priority = Threading.ThreadPriority.Highest
>>>
>>> Qry.QueryString = TextBox1.Text
>>> Qry.TblName = "tbl"
>>>
>>> t.Start()
>>>
>>> If IsNothing(Qry.DSet) Then
>>>    MsgBox("No Dataset")
>>>    Exit Sub
>>> End If
>>>
>>> DataGrid1.DataSource = Qry.DSet.Tables(Qry.TblName)
>>> WaitForm.Close()
>>>
>>> End Sub
>>>
>>>
>>>
>>
>>
>
>

17 Apr 2006 8:50 PM

Brian Gideon

Lee,

I suspect what's happening is the read immediately after calling
Thread.Start is seeing that Qry.DSet is nothing because the thread
hasn't set it yet.

If you're using .NET 2.0 then you might find BackgroundWorker class
helpful. It simplifies of the task of executing code asynchronously.

Brian

Lee Moore wrote:
Show quoteHide quote

> I am trying to develop a threaded function to create a dataset from an
> Oracle database. When querying an elaborate view, queries take a while. I
> would like to place and animated wait dialog up while the query runs. I
> don't know much about threading, so bear with me. The following code
> contains a class that works fine when threading is not used, but fails to
> return a dataset when I execute the threading code. Any help would be
> appreciated.
>
> Public Class QueryClass
>         Public QueryString As String
>         Public DSet As DataSet
>         Public TblName As String
>
>         Public Sub GetOracleDataset()
>             SyncLock GetType(QueryClass)
>                 Dim connectionString As String =
> "Provider=OraOLEDB.Oracle;Data Source=PRODUCTION;User
> Id=myuser;Password=abc123;"
>                 Dim dbConnection As New
> System.Data.OleDb.OleDbConnection(connectionString)
>                 Dim dbCommand As New System.Data.OleDb.OleDbCommand
>                 Dim dataSet As New System.Data.DataSet(TblName)
>                 Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter
>                 DSet = New DataSet
>                 Try
>                     dbCommand.CommandText = QueryString
>                     dbCommand.Connection = dbConnection
>                     dataAdapter.SelectCommand = dbCommand
>                     dataAdapter.Fill(DSet, TblName)
>                 Catch e As Exception
>                     Dim logfile As New
> System.IO.StreamWriter("c:\oracle.log")
>                     logfile.WriteLine(e.Message & Chr(13) & "SQL: " &
> QueryString)
>                     logfile.Close()
>                     MsgBox(e.Message & Chr(13) & "SQL: " & QueryString)
>                     DSet = Nothing
>                     Exit Sub
>                 End Try
>             End SyncLock
>         End Sub
> End Class
>
>
> Sub DoThis()
>
> Dim WaitForm As New dlgWait
>
> WaitForm.Show()
> WaitForm.Refresh()
>
> Dim Qry As New QueryClass
> Dim t As System.Threading.Thread
>
>
> t = New Threading.Thread(AddressOf Qry.GetOracleDataset)
> t.Priority = Threading.ThreadPriority.Highest
>
> Qry.QueryString = TextBox1.Text
> Qry.TblName = "tbl"
>
> t.Start()
>
> If IsNothing(Qry.DSet) Then
>     MsgBox("No Dataset")
>     Exit Sub
> End If
>
> DataGrid1.DataSource = Qry.DSet.Tables(Qry.TblName)
> WaitForm.Close()
>
> End Sub