User1248258412 posted
Hi, Currently I have the code as below, it working fine for insert row by row.
But I need help to edit the code for bulk insert into Oracle. if any row hit error, whole batch also stop inserting. Kindly advice, any comment will be appreciated.
urgent help needed.. did anyone know how to bulk insert the data I read from text file? it many contain many line, and after split the date.
1. need to check each line it's same source no? each text file can contain only 1 source no, so 1 text file contain few source no, it need to fail the insert as well.
2. check source no with database, if duplicate then cannot insert.
3. if all good then perform bulk insert, if any row fail, whole batch cannot insert into database.
Dim oraConn As OleDb.OleDbConnection = Nothing
Dim cmd As OleDb.OleDbCommand = Nothing
Dim cmdChk As OleDb.OleDbCommand = Nothing
oraConn.Open()
Dim cmdInsert As OleDb.OleDbCommand
Dim objReader As New StreamReader(FileNameX)
Dim sLine As String = ""
Dim arrText As New ArrayList()
Do
sLine = objReader.ReadLine()
If Not sLine Is Nothing Then
arrText.Add(sLine)
End If
Loop Until sLine Is Nothing
For Each sLine In arrText
Try
arrLine = Split(sLine, ";")
If arrLine.Length <> 0 Then
Class_ID = arrLine(0)
USER_ID = arrLine(1)
SOURCE_NO = arrLine(2)
'check duplicate
Dim strSelect As String = "Select * From tblClass Where Class_ID =:Class_ID "
cmdChk = New OleDb.OleDbCommand(strSelect, oraConn)
cmdChk.Parameters.Add(New OleDb.OleDbParameter(":Class_ID ", SqlDbType.VarChar))
cmdChk.Parameters(0).Value = Class_ID
rChk = cmdChk.ExecuteReader(CommandBehavior.SingleRow)
If rChk.HasRows Then
errorFound = True
Else
Try
Dim stSQLx As String = "INSERT INTO tblClass (Class_ID, USER_ID, SOURCE_NO) VALUES(?,?,?)"
cmdInsert = New OleDb.OleDbCommand(stSQLx, oraConn)
cmdInsert.Parameters.Add(New OleDb.OleDbParameter(":Class_ID ", SqlDbType.VarChar))
cmdInsert.Parameters(0).Value = CLASS_ID
cmdInsert.Parameters.Add(New OleDb.OleDbParameter(":USER_ID", SqlDbType.VarChar))
cmdInsert.Parameters(1).Value = USER_ID
cmdInsert.Parameters.Add(New OleDb.OleDbParameter(":SOURCE_NO", SqlDbType.VarChar))
cmdInsert.Parameters(2).Value = SOURCE_NO
cmdInsert.ExecuteNonQuery()
Catch ex As Exception
errorFound = True
End Try
End If
End If
Next
End Try
