Hello World Ready Sample

  • Article

 

Applies To: SQL Server 2016 Preview

The Hello World Ready sample demonstrates the basic operations that are involved in creating, deploying, and testing a simple world ready common language runtime (CLR) integration-based stored procedure. A world-ready component can be easily localized into different languages for different markets around the world without changing the component's source code. This sample also demonstrates how to return data through an output parameter and through a record, which is dynamically constructed by the stored procedure and returned to the client.This sample is almost identical to the Hello World Sample except that it is much easier and safer to localize this application. To change localized text requires the following:

  1. Changing an XML file (the .resx file) for the particular culture in the resources directory

  2. Building the culture's resources file by using resgen 

  3. Building the updated satellite DLL for that culture

  4. Dropping and adding that assembly in SQL Server

The source code and assembly for the CLR stored procedure itself does not change. A build.cmd script is provided which demonstrates how to compile and link the resource assemblies.Although the source code for the application creates a resource manager based the currently executing assembly, you do not have to embed the culture neutral resources in the DLL that contains the stored procedure. The System.Resources.NeutralResourcesLanguage attribute permits the culture-neutral resources to exist in a satellite DLL. It is much better to use a separate DLL for this purpose so that when localized text needs to be added or changed, the primary DLL that contains the CLR stored procedure does not have to be changed. This is especially useful for CLR user-defined types that might have columns and other dependencies which would make it difficult to drop and re-add the type.Ordinarily, the satellite DLL versions must be identical to the main assembly version. However, you can use the SatelliteContractVersion attribute to allow the main assembly to be updated without updating the satellite assemblies too. For more information, see the ResourceManager class in the Microsoft .NET documentation.

Prerequisites

This sample works only with SQL Server 2005 and later versions.

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. In c:\MySample, create the file messages.resx and copy the sample code into the file.

  5. In c:\MySample, create the file messages.de.resx by saving the file messages.resx as messages.de.resx after changing the line

    • <value xml:space="preserve">Hello, World!</value>

    • To read 

    • <value xml:space="preserve">Hallo Welt!</value>

  6. In c:\MySample, create the file messages.es.resx by saving the file messages.resx as messages.es.resx after changing the line

    • <value xml:space="preserve">Hello, World!</value>

    • To read 

    • <value xml:space="preserve">Hola a todos</value>

  7. In c:\MySample, create the file messages.fr.resx by saving the file messages.resx as messages.fr.resx after changing the line

    • <value xml:space="preserve">Hello, World!</value>

    • To read 

    • <value xml:space="preserve">Bonjour !</value>

  8. In c:\MySample, create the file messages.fr-FR.resx by saving the file messages.resx as messages.fr-FR.resx after changing the line

    • <value xml:space="preserve">Hello, World!</value>

    • To read 

    • <value xml:space="preserve">Bonjour de France!</value>

  9. In c:\MySample, create the file messages.it.resx by saving the file messages.resx as messages.it.resx after changing the line

    • <value xml:space="preserve">Hello, World!</value>

    • To read 

    • <value xml:space="preserve">Buongiorno</value>

  10. In c:\MySample, create the file messages.ja.resx by saving the file messages.resx as messages.ja.resx after changing the line

    • <value xml:space="preserve">Hello, World!</value>

    • To read 

    • <value xml:space="preserve"> ã?“ã‚“ã?«ã?¡ã?¯</value>

  11. In c:\MySample, create the file build.com and copy the sample code into the file

  12. Build the satellite assembles by executing the file build at the command prompt.

  13. Compile the sample code from the command line prompt by executing the one of the following:

    • 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 /out:HelloWorldReady.dll /target:library HelloWorld.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 /out:HelloWorldReady.dll /target:library Hello.csCopy the tsql installation code into a file and save it as Install.sql in the sample directory.

  14. If the sample is installed in a directory other then C:\MySample\, edit the file Install.sql as indicated to point to that location.

  15. Deploy the assembly and stored procedure by executing

    • sqlcmd -E -I -i install.sql

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

  17. Execute the test script with the following command

    • sqlcmd -E -I -i test.sql

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

  19. 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.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
using System.Threading;
using System.Resources;
using System.Reflection;
using System.Runtime.CompilerServices;

[assembly: System.Resources.NeutralResourcesLanguage("", System.Resources.UltimateResourceFallbackLocation.Satellite)]
[assembly: System.Security.Permissions.SecurityPermissionAttribute(System.Security.Permissions.SecurityAction.RequestMinimum)]
[assembly: System.Runtime.ConstrainedExecution.ReliabilityContract(System.Runtime.ConstrainedExecution.Consistency.MayCorruptInstance, System.Runtime.ConstrainedExecution.Cer.None)]

    public sealed partial class StoredProcedures
    {
        private StoredProcedures()
        {
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), Microsoft.SqlServer.Server.SqlProcedure]
        public static void HelloWorldReady(string culture, out string greeting)
        {
ResourceManager rm 
= new ResourceManager("Messages", 
Assembly.GetExecutingAssembly());

string message = rm.GetString("HelloWorld", CultureInfo.GetCultureInfo(culture));

            Microsoft.SqlServer.Server.SqlMetaData columnInfo
                = new Microsoft.SqlServer.Server.SqlMetaData("Column1", SqlDbType.NVarChar, 24);
            SqlDataRecord greetingRecord
                = new SqlDataRecord(new Microsoft.SqlServer.Server.SqlMetaData[] { columnInfo });
            greetingRecord.SetString(0, message);
            SqlContext.Pipe.Send(greetingRecord);
            greeting = message;
        }
    }

Visual Basic

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Globalization
Imports System.Resources
Imports System.Reflection
Imports System.Runtime.InteropServices
<Assembly: AssemblyVersion("1.0.*")> 
<Assembly: System.Runtime.InteropServices.ComVisible(False)> 
<Assembly: System.CLSCompliant(True)> 
<Assembly: System.Resources.NeutralResourcesLanguage("", System.Resources.UltimateResourceFallbackLocation.Satellite)> 
<Assembly: System.Security.Permissions.SecurityPermissionAttribute(System.Security.Permissions.SecurityAction.RequestMinimum)> 
<Assembly: System.Runtime.ConstrainedExecution.ReliabilityContract(System.Runtime.ConstrainedExecution.Consistency.WillNotCorruptState, Runtime.ConstrainedExecution.Cer.None)> 

Partial Public NotInheritable Class StoredProcedures
    Private Sub New()
    End Sub
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub HelloWorldReady(ByVal culture As String, ByRef greeting As String)
        Dim rm As New ResourceManager("Messages", Assembly.GetExecutingAssembly())
        Dim message As String = rm.GetString("HelloWorld", CultureInfo.GetCultureInfo(culture))
        Dim columnInfo As New Microsoft.SqlServer.Server.SqlMetaData("Column1", _
            SqlDbType.NVarChar, 24)
        Dim greetingRecord As New SqlDataRecord(New  _
            Microsoft.SqlServer.Server.SqlMetaData() {columnInfo})
        greetingRecord.SetString(0, message)
        SqlContext.Pipe.Send(greetingRecord)
        greeting = message
    End Sub
End Class

This is build.com, which builds the satellite assemblies.

resgen Messages.resx
resgen Messages.de.resx
resgen Messages.es.resx
resgen Messages.fr.resx
resgen Messages.fr-Fr.resx
resgen Messages.it.resx
resgen Messages.ja.resx
if not exist de/ mkdir de
if not exist es/ mkdir es
if not exist fr/ mkdir fr
if not exist fr-FR/ mkdir fr-FR
if not exist it/ mkdir it
if not exist ja/ mkdir ja
al /t:lib /culture:de /embed:Messages.de.resources /out:de\HelloWorldReady.resources.dll
al /t:lib /culture:es /embed:Messages.es.resources /out:es\HelloWorldReady.resources.dll
al /t:lib /culture:fr /embed:Messages.fr.resources /out:fr\HelloWorldReady.resources.dll
al /t:lib /culture:fr-FR /embed:Messages.fr-FR.resources /out:fr-FR\HelloWorldReady.resources.dll
al /t:lib /culture:it /embed:Messages.it.resources /out:it\HelloWorldReady.resources.dll
al /t:lib /culture:ja /embed:Messages.ja.resources /out:ja\HelloWorldReady.resources.dll
al /t:lib /culture:"" /embed:Messages.resources /out:HelloWorldReady.resources.dll

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

USE AdventureWorks
GO

-- Drop existing sproc and assembly if any.

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

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

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.neutral')
DROP ASSEMBLY [HelloWorldReady.resources.neutral]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.de')
DROP ASSEMBLY [HelloWorldReady.resources.de]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.es')
DROP ASSEMBLY [HelloWorldReady.resources.es]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.fr')
DROP ASSEMBLY [HelloWorldReady.resources.fr]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.fr-FR')
DROP ASSEMBLY [HelloWorldReady.resources.fr-FR]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.it')
DROP ASSEMBLY [HelloWorldReady.resources.it]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.ja')
DROP ASSEMBLY [HelloWorldReady.resources.ja]
GO

DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of this variable if you have installed the sample someplace other than the default location.
Set @SamplesPath = N'C:\MySample\'

-- Add the assembly and CLR integration based stored procedure

CREATE ASSEMBLY HelloWorldReady
FROM @SamplesPath + 'HelloWorldReady.dll'
WITH permission_set = Safe;


