?
Solved

data reader does not execute in Backgroundworker

Posted on 2011-05-06
6
Medium Priority
?
550 Views
Last Modified: 2012-05-11
Hi,
I have defined table on SQL Server database with 4 rules which parameters I would like to use in order to retrieve data from Oracle database and load into SQL Server....
Please See vb code below...
The thread is huge so I use background worker for the execution of the 4 rules...
The situation is as follows:
For the first rule (out of four) it works perfectly....All parameters are passed correctly and data are retrieved from Oracle and inserted on SQL Server (bulk copy)....
For the 2nd, 3rd and 4th rule all parameters are passed correctly but
Dim rdr As OracleDataReader = cmd1.ExecuteReader does not execute the Sql statement (defined within cmd1.CommandText = data_reader("Source_SQLID").ToString)....
I can not  find reason for this situation...Please if someone could help me to solve the problem...
I would appreciate a lot...
Regards
Alsam

Private Sub backgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim worker As BackgroundWorker = CType(sender, BackgroundWorker)
        Dim clbItems As List(Of String) = CType(e.Argument, List(Of String))
        Dim item As String

        For Each item In clbItems
            If (worker.CancellationPending = True) Then
                e.Cancel = True
                Exit For
            Else
                ' Perform a time consuming operation and report progress.
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
                con.Open()
                Dim cmd As New SqlCommand
                cmd.Connection = con
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT * FROM BI_A_LOAD_MAPPING WHERE [Desc] = @description "
                cmd.Parameters.Add("@description", SqlDbType.NVarChar)
                cmd.Parameters("@description").Value = item.ToString
                data_reader = cmd.ExecuteReader

                If data_reader.HasRows Then

                    Do While data_reader.Read
                        worker.ReportProgress(0, item.ToString)

                        Dim con1 As OracleConnection = New OracleConnection(ConnectionStrings.Oracle_connection(data_reader("Source_database").ToString))
                        con1.Open()

                        Dim cmd1 As New OracleCommand
                        Dim datumk As New OracleParameter
                        Dim datump As New OracleParameter

                        datumk.DbType = DbType.String
                        datumk.Value = Me.DateTimePicker2.Text.ToString
                        datumk.ParameterName = "date"

                        datump.DbType = DbType.String
                        datump.Value = Me.DateTimePicker1.Text.ToString
                        datump.ParameterName = "date1"

                        cmd1.Connection = con1
                        cmd1.CommandType = CommandType.Text
                        cmd1.Parameters.Add(datumk)
                        cmd1.Parameters.Add(datump)
                        cmd1.CommandText = data_reader("Source_SQLID").ToString
                        MsgBox(cmd1.CommandText.ToString)
                        Dim rdr As OracleDataReader = cmd1.ExecuteReader
                        Dim dt As New DataTable("My_datatable")
                        dt.Load(rdr)
                        rdr.Close()
                        con1.Close()


                        Using cn As New SqlConnection(ConnectionStrings.MicrosoftSqlServer(data_reader("Destination_server").ToString, data_reader("Destination_server").ToString))
                            cn.Open()

                            Dim cmd2 As New SqlCommand()
                            cmd2.Connection = cn
                            cmd2.CommandType = CommandType.Text
                            cmd2.CommandText = data_reader("Delete_SQLID").ToString
                            cmd2.Parameters.Add("@datum", SqlDbType.NVarChar)
                            cmd2.Parameters("@datum").Value = Me.DateTimePicker2.Text.ToString
                            cmd2.ExecuteNonQuery()

                            Using copy As New SqlBulkCopy(cn)
                                copy.DestinationTableName = data_reader("Destination_table").ToString
                                copy.WriteToServer(dt)
                            End Using
                        End Using
                    Loop
                End If
            End If
        Next

    End Sub

Open in new window

0
Comment
Question by:alsam
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35706126
Does it error out? Does it freeze there? What happens?
0
 
LVL 1

Author Comment

by:alsam
ID: 35706274
Hi,
Nothing at all...
It just pass over the line where dr should be executed without any error returned...
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35706424
Hi, at this lines:
datumk.DbType = DbType.String
datumk.Value = Me.DateTimePicker2.Text.ToString
datumk.ParameterName = "date"

datump.DbType = DbType.String
datump.Value = Me.DateTimePicker1.Text.ToString
datump.ParameterName = "date1"

Open in new window


Do you want to use Me.DateTimePicker2.Text or Me.DateTimePicker2.Value ??? the same for DateTimePicker1
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 83

Accepted Solution

by:
CodeCruiser earned 1000 total points
ID: 35706616
You are accessing the UI elements from your backgroundworker which would cause illegal cross thread call errors. Use class level variables to share these values.
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 1000 total points
ID: 35707073
ADO.Net already implements asynchronous feature so you better use it instead of the BackGroundWorker. check : http://emoreau.com/Entries/Articles/2006/11/MARS-and-Asynchronous-ADONet.aspx
0
 
LVL 1

Author Comment

by:alsam
ID: 35708783
Hi,
Thank you all for your time and effort....
Unfortunatelly my working week ended a few hours ago so I will be able to try all proposed solution not before monday....
I will let you know the results....
Thanks....



0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question