.NET Stored Procedures: Autogenerating INSERT Statements for rows in Oracle Tables

In this article, I shall introduce you to developing a .NET based CLR stored procedure, which can generate INSERT commands automatically for every row in a specified SELECT statement against an Oracle database.

Contributed by
Rating: 5 stars5 stars5 stars5 stars5 stars / 6
December 20, 2005
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

A downloadable file for this article is available here.
Preparing before developing a .NET CLR stored procedure

I already introduced .NET based stored procedures in Oracle databases in my article ".NET CLR 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:

In some real scenarios, we may need to provide INSERT commands to the customer, to have some default rows to be inserted into some of the tables.  If we have rows in an Oracle table, and we would like to generate INSERT commands for all of those, it would be very difficult to achieve (unless you are an expert in PL/SQL).  In this article I dare to achieve it, but by using a .NET stored procedure.

The .NET stored procedure that we are going to develop in this article will create a text file and place all auto-generated INSERT commands automatically.  It basically accepts any SELECT statement for fetching all the rows.  We also need to provide the table name on which the INSERT command should be based.  And finally we provide a filename (along with the path) to hold all the INSERT statements.  But you must make sure that the file is generated only at the server (unless you provide a network shared path).

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 suggest that you make the necessary changes to the source code, if you create (or modify) the table in a different schema.

I assume that you have already connected to the Oracle database using “Oracle Explorer” using Visual Studio.NET 2003.  If you are not quite familiar with “Oracle Explorer,” you will want to refer to my article linked several paragraphs above.

Now we shall proceed to the next section, which deals with creating the stored procedure.

Developing Oracle based .NET CLR stored procedure using Visual Studio.NET

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 the Project Type, “Oracle Project” as the template, Name as “OracleSample” and click “OK” 
  • Modify the code as follows:
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO
 
Public Class Class1
 
    Public Shared Sub GenerateInsertCommands(ByVal sqlSELECT As String, ByVal TableName As String, ByVal FilePath As String)
        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()
 
        Dim dr As DataRow
        Dim dc As DataColumn
        Dim dt As DataTable = ds.Tables("temp")
        ' Create an instance of StreamWriter to write text to a file.
        Dim sw As StreamWriter = New StreamWriter(FilePath)
        For Each dr In dt.Rows
            Dim ins As String = ""
            ins = "INSERT INTO "
            ins &= TableName & " "
            ins &= "( " 'starting column names
            'adding column names
            For Each dc In dt.Columns
                ins &= dc.ColumnName & ", "
            Next
            ins = ins.TrimEnd.TrimEnd(",")
            ins &= ") " 'finished column names
            ins &= " values "
            ins &= "( " 'starting column values
            For Each dc In dt.Columns
                If IsDBNull(dr(dc.ColumnName)) Then
                    ins &= "null, "
                Else
                    Select Case dc.DataType.Name
                        Case "String", "DateTime"
                            ins &= "'" & dr(dc.ColumnName) & "', "
                        Case Else
                            ins &= dr(dc.ColumnName) & ", "
                    End Select
                End If
            Next
            ins = ins.TrimEnd.TrimEnd(",")
            ins &= "); " 'finished column values
            sw.WriteLine(ins)
        Next
        sw.Close()
    End Sub
 
End Class
  • Finally build the application

Understanding the .NET CLR Stored Procedure

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.

Understanding the .NET CLR Stored Procedure - continued

In the previous section, we discussed some of the parts of the stored procedure.  In this section, we shall extend the same discussion further.

Let us consider the following code fragment:

        Dim dr As DataRow
        Dim dc As DataColumn
        Dim dt As DataTable = ds.Tables("temp")

Those are the declarations needed to hold each row (in “dr”), each column (in “dc”) available from the table (“dt”) extracted from the data set (“ds”).  Continuing, we have the following:

 Dim sw As StreamWriter = New StreamWriter(FilePath)
        For Each dr In dt.Rows
            Dim ins As String = ""
            ins = "INSERT INTO "
            ins &= TableName & " "
            ins &= "( " 'starting column names

In the above code, we started to create the text file using “StreamWriter”.  We also started a loop to go through all the rows available in the data table.  Every row tries to generate a new INSERT statement (into “ins”).  We start framing the INSERT statement as above.  At this moment, no column names are specified.  We need to add the column names dynamically.  The following code does this:

            'adding column names
            For Each dc In dt.Columns
                ins &= dc.ColumnName & ", "
            Next
            ins = ins.TrimEnd.TrimEnd(",")
            ins &= ") " 'finished column names
            ins &= " values "
            ins &= "( " 'starting column values

Within the above code fragment, we are going through each column available within that row and adding the column name to the INSERT statement.  Once the adding is completed, we need to remove the last comma and close the columns section.  Next we open the “values” section and start adding the values using the following code:

            For Each dc In dt.Columns

                If IsDBNull(dr(dc.ColumnName)) Then
                    ins &= "null, "
                Else
                    Select Case dc.DataType.Name
                        Case "String", "DateTime"
                            ins &= "'" & dr(dc.ColumnName) & "', "
                        Case Else
                            ins &= dr(dc.ColumnName) & ", "
                    End Select
                End If
            Next
            ins = ins.TrimEnd.TrimEnd(",")
            ins &= "); " 'finished column values
            sw.WriteLine(ins)
        Next
        sw.Close()

In the above code, I am testing mainly for NULLS and data types.  I am generating the INSERT statement accordingly based on the data types.  Finally, we write the INSERT statement into the file using the “sw.Writeline” statement.  Finally, we close the file with the last statement in the above code fragment.

Deploying and testing the Oracle based .NET CLR stored procedure using Visual Studio.NET

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 “OracleSample” 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, because we are trying to access “something outside” of the Oracle database.  And thus it is considered to not be “safe.”  This is a bit different from what we saw in my first article.

I already introduced testing 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 the “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
GenerateINSERTcommands(“select * from
emp”,”emp”,”c:\sample.txt”);
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.

To know and work with “OracleConnection”, “OracleCommand” and other objects, I suggest you go through my series on “ODP.NET with ASP.NET” within this 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 

blog comments powered by Disqus
.NET ARTICLES

- .Net 4.5 Brings Changes
- Understanding Events in VB.NET
- Objects, Properties, Events and Methods in V...
- Install Visual Web Developer Express 2010
- Microsoft Gadgeteer an Open Source Alternati...
- Best DotNetNuke Modules
- Facebook Image Viewer in Visual Basic
- Murach`s ADO.NET 4 Database Programming with...
- 5 Must Have Visual Studio 2010 Extensions
- Dynamic Web Applications with ASP.NET Mono u...
- PDFSharp: HTML to PDF in ASP.NET 3.5 using V...
- Using the PDFSharp Library in ASP.NET 3.5 wi...
- Sending Email in ASP.NET 3.5 using VB.NET wi...
- ASP.NET 3.5 Role Based Security and User Aut...
- Creating ASP.NET Login Web Pages and Basic C...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 10 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials