Getting Started with CLR Integration （sql调用C#）

2019独角兽企业重金招聘Python工程师标准>>>

Getting Started with CLR Integration

APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

This topic provides an overview of the namespaces and libraries required to compile database objects using the Microsoft SQL Server integration with the .NET Framework common language runtime (CLR). The topic also shows you how to write, compile, and run a simple CLR stored procedure written in Microsoft Visual C#.

Required Namespaces

The components required to develop basic CLR database objects are installed with SQL Server. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:

System.Data

System.Data.Sql

Microsoft.SqlServer.Server

System.Data.SqlTypes

Writing A Simple "Hello World" Stored Procedure

Copy and paste the following Visual C# or Microsoft Visual Basic code into a text editor, and save it in a file named "helloworld.cs" or "helloworld.vb".

C#

using System;  
using System.Data;  
using Microsoft.SqlServer.Server;  
using System.Data.SqlTypes;  
  
public class HelloWorldProc  
{  
    [Microsoft.SqlServer.Server.SqlProcedure]  
    public static void HelloWorld(out string text)  
    {  
        SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);  
        text = "Hello world!";  
    }  
}  

This simple program contains a single static method on a public class. This method uses two new classes, SqlContext and SqlPipe, for creating managed database objects to output a simple text message. The method also assigns the string "Hello world!" as the value of an out parameter. This method can be declared as a stored procedure in SQL Server, and then run in the same manner as a Transact-SQL stored procedure.

Compile this program as a library, load it into SQL Server, and run it as a stored procedure.

Compile the "Hello World" stored procedure

SQL Server installs the Microsoft .NET Framework redistribution files by default. These files include csc.exe and vbc.exe, the command-line compilers for Visual C# and Visual Basic programs. In order to compile our sample, you must modify your path variable to point to the directory containing csc.exe or vbc.exe. The following is the default installation path of the .NET Framework.

C:\Windows\Microsoft.NET\Framework\(version)  

Version contains the version number of the installed .NET Framework redistributable. For example:

C:\Windows\Microsoft.NET\Framework\v4.6.1  

Once you have added the .NET Framework directory to your path, you can compile the sample stored procedure into an assembly with the following command. The /target option allows you to compile it into an assembly.

For Visual C# source files:

csc /target:library helloworld.cs   

For Visual Basic source files:

vbc /target:library helloworld.vb  

These commands launch the Visual C# or Visual Basic compiler using the /target option to specify building a library DLL.

Loading and Running the "Hello World" Stored Procedure in SQL Server

Once the sample procedure has successfully compiled, you can test it in SQL Server. To do this, open SQL Server Management Studio and create a new query, connecting to a suitable test database (for example, the AdventureWorks sample database).

The ability to execute common language runtime (CLR) code is set to OFF by default in SQL Server. The CLR code can be enabled by using the sp_configure system stored procedure. For more information, see Enabling CLR Integration.

We will need to create the assembly so we can access the stored procedure. For this example, we will assume that you have created the helloworld.dll assembly in the C:\ directory. Add the following Transact-SQL statement to your query.

CREATE ASSEMBLY helloworld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE  

Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement. We will call our stored procedure "hello":

  
CREATE PROCEDURE hello  
@i nchar(25) OUTPUT  
AS  
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld  
-- if the HelloWorldProc class is inside a namespace (called MyNS),  
-- the last line in the create procedure statement would be  
-- EXTERNAL NAME helloworld.[MyNS.HelloWorldProc].HelloWorld  

Once the procedure has been created, it can be run just like a normal stored procedure written in Transact-SQL. Execute the following command:

DECLARE @J nchar(25)  
EXEC hello @J out  
PRINT @J  

This should result in the following output in the SQL Server Management Studio messages window.

Hello world!  
Hello world!  

Removing the "Hello World" Stored Procedure Sample

When you are finished running the sample stored procedure, you can remove the procedure and the assembly from your test database.

First, remove the procedure using the drop procedure command.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hello')  
   drop procedure hello  

Once the procedure has been dropped, you can remove the assembly containing your sample code.

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'helloworld')  
   drop assembly helloworld  

 

其他的示例：https://www.c-sharpcorner.com/blogs/calling-web-service-from-sql-server-using-sql-clr

 

 

Problem 

In my previous project, I was asked to call Web Services from SQL Server stored procedures. 

It was done using SQL CLR. By using CLR, we can run and manage the code inside the SQL Server. 

Code that runs within CLR is referred to as a managed code. 

We can create the stored procedures, triggers, user defined types and user-defined aggregates in the managed code. We can achieve significant performance increases because the managed code compiles to the native code prior to the execution. We can use SQL CLR in in SQL Server 2005 and later. 

Why SQL CLR in SQL Server?

In some cases, some tasks are not possible by T-SQL as per my requirement. We can go with SQL CLR. 

The tools used in this post are.

• Visual Studio 2015
• SQL Server 2014

In Action

 

1. Create SQL Server Database project in VS 2015.
   
   
    
2. Add SQL CLR C# stored procedure. 
   
   
   
   Name the stored procedure As CallWebService.
    
3. Add C# codes to Call Webservice. I am using the Web Service, mentioned below to do the test.
   
   http://www.webservicex.net/globalweather.asmx?op=GetCitiesByCountry
   1. HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://www.webserviceX.NET//globalweather.asmx//GetCitiesByCountry?CountryName=Sri Lanka");  
   2.   
   3. request.Method = "GET";  
   4. request.ContentLength = 0;  
   5. request.Credentials = CredentialCache.DefaultCredentials;  
   6. HttpWebResponse response = (HttpWebResponse)request.GetResponse();   
   7. Stream receiveStream = response.GetResponseStream();   
   8. // Pipes the stream to a higher level stream reader with the required encoding format.   
   9. StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);   
   10.   
   11. Console.WriteLine("Response stream received.");  
   12. System.IO.File.WriteAllText("d://response.txt", readStream.ReadToEnd());  
   13.   
   14. response.Close();  
   15. readStream.Close();  
   I am simply writing a response to a text file. You can do it as per your desire. 
    
4. Enable CLR and set trust worthy on the database. I am using AdventureWorks database. 
   1. sp_configure 'show advanced options', 1;  
   2. GO  
   3. RECONFIGURE;  
   4. GO  
   5. sp_configure 'clr enabled', 1;  
   6. GO  
   7. RECONFIGURE;  
   8. GO  
   9.   
   10. alter database [AdventureDatabase] set trustworthy on;  
5. Build Visual Studio Project.
   
   It will generate DLL in bin folder. 
    
6. Register assembly in the database. 
   
   Go to AdventureWorks > Programmability > Assemblies 
   
   Right click on Assemblies and click new Assembly.
   
   
   
   Set Permission to External access and browse for our DLL. It is in the bin folder of your project. 
   
   Once you add it, we can see the assembly registered in side Assemblies, as mentioned below. 
   
   
    
7. Create stored procedures to call assembly’s stored procedure. 
   
   
   
   Once you created a stored procedure, you can see locked stored procedure. 
   
   
    
8. Hence, we have finished executing the stored procedure. You can see the text file is generated in the drive.
   
    

转载于:https://my.oschina.net/ethanleellj/blog/3040813