ADO.NET, the data-access technology used within the .NET Framework, has been enhanced to operate within routines hosted by SQL Server 2008. These enhancements are fairly simple to exploit. For most operations, the only difference between coding on a client layer or within the database will be modification of a connection string. Thanks to this, .NET developers will find a shallow learning curve when picking up SQL CLR skills. And when necessary, moving code between tiers will be relatively simple.
Enhancements to ADO.NET for SQL Server Hosting
CLR stored procedures use ADO.NET objects to retrieve data from and write data to the database. These are the same objects you’re already familiar with if you use ADO.NET today: SqlCommand, SqlDataReader, DataSet, and so on. The only difference is that these can now be run in SQL Server’s process space (in-processes) instead of only on a client. When accessing SQL Server via an ADO.NET client, the SqlConnection object is instantiated, and a connection string is set, either in the constructor or using the ConnectionString property. This same process happens when instantiating an in-process connection, but the connection string has been rewired for SQL Server. Using the connection string "Context connection=true" tells SQL Server to use the same connection that spawned the CLR method as the connection from which to perform data access.
This means, in essence, that only a single change is all that’s necessary for migration of the majority of data-access code between tiers. To migrate code into SQL Server, classes and methods will still need to be appropriately decorated with attributes describing how they should function, but the only substantial code change will be to the connection string! Virtually all members of the SqlClient namespace—with the notable exception of asynchronous operations—will work within the SQL Server process space. The other major code difference between CLR routines and ADO.NET programming on clients is that inside CLR routines, the developer will generally want to communicate back to the session that invoked the routine. This communication can take any number of forms, from returning scalar values to sending back a result set from a stored procedure or table-valued function. However, until SQL Server 2005, the ADO.NET client did not include mechanisms for communicating back to the session. Now these are available through the Microsoft.SqlServer.Server namespace.
Overview of the .NET Namespaces for SQL Server
The Microsoft.SqlServer.Server namespace was added to the .NET Framework to facilitate database integration. This namespace contains the methods and attributes necessary to create CLR routines within SQL Server, and perform manipulation of database objects within those routines. The Microsoft.SqlServer.Server namespace contains attributes for defining managed routines, as well as ADO.NET methods specific to the SQL Server provider.
In order for classes and methods to be defined as hosted CLR routines, they must be decorated with attributes to tell SQL Server what they are. These attributes include, among others, SqlProcedureAttribute for defining CLR stored procedures and SqlFunctionAttribute for CLR user-defined functions. The namespace also contains ADO.NET methods that allow CLR routines to communicate back to the session that invoked them. What can be communicated back depends on the type of CLR routine. For instance, a stored procedure can return messages, errors, result sets, or an integer return value. A table-valued user-defined function, on the other hand, can return only a single result set.
When programming CLR routines that need to return data, an object called SqlContext is available. This object represents a connection back to the session that instantiated the CLR routine. Exposed by this object is another object, SqlPipe. This is the means by which data is sent back to the caller. Sending properly formatted messages or result sets “down the pipe” means that the calling session will receive the data.
Note that not all SqlContext features are available from all routine types. For instance, a scalar user-defined function cannot send back a result set. Developers must remember to carefully test CLR routines; using a feature that’s not available won’t result in a compile-time error! Instead, an error will occur at runtime when the system attempts to use the unavailable feature. It’s very important to keep this in mind during development in order to avoid problems once routines are rolled out to production systems.
Source of Information : Apress Accelerated SQL Server 2008
Enhancements to ADO.NET for SQL Server Hosting
CLR stored procedures use ADO.NET objects to retrieve data from and write data to the database. These are the same objects you’re already familiar with if you use ADO.NET today: SqlCommand, SqlDataReader, DataSet, and so on. The only difference is that these can now be run in SQL Server’s process space (in-processes) instead of only on a client. When accessing SQL Server via an ADO.NET client, the SqlConnection object is instantiated, and a connection string is set, either in the constructor or using the ConnectionString property. This same process happens when instantiating an in-process connection, but the connection string has been rewired for SQL Server. Using the connection string "Context connection=true" tells SQL Server to use the same connection that spawned the CLR method as the connection from which to perform data access.
This means, in essence, that only a single change is all that’s necessary for migration of the majority of data-access code between tiers. To migrate code into SQL Server, classes and methods will still need to be appropriately decorated with attributes describing how they should function, but the only substantial code change will be to the connection string! Virtually all members of the SqlClient namespace—with the notable exception of asynchronous operations—will work within the SQL Server process space. The other major code difference between CLR routines and ADO.NET programming on clients is that inside CLR routines, the developer will generally want to communicate back to the session that invoked the routine. This communication can take any number of forms, from returning scalar values to sending back a result set from a stored procedure or table-valued function. However, until SQL Server 2005, the ADO.NET client did not include mechanisms for communicating back to the session. Now these are available through the Microsoft.SqlServer.Server namespace.
Overview of the .NET Namespaces for SQL Server
The Microsoft.SqlServer.Server namespace was added to the .NET Framework to facilitate database integration. This namespace contains the methods and attributes necessary to create CLR routines within SQL Server, and perform manipulation of database objects within those routines. The Microsoft.SqlServer.Server namespace contains attributes for defining managed routines, as well as ADO.NET methods specific to the SQL Server provider.
In order for classes and methods to be defined as hosted CLR routines, they must be decorated with attributes to tell SQL Server what they are. These attributes include, among others, SqlProcedureAttribute for defining CLR stored procedures and SqlFunctionAttribute for CLR user-defined functions. The namespace also contains ADO.NET methods that allow CLR routines to communicate back to the session that invoked them. What can be communicated back depends on the type of CLR routine. For instance, a stored procedure can return messages, errors, result sets, or an integer return value. A table-valued user-defined function, on the other hand, can return only a single result set.
When programming CLR routines that need to return data, an object called SqlContext is available. This object represents a connection back to the session that instantiated the CLR routine. Exposed by this object is another object, SqlPipe. This is the means by which data is sent back to the caller. Sending properly formatted messages or result sets “down the pipe” means that the calling session will receive the data.
Note that not all SqlContext features are available from all routine types. For instance, a scalar user-defined function cannot send back a result set. Developers must remember to carefully test CLR routines; using a feature that’s not available won’t result in a compile-time error! Instead, an error will occur at runtime when the system attempts to use the unavailable feature. It’s very important to keep this in mind during development in order to avoid problems once routines are rolled out to production systems.
Source of Information : Apress Accelerated SQL Server 2008
|
|
0 comments
Post a Comment