Building Database Objects with the .NET Framework

Microsoft SQL Server 2005 (including Microsoft SQL Server Express) supports building database objects with the .NET Framework. For example, you can create user-defined types, stored procedures, user-defined functions, and triggers written with the Visual Basic .NET or C# programming language.

The SQL language is optimized for retrieving database records. However, it is a crazy language that doesn't look like any other computer language on earth. Doing basic string parsing with SQL, for example, is a painful experience. Doing complex logic in a stored procedure is next to impossible (although many people do it).

When you work in the .NET Framework, on the other hand, you have access to thousands of classes. You can perform complex string matching and manipulation by using the Regular expression classes. You can implement business logic, no matter how complex.

By taking advantage of the .NET framework when writing database objects, you no longer have to struggle with the SQL language when implementing your business logic. In this section, you learn how to build both user-defined types and stored procedures by using the .NET Framework.

Enabling CLR Integration

By default, support for building database objects with the .NET Framework is disabled. You must enable CLR integration by executing the following SQL Server command:

sp_configure 'clr enabled', 1 RECONFIGURE 

When using SQL Express, you can execute these two commands by right-clicking a database in the Database Explorer window and selecting the New Query menu option. Enter the following string:

sp_configure 'clr enabled', 1; RECONFIGURE 

Select Query Designer, Execute SQL to execute the commands (see Figure 16.20). You'll receive warnings that the query can't be parsed, which you can safely ignore.

Figure 16.20. Executing a database query in Visual Web Developer.

Creating User-Defined Types with the .NET Framework

You can create a new user-defined type by creating either a .NET class or .NET structure. After you create a user-defined type, you can use it in exactly the same way as the built-in SQL types such as the Int, NVarChar, or Decimal types. For example, you can create a new type and use the type to define a column in a database table.

To create a user-defined type with the .NET Framework, you must complete each of the following steps:

1.	Create an assembly that contains the new type.
2.	Register the assembly with SQL Server.
3.	Create a type based on the assembly.

We'll go through each of these steps and walk through the process of creating a new user-defined type. We'll create a new user-defined type named DBMovie. The DBMovie type represents information about a particular movie. The type includes properties for the Title, Director, and BoxOfficeTotals for the movie.

After we create the DBMovie type, we can use the new type to define a column in a database table. Next, we write ADO.NET code that inserts and retrieves DBMovie objects from the database.

Creating the User-Defined Type Assembly

You can create a new user-defined type by creating either a class or a structure. We create the DBMovie type by creating a new .NET class.

When creating a class that will be used as a user-defined type, you must meet certain requirements:

• The class must be decorated with a SqlUserDefinedType attribute.

• The class must be able to equal NULL.

• The class must be serializable to/from a byte array.

• The class must be serialisable to/from a string.

If you plan to use a class as a user-defined type, then you must add the SqlUserDefinedType attribute to the class. This attribute supports the following properties:

• Format Enables you to specify how a user-defined type is serialized in SQL Server. Possible values are Native and UserDefined.

• IsByteOrdered Enables you to cause the user-defined type to be ordered in the same way as its byte representation.

• IsFixedLength Enables you to specify that all instances of this type have the same length.

• MaxByteSize Enables you to specify the maximum size of the user-defined type in bytes.

• Name Enables you to specify a name for the user-defined type.

• ValidationMethodName Enables you to specify the name of a method that is called to verify whether a user-defined type is valid (useful when retrieving a user-defined type from an untrusted source).

The most important of these properties is the Format property. You use this property to specify how the user-defined type is serialized. The easiest option is to pick Native. In that case, SQL Server handles all the serialization issues and you don't need to perform any additional work.

Unfortunately, you can take advantage of native serialization only for simple classes. If your class exposes a non-value type property such as a String, then you can't use native serialization.

Because the DBMovie class includes a Title and Director property, it's necessary to use UserDefined serialization. This means that it's also necessary to implement the IBinarySerialize interface to specify how the class gets serialized.

The DBMovie class is contained in Listing 16.36.

Listing 16.36. DBMovie.vb

