Here is a little class that I whipped up to store IP Addresses in a strongly types fashion in SQL Server. As a general rules of thumb, I'm against UDTs in the database because once you deploy one, it's there forever. That said, because the IP Address class will almost certianly never change (and the underlying long type is NEVER going to change) it's a safe bet.
Historically we have stored IP Addresses in a char(15) column, but this requires a signifigant amount of validation to ensure proper formating. By delegating this validation to the underlying BCL we can ensure that IP Address data entered into our systems are always valid at the storage level.
There are some not implemented methods down at the bottom that most people will never use, so I didn't include them (they are pretty long). In a nutshell there are certain IP Addresses in the space that are private (10.... 192.168... etc) and there is are some address that are reserved (anything that ends in 255.255, addresses that start with 254 iirc). If anyone needs this additional code shoot me a short note from the contact page and I'll mail it to you when I find the most performant way to run it. Currently I'm using string comparisons, however I'm pretty sure that bit comparisons will be faster.
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Data.SqlTypes;
using
System.IO;
using
Net=System.Net;
using
System.Net.Sockets;
using
Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedType(Format.UserDefined,
MaxByteSize=sizeof(Int64))]
public
struct IPAddress
: INullable,
IBinarySerialize
{
#region Private
Members
private Net.IPAddress
_ipAddress;
#endregion
#region
Construction
private IPAddress(Net.IPAddress
ipAddress)
{
_ipAddress =
ipAddress;
}
#endregion
#region
INullable and SQL UDT Required Members
public override
String ToString()
{
return _ipAddress.ToString();
}
public Boolean
IsNull
{
get
{
if
(null == _ipAddress)
return true;
else return
false;
}
}
public static
IPAddress Null
{
get
{
IPAddress result =
new IPAddress();
return result;
}
}
public static
IPAddress Parse(SqlString
s)
{
if (s.IsNull) return
Null;
// create our result
Net.IPAddress
newIPAddress = null;
// attempt the parse
if (Net.IPAddress.TryParse(s.ToString(),
out newIPAddress))
{
IPAddress result =
new IPAddress(newIPAddress);
return result;
}
else { throw
new
ArgumentException("Invalid IPAddress
String", "s"); }
}
#endregion
#region
IBinarySerialize Members (Required for user defined serialization)
public void
Read(BinaryReader reader)
{
Int32 byteLength = (Int32)reader.BaseStream.Length;
Byte[] bytes = new
Byte[byteLength];
bytes =
reader.ReadBytes(byteLength);
_ipAddress =
new System.Net.IPAddress(bytes);
}
public void
Write(BinaryWriter writer)
{
writer.Write(_ipAddress.GetAddressBytes());
}
#endregion
#region
System.Net.IPAddress Public Member Wrappers
public override
Boolean Equals(object
obj)
{
if (obj is
IPAddress)
{
IPAddress ipAddress = (IPAddress)obj;
return _ipAddress.Equals(ipAddress);
}
return false;
}
public override
Int32 GetHashCode()
{
return _ipAddress.GetHashCode();
}
public
AddressFamily AddressFamily
{
get { return
_ipAddress.AddressFamily; }
}
public Boolean
IsIPv6LinkLocal
{
get { return
_ipAddress.IsIPv6LinkLocal; }
}
public Boolean
IsIPv6Multicast
{
get { return
_ipAddress.IsIPv6Multicast; }
}
public Boolean
IsIPv6SiteLocal
{
get { return
_ipAddress.IsIPv6SiteLocal; }
}
public Int64
ScopeId
{
get { return _ipAddress.ScopeId;
}
set { _ipAddress.ScopeId =
value; }
}
public Boolean
IsLoopback
{
get { return
Net.IPAddress.IsLoopback(_ipAddress); }
}
#endregion
#region TODO -
Need to talk to some network nerds about the best way to do this
public Boolean
IsPrivateNetwork
{
get
{
throw new
NotImplementedException();
}
}
public Boolean
IsReservedAddress
{
get { throw
new
NotImplementedException(); }
}
public Boolean
IsPublicNetwork
{
get { return !IsPrivateNetwork
&& !IsReservedAddress; }
}
#endregion
}