Developing Client applications in SQL Server 2005

In this tutorial you will learn about developing client applications in SQL Server 2005, The context connection, Restrictions for the context connection, ADO.NET in different SQLCLR objects, Avoiding SQL CLR +ADO.NET, System.Transactions, ADO.NET and SQLCLR.

A .NET data provider is a link between an application and a data source. The link returns result sets from the data source and propagates changes to the data source. SQL Server 2005 is highly integrated with .NET Framework. Stored procedures, functions, user-defined types and user defined aggregates can be created using .NET supported programming languages. The constructs take advantage of large portions of the .NET framework infrastructure, the base class library and third party managed libraries to assist the developer in creating seamless data access applications.

One of the major areas where a developer may require data access scenarios is the computation over a large set of data or integration across systems where different servers are accessed for a database related operation. The solution to this was ADO.NET inside SQLCLR. The syntax would look like the one under written in C#

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
// Connection strings shouldn’t be hard coded for production code ;;;;;;
using(SqlConnection conn = new SqlConnection("server=EXServer; ;;;;;;;;;
database=Exforsys; user id=EXUser; password=ExPassword")) ;;;;;;;;;;;;;;
{ ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
conn.Open(); ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SqlCommand cmd = new SqlCommand( ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
"SELECT Name, GroupName FROM Courses.Department", conn); ;;;;;;;;;;;;;;;
SqlDataReader r = cmd.ExecuteReader(); ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
while(r.Read()) ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
{ ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
//Consume the data from the reader and perform some computation with it
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

Systme.Data.Sqlclient provider has been used to connect to SQL Server and the code runs inside SQLCLR.

Let us now create a project that uses ADO.NET in Visual Studio 2005.

1. Create a SQLCLR project and select the language of your choice. We shall be using C# as the language of our choice in this tutorial.
2. Set the permissions to EXTERNAL_ACCESS in the Database tab of the properties of the project. 3. Right click on the project node and select Add>New Item and select Stored Procedure.
4. Customize the template created by Visual Studio>NET to connect to another SQL Server by adding a Using System.Data.SqlClient statement for C# code.
5. Now we are ready to code the body of the procedure. A number of methods have been added to the stored procedure which can be customized by the developer. In fact there is hardly any difference between the stored procedure code and the old fashioned ADO.NET code!

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
C# ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using System.Data; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using System.Data.SqlClient; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using Microsoft.SqlServer.Server; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
public partial class StoredProcedures ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
{ ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
[Microsoft.SqlServer.Server.SqlProcedure()] ;;;;;;;;;;;;;;;;;;;;;;;;;;;;
public static void SampleSP() ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
{ ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
// as usual, connection strings shouldn’t be hardcoded for production ;;
code ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using(SqlConnection conn = new SqlConnection( "server=EXServer; ;;;;;;;;
database=Exforsys; " +"user id=EXUser; Password=EXPassword")) ;;;;;;;;;;
{ ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
conn.Open(); ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SqlCommand cmd = new SqlCommand( ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
"SELECT Name, GroupName FROM HumanResources.Department", conn); ;;;;;;;;
SqlDataReader r = cmd.ExecuteReader(); ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
while(r.Read()) { ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
// consume the data from the reader and perform some processing ;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
};;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;l

6. Now we are ready to deploy the assembly. Build the project from the Build menu and select Deploy solution. Visual Studio connects to the SQL server, drops previous versions of the assembly and sends the new assembly to the server for registration. The stored procedure is then registered and added to the assembly.

Managed Stored procedures can use SDK with ADO.NET also. The code can be compiled to produce a DLL assembly containing the stored procedure and the same can be registered with the server. This code can be run from SQL Server Management studio or from sqlcmd command line utility. The default permission is SAFE but does not allow external access and hence it has to be specifically set. The assembly can be refreshed at a later date if the public interface has not been changed.

The context connection

If at any point of time the developer wants to access the same server as the one in which the CLR stored procedure is executing, he can select one of the two options available. First he can create a regular connection and specify a connection string that points to the local server and specify credentials for logging in as it will be a separate connection. The disadvantage is that temporary tables are opaque to it. This is called a context connection. It lets the developer execute SQL statements in the same context as that of the context invoked by the code. The Syntax would be as under:

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
C# ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using(SqlConnection c = new SqlConnection("context connection=true")) {
c.Open(); ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
// do something with the connection ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Visual Basic .NET ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Using c as new SqlConnection("context connection=true") ;;;;;;;;;;;;;;;;
c.Open() ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
‘ do something with the connection ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
End Using ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

In order to see whether your code is actually running in the same connection as the caller, you can do the following experiment: use a SqlConnection object and compare the SPID (the SQL Server session identifier) as seen from the caller and from within the connection. The code for the procedure looks like this:

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
C# ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using System.Data; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using System.Data.SqlClient; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using Microsoft.SqlServer.Server; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
public partial class StoredProcedures { ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
[Microsoft.SqlServer.Server.SqlProcedure()] ;;;;;;;;;;;;;;;;;;;;;;;;;;;;
public static void SampleEX(string connstring, out int spid) { ;;;;;;;;;
using (SqlConnection conn = new SqlConnection(connstring)) { ;;;;;;;;;;;
conn.Open(); ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SqlCommand cmd = new SqlCommand("SELECT @@EXID", conn); ;;;;;;;;;;;;;;;;
spid = (int)cmd.ExecuteScalar(); ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

Restrictions for the context connection

A major restriction in context connection is that only one connection can be opened at a time. If multiple statements are running concurrently in separate connections each one can be assigned a separate connection. However, multiple active result sets(MARS) is not supported. SqlBulkCopy class becomes non operational and update batching is not supported. SqlNotificationRequest cannot be used and canceling commands will be ignored. No connection string keywords can be used when the context connection is set to true.

Though almost all the functionality of ADO.NET is available inside SQLCLR, asynchronous command execution and SqlDependency objects and related infrastructures are not supported by design.

ADO.NET in different SQLCLR objects

The context object executes within a given context or environment where the SQLCLR code was activated. It allows the code to access the appropriate runtime information on the basis of the object used. At the top level is the SqlContext class defined within the Microsoft.SqlServer.Server namespace. The Pipe object defines the connection to the client.

Stored procedures can be used to obtain data both on the local server and in remote data sources and send results to clients in manner similar to that of T-SQL stored procedures. However, for computation intensive work it is recommended that the developer should use T-SQL.

User defined scalar functions can be created in managed code in SQL Server 2005. The software assumes that no side effects are caused by the function such as change in the state of the database as there is very little enforcement of restriction in ADO.NET. Therefore, it is recommended that operations of this nature should be performed with adequate care and complete understanding of the implications. Moreover, these functions do not return result sets to the client as stored procedures.

Table valued functions(TVF) created with managed code in SQL Server 2005 return a relation instead of a scalar and hence can be used in the FROM part of a SELECT statement. These can be streamed when the server calls into the object to obtain rows to create the result in memory and send them back into the database.

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using System.Collections; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using System.Data; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using System.Data.SqlClient; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using System.Transactions; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
using Microsoft.SqlServer.Server; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
public partial class Functions { ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow")] ;;
// if you’re using VS then add the following property setter to ;;;;;;;;
// the attribute above: TableDefinition="s NVARCHAR(4000)" ;;;;;;;;;;;;;
public static IEnumerable ParseString(string str) { ;;;;;;;;;;;;;;;;;;;;
// Split() returns an array, which in turn ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
// implements IEnumerable, so we’re done 🙂 ;;;;;;;;;;;;;;;;;;;;;;;;;;;;
return str.Split(‘,’); ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
public static void FillRow(object row, out string str) { ;;;;;;;;;;;;;;;
// "crack" the row into its parts. this case is trivial ;;;;;;;;;;;;;;;;
// because the row is only made of a single string ;;;;;;;;;;;;;;;;;;;;;
str = (string)row; ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
} ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

Avoiding SQL CLR +ADO.NET

When a stored procedure executes a query it is advantageous to use T-SQL. SQLCLR will consume more development time and will be slow at runtime. It is also better to avoid procedural row processing if set oriented operations can be harnessed to the task.

System.Transactions, ADO.NET and SQLCLR

The new namespace that is part of .NET Framework 2.0 is System.Transaction. This namespace is intended to extend and simplify the use of local and distributed transactions in managed applications.

When a connection is opened with a remote server afresh, using ADO.NET connection a database transaction is automatically detected in the context and the same is promoted into a distributed transaction. The connection is then enlisted into the distributed transaction and everything is coordinated!

The surfacing of the transaction in the connection is sensed through the System.Transactions.Transaction class in the SQLCLR API. ADO.NET checks Transaction.Current status during Open() and the connection is enlisted transparently. The transaction can also be enlisted directly if an external transaction has to be aborted from within a stored procedure by calling the Transaction.Current.Rollback().

System.Transactions can be used by putting a transaction scope around the code that needs to be transacted. The TransactionScope will happen within an active transaction or it will start a new active transaction. Since. A number of overloads are available to customize the behavior. it is placed in the Using block, the compiler will call Dispose at the end. The Complete method on the scope is used to indicate that the task has been completed successfully. However, the Commit will be called before the complete to commit the transaction, else it will rollback the transaction.

Transactions can be nested and stored procedures can be called from within a transaction.
Transactions started in T-SQL differ from those started in SQLCLR. SQLCLR cannot unbalance a transaction state on entry or exit and this implies that the transaction cannot be committed or rolled back and an error will be generated at the time of exit. An outer transaction also cannot be rolled back. Transactions not started in a procedure cannot be rolled back and will cause a runtime error.

SQLCLR is good technology and helps the developer in a number of ways. Using ADO.Net inside SQLCLR is dynamite. It makes it possible to combine heavy processing with data access to both local and remote servers while maintaining transaction accuracy. A judicious combination of T-SQL and SQLCLR will make for applications that are flexible and have optimized performance!