.NET CLR stored procedures within Oracle database: Another breaking revolution - Developing an Oracle based .NET CLR stored procedure using Visual Studio.NET
(Page 3 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 Project Type “Oracle Project” as template, Name as “CLRSample” and click “OK” (shown in Fig5).
- Modify the code as follows:
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Public Class Class1
Public Shared Sub IncrementSalary(ByVal empno As Integer, ByVal incrementValue As Double)
' Add code here.
Dim conn As New OracleConnection("context connection=true")
conn.Open()
Dim cmd As OracleCommand = conn.CreateCommand
cmd.CommandText = "UPDATE scott.emp SET sal = sal + " & incrementValue & " WHERE empno = " & empno
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()
End Sub
End Class
- Press Ctrl+Alt+L to open “Solution Explorer.”
- Right click on “CLRSample” project and select “Deploy” (shown in Figure 6).
- 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” (shown in Figure 7).
- Accept the defaults in the next screen (shown in Figure 8) and click “next.”
- Accept the defaults in the next screen (shown in Figure 9) and click “next.”
- Select all checkboxes in the “Available methods” (shown in Figure 10) and click “finish.”
Next: Testing an Oracle based .NET CLR stored procedure using Visual Studio.NET >>
More .NET Articles
More By Jagadish Chaterjee
