Before looking at an example, it is important to understand the CLR integration features of SQL Server 2005. One of the excellent features of SQL Server 2005 is the integration with the .NET CLR (Common Language Runtime). The integration of CLR with SQL Server 2005 extends the capability of SQL Server in several important ways. In previous versions of SQL Server, database programmers were limited to using T-SQL when writing code on the server side. With CLR integration, database developers can now perform tasks that were impossible or difficult to achieve with Transact-SQL alone. Both Visual Basic .NET and C# are modern programming languages offering full support for arrays, structured exception handling, and collections. Developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks using languages such as VB.NET and C#. Both VB.NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. |
Advantages of CLR Integration |
Managed code is better suited than Transact-SQL for number crunching and compli- cated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Base Class Library (BCL), database developers now have access to thousands of pre-built classes and routines which can be easily accessed from any stored procedure, trigger, or user-defined function. The BCL includes classes that provide functionality for improved string functioning, advanced math operations, file access, cryptography, and more. Although many of these classes are available for use from within SQL CLR code, those that are not appropriate for server-side use (for example, windowing classes) are not available. Another benefit of managed code is type safety. Before managed code is executed, the CLR verifies that the code is safe. This process is known as “verification.” During verification, the CLR performs several checks to ensure that the code is safe to run. For example, the code is checked to ensure that no memory is read that has not been written to. The CLR will also prevent buffer overflows. By default, both Visual Basic .NET and C# always produce safe code; however, C# programmers have the option of using the unsafe keyword to produce unsafe code that, for example, directly accesses memory. |
For the purposes of this example, consider the |
Employee table that has been used in the previous examples. |
CREATE TABLE Employee (id int primary key, xml_data xml( EmployeeSchema)) |
Listing 10-3 illustrates how the XML data type can be accessed from the in-proc provider. The context connection allows you to execute SQL statements in the same context that the CLR code was invoked. For out-of-proc access, a new connection to the database must be established. |
Listing 10-3: Accessing an XML Data Type Column Using In-Proc |
using System; using System.Data; |
using System.Data.SqlClient;
learn guitarphysics learnteliphonyxmlphysicsenjoylife