locked
URGENT help needed - bulk insert into Oracle RRS feed

  • Question

  • 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
    

    Wednesday, June 15, 2016 2:37 AM

All replies