Export (0) Print
Expand All

CLR Transactions Sample

This sample demonstrates controlling transactions by using the managed APIs located in the System.Transactions namespace. In particular, the System.Transactions.TransactionScope class is used to establish a transaction boundary to ensure that inventory figures are not adjusted unless there is sufficient inventory to cover the request, and if there is sufficient inventory that the transfer from of the inventory from one location to another occurs in an atomic fashion. Automatic registration in a distributed transaction is demonstrated by logging changes in inventory to an auditing database stored on a separate instance of

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 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.

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. Since this example requires a signed assembly, create an asymmetric key by typing the command:

    sn -k SampleKey.snk

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

    • Vbc /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll","C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Data.DataSetExtensions.dll","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.Transactions.dll","C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll" /keyfile:Key.snk /target:Library /out:Transaction.dll InventoryMover.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.Transactions.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /keyfile:key.snk /out:Transaction.dll /target:library InventoryMover.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 the Transact-SQL database installation code into a file and save it as installDB.sql in the sample directory.

  8. Install the audit database by executing

    • Sqlcmd –S server_name [ \instance_name ] -E -I -i installDB.sql

    with appropriate values of the instance and server.

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

  10. Execute the test script with the following command

    • sqlcmd -E -I -i test.sql

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

  12. Execute the script with the following command

    • Sqlcmd –S server_name [ \instance_name ] -E -I -i cleanup.sql

      with appropriate values of the instance and server.

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

  14. Execute the script with the following command

    • sqlcmd -E -I -i cleanup.sql

The following are the code listings for this sample.

