VB.NET • Define the SQL CLR Stored Procedure

Listing 1. This class module shows how to define a simple CLR SQL stored procedure using VB.NET. Notice that SQL Server 2005 introduces two new namespaces (System.Data.SqlServer and System.Data.SqlTypes), which provide access to the in-process data provider, giving you a way to access the database through the current context.

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes

Partial Public Class StoredProcedures
        <SqlProcedure()> Public Shared Sub SimpleStoredProc()
                ' Get the current connection and output pipe for this context
                Dim objSqlCommand As SqlCommand = SqlContext.GetCommand()
                Dim objSqlPipe As SqlPipe = SqlContext.GetPipe()

                ' Execute our query
                objSqlCommand.CommandText = "SELECT @@VERSION"

                ' Return the result
                objSqlPipe.Send(objSqlCommand.ExecuteScalar().ToString)
        End Sub
End Class