asked on

How can I edit CLR SQL Server User-Defined Function

Hello, Help!!

On my SQL server I have a couple of 'CLR SQL Server User-Defined Functions' that were written on an old PC. the Visual Studio project that created these CLR functions, has gone.

I need to make changes to the functions, as one of the things that it does is remove line breaks, I really need to replace line breaks with a space...

So my question is this, how on SSMS or Visual Studio 2008, can I link to and edit these CLR functions?

I'm guessing this is more a visual studio question than a SSMS question?

Please advise.

Thanks

Guy Hengel [angelIII / a3]

you cannot "edit" the CLR functions without the original c# / vb.net code.

you may be able to get the reverse engineered code from the assembly:
http://reflector.red-gate.com/download.aspx

to find the assembly:
http://serverfault.com/questions/139703/extracting-a-sqlclr-assembly

garethtnash

ASKER

Hello AngelIII,

Thanks,

Still a little confused though..

Using this -

select * from sys.assembly_modules I can see

object_id      assembly_id      assembly_class      assembly_method      null_on_null_input      execute_as_principal_id
1931153925      65545      UserDefinedFunctions      CleanHTML      0      NULL
1947153982      65545      UserDefinedFunctions      CleanHTMLAtt      0      NULL

It is CLEANHTML and CleanHTMLAtt, that i want to recode.

Thanks

garethtnash

ASKER

Hello again,

So I've just discovered, in Visual Studio on the server, if I create a new project and connect to the database, I can open functions and see both of the CLR functions, if I double click one of these I get -

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Text.RegularExpressions;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlString CleanHTML(SqlString s)
    {
        if (s.IsNull) return String.Empty;
        string s1 = s.ToString().Trim();
        if (s1.Length == 0) return String.Empty;
        StringBuilder tmpS = new StringBuilder(s1.Length);
        //striping out the "control characters"
        if (!Char.IsControl(s1[0])) tmpS.Append(s1[0]);
        for (int i = 1; i <= s1.Length - 1; i++)
        {
            if (Char.IsControl(s1[i]))
            {
                if (s1[i - 1] != ' ') tmpS.Append(' ');
            }
            else
            {
                tmpS.Append(s1[i]);
            }
        }
        string result = tmpS.ToString();
        //finding the HTML tags and replacing them with an empty string
        string pattern = @"<[^>]*?>|<[^>]*>";

        Regex rgx = new Regex(pattern);
        result = rgx.Replace(result, String.Empty);
        result = result.Replace(@"&amp;amp;", @"&amp;");
        result = result.Replace(@"&amp;", @"&");
        result = result.Replace(@"&pound;", @"£");
        result = result.Replace(@"&#163;", @"£");
        result = result.Replace(@"&quot;", @"""");
        result = result.Replace(@"&apos;", @"'");
        result = result.Replace(@"&", @"&amp;");
        result = result.Replace(@"£", @"&pound;");
        result = result.Replace(@"""", @"&quot;");
        result = result.Replace(@"'", @"&apos;");
        //more replaces here
        return result;
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlString CleanHTMLAtt(SqlString s)
    {
        string result = s.ToString();

        //keep all HTML Entities but remove attributes 
        string pattern = @"(<[a-z][^ >/]*)(?:[^>/]|/(?!>))*";
        Regex rgx = new Regex(pattern);
        result = rgx.Replace(result, "$1");

        //If the input has already been HTML encoded or double encoded, unencode it -
        result = result.Replace("&amp;amp;", "&amp;");// removes all ... (tab etc)
        result = result.Replace("&amp;", "&");// removes all ... (tab etc)
        result = result.Replace("&nbsp;", " ");// removes all ... (tab etc)
        result = result.Replace("&pound;", "£");
        result = result.Replace("&#163;", "£");
        result = result.Replace("&apos;", "'");// removes all ... (tab etc)
        result = result.Replace("&quot;", @"""");// removes all ... (tab etc)

        //Remove tabs, double spaces etc -
        result = result.Replace("\t", " ");// replace tabs with a space 
        result = result.Replace("  ", " ");// replace tabs with a space 

        //Replace line breaks with < /br>
        pattern = "\r\n|\r|\n";
        rgx = new Regex(pattern);
        result = rgx.Replace(result, "<br />");// replace any carriage returns with either <br />

        //Once the string is clean, encode special characters
        result = result.Replace("&", "&amp;");
        result = result.Replace("£", "&pound;");
        result = result.Replace(@"""", "&quot;");
        result = result.Replace("'", "&apos;");
        result = result.Replace("&amp;bull;", "&bull;");

        return result;
    }
}

Open in new window

it opens in a tab (with a blue padlock) named Fuction1.cs

This looks to me as if it creates 2 CLR functions? Anything I can do with this?

Thanks AngelIII

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

Thanks AngelIII

One question that has always... confused me.

Should I be HTML Encoding the data I store on the database, or just HTML encoding it when i write it to the HTML page?

Thanks

Guy Hengel [angelIII / a3]

you can do it at whatever level you want, but not without "user code", but with the .NET framework functions:
http://msdn.microsoft.com/en-us/library/system.web.httputility.htmlencode.aspx

garethtnash

ASKER

Sorry for the late vote, been out of the office