Exemple d'accès aux données in-process
L'exemple InProcessDataAccess contient plusieurs fonctions simples qui illustrent différentes fonctionnalités du fournisseur d'accès aux données in-process SQL Server CLR.
Configuration préalable requise
Pour créer et exécuter ce projet, les logiciels suivants doivent être installés :
SQL Server ou SQL Server Express. Vous pouvez vous procurer gratuitement SQL Server Express à partir du site Web SQL Server Express Documentation and Samples (en anglais)
Base de données AdventureWorks qui est disponible sur le site Web du Centre pour les développeurs SQL Server
Le Kit de développement logiciel .NET Framework SDK 2.0 ou version ultérieure, ou Microsoft Visual Studio 2005 ou version ultérieure. Vous pouvez vous procurer gratuitement le Kit de développement logiciel .NET Framework SDK.
De plus, les conditions suivantes doivent être réunies :
L'intégration du CLR doit être activée sur l'instance SQL Server que vous utilisez.
Pour activer l'intégration du CLR, effectuez les étapes suivantes :
Activation de l'intégration du CLR
- Exécutez les commandes Transact-SQL suivantes :
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
[!REMARQUE]
Pour activer l'intégration du CLR, vous devez disposer de l'autorisation de niveau serveur ALTER SETTINGS qui est attribuée implicitement aux membres des rôles serveur fixes sysadmin et serveradmin.
La base de données AdventureWorks doit être installée sur l'instance SQL Server que vous utilisez.
Si vous n'êtes pas administrateur de l'instance SQL Server utilisée, vous devez demander à un administrateur de vous accorder l'autorisation CreateAssembly pour terminer l'installation.
Génération de l'exemple
Créez et exécutez l'exemple à l'aide des instructions suivantes :
Ouvrez une invite de commandes Visual Studio ou .NET Framework.
Si nécessaire, créez un répertoire pour votre exemple. Pour cet exemple, nous utiliserons C:\MySample.
Dans c:\MySample, créez inprocda.vb (pour l'exemple Visual Basic) ou inprocda.cs (pour l'exemple C#) et copiez l'exemple de code Visual Basic ou C# approprié (ci-dessous) dans le fichier.
Compilez l'exemple de code dans l'assembly requis à partir de l'invite de ligne de commande en exécutant l'un des éléments suivants, selon le langage choisi.
Vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library InProcDA.vb
Csc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.XML.dll /target:library inprocda.cs
Copiez le code d'installation Transact-SQL dans un fichier et enregistrez-le sous Install.sql dans le répertoire d'exemple.
Si l'exemple est installé dans un répertoire autre que C:\MySample\, modifiez le fichier Install.sql comme indiqué pour pointer sur cet emplacement.
Déployez l'assembly, la procédure stockée et les fonctions en exécutant
- sqlcmd -E -I -i install.sql
Copiez le code d'installation Transact-SQL dans un fichier et enregistrez-le sous test.sql dans le répertoire d'exemple.
Testez l'application en exécutant la ligne suivante à l'invite de commandes :
- sqlcmd -E -I -i test.sql
Copiez le script de nettoyage Transact-SQL dans un fichier et enregistrez-le sous cleanup.sql dans le répertoire d'exemple.
Exécutez le script avec la commande suivante
- sqlcmd -E -I -i cleanup.sql
Exemple de code
Voici les listes de code pour cet exemple.
C#
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public sealed class DataAccessDemo
{
private DataAccessDemo()
{
}
/// <summary>
/// Simple example to send a message to the client.
/// </summary>
public static void SendMessage(string msg)
{
SqlContext.Pipe.Send("Message from server: " + msg);
}
/// <summary>
/// Simple example of performing data access within
/// a function
/// </summary>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = Microsoft.SqlServer.Server.DataAccessKind.Read)]
public static string ReportSqlVersion()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
//create a command from the current context
SqlCommand cmd = conn.CreateCommand();
//execute something
cmd.CommandText = "select @@version";
conn.Open();
//return results as scalar
return (string)cmd.ExecuteScalar();
}
}
/// <summary>
/// Create a result set on the fly and send it to the client.
/// </summary>
public static void SendTransientResultSet()
{
//create the metadata for the columns
Microsoft.SqlServer.Server.SqlMetaData[] columnSchema
= new Microsoft.SqlServer.Server.SqlMetaData[] {
new Microsoft.SqlServer.Server.SqlMetaData("stringcol", SqlDbType.NVarChar, 128)
};
//create a record based on that metadata
SqlDataRecord newRecord = new SqlDataRecord(columnSchema);
//populate it
newRecord.SetString(0, "Hello World!");
//send it
SqlContext.Pipe.Send(newRecord);
}
/// <summary>
/// Execute a command and send the results to the client directly.
/// </summary>
public static void ExecuteToClient()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select @@version";
conn.Open();
SqlContext.Pipe.ExecuteAndSend(cmd);
}
}
/// <summary>
/// Execute a command and send the resultig reader to the client
/// </summary>
public static void SendReaderToClient()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select @@version";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
try
{
SqlContext.Pipe.Send(rdr);
}
finally
{
rdr.Close();
}
}
}
};
Visual Basic
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Diagnostics
Partial Public NotInheritable Class DataAccessDemo
Private Sub New()
End Sub
''' <summary>
''' Simple example of performing data access within a function
''' </summary>
''' <returns></returns>
<SqlFunction(DataAccess:=DataAccessKind.Read)> _
Public Shared Function ReportSqlVersion() As SqlString
Using conn As New SqlConnection("context connection=true")
'create a command from the current context
Dim cmd As SqlCommand = conn.CreateCommand()
'execute something
cmd.CommandText = "SELECT @@VERSION"
conn.Open()
'return results as scalar
Return CType(cmd.ExecuteScalar(), String)
End Using
End Function
''' <summary>
''' Simple example to send a message to the client.
''' </summary>
Public Shared Sub SendMessage(ByVal msg As String)
SqlContext.Pipe.Send(("Message from server: " & msg))
End Sub
''' <summary>
''' Create a result set on the fly and send it to the client.
''' </summary>
Public Shared Sub SendTransientResultSet()
'create the metadata for the columns
Dim columnSchema() As Microsoft.SqlServer.Server.SqlMetaData _
= {New SqlMetaData("stringcol", SqlDbType.NVarChar, 128)}
'create a record based on that metadata
Dim newRecord As New SqlDataRecord(columnSchema)
'populate it
newRecord.SetString(0, "Hello World!")
'send it
SqlContext.Pipe.Send(newRecord)
End Sub
''' <summary>
''' Execute a command and send the results to the client directly.
''' </summary>
Public Shared Sub ExecuteToClient()
Using conn As New SqlConnection("context connection=true")
Dim cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = "SELECT @@VERSION"
conn.Open()
SqlContext.Pipe.ExecuteAndSend(cmd)
End Using
End Sub
''' <summary>
''' Execute a command and send the resulting reader to the client
''' </summary>
Public Shared Sub SendReaderToClient()
Using conn As New SqlConnection("context connection=true")
Dim cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = "SELECT @@VERSION"
conn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader()
Try
SqlContext.Pipe.Send(rdr)
Finally
rdr.Close()
End Try
End Using
End Sub
End Class
Il s'agit du script d'installation Transact-SQL (Install.sql), qui déploie l'assembly et crée les procédures stockées et la fonction requises par cet exemple.
USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))
DROP FUNCTION [ReportSqlVersion];
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO
DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
set @SamplesPath = N'C:\MySample\'
CREATE ASSEMBLY InProcDA FROM @SamplesPath + 'InProcDA.dll'
WITH permission_set = SAFE;
GO
CREATE PROCEDURE [SendMessage] @msg nvarchar(4000)
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendMessage];
GO
CREATE FUNCTION [ReportSqlVersion]() RETURNS nvarchar(4000)
AS EXTERNAL NAME [InProcDA].[DataAccessDemo].[ReportSqlVersion];
GO
CREATE PROCEDURE [SendTransientResultSet]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendTransientResultSet];
GO
CREATE PROCEDURE [ExecuteToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[ExecuteToClient];
GO
CREATE PROCEDURE [SendReaderToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendReaderToClient];
GO
Le code Transact-SQL suivant (test.sql) teste l'exemple en exerçant les procédures stockées et la fonction définies dans cet exemple.
USE AdventureWorks;
GO
-- send a message to the client
EXEC SendMessage N'This is a test message.';
-- exec a function that does data access
SELECT dbo.ReportSqlVersion();
-- exec the proc that sends a result set to the client
EXEC SendTransientResultSet;
EXEC ExecuteToClient;
EXEC SendReaderToClient;
USE master;
GO
Le code Transact-SQL suivant supprime l'assembly, la fonction et les procédures stockées de la base de données.
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))
DROP FUNCTION [ReportSqlVersion];
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO
Voir aussi
Concepts
Scénarios et exemples d'utilisation pour l'intégration du CLR (Common Language Runtime)