Introduction
With the integration of the CLR with SQL Server 2005, we can create database objects using modern object-oriented languages like VB.NET. In fact, for pure non-data access code like computation, string parsing logic etc., we should use .NET for writing the SQL server objects. It is also advisable to use managed code for writing the store procedure. Also for accessing webservices, exploring OOP's programming for better reusability and read external files, it is good to used managed store procedure.
This article is trying to explain the simple and required steps that are require starting the creation of Manage Stored Procedure using VB.NET and using them.
The Project
We will create a Visual Studio 2005 database project for the managed stored procedure.
Creating the Database project:
Open Microsoft Visual Studio 2005 and create a SQL Server Project.
File->New->Project->Database
Adding a database reference:
Now it will ask for a database reference. Add one.
Adding a Stored Procedure:
Write click on the Project and add a stored procedure.
The file SPOne.vb:
Past following lines to the file SPOne.vb. Make sure that the table Person exist on your database or give the name of your table instead of Person.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub SPOne()
Dim p As SqlPipe
Dim sCmd As New SqlCommand()
sCmd.CommandText = "Select * from Person"
p = SqlContext.Pipe
p.ExecuteAndSend(sCmd)
End Sub
End Class
Deploy the Stored Procedure:
Build the project and then deploy it.
Run the Stored Procedure:
Make sure the CLR is enabled with your SQL Server by running the following SQL.
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Now execute the Stored Procedure and you will get an output of select statement.
Make your Life follows Procedures and Stored them safely! If possible, manage them!!!!
NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# CORNER (http://www.c-sharpcorner.com/).