Since Microsoft SQL Server 2005, stored procedures (and some other database objects) can be written externally in some .NET language, imported to the SQL database and called as stored procedures. The following sample demonstrates how to call Rebex FTP component from the T-SQL code.
Methods of Rebex FTP API cannot be accessed from T-SQL directly because only static methods can be mapped to stored procedures. Because Rebex FTP API is not based on static methods, a wrapper class should be created to call Rebex FTP API.
NOTE: the following example considers using of “Rebex FTP for .NET” component in MS SQL Server 2008 and using MS Visual Studio 2008.
UPDATE: you cannot put the "Rebex FTP for .NET CF" libraries (as well as any other "CF" libraries) into MS SQL Server.
Configure SQL Server and the database for CLR
Some of these initial steps can be skipped if the environment is already configured.
Enable CLR in the SQL Server
USE master
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
Create a sample database
Make the database trustworthy to allow access external resources
USE Test
GO
ALTER DATABASE Test SET TRUSTWORTHY ON
GO
Import the Rebex assemblies into the database
CREATE ASSEMBLY [Rebex.Net.Ftp]
-- replace with your path to the Rebex.Net.Ftp.dll:
FROM 'c:\Program Files (x86)\Rebex\FTP for .NET 2.0 Trial\bin\Rebex.Net.Ftp.dll'
-- "UNSAFE" permission set is needed to allow network communication
WITH PERMISSION_SET = UNSAFE;
Note that importing of the Rebex.Net.Ftp assembly also imports all other assemblies the primary assembly depends on.
In our case, a Rebex.Net.ProxySockets assembly is automatically loaded.
You should see that two assemblies in the SQL Server Management Studio:
Write a wrapper class in Visual Studio 2008
Create a SQL Server Project in Visual Studio
Create new VS project of type “SQL Server Project” (located in the “database” section):
After creating the project, you will be prompted to add a database reference.
Specify connection to the database where the Rebex assemblies were imported in the previous step.
Add reference to the Rebex assemblies
In the Visual Studio project References click on the “Add Reference”.
The following dialog should appear:
Select “Rebex.Net.Ftp” and “Rebex.Net.ProxySocket” assemblies and click OK.
NOTE: the list of assemblies is retrieved from the assemblies registered in the SQL Server Database (defined in the previous step), they can not retrieved from filesystem or GAC. The assemblies in the SQL server work in the separate subset of .NET framework and cannot see any assemblies outside.
Create a new method for the .NET code of the stored procedure
Add a new project file “FtpUploadText.cs” with type “Stored Procedure”:
NOTE: the Visual Studio SqlServerProject stores all procedures to the one big partial class – each method into separate .cs file.
Implement body the stored procedure method
Let’s create a following sample method that receives a string and writes it into a text file on the FTP server:
using System;
using Microsoft.SqlServer.Server;
using Rebex.Net;
public partial class StoredProcedures
{
/// <summary>
/// Upload a text to the file on FTP server.
/// </summary>
/// <param name="ftpHost">Hostname of the FTP server.</param>
/// <param name="username">FTP user name.</param>
/// <param name="password">FTP user password.</param>
/// <param name="remotePath">FTP path of the target file.</param>
/// <param name="content">String that will be stored into the target file.</param>
[SqlProcedure]
public static void FtpUploadText(string ftpHost, string username, string password, string remotePath, string content)
{
// create client, connect and log in
Ftp client = new Ftp();
client.Connect(ftpHost);
client.Login(username, password);
// upload a text using a MemoryStream
byte[] data = System.Text.Encoding.Default.GetBytes(content);
System.IO.MemoryStream ms = new System.IO.MemoryStream(data);
client.PutFile(ms, remotePath);
client.Disconnect();
}
};
Build and deploy the stored procedure method
Build the project and import the compiled assembly into the SQL Server Database using the following command:
CREATE ASSEMBLY [MyClrCode]
-- replace with your path to the compiled binaries
FROM 'c:\projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll'
-- "UNSAFE" permission set is needed to allow network communication
WITH PERMISSION_SET = UNSAFE;
NOTE: you can do the same by choosing “Deploy” right clicking on the project name in the Visual Studio.
In that case, don’t forget to set the “Permission level = Unsafe” in the project properties.
Create and use the stored procedure
Map the CLR method to the T-SQL stored procedure
To call the method from the T-SQL, you should create a stored procedure envelope like this:
CREATE PROCEDURE FtpUploadText
(
@host NVarchar(1000),
@username NVarchar(100),
@password NVarchar(100),
@remotePath NVarchar(1000),
@content NVarchar(max)
)
AS
EXTERNAL NAME [MyClrCode].[StoredProcedures].[FtpUploadText]
GO
Syntax of the EXTERNAL NAME phrase is:
[MyClrCode] – name of the assembly (as the assembly has been imported to the SQL Server database)
[StoredProcedures] – class name
[FtpUploadText] – method name
NOTE that the number and types of the stored procedure parameters should be the same as parameters of the method. Parameter names need not be the same.
Execute the stored procedure
The stored procedure can be executed as usual:
EXEC FtpUploadText 'ftp.test.com', 'auser', 'thepassword', '/folder/file.txt', 'Hello world!'