With the integration of Common Language Runtime and SQL Server 2005 now its possible to manage stored procedures, trigges and user defined functions using .Net compliant language. Imagine you are writing these database objects in .Net language of your choice and running inside CLR which is already full of added features like Code Access Security, memory management and object life cycle management. Comparing previous versions of SQL Server where T-SQL was the only developers choice for managing up these objects and now with the advent of SQL Server 2005 its easy to manage these objects in your favourite .Net language.

Believe me with the advent of SQL Server 2005 how easy it is to perform all those tasks where were difficult to achieve in previous version of SQL Server's. Now dealing with large code is much more easier because you can manage up with .Net compliant languages with the object oriented concepts intact. Infact once you run your database object on top SQL CLR it will take care of memory and security.

Lets start by creating a simple Managed Stored Procedure

- Create a .Net class

using System.Data ;
using System.Data.Sql ;
using System.Data.SqlServer ;

/// <summary>
///  Your CLR code
/// </summary>

public class Yukonizer
{
     public static void FetchEmployees()
      {
           SqlPipe objPipe = SqlContext.GetPipe() ;

           objPipe.Send("Hello World") ;

           SqlCommand cmd = SqlContext.GetCommand() ;

           cmd.CommandText ="select * from employees" ;
          
           objPipe.Send(cmd.ExecuteReader()) ;

     }
}

 

- Compile to create an assembly

Now compile this code to produce an assembly using

csc.exe /t:library /r:"<path>\sqlaccess.dll" /out:Yukonizer.dll Yukonizer.cs

The System.Data.SqlServer namespace resides in sqlaccess.dll thats why we have specified the dll in the path.

Register the assembly

Once compiled register it with SQL Server. Registration is very easy issue the CREATE ASSEMBLY statement in SQL Server Workbench.

CREATE ASSEMBLY Yukonizer FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\Procedures\Yukonizer.dll'

The above statement register the assembly with SQL Server, every time you make a change you have to drop and register the assembly. Again droping is very easy simply use the DROP ASSEMBLY statement in SQL Workbench.

DROP ASSEMBLY Yukonizer

- Creating Stored Procedure to call the method written inside assembly

Its time to call the the method written inside assembly, simply create a stored procedure using CREATE PROCEDURE statement and associate this procedure to the method.

CREATE PROCEDURE [dbo].[GetEmployees]
AS
EXTERNAL NAME
[Yukonizer]:[Yukonizer]::[FetchEmployees]

The EXTERNAL clause lets you reference a method in a registered assembly which hooks the stored procedure to the external method. The EXTERNAL clause has a syntax like this :

EXTERNAL NAME [Assembly Name] : [Class Name] :: [Method Name]

Testing the assembly

Type the below statement under SQL Server Workbench

exec GetEmployees

Permissions for Assembly

In SQL Server 2005 you can specifiy three permission levels to run the code.

* SAFE - Default permission. This only manages with in-process manage provider
* EXTERNAL_ACCESS - This is required when the code inside requires access to external resources such as files, network. Here the impersonation works.
* USAFE - In an unrestricted environment like Direct System Access, especially using Win32.

To specify a security or permission level modify the above CREATE ASSEMBLY syntax under SQL Server Workbench

CREATE ASSEMBLY Yukonizer FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\Procedures\Yukonizer.dll' WITH PERMISSION_SET = SAFE | EXTERNAL_ACCESS | UNSAFE

This way you can develop and deploy your .Net Assemblies and run them inside SQL Server 2005 using CLR.

Hope this helps you out.

Cheers,

 

 

Dont' miss this exciting upcoming event on 30th April - 1st May 2006 at Mall of Emirates, Dubai.

Be there, we'll meet Inshallah. 

Cheers

~Danish Sami

This was the message propagated during the Window Vista and Office 2007 Readiness Tour. I was feeling iffy and felt flying on Windows Carpet

 

Ever wonder what exceptions were generated during program execution.

Windows provides a readymade class EventLog to log your exceptions as eventlog.

a sample code to how to use EventLog

// this contains the EventLog class
using System.Diagnostics ;

try
{
// some code
}
catch(Exception _ex)
{
EventLog.WriteEntry("source", _ex.ToString(), EvenLogEntryType.Error, 0, 0, null) ;
// your code
}

Summary:

The WriteEntry method writes an entry with the given message text, application-defined event identifier, and application-defined category to the event log (using the specified registered event source) and appends binary data to the message.

 

This week I was very busy in debugging applications :( cause the application was not thoroughly tested.

Several facsinating Testing documents were written to convince the Development Managers and filled with level of details but of no use cause when it finally landed on clients desktop it bails out. Honestly speaking I was not convinced with the kind of testing we were doing. For the whole day I was listening up remarks from Lead Manager, while taking coffee in our food court I started discussing about Test driven development, he showed his interest then I added more and started discussing about Unit testing tools, among them I draw a picture of NUnit, I showed him the NUnit reviews and comments based on his request. 

Next day we downloaded the NUnit tool and played with the samples, he was quite happy as the NUnit tool was .

In the next upcoming post I will draw some nice picture of what is NUnit and how a developer could barely take advantage by using NUnit to save his time and effort debugging applications and at the same time guarenteed to the End User that what is developed IS AS per specification.

Thanks.

~Danish Sami