.NET Stored Proceedures: Reading Excel Files or Transferring SQL Server Tables into Oracle Tables - Developing an Oracle based .NET CLR stored procedure using Visual Studio.NET
(Page 2 of 5 )
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
dr(col.ColumnName) = xlrow(col.ColumnName)
Next
ds.Tables("tempemp").Rows.Add(dr)
Next
da.Update(ds, "tempemp")
da.Dispose()
conn.Close()
End Sub
End Class
- Finally build the application
Next: Understanding the .NET CLR Stored Procedure (working with Excel) >>
More .NET Articles
More By Jagadish Chaterjee
/ 32 