Imports System Imports System.Text Imports Microsoft.SqlServer.Server Imports System.Data.SqlTypes Imports System.Runtime.InteropServices Imports System.IO <SqlUserDefinedType(Format.UserDefined, MaxByteSize:=512, IsByteOrdered:=True)> _ Public Class DBMovie     Implements INullable     Implements IBinarySerialize     Private _isNull As Boolean     Private _title As String     Private _director As String     Private _boxOfficeTotals As Decimal     Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull         Get             Return _isNull         End Get     End Property     Public Shared ReadOnly Property Null() As DBMovie         Get             Dim movie As New DBMovie()             movie._isNull = True             Return movie         End Get     End Property     Public Property Title() As String         Get             Return _title         End Get         Set(ByVal Value As String)             _title = Value         End Set     End Property     Public Property Director() As String         Get             Return _director         End Get         Set(ByVal Value As String)             _director = Value         End Set     End Property     <SqlFacet(Precision:=38, Scale:=2)> _     Public Property BoxOfficeTotals() As Decimal         Get             Return _boxOfficeTotals         End Get         Set(ByVal Value As Decimal)             _boxOfficeTotals = Value         End Set     End Property     <SqlMethod(OnNullCall:=False)> _     Public Shared Function Parse(ByVal s As SqlString) As DBMovie         If (s.IsNull) Then             Return Null         End If         Dim movie As New DBMovie()         Dim parts() As String = s.Value.Split(New Char() {","c})         movie.Title = parts(0)         movie.Director = parts(1)         movie.BoxOfficeTotals = Decimal.Parse(parts(2))         Return movie     End Function     Public Overrides Function ToString() As String         If Me.IsNull Then             Return "NULL"         End If         Dim builder As New StringBuilder()         builder.Append(_title)         builder.Append(",")         builder.Append(_director)         builder.Append(",")         builder.Append(_boxOfficeTotals.ToString())         Return builder.ToString()     End Function     Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write         w.Write(_title)         w.Write(_director)         w.Write(_boxOfficeTotals)     End Sub     Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read         _title = r.ReadString()         _director = r.ReadString()         _boxOfficeTotals = r.ReadDecimal()     End Sub     Public Sub New()     End Sub End Class 

The class in Listing 16.36 exposes three properties: the movie Title, Director, and BoxOfficeTotals properties. Notice that the BoxOfficeTotals property is decorated with a SqlFacet attribute that indicates the precision and scale of the property value. You must include this attribute if you want to perform SQL queries that use comparison operators with this property.

The class in Listing 16.36 also includes both an IsNull and Null property. SQL Server uses a three-valued logic (true,False,Null). All SQL Server types must be nullable.

The DBMovie class also includes both a Parse() and a ToString() method. These methods are required for converting the DBMovie class back and forth to a string representation.

Finally, the DBMovie class includes both a Write() and Read() method. These methods are required by the IBinarySerialize interface. The Write() method serializes the class. The Read() method deserializes the class. These methods must be implemented because the class uses UserDefined serialization.

You need to compile the DBMovie class into a separate assembly (.dll file). After you create (and debug) the class, move the class from your App_Code folder to another folder in your application, such as the root folder. Next, open the SDK Command prompt and execute the following command:

vbc /t:library DBMovie.vb 

This command uses the Visual Basic command-line compiler to compile the DBMovie class into an assembly.

Registering the User-Defined Type Assembly with SQL Server

After you create the assembly that contains your user-defined type, you must register the assembly in SQL Server. You can register the DBMovie assembly by executing the following command:

CREATE ASSEMBLY DBMovie FROM 'C:\DBMovie.dll' 

You need to provide the right path for the DBMovie.dll file on your hard drive.

After you complete this step, the assembly is added to Microsoft SQL Server. When using Visual Web Developer, you can see the assembly by expanding the Assemblies folder in the Database Explorer window. Alternatively, you can view a list of all the assemblies installed on SQL Server by executing the following query:

SELECT * FROM sys.assemblies 

You can drop any assembly by executing the DROP Assembly command. For example, the following command removes the DBMovie assembly from SQL Server:

DROP Assembly DBMovie 

Creating the User-Defined Type

After you have loaded the DBMovie assembly, you can create a new user-defined type from the assembly. Execute the following command:

CREATE TYPE dbo.DBMovie EXTERNAL NAME DBMovie.DBMovie 

If you need to delete the type, you can execute the following command:

DROP TYPE DBMovie 

After you have added the type, you can use it just like any other SQL Server native type. For example, you can create a new database table with the following command:

