How to Create CLR Store Procedure in VB.NET.

CLR Store Procedure:

Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security. With the CLR hosted in Microsoft SQL Server (called CLR integration),

you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Create CLR Store Procedure Step by Step:

There are various steps include to create the store procedure, that are:

• Step first Open Microsoft Visual Studio >> Click on New Project >> Select Database Projects >> SQL Server Project.
  
  
  

• Step second You can choose reference of existing database connection or click on Add New Reference.
  
  
  

• Step third If you select from existing references, skip step 3 else add new database reference as shown in the following image and click on Test Connection to test the connection.

• Step four Once the database reference and debugging option is selected, the project will be displayed in Solution Explorer. Select the project and right click on Solution Explorer >> Click on Add >> Stored Procedure.
  
  
  

• Step five Once you select the template, it will createStoredProcedure1 .vb file with the content shown.

• Step six write these code:
  
  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  StoredProcedure1 ()
          Dim sql As SqlCommand
          Dim connectionAs SqlConnection
          connection = New SqlConnection("context connection=true")
          sql = New SqlCommand("select*from info", connection)
          SqlContext.Pipe.ExecuteAndSend(sql)     
      End Sub
  End Class
  
  

• To Execute your clr procedure write these code in Sql server:
  
  sp_configure 'clr enabled', 1
  RECONFIGURE
  exec dbo.StoredProcedure1

Output:

               

Benefits of Clr Store Procedure:

There are various advantages of CLR Store Procedure, that are

• Gives better results while executing complex logic, intense string operation or string manipulations, cryptography, accessing system resources and file management, etc.

• CLR Stored Procedures are managed codes so ensures type safety, memory management, etc.

• Better code management and provides object oriented programming capability thus enables encapsulation, polymorphism & inheritance.

• Convenient for programmer as CLR Stored Procedures can be written in C#, VB or any other language that the .NET Framework supports.

• CLR Stored Procedures can also be used with Oracle 10g Release 2 or later versions.

Additional Resources: MSDN