.NET Stored Procedures: Autogenerating INSERT Statements for rows in Oracle Tables - Developing 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 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
Next: Understanding the .NET CLR Stored Procedure >>
More .NET Articles
More By Jagadish Chaterjee
