Home.NET .NET Stored Procedure: Reading a Text File...
.NET Stored Procedure: Reading a Text File into an Oracle Table
In this article, I shall introduce you to developing a .NET based CLR stored procedure, which can read a simple text file and transfer that information into an Oracle database using Visual Studio.NET.
As we are trying to read a text file from a file system, we first need to create a simple text file (in this article, I named the file “sample.txt”) with a few lines. The most important issue to remember is that the file needs to reside at the Oracle database server and not at the client (or with required shared permissions, if on the network drive).
The next step would be to create a table in the Oracle database to hold all those “strings” of lines in a single column. Try the following command to create a new table in Oracle as follows:
CREATE TABLE SAMPLETABLE
(
DESCRIPTION VARCHAR2(300)
)
As this is a demonstration, I suggest you create the above table “SAMPLETABLE” within the SCOTT schema. My entire source code focuses on the “SCOTT” schema. I request that you make necessary changes to the source code, if you create the table in a different schema.
I assume that you are already connected to the Oracle database using “Oracle Explorer” using Visual Studio.NET 2003. If you are not quite familiar with “Oracle Explorer,” I request that you refer to my article at “.NET CLR stored procedures within Oracle database: Another breaking revolution," linked above.
Now we shall proceed to the next section, which deals with creating the stored procedure.
Developing Oracle based .NET CLR stored procedure using Visual Studio.NET
Once you complete all of the steps in the previous section, proceed with the following steps to develop a .NET CLR based stored procedure using Visual Studio.NET.
Go to File -> New -> Project.
Within the “New Project” window, select “Visual Basic Projects” as Project Type “Oracle Project” as the template, Name as “Sample1” and click “OK” (Fig 1).
Modify the code as follows:
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO
Public Class Class1
Public Shared Sub read()
Dim sr As New StreamReader("c:\sample.txt")
Dim l As String = sr.ReadLine
sr.Close()
Dim conn As New OracleConnection("context connection=true")
Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandText = "insert into scott.sampletable values ('" & l & "')"
conn.Open()
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()
End Sub
End Class
Finally build the application
Before deploying the stored procedure, let us go through some of its details. You must have observed “imports System.IO” at the top. This is primarily required to deal with the file system (and also other input/output operations). I created an object based on the “StreamReader” class to open and read the file “sample.txt”. At the moment, I read only one line from the file (the first line only) and I am trying to insert it using the “OracleCommand” object.
Once you complete all of the steps in the previous section, proceed with the following steps to deploy the .NET CLR based stored procedure using Visual Studio.NET.
Press Ctrl+Alt+L to open “Solution Explorer.”
Right click on “Sample1” project and select “Deploy.”
Skip the welcome screen by clicking on “next.”
Select the “data connection” you already created (you can even create a new connection by clicking on the “New Connection” button) and click “next.”
In the next screen, select the “Copy assembly and generate stored procedures” option and click “next” (shown in Fig 2).
Accept the defaults in the next screen and click “next.”
Accept the defaults in the next screen and click “next.”
Select all checkboxes in the “Available methods,” select “External” for security (shown in Fig 3) and click “finish.”
You need to select “External” as part of security, as we are trying to access “something outside” the Oracle database. And thus it is considered to not be “safe.” This is a bit different from what we have seen in my first article.
I already introduced a way to test the stored procedure from within the Visual Studio.NET environment. To test the above stored procedure, I just proceed with iSQLPlus from now on.
Open any browser and go to “http://server:port/isqlplus.” Provide “Username,” “password” and “connect identifier” according to your configuration and click “login.”
Once it opens workspace, just type the following code and press “execute.”
Begin
read;
End;
You should see output something like the following (Fig 4).
That’s it. Actually, the above three lines are nothing but some simple PL/SQL statements, generally used to call (or execute) any stored procedure stored in an Oracle database.
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.
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
/ 14 
ASP.NET Export to Excel and Word using VB.NET and C#
