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

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

 

Background

Let's talk about why data synchronization between SQL Server and Sharepoint list is required. This is an application of a real project.

The customer's SharePoint site has four lists, which form a hierarchical organization data. The data is ultimately derived from a table in the database used by another system. In the early stage of the project, developers manually export data from the database and then import the data to the list created on the SharePoint site. in the later stage of the project, the data in the source database has changed, therefore, developers need to manually check the data changes and synchronize them to the Sharepoint list. Because the amount of data is large, this work is very labor-intensive, So we plan to write a stored procedure in SQL Server and add it to the scheduled task of SQL Server, enable SQL Server to automatically synchronize the data in a cycle. Because access to SharePoint sites and lists and permission control are involved, we thought of using the CLR method in SQL Server to write and deploy stored procedures. The following are the structures and data sources of the four lists.

Area list

Two text fields: Title and area_code. title is the name of area, and area_code is the region code of area.

Region list

Title: name of the region type.

Area: The loopup type, pointing to the ID of the area.

Region_code: Specifies the region encoding type of the text type.

 

 Country List

Title: name of the country type.

Region: Lookup type, pointing to the ID of region.

Country_code: Specifies the country encoding type.

 

Companycode list

CompanyName: name of company in the text type.

Companycode: Company encoding.

Country: indicates the country ID of the lookup type.

 

Source table in Database

Combine hierarchies in a table. area_code and area_name correspond to area list, region_code and regionname correspond to region list, subregion_code and subregion_name correspond to country list, company_code and company_name correspond to companycode list.

 

 

Preparations

We need to perform Code testing in a test environment before deploying it to the production environment. Create a new blank SharePoint site on the test server, create four lists as described above, and then import the data to the test database from the client server. When creating a list, pay attention to the field name. Modifying the name will cause the internal name of the List field to be inconsistent with the external name.ProgramYou need to pay attention to it.

Open Visual Studio and create an SQL server project named sqlclrsharepointsynchronizer. Next we will start coding our synchronization program.

 

Start

The program connects to the database where the data source is located, obtains the data, and then connects to the SharePoint site for data synchronization, finally, call the method provided by WSS to write the data to the corresponding list. Considering that the program to be compiled must be deployed to the host database for execution, the first question to consider is how to execute the program. Because the functions completed by the program are relatively independent and independent from other objects in the database (such as tables and views), the ideal method is stored procedures and user-defined functions, user-defined functions are divided into two types: scale functions and tvf functions (as described in the previous article). The scale function is suitable for data processing, similar to a data processing tool function, it is obviously inappropriate to use it here, while the tvf function needs to specify an appropriate output table structure. Besides, our program only completes a data synchronization process, the output information may be as simple as a text such as success or failure, so no tvf function is required. In addition, the user-defined function must have a return value, which may not be what we need. Therefore, the stored procedure is the most appropriate choice. Therefore, we first create a class named synchronizer and a public static method synchierarchy to represent a database stored procedure.

The next step is to do the basic work well. A dbutil class is used to process database connections and obtain data. This is the ASP in most three-layer structures. net program development, there is nothing to say, but here I don't need it to complete more functions, just need to execute SQL statements and return a dataset filled with data, all other methods are deleted. The reader can see this simple class in the attachment, and I will not repeat it hereCode.

It is important to use the methods provided by WSS in the project. We reference the Web service provided by the SharePoint site in the project. Its address isHttp ://Sitename/_ Vti_bin/lists. asmxThe sitename is the address of the SharePoint site. Name the Web reference lists and modify the proxy class as follows:

Using System;
Using System. Collections. Generic;
Using System. text;
Using Microsoft. sqlserver. server;
Using System. Security. Principal;
Using System. net;

NamespaceSqlclrsharepointsynchronizer. Lists
{
Public Partial ClassLists
{
PublicWindowsimpersonationcontext impersonationcontext;

Public Lists ( String Siteurl, Bool Impersonatecaller, iwebproxy proxy)
: This ()
{
If (Siteurl. endswith ( @" / " ))
{
Siteurl = Siteurl +   " _ Vti_bin/lists. asmx " ;
}
Else
{
Siteurl = Siteurl +   " /_ Vti_bin/lists. asmx " ;
}

If(Impersonatecaller)
{
This. Impersonationcontext=Sqlcontext. windowsidentity. Impersonate ();
}

This . Url = Siteurl;
This . Credentials = Credentialcache. defaultcredentials;
// This. Credentials = new networkcredential ("uid", "PWD", "Domain ");

If (Proxy ! =   Null )
{
This . Proxy = Proxy;
}

}

Protected   Override   Void Dispose ( Bool Disposing)
{
If (Disposing && Impersonationcontext ! =   Null )
{
Impersonationcontext. Undo ();
}
Base . Dispose (disposing );
}
}
}

The main function is to improve the program's permission to access the Sharepoint list. This permission is simulated using the sqlcontext object through the current Logon account of the SQL Server Host Program. Remember that the Undo operation must be performed after the permission is elevated, this is the corresponding!

The core part of the code is completed in the splistutil class, mainly to call the web service and execute the update splist method. Two Web service methods are used: Lists. getlistitems () and lists. updatelistitems (). Note that splist contains lookup fields. Therefore, when updating data, we need to write the values of these fields as follows:1; # title1; #2; # title2In the program. In addition, when the data volume for one-time update is large, execute lists. the caml used in the updatelistitems () method will be automatically truncated. the solution is provided in the section of the truncated caml of the updatelistitems () method.

For details about the code, refer to the Attachment I provided later in this article. I read data from the data source table through four SQL statements and fill it in dataset. Then, I convert the data in sequence and update it to the corresponding splist. At the same time, I output lists. the returned information of the updatelistitems () method. This information is an XML text that contains the details of caml data update. It is formatted in the formatresult () method, format the returned information as expected. The following is the SQL statement used in the program to retrieve data.

String Select_area =   " Select distinct area_name, area_code from {0} DBO. seccompanyhierarchy where area_name <>'' " ;
String Select_region =   " Select distinct region_name, area_code, region_code from {0} DBO. seccompanyhierarchy where region_name <>'' " ;
String Select_country =   " Select distinct subregion_name, region_code, subregion_code from {0} DBO. seccompanyhierarchy where subregion_name <>'' " ;
String Select_companycode =   " Select company_code, company_name, subregion_code from {0} DBO. seccompanyhierarchy where company_code <> ''and company_name <>'' " ;

Because the host database may use the linkedservers method to connect to the client database server, this method of SQL statement is used. The rest should be easy to understand.Source codeOK.

Finally, let's talk about deployment. You can deploy the DLL on the host database server according to the deployment method described in the previous articles. in Visual Studio, right-click the project file, click deploy to automatically deploy. Generally, automatic deployment will not be a problem, but we will always encounter the problem of deploying compiled DLL to the official server, readers can refer to the example of the manually deployed SQL script I provided in the third article. After the deployment is successful, it is executed in the SQL Server Query analyzer. The following is the execution process.

 

The first parameter of the stored procedure is the destination address for data synchronization, and the second parameter is the name of the parameter servers. If the database is on the local host server, you do not need to specify the second parameter. The formatted execution result is also provided in the messages window.

Download Sample Code

 

Conclusion

Four articles have introduced the background, examples, debugging and deployment of SQL clr programming, as well as examples of practical applications. They hope to help those who are using or will use SQL CLR in the future, it also makes a record for future SQL CLR programming and development.

1

2

3

4