Accessing SQLCLR Features from the Client

Accessing SQLCLR Features from the Client

Accessing most of the SQLCLR features from a client application is rather straightforward. Stored procedures, for example, are executed exactly in the same fashion as their T-SQL counterparts.

One difference you will find is using the User Defined Types that you created in SQL Server from the client. To test this, you need to add a new project to your existing solution. In Visual Studio, click File | Add | New Project. Select a Windows application in C# or Visual Basic and name the project UdtTest.

Add a reference to the SqlServerCs or SqlServerVb project by right-clicking the UdtTest project and clicking Add Reference. Click the Projects tab, and you will see your SqlServerCs or SqlServerVb project. Select it to add the reference.

If you are working with Visual Basic, you will want to add an imports statement for the SqlServerVb namespace because Visual Basic adds this by default, whereas C# does not add a namespace.

You need to change the UdtTest project to be the startup project. Do this by right-clicking the UdtTest project and clicking Set as StartUp Project.

Add a Button control and DataGridView control to the default form. Set the Button control's Text property to "UDT Test". Double-click the button to add a click handler, and add the following code.

Visual Basic

Imports System.Data Imports System.Data.SqlClient Imports SqlServerVb Public Class Form1     Private Sub button1_Click(ByVal sender As System.Object, _             ByVal e As System.EventArgs) Handles button1.Click         Using cn As New SqlConnection()             cn.ConnectionString = _                     "server=.;database=northwind;integrated security=true"             Using cmd As SqlCommand = cn.CreateCommand()                 cmd.CommandText = _                      "create table UdtTestCs (" _                        + "Id int not null, " _                        + "Distance DistanceVb not null)"                 cn.Open()                 cmd.ExecuteNonQuery()                 cmd.CommandText = _                         "insert into UdtTestCs values(@Id, @distance)"                 Dim id As SqlParameter = cmd.CreateParameter()                 id.ParameterName = "@id"                 id.DbType = DbType.Int32                 Dim distance As SqlParameter = cmd.CreateParameter()                 distance.ParameterName = "@distance"                 distance.SqlDbType = SqlDbType.Udt                 distance.UdtTypeName = "DistanceVb"                 cmd.Parameters.Add(id)                 cmd.Parameters.Add(distance)                 id.Value = 1                 distance.Value = DistanceVb.Parse("2 ft. 5 in.")                 cmd.ExecuteNonQuery()                 id.Value = 2                 distance.Value = DistanceVb.Parse("15 in.")                 cmd.ExecuteNonQuery()                 id.Value = 3                 distance.Value = DistanceVb.Parse("10 ft.")                 cmd.ExecuteNonQuery()                 id.Value = 4                 distance.Value = DistanceVb.Parse("1 ft. 23 in.")                 cmd.ExecuteNonQuery()                 cmd.CommandText = "Select * from UdtTestCs"                 cmd.Parameters.Clear()                 Dim testTable As New DataTable()                 testTable.Load(cmd.ExecuteReader())                 dataGridView1.DataSource = testTable                 cmd.CommandText = "DROP TABLE UdtTestCs"                 cmd.ExecuteNonQuery()             End Using         End Using     End Sub End Class