Code

The IP Address Table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_IPAddress_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_IPAddress_Insert]
GO

CREATE PROCEDURE p_IPAddress_Insert
/******************************************************************************
 *  file:   p_IPAddress_Insert.sql
 *  date:   2024.02.08 T 17:03:08
******************************************************************************/

	 @Id              int	 OUTPUT
	,@IpAddress       varchar(32)
	,@CountryCode     char(2)
	,@City            varchar(60)
	,@State           varchar(60)
	,@Postal          varchar(32)
	,@Latitude        decimal(7,4)
	,@Longitude       decimal(7,4)
AS

	IF NOT EXISTS (
		SELECT
			Id
		FROM
			IPAddress
		WHERE
			IpAddress = @IpAddress )
		BEGIN

			-- when dealing with addresses, make sure we handle overflows...
			SET @City   = SUBSTRING(@City,   1, 60)
			SET @State  = SUBSTRING(@State,  1, 60)
			SET @Postal = SUBSTRING(@Postal, 1, 32)

			INSERT INTO IPAddress(
				 IpAddress
				,CountryCode
				,City
				,State
				,Postal
				,Latitude
				,Longitude
			)
			VALUES(
				 @IpAddress
				,@CountryCode
				,@City
				,@State
				,@Postal
				,@Latitude
				,@Longitude
			);
	
			IF @@error = 0
				SET @Id = SCOPE_IDENTITY();
			ELSE
				SET @Id = -1;
		END
	ELSE
		BEGIN
			SELECT
				@Id = Id
			FROM
				IPAddress
			WHERE
				IpAddress = @IpAddress;
		END

GO

#region Class Level Private Variables

private int        _Id              = 0;
private string     _IpAddress       = "";
private string     _CountryCode     = "";
private bool       _Blocked         = false;
private int        _BlockedHitCount = 0;
private string     _City            = "";
private string     _State           = "";
private string     _Postal          = "";
private double     _Latitude        = 0;
private double     _Longitude       = 0;

#endregion //Class Level Private Variables

#region Properties

/// 
public accessor for _Id
public int Id
{
	get { return _Id;}
	set
	{
		if (value != _Id)
		{
			_Id = value;
		}
	}
}
/// 
public accessor for _IpAddress
public string IpAddress
{
	get { return _IpAddress;}
	set
	{
		if (value != _IpAddress)
		{
			_IpAddress = value;
		}
	}
}

// etc.

#endregion //Properties

// Load RecordSet
public void FromReader(SafeDataReader dr)
{
	int i = 0;

	_Id              = dr.GetInt32(i++);
	_IpAddress       = dr.GetString(i++);
	_CountryCode     = dr.GetString(i++);
	_Blocked         = dr.GetBoolean(i++);
	_BlockedHitCount = dr.GetInt32(i++);
	_City            = dr.GetString(i++);
	_State           = dr.GetString(i++);
	_Postal          = dr.GetString(i++);
	_Latitude        = dr.GetDouble(i++);
	_Longitude       = dr.GetDouble(i++);
}

// GetByID
public static IPAddress GetByID(int Id)
{
	// return
	IPAddress iPAddress = null;

	ConnectionManager cm = new ConnectionManager();
	SqlCommand cmd = new SqlCommand();
			
	try
	{
		SqlConnection cn  = cm.OpenConnection();
		SafeDataReader dr = null;
		try
		{
			cmd.Connection  = cn;
			cmd.CommandType = CommandType.StoredProcedure;
					
			cmd.CommandText = "p_IPAddress_GetByID";

			cmd.Parameters.AddWithValue("@Id", Id); 
					
			dr = new SafeDataReader(cmd.ExecuteReader());
			if ( dr.Read() )
			{
				iPAddress = new IPAddress();
				iPAddress.FromReader(dr);
			}
		}
		finally
		{
			if ( dr != null && !dr.IsClosed )
				dr.Close();
		}
	}
	catch(Exception ex)
	{
		throw(ex);
	}
	finally
	{
		cm.CloseConnection();
	}

	return iPAddress;
}