.NET Stored Proceedures: Reading Excel Files or Transferring SQL Server Tables into Oracle Tables - Understanding the .NET CLR Stored Procedure (working with SQL Server 2000 table)
(Page 5 of 5 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 31 