C#

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Transactions;
using Microsoft.SqlServer.Server;
using System.Security.Principal;

    public static class InventoryMover
    {
        const string contextConnectionString = "context connection=true";

        // **********
        // Important: Change this connection string to refer to a server other than the one
        // you have installed the AdventureWorks database.  This sample demonstrates 
        // two-phase commit across multiple servers, and loopback to the same server is not
        // permitted from CLR integrated based stored procedures.
        // **********
        const string auditConnectionString = "server=<YourServer>; database=InventoryAudit; user=<YourUser>; password=<YourPassword>";

 
        [SqlMethod(DataAccess = DataAccessKind.Read, IsMutator = true)]
        public static void ExecuteTransfer(int productID, short fromLocationID,
            short toLocationID, short quantityToTransfer)
        {
                       // Establish bounds of the transaction
            using (TransactionScope transScope = new TransactionScope())
            {
                using (SqlConnection adventureworksConnection = new
                   SqlConnection(contextConnectionString))
                {
                    // Opening adventureworksConnection automatically enlists it in the 
                    // TransactionScope as part of the transaction.
                    adventureworksConnection.Open();

                    SqlCommand checkCommand = adventureworksConnection.CreateCommand();
                    checkCommand.CommandText = "SELECT TOP 1 Quantity"
                        + " FROM Production.ProductInventory WITH (REPEATABLEREAD)"
                        + " WHERE ProductID = @ProductID AND LocationID = @LocationID;";
                    checkCommand.Parameters.Add("@ProductID", SqlDbType.Int);
                    checkCommand.Parameters[0].Value = productID;
                    checkCommand.Parameters.Add("@LocationID", SqlDbType.Int);
                    checkCommand.Parameters[1].Value = fromLocationID;

                    object result = checkCommand.ExecuteScalar();
                    short availableQuantity = (short)result;

                    if (availableQuantity < quantityToTransfer)
                        //It would be better to throw a custom error, and in some cases to actually
                        //RAISERROR.  Also, it would be better to map product IDs and location IDs to 
                        //names for the error message.
                        throw new ArgumentOutOfRangeException("quantityToTransfer",
                            string.Format("Attempt to transfer {0} of product {1} from"
                                + " location {2} but only {3} is available.",
                                quantityToTransfer, productID, fromLocationID,
                                availableQuantity));


                    //Remove inventory count from source
                    SqlCommand reduceCommand = adventureworksConnection.CreateCommand();
                    reduceCommand.CommandText = "UPDATE Production.ProductInventory"
                        + " SET Quantity = Quantity - @QuantityToTransfer"
                        + " WHERE ProductID = @ProductID AND LocationID = @LocationID;";
                    reduceCommand.Parameters.Add("@ProductID", SqlDbType.Int);
                    reduceCommand.Parameters[0].Value = productID;
                    reduceCommand.Parameters.Add("@LocationID", SqlDbType.SmallInt);
                    reduceCommand.Parameters[1].Value = fromLocationID;
                    reduceCommand.Parameters.Add("@QuantityToTransfer", SqlDbType.SmallInt);
                    reduceCommand.Parameters[2].Value = quantityToTransfer;

                    reduceCommand.ExecuteNonQuery();

                    //Increate inventory count at destination
                    SqlCommand increaseCommand = adventureworksConnection.CreateCommand();
                    increaseCommand.CommandText = "UPDATE Production.ProductInventory"
                        + " SET Quantity = Quantity + @QuantityToTransfer"
                        + " WHERE ProductID = @ProductID AND LocationID = @LocationID;";
                    increaseCommand.Parameters.Add("@ProductID", SqlDbType.Int);
                    increaseCommand.Parameters[0].Value = productID;
                    increaseCommand.Parameters.Add("@LocationID", SqlDbType.SmallInt);
                    increaseCommand.Parameters[1].Value = toLocationID;
                    increaseCommand.Parameters.Add("@QuantityToTransfer", SqlDbType.SmallInt);
                    increaseCommand.Parameters[2].Value = quantityToTransfer;

                    increaseCommand.ExecuteNonQuery();

                    // Create an audit trail of the inventory transfer.  We must impersonate the
                    // client credentials in order for this to work.  Otherwise we'd have to
                    // set up security for the machine account.
//                    SqlConnection auditConnection = adventureworksConnection;
                    using (SqlConnection auditConnection = new SqlConnection(auditConnectionString))
                    {
                        SqlCommand auditCommand = auditConnection.CreateCommand();
                        auditCommand.CommandText = "INSERT InventoryChange "
                            + " (ProductID, FromLocationID, ToLocationID, Quantity) "
                            + " VALUES (@ProductID, @FromLocationID, @ToLocationID, @Quantity);";
                        auditCommand.Parameters.Add("@ProductID", SqlDbType.Int);
                        auditCommand.Parameters[0].Value = productID;
                        auditCommand.Parameters.Add("@FromLocationID", SqlDbType.SmallInt);
                        auditCommand.Parameters[1].Value = fromLocationID;
                        auditCommand.Parameters.Add("@ToLocationID", SqlDbType.SmallInt);
                        auditCommand.Parameters[2].Value = toLocationID;
                        auditCommand.Parameters.Add("@Quantity", SqlDbType.SmallInt);
                        auditCommand.Parameters[3].Value = quantityToTransfer;

                        // Opening auditConnection automatically enlists it in the 
                        // TransactionScope as part of the transaction.
                        auditConnection.Open();
                        auditCommand.ExecuteNonQuery();
                    }

                }
                //  The Complete method commits the transaction.
                transScope.Complete();
            }
        }
    }

Visual Basic

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.Transactions
Imports Microsoft.SqlServer.Server
Imports System.Security.Principal
Imports System.Globalization