CREATE TABLE DBMovies(Id INT IDENTITY, Movie DBMovie) 

You can insert a new record into this table with the following command:

INSERT DBMovies (Movie) VALUES ('Star Wars,George Lucas,12.34') 

Finally, you can perform queries against the table with queries like the following:

SELECT Id, Movie FROM DBMovies WHERE  Movie.BoxOfficeTotals > 13.23 SELECT  MAX(Movie.BoxOfficeTotals) FROM DBMovies SELECT  Movie FROM DBMovies WHERE Movie.Director LIKE 'g%' 

I find the fact that you can execute queries like this truly amazing.

Building a Data Access Layer with a User-Defined Type

In this final section, let's actually do something with our new user-defined type. We'll create a new data access component that uses the DBMovie class and an ASP.NET page that interfaces with the component.

Before we can do anything with the DBMovie type, we need to add a reference to the DBMovie.dll assembly to our application. In Visual Web Developer, select the menu option Website, Add Reference, and browse to the DBMovie.dll. Alternatively, you can create an application root Bin folder and copy the DBMovie.dll into the Bin folder.

Our new data access component is contained in Listing 16.37.

Listing 16.37. App_Code\DBDataLayer.vb

[View full width]

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Imports System.Collections.Generic Public Class DBDataLayer     Private Shared ReadOnly _connectionString As String     Public Function GetAll() As List(Of DBMovie)         Dim results As New List(Of DBMovie)()         Dim con As New SqlConnection(_connectionString)         Dim cmd As New SqlCommand("SELECT Movie FROM DBMovies", con)         Using con             con.Open()             Dim reader As SqlDataReader = cmd.ExecuteReader()             While reader.Read()                 Dim NewMovie As DBMovie = CType(reader("Movie"), DBMovie)                 results.Add(NewMovie)             End While         End Using         Return results     End Function     Public Sub Insert(ByVal movieToAdd As DBMovie)         Dim con As New SqlConnection(_connectionString)         Dim cmd As New SqlCommand("INSERT DBMovies (Movie) VALUES (@Movie)", con)         cmd.Parameters.Add("@Movie", SqlDbType.Udt)         cmd.Parameters("@Movie").UdtTypeName = "DBMovie"         cmd.Parameters("@Movie").Value = movieToAdd         Using con             con.Open()             cmd.ExecuteNonQuery()         End Using     End Sub     Shared Sub New()         _connectionString = WebConfigurationManager.ConnectionStrings("Movies") .ConnectionString     End Sub End Class 

The component in Listing 16.37 contains two methods: GetAll() and Insert(). The GetAll() method retrieves all the Movie objects from the DBMovies database table. Notice that you can cast the object represented by the DataReader directly to a DBMovie.

The Insert() method adds a new DBMovie to the DBMovies database table. The method creates a normal ADO.NET Command object. However, notice that a special parameter is added to the command that represents the DBMovie object.

When you create a parameter that represents a user-defined type, you must specify a UdtTypeName property that represents the name of the user-defined type. In Listing 16.38, the value DBMovie is assigned to the UdtTypeName property. When the command executes, a new DBMovie object is added to the DBMovies database table.

The page in Listing 16.38 contains a GridView, DetailsView, and ObjectDataSource control. The GridView displays all the movies from the DBMovies database table. The DetailsView control enables you to insert a new DBMovie into the database (see Figure 16.21).

Listing 16.38. ShowDBDataLayer.aspx

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>Show DBDataLayer</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         Runat="server" />     <br />     <fieldset>     <legend>Add Movie</legend>     <asp:DetailsView                  DataSource         DefaultMode="Insert"         AutoGenerateInsertButton="true"         AutoGenerateRows="false"         Runat="server">         <Fields>         <asp:BoundField DataField="Title" HeaderText="Title" />         <asp:BoundField DataField="Director" HeaderText="Director" />         <asp:BoundField DataField="BoxOfficeTotals"            HeaderText="Box Office Totals" />         </Fields>     </asp:DetailsView>     </fieldset>     <asp:ObjectDataSource                  TypeName="DBDataLayer"         DataObjectTypeName="DBMovie"         SelectMethod="GetAll"         InsertMethod="Insert"         Runat="server" />     </div>     </form> </body> </html> 

Figure 16.21. Displaying and inserting DBMovie objects.

