Getting dig into SQL CLR

SQL CLR is a new SQL Server feature that allows you to embed logic written in C#, VB.Net, and other managed code into the body of T-SQL objects like stored procedures, functions, triggers, aggregates and types. Client applications interact with these resulting routines like they are written in native T-SQL. Internally, things like string manipulations and complex calculations become easier to program because you are no longer restricted to using T-SQL and now have access to structured .Net languages and the reuse of base class libraries. Externally, the logic you create is wrapped in T-SQL prototypes so that the client application is not aware of the implementation details. This is advantageous because you can employ SQL CLR where you need it without rearchitecting your existing client code.

With SQL CLR, you are also freed from the constraint of logic that applies only within the context of the database. You can with appropriate permissions write logic to read and write to file systems, use logic contained in external COM or .Net DLLs. These capabilities are exciting and concerning, especially in the historical context of new feature overuse. To help you use this new feature appropriately, we want to make sure that you understand how it integrates with SQL Server and where this feature may be heading. In this chapter, we'll give you this type of overview. We'll spend the rest of the book explaining these concepts using real-world SQL CLR examples that you can use today.

Let's go through step by step for your first introduction on sql clr.

The first step in creating a stored procedure (or any database object) is to create a SQL Server project in Visual Studio:

• From the File menu, create a new project
• In the New Project Dialog, select and expand a language node in the Project Types area
• Select the Database node
• Select the SQL Server Project template
• Click OK and you are done

Next you will be promted of of creating sql connection to connect to sql server. But wait, before that your datebase and server need to be configured to run/host SQL CLR.

If you dont know how to do that just run the following code in your query window

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

Creating your first user defined CLR function

• Open an existing SQL Server Project, or create a new one. From the Project menu, select Add New Item.
• Select User Defined Function
• Type a Name for the new user defined function.
• Add code to run when the function is executed.
• In the following code snippet, i have written a custom user defined SQL CLR function which convert the european anccented characters into regular english character.

[Code]public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.None, Name = "fnReplaceAccentedCharacters", IsDeterministic = true)] public static SqlString fnReplaceAccentedCharacters(SqlString inputString)

{

SqlString xReturnValue="";

Int16 a;

String[,] xCharGroup ={{"à", "a"}, {"â", "a"}, {"ä", "a"}, {"é", "e"}, {"è", "e"}, {"ê", "e"}, {"ë", "e"}, {"î", "i"}, {"ï", "i"}, {"ô", "o"}, {"ö", "o"}, {"û", "u"}, {"ü", "u"}, {"ù", "u"}, {"ç", "c"}, {"œ", "oe"}, {"Œ", "oe"}, {" ", "-"}};

for (a = 0; a < xCharGroup.GetLongLength(0); a++)

inputString = ((String)inputString).Replace(xCharGroup[a, 0], xCharGroup[a, 1]);

return (xReturnValue=inputString);

}

};[/code]

Once the code is ready, you can build and deploy the code to the database by pressing 'F5′.

To test the deployment, there is a file in your project called test.sql. In this file, you can add script calls to the obects you just created. For example, to call the function created above, enter: Select dbo.fnReplaceAccentedCharacters('des objets de très grande qualité, originaux, épurés,')

That's it. If the stored procedure was written correctly, you will now see this item in list of functions in your management stuidio. However, you'll see the object has a lock icon next to it, as shown below. This is because the object is not editable outside of the Visual Studio project.