question

JPScerri avatar image
JPScerri asked

Using Extensions in SQL CLR Project

Hi I am writing some SQL CLR UDFs and want to use a private function as an extension, eg : Private Function Test(MyString as String) as String Return MyString + "(Test)" End Function When I try to use my extension function in the UDFs, eg: Dim zz as String = "ABC".Test() it reports an error saying the Test() is not a member of String() Is there a way to use Extension in SQL CLR code? Thanks JP
functionsclrudf
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The site works on voting. For all helpful answers please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution you can indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
cjperry1 avatar image
cjperry1 answered
Is your extension defined in the same class as the function you're trying to call? If it isn't, you'll have to make the extension public.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tried to make the extension function in the same class and also public, but still the same error.
0 Likes 0 ·
cjperry1 avatar image
cjperry1 answered
Here's some sample code to try (I'm using .NET 3.5): I have a SQL CLR project and it contains two .vb files (ignoring the VS generated items and folders). The first file is a module that contains the extensions: TestExtensions.vb Imports System.Runtime.CompilerServices Module TestExtensions Public Function Test(MyString as String) as String Return MyString + " (Test)" End Function End Module The second file contains the UDF: TestFunctions.vb Imports System.Data.SqlTypes Partial Public Class UserDefinedFunctions _ Public Shared Function TestFunctions(UserString as String) as SqlString Return New SqlString(UserString.Test()) End Function End Class That should be all you need. Once you deploy it to your server, you can test it like this: SELECT dbo.TestFunctions('Hello world'); Let me know if you need additional help!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi cjperry1 Thanks for your reply. I had already tried that, but no luck. I copied the same method and class to another Non CLR project and it worked fine. It seems that my CLR project is missing something. :( I am also using frameworks 3.5 and VS 2012. JP
0 Likes 0 ·
cjperry1 avatar image
cjperry1 answered
Here are the actual VS files for my sample. See if this project works for you. I used VS2010 but it shouldn't matter. [link text][1] [1]: /storage/temp/1071-sqlserverproject1.zip

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JPScerri avatar image
JPScerri answered
Hi Unfortunately it did not work. It is still not recognising the Extension attribute. I am using VS 2012. ![alt text][1] [1]: /storage/temp/1114-screenshot001.jpg

screenshot001.jpg (87.0 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.