Convert VB CLR function to C#

Question

How can I convert below VB.net SQL CLR function to C# CLR Function for a VS SSDT SQL Project?

I need to convert it to C# as it seems that built-in SSDT Project support for CLR functions is C# only? If I could find a way to use the original VB in a way that's compatible with VS SSDT projects and won't break Publish workflow, then I would accept that answer! I found it really easy to compile the .dll into SQL server using T-SQL commands in SSMS... but as soon as I brought it into our SSDT project to incorporate into our next .dacpac release, nothing worked!

SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
Public Shared Function RegExOptionEnumeration(ByVal IgnoreCase As SqlBoolean, _
             ByVal MultiLine As SqlBoolean, _
                           ByVal ExplicitCapture As SqlBoolean, _
                           ByVal Compiled As SqlBoolean, _
                           ByVal SingleLine As SqlBoolean, _
                           ByVal IgnorePatternWhitespace As SqlBoolean, _
                           ByVal RightToLeft As SqlBoolean, _
                           ByVal ECMAScript As SqlBoolean, _
                           ByVal CultureInvariant As SqlBoolean) _
              As SqlInt32
  Dim Result As Integer
  Result = (IIf(IgnoreCase.Value, RegexOptions.IgnoreCase, RegexOptions.None) Or _
   IIf(MultiLine.Value, RegexOptions.Multiline, RegexOptions.None) Or _
   IIf(ExplicitCapture.Value, RegexOptions.ExplicitCapture, _
                RegexOptions.None) Or _
   IIf(Compiled.Value, RegexOptions.Compiled, RegexOptions.None) Or _
   IIf(SingleLine.Value, RegexOptions.Singleline, RegexOptions.None) Or _
   IIf(IgnorePatternWhitespace.Value, RegexOptions.IgnorePatternWhitespace, _
                RegexOptions.None) Or _
   IIf(RightToLeft.Value, RegexOptions.RightToLeft, RegexOptions.None) Or _
   IIf(ECMAScript.Value, RegexOptions.ECMAScript, RegexOptions.None) Or _
   IIf(CultureInvariant.Value, RegexOptions.CultureInvariant, RegexOptions.None))
  Return (Result)

I have attempted to run the Class through Telerik C# Converter.

Telerik produces below C#:

public class RegularExpressionFunctions
    {
        // 
        // RegExOptions function
        // this is used simply to create the bitmap that is passed to the various 
        // CLR routines

        [SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlInt32 RegExOptionEnumeration(SqlBoolean IgnoreCase, SqlBoolean MultiLine, SqlBoolean ExplicitCapture, SqlBoolean Compiled, SqlBoolean SingleLine, SqlBoolean IgnorePatternWhitespace, SqlBoolean RightToLeft, SqlBoolean ECMAScript, SqlBoolean CultureInvariant)
        {
            int Result;
            Result = (Interaction.IIf(IgnoreCase.Value, RegexOptions.IgnoreCase, RegexOptions.None) | Interaction.IIf(MultiLine.Value, RegexOptions.Multiline, RegexOptions.None) | Interaction.IIf(ExplicitCapture.Value, RegexOptions.ExplicitCapture, RegexOptions.None) | Interaction.IIf(Compiled.Value, RegexOptions.Compiled, RegexOptions.None) | Interaction.IIf(SingleLine.Value, RegexOptions.Singleline, RegexOptions.None) | Interaction.IIf(IgnorePatternWhitespace.Value, RegexOptions.IgnorePatternWhitespace, RegexOptions.None) | Interaction.IIf(RightToLeft.Value, RegexOptions.RightToLeft, RegexOptions.None) | Interaction.IIf(ECMAScript.Value, RegexOptions.ECMAScript, RegexOptions.None) | Interaction.IIf(CultureInvariant.Value, RegexOptions.CultureInvariant, RegexOptions.None));
            return (Result);
        }
}

All other functions in the Class seem to convert OK with the Telerik converter. I tried searching around for this and it seems that IIf is similar to ternary operators. On this basis, I have tried the following 2 examples, but nothing works...

Attempt 1

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlInt32 RegExOptionEnumeration(SqlBoolean IgnoreCase, SqlBoolean MultiLine, SqlBoolean ExplicitCapture, SqlBoolean Compiled, SqlBoolean SingleLine, SqlBoolean IgnorePatternWhitespace, SqlBoolean RightToLeft, SqlBoolean ECMAScript, SqlBoolean CultureInvariant)
    {
        int Result;
        Result = 
            (IgnoreCase.Value == true ? RegexOptions.IgnoreCase : RegexOptions.None) ||
            (MultiLine.Value ? RegexOptions.Multiline : RegexOptions.None) ||
            (ExplicitCapture.Value ? RegexOptions.ExplicitCapture : RegexOptions.None) || 
            (Compiled.Value ? RegexOptions.Compiled : RegexOptions.None) || 
            (SingleLine.Value ? RegexOptions.Singleline : RegexOptions.None) || 
            (IgnorePatternWhitespace.Value ? RegexOptions.IgnorePatternWhitespace : RegexOptions.None) || 
            (RightToLeft.Value ? RegexOptions.RightToLeft : RegexOptions.None) || 
            (ECMAScript.Value ? RegexOptions.ECMAScript : RegexOptions.None) || 
            (CultureInvariant.Value ? RegexOptions.CultureInvariant : RegexOptions.None);
        return (Result);
    }

Attempt 2

public static object Iif(bool cond, object left, object right)
    {
        return cond ? left : right;
    }

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlInt32 RegExOptionEnumeration(SqlBoolean IgnoreCase, SqlBoolean MultiLine, SqlBoolean ExplicitCapture, SqlBoolean Compiled, SqlBoolean SingleLine, SqlBoolean IgnorePatternWhitespace, SqlBoolean RightToLeft, SqlBoolean ECMAScript, SqlBoolean CultureInvariant)
    {
        int Result;
        Result = Iif(IgnoreCase.Value, RegexOptions.IgnoreCase, RegexOptions.None) || Iif(MultiLine.Value, RegexOptions.Multiline, RegexOptions.None) || Iif(ExplicitCapture.Value, RegexOptions.ExplicitCapture, RegexOptions.None) || Iif(Compiled.Value, RegexOptions.Compiled, RegexOptions.None) || Iif(SingleLine.Value, RegexOptions.Singleline, RegexOptions.None) || Iif(IgnorePatternWhitespace.Value, RegexOptions.IgnorePatternWhitespace, RegexOptions.None) || Iif(RightToLeft.Value, RegexOptions.RightToLeft, RegexOptions.None) || Iif(ECMAScript.Value, RegexOptions.ECMAScript, RegexOptions.None) || Iif(CultureInvariant.Value, RegexOptions.CultureInvariant, RegexOptions.None);
        return (Result);
    }

in your version you are doing || which is logical or, but you want bitwise or | as it is turning the flags into bitfields which are stored in an int — Keith Nicholas, Apr 16 '21 at 01:48

Keith Nicholas · Accepted Answer · 2021-04-16T02:02:04.910

The function is building an int with the bitfields of the regex options (which are designed in such a way they can be combined together). So I'd write a utility function and do it something like :-

    static int BitIf(SqlBoolean condition, RegexOptions flag) => condition.Value ? (int) flag : 0;

    static SqlInt32 RegExOptionEnumeration(SqlBoolean IgnoreCase, SqlBoolean MultiLine, SqlBoolean ExplicitCapture, SqlBoolean Compiled, SqlBoolean SingleLine, SqlBoolean IgnorePatternWhitespace, SqlBoolean RightToLeft, SqlBoolean ECMAScript, SqlBoolean CultureInvariant)
    {
            return BitIf(IgnoreCase, RegexOptions.IgnoreCase)
            | BitIf(MultiLine, RegexOptions.Multiline)
            | BitIf(ExplicitCapture, RegexOptions.ExplicitCapture)
            | BitIf(Compiled, RegexOptions.Compiled)
            | BitIf(SingleLine, RegexOptions.Singleline)
            | BitIf(IgnorePatternWhitespace, RegexOptions.IgnorePatternWhitespace)
            | BitIf(RightToLeft, RegexOptions.RightToLeft)
            | BitIf(ECMAScript, RegexOptions.ECMAScript)
            | BitIf(CultureInvariant, RegexOptions.CultureInvariant);
    }
}