CREATE ASSEMBLY [HelloWorldReady.resources.neutral]
FROM @SamplesPath + 'HelloWorldReady.resources.dll'
WITH permission_set = Safe; 

CREATE ASSEMBLY [HelloWorldReady.resources.de]
FROM @SamplesPath + '\de\HelloWorldReady.resources.dll'
WITH permission_set = Safe;

CREATE ASSEMBLY [HelloWorldReady.resources.es]
FROM @SamplesPath + '\es\HelloWorldReady.resources.dll'
WITH permission_set = Safe;

CREATE ASSEMBLY [HelloWorldReady.resources.fr]
FROM @SamplesPath + '\fr\HelloWorldReady.resources.dll'
WITH permission_set = Safe;

CREATE ASSEMBLY [HelloWorldReady.resources.fr-FR]
FROM @SamplesPath + '\fr-FR\HelloWorldReady.resources.dll'
WITH permission_set = Safe;

CREATE ASSEMBLY [HelloWorldReady.resources.it]
FROM @SamplesPath + '\it\HelloWorldReady.resources.dll'
WITH permission_set = Safe;

CREATE ASSEMBLY [HelloWorldReady.resources.ja]
FROM @SamplesPath + '\ja\HelloWorldReady.resources.dll'
WITH permission_set = Safe;
GO 


CREATE PROCEDURE usp_HelloWorldReady
(
@Culture NVarchar(12),
@Greeting NVarchar(24) OUTPUT
)
AS EXTERNAL NAME HelloWorldReady.StoredProcedures.HelloWorldReady;
GO

USE master;
GO

This is test.sql, which tests the sample by executing the functions on each locale.

USE AdventureWorks
GO

DECLARE @GreetingDe nvarchar(24);
DECLARE @GreetingDe_CH nvarchar(24);
DECLARE @GreetingEn nvarchar(24);
DECLARE @GreetingEs nvarchar(24);
DECLARE @GreetingFr nvarchar(24);
DECLARE @GreetingFr_FR nvarchar(24);
DECLARE @GreetingIt nvarchar(24);
DECLARE @GreetingJa nvarchar(24);

--German as spoken anywhere in the world (the neutral German culture)
EXEC usp_HelloWorldReady 'de', @GreetingDe OUTPUT;
--German as spoken in Switzerland.  Because we don't have a specific assembly
--for this case, the .NET Framework will automatically fall back to the neutral German culture DLL.
EXEC usp_HelloWorldReady 'de-CH', @GreetingDe_CH OUTPUT;
EXEC usp_HelloWorldReady 'en', @GreetingEn OUTPUT;
EXEC usp_HelloWorldReady 'es', @GreetingEs OUTPUT;
--French as spoken anywhere in the world (the neutral French culture)
EXEC usp_HelloWorldReady 'fr', @GreetingFr OUTPUT
--French as spoken in France.  Since we do have a specific assembly for this case, a specific 
--greeting is provided from that DLL.  The neutral French culture DLL is not used in this case.
EXEC usp_HelloWorldReady 'fr-FR', @GreetingFr_FR OUTPUT
EXEC usp_HelloWorldReady 'it', @GreetingIt OUTPUT;
EXEC usp_HelloWorldReady 'ja', @GreetingJa OUTPUT;

SELECT @GreetingDe AS OUTPUT_PARAMETER_DE;
SELECT @GreetingDe_CH AS OUTPUT_PARAMETER_De_CH;
SELECT @GreetingEn AS OUTPUT_PARAMETER_EN;
SELECT @GreetingEs AS OUTPUT_PARAMETER_ES;
SELECT @GreetingFr AS OUTPUT_PARAMETER_FR;
SELECT @GreetingFr_FR AS OUTPUT_PARAMETER_Fr_FR;
SELECT @GreetingIt AS OUTPUT_PARAMETER_IT;
SELECT @GreetingJa AS OUTPUT_PARAMETER_JA;

GO

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

USE AdventureWorks;
GO

-- Drop existing sproc and assembly if any.

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

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

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.neutral')
DROP ASSEMBLY [HelloWorldReady.resources.neutral]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.de')
DROP ASSEMBLY [HelloWorldReady.resources.de]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.es')
DROP ASSEMBLY [HelloWorldReady.resources.es]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.fr')
DROP ASSEMBLY [HelloWorldReady.resources.fr]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.fr-FR')
DROP ASSEMBLY [HelloWorldReady.resources.fr-FR]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.it')
DROP ASSEMBLY [HelloWorldReady.resources.it]
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.ja')
DROP ASSEMBLY [HelloWorldReady.resources.ja]
GO

USE master;
GO

See Also

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