.NET CLR stored procedures in SQL Server 2005 DB: Good News for Programmers, Shock for DBAs - Developing SQL Server 2005 based .NET CLR stored procedure using Visual Studio 2005
(Page 3 of 5 )
Once you complete all of the steps in the previous section, proceed with the following steps to develop a .NET CLR based stored procedure using Visual Studio 2005.
· Go to the Solution Explorer, right click on project and go to Add -> Stored Procedures (shown in Figure 6).
· Select “Stored procedure” as in the template, provide the name of the stored procedure as “dsp_IncSalary.vb” and finally click “add” (shown in Figure 7).
· Modify the code as follows:
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub dsp_IncSalary()
' Add your code here
Dim conn As New SqlConnection("context connection=true")
Dim cmd As New SqlCommand
With cmd
.Connection = conn
.CommandText = "update dbo.emp set sal = sal + 100"
.CommandType = CommandType.Text
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
End Class
Next: Deploying and testing SQL Server 2005 based .NET CLR stored procedure using Visual Studio 2005 >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 14 
