Home » Sql » How to Use Third Party Dll Reference in a SQL CLR Function

How to Use Third Party Dll Reference in a SQL CLR Function

In a previous article, I was using a SQL CLR function written in C# to be executed by SQL server during a migration of account. The reason was to use the hashing code from Asp.Net Identity that is used by Entity Framework and Asp.Net. In that article, instead of using a copy of the code of the real implementation, we are going to use the real Asp.Net Identity dll. Our C# Dll will just be a proxy to the third party.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString HashPassword2(string plainPassword)
{
   var hasher = new Microsoft.AspNet.Identity.PasswordHasher();
   return hasher.HashPassword(plainPassword);
}

The code is way simpler, and reuse the Microsoft Asp.Net code which is interesting. The problem is that SQL project does not allow to use NuGet. It’s not possible to get the reference from a NuGet package. So, we need to select the DLL from the browse window of the reference. The simple way to do it is to select the one in the Bin folder of you Asp.Net MVC project. Once done, we still need to do something to allow SQL to read it. If you do not, it will compile but not publish.

Creating [MySqlToMsSql]...
(63,1): SQL72014: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'microsoft.aspnet.identity.core, version=2.0.0.0, culture=neutral, publickeytoken=31bf3856ad364e35.' was not found in the SQL catalog.
(63,0): SQL72045: Script execution error.  The executed script:
CREATE ASSEMBLY [MySqlToMsSql]
    AUTHORIZATION [dbo]
    FROM 0x4D5A9000...
An error occurred while the batch was being executed.

To fix that issue, we need to go in the property of the third-party DLL added to the project.

PropertyOfThirdParty

The property about Model Aware, Is Visible and Generate Sql Script must be set to true.

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

One Response so far.

  1. Gousepeer Shaik says:

    I do it(Model Aware, Is Visible and Generate Sql Script set to true).But I got the same problem

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.