Today's mission has been to write a stored procedure that does a reverse DNS on various IP addresses that I need to process. My stored proc is very simple and looks like the following -
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SpProcessIP(SqlString ipaddr)
{
string ourip = ipaddr.Value;
// first lookup if we have a resolved ip or not
SqlConnection sconn = new SqlConnection("context connection=true");
sconn.Open();
string stringstrsql = "SELECT COUNT(*) FROM [IP Log] WHERE IP='" + ourip + "'";
SqlCommand sc = new SqlCommand(stringstrsql,sconn);
sc.CommandType = CommandType.Text;
bool haveresolved = Convert.ToInt32(sc.ExecuteScalar().ToString())==1;
sc.Dispose();
if (!haveresolved)
{
string ourname = ourip;
IPHostEntry i = Dns.GetHostEntry(ourip);
ourname = i.HostName;
stringstrsql = "INSERT INTO ";
stringstrsql += "[IP Log]";
stringstrsql += "(";
stringstrsql += " IP";
stringstrsql += ",[Resolved To]";
stringstrsql += ")";
stringstrsql += "VALUES ";
stringstrsql += "(";
stringstrsql += " '"+ourip+"'";
stringstrsql += ",'" + ourname + "'";
stringstrsql += ")";
sc = new SqlCommand(stringstrsql, sconn);
sc.CommandType = CommandType.Text;
sc.ExecuteNonQuery();
sc.Dispose();
}
sconn.Close();
sconn.Dispose();
}
}
However to get it installed in SQL server is a bit of a fiddle.
Firstly you have to from the project properties in Visual Studio you have to switch on, that the sproc has external access.
See below.
I then built the project, copied the compiled DLL onto the SQL Server and ran the following (on the master database) -
CREATE ASYMMETRIC KEY LogFileDbComponentsManagedKey FROM
EXECUTABLE FILE = 'C:\components\LogFileDbComponentsManaged.dll' CREATE LOGIN dbo
FROM ASYMMETRIC KEY LogFileDbComponentsManagedKey
GRANT EXTERNAL ACCESS ASSEMBLY TO dbo
From this point on, its plain sailing, you can then deploy from Visual Studio and use the stored procedure quite happily.