How to use MSSQLSERVER access C # CLR assemblies

Preface

MSSQLSERVER provides an assembly, which can undoubtedly make it easier for programmers to manipulate database data, such as C # written functions, which can be used in a database as a function of SQL, and you think about how well he is for programmers who are unfamiliar with the database. Such a good mechanism, we certainly can not be missed, but for the database change migration, there may be obstacles and inconvenience. It is recommended that you make a tradeoff when using the Sqlsesrver CLR assembly to avoid any inconvenience to future expansion of the database. We recommend that you use SQL Server CLR assemblies for single-time data, data filtering, and so on. Long-term dependence, or alternative solutions.

MSSQLServer access to the C#CLR assembly, making C # functions variable SQL functions

Simulation Scenario:

1, database table data as follows

2. Problem Description: The JSON data format of the Areapoints field is not a problem in third-party JSON data parsing in C #. But he is called by the multiport data, the calling program may be Java or other languages, the non-canonical JSON data format can not be parsed, so there will be a data error.

3, Solution: Here I will not talk about it, the scheme must be a lot of, today I cut into the main body, using C # class method, so that the non-standard is sent data format into a uniform specification of the JSON data format.

4, show the specific operation as follows.

MSSQLSERVER Access C#CLR assembly, the specific steps, follow-up

1. Create C # functions. Add---> New project under Solutions

2. Add-new Item---SQL Server---SQL CLR C #------SQL CLR C # user-defined functions

3. Create C # function method, JSON format of irregular JSON to specification

usingSystem;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Data.SqlTypes;usingMicrosoft.SqlServer.Server; Public Partial classuserdefinedfunctions{[Microsoft.SqlServer.Server.SqlFunction]//Static Methods     Public StaticSqlString ToJson1 (SqlString str) {Try        {            stringJSON =Str.            ToString (); //double quotes unchanged            if(JSON. Contains ("\""))            {                returnJSON; }            //Double quotation marks with single quotation marks            Else if(JSON. Contains ("'"))            {                returnJson. Replace ("'","\""); }            //double quotation marks with no quotation marks            Else if(JSON. Contains ("LNG:") ) {JSON= json. Replace ("lat","\ "Lat\""). Replace (":",":\""). Replace (", LNG","\ ", \" lng\ ""). Replace ("}","\"}"); returnJSON; }            Else            {                returnJSON; }        }        Catch        {            returnstr; }    }}

SQL Server adds 2 ways to add the C # CLR, 1, the program is published.

1. Review the properties of the C # CLR program that you have built, check the project settings, determine the target platform and the version of the data you want to build to the same time, not one side is sqlserver2008 again is sqlserver2012, this is the publication is unsuccessful. There is a choice of SQLCLR, the target frame in 4.0, I choose 3.5.

2. Turn on database server configuration option CLR enabled

---Turn on all server configuration optionsEXECsp_configure N'Show advanced Options'N'1' RECONFIGURE  withOVERRIDE--turn on the CLR enabled optionEXECsp_configure N'CLR enabled'N'1'RECONFIGURE  withOVERRIDE--Turn off all server configuration optionsEXECsp_configure N'Show advanced Options'N'0' RECONFIGURE  withOVERRIDE

--if there is a permission problem, execute the following script

ALTER DATABASE [master] set trustworthy on

EXEC sp_changedbowner ' sa '

3. Publish the SQL C # CLR assembly, select the server you want to publish, and which database you want to publish to. Are you sure.

4. Configuration Release Succeeded

SQL Server is added in 2 ways in the CLR, 2, manually adding assemblies to the database.

1. Find yourself writing DLL files under the Bin folder generated under SQL C # CLR Assembly

2. Execute the above script as well, turn on the database server configuration option, CLR enabled, and a script to pay SA permission.

3. Open the programmability under the database, assembly, new Assembly

4. Execute the script below to register the function

Create function dbo. ToJson1 (@patten[nvarchar](4000)RETURNS  nvarchar(4000)as---sql C # CLR assembly file name. Class name. Method Name EXTERNAL NAME BgSqlFun.UserDefinedFunctions.ToJson1

use of SQL C # CLR custom functions

The above 2 ways to add any one of the lines.

Declare @points varchar (4000); Select @points =  from where id=672Select dbo. ToJson1 (@points)

Just do it. A simple example, hoping to give some players, to provide help. Thanks

How to use MSSQLSERVER access C # CLR assemblies