Solomon Rutzky · Answer 2 · 2021-04-16T02:36:28.937

While the wording on the linked documentation page is admittedly misleading, it doesn't state that VB.NET isn't supported. Have you tried creating a VB.NET project? What is "broken" about the publishing? I don't use VB.NET so I haven't tried it, but I also haven't heard / seen anyone ever make this claim in the past 16 years (since SQLCLR was introduced and people have been using mostly C#, but also definitely VB.NET, and to a lesser degree Visual C++, and on occasion, and not without some pain, F#).

but as soon as I brought it into our SSDT project to incorporate into our next .dacpac release, nothing worked!

What does this have to do with the initial language it was written in? The DLL is in the same byte-code that it would be even if it was initially written in C#, right? What version of SQL Server are you using? Most likely this is a security issue, not a language issue.

Also, what is the "it" that you brought into the SSDT project? The original code or the assembly since it had already been loaded into SQL Server? This could be an SSDT configuration issue.

In either case, if you received errors when using the VB.NET code, start by posting those errors and confirming that the issue is indeed the language instead of many other things that it could be. You might be doing a whole lot of extra work for nothing, all based on a bad assumption. Assuming you did get an error with a message number and/or text, did you look either of those up? Again, please post those to the question since it is also just as likely that you run into that same problem again, even with working C# code.

Also, just to put this out there, if you want RegEx functions, you can be done already simply by installing the SQL# (SQLsharp) library (that I wrote). The Free version contains most of the RegEx functions, and they are a fairly complete representation of what you can do in .NET with RegEx (including passing in the RegExOptions): Match, Matches, Split, Replace, CaptureGroups, CaptureGroupCaptures, etc.

Hi. Thanks for the input, I will check out your library. I tried all sorts - bringing in the assembly and adding a reference, bringing in the code to a project in the solution and referencing the project. Maybe it’s a config issue as you say, but one thing I noticed is that if I add a new item to the solution and search for CLR, all of the items are C# based. I can keep trying but I’m a bit more comfortable with C# than I am at VB (though admittedly not great at C# either!) — Adam, Apr 16 '21 at 08:10
@Adam When you installed Visual Studio, did you install VB.NET support? I haven't installed it in a while, but I thought you had to select the languages you wanted to work with, such as C#, VB.NET, VC++, and years ago J# was an option. Or am I mis-remembering? — Solomon Rutzky, Apr 16 '21 at 19:08
thanks your suggestions eventually led me to finding an answer. The issue wasn't at the installer level but at the project properties level. I have added an answer with the steps needed to solve. — Adam, Apr 20 '21 at 12:18

score 0 · Answer 3 · answered Apr 20 '21 at 12:17

I found a way to use the VB script without converting to C#

Project Properties > SQL CLR > Language

Once this is complete, all items tagged CLR are available using VB flavour:

It seems that SSDT DB Project can use C# CLR or VB CLR but not both (even though both is possible in SQL Server).

Convert VB CLR function to C#

3 Answers3