IV.7.3. Integrated Application Development with the .NET Framework

Although you can do a lot using Transact-SQL, T-SQL isn't a full-featured development language. You might want your database application to do more complex operations than what T-SQL can do alone.

Therefore, you can create an assembly with a .NET language, such as Visual Basic or C#, and then integrate the assembly into a database object. This is referred to as using managed code.

A CLR integrated object is one that is using an external assembly. An assembly is a compiled Dynamic Link Library (DLL). The following database objects can be CLR integrated:

  • Stored procedures

  • Triggers

  • User-defined functions

  • User-defined types

  • User-defined aggregates

CLR objects excel in the following situations:

  • String comparisons and manipulation: For example, if you need to check to see whether a valid e-mail address is entered, you wouldn't be able to easily by using only T-SQL. An e-mail address may look like name@place.com. You need to check for one or more text characters, the @ symbol, one or more characters ending with a period, and a valid top-level domain, such as com or net. However, by using a high-level language, such as Visual Basic or C#, this could be reduced to a relatively simple string comparison.

  • Complex calculations: Although T-SQL includes access to many built-in functions, managed code provides access to the full .NET Framework Library. The capabilities within the .Net Framework Library combined with the ease of use ...

Get Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies® now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.