Microsoft SQL Server 2005 Developer’s Guide- P5

Microsoft SQL Server 2005 Developer’s Guide- P5:This book is the successor to the SQL Server 2000 Developer’s Guide, whichwas extremely successful thanks to all of the supportive SQL Server developerswho bought that edition of the book. Our first thanks go to all of the peoplewho encouraged us to write another book about Microsoft’s incredible new relationaldatabase server: SQL Server 2005.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2005 Developer’s Guide- P5 Chapter 3: Developing CLR Database Objects 79CLR ArchitectureThe .NET Framework CLR is very tightly integrated with the SQL Server 2005database engine. In fact, the SQL Server database engine hosts the CLR. This tightlevel of integration gives SQL Server 2005 several distinct advantages over the .NETintegration that’s provided by DB2 and Oracle. You can see an overview of the SQLServer 2005 database engine and CLR integration in Figure 3-1. As you can see in Figure 3-1, the CLR is hosted within the SQL Server databaseengine. A SQL Server database uses a special API or hosting layer to communicatewith the CLR and interface the CLR with the Windows operating system. Hosting the CLR within the SQL Server database gives the SQL Server databaseengine the ability to control several important aspects of the CLR, including Memory management Threading Garbage collection The DB2 and Oracle implementation both use the CLR as an external process,which means that the CLR and the database engine both compete for systemresources. SQL Server 2005’s in-process hosting of the CLR provides severalimportant advantages over the external implementation used by Oracle or DB2. First,in-process hosting enables SQL Server to control the execution of the CLR, putting SQL Server Engine CLR Hosting Layer SQL Server OS WindowsFigure 3-1 The SQL Server CLR database architecture80 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e essential functions such as memory management, garbage collection, and threading under the control of the SQL Server database engine. In an external implementation the CLR will manage these things independently. The database engine has a better view of the system requirements as a whole and can manage memory and threads better than the CLR can do on its own. In the end, hosting the CLR in-process will provide better performance and scalability. Enabling CLR Support By default, the CLR support in the SQL Server database engine is turned off. This ensures that update installations of SQL Server do not unintentionally introduce new functionality without the explicit involvement of the administrator. To enable SQL Server’s CLR support, you need to use the advanced options of SQL Server’s sp_configure system stored procedure, as shown in the following listing: sp_configure show advanced options, 1 GO RECONFIGURE GO sp_configure clr enabled, 1 GO RECONFIGURE GO CLR Database Object Components To create .NET database objects, you start by writing managed code in any one of the .NET languages, such as VB, C#, or Managed C++, and compile it into a .NET DLL (dynamic link library). The most common way to do this would be to use Visual Studio 2005 to create a new SQL Server project and then build that project, which creates the DLL. Alternatively, you create the .NET code using your editor of choice and then compiling the code into a .NET DLL using the .NET Framework SDK. ADO.NET is the middleware that connects the CLR DLL to the SQL Server database. Once the .NET DLL has been created, you need to register that DLL with SQL Server, creating a new SQL Server database object called an assembly. The assembly essentially encapsulates the .NET DLL. You then create a new database object such as a stored procedure or a trigger that points to the SQL Server assembly. You can see an overview of the process to create a CLR database object in Figure 3-2. Chapter 3: Developing CLR Database Objects 81 Code database object using managed code and complie to DLL Register DLL with SOL Server using T-SQL create assembly Create database object using T-SQL CreateFigure 3-2 Creating CLR database objectsSQL Server .NET Data ProviderIf you’re familiar with ADO.NET, you may wonder exactly how CLR databaseobjects connect to the database. After all, ADO.NET makes its database connectionusing client-based .NET data providers such as the .NET Framework Data Providerfor SQL Server, which connects using networked libraries. While that’s great fora client application, going through the system’s networking support for a databasecall isn’t the most efficient mode for code that’s running directly on the server. Toaddress this issue, Microsoft created the new SQL Server .NET Data Provider. TheSQL Server .NET Data Provider establishes an in-memory connection to the SQLServer database.AssembliesAfter the coding for the CLR object has been completed, you can use that code tocreate a SQL Server assembly. If you’re using Visual Studio 2005, then you cansimply select the Deploy option, which will take care of both creating the SQLServer assembly as well as creating the target database object. If you’re not using Visual Studio 2005 or you want to perform the deploymentprocess manually, then you need to copy the .NET DLL to a common storagelocation of your choice. Then, using SQL Server Management Studio, you canexecute a T-SQL CREATE ASSEMBLY statement that references the location of the.NET DLL, as you can see in the following listing:CREATE ASSEMBLY MyCLRDLLFROM \\SERVERNAME\CodeLibrary\MyCLRDLL.dll82 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e The CREATE A ...