Creating Stored Procedures with the .NET Framework

You can use the .NET Framework to build a SQL stored procedure by mapping a stored procedure to a method defined in a class. You must complete the following steps:

• Create an assembly that contains the stored procedure method.
  
  
• Register the assembly with SQL Server.
  
  
• Create a stored procedure based on the assembly.
  
  

In this section, we create two stored procedures with the .NET Framework. The first stored procedure, named GeTRandomRow(), randomly returns a single row from a database table. The second stored procedure, GetrandomRows(), randomly returns a set of rows from a database table.

Creating the Stored Procedure Assembly

Creating a stored procedure with the .NET Framework is easy. All you need to do is decorate a method with the SqlProcedure attribute.

The method used for the stored procedure must satisfy two requirements. The method must be a shared (static) method. Furthermore, the method must be implemented either as a subroutine or as a function that returns an integer value.

Within your method, you can take advantage of the SqlPipe class to send results back to your application. The SqlPipe class supports the following methods:

• Send() Enables you to send a DataReader, single-row resultset, or string.

• ExecuteAndSend() Enables you to execute a SqlCommand and send the results.

• SendResultsStart() Enables you to initiate the sending of a resultset.

• SendResultsRow() Enables you to send a single row of a resultset.

• SendResultsEnd() Enables you to end the sending of a resultset.

Within the method used for creating the stored procedure, you can use ADO.NET objects such as the SqlCommand, SqlDataReader, and SqlDataAdapter objects in the normal way. However, rather than connect to the database by using a normal connection string, you can create something called a context connection. A context connection enables you to connect to the same database server as the stored procedure without authenticating.

Here's how you can initialize a SqlConnection to use a context connection:

Dim con As New SqlConnection("context connection=true") 

Notice that you don't specify credentials or the location of the database in the connection string. Remember that the method actually executes within SQL Server. Therefore, you don't need to connect to SQL Server in the normal way.

The class in Listing 16.39 contains two methods named GetrandomRow() and GetrandomRows(). Both methods use a SqlDataAdapter to fill a DataTable with the contents of the Movies database table. The GetrandomRow() method grabs a single row from the DataTable and sends it back to the client. The GetrandomRows() method sends multiple rows back to the client.

Listing 16.39. RandomRows.vb

Imports System Imports System.Data Imports System.Data.SqlClient Imports Microsoft.SqlServer.Server Public Class RandomRows     <SqlProcedure()> _     Public Shared Sub GetRandomRow()         ' Dump all records from Movies into a DataTable         Dim dad As New SqlDataAdapter("SELECT Id,Title FROM Movies","context connection=true")         Dim dtblMovies As New DataTable()         dad.Fill(dtblMovies)         ' Grab a random row         Dim rnd As New Random()         Dim ranRow As DataRow = dtblMovies.Rows(rnd.Next(dtblMovies.Rows.Count))         ' Build a SqlDataRecord that represents the row         Dim result As New SqlDataRecord( _                  New SqlMetaData("Id", SqlDbType.Int), _                  New SqlMetaData("Title", SqlDbType.NVarChar, 100))         result.SetSqlInt32(0, CType(ranRow("Id"), Integer))         result.SetSqlString(1, CType(ranRow("Title"), String))         ' Send result         SqlContext.Pipe.Send(result)     End Sub     <SqlProcedure> _     Public Shared Sub GetRandomRows(ByVal rowsToReturn As Integer)         ' Dump all records from Movies into a DataTable         Dim dad As New SqlDataAdapter("SELECT Id,Title FROM Movies","context connection=true")         Dim dtblMovies As New DataTable()         dad.Fill(dtblMovies)         ' Send start record         Dim result As New SqlDataRecord( _               New SqlMetaData("Id", SqlDbType.Int), _               New SqlMetaData("Title", SqlDbType.NVarChar, 100))         SqlContext.Pipe.SendResultsStart(result)         Dim rnd As Random = New Random()         For  i As integer = 0 To rowsToReturn - 1             ' Grab a random row             Dim ranRow As DataRow = dtblMovies.Rows(rnd.Next(dtblMovies.Rows.Count))             ' Set the record             result.SetSqlInt32(0, CType(ranRow("Id"), Integer))             result.SetSqlString(1, CType(ranRow("Title"), String))             ' Send record             SqlContext.Pipe.SendResultsRow(result)         Next         ' Send end record         SqlContext.Pipe.SendResultsEnd()     End Sub End Class 

