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
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"
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
Does it error out? Does it freeze there? What happens?
ASKER
Hi,
Nothing at all...
It just pass over the line where dr should be executed without any error returned...
Nothing at all...
It just pass over the line where dr should be executed without any error returned...
Hi, at this lines:
Do you want to use Me.DateTimePicker2.Text or Me.DateTimePicker2.Value ??? the same for DateTimePicker1
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"
Do you want to use Me.DateTimePicker2.Text or Me.DateTimePicker2.Value ??? the same for DateTimePicker1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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....
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....