.NET Stored Procedure: Reading a Text File into an Oracle Table
(Page 1 of 5 )
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.
A downloadable file for this article is available
here.
Getting prepared before developing .NET CLR stored procedure
I already introduced .NET based stored procedures in an Oracle database at “.NET CLR stored procedures within Oracle database: Another breaking
revolution." If you are very new to this concept, I suggest you go through that article before proceeding further.
Before proceeding further with this article, make sure that all of the following software is properly installed and configured on your system:
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.NETOnce 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.
Next: Deploying the Oracle based .NET CLR stored procedure using Visual Studio.NET >>
More .NET Articles
More By Jagadish Chaterjee
/ 12 
