SQL Server integration with the CLR

. NET programming and SQL Server--sql server and CLR integration One, why SQL Server integrates with the CLR

1, SQL Server provides a very limited stored procedures, functions, etc., often require external code to perform some heavy porting;

2. Integration with the CLR can migrate functions that would otherwise require separate programs to SQL Server for data operations;

3. T-SQL data Query language is good for returning datasets, but otherwise poorly performing. Integration with the CLR can solve this problem;

4,. NET operation code and execution is much faster than T-SQL. NET program is compiled binary code, not as a stored procedure to build, no longer compile directly to run.

II. assemblies in SQL Server (compile, add, modify, delete)

CLR stored procedures, CLR functions, and so on can be built on the Assembly only after the Assembly has been added.

1. CLR code (Compile) →dll file (registration) →sql Server (as database object) → Perform database operations as follows:

(1) Write a managed program as a set of class definitions. Compile the code after compiling into a DLL file;

Stored procedures, user-defined functions, triggers are written as static methods of the class;

A user-defined type, an aggregate function, is written as a struct.

(2) DLL files are uploaded on the SQL Server disk, and the DLL assembly is stored to the system directory using CREATE assembly;

(3) Create SQL objects (functions, stored procedures, triggers, etc.) and bind them to the entry point of the Assembly;

Stored procedure: CREATE PROCEDURE

User-defined functions: Create function

Trigger: Create Trigger

User Custom type: Create Type

Aggregation function: Create aggregate

(4) Use as you would with a T-SQL routine.

2. Assemblies in SQL Server (create an assembly and upload to an instance of SQL Server and then create a database object)

(1) SQL Server 2008 is disabled by default for CLR integration and must be enabled before SQL Server access. NET object.

Enable CLR integration

exec sp_configure ' show advanced options ', ' 1 ';
Go
Reconfigure
Go
exec sp_configure ' clr enabled ', ' 1 ';//turn on CLR integration
Go
Reconfigure
Go

Explain

(2) Add the DLL assembly to SQL Server. Add an assembly in SQL Server using the CREATE ASSEMBLY command.

Create Assembly assembly_name (assembly name)
[Authorization owner_name]
from {<client_assembly_specifier>|<assembly_bits>}
[With Permission_set={safe|external_access|unsafe}]

where <client_assembly_specifier>: Represents the local location or network location where the assembly resides, and the manifest file name that corresponds to the assembly.

<assembly_bits>: Represents a list of binary values that comprise assemblies and dependent assemblies.

Permission_set={safe|external_access|unsafe: Represents a set of access rights granted by the Assembly when the SQL Server access assembly is specified, and the default value is safe.

(3) Modifying assemblies

ALTER ASSEMBLY ASSEMBLY_NAME

[From <client_assembly_specifier>|<assembly_bits>]
[With <assembly_option>[,.... N]]
[Drop file{file_name[,.... N]|all}]
[Add file from Client_file_specifier [as file_name]|file_bits as file_name} [,.... N]] [;]

Among them, <assembly_option>::=permission_set=[{safe|external_access|unsafe} | Visibility={on|off} | Unchecked data], where Visibility={on|off}: Indicates whether the assembly is visible when creating CLR functions, stored procedures, triggers, user-defined types, and user-defined aggregate functions. If set to OFF, the assembly can be called only by other assemblies. Unchecked data: By default, if ALTER assembly must verify the consistency of individual table rows, he will fail. This option allows the user to defer the check to a later time by using DBCC CHECKTABLE.

A. Add files to the assembly:

ALTER ASSEMBLY ASSEMBLY_NAME

Add file from Client_file_specifier [as file_name]|file_bits as file_name}[,.... n]][;]

B. Update the assembly:

Use database_name

Go

ALTER ASSEMBLY ASSEMBLY_NAME

Drop file All

Go

ALTER ASSEMBLY ASSEMBLY_NAME

from <client_assembly_specifier>|<assembly_bits>]

Add file from Client_file_specifier [as file_name]|file_bits as file_name}[,.... n]][;]

(4) Deleting an assembly

Deleting an assembly is the removal of the assembly and all its associated files, such as source code and debug files, from the database. However, if the assembly is referenced by another object, an error is returned.

Drop assembly assembly_name[,.... N]
[With no dependents]

Where with no dependents: Represents a related assembly that removes only assembly_name without deleting the assembly reference. If you do not specify it, drop assembly will delete assembly_name and all related assemblies.

Iii. creating CLR functions (function)

To create a CLR program that is referenced by SQL Server, you need to reference the Microsoft.SqlServer.Server namespace, and creating a CLR function also requires that the Sqlfunctionattribute attribute class under that namespace is about to [ Microsoft.SqlServer.ServerSqlFunction.] Place the head of the CLR function.

1. Creating CLR Scalar-valued functions

(1) using C # to write a CLR scalar value function After creating a CLR function in VS2010, compile it into a DLL file and add the file to the database.

(2) using a CLR scalar-valued function in SQL Server creates a function that references the registered assembly using the CREATE function.

Create function--[schema_name.] Function_name//[schema_name.] such as: [dbo.]
(
{@parameter_name [as] [Type_schema_name.] Parameter_data_type [=default]}[,.... N]
)
return {Return_date_type}
[With <clr_function_option> [,... N]]
[As]external name Assembly_name.class_name.method_name

where external name Assembly_name.class_name.method_name: Specifies the method that binds the assembly to the function. <clr_function_option>::={[returns null on NULL input | called no NULL input] | [Execute_as_clause]} where returns NULL on NULL input | Called No null input] | [Execute_as_clause]: Specifies the Onnullcall property of a scalar-valued function. If not specified, the default value is called on null input. This means that the function body is executed even if the passed argument is null. If returns null on NULL input is specified in a CLR function, it indicates that when any one of the parameters received by SQL Server is null, it can return null without actually calling the function body. takes precedence over the properties indicated by the CREATE FUNCTION statement. The Onnullcall property cannot be specified for a table-valued function.

2. Creating CLR Table-valued functions
(1) Writing CLR table-valued functions using C #
The CLR table-valued function returns only one table, creates the corresponding function in. NET, and returns a IEnumerable interface that represents a collection. An instance of an object in the collection is not a table recognized in SQL Server, so you need to specify FillRowMethodName in the function's properties, which is used to set the. NET is converted to the function name of the table column. The feature [Microsoft.SqlServer.Server.SqlFunction (fillrowmethodname= "fillsplittable")] is placed with the header of the table-valued function to specify that the function under that attribute is a CLR table-valued function. Where Fillsplittable is the name of the function that converts objects in. NET into table columns. There are also applications for the. NET must be a static method for converting objects into table columns. The first parameter must be a System.Object type, and the next number of arguments is the number of columns. The next parameter must be declared as a ref parameter. The data type and order of the columns returned in SQL Server must be the same as the data type and order of the ref parameter in the function. Compile the DLL file and add it to the database after you have finished writing it.
(2) using CLR table-valued functions in SQL Server
A, update the assembly
To use CLR table-valued functions written in C # in SQL Server, you must first update the assembly.
Such as:
ALTER ASSEMBLY ASSEMBLY_NAME
From ' Assembly address '
With Permission_set=safe
B. Creating CLR Table-valued functions
Create function [schema_name.] Function_name
(
{@parameter_name [as][type.schema_name.]
Parameter_data_type [=default]}[,... N]
)
Return table<clr_table_type_definition>
[With <clr_function_option>[,... N]]
[Order (<order_clause>)]
[as]external name assembly_name.class_name.method_name[;]
where <clr_table_type_definition>::= ({column_name data_type}[,... n]) defines the table data type of the CLR function. A table declaration contains only column names and data types. The table is always placed in the primary filegroup. Order (<order_clause>) specifies the order in which results are returned from table-valued functions.

3. Using CLR functions in T-SQL

Iv. creating CLR Stored procedures (Procedure)

1. The functions required to write CLR stored procedures using C #:
A function written in C # that can be used for CLR stored procedure references must be identified by using the SqlProcedure property. The stored procedure does not require a return value, so the void function is established in C #. Stored procedures are typically used to query and generate a table of queries, and in C # you need to use the SqlPipe object to pass tabular results back to the client. In general, the SqlPipe object is obtained through the pipe property of the SqlContext class, and then the Send () method of the pipe object is called to pass the table result or information to the client, or use the Executeandsend () of the SqlPipe object. method to pass the query results to the client. The Executeandsend () method provides an efficient way to pass query results to the client. Use the attribute [Microsoft.SqlServer.Server.SqlProcedure] to place the head of the function called by the stored procedure to indicate that the function is the corresponding function of the CLR stored procedure that was called as a CLR stored procedure. Compile code written in C # into a DLL file and add it to the database.

2. Using CLR stored procedures in SQL Server

Create {proc|procedure}[schema_name.] procedure_name [; number]
[
{@parameter [type_schema_name.] Data_type}
[Varying] [=default] [Out|output] [ReadOnly]
[,... N]
[With <procedure_option> [,... N]]
[For replication]
As external name assembly_name.class_name.method_name [;]

Where external name Assembly_name.class_name.method_name specifies the method of the. NET Framework assembly for assembly reference. The class_name must exist with the assembly, and the specified method must be a static method of the class.

<procedure_option>::=[encryption] [recompile]

3. Creating CLR stored procedures with output parameters

The output parameter can also be used in stored procedures, and the value of the parameter with output is modified inside the stored procedure to apply the modification to the outside of the stored procedure equivalent to pointers and ref parameters. The output parameter corresponds to the ref parameter in C #.

4. Using CLR stored procedures in T-SQL

V. Creating a CLR Trigger (Trigger)

A trigger is a special stored procedure that occurs automatically in a database server when something happens.

DML triggers: If the user passes DML event data, the DML trigger is executed. DML events are insert, UPDATE, or DELETE statements for a table or view.

DDL triggers: Used in response to various DDL events, primarily create, alter, and DROP statements.

1. Writing CLR triggers using C #

The Microsoft.SqlServer.Server namespace provides the SqlTriggerContext class for the ability to handle trigger firings in C #. The SqlTriggerContext class provides contextual information about the triggers that are fired, obtained through Sqlcontext.triggercontext. The type of the trigger is obtained through triggeraction, and the Sqltriggercontext.triggeraction property indicates the action to fire the trigger. It is possible to use two special tables in a trigger when writing CLR triggers using C #: Insert and deleted need to use SqlCommand. such as:

SqlConnection connection = new SqlConnection ("Context connection=true"); connection. Open ();//Opens the link SqlCommand sqlcom=new SqlCommand (); sqlcom.commandtext= "SELECT * from" + "inserted"; Use to inserted table reader=sqlcom. ExecuteReader ();//Execute SQL Statement reader.read ();//Read data for (int columnnumber=0;columnnumber<triggercontext.columncount; columnnumber++) {//The column name of each column is passed through the pipe. The Send method is sent to the client pipe.send ("Update Column" +reader. GetName (ColumnNumber) + "?" +triggercontext.isupdatecolumn (ColumnNumber). Tostring ());} Reader. Close ();//Closing the link compiles code written in C # into a DLL file, adds it to the database, and updates the assembly in SQL Server. 2. Using a CLR trigger in SQL Server to add a trigger function in an assembly to SQL Server requires the CREATE TRIGGER command.

Create trigger [schema_name.] Trigger_name
On {table | view}
[With <dml_trigger_option>[,... N]]
{for | after | instead of}
{[insert] [,] [UPDATE] [,] [delete]}
[With Append]
[Not FOR replication]
As external name Assembly_name.class_name.method_name

Where external name assembly_name.class_name.method_name is used to specify the method that the assembly binds to the trigger. The method takes no parameters and must return a null value.

3. Using CLR triggers in T-SQL

Vi. Creating user-defined aggregate functions (Aggregate)

In SQL Server, you often need to customize the aggregation of data by group, the default aggregate function is only sum (), MAX (), MIN (), AVG (), etc., so you need to define a user-defined aggregate function.

1. Write aggregate functions using C #

Creating a user-defined aggregate function must use the attribute [Microsoft.SqlServer.Server.SqlUserDefinedAggregate (format.native)] to place the head of the aggregate function to identify that the function is a user-defined aggregate function. The aggregate function created must also be serializable, using the attribute [Serializable] identifier.

An aggregate function is actually a struct type or an aggregate function that corresponds to a struct type rather than a method in which 4 methods must be implemented:

(1) Init () initialization function: Call the init () method for each set of rows to be processed. In this method, the initialization is performed for each set of rows to be computed;

(2) accumulate () defines a function for a specific aggregation operation: This method is called for each value in all groups. The parameter of this method must be the correct additive type and can also be on a user-defined type. The function defines the aggregate operation of the aggregation function;

(3) Merge () merging function: The result of aggregation must be merged with another aggregation result, calling the merge () method.

(4) Terminate () End Function: This method is called after the last row of each group is processed. Here, the result of the aggregation must be returned with the correct data type.

After you have compiled the aggregate function, recompile the entire project to add the DLL file to the database. After you use the ALTER ASSEMBLY command, the assembly is aggregated into SQL Server.

2. Create a user-defined aggregate function in SQL Server

Create a user-defined aggregate function in SQL Server to reference an aggregate function in the CLR. Create a user custom aggregate function using the Create Aggregate command. As follows:

Create aggregate [schema_name.] Aggregate_name
(
@param_name <input_sqltype>[,... N]
)
Returns <return_type>
External name assembly_name [. class_name]
<input_sqltype>::=
System_scalar_type | {[Udt_schema_name.] Udt_type_name}
<return_type>::=
System_scalar_type | {[Udt_schema_name.] Udt_type_name}

Where System_scalar_type: represents any SQL Server system scalar data type to hold input parameter values or return values. All scalar data types except text, ntext, and image can be used as parameters for custom aggregate functions. Non-scalar types, such as cursor and table, cannot be specified.
Udt_schema_name: Represents the name of the schema to which the CLR user-defined type belongs. If unspecified, the database references udt_schema_name in the following order: the native SQL type namespace, the default schema for the current user in the current database, and the DBO schema in the current database.

Udt_type_name: Represents the name of the CLR user custom type that is created in the current database. If Udt_schema_name is not specified, SQL Server assumes that the type belongs to the schema of the current user.

assembly_name [. Class_name]: Represents a specified assembly that is bound to a user-defined aggregate function and optionally, the name of the schema to which the assembly belongs and the class name that implements the user-defined aggregate function in the assembly.

3. Using user-defined aggregate functions in T-SQL

Create aggregate Countvowels
(
@input nvarchar (4000)
)
returns int
External name Testassembly.countvowels
Go
Select City, COUNT (city) as personcount,dbo. Countvowels (city) as Cityvowelscount
From Person.Address
Group BY City

Vii. creating a CLR user-defined type (UDT)

Create CLR user-defined types to extend the type system of SQL, which can be used to define the type of a column in a table or the type of a variable or routine (stored procedure, trigger, etc.) parameter in a T-SQL. A user-defined type instance can be a column in a table that is more than a handle, a variable in a function or stored procedure, or a parameter to a function or stored procedure.

1. Defining types using C #

The user-defined type must implement the interface inullable, stating that the IsNull property represents a null value for the type, and that the user-defined type is represented in C # with a serializable struct, which is the same as a CLR user-defined aggregate function. Compile the C # code and build the DLL file and update it to the database.

2. Using CLR user-defined types in SQL Server

To create a CLR user-defined type using the Create Type command, you can create a user-defined type that is based not only on the SQL data type, but also on the CLR-based user custom type.

Create Type [schema_name] Type_name

External name Assembly_name. [Class_name]

3. Using CLR user-defined Types

Create Type Myfirsttype

External name Mytypeassembly.myfirsttype

Go

Select Table Testmyfirsttype

(

T Myfirsttype;

)

Go

INSERT INTO Testmyfirsttype

VALUES (' 1,7 ');

INSERT INTO Testmyfirsttype

VALUES (' 6,0 ');

Go

Select T

From Testmyfirsttype

SQL Server integration with the CLR