.NET Stored Procedures: Autogenerating INSERT Statements for rows in Oracle Tables - Understanding the .NET CLR Stored Procedure
(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.IO” at the top. This is primarily required to deal with any operations on the file system. You can completely control the file system (make sure that you have the permissions) just by using “System.IO”. As I am dealing with text files here, I need to make use of it at the moment.
Let us go through the program part by part:
Public Shared Sub GenerateInsertCommands(ByVal sqlSELECT As String, ByVal TableName As String, ByVal FilePath As String)
The above statement is the main declaration for the stored procedure. It has three parameters (arguments):
- sqlSELECT
- TableName
- FilePath
“sqlSELECT” is mainly used to pass your own “SELECT” statement to retrieve whatever rows you want. Any SELECT statement is acceptable, including JOINS, SUBQUERIES, and so on. All of the rows expected from the SELECT statement are to be turned to INSERT statements.
Everyone knows that the INSERT needs to be specified with a table name. The second parameter “TableName” is required to provide the table name for the INSERT statement, from which it needs to be generated.
Finally, we need to place all the INSERT statements in a particular text file. The final parameter “FilePath” is nothing but the file name of that text file along with the required path. But you should also make sure that you have permission to use that path, to generate/create files.
Proceeding further, we have the following:
Dim conn As New OracleConnection("context connection=true")
Dim da As New OracleDataAdapter(sqlSELECT, conn)
Dim cb As OracleCommandBuilder = New OracleCommandBuilder(da)
Dim ds As New DataSet
da.Fill(ds, "temp")
da.Dispose()
conn.Close()
The above code fragment just connects to Oracle (with no external connection; it uses the internal context), executes the SELECT statement (specified through “sqlSELECT”) and fetches all rows in to the dataset “ds”. Once we get a local copy of all the rows, we can close the connection and release the resources.
We shall discuss the heart of the .NET-stored procedure in the next section.
Next: Understanding the .NET CLR Stored Procedure - continued >>
More .NET Articles
More By Jagadish Chaterjee
