The following tips are from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.
The integration of the .NET Framework's Common Language Runtime (CLR) with SQL Server 2005 is arguably the most significant new development
featured in the SQL Server 2005 release. The integration of the CLR brings with it a whole host of new capabilities, including the capability to create database objects using any of the .NET-compatible languages, including C#, Visual Basic, and managed C++. In this chapter you'll learn about how Microsoft has implemented the new .NET CLR integration with SQL Server as well as see how to create CLR database objects.
Understanding CLR and SQL Server 2005 Database Engine
The integration of the CLR with SQL Server extends the capability of SQL Server in several important ways. While T-SQL, the existing data access and manipulation language, is well suited for set-oriented data access operations, it also has limitations. Designed more than a decade ago, T-SQL is a procedural language, not an object-oriented language. The integration of the CLR with SQL Server 2005 brings with it the ability to create database objects using modern object-oriented languages like VB.NET and C#. While these languages do not have the same strong set-oriented nature as T-SQL, they do support complex logic, have better computation capabilities, provide access to external resources, facilitate code reuse, and have a first-class development environment that provides much more power than the old Query Analyzer.
The integration of the .NET CLR with SQL Server 2005 enables the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages. The integration of the .NET CLR with SQL Server 2005 is more than just skin deep. In fact, the SQL Server 2005 database engine hosts the CLR in-process. Using a set of APIs, the SQL Server engine performs all of the memory management for hosted CLR programs.
The managed code accesses the database using ADO.NET in conjunction with the new SQL Server .NET Data Provider. A new SQL Server object called an assembly is the unit of deployment for .NET objects with the database. To create CLR database objects, you must first create a DLL using Visual Studio 2005. Then you import that DLL into SQL Server as an assembly. Finally, you link that assembly to a database object such as a stored procedure or a trigger. In the next section you'll get a more detailed look at how you actually use the new CLR features found in SQL Server 2005.
| BOOK INFORMATION: |
|
|
Build powerful, enterprise-wide database programs using Microsoft SQL Server 2005 and the comprehensive information contained in this definitive volume. "Microsoft SQL Server 2005 Developer's Guide" explains how to develop server-side and client-side database applications and analyze business intelligence data. All of the brand-new features are covered in-depth -- including .NET CLR Integration, Reporting Services, Integration Services, Notification Services, and Service Broker. Real-world examples and practical tips detail how to use SQL Server 2005 as a cross-tier database platform with Visual Studio 2005, create custom management scripts with SQLCMD, and improve performance with SQL Profiler.Click here for complete book and purchasing information.
|
Developing CLR database objects
Home: Introduction
Tip 1: CLR architecture
Tip 2: CLR assemblies
Tip 3: Creating CLR database objects
Tip 4: CLR stored procedures
Tip 5: User-Defined Functions
Tip 6: CLR triggers
Tip 7: User-Defined Types
Tip 8: CLR aggregates
Tip 9: Debugging CLR database objects
Tip 10: .NET database object security
To download a pdf of the entire book chapter Developing CLR database objects click here.
