Condividi tramite

Hello World Sample

  • Articolo

 

Applies To: SQL Server 2016 Preview

The Hello World sample demonstrates the basic operations that are involved in creating, deploying, and testing a simple common language runtime (CLR) integration-based stored procedure. This sample also demonstrates how to return data through a record, which is dynamically constructed by the stored procedure and returned to the caller.

The HelloWorld stored procedure returns the string “Hello world!” in a result set consisting of one row. This example illustrates some uses for the classes Microsoft.SqlServer.Server.SqlMetaData, Microsoft.SqlServer.Server.SqlDataRecord and Microsoft.SqlServer.Server.Pipe.

Prerequisites

To create and run this project the following the following software must be installed:

  • SQL Server or SQL Server Express. You can obtain SQL Server Express free of charge from the SQL Server Express Documentation and Samples Web site

  • The AdventureWorks database that is available at the SQL Server  Developer Web site

  • .NET Framework SDK 2.0 or later or Microsoft Visual Studio 2005 or later. You can obtain .NET Framework SDK free of charge.

  • In addition, the following conditions must be met:

  • The SQL Server instance you are using must have CLR integration enabled.

  • In order to enable CLR integration, perform the following steps:

    Enabling CLR Integration

    • Execute the following Transact-SQL commands:

    sp_configure 'clr enabled', 1

    GO

    RECONFIGURE

    GO

    Note

    To enable CLR, you must have ALTER SETTINGS server level permission, which is implicitly held by members of the sysadmin and serveradmin fixed server roles.

  • The AdventureWorks database must be installed on the SQL Server instance you are using.

  • If you are not an administrator for the SQL Server instance you are using, you must have an administrator grant you CreateAssembly permission to complete the installation.

Building the Sample

Create and run the sample by using the following instructions:

  1. Open a Visual Studio or .NET Framework command prompt.

  2. If necessary, create a directory for your sample. For this example, we will use C:\MySample.

  3. In c:\MySample, create HelloWorld.vb (for the Visual Basic sample) or HelloWorld.cs (for the C# sample) and copy the appropriate Visual Basic or C# sample code (below) into the file.

  4. Compile the sample code from the command line prompt by executing one of the following, depending on your choice of language.

    • vbc C:HelloWorld.vb /target:library

    • csc /target:library HelloWorld.cs

  5. Copy the Transact-SQL installation code into a file and save it as Install.sql in the sample directory.

  6. Deploy the assembly and stored procedure by executing

    • sqlcmd -E -I -i install.sql -v root = "C:\MySample\"

  7. Copy Transact-SQL test command script into a file and save it as test.sql in the sample directory.

  8. Execute the test script with the following command

    • sqlcmd -E -I -i test.sql

  9. Copy the Transact-SQL cleanup script into a file and save it as cleanup.sql in the sample directory.

  10. Execute the script with the following command

    • sqlcmd -E -I -i cleanup.sql

Sample Code

The following are the code listings for this sample.

C#

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]
    public static void HelloWorld()
    {
        Microsoft.SqlServer.Server.SqlMetaData columnInfo
                = new Microsoft.SqlServer.Server.SqlMetaData("Column1", SqlDbType.NVarChar, 12);
        SqlDataRecord greetingRecord
            = new SqlDataRecord(new Microsoft.SqlServer.Server.SqlMetaData[] { columnInfo });
        greetingRecord.SetString(0, "Hello world!");
        SqlContext.Pipe.Send(greetingRecord);
    }
};

Visual Basic

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public NotInheritable Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub HelloWorld()
        Dim columnInfo As New Microsoft.SqlServer.Server.SqlMetaData("Column1", _
            SqlDbType.NVarChar, 12)
        Dim greetingRecord As New SqlDataRecord(New  _
            Microsoft.SqlServer.Server.SqlMetaData() {columnInfo})
        greetingRecord.SetString(0, "Hello World!")
        SqlContext.Pipe.Send(greetingRecord)
    End Sub
End Class

This is the Transact-SQL installation script (Install.sql), which deploys the assembly and creates the stored procedure in the database.

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_HelloWorld')
DROP PROCEDURE usp_HelloWorld;
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorld')
DROP ASSEMBLY HelloWorld;
GO
DECLARE @SamplesPath nvarchar(1024)
set @SamplesPath = '$(root)'
CREATE ASSEMBLY HelloWorld 
FROM @SamplesPath + 'HelloWorld.dll'
WITH permission_set = Safe;
GO

CREATE PROCEDURE usp_HelloWorld
--(
--    @Greeting nvarchar(12) OUTPUT
--)
AS EXTERNAL NAME HelloWorld.[StoredProcedures].HelloWorld;
GO

This is test.sql, which tests the sample by executing the stored procedure.

use AdventureWorks
go
execute usp_HelloWorld

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_HelloWorld')
DROP PROCEDURE usp_HelloWorld;
GO

The following Transact-SQL removes the assembly and stored procedure from the database.

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_HelloWorld')
DROP PROCEDURE usp_HelloWorld;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorld')
DROP ASSEMBLY HelloWorld;
GO

See Also

Usage Scenarios and Examples for Common Language Runtime (CLR) Integration