.NET Stored Procedure: Reading a Text File into an Oracle Table - How to read all the lines from the text file – in a better and more efficient way
(Page 5 of 5 )
In the previous section, the “OracleConnection” is only opened once, but we are creating an “OracleCommand” object for every line. Therefore, if we have 1000 lines of text, the loop rotates 1000 times, creating 1000 “OracleCommand” objects in memory.
Even though we tried to close all the “OracleCommand” objects before going to the next iteration, the previous method is still expensive, as there exists no possibility of “caching” the same command, which is being issued (or executed) repeatedly.
The most efficient way to deal with the above case would be something like the following code:
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()
'an efficient method
'==============================
Dim conn As New OracleConnection("context
connection=true")
Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandText = "insert into scott.sampletable values
(:line)"
conn.Open()
For Each l In arContent
cmd.Parameters.Clear()
cmd.Parameters.Add("line", OracleDbType.Varchar2).Value = l
cmd.ExecuteNonQuery()
Next
cmd.Dispose()
conn.Close()
End Sub
Even in the above case, you could see that I used a loop. But I am creating only one “OracleCommand” object (created outside the loop) for any number of iterations. Working with the “parameters” is the most efficient way to deal with several “inserts” or “updates.” Dealing with the solution in the above manner would automatically involve “server-side” caching, which gives a tremendous hit to the performance.
To know and work with “OracleConnection,” “OracleCommand” and other objects, I suggest you go through my series on “ODP.NET with ASP.NET” within this website. Other enhancements to this solution would be something like “sending the filename as parameter,” “working with multiple columns,” “working with delimiters,” and so on. You can download the entire source code (Visual studio.NET) solution here or at the beginning of this article. I leave it to the programmers for further enhancements. Any doubts, comments, suggestions, bugs, errors or feedback are welcomed at jag_chat@yahoo.com
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 12 
