Ms SQL Server CLR development SQL CLR

SQL clr 1. Configure SQL Server to allow CLR integration:

1. Click Start and point to allProgram", Microsoft SQL Server 2005, and" configuration tools ", and then click" peripheral application configurator ".

2. In the SQL Server 2005 peripheral application configurator tool, click "peripheral application configurator of the function ".

3. Select your server instance, expand the "Database Engine" option, and click "CLR integration ".

 

4. Select "enable CLR integration ".

In addition, you can run the following query in SQL Server (this query requires the alter settings permission ):

Use [database]
Sp_configure 'clr enabled', 1;
Go
Reconfigure;
Go

2. Create an SQL server project and configure database connection information. Right-click the properties of the project, select "Database", and set the permission level to "unsafe"

III,

Add a. CS file after the project is created. The following shows how to extend the table value function.

Using system;
Using system. Collections. Generic;
Using system. text;
Using Microsoft. sqlserver. server;
Using system. diagnostics;
Using system. Data. sqltypes;
Using system. collections;

Public partial class functest
{
// Table valued function definition. The first method (initmethod) is assigned the sqlfunction attribute, which is used to specify it as the entry point of the table valued function.
// This method must return the ienumerable or ienumerator object. This object contains the data that will be used to fill the returned table.
// When the function is executed, SQL Server cyclically accesses each object in the ienumerator object and uses it to fill data rows.
// To this end, it will pass the object to the second method fillrow in the class. This method converts the object to a row in the returned table.
// This method is specified in the fillrowmethodname parameter of the sqlfunction attribute
// After the project is deployed, the corresponding table value function DBO. readeventlog is generated in the database.
[Sqlfunction (tabledefinition = "logtime datetime, message nvarchar (4000), category nvarchar (4000), instanceid bigint ",
Name = "readeventlog", fillrowmethodname = "fillrow")]
Public static ienumerable initmethod (string LOGNAME)
{
Return new EventLog (LOGNAME, environment. machinename). entries;
}
Public static void fillrow (Object OBJ, out sqldatetime timewritten, out sqlchars message, out sqlchars category, out long instanceid)
{
Eventlogentry = (eventlogentry) OBJ;
Timewritten = new sqldatetime (eventlogentry. timewritten );
Message = new sqlchars (eventlogentry. Message );
Category = new sqlchars (eventlogentry. Category );
Instanceid = eventlogentry. instanceid;
}
}

Add a stored procedure file after the project is created. The following shows how to perform the stored procedure extension.

 

Using system;
Using system. Data;
Using system. Data. sqlclient;
Using system. Data. sqltypes;
Using Microsoft. sqlserver. server;

Public partial class storedprocedures
{
[Microsoft. sqlserver. server. sqlprocedure]
/// <Summary>
///
/// </Summary>
/// <Param name = "greeting"> return value </param>
Public static void sayhello (ref string greeting)
{
Sqlmetadata columninfo = new sqlmetadata ("columnname", sqldbtype. nvarchar, 12 );
Sqldatarecord greetingrecord = new sqldatarecord (New sqlmetadata [] {columninfo });
Greetingrecord. setstring (0, "helloworld ");
// Call the send method of the pipe object to directly send the sqldatarecord object to the client.
Sqlcontext. Pipe. Send (greetingrecord );
// Output parameters
Greeting = "nice to see you! ";
}
}

4. compile and generate DLL files and deploy them to the database. (Build ----> deploy projectname)

In this way, you can deploy the project to the corresponding assembly, function, and stored procedure in the database.

 

SQL script generates an assembly. The function is as follows:

Create Assembly helloworld
From 'd: \ helloworld. dll'
With permission_set = unsafe
Go

Create Function readeventlog (@ LOGNAME nvarchar (100 ))
Returns table
(
Logtime datetime,
[Message] nvarchar (4000 ),
CATEGORY nvarchar (4000 ),
Instanceid bigint
)
As
External name helloworld. functest. initmethod
Go
 

 

Create procedure [DBO]. [Hello]
@ Greeneting [nvarchar] (4000) Output
With execute as caller
As
External name [SSP]. [storedprocedures]. [Hello]
Go
Exec sys. sp_addextendedproperty @ name = n' autodeployed', @ value = n' Yes ', @ level0type = n' schema', @ level0name = n' dbo ', @ level1type = n' procedure ', @ level1name = n' hello'

Go
Exec sys. sp_addextendedproperty @ name = n' sqlassemblyfile', @ value = n' hello. CS ', @ level0type = n' schema', @ level0name = n' dbo', @ level1type = n' procedure ', @ level1name = n' hello'

Go
Exec sys. sp_addextendedproperty @ name = n' sqlassemblyfileline ', @ value = 10, @ level0type = n' schema', @ level0name = n' dbo', @ level1type = n' procedure ', @ level1name = n'hello'