Public NotInheritable Class InventoryMover
    Private Const contextConnectionString As String = "context connection=true"

    Private Sub New()

    End Sub

    ' **********
    ' Important: Change this connection string to refer to a server other than the one
    ' you have installed the AdventureWorks database.  This sample demonstrates 
    ' two-phase commit across multiple servers, and loopback to the same server is not
    ' permitted from CLR integrated based stored procedures.
    ' **********
    Private Const auditConnectionString As String = "server=<YourServer>; database=InventoryAudit; user=<YourUser>; password=<YourPassword>"
    <SqlMethod(DataAccess:=DataAccessKind.Read, IsMutator:=True)> _
    Public Shared Sub ExecuteTransfer(ByVal productID As Integer, ByVal fromLocationID As Short, _
        ByVal toLocationID As Short, ByVal quantityToTransfer As Short)
        ' Establish bounds of the transaction
        Using transScope As New TransactionScope()
            Using adventureworksConnection As New SqlConnection(contextConnectionString)
                ' Opening adventureworksConnection automatically enlists it in the 
                ' TransactionScope as part of the transaction.
                adventureworksConnection.Open()

                Dim checkCommand As SqlCommand = adventureworksConnection.CreateCommand()
                checkCommand.CommandText = "SELECT TOP 1 Quantity" _
                        & " FROM Production.ProductInventory WITH (REPEATABLEREAD)" _
                        & " WHERE ProductID = @ProductID AND LocationID = @LocationID;"
                checkCommand.Parameters.Add("@ProductID", SqlDbType.Int)
                checkCommand.Parameters(0).Value = productID
                checkCommand.Parameters.Add("@LocationID", SqlDbType.Int)
                checkCommand.Parameters(1).Value = fromLocationID

                Dim result As Object = checkCommand.ExecuteScalar()
                Dim availableQuantity As Short = CType(result, Short)

                If (availableQuantity < quantityToTransfer) Then
                    'It would be better to throw a custom error, and in some cases to actually
                    'RAISERROR.  Also, it would be better to map product IDs and location IDs to 
                    'names for the error message.
                    Throw New ArgumentOutOfRangeException("quantityToTransfer", _
                        String.Format(CultureInfo.InvariantCulture, "Attempt to transfer {0} of product {1} from" _
                        & " location {2} but only {3} is available.", _
                        quantityToTransfer, productID, fromLocationID, _
                        availableQuantity))
                End If

                'Remove inventory count from source
                Dim reduceCommand As SqlCommand = adventureworksConnection.CreateCommand()
                reduceCommand.CommandText = "UPDATE Production.ProductInventory" _
                    & " SET Quantity = Quantity - @QuantityToTransfer" _
                    & " WHERE ProductID = @ProductID AND LocationID = @LocationID;"
                reduceCommand.Parameters.Add("@ProductID", SqlDbType.Int)
                reduceCommand.Parameters(0).Value = productID
                reduceCommand.Parameters.Add("@LocationID", SqlDbType.SmallInt)
                reduceCommand.Parameters(1).Value = fromLocationID
                reduceCommand.Parameters.Add("@QuantityToTransfer", SqlDbType.SmallInt)
                reduceCommand.Parameters(2).Value = quantityToTransfer

                reduceCommand.ExecuteNonQuery()

                'Increate inventory count at destination
                Dim increaseCommand As SqlCommand = adventureworksConnection.CreateCommand()
                increaseCommand.CommandText = "UPDATE Production.ProductInventory" _
                    & " SET Quantity = Quantity + @QuantityToTransfer" _
                    & " WHERE ProductID = @ProductID AND LocationID = @LocationID;"
                increaseCommand.Parameters.Add("@ProductID", SqlDbType.Int)
                increaseCommand.Parameters(0).Value = productID
                increaseCommand.Parameters.Add("@LocationID", SqlDbType.SmallInt)
                increaseCommand.Parameters(1).Value = toLocationID
                increaseCommand.Parameters.Add("@QuantityToTransfer", SqlDbType.SmallInt)
                increaseCommand.Parameters(2).Value = quantityToTransfer

                increaseCommand.ExecuteNonQuery()


                ' Create an audit trail of the inventory transfer.  We must impersonate the
                ' client credentials in order for this to work.  Otherwise we'd have to
                ' set up security for the machine account.
                'SqlConnection auditConnection = adventureworksConnection
                Using auditConnection As New SqlConnection(auditConnectionString)
                    Dim auditCommand As SqlCommand = auditConnection.CreateCommand()
                    auditCommand.CommandText = "INSERT InventoryChange " _
                        & " (ProductID, FromLocationID, ToLocationID, Quantity) " _
                        & " VALUES (@ProductID, @FromLocationID, @ToLocationID, @Quantity);"
                    auditCommand.Parameters.Add("@ProductID", SqlDbType.Int)
                    auditCommand.Parameters(0).Value = productID
                    auditCommand.Parameters.Add("@FromLocationID", SqlDbType.SmallInt)
                    auditCommand.Parameters(1).Value = fromLocationID
                    auditCommand.Parameters.Add("@ToLocationID", SqlDbType.SmallInt)
                    auditCommand.Parameters(2).Value = toLocationID
                    auditCommand.Parameters.Add("@Quantity", SqlDbType.SmallInt)
                    auditCommand.Parameters(3).Value = quantityToTransfer

                    ' Opening auditConnection automatically enlists it in the 
                    ' TransactionScope as part of the transaction.
                    auditConnection.Open()
                    auditCommand.ExecuteNonQuery()

                End Using
            End Using

            '  The Complete method commits the transaction.
            transScope.Complete()
        End Using
    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

-- Drop existing sproc and assembly if any.

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

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

USE master
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'ExternalSample_Login')
DROP LOGIN ExternalSample_Login;
GO

IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'ExternalSample_Key')
DROP ASYMMETRIC KEY ExternalSample_Key;
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 = 'C:\MySample\'

EXEC('CREATE ASYMMETRIC KEY ExternalSample_Key FROM EXECUTABLE FILE = ''' + @SamplesPath + 'Transaction.dll'';');
CREATE LOGIN ExternalSample_Login FROM ASYMMETRIC KEY ExternalSample_Key
GRANT EXTERNAL ACCESS ASSEMBLY TO ExternalSample_Login
GO

USE AdventureWorks
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 = 'C:\MySample\'
-- Add the assembly and CLR integration based stored procedure

CREATE ASSEMBLY [Transaction] 
FROM @SamplesPath + 'Transaction.dll'
WITH permission_set = External_Access;
GO

CREATE PROCEDURE usp_ExecuteTransfer
(
@ProductID int,
@FromLocationID smallint,
@ToLocationID smallint,
@QuantityToTransfer smallint
)
AS EXTERNAL NAME [Transaction].[InventoryMover].ExecuteTransfer;
GO

This is the Transact-SQL installation script (InstallDB.sql), which creates the audit database in the second instance of SQL Server.

SET NOCOUNT OFF;
GO

PRINT CONVERT(varchar(1000), @@VERSION);
GO

PRINT '';
PRINT 'Started - ' + CONVERT(varchar, GETDATE(), 121);
GO

USE [master];
GO


-- ****************************************
-- Drop Database
-- ****************************************
PRINT '';
PRINT '*** Dropping Database';
GO

IF EXISTS (SELECT [name] FROM [master].[sys].[databases] WHERE [name] = N'InventoryAudit')
    DROP DATABASE [InventoryAudit];

-- If the database has any other open connections close the network connection.
IF @@ERROR = 3702 
    RAISERROR('[InventoryAudit] database cannot be dropped because there are still other open connections', 127, 127) WITH NOWAIT, LOG;
GO


-- ****************************************
-- Create Database
-- ****************************************
PRINT '';
PRINT '*** Creating Database';
GO

DECLARE 
    @sql_path nvarchar(256), 
    @sql_cmd nvarchar(256);

SELECT @sql_path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1)
FROM [master].[sys].[master_files] 
WHERE [database_id] = 1 
    AND [file_id] = 1;

-- COLLATE Latin1_General_CS_AS
EXECUTE (N'CREATE DATABASE [InventoryAudit] 
    ON (NAME = ''InventoryAudit_Data'', FILENAME = N''' + @sql_path + N'InventoryAudit_Data.mdf'', SIZE = 120, FILEGROWTH = 8) 
    LOG ON (NAME = ''InventoryAudit_Log'', FILENAME = N''' + @sql_path + N'InventoryAudit_Log.ldf'' , SIZE = 2, FILEGROWTH = 96);');
GO

ALTER DATABASE [InventoryAudit] 
SET RECOVERY SIMPLE, 
    ANSI_NULLS ON, 
    ANSI_PADDING ON, 
    ANSI_WARNINGS ON, 
    ARITHABORT ON, 
    CONCAT_NULL_YIELDS_NULL ON, 
    QUOTED_IDENTIFIER ON, 
    NUMERIC_ROUNDABORT OFF, 
    PAGE_VERIFY CHECKSUM, 
    ALLOW_SNAPSHOT_ISOLATION OFF;
GO

USE [InventoryAudit];
GO

PRINT '';
PRINT '*** Creating Table';
GO

CREATE TABLE [InventoryChange] (
[InventoryChangeID] int IDENTITY (1, 1) NOT NULL,
[ProductID] int NOT NULL,
[FromLocationID] smallint,
[ToLocationID] smallint,
[Quantity] smallint NOT NULL
);
GO

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

USE AdventureWorks
GO
SELECT 'Before first transfer quantity of adjustable races at Tool Crib = ', Quantity FROM Production.ProductInventory
WHERE ProductID = 1 AND LocationID = 1

SELECT 'Before first transfer quantity of adjustable races at Miscellaneous Storage = ', Quantity FROM Production.ProductInventory
WHERE ProductID = 1 AND LocationID = 6

--Move 12 adjustable race parts (product id 1) from the Tool Crib (location id 1) 
--to Miscellaneous Storage (location id 6).
EXEC usp_ExecuteTransfer 1, 1, 6, 12

SELECT 'After first transfer quantity of adjustable races at Tool Crib = ', Quantity FROM Production.ProductInventory
WHERE ProductID = 1 AND LocationID = 1

SELECT 'After first transfer quantity of adjustable races at Miscellaneous Storage = ', Quantity FROM Production.ProductInventory
WHERE ProductID = 1 AND LocationID = 6


--Move them back
EXEC usp_ExecuteTransfer 1, 6, 1, 12

SELECT 'After second transfer quantity of adjustable races at Tool Crib = ', Quantity FROM Production.ProductInventory
WHERE ProductID = 1 AND LocationID = 1

SELECT 'After second transfer quantity of adjustable races at Miscellaneous Storage = ', Quantity FROM Production.ProductInventory
WHERE ProductID = 1 AND LocationID = 6

The following tsql removes the assembly and stored procedure from the database (Cleanup.sql).
USE AdventureWorks
GO

-- Drop existing sproc and assembly if any.

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

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

USE master
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'ExternalSample_Login')
DROP LOGIN ExternalSample_Login;
GO

IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'ExternalSample_Key')
DROP ASYMMETRIC KEY ExternalSample_Key;
GO

USE AdventureWorks
GO

The following Transact-SQL removes the audit database from the second instance

SET NOCOUNT OFF;
GO

PRINT CONVERT(varchar(1000), @@VERSION);
GO

PRINT '';
PRINT 'Started - ' + CONVERT(varchar, GETDATE(), 121);
GO

USE [master];
GO


-- ****************************************
-- Drop Database
-- ****************************************
PRINT '';
PRINT '*** Dropping Database';
GO

IF EXISTS (SELECT [name] FROM [master].[sys].[databases] WHERE [name] = N'InventoryAudit')
    DROP DATABASE [InventoryAudit];

-- If the database has any other open connections close the network connection.
IF @@ERROR = 3702 
    RAISERROR('[InventoryAudit] database cannot be dropped because there are still other open connections', 127, 127) WITH NOWAIT, LOG;
GO

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

SE AdventureWorks
GO

-- Drop existing sproc and assembly if any.

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

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

USE master
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'ExternalSample_Login')
DROP LOGIN ExternalSample_Login;
GO

IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'ExternalSample_Key')
DROP ASYMMETRIC KEY ExternalSample_Key;
GO

USE AdventureWorks
GO

Community Additions

ADD
Show:
© 2014 Microsoft