I will discuss the implementation by discussing each of the above layers, starting with the database layer.
Database Objects using Managed Code
One of the neat features of SQL Server 2005 is the integration with the .NET CLR. The integration of CLR with SQL Server extends the capability of SQL Server in several important ways. This integration enables developers to create database objects such as stored procedures, user defined functions, and triggers by using modern object-oriented languages such as VB.NET and C#. In this article, I will demonstrate how to create the stored procedures using C#. Before looking at the code, let us understand the pros and cons of using managed language in the database tier to create server side objects.
T-SQL Vs Managed Code
Although T-SQL, the existing data access and manipulation language, is well suited for set-oriented data access operations, it also has limitations. It was designed more than a decade ago and it is a procedural language rather than an object-oriented language. The integration of the .NET CLR with SQL Server enables the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages. This is enabled by the fact that the SQL Server engine hosts the CLR in-process. All managed code that executes in the server runs within the confines of the CLR. The managed code accesses the database using ADO.NET in conjunction with the new SQL Server Data Provider. Both Visual Basic .NET and C# are modern programming languages offering full support for arrays, structured exception handling, and collections. Developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks using languages such as Visual Basic .NET and C#. Managed code is better suited than Transact-SQL for number crunching and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. T-SQL is a better candidate in situations where the code will mostly perform data access with little or no procedural logic. Even though the example you are going to see in this article is best written using T-SQL, I will take the managed code approach and show you how to leverage that feature.
Creating CLR Based Stored Procedures
For the purposes of this example, create a new SQL Server Project using Visual C# as the language of choice
in Visual Studio 2005. Since you are creating a database project, you need to associate a data source with the
project. At the time of creating the project, Visual Studio will automatically prompt you to either select an
existing database reference or add a new database reference. Choose pubs as the database. Once the project is
created, select Add Stored Procedure from the Project menu. In the Add New Item
dialog box, enter Authors.cs and click Add button. After the class is created, modify the code in the class to look like the following.
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class Authors
{
[SqlProcedure]
public static void GetAuthors()
{
SqlPipe sp = SqlContext.Pipe;
using (SqlConnection conn = new
SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandText = "Select DatePart(second, GetDate()) " +
" As timestamp,* from authors";
SqlDataReader rdr = cmd.ExecuteReader();
sp.Send(rdr);
}
}
[SqlProcedure]
public static void GetTitlesByAuthor(string authorID)
{
string sql = "select T.title, T.price, T.type, " +
"T.pubdate from authors A" +
" inner join titleauthor TA on A.au_id = TA.au_id " +
" inner join titles T on TA.title_id = T.title_id " +
" where A.au_id = '" + @authorID + "'";
using (SqlConnection conn = new
SqlConnection("context connection=true"))
{
conn.Open();
SqlPipe sp = SqlContext.Pipe;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandText = sql;
SqlParameter paramauthorID = new
SqlParameter("@authorID", SqlDbType.VarChar, 11);
paramauthorID.Direction = ParameterDirection.Input;
paramauthorID.Value = authorID;
cmd.Parameters.Add(paramauthorID);
SqlDataReader rdr = cmd.ExecuteReader();
sp.Send(rdr);
}
}
}
Let us examine the above lines of code. The above code starts by importing the required namespaces and then declares a class named Authors. There are two important classes in the Microsoft.SqlServer.Server namespace that are specific to the in-proc provider:
- SqlContext: This class encapsulates the extensions required to execute in-process code in SQL Server 2005. In addition it provides the transaction and database connection which are part of the environment in which the routine executes.
- SqlPipe: This class enables routines to send tabular results and messages to the client. This class is conceptually similar to the Response class found in ASP.NET in that it can be used to send messages to the callers.
The Authors class contains two static methods named GetAuthors and GetTitlesByAuthor. As the name suggests, the GetAuthors method simply returns all the authors from the authors table in the pubs database and the GetTitlesByAuthor method returns all the titles for a specific author.
Inside the GetAuthors method, you start by getting reference to the SqlPipe object by invoking the Pipe property of the SqlContext class.
SqlPipe sp = SqlContext.Pipe;
Then you open the connection to the database using the SqlConnection object. Note that the connection string passed to the constructor of the SqlConnection object is set to "context connection=true" meaning that you want to use the context of the logged on user to open the connection to the database.
using (SqlConnection conn = new SqlConnection("context connection=true"))
Here open the connection to the database using the Open() method.
conn.Open();
Then you create an instance of the SqlCommand object and set its properties appropriately.
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandText = "Select DatePart(second, GetDate()) " + " As timestamp,* from authors";
Finally you execute the sql query by calling the ExecuteReader method of the SqlCommand object.
SqlDataReader rdr = cmd.ExecuteReader();
Then using the SqlPipe object, you then return tabular results and messages to the client. This is accomplished using the Send method of the SqlPipe class.
sp.Send(rdr);
The Send method provides various overloads that are useful in transmitting data through the pipe to the calling application. Various overloads of the Send method are:
- Send (ISqlDataReader) - Sends the tabular results in the form of a SqlDataReader object.
- Send (ISqlDataRecord) - Sends the results in the form of a SqlDataRecord object.
- Send (ISqlError) - Sends error information in the form of a SqlError object.
- Send (String) - Sends messages in the form of a string value to the calling application.
Both the methods in the Authors class utilize one of the Send methods that allows you to send tabular results to the client application in the form of a SqlDataReader object. Since the GetTitlesByAuthor method implementation is very similar to the GetAuthors method, I will not be discussing that method in detail.
Now that the stored procedures are created, deploying it is very simple and straightforward.
Before deploying it, you need to build the project first. To build the project, select
Build->Build <ProjectName> from the menu. This will compile all the
classes in the project and if there are any compilation errors, they will be displayed in the
Error List pane. Once the project is built, you can then deploy it onto the SQL Server by selecting
Build->Deploy <ProjectName> from the menu. This will not only register the assembly in the SQL Server but also deploy the stored procedures in the SQL Server. Once the stored procedures are deployed to the SQL Server, they can then be invoked from the data access layer, which is the topic of focus in the next section.
Before executing the stored procedure, ensure you execute the following sql script using SQL Server Management Studio to enable managed code execution in the SQL Server.
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
Data Access Layer using TableAdapter Configuration Wizard
Traditionally the process you employ to create data access layer classes is a manual process, meaning that you first create a class and then add the appropriate methods to it. With the introduction of Visual Studio 2005, Microsoft has introduced a new TableAdapter Configuration Wizard that makes creating a data access logic layer class a breezy experience. Using this wizard, you can create a data access logic layer component without having to write a single line of code. This increases the productivity of the developers to a great extent. Once you create those classes, you can consume them exactly the same way you consume built-in objects. Before looking at an example, let us briefly review what a TableAdapter is. A TableAdapter connects to a database, executes queries, or stored procedures against a database, and fills a DataTable with the data returned by the query or stored procedure. In addition to filling existing data tables with data, TableAdapters can return new data tables filled with data. The TableAdapter Configuration Wizard allows you to create and edit TableAdapters in strongly typed datasets. The wizard creates TableAdapters based on SQL statements or existing stored procedures in the database. Through the wizard, you can also create new stored procedures in the database.
This section will discuss the creation of a data access component that will leverage the stored procedures
created in the previous step. To start, create a new ASP.NET web site named NTierExample in
Visual C# by selecting New Web Site from the File menu as shown below.
To create a data component, begin by right clicking on the web site and selecting Add New Item
from the context menu. In the Add New Item dialog box, select DataSet from the
list of templates. Change the name of the file to Authors.xsd and click Add.
When you click Add, you will be prompted if you want to place the component inside the App_Code directory.
Click OK in the prompt and this will bring up the TableAdapter Configuration Wizard. In the first step of the TableAdapter Configuration Wizard, you need to specify the connection string and in the second step you will be prompted if you want to save the connection string in the web.config file. In this step, save the connection string to the web.config file by checking the check box.
In the next step, you will be asked to choose a command type. Select the
Use existing stored procedures option as shown below and click Next.
Clicking Next in the above screen brings up the following screen wherein you select the stored procedure to use.
Click Next in the above dialog box and you will see the Choose Methods to Generate dialog box
wherein you can specify the name of the method that will be used to invoke the stored procedure selected in the previous
step. Specify the name of the method as GetAuthors as shown below:
Clicking Next in the above screenshot results in the following screen wherein you just hit Finish.
When you click on Finish, Visual Studio will create the required classes for you. After the classes are created, you need to rename the class to Authors. After making all the changes, the final output should look as follows.
That's all there is to creating a data access component using the TableAdapter Configuration Wizard. As you can see, all you have to do is to provide the wizard with certain information and Visual Studio hides all the complexities of creating the underlying code for you.
Now that you have created the data access layer method for the GetAuthors stored procedure, you need to do the same
thing for the GetTitlesByAuthor stored procedure. To this end, add another TableAdapter to the Authors.xsd by selecting
Data->Add->TableAdapter from the menu and follow through the wizard steps. Remember to specify the
stored procedure name as GetTitlesByAuthor this time. Note that at the time of writing this article using Visual Studio
2005 Beta 2, I encountered some problems in getting the wizard to work because of some bugs. If you run into any problem
with the wizard, simply exit from the wizard, select the appropriate TableAdapter from the designer and select
View->Properties Window from the menu. Through the properties dialog box, you should be able to perform all the configurations related to a TableAdapter.
Storing Utility Classes in App_Code Directory
You might remember that when you created the data component, you placed the data component class inside the App_Code directory, which is a special directory used by ASP.NET. It is very similar to bin directory, but with the following exceptions: While the bin directory is designed for storing pre-compiled assemblies used by your application, the App_Code directory is designed for storing class files to be compiled dynamically at run time. This allows you to store classes for business logic components, data access components, and so on in a single location in your application, and use them from any page. Because the classes are compiled dynamically at run time and automatically referenced by the application containing the App_Code directory, you don't need to build the project before deploying it, nor do you need to explicitly add a reference to the class. ASP.NET monitors the App_Code directory and when new components are added, it dynamically compiles them. This enables you to easily make changes to a component and deploy with a simple XCOPY or with a drag-and-drop operation. In addition to simplifying the deployment and referencing of components, the \App_Code directory also greatly simplifies the creation and accessing of resource files (.resx) used in localization, as well as automatically generating and compiling proxy classes for WSDL files (.wsdl).
With the introduction of this new directory, you might be wondering when to use this directory when compared to the bin directory. If you have an assembly that you want to use in your web site, create a bin subdirectory and then copy the .dll to that subdirectory. If you are creating reusable components that you want to use only from your ASP.NET pages, place them under the App_Code directory. Note that any class you add to the App_Code directory is visible only within that Web site meaning that it will not be visible outside of that Web site. So if you are creating a class that needs to be shared across multiple Web sites, you will be better off creating that class as part of a class library project and share that project among those Web sites.
