Synchronize SQL Server and Sharepoint list data through CLR (1)

Preface

This seriesArticleThere are four parts:

1.CLR overview.

2. Perform CLR integrated programming in Visual Studio and deploy it to SQL Server, including stored procedures, triggers, custom functions, custom types, and aggregation.

3. debugging of CLR integrated programming and problems encountered.

4. Use CLR to synchronize SQL Server tables and SharePoint lists (from actual project applications ).

This series of articles is based on the following software environments:

• Windows Server 2003 Enterprise Edition Service Pack 2
• Microsoft Visual Studio team 2008
• Microsoft SQL Server 2008
• Microsoft Office Sharepoint Server 2007

 

Preface

CLR (Common Language Runtime) is a concept created after Microsoft came out of. net. It is an important component of the. NET architecture, for all. NET FrameworkCodeProvides an execution environment. The code running in CLR is called managed code. CLR provides executionProgramVarious functions and services required, including real-time (JIT) compilation, Memory Allocation and Management, forced type security, exception handling, thread management, and security. I believe that the reader has read about it in any book introducing. NET and understands the working principles of CLR. This article will not only introduce the CLR In the. NET architecture, but also the Integrated Programming of CLR. In fact, Microsoft has made a lot of preparations for the integration programming of the Common Language Runtime (CLR), so that users can use it now. in any language of. net, you can install your own functions on any Microsoft Product (which may be exaggerated), such as SQL Server and office products, this article introduces the application of CLR on SQL Server. I will introduce the applications of CLR on other products in other articles (such as vsto ).

