Microsoft SQL Server now features the integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.
major benefits of this integration are:
· A better programming model. The .NET Framework languages are in many respects richer than Transact-SQL, offering constructs and capabilities previously not available to SQL Server developers. Developers may also leverage the power of the .NET Framework Library, which provides an extensive set of classes that can be used to quickly and efficiently solve programming problems.
· Improved safety and security. Managed code runs in a common language run-time environment, hosted by the Database Engine. SQL Server leverages this to provide a safer and more secure alternative to the extended stored procedures available in earlier versions of SQL Server.
· Ability to define data types and aggregate functions. User defined types and user defined aggregates are two new managed database objects which expand the storage and querying capabilities of SQL Server.
· Streamlined development through a standardized environment. Database development is integrated into future releases of the Microsoft Visual Studio .NET development environment. Developers use the same tools for developing and debugging database objects and scripts as they use to write middle-tier or client-tier .NET Framework components and services.
· Potential for improved performance and scalability. In many situations, the .NET Framework language compilation and execution models deliver improved performance over Transact-SQL.
You can build database objects using the SQL Server integration with the .NET Framework common language runtime (CLR). Managed code that runs inside of Microsoft SQL Server is referred to as a “CLR routine.” These routines include:
· Scalar-valued user-defined functions (scalar UDFs)
· Table-valued user-defined functions (TVFs)
· User-defined procedures (UDPs)
· User-defined triggers
CLR routines have the same structure in managed code. They are mapped to public, static (shared in Microsoft Visual Basic .NET) methods of a class. In addition to routines, user-defined types (UDTs) and user-defined aggregate functions can also be defined using the .NET Framework. UDTs and user-defined aggregates are mapped to entire .NET Framework classes.
(Example from Pinal Dave Blog)
Let us create one very simple CLR where we will print current system datetime.
Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project
Select the database and connect. On the solution explorer pane, right click and add new item
Then write the code. Compile and deploy. This object will be available in the data base as regular objects.
Data Access from CLR objects :A common language runtime (CLR) routine may easily access data stored in the instance of Microsoft SQL Server in which it runs, as well as data stored in remote instances. Which particular data the routine can access is determined by the user context in which the code is running. Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server, also referred to as SqlClient. This is the same provider used by developers accessing SQL Server data from managed client and middle-tier applications. Because of this, you can leverage your knowledge of ADO.NET and SqlClient in client and middle-tier applications.
Security :A common language runtime (CLR) routine may easily access data stored in the instance of Microsoft SQL Server in which it runs, as well as data stored in remote instances. Which particular data the routine can access is determined by the user context in which the code is running. Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server, also referred to as SqlClient. This is the same provider used by developers accessing SQL Server data from managed client and middle-tier applications. Because of this, you can leverage your knowledge of ADO.NET and SqlClient in client and middle-tier applications.
Debugging CLR objects : SQL Server provides support for debugging Transact-SQL and common language runtime (CLR) objects in the database. The key aspects of debugging in SQL Server are the ease of setup and use, and the integration of the SQL Server debugger with the Microsoft Visual Studio debugger. Furthermore, debugging works across languages. Users can step seamlessly into CLR objects from Transact-SQL, and vice versa. The SQL Server Transact-SQL debugger in SQL Server Management Studio cannot be used to debug managed database objects, but you can debug the objects by using the debuggers in Visual Studio. Managed database object debugging in Visual Studio supports all common debugging features, such as “step into” and “step over” statements within routines executing on the server. Debuggers can set breakpoints, inspect the call stack, inspect variables, and modify variable values while debugging. Note that Visual Studio .NET 2003 cannot be used for CLR integration programming or debugging. SQL Server includes the .NET Framework pre-installed, and Visual Studio .NET 2003 cannot use the .NET Framework 2.0 assemblies.