.NET Stored Procedure: Reading a Text File into an Oracle Table - How to read all the lines from the text file
(Page 4 of 5 )
In the previous sections, we have just seen how to work with only the first line from the text file. Nobody will be happy to work with only one line. Now, I shall expand the same program to meet our requirements of reading several lines (or all lines) at a time.
Instead of modifying the above code, I planned to create a new method named “ReadAll” (stands for “read all lines” from the text file). The code would be as follows:
Public Shared Sub readAllLines()
Dim sr As New StreamReader("c:\sample.txt")
Dim l As String = sr.ReadLine
Dim arContent As New ArrayList
While Not l Is Nothing
arContent.Add(l)
l = sr.ReadLine
End While
sr.Close()
Dim conn As New OracleConnection("context
connection=true")
For Each l In arContent
Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandText = "insert into scott.sampletable
values ('" & l & "')"
conn.Open()
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()
Next
End Sub
Let us go part by part. Let us consider the following code first:
Dim l As String = sr.ReadLine
Dim arContent As New ArrayList
While Not l Is Nothing
arContent.Add(l)
l = sr.ReadLine
End While
sr.Close()
The above code fragment is the heart of the method, as it reads all the lines using a simple loop and finally places all those lines into an array list called “arContent”. The next code fragment will deal with transferring from “arContent” to the table:
Dim conn As New OracleConnection("context
connection=true")
For Each l In arContent
Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandText = "insert into scott.sampletable
values ('" & l & "')"
conn.Open()
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()
Next
We used a new loop to read every line from the array list “arContent” and used the “OracleCommand” object to work with the INSERT command.
Next: How to read all the lines from the text file – in a better and more efficient way >>
More .NET Articles
More By Jagadish Chaterjee
/ 12 