1. Configure SQL Server to allow CLR integration:

1. Click the start button, point to "all programs", Microsoft SQL Server 2005, and "configuration tools", and then click "peripheral application configurator ".

2. In the SQL Server 2005 peripheral application configurator tool, click "peripheral application configurator of the function ".

3. Select your server instance, expand the "Database Engine" option, and click "CLR integration ".

 

4. Select "enable CLR integration ".

In addition, you can run the following query in SQL Server (this query requires the alter settings permission ):

Use [database]
Sp_configure 'clr enabled', 1;
Go
Reconfigure;
Go

2. Create an SQL server project and configure database connection information. Right-click the properties of the project, select "Database", and set the permission level to "unsafe"

III,

Add a. CS file after the project is created. The following shows how to extend the table value function.

Using system;
Using system. Collections. Generic;
Using system. text;
Using Microsoft. sqlserver. server;
Using system. diagnostics;
Using system. Data. sqltypes;
Using system. collections;

Public partial class functest
{
// Table valued function definition. The first method (initmethod) is assigned the sqlfunction attribute, which is used to specify it as the entry point of the table valued function.
// This method must return the ienumerable or ienumerator object. This object contains the data that will be used to fill the returned table.
// When the function is executed, SQL Server cyclically accesses each object in the ienumerator object and uses it to fill data rows.
// To this end, it will pass the object to the second method fillrow in the class. This method converts the object to a row in the returned table.
// This method is specified in the fillrowmethodname parameter of the sqlfunction attribute
// After the project is deployed, the corresponding table value function DBO. readeventlog is generated in the database.
[Sqlfunction (tabledefinition = "logtime datetime, message nvarchar (4000), category nvarchar (4000), instanceid bigint ",
Name = "readeventlog", fillrowmethodname = "fillrow")]
Public static ienumerable initmethod (string LOGNAME)
{
Return new EventLog (LOGNAME, environment. machinename). entries;
}
Public static void fillrow (Object OBJ, out sqldatetime timewritten, out sqlchars message, out sqlchars category, out long instanceid)
{
Eventlogentry = (eventlogentry) OBJ;
Timewritten = new sqldatetime (eventlogentry. timewritten );
Message = new sqlchars (eventlogentry. Message );
Category = new sqlchars (eventlogentry. Category );
Instanceid = eventlogentry. instanceid;
}
}

Add a stored procedure file after the project is created. The following shows how to perform the stored procedure extension.

 

Using system;
Using system. Data;
Using system. Data. sqlclient;
Using system. Data. sqltypes;
Using Microsoft. sqlserver. server;

Public partial class storedprocedures
{
[Microsoft. sqlserver. server. sqlprocedure]
/// <Summary>
///
/// </Summary>
/// <Param name = "greeting"> return value </param>
Public static void sayhello (ref string greeting)
{
Sqlmetadata columninfo = new sqlmetadata ("columnname", sqldbtype. nvarchar, 12 );
Sqldatarecord greetingrecord = new sqldatarecord (New sqlmetadata [] {columninfo });
Greetingrecord. setstring (0, "helloworld ");
// Call the send method of the pipe object to directly send the sqldatarecord object to the client.
Sqlcontext. Pipe. Send (greetingrecord );
// Output parameters
Greeting = "nice to see you! ";
}
}

4. compile and generate DLL files and deploy them to the database. (Build ----> deploy projectname)

In this way, you can deploy the project to the corresponding assembly, function, and stored procedure in the database.

 

SQL script generates an assembly. The function is as follows:

Create Assembly helloworld
From 'd: \ helloworld. dll'
With permission_set = unsafe
Go

Create Function readeventlog (@ LOGNAME nvarchar (100 ))
Returns table
(
Logtime datetime,
[Message] nvarchar (4000 ),
CATEGORY nvarchar (4000 ),
Instanceid bigint
)
As
External name helloworld. functest. initmethod
Go
 

 

Create procedure [DBO]. [Hello]
@ Greeneting [nvarchar] (4000) Output
With execute as caller
As
External name [SSP]. [storedprocedures]. [Hello]
Go
Exec sys. sp_addextendedproperty @ name = n' autodeployed', @ value = n' Yes ', @ level0type = n' schema', @ level0name = n' dbo ', @ level1type = n' procedure ', @ level1name = n' hello'

Go
Exec sys. sp_addextendedproperty @ name = n' sqlassemblyfile', @ value = n' hello. CS ', @ level0type = n' schema', @ level0name = n' dbo', @ level1type = n' procedure ', @ level1name = n' hello'

go
exec sys. sp_addextendedproperty @ name = n' sqlassemblyfileline ', @ value = 10, @ level0type = n' schema', @ level0name = n' dbo', @ level1type = n' procedure ', @ level1name = n'hello'