Question perform bulk insert

aoshi_kh

Member
Joined
Jun 17, 2016
Messages
6
Programming Experience
Beginner
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.

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
 

aoshi_kh

Member
Joined
Jun 17, 2016
Messages
6
Programming Experience
Beginner
thanks for you advice, while i still new to this.. can you explain more how it work for my situation below? appreacite your help.
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
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,314
Location
Sydney, Australia
Programming Experience
10+
1. When you read the first line, store the source number in a variable. As you read each subsequent row, compare the source number to that variable and break if they are not the same.
2. Query the database for the source number you have read from the file. If there's a match then break.
3. Wrap the Update call in a transaction, which can then be committed or rolled back.

Firstly, I'd suggest using a TextFieldParser to read the data. The MSDN documentation for the class has code examples. As for the ADO.NET operations, check out my code examples here. As for using a transaction, it would look something like this:
Dim table As New DataTable

'Polpulate table here.

Using connection As New OleDbConnection("connection string here"),
      command As New OleDbCommand("INSERT statement here", connection),
      adapter As New OleDbDataAdapter With {.InsertCommand = command}
    'Add parameters to command here.

    connection.Open()

    Dim transaction = connection.BeginTransaction()

    Try
        adapter.Update(table)
        transaction.Commit()
    Catch ex As Exception
        transaction.Rollback()
    End Try
End Using
 

aoshi_kh

Member
Joined
Jun 17, 2016
Messages
6
Programming Experience
Beginner
I was now able to populate the data into data table, but may I know how to compare the source_no on this?


PHP:
Using csvReader As New TextFieldParser(C:\Test\ & xLoad01)
            Dim csvData As New DataTable()
            csvReader.SetDelimiters(New String() {":"})
            csvReader.HasFieldsEnclosedInQuotes = True
            Dim colFields As String() = csvReader.ReadFields()
            For Each column As String In colFields
                Dim datecolumn As New DataColumn(column)
                datecolumn.AllowDBNull = True
                csvData.Columns.Add(datecolumn)
            Next

            While Not csvReader.EndOfData
                Dim fieldData As String() = csvReader.ReadFields()
                'Making empty value as null
                For i As Integer = 0 To fieldData.Length - 1
                    If fieldData(i) = "" Then
                        fieldData(i) = Nothing
                    End If
                Next
                csvData.Rows.Add(fieldData)
            End While
        End Using
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,314
Location
Sydney, Australia
Programming Experience
10+
may I know how to compare the source_no on this?
That's the easiest part of the whole thing. Read what I said and do that:
When you read the first line, store the source number in a variable. As you read each subsequent row, compare the source number to that variable and break if they are not the same.
If you have problems doing it then we can talk further but you're not having problems if you haven't tried.
 

aoshi_kh

Member
Joined
Jun 17, 2016
Messages
6
Programming Experience
Beginner
hi sir, i did store all into the csvdata column. but i dont know how to the source no into variable from that?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,314
Location
Sydney, Australia
Programming Experience
10+
Think it through. In order to populate a row in your DataTable you must first read the data from the CSV file into Strings. One of those Strings contains your source number. Assign that String to a variable. You're trying to make it harder than it is. Read the first row, assign the source number to the variable, populate the DataRow. You can then read the rest of the lines and, for each one, get the source number and compare it to that variable. If they match, populate another DataRow, otherwise abort.
 

aoshi_kh

Member
Joined
Jun 17, 2016
Messages
6
Programming Experience
Beginner
totally no idea how to assign the source no to variable. while is ok.. will get help from other beginner forum that can guide already.
Thanks
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,314
Location
Sydney, Australia
Programming Experience
10+
totally no idea how to assign the source no to variable.
You really are trying to make this harder than it is. How do you usually assign something to a variable?
VB.NET:
someVariable = colFields(columnIndex)
 
Top Bottom