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
C#
using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; namespace UdtTest { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = "server=.;database=northwind;integrated security=true"; using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "create table UdtTestCs (" + "Id int not null, " + "Distance DistanceCs not null)"; cn.Open(); cmd.ExecuteNonQuery(); cmd.CommandText = "insert into UdtTestCs values(@Id, @distance)"; SqlParameter id = cmd.CreateParameter(); id.ParameterName = "@id"; id.DbType = DbType.Int32; SqlParameter distance = cmd.CreateParameter(); distance.ParameterName = "@distance"; distance.SqlDbType = SqlDbType.Udt; distance.UdtTypeName = "DistanceCs"; cmd.Parameters.Add(id); cmd.Parameters.Add(distance); id.Value = 1; distance.Value = DistanceCs.Parse("2 ft. 5 in."); cmd.ExecuteNonQuery(); id.Value = 2; distance.Value = DistanceCs.Parse("15 in."); cmd.ExecuteNonQuery(); id.Value = 3; distance.Value = DistanceCs.Parse("10 ft."); cmd.ExecuteNonQuery(); id.Value = 4; distance.Value = DistanceCs.Parse("1 ft. 23 in."); cmd.ExecuteNonQuery(); cmd.CommandText = "Select * from UdtTestCs"; cmd.Parameters.Clear(); DataTable testTable = new DataTable(); testTable.Load(cmd.ExecuteReader()); dataGridView1.DataSource = testTable; cmd.CommandText = "DROP TABLE UdtTestCs"; cmd.ExecuteNonQuery(); } } } } }
This code creates a connection to the database and then executes a series of T-SQL commands using the same SqlCommand object. The first command creates a table. After that, there are four commands to insert data. Notice that the parameter needed to have its SqlDbType set to SqlDbType.Udt and UdtTypeName was set to either DistanceCs or DistanceVb. To create an instance of the appropriate distance, the Parse method was executed. Finally, the last command retrieved the data that was placed into the table and placed the results into the Data GridView control. The result is shown in Figure 9-6. Notice that the distance is sortable because the IComparable interface was implemented on the distance UDT.
Figure 9-6: The result after adding distance objects to the database and then retrieving them into a DataGridView control.