You need to compile the RandomRows class into a separate assembly (.dll file). After you create (and debug) the class, move the class from your App_Code folder to another folder in your application, such as the root folder. Next, open the SDK Command prompt and execute the following command:

vbc /t:library RandomRows.vb 

This command uses the Visual Basic command-line compiler to compile the RandomRows class into an assembly.

Registering the Stored Procedure Assembly with SQL Server

After you compile the RandomRows assembly, you are ready to deploy the assembly to SQL Server. You can load the assembly into SQL Server by executing the following command:

CREATE ASSEMBLY RandomRows FROM 'C:\RandomRows.dll' 

You need to supply the proper path to the RandomRows.dll assembly on your hard drive.

If you need to remove the assembly, you can execute the following command:

DROP Assembly RandomRows 

Creating the Stored Procedures

Now that the assembly is loaded, you can create two stored procedures that correspond to the two methods defined in the assembly. Execute the following two SQL commands:

CREATE PROCEDURE GetRandomRow AS EXTERNAL NAME RandomRows.RandomRows.GetRandomRow CREATE PROCEDURE GetRandomRows(@rowsToReturn Int) AS EXTERNAL NAME RandomRows.RandomRows.GetRandomRows 

After you execute these two commands, you'll have two new stored procedures named GeTRandomRow and GetrandomRows. You can treat these stored procedures just like normal stored procedures. For example, executing the following command displays three random movies from the Movies database:

GetRandomRows 3 

If you need to delete these stored procedures, you can execute the following two commands:

DROP PROCEDURE GetRandomRow DROP PROCEDURE GetRandomRows 

Executing a .NET Stored Procedure from an ASP.NET Page

After the two stored procedures have been created, you can use the stored procedures with an ASP.NET page. For example, the component in Listing 16.40 contains two methods that call the two stored procedures.

Listing 16.40. App_Code\RandomDataLayer.vb

[View full width]

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Imports System.Collections.Generic Public Class RandomDataLayer     Private Shared ReadOnly _connectionString As String     Public Function GetRandomMovies() As List(Of String)         Dim results As New List(Of String)()         Dim con As New SqlConnection(_connectionString)         Dim cmd As New SqlCommand("GetRandomRows", con)         cmd.CommandType = CommandType.StoredProcedure         cmd.Parameters.AddWithValue("@rowsToReturn", 5)         Using con             con.Open()             Dim reader As SqlDataReader = cmd.ExecuteReader()             While reader.Read()                 results.Add(CType(reader("Title"), String))             End While         End Using         Return results     End Function     Public Shared Function GetRandomMovie() As String         Dim result As String = String.Empty         Dim con As New SqlConnection(_connectionString)         Dim cmd As New SqlCommand("GetRandomRow", con)         cmd.CommandType = CommandType.StoredProcedure         Using con             con.Open()             Dim reader As SqlDataReader = cmd.ExecuteReader()             If reader.Read() Then                 result = CType(reader("Title"), String)             End If         End Using         Return result     End Function     Shared Sub New()         _connectionString = WebConfigurationManager.ConnectionStrings("Movies") .ConnectionString     End Sub End Class 

In Listing 16.40, the GetrandomRow and GetrandomRows stored procedures are executed with the help of SqlCommand objects.

The page in Listing 16.41 contains a GridView and ObjectDataSource control. The ObjectDataSource control represents the RandomDataLayer component. When you request the page, a single random movie title is displayed in a Label control. Furthermore, a list of five random movie titles is displayed in the GridView control (see Figure 16.22).

Figure 16.22. Calling a .NET stored procedure from an ASP.NET page.

Listing 16.41. ShowRandomDataLayer.aspx

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">     Sub Page_Load()         lblRandomMovie.Text = RandomDataLayer.GetRandomMovie()     End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>Show RandomDataLayer</title> </head> <body>     <form  runat="server">     <div>     Random Movie:     <asp:Label                  Runat="server" />     <hr />     <asp:GridView                  DataSource         Runat="server" />     <asp:ObjectDataSource                  TypeName="RandomDataLayer"         SelectMethod="GetRandomMovies"         Runat="server" />     </div>     </form> </body> </html>