.NET Stored Procedure in Yukon for Web Search in .NET

Here is how to write a .NET Stored Procedure in Yukon using c#:

• Reference the appropiate assemblies.  The two biggies are Microsoft.VisualStudio.DataTools.SqlAttributes and sqlaccess.
• Reference the appropiate namespaces within your class file.  The ones that I referenced are System.Data.Sql and System.Data.SqlServer.
• Note that the method you want to call as a stored proc should have the attribute SqlProcedure and should be public.
• All methods that are touched must be static (c#) / shared (at least I think it is shared in vb.net).
• Perform the CREATE ASSEMBLY command:
  CREATE ASSEMBLY dbWebSearch FROM 'path to\dbWebSearch.dll'  //Note that my dll also needed WITH PERMISSION_SET = UNSAFE
• Perform the CREATE PROCEDURE command:
  CREATE PROCEDURE sp_Add_URL_DOTNET( @Url as nvarcahr(4000) ) AS EXTERNAL NAME dbWebSearch:[dbWebSearch.cSqlServer]::sp_Add_URL_DOTNET
• Here is a section of my code:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Diagnostics;
namespace dbWebSearch
{
public class cSqlServer
{
public cSqlServer()
{
}
[SqlProcedure] public static void sp_Add_URL_DOTNET(string pstrUrl)
{
SqlCommand sqlCm = SqlContext.GetCommand();
string strSql;
string strDomainName = CalculateDomainName(pstrUrl);  //method ommitted for brevity.
string strSearchCode = CalculateSearchCode(pstrUrl); //method ommitted for brevity
try
{
strSql = "select count(*) from tblSearchUrl where UrlAddress='" + SqlEscape(pstrUrl) + "' and DomainName='" + strDomainName + "' and " + "SearchCode=" + strSearchCode;
sqlCm.CommandText = strSql;
if ( Convert.ToInt32(sqlCm.ExecuteScalar()) == 0 ) {
strSql = "insert into tblSearchUrl (UrlAddress, UrlStatus, DomainName, SearchCode ) values (" + "'" + pstrUrl + "', 'NEW_URL', '" + SqlEscape(strDomainName) + "', " + strSearchCode + ")";
sqlCm.CommandText = strSql;
sqlCm.ExecuteNonQuery();
}
}
catch (System.Exception sysExc)  //Yes it is bad form to catch a System.Exception, but it works for this and is fairly simple.
{
EventLog.WriteEntry("dbWebSearch", "Error Message: " + sysExc.Message, EventLogEntryType.Information);
}
finally
{
sqlCm.Dispose();
}
}
}

Now, before you say that I shouldn't say anything about this, just in case there is any confusion on this, please check this link.

Enjoy,
Wally