Converting C# Method Using UUIDNext Library to MSSQL Functions with ChatGPT

Converting code between different programming languages can be challenging, especially when the original code relies on specific libraries. In this article, we'll explore how to use ChatGPT to convert a C# method that utilizes the UUIDNext library into an equivalent MSSQL function. We'll provide code snippets and detailed explanations to guide you through the conversion process.

Introduction

Suppose you have a C# method that generates a Guid based on a namespace GUID and a tenant ID using the UUIDNext library. You need to replicate this functionality in MSSQL to ensure consistent GUIDs across your application and database. Manually translating the code can be complex due to language differences and library dependencies. This is where ChatGPT can assist, providing insights and generating code snippets to facilitate the conversion.

The C# Code Using UUIDNext Library

First, let's examine the original C# method that uses the UUIDNext library:

using UUIDNext;

public static Guid GetTenantGuid(long tenantId)
{
    return Uuid5.Generate(Guid.Parse("d2bc0b86-b0dc-4f69-91ea-0c5266b727b7"), tenantId.ToString());
}

Explanation:

• UUIDNext Library: A .NET library that provides functionality for generating different versions of UUIDs, including version 5 UUIDs (name-based UUIDs using SHA-1 hashing).
• Uuid5.Generate: Generates a version 5 UUID based on a namespace GUID and a name (in this case, the tenant ID converted to a string).
• Namespace GUID: "d2bc0b86-b0dc-4f69-91ea-0c5266b727b7" is used as the namespace for generating the UUID.

Understanding the Goal

Our objective is to create an MSSQL function that replicates the functionality of the above C# method:

• Input: A BIGINT tenant ID.
• Output: A UNIQUEIDENTIFIER (GUID) that is consistent with the one generated by the C# method using the UUIDNext library.
• Consistency: Ensuring the GUIDs generated in MSSQL match those from the C# application.

Using ChatGPT for Code Conversion

To convert the C# code to MSSQL, we'll use ChatGPT to guide us through the process. Here's the approach:

1. Describe the Task: Explain to ChatGPT what the C# method does and what we need in MSSQL.
2. Seek Assistance: Ask ChatGPT to help write an equivalent MSSQL function.
3. Iterate: Review the generated code, provide feedback, and request clarifications or adjustments as necessary.

The Converted MSSQL Functions

After utilizing ChatGPT's assistance, we arrive at the following MSSQL functions:

Helper Function: dbo.NvarcharToUtf8

First, we need a helper function to convert an NVARCHAR string to its UTF-8 VARBINARY representation:

CREATE FUNCTION dbo.NvarcharToUtf8 (@string NVARCHAR(MAX))
RETURNS VARBINARY(MAX)
AS
BEGIN
    DECLARE @utf8 VARBINARY(MAX) = 0x;
    DECLARE @i INT = 1;
    DECLARE @length INT = LEN(@string);
    DECLARE @codePoint INT;

    WHILE @i <= @length
    BEGIN
        SET @codePoint = UNICODE(SUBSTRING(@string, @i, 1));

        IF @codePoint <= 0x7F
            SET @utf8 = @utf8 + CAST(@codePoint AS BINARY(1));
        ELSE IF @codePoint <= 0x7FF
            SET @utf8 = @utf8 
                + CAST((0xC0 | (@codePoint / 0x40)) AS BINARY(1))
                + CAST((0x80 | (@codePoint & 0x3F)) AS BINARY(1));
        ELSE IF @codePoint <= 0xFFFF
            SET @utf8 = @utf8 
                + CAST((0xE0 | (@codePoint / 0x1000)) AS BINARY(1))
                + CAST((0x80 | ((@codePoint / 0x40) & 0x3F)) AS BINARY(1))
                + CAST((0x80 | (@codePoint & 0x3F)) AS BINARY(1));
        ELSE
            SET @utf8 = @utf8 
                + CAST((0xF0 | (@codePoint / 0x40000)) AS BINARY(1))
                + CAST((0x80 | ((@codePoint / 0x1000) & 0x3F)) AS BINARY(1))
                + CAST((0x80 | ((@codePoint / 0x40) & 0x3F)) AS BINARY(1))
                + CAST((0x80 | (@codePoint & 0x3F)) AS BINARY(1));

        SET @i = @i + 1;
    END

    RETURN @utf8;
