.NET Stored Proceedures: Reading Excel Files or Transferring SQL Server Tables into Oracle Tables - Understanding the .NET CLR Stored Procedure (working with Excel)
(Page 3 of 5 )
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.
Next: Deploying and testing Oracle based .NET CLR stored procedure using Visual Studio.NET >>
More .NET Articles
More By Jagadish Chaterjee
/ 38 
