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.

image from book

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 
image from book

image from book


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();                 }             }         }     } } 
image from book

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.

image from book
Figure 9-6: The result after adding distance objects to the database and then retrieving them into a DataGridView control.


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON © 2008-2017.
If you may any questions please contact us: