SQL Server 2005: Creating Your First C# CLR UDF in 10 Easy Steps (One of Which Includes Partying)

Well, 10 steps, give or take. This could have been executed in fewer steps had we not chosen such a useful function as an example <G>. This function, which is only supported in SQL Server 2005 with CLR enabled, esentially performs a reverse IP lookup (nslookup -type=ptr), uses System.Net.Dns.GetHostEntry and thus requires a bit more security configuration. So, let’s begin.

1. In VS 2005, click File -> New -> Project -> Visual C# -> Database -> SQL Server Project -> OK.
2. Enter SQL Server name, credentials, and database name -> OK.
a. Accept the prompt which asks you if you want to enable CLR debugging in the database
3. In Solution Explorer, right click on the name of your SQL Server Project -> Add -> User Defined Function -> save it as “IPtoHostname.cs” (this will be the name of your function)
4. Copy and paste the following code into the IPtoHostname.cs window:

IPtoHostname.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static System.Data.SqlTypes.SqlString IPtoHostName(string ipaddr) // this is like T-SQL's CREATE FUNCTION dbo.IPtoHostName  (@strCCode varchar(xx))
    {
        try // This basically means "If the IP resolves properly and doesn't throw an exception, then..."
        {
            string myIP = System.Net.Dns.GetHostEntry(ipAddress.Trim()).HostName.ToString();
            return new SqlString(myIP);
        }
        catch // "If it doesn't, just return the IP address"
        {
            return new SqlString(ipaddr);
        }
    }
};

Note: Again, because we use System.Net, we need to set some permissions which weakens the security of the given database. I haven’t researched the implications of doing so but I figure if I’m going to run CLR stuff in my database, I’m willing to bend a little on security anyway.

5. Save your work. In Solution Explorer, right click on then name of your SQL Server Project and click Properties. Change the Permission Level to External.
6. Open SQL Server Management Studio and issue the following command, using the name of the database you selected in step 2.

EXEC sp_configure 'CLR ENABLED' , '1'
GO
RECONFIGURE
GO
ALTER DATABASE MyDBFromStep2 SET TRUSTWORTHY ON
GO

Note: In order to enable CLR or set the TRUSTWORTHYness of a database, it’s likely that you must be a sys admin. And this is mostly for keywords to help Googlers… if you do not do the above, you will run into an error similiar to the following:

System.Security.SecurityException…

A .NET Framework error occurred during execution of user defined routine or aggregate 'IPtoHostName':
System.Security.SecurityException: Request for the permission of type 'System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
   at System.Security.CodeAccessPermission.Demand()
   at System.Net.Dns.GetHostEntry(String hostNameOrAddress)
   at UserDefinedFunctions.IPtoHostName(String ipaddr)

7. Next, go back to Visual Studio, and select the “Debug” dropdown. Change that to “Release.”
8. Save. Hit F5. You should have a successful build.
9. In SQL Management Studio, Query your database with the following statement:

select dbo.IPtoHostName(’24.0.175.222′) as hostname

See “c-24-0-175-222.hsd1.tx.comcast.net”
10. Laissez les bon temps rouler! (Party!)

Posted in SQL Server
6 comments on “SQL Server 2005: Creating Your First C# CLR UDF in 10 Easy Steps (One of Which Includes Partying)
  1. Md.Amir Hossain says:

    Thanks a lot for such a example. Its really a good example for understand the process of CLR integration. Thanks again.

  2. Tracy Isbell says:

    Very helpful information. I searched for a long time and was having difficulty piecing it all together. Thanks a bunch.

  3. SAF says:

    Hi,
    I did not get the prompt to enable CLR. Also I get the error “Error 1 Execution of .NET Framework code is disabled. Set “clr enabled” configuration option and restart the server.”

    Could you please help me?

    Regards
    Ansaf

  4. Troy Howard says:

    Thanks so much for this example.. I found my way here after getting to the permissions exceptions, and this explained nicely how to fix that. I’m actually solving the exact same problem, so that we can track the client machine hostname in our ISA web proxy log.

    One minor modification to this code would be to add .Trim() to the ipaddr parameter. I noticed a number of the ips failing to resolve when I first ran this on real data. That’s because some of them had trailing spaces in the string. If you pass an untrimmed string to Dns.GetHostEntry, it will fail to resolve.

    So just change:

    string myIP = System.Net.Dns.GetHostEntry(ipAddress).HostName.ToString();

    to

    string myIP = System.Net.Dns.GetHostEntry(ipAddress.Trim()).HostName.ToString();

    and it all works peachy.

    Thanks,
    Troy

  5. Chrissy says:

    Thanks, Troy! Updated.

  6. pankaj mahajan says:

    mast hai bhai.It is a excellent feature when we have our own server else it will fails.

1 Pings/Trackbacks for "SQL Server 2005: Creating Your First C# CLR UDF in 10 Easy Steps (One of Which Includes Partying)"
  1. [...] go deep into step of registering this function in SQL Server because it was described many times on many places. However, this function is manipulating with files, some security changes must be done to your [...]

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">