Link to home
Start Free TrialLog in
Avatar of alsam
alsam

asked on

data reader does not execute in Backgroundworker

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

Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Does it error out? Does it freeze there? What happens?
Avatar of alsam
alsam

ASKER

Hi,
Nothing at all...
It just pass over the line where dr should be executed without any error returned...
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
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of alsam

ASKER

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....