Programming SQL Server .NET

for RuBoard
graphics/newterm.gif

As mentioned on Day 1, in its initial release, ADO.NET splits the two main programming models encapsulated in the ADO Recordset object into two objects. The DataSet is analogous, although more powerful, than a disconnected Recordset , whereas the data reader is analogous to using a firehose cursor to provide forward-only, read-only, connected access to data. However, the ADO Recordset was also used to access data that remained on the database server through dynamic, server-side cursors . Late last year, Microsoft revealed how server-side data access is to be restored ”and indeed greatly expanded ”in ADO.NET when the next release of SQL Server code, named Yukon, (and probably called SQL Server .NET) is released in 2003.

Caution

Keep in mind that the details discussed here might change as the release of Yukon approaches. This discussion is intended only to give you an idea of what you might expect in the future.


To understand how server-side access will be implemented, the following sections discuss the benefits of allowing server-side access and a quick look at how it might be implemented.

Hosting the Common Language Runtime

The extent to which ADO.NET and the server-side programming model will be integrated is made manifest by the fact that Yukon will actually host the common language runtime in the database server process. This will allow code written in .NET assemblies to be stored in and executed by the database server, which will provide the following advantages:

  • Use of Any .NET Language . Because the CLR will run in-process in Yukon, any .NET language can be used to write stored procedures, functions, triggers, and types. This frees developers from the restriction of only using Transact-SQL for server-side logic. Although T-SQL is functional, it's also obviously a fairly limited programming language.

  • Unified Programming Model . By allowing managed code to run on the server, the programming model now used by ADO.NET developers can be extended to the server. In fact, Yukon will implement a server-side .NET Data Provider that will allow ADO.NET developers to leverage their current knowledge when writing server-side code.

  • Tool Leverage . Because the code running in Yukon is simply managed code, you'll be able to leverage the same tools that you use today to build, debug, and manage ADO.NET applications. For example, VS .NET will be extended to create projects for Yukon and the debugger will be enhanced to allow integrated debugging in which you can step from client to server code across languages and across both managed and T-SQL code.

  • Performance . Because the server-side .NET Data Provider will be hosted in the same process as the database engine, it won't have to use tabular data stream (TDS) to communicate with the server. Instead, it will have direct access to the server's data structures, offering it the highest performance possible. In addition, the provider will be integrated with Yukon's threading model, and Yukon will integrate with the managed garbage collector so as to efficiently use server resources. In fact, in preliminary tests, the compiled nature of managed code running on Yukon indicated that it would be much faster for complex expressions than the equivalent T-SQL expressions.

Accessing Data

As mentioned in the previous section, with Yukon, a server-side .NET Data Provider will be shipped that implements the ADO.NET programming model for writing code that lives on the server. That provider will likely contain the System.Data.SqlServer namespace.

From within a VS .NET project, you can then reference the SqlServer namespace and write functions, stored procedures, triggers, and types as methods exposed in a managed class. Those methods will make heavy use of the existing System.Data.SqlTypes namespace as the types for parameters passed to the methods and return values.

Note

The types in the SqlTypes namespace have the major advantage of mapping directly to SQL Server types, of course, but also handle NULL values as expected for SQL Server, which decreases the amount of code you need to write.


For example, you could write a function to calculate the revenue generated for a particular book, as shown in Listing 21.1.

Listing 21.1 Creating a function. This listing shows how you might create a function in Yukon using managed code.
 using System.Data.SqlServer; using System.Data.SqlTypes; public class Books {   public static SqlMoney RevByBook( string isbn )   {     SqlCommand cmd = SqlContext.GetCommand( );     cmd.CommandText = "SELECT SUM(Quantity * UnitPrice) AS Revenue " +     "FROM OrderDetails WHERE ISBN = @isbn";     SqlParameter param = cmd.Parameters.Add("@isbn",         SqlDbType.NVarChar, 10);     param.Value = isbn;     SqlMoney amount = cmd.ExecuteScalar( );     return amount;   } } 
graphics/analysis.gif

As you can see from Listing 21.1, the Books class simply contains the RevByBook method that accepts an ISBN and returns the amount of revenue generated using the SqlMoney type from the SqlTypes namespace.

Because the SqlServer namespace implements the common ADO.NET provider programming model, its SqlCommand object supports the CommandText and ExecuteScalar methods and exposes a set of SqlParameter objects associated with the command. Note that the command is generated using a static method of the SqlContext object that exposes information about the current context in which the code is executing, such as the connection information.

After the assembly is compiled, it can be saved in the database using a set of extensions for T-SQL. For example, the CREATE ASSEMBLY statement can be used to load the assembly into the server and register its contents like so:

 CREATE ASSEMBLY CbksServer FROM '\server1\sql\cbksserver.dll'        WITH PERMISSION_SET = SAFE        WITH AUTOREGISTER 

In this case, the assembly is loaded into the server and referenced as CbksServer. The PERMISSION SET attribute specifies which of the code access security (CAS) permission sets to associate with the assembly. It is thought that assemblies will be able to be assigned SAFE , EXTERNAL_ACCESS , or UNRESTRICTRED permission sets, where SAFE allows only data access and computation, EXTERNAL_ACCESS allows calls to other managed code, and UNRESTRICTRED allows unmanaged and other managed code to be called similarly to extended stored procedures today. As you might expect, only a system administrator will be able to place assemblies in the third category.

Note

It's important to remember that once the assembly has been created, as in the previous snippet, the actual bits for the assembly are stored inside the database server, so the path to the original assembly needn't be saved. This allows the server to fully manage the assembly, for example, so that it will be a part of database backups and restores .


The AUTOREGISTER attribute can be used to automatically register specific methods in the assembly as stored procedures, functions, triggers, or types using attributes in the SqlServer namespace. If the attributes are not present, you must then use the extensions to the T-SQL CREATE FUNCTION , CREATE PROCEDURE , CREATE TRIGGER , and CREATE TYPE statements. For example, the CREATE FUNCTION statement could then be written as follows :

 CREATE FUNCTION RevByBook (@isbn nchar(10)) RETURNS money EXTERNAL NAME 'CbksServer:Books.RevByBook' DETERMINISTIC RETURNS NULL ON NULL INPUT 

As you can see, the RevByBook function accepts the same ISBN and returns the money SQL Server data type. The function is then mapped to the RevByBook method of the Books class in the "CbksServer" assembly.

Note

There will be some restrictions on how the managed methods can be defined in order to map to specific SQL Server objects. For example, functions must be static and deterministic although stored procedures will be able to contain data definition language (DDL) and return result sets directly to clients .


To assist in the development of managed code for the server, you'll likely see two major improvements when Yukon ships:

  • SQL Server Work Bench . This management tool, which will ship with Yukon, can be thought of as a code-management subset of VS .NET that makes deployment and versioning of assemblies and managing projects in the database easier. For example, it will contain a way to deploy assemblies to multiple servers.

  • Extensions to VS .NET . VS .NET will also be extended with new project types that make it easier to create "server assemblies" that integrate T-SQL and managed code. These templates will ensure that the projects fit the requirements for deployment in Yukon. For example, by making sure that the code is verifiable and can be used with the predefined permission sets mentioned previously.

As a whole, this deep level of integration with Yukon will both enable you to leverage your ADO.NET skills on the server side and make your applications more responsive and maintainable .

for RuBoard


Sams Teach Yourself Ado. Net in 21 Days
Sams Teach Yourself ADO.NET in 21 Days
ISBN: 0672323869
EAN: 2147483647
Year: 2002
Pages: 158
Authors: Dan Fox

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net