Hi, currently I have text file showing as this
Class_ID, USER_ID, SOURCE_NO
10001:A01:AAA01
10002:A02:AAA01
10003:A03:AAA01
10004:A04:AAA01
10005:A05:AAA02 <-- this is wrong, I need to compare within text file as well.
Did anyone know how to bulk insert the data I read from text file? it many contain many line, and after split the data.
1. need to check each line it's same source no? each text file can contain only 1 source no, so I need to compare within the same text file, if have difference source_no then have to fail for insert the whole set.
2. check source_no with database, if already exist then cannot insert whole batch as well.
3. if all good then perform bulk insert, if any row fail, whole batch need to rollback
My code as below is tested working fine for insert data row by row.
But I need help to edit the code for bulk insert into Oracle.
I did search around and test on many way but no luck. K
Any comment will be appreciated, thanks in advance.
Class_ID, USER_ID, SOURCE_NO
10001:A01:AAA01
10002:A02:AAA01
10003:A03:AAA01
10004:A04:AAA01
10005:A05:AAA02 <-- this is wrong, I need to compare within text file as well.
Did anyone know how to bulk insert the data I read from text file? it many contain many line, and after split the data.
1. need to check each line it's same source no? each text file can contain only 1 source no, so I need to compare within the same text file, if have difference source_no then have to fail for insert the whole set.
2. check source_no with database, if already exist then cannot insert whole batch as well.
3. if all good then perform bulk insert, if any row fail, whole batch need to rollback
My code as below is tested working fine for insert data row by row.
But I need help to edit the code for bulk insert into Oracle.
I did search around and test on many way but no luck. K
Any comment will be appreciated, thanks in advance.
PHP:
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