END;

• Purpose: Converts an NVARCHAR string to its UTF-8 encoded VARBINARY representation.
• UTF-8 Encoding: Handles Unicode code points, including characters beyond the Basic Multilingual Plane (BMP).
• Usage: Necessary because the SHA-1 hashing function operates on bytes, and we need to ensure the same byte sequence is used as in the C# implementation.

Main Function: dbo.FromTenantId

Next, we create the main function that generates the GUID:

CREATE FUNCTION dbo.FromTenantId (@tenantId BIGINT)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
    DECLARE @namespace UNIQUEIDENTIFIER = 'd2bc0b86-b0dc-4f69-91ea-0c5266b727b7';
    DECLARE @namespace_bin VARBINARY(16) = CAST(@namespace AS VARBINARY(16));
    DECLARE @namespace_bytes VARBINARY(16);
    DECLARE @name NVARCHAR(50);
    DECLARE @name_bytes VARBINARY(MAX);
    DECLARE @hash VARBINARY(20);
    DECLARE @guid BINARY(16);
    DECLARE @guid_sql_server_format BINARY(16);

    -- Convert tenantId to string
    SET @name = CAST(@tenantId AS NVARCHAR(50));

    -- Convert @name to UTF-8 VARBINARY
    SET @name_bytes = dbo.NvarcharToUtf8(@name);

    -- Adjust the byte order of the namespace GUID to big-endian format
    SET @namespace_bytes =
        SUBSTRING(@namespace_bin, 4, 1) +
        SUBSTRING(@namespace_bin, 3, 1) +
        SUBSTRING(@namespace_bin, 2, 1) +
        SUBSTRING(@namespace_bin, 1, 1) +
        SUBSTRING(@namespace_bin, 6, 1) +
        SUBSTRING(@namespace_bin, 5, 1) +
        SUBSTRING(@namespace_bin, 8, 1) +
        SUBSTRING(@namespace_bin, 7, 1) +
        SUBSTRING(@namespace_bin, 9, 8);

    -- Hash the namespace GUID and the name (tenant ID) using SHA1
    SET @hash = HASHBYTES('SHA1', @namespace_bytes + @name_bytes);

    -- Use the first 16 bytes of the hash for the GUID
    SET @guid = SUBSTRING(@hash, 1, 16);

    -- Set the version to 5 (bits 12-15 of the 7th byte)
    SET @guid =
        SUBSTRING(@guid, 1, 6) +
        CAST(
            (CAST(SUBSTRING(@guid, 7, 1) AS TINYINT) & 0x0F) | 0x50
            AS BINARY(1)
        ) +
        SUBSTRING(@guid, 8, 9);

    -- Set the variant (bits 6-7 of the 9th byte to binary 10)
    SET @guid =
        SUBSTRING(@guid, 1, 8) +
        CAST(
            (CAST(SUBSTRING(@guid, 9, 1) AS TINYINT) & 0x3F) | 0x80
            AS BINARY(1)
        ) +
        SUBSTRING(@guid, 10, 7);

    -- Rearrange the GUID bytes to match SQL Server's uniqueidentifier format
    SET @guid_sql_server_format =
        SUBSTRING(@guid, 4, 1) +
        SUBSTRING(@guid, 3, 1) +
        SUBSTRING(@guid, 2, 1) +
        SUBSTRING(@guid, 1, 1) +
        SUBSTRING(@guid, 6, 1) +
        SUBSTRING(@guid, 5, 1) +
        SUBSTRING(@guid, 8, 1) +
        SUBSTRING(@guid, 7, 1) +
        SUBSTRING(@guid, 9, 8);

    -- Convert the rearranged bytes to UNIQUEIDENTIFIER
    RETURN CONVERT(UNIQUEIDENTIFIER, @guid_sql_server_format);
END;

• Namespace GUID: We use the same namespace GUID as in the C# code.
• Name Conversion: The tenant ID is converted to a string and then to UTF-8 bytes.
• Byte Order Adjustment: We adjust the byte order of the namespace GUID and the final GUID to ensure consistency with the UUID specification and SQL Server's storage format.
• SHA-1 Hashing: We compute the SHA-1 hash of the concatenated namespace and name bytes.
• Version and Variant Setting: We modify specific bits to set the UUID version to 5 and the variant to RFC 4122.
• Result: The function returns a UNIQUEIDENTIFIER that matches the GUID generated by the C# method.

Explaining the Conversion Steps

Let's delve into the critical steps of the conversion:

Byte Order Adjustment

SQL Server stores GUIDs in a unique format where certain parts of the GUID are stored in little-endian format. However, the UUID specification and the UUIDNext library use big-endian format. To match the GUIDs generated by the C# code, we need to adjust the byte order:

• Namespace GUID:
  • Rearranged to big-endian before hashing.
  • Ensures the bytes fed into the SHA-1 hash match those used in the C# implementation.
• Final GUID:
  • After setting the version and variant, rearranged back to match SQL Server's format for UNIQUEIDENTIFIER.

Setting the Version and Variant

In a UUID:

• Version (UUID Version 5):
  • Set in the most significant 4 bits of the 7th byte.
  • We mask the 7th byte with 0x0F and then set the version bits using 0x50 (where 5 represents the version and 0x50 sets the version bits appropriately).
• Variant (RFC 4122):
  • Set in the most significant 2 bits of the 9th byte.
  • We mask the 9th byte with 0x3F and then set the variant bits using 0x80.

SHA-1 Hashing

• Hash Input:
  • Concatenation of the namespace bytes and the UTF-8 bytes of the name (tenant ID).
• Hash Function:
  • SHA-1 hashing is used, consistent with UUID version 5 specifications.
• Hash Output:
  • The first 16 bytes of the SHA-1 hash are used to construct the GUID.

UTF-8 Encoding

• Why UTF-8:
  • The UUID specification requires that the name (tenant ID) be encoded in UTF-8 before hashing.
• Conversion:
  • The helper function dbo.NvarcharToUtf8 handles the conversion, ensuring that the bytes used in the hash match those from the C# implementation.

Using ChatGPT Effectively

When using ChatGPT for code conversion:

• Provide Clear Context: Mention the specific libraries used (e.g., UUIDNext) and the functionality required.
• Ask Specific Questions: If you're unsure about aspects like byte order or encoding, ask ChatGPT for clarification.
• Iterative Approach: Review the generated code, test it, and ask for refinements if necessary.
• Verification: Always test the final code to ensure it produces the expected results.

Testing the Functions

To ensure that the MSSQL function generates the same GUIDs as the C# method, you can perform the following tests:

C# Test Code

using System;
using UUIDNext;

class Program
{
    static void Main()
    {
        long tenantId = 12345;
        Guid guid = GetTenantGuid(tenantId);
        Console.WriteLine("C# GUID: " + guid);
    }

    public static Guid GetTenantGuid(long tenantId)
    {
        return Uuid5.Generate(Guid.Parse("d2bc0b86-b0dc-4f69-91ea-0c5266b727b7"), tenantId.ToString());
    }
}

MSSQL Test Query

DECLARE @tenantId BIGINT = 12345;
SELECT dbo.FromTenantId(@tenantId) AS [MSSQL GUID];

Comparing Results

• Run the C# test code to get the GUID.
• Run the MSSQL test query to get the GUID from the database.
• Compare the two GUIDs to ensure they match.

Conclusion

By leveraging ChatGPT, we successfully converted a C# method that uses the UUIDNext library into an MSSQL function that generates consistent GUIDs based on a tenant ID. This approach simplifies the code conversion process and helps maintain consistency across different parts of your application.