.NET Stored Procedures: Autogenerating INSERT Statements for rows in Oracle Tables - Understanding the .NET CLR Stored Procedure - continued
(Page 4 of 5 )
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.
Next: Deploying and testing the Oracle based .NET CLR stored procedure using Visual Studio.NET >>
More .NET Articles
More By Jagadish Chaterjee
