To validate the email address, you'll search for a pattern that looks like xxxx@xxxx.com. For stored procedures, you used SQLPipe to send data back to SQL Server. But to get a trigger context, you use the GetTriggerContext method that the code at callout C shows. Once you get this context, you have access to the INSERTED and DELETED tables in SQL Server's trigger-execution space. These pseudo tables are automatically populated as soon as a DML operation such as INSERT, UPDATE, or DELETE occurs on the base table. To access the pseudo tables, you need to create a SQLCommand object, as the code at callout D shows, and get the email address from the Email ID column in the INSERTED pseudo table.
Once you get the address from the EmailID column, you can use the Regex.IsMatch method that the code at callout E shows to validate all email addresses that have the string pattern you specified. No validation happens against any online mail server service; you don't need to use any connected system for validations, which is one of the advantages of using regular expressions.
Depending on the return value (a Boolean value of True or False), the trigger either raises an error, as the code at callout F shows, or lets the process insert the email address into the table. If the address is invalid, you'll get the error message that Figure 3 shows. (Web Listing 1, which you can download at InstantDoc 45673, shows the script to create this error message.) Notice that the error message includes the trigger name so that you can use the error message for corrective action. To add a bit more functionality to Listing 2's trigger code, if the email is valid, you can send an email message to notify the user that the address is accepted. The code in Web Listing 2 creates the acceptance message.
This email message code will run synchronously while the trigger code executes. With this extension to the trigger's functionality, you not only validate the email address but also add more value by using the CLR BCLs. More important, this back-end email address validation would have been next to impossible in previous SQL Server versions.
Using CLR UDAs
Another important SQL Server 2005 CLR innovation is the introduction of UDA functions. In addition to the standard SQL Server aggregate functions such as SUM, COUNT, MIN, and MAX, UDAs let you create your own aggregate functions for purposes such as consolidating values in a column, as well as other complex mathematical operations. For example, say you have a Department table that contains a product_key column and you need to find the product of all the values for a given department. This aggregate function isn't available in SQL Server 2000; you can write your own code to get the product, but most homegrown solutions are roundabout and inelegant, and can be achieved only by using temp-table explicit looping techniques or by using complex formulae with logarithmic expressions.
Listing 3 shows the Visual Basic .NET code to create a UDA that finds the product of the values in a column. Open Visual Studio 2005, and add User Defined Aggregate to the project. The template will list the framework methods for creating a UDA. The standard methods you need for this UDA are Init, Accumulate, Merge, and Terminate. These methods, which you see highlighted in Listing 3, are the invocation, the aggregate process, the merger (in case of parallelism), and the termination positions for the aggregate function. As with the other CLR objects we've created, the class needs to be prefixed with the SqlUserDefinedAggregate attribute, and in Visual Studio, the template automatically generates this attribute. You also need to mark the class as Serializable. A typical call to the new UDA would look like:
SELECT Dept_ID, dbo.clr_product(product_key)
FROM #temp_dept
GROUP BY Dept_ID
Figure 4 shows the output this query generates.
Let's look at another way you can unleash the power of UDAs. Say the Department table you're working with includes employee data, and you want a comma-separated list of all the employees for each department. If you had to write the T-SQL code for this request in SQL Server 2000, you'd have to create a temporary table and use all sorts of looping techniques to form the comma-separated values, as the script in Web Listing 3 shows. But with the CLR, you can use the concatenation UDA that Listing 4 shows and call it with the following simple statement:
SELECT Dept_ID, dbo.clr_concat(name)
FROM #temp_dept
GROUP BY Dept_ID
Figure 5 shows the output from this query. When you compare the code in Web Listing 3 and Listing 4, you might think that you had to write more code for the UDA. However, the UDA is a reusable code block that you can use across the database for multiple queries. Moreover, the UDA code is more elegant and has fewer string manipulations, which incur CPU overhead in SQL Server and don't perform as well as the .NET stringbuilder class. Using the stringbuilder class lets you utilize the existing .NET Framework's capability to optimize string manipulations.
Deploying CLR Objects
You can deploy CLR objects manually, or you can deploy objects automatically in Visual Studio 2005 by simply right-clicking the project and selecting Deploy from the resulting popup menu. The deployment operation in Visual Studio 2005 uses the custom attributes SqlProcedure, SqlFunction, SqlTrigger, and SqlUserDefinedAggregate to automatically create the routines in the database. To see a list of assemblies for a given database, go to Visual Studio's Object Explorer and select the database name, then select Programmability, Assemblies. For manual deployment, you can use DDL statements, as the code in Web Listing 4 shows. (For a brief explanation of the .NET security settings you use for assemblies, see the Web sidebar "Security Settings for CLR Assemblies in SQL Server" at InstantDoc ID 45948.) You can also optionally upload the assemblies' source code into SQL Server to use for debugging CLR objects.
You can also get information about CLR deployed assemblies from SQL Server's system tables. The sys.assemblies table provides information about the assembly; the sys.assembly_references table provides dependency references to other cataloged assemblies; and the sys.assembly_files table provides assembly CLR code (if you've uploaded the code).
A typical way to use system tables is to get information about the assembly attributes. For example, the statement that Web Listing 5 shows returns such assembly-related information as version information and the permission set under which this assembly is deployed. (For information about the security settings you can use for .NET assemblies, see the Web sidebar "Security for CLR Assemblies in SQL Server.") If you right-click the assembly and select View Dependencies in SQL Server Management Studio, you can view the list of CLR objects that you deployed with the code in Web Listing 4.
T-SQL Is Not Dead
Although CLR integration is generating a lot of excitement among SQL Server developers, the introduction of .NET languages into SQL Server doesn't mean you're going to have to replace all your T-SQL code. The CLR can boost productivity in some ways that are impossible in earlier SQL Server editions, so exploiting the advantages of the CLR is worth the challenge. But SQL Server 2005 also includes rich T-SQL enhancements that make T-SQL more robust, such as common table expressions (CTEs) that provide support for recursive queries; PIVOT and UnPivot, which provide the ability to transpose rows to columns and vice versa; ranking capabilities; and the addition of the XML data type and XQuery. These features take the implementation of set-based solutions to the next level.
To make a judicious choice about when to use T-SQL and when to use the CLR, remember that CLR extensions perform better when you need a high level of computation or text manipulation. For data-intensive tasks, T-SQL's set-based solutions perform better than CLR-based solutions because T-SQL works more closely with the data and doesn't require multiple transitions between the CLR and the SQL OS. In processes such as cryptography, text manipulation, I/O operations, and invoking Web services, the CLR offers rich APIs that open capabilities that aren't possible in SQL Server 2000 and earlier versions. Finally, test every piece of code for security, reliability, and performance. Any given task can have multiple implementations when you're using the .NET BCLs.
Conclusion
This article has only touched the surface of what is possible when you use the .NET 2.0 CLR objects in SQL Server 2005. Use the examples I've given you to imagine what you can do in your application's database tier, for example, now that you can access Web services from within the database logic. And explore the database objects that you can create to utilize the feature-rich .NET 2.0 Framework and programming languages.