.NET Stored Proceedures: Reading Excel Files or Transferring SQL Server Tables into Oracle Tables
In this article, I shall introduce you to developing a .NET based CLR stored procedure which can read a simple Excel file and transfer that information into an Oracle database using Visual Studio.NET. Once we have completed the stored procedure with the Excel file, we will further extend it with a SQL Server 2000 table.
This article has two downloadable files associated with it. You can get them here and here.
Getting prepared before developing the .NET CLR stored procedure
I already introduced the subject of .NET based stored procedures in an Oracle database at ".NETCLR stored procedures within Oracle database: Another breaking revolution." If you are very new to this concept (or having trouble working with the Oracle wizard in this article), 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 an Excel file from the file system, the first need is to create a simple Excel file (in this article, I named the file “book1.xls”) with a few rows of information. 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 network drive).
As this is a demonstration, I simply worked with the table “emp” which relies on the SCOTT schema. My entire source code focuses on the “SCOTT” schema. I request you make the necessary changes to the source code, if you create (or modify) the table in a different schema.
I assume that you already connected to the Oracle database using “Oracle Explorer” using Visual Studio.NET 2003. If you are not familiar with “Oracle Explorer”, I request 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.
Once you complete all of the steps in 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 template, Name as “Sample2” and click “OK”
Modify the code as follows:
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data.OleDb
Public Class Class1
Public Shared Sub readXL()
Dim xlconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\book1.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
Dim xlda As New OleDbDataAdapter("select * from [sheet1$]", xlconn)
Dim xldt As New DataTable
xlda.Fill(xldt)
xlda.Dispose()
xlconn.Close()
Dim conn As New OracleConnection("context connection=true")
Dim da As New OracleDataAdapter("select * from scott.tempemp", conn)
Dim cb As OracleCommandBuilder = New OracleCommandBuilder(da)
Dim ds As New DataSet
da.Fill(ds, "tempemp")
For Each xlrow As DataRow In xldt.Rows
Dim dr As DataRow = ds.Tables("tempemp").NewRow
For Each col As DataColumn In ds.Tables("tempemp").Columns
Before deploying the .NET stored procedure, let us go through some of the details of the previous program. You must have observed, “imports System.data.OLEDB” at the top. This is primarily required to deal with any “data source.” The “data source” could be any database, file system, Mail system, Word, Excel, and so on.
Let us go through the program part by part:
Dim xlconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\book1.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
Dim xlda As New OleDbDataAdapter("select * from [sheet1$]", xlconn)
Dim xldt As New DataTable
xlda.Fill(xldt)
xlda.Dispose()
xlconn.Close()
To work with Excel files, the first need is to connect to those files. I created a connection object “xlconn” to deal with an Excel file. I also created an object “OledbDataAdapter” (xlda) to file the information available in the Excel file into a data table (xldt). You must have observed the SELECT statement I wrote. The table name is nothing but the Excel Sheet name. Within the connection string, you should also observe the name of the Excel file I added.
From the above part, we managed to fetch the information from an Excel file and fill in a data table. As the reading of the Excel file is complete, the next issue is to transfer those rows into the Oracle table. Let us continue with the second part.
Dim conn As New OracleConnection("context connection=true")
Dim da As New OracleDataAdapter("select * from scott.emp", conn)
Dim cb As OracleCommandBuilder = New OracleCommandBuilder(da)
Dim ds As New DataSet
da.Fill(ds, "emp")
Within the above code fragment, I am connecting to the Oracle database and creating an OracleDataAdapter object (da) to deal directly with the database table. I am also creating an “OracleCommandBuilder” object to auto generate all the SQL commands (for INSERT, UPDATE, DELETE) internally for the OracleDataAdapter (da). Finally, I fetch the “scott.emp” table into a “dataset” (ds) with a named data table (called “emp”).
Now we need to loop through all the rows fetched from Excel and transfer them into the data table of the data set (ds). Let us go through the following code fragment:
For Each xlrow As DataRow In xldt.Rows
Dim dr As DataRow = ds.Tables("emp").NewRow
For Each col As DataColumn In ds.Tables("emp").Columns
dr(col.ColumnName) = xlrow(col.ColumnName)
Next
ds.Tables("emp").Rows.Add(dr)
Next
da.Update(ds, "emp")
da.Dispose()
conn.Close()
The above code fragment contains two nested loops. One works with the rows and the other (inner loop) works with columns. For every iteration of the outer loop, I create a new row (for dataset “ds”) and populate (or transfer) the values from the data table “xldt” using the inner loop.
The “da.Update()” automatically inserts all the copied rows from Excel into the Oracle table. And finally we close and release all of the resources of the database connection.
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 “Sample2” 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.”
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 and click “finish.”
You need to select “External” as part of security, as we are trying to access “something outside” of the Oracle database. Thus it is considered to not be "safe." This is a bit different from what we saw in my first article.
I already introduced a way to test the stored procedure from within the Visual Studio.NET environment in my previous articles. To test the above stored procedure, I just proceed with iSQLPlus.
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
readXL;
End;
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 the Oracle database.
In the previous section, I explained how to work with Excel. Now, I would like to change my direction from Excel to SQL Server 2000. This means that now we shall transfer rows from SQL Server 2000 table to an Oracle Table using the .NET stored procedure in Oracle.
The following is the code to accomplish this task:
Public Shared Sub ReadSQL()
Dim sqlconn As New SqlConnection("data source=pc2;initial catalog=northwind;user id=sa")
Dim sqlda As New SqlDataAdapter("select * from [tempemp]", sqlconn)
Dim sqldt As New DataTable
sqlda.Fill(sqldt)
sqlda.Dispose()
sqlconn.Close()
Dim conn As New OracleConnection("context connection=true")
Dim da As New OracleDataAdapter("select * from scott.tempemp", conn)
Dim cb As OracleCommandBuilder = New OracleCommandBuilder (da)
Dim ds As New DataSet
da.Fill(ds, "tempemp")
For Each xlrow As DataRow In sqldt.Rows
Dim dr As DataRow = ds.Tables("tempemp").NewRow
For Each col As DataColumn In ds.Tables ("tempemp").Columns
dr(col.ColumnName) = xlrow(col.ColumnName)
Next
ds.Tables("tempemp").Rows.Add(dr)
Next
da.Update(ds, "tempemp")
da.Dispose()
conn.Close()
End Sub
The above stored procedure directly connects to SQL Server 2000 with the help of the “.NET SQL Client provider.” The rest of this part is quite similar.
To know and work with “OracleConnection”, “OracleCommand” and other objects, I suggest you go through my series on “ODP.NET with ASP.NET” within the same 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 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
/ 39 
ASP.NET Export to Excel and Word using VB.NET and C#