Microsoft has integrated the Common Language Runtime (CLR) component from SQL Server 2005, however, the application at that time may not be very extensive (maybe I haven't heard of it yet), and there are not many users. However, this means that the user can use it. any language of. Net (such as VB. net and C #. to compile database objects such as stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregation, and streaming table functions. This news sounds really exciting, which gives the DBAs and Dev (database developers) who have been very conservative for many years to have more options, at the same time, many simple SDE (software developers) can try database development, so that the program and the background database can be more closely integrated, and the collaboration between developers is smoother.

 

Introduction to Common Language Runtime (CLR) Integration

Microsoft SQL Server has the ability to integrate with the. NET Framework Common Language Runtime (CLR) component. CLR provides services for hosted code, such as cross-language integration, code access security, object lifetime management, and debugging and analysis support. It has the following features:

• Better Programming Model.. The. NET Framework language is richer than Transact-SQL in many aspects. It provides SQL server developers with structures and functions that are not previously available. Developers can also use the function of the. NET Framework library, which provides a large number of classes that can be used to quickly and effectively solve programming problems.
• Improved security and security. The managed code runs in the runtime environment of the public language hosted by the database engine. SQL Server uses this feature to provide a safer and more reliable alternative to the extended stored procedures provided in earlier versions of SQL Server.
• Ability to define data types and Aggregate functions. User-Defined types and user-defined aggregation are two new managed database objects. These two objects extend the storage and query functions of SQL Server.
• Simplified development through standardized Environment. Database development is integrated into a future version of Microsoft Visual Studio. NET development environment. The tools used by developers to develop and debug database objects and scripts are the same as those used to compile. NET Framework components and services at the intermediate or client layer.
• Potential for improving performance and scalability. In most cases, the. NET Framework language compilation and execution model improves performance through Transact-SQL.

Managed code uses code access security (CAS) to prevent the Assembly from performing certain operations. SQL Server uses CAs to help protect hosted code and prevent attacks against operating systems or database servers.

Advantages of CLR Integration

Transact-SQL is designed to directly access and manipulate data in the database. Although Transact-SQL performs well in data access and management, it is not completeProgramming Language. For example, transact-SQL does not support arrays, sets, for-each loops, shifts, or classes. Although some of these structures can be simulated in transact-SQL, managed code has integrated support for these structures. Depending on the actual situation, these features are sufficient to provide sufficient justification for implementing certain database features in managed code.

Select whether to use Transact-SQL or managed code

If the code is mainly executed with no or only few data accesses to the process logic, use Transact-SQL. If you want to write functions and processes with complex logic and high CPU usage, or want to use. NET Framework's BCl, use managed code.

Select whether to run on the server or on the client

Another factor affecting the use of transact-SQL or code hosting is whether you want to resident the code on the server computer or on the client computer. Both Transact-SQL and managed code can run on the server. In this way, code and data can be put together and you can take advantage of the powerful processing capabilities of the server. On the other hand, you may want to avoid placing tasks that occupy a large number of processors on the database server. Currently, most client computers have very powerful functions, so you may want to use this processing capability by placing as much code as possible on the client. The managed code can run on the client computer, but the transact-SQL cannot.

Choose whether to extend the stored procedure or host the code

You can generate extended stored procedures to execute functions that cannot be implemented using the transact-SQL stored procedure. However, extended stored procedures may compromise the integrity of SQL Server processes, but managed code that is verified to be type-safe will not. Further, the CLR-hosted code and SQL Server are deeply integrated with memory management, thread and fiber scheduling, and synchronization services. If the written stored procedure needs to execute tasks that cannot be completed in the transact-SQL statement, CLR integration provides a safer way to implement the stored procedure than the extended stored procedure.

 

Common Language Runtime (CLR) integration performance

Currently, developers are free to choose whether to develop SQL Server databases using Transact-SQL or CLR. However, the two have their own advantages and disadvantages in terms of performance. They are applicable to different types of operations and database objects, the following table shows the differences between the two.

type	transact_ SQL	CLR
User-Defined Functions	it is more effective for data access.	applicable to Process Code, computation, and string operations, as well as those that require massive computing and no data access.
User-Defined aggregation	the performance of non-cursor-based Built-in Aggregate functions is higher than that of CLR.	performance is higher than cursor-based aggregation, but execution is slow.
streaming Table value function (tvf function)	-	the performance of managed tvf is better than that of comparable extended stored procedures, it returns the managed function of the ienumerable interface.
array and cursor	the cursor performance is lower than the array in CLR	when a Transact-SQL cursor must traverse data that is more easily represented as an array, using managed code can significantly improve performance.
string data	Char or varchar data type.	the managed function can be of the sqlstring or sqlchars type.
Extended Stored Procedure	You cannot view or control the resource usage of the extended stored procedure.	You can use managed code to detect a given thread.

 

Common Language Runtime (CLR) Integration Security

The security mode of SQL Server integrated with the. NET Framework Common Language Runtime (CLR) is used to manage and protect access between different types of CLR objects and non-CLR objects running in SQL Server. These objects may be called by Transact-SQL statements or other CLR objects running on the server. Objects are called links. The security check type for these objects depends on the link type. CLR integration security mode can achieve the following goals:

• By default, running hosted user code on SQL server does not compromise the integrity and stability of SQL Server. If you perform operations that may damage the reliability of SQL Server, you should be protected by appropriate advanced permissions.
• The managed user code should not obtain unauthorized access to user data in the database or other user code. User-Defined code should run in the security context of the user session that calls the code, and have the correct privilege of the security context.
• There should be control to restrict user code from accessing any resources other than the server, but only for local data access and computing.
• User-Defined code should not be able to obtain unauthorized access to system resources by running in the SQL server process.

SQL Server has integrated the user-based security mode of SQL Server and the code-based access security mode of CLR. The code access security permission set granted to the Assembly at the SQL server host policy level is determined by the permission set specified when the Assembly is created. Three permission sets are available: Safe, external_access, and unsafe.

safe	the most restrictive permission set, which only allows access to internal computation and local data, you cannot access external system resources, such as files, networks, environment variables, or registries. You can only use the context connection string to specify the database connection, that is, context connection = true or context connection = yes.
External_access	It has the same permissions as safe, but allows access to external system resources.
Unsafe	Allow the Assembly to access the internal and external resources of SQL Server without restriction. In this case, the Assembly is granted fulltrust.

Generally, we recommend that you use a safe assembly when you do not use SQL Server external system resources. If you need to access external system resources in the Assembly, we recommend that you use external_access instead of unsafe, the latter will allow the code in the Assembly to perform illegal operations on the SQL server process space, which may damage the robustness and reliability of SQL Server. The external_access Assembly runs with the current SQL Server service account by default. It can display the security context of the simulated caller's windows identity authentication, this is why I set the Assembly to external_access when using SQL CLR to connect to the Sharepoint list later!

 

Conclusion

In short, the Common Language Runtime (CLR) connects SQL Server and external resources (such as Sharepoint list, network resources, and file systems) using the C # custom assembly ), and SQL server data operations provide a good foundation and better convenience. In the next article, I will introduce how to enable CLR in SQL Server 2008 and write CLR to make it run successfully in SQL Server, which may encounter many minor problems, then I will give a solution one by one.

1

2

3

4