Building Data Access Components with ADO.NET

Connected Data Access

The ADO.NET Framework encompasses a huge number of classes. However, at its heart, it really consists of the following three classes:

• Connection—Enables you to represent a connection to a data source.
• Command—Enables you to execute a command against a data source.
• DataReader—Enables you to represent data retrieved from a data source.

Most of the other classes in the ADO.NET Framework are built from these three classes. These three classes provide you with the fundamental methods of working with database data. They enable you to connect to a database, execute commands against a database, and represent the data returned from a database.

Now that you understand the importance of these three classes, it’s safe to tell you that they don’t really exist. ADO.NET uses the Provider model. You use different sets of ADO.NET classes for communicating with different data sources.

For example, there is no such thing as the Connection class. Instead, there is the SqlConnection class, the OracleConnection class, the OleDbConnection class, and the ODBCConnection class. You use different Connection classes to connect to different data sources.

The different implementations of the Connection, Command, and DataReader classes are grouped into the following namespaces:

• System.Data.SqlClient—Contains ADO.NET classes for connecting to Microsoft SQL Server version 7.0 or higher.
• System.Data.OleDb—Contains ADO.NET classes for connecting to a data source with an OLEDB provider.
• System.Data.Odbc—Contains ADO.NET classes for connecting to a data source with an ODBC driver.
• System.Data.OracleClient—Contains ADO.NET classes for connecting to an Oracle database (requires Oracle 8i Release 3/8.1.7 Client or later).
• System.Data.SqlServerCe—Contains ADO.NET classes for connecting to SQL Server Mobile.

If you are connecting to Microsoft SQL Server 7.0 or higher, you should always use the classes from the SqlClient namespace. These classes provide the best performance because they connect directly to SQL Server at the level of the Tabular Data Stream (the low-level protocol that Microsoft SQL Server uses to communicate with applications). Of course, there are other databases in the world than Microsoft SQL Server. If you are communicating with an Oracle database, you should use the classes from the OracleClient namespace. If you are communicating with another type of database, you need to use the classes from either the OleDb or Odbc namespaces. Just about every database ever created has either an OLEDB provider or an ODBC driver.

Because ADO.NET follows the Provider model, all implementations of the Connection, Command, and DataReader classes inherit from a set of base classes. Here is a list of these base classes:

• DbConnection—The base class for all Connection classes.
• DbCommand—The base class for all Command classes.
• DbDataReader—The base class for all DataReader classes.

These base classes are contained in the System.Data.Common namespace. All the sample code in this chapter assumes that you are working with Microsoft SQL Server. Therefore, all the sample code uses the classes from the SqlClient namespace. However, because ADO.NET uses the Provider model, the methods that you would use to work with another database are very similar to the methods described in this chapter. Before you can use the classes from the SqlClient namespaces in your components and pages, you need to import the System.Data.SqlClient namespace.

Using the Connection Object

The Connection object represents a connection to a data source. When you instantiate a Connection, you pass a connection string to the constructor, which contains information about the location and security credentials required for connecting to the data source. For example, the following statement creates a SqlConnection that represents a connection to a Microsoft SQL Server database named Pubs that is located on the local machine: 

SqlConnection con = new SqlConnection(“Data Source=localhost;IntegratedSecurity=True;Initial Catalog=Pubs”);

For legacy reasons, there are a number of ways to write a connection string that does exactly the same thing. For example, the keywords Data Source, Server, Address, Addr, and Network Address are all synonyms. You can use any of these keywords to specify the location of the database server.

You can use the SqlConnectionStringBuilder class to convert any connection string into canonical syntax. For example, this class replaces the keyword Server with the keyword Data Source in a connection string.

Connected Data Access

Before you execute any commands against the data source, you first must open the connection. After you finish executing commands, you should close the connection as quickly as possible. A database connection is a valuable resource. Strive to open database connections as late as possible and close database connections as early as possible. Furthermore, always include error handling code to make sure that a database connection gets closed even when there is an exception.

For example, you can take advantage of the Using statement to force a connection to close even when an exception is raised, like this:

SqlConnection con = new SqlConnection(“Data Source=localhost;IntegratedSecurity=True;Initial Catalog=Pubs”);

SqlCommand cmd = new SqlCommand(“INSERT Titles (Title) VALUES (‘Some Title’)”, con);

using (con)

{

con.Open();

cmd.ExecuteNonQuery();

}

The using statement forces the connection to close, regardless of whether there is an error when a command is executed against the database. The using statement also disposes of the Connection object. (If you need to reuse the Connection, then you need to reinitialize it.)

Alternatively, you can use a try...catch statement to force a connection to close like this:

SqlConnection con = new SqlConnection(“Data Source=localhost;IntegratedSecurity=True;Initial Catalog=Pubs”);

SqlCommand cmd = new SqlCommand(“INSERT Titles (Title) VALUES (‘Some Title’)”, con);

try

{

con.Open();

cmd.ExecuteNonQuery();

}

finally

{

con.Close();

}

The finally clause in this try...catch statement forces the database connection to close both when there are no errors and when there are errors. Retrieving Provider Statistics When you use the SqlConnection object, you can retrieve statistics about the database commands executed with the connection. For example, you can retrieve statistics on total execution time.

The SqlConnection object supports the following properties and methods related to gathering statistics:

• StatisticsEnabled—Enables you to turn on statistics gathering.
• RetrieveStatistics()—Enables you to retrieve statistics represented with an IDictionary collection.
• ResetStatistics()—Resets all statistics to 0.

You can call the RetrieveStatistics() method multiple times on the same SqlConnection. Each time you call the method, you get another snapshot of the Connection statistics.

Here’s a list of the statistics that you can gather:

• BuffersReceived—Returns the number of TDS packets received.
• BuffersSent—Returns the number of TDS packets sent.
• BytesReceived—Returns the number of bytes received.
• BytesSent—Returns the number of bytes sent.
• ConnectionTime—Returns the total amount of time that the connection has been opened.
• CursorsOpen—Returns the number of cursors opened.
• ExecutionTime—Returns the connection execution time in milliseconds.
• IduCount—Returns the number of INSERT, DELETE, and UPDATE commands executed.
• IduRows—Returns the number of rows modified by INSERT, DELETE, and UPDATE commands.
• NetworkServerTime—Returns the amount of time spent waiting for a reply from the database server.
• PreparedExecs—Returns the number of prepared commands executed.
• Prepares—Returns the number of statements prepared.
• SelectCount—Returns the number of SELECT commands executed.
• SelectRows—Returns the number of rows selected.
• ServerRoundtrips—Returns the number of commands sent to the database that received a reply.
• SumResultSets—Returns the number of resultsets retrieved.
• Transactions—Returns the number of user transactions created.
• UnpreparedExecs—Returns the number of unprepared commands executed.

Improving Performance with Connection Pooling

Database connections are precious resources. If you want your ASP.NET application to scale to handle the demands of thousands of users, then you need to do everything in your power to prevent database connections from being wasted. Opening a database connection is a slow operation. Rather than open a new database connection each time you need to connect to a database, you can create a pool of connections that can be reused for multiple database queries.

When connection pooling is enabled, closing a connection does not really close the connection to the database server. Instead, closing the connection releases the database connection back into the pool. That way, the next time a database query is performed, a new connection to the database does not need to be opened.

When you use the SqlConnection object, connection pooling is enabled by default. By default, the ADO.NET framework keeps a maximum of 100 connections open in a connection pool. You need to be warned about two things in regard to connection pooling. First, when taking advantage of connection pooling, it is still very important to close your connections by calling the SqlConnection.Close() method. If you don’t close a connection, the connection is not returned to the pool. It might take a very long time for an unclosed connection to be reclaimed by ADO.NET.

Second, different connection pools are created for different connection strings. In particular, a different connection pool is created for each unique combination of connection string, process, application domain, and Windows identity. An exact character-by-character match is performed on the connection string. For this reason, you should always store your connection strings in the web configuration file. Don’t hardcode connection strings inside your components. If there is a slight variation between two connection strings, then separate connection pools are created, which defeats the performance gains that you get from connection pooling.

The SqlConnection object supports two methods for clearing connection pools programmatically:

• ClearAllPools—Enables you to clear all database connections from all connection pools.
• ClearPool—Enables you to clear all database connections associated with a particular SqlConnection object.

These methods are useful when you are working with a cluster of database servers. For example, if you take a database server down, you can programmatically clear the connection pool to the database server that no longer exists.

You can control how connections are pooled by using the following attributes in a connection string:

• Connection Timeout—Enables you to specify the maximum lifetime of a connection in seconds. (The default value is 0, which indicates that connections are immortal.)
• Connection Reset—Enables you to reset connections automatically when retrieved from the connection pool (default value is True).
• Enlist—Enables you to enlist a connection in the current transaction context (default value is True).
• Load Balance Timeout—Same as Connection Timeout.
• Max Pool Size—Enables you to specify the maximum number of connections kept in the connection pool (default value is 100).
• Min Pool Size—Enables you to specify the minimum number of connections kept in the connection pool (default value is 0).
• Pooling—Enables you to turn on or off connection pooling (default value is True).

Using the Command Object

The Command object represents a command that can be executed against a data source. In this section, you learn how to use the SqlCommand object to execute different types of database commands against Microsoft SQL Server. Executing a Command You can use the SqlCommand.ExecuteNonQuery() method to execute a SQL command that does not return a set of rows. You can use this method when executing SQL UPDATE, DELETE, and INSERT commands. You can also use this method when executing more specialized commands, such as a CREATE TABLE or DROP DATABASE command.

Executing a Command with Parameters

Most database commands that you execute include parameters. For example, when updating a database record, you need to supply parameters that represent the new values of the database record columns.

Never build command parameters through string concatenation because concatenating strings is an open invitation for SQL injection attacks. If a user enters the proper sequence of characters in a form field, and a SQL command is built through concatenation, then a user can execute an arbitrary SQL command.

Always explicitly create parameters by creating instances of the SqlParameter object. When a SQL command is executed with explicit parameters, the parameters are passed individually to a SQL Server stored procedure named sp_executesql. You represent a parameter with the SqlParameter object. You can create a new SqlParameter in multiple ways. The easiest way is to call the SqlCommand.AddWithValue() method like this:

SqlCommand cmd = new SqlCommand(“INSERT Titles (Title) VALUES (@Title)”, con);

cmd.Parameters.AddWithValue(“@Title”, “ASP.NET 3.5);

The first statement creates a SqlCommand object that represents a SQL INSERT command. Notice that the command includes a parameter named @Title. The second statement adds a SqlParameter to the SqlCommand object’s Parameters collection. The AddWithValue() method enables you to add a parameter with a certain name and value. In this case, the method is used to supply the value for the @Title parameter.

When you execute the SqlCommmand, the following command is sent to Microsoft SQL Server:

exec sp_executesql N’INSERT Titles (Title) VALUES (@Title)’,N’@Title nvarchar(17)’,@Title = N’ASP.NET’

The SqlCommand object calls the sp_executesql stored procedure when it executes a command. In this case, it passes the type, size, and value of the @Title parameter to the sp_executesql stored procedure. When you use AddWithValue(), the SqlCommand object infers the type and size of the parameter for you. The method assumes that string values are SQL NVarChar values, integer values are SQL Int values, decimal values are SQL decimal values, and so on.

As an alternative to using the AddWithValue() method, you can create a SqlParameter explicitly and add the SqlParameter to a SqlCommand object’s Parameters collection. The advantage of creating a parameter explicitly is that you can specify parameter properties explicitly, such as its name, type, size, precision, scale, and direction.

For example, the following code creates a parameter named @Title with a particular data type, size, and value:

SqlCommand cmd = new SqlCommand(“INSERT Titles (Title) VALUES (@Title)”, con);

SqlParameter paramTitle = new SqlParameter();

paramTitle.ParameterName = “@Title”;

paramTitle.SqlDbType = SqlDbType.NVarChar;

paramTitle.Size = 50;

paramTitle.Value = “ASP.NET 3.5 Unleashed”;cmd.Parameters.Add(paramTitle);

If this seems like a lot of code to do something simple, then you can use one of the overloads of the Add() method to create a new SqlParameter like this:

SqlCommand cmd = new SqlCommand(“INSERT Test (Title) VALUES (@Title)”, con);

cmd.Parameters.Add(“@Title”, SqlDbType.NVarChar,50).Value = “ASP.NET 3.5”;

In general, in this book and in the code that I write, I use the AddWithValue() method to

er171

97

 create parameters. I like the AddWithValue() method because it involves the least typing.

Executing a Command That Represents a Stored Procedure

You can use a SqlCommand object to represent a Microsoft SQL Server stored procedure. For example, you can use the following two statements to create a SqlCommand object that represents a stored procedure named GetTitles:

SqlCommand cmd = new SqlCommand(“GetTitles”, con);

cmd.CommandType = CommandType.StoredProcedure;

When you execute this SqlCommand, the GetTitles stored procedure is executed.

Returning a Single Value If you need to return a single value from a database query, you can use the SqlCommand.ExecuteScalar() method. This method always returns the value of the first column from the first row of a resultset. Even when a query returns hundreds of columns and billions of rows, everything is ignored except for the value of the first column from the first row.

Notice that you have a choice here. Rather than use the ExecuteScalar() method, you can use an output parameter. You can use either method to return a single value from a database. There is no real difference in performance between using the ExecuteScalar() method with a stored procedure or using an output parameter. The approach you take is largely a matter of preference.

For performance comparisons between ExecuteScalar and output parameters, see Priya Dhawan’s article at the Microsoft MSDN website (msdn.Microsoft.com), entitled “Performance Comparison: Data Access Techniques.”

Returning a Resultset

If you need to return multiple rows of data with a SqlCommand object, then you can call the SqlCommand.ExecuteReader() method. This method returns a SqlDataReader that you can use to fetch each row of records from the database.

Using the DataReader Object

The DataReader object represents the results of a database query. You get a DataReader by calling a Command object’s ExecuteReader() method. You can verify whether a DataReader represents any rows by checking the HasRows property or calling the Read() method. The Read() method returns true when the DataReader can advance to a new row. (Calling this method also advances you to the next row.) The DataReader represents a single row of data at a time. To get the next row of data, you need to call the Read() method. When you get to the last row, the Read() method returns False.

There are multiple ways to refer to the columns returned by a DataReader. For example, imagine that you are using a SqlDataReader named reader to represent the following query: SELECT Title, Director FROM Movies If you want to retrieve the value of the Title column for the current row represented by a

DataReader, then you can use any of the following methods:

string title = (string)reader[“Title”];

string title = (string)reader[0];

string title = reader.GetString(0);

SqlString title = reader.GetSqlString(0);

The first method returns the Title column by name. The value of the Title column is returned as an Object. Therefore, you must cast the value to a string before you can assign the value to a string variable.

The second method returns the Title column by position. It also returns the value of the Title column as an Object, so you must cast the value before using it. The third method returns the Title column by position. However, it retrieves the value as a String value. You don’t need to cast the value in this case.

Finally, the last method returns the Title column by position. However, it returns the value as a SqlString rather than a normal String. A SqlString represents the value using the specialized data types defined in the System.Data.SqlTypes namespace.

There are tradeoffs between the different methods of returning a column value. Retrieving a column by its position rather than its name is faster. However, this technique also makes your code more brittle. If the order of your columns changes in your query, your code no longer works.

Returning Multiple Resultsets

A single database query can return multiple resultsets. For example, the following query returns the contents of both the MovieCategories and Movies tables as separate resultsets:

SELECT * FROM MoviesCategories;SELECT * FROM Movies

Notice that a semicolon is used to separate the two queries. Executing multiple queries in one shot can result in better performance. When you execute multiple queries with a single command, you don’t tie up multiple database connections.

Working with Multiple Active Resultsets

ADO.NET 2.0 includes a new feature named Multiple Active Results Sets (MARS). In the previous version of ADO.NET, a database connection could represent only a single resultset at a time. If you take advantage of MARS, you can represent multiple resultsets with a single database connection. Using MARS is valuable in scenarios in which you need to iterate through a resultset and perform an additional database operation for each record in the resultset. MARS is disabled by default. To enable MARS, you must include a MultipleActiveResultSets=True attribute in a connection string.

Disconnected Data Access

The ADO.NET Framework supports two models of data access. In the first part of this chapter, you saw how you can use the SqlConnection, SqlCommand, and SqlDataReader objects to connect to a database and retrieve data. When you read data from a database by using a SqlDataReader object, an open connection must be maintained between your application and the database.

In this section, we examine the second model of data access supported by ADO.NET: the disconnected model. When you use the objects discussed in this section, you do not need to keep a connection to the database open.

This section discusses four new ADO.NET objects:

• DataAdapter—Enables you to transfer data from the physical database to the inmemory database and back again.
• DataTable—Represents an in-memory database table.
• DataView—Represents an in-memory database view.
• DataSet—Represents an in-memory database.

The ADO.NET objects discussed in this section are built on top of the ADO.NET objects discussed in the previous section. For example, behind the scenes, the DataAdapter uses a DataReader to retrieve data from a database.

The advantage of using the objects discussed in this section is that they provide you with more functionality. For example, you can filter and sort the rows represented by a DataView. Furthermore, you can use the DataTable object to track changes made to records and accept or reject the changes.

The big disadvantage of using the objects discussed in this section is that they tend to be slower and more resource intensive. Retrieving 500 records with a DataReader is much faster than retrieving 500 records with a DataAdapter.

Using the DataAdapter Object

The DataAdapter acts as the bridge between an in-memory database table and a physical database table. You use the DataAdapter to retrieve data from a database and populate a DataTable. You also use a DataAdapter to push changes that you have made to a DataTable back to the physical database.

SqlConnection con = new SqlConnection(...connection string...);

SqlDataAdapter dad = new SqlDataAdapter(“SELECT Title,Director FROM Movies”, con);

using (con)

{

con.Open();

dad.Fill(dtblMovies);

... Perform other database operations with connection ...

}

If a SqlConnection is already open when you call the Fill() method, the Fill() method doesn’t close it. In other words, the Fill() method maintains the state of the connection.

Performing Batch Updates

You can think of a SqlDataAdapter as a collection of four SqlCommand objects:

• SelectCommand—Represents a SqlCommand used for selecting data from a database.
• UpdateCommand—Represents a SqlCommand used for updating data in a database.
• InsertCommand—Represents a SqlCommand used for inserting data into a database.
• DeleteCommand—Represents a SqlCommand used for deleting data from a database.

You can use a DataAdapter not only when retrieving data from a database. You can also use a DataAdapter when updating, inserting, and deleting data from a database.

If you call a SqlDataAdapter object’s Update() method and pass the method a DataTable, then the SqlDataAdapter calls its UpdateCommand, InsertCommand, and DeleteCommand to make changes to the database. You can assign a SqlCommand object to each of the four properties of the SqlDataAdapter. Alternatively, you can use the SqlCommandBuilder object to create the UpdateCommand, InsertCommand, and DeleteCommand. The SqlCommandBuilder class takes a SqlDataAdapter that has a SELECT command and generates the other three commands automatically.

Using the DataTable Object

The DataTable object represents an in-memory database table. You can add rows to a DataTable with a SqlDataAdapter, with a SqlDataReader, with an XML file, or programmatically.

Selecting DataRows

You can retrieve particular rows from a DataTable by using the DataTable object’s Select() method. The Select() method accepts a filter parameter. You can use just about anything that you would use in a SQL WHERE clause with the filter parameter. When you retrieve an array of rows with the Select() method, you can also specify a sort order for the rows. When specifying a sort order, you can use any expression that you would use with a SQL ORDER BY clause.

DataRow States and DataRow Versions

When you modify the rows in a DataTable, the DataTable keeps track of the changes that you make. A DataTable maintains both the original and modified version of each row.

Each row in a DataTable has a particular RowState that has one of the following values:

• Unchanged—The row has not been changed.
• Added—The row has been added.
• Modified—The row has been modified.
• Deleted—The row has been deleted.
• Detached—The row has been created but not added to the DataTable.

Each row in a DataTable can have more than one version. Each version is represented by one of the following values of the DataRowVersion enumeration:

• Current—The current version of the row.
• Default—The default version of the row.
• Original—The original version of the row.
• Proposed—The version of a row that exists during editing.

You can use the DataTable.AcceptChanges() method to copy the current versions of all the rows to the original versions of all the rows. And you can use the DataTable. RejectChanges() method to copy the original versions of all the rows to the current versions of all the rows.

Using the DataView Object

The DataView object represents an in-memory database view. You can use a DataView object to create a sortable, filterable view of a DataTable.

The DataView object supports three important properties:

• Sort—Enables you to sort the rows represented by the DataView.
• RowFilter—Enables you to filter the rows represented by the DataView.
• RowStateFilter—Enables you to filter the rows represented by the DataView according to the row state (for example, OriginalRows, CurrentRows, Unchanged).

The easiest way to create a new DataView is to use the DefaultView property exposed by the DataTable class like this:

Dim dataView1 As DataView = dataTable1.DefaultView;

The DefaultView property returns an unsorted, unfiltered view of the data contained in a DataTable.

Using the DataSet Object

The DataSet object represents an in-memory database. A single DataSet can contain one or many DataTable objects. You can define parent/child relationships between the DataTable objects contained in a DataSet.

Executing Asynchronous Database Commands

ADO.NET 2.0 supports asynchronous database commands. Normally, when you execute a database command, the thread that is executing the command must wait until the command is finished before executing any additional code. In other words, normally, when you execute a database command, the thread is blocked.

When you take advantage of asynchronous commands, on the other hand, the database command is executed on another thread so that the current thread can continue performing other work. For example, you can use the current thread to execute yet another database command.

There are two reasons that you might want to use asynchronous database commands when building an ASP.NET page. First, executing multiple database commands simultaneously can significantly improve your application’s performance. This is especially true when the database commands are executed against different database servers.

Second, the ASP.NET Framework uses a limited thread pool to service page requests. When the ASP.NET Framework receives a request for a page, it assigns a thread to handle the request. If the ASP.NET Framework runs out of threads, the request is queued until a thread becomes available. If too many threads are queued, then the framework rejects the page request with a 503—Server Too Busy response code.

If you execute a database command asynchronously, then the current thread is released back into the thread pool so that it can be used to service another page request. While the asynchronous database command is executing, the ASP.NET Framework can devote its attention to handling other page requests. When the asynchronous command completes, the framework reassigns a thread to the original request and the page finishes executing.

You can configure the ASP.NET thread pool with the httpRuntime element in the web configuration file. You can modify the appRequestQueueLimit, minFreeThreads, and minLocalRequestFreeThreads attributes to control how many requests the ASP.NET Framework queues before giving up and sending an error.

There are two parts to this task undertaken in this section. A data access component that supports asynchronous ADO.NET methods must be created, as well as an ASP.NET page that executes asynchronously.

Using Asynchronous ADO.NET Methods

ADO.NET 2.0 introduces asynchronous versions of several of its methods. These methods come in pairs: a Begin and End method. For example, the SqlCommand object supports the following asynchronous methods:

. BeginExecuteNonQuery()

. EndExecuteNonQuery()

. BeginExecuteReader()

. EndExecuteReader()

. BeginExecuteXmlReader()

. EndExecuteXmlReader()

The idea is that when you execute the Begin method, the asynchronous task is started on a separate thread. When the method finishes executing, you can use the End method to get the results. To use these asynchronous methods, you must use a special attribute in your connection string: the Asynchronous Processing=true attribute.

Using Asynchronous ASP.NET Pages

When you take advantage of asynchronous ADO.NET methods, you must also enable asynchronous ASP.NET page execution. You enable an asynchronous ASP.NET page by adding the following two attributes to a page directive:

<%@ Page Async=”true” AsyncTimeout=”8” %>

The first attribute enables asynchronous page execution. The second attribute specifies a timeout value in seconds. The timeout value specifies the amount of time that the page gives a set of asynchronous tasks to complete before the page continues execution. After you enable asynchronous page execution, you must set up the asychronous tasks and register the tasks with the page. You represent each asynchronous task with an instance of the PageAsyncTask object. You register an asynchronous task for a page by calling the Page.RegisterAsyncTask() method.

The constructor for the PageAsyncTask object accepts the following parameters:

• beginHandler—The method that executes when the asynchronous task begins.
• endHandler—The method that executes when the asynchronous task ends.
• timoutHandler—The method that executes when the asynchronous task runs out of time according to the Page directive’s AsyncTimeout attribute.
• state—An arbitrary object that represents state information.
• executeInParallel—A Boolean value that indicates whether multiple asynchronous tasks should execute at the same time or execute in sequence.

You can create multiple PageAsyncTask objects and register them for the same page. When you call the ExecuteRegisteredAsyncTasks() method, all the registered tasks are executed.

If an asynchronous task does not complete within the time alloted by the AsyncTimeout attribute, then the timoutHandler method executes.

As an alternative to using the Page.RegisterAsyncTask() method to register an asynchronous task, you can use the Page.AddOnPreRenderCompleteAsync() method. However, this latter method does not provide you with as many options.

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

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.

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 serializable 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 userdefined 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 nonvalue type property such as a String, then you can’t use native serialization.

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.

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.

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:

1. Create an assembly that contains the stored procedure method.

2. Register the assembly with SQL Server.

3. 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:

SqlConnection con = new SqlConnection(“context connection=true”);

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.