Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

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 [e-mail]. 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 ...