Collection of extension methods to convert .NET objects/values to equivalent parameters for MS SQL Server. For use with ORMs such as NPoco or PetaPoco to provide explicit conversion to parameter rather than defaults such as all strings being treated as nvarchar(8000) fields.

SqlServerDataTypeParameterMappingExtension.cs

using System;
using System.Data;
using System.Data.SqlClient;



/// <summary>
/// Collection of extension methods to convert .NET objects/values to equivalent parameters for MS SQL Server
/// For use with ORMs such as NPoco or PetaPoco to provide explicit conversion to parameter rather than defaults such as all strings being treated as nvarchar(8000) fields
/// </summary>
/// <remarks>
/// Data type mappings based on MSDN article: http://msdn.microsoft.com/en-us/library/cc716729.aspx
/// </remarks>
public static class SqlServerDataTypeParameterMappingExtension
{


	/// <summary>
	/// Method used internally to build parameter objects
	/// </summary>
	/// <param name="value">Parameter value</param>
	/// <param name="sqlDbType">Database type enum value</param>
	/// <param name="name">Parameter name</param>
	/// <param name="direction">Direction</param>
	/// <returns>Parameter object</returns>
	private static SqlParameter createParameter(Object value, SqlDbType sqlDbType, string name = null, ParameterDirection direction = ParameterDirection.Input)
	{
		var parm = new SqlParameter();

		parm.SqlDbType = sqlDbType;

		//if supplied with name in format @name use name string, else prepend "@" sign
		if (!String.IsNullOrWhiteSpace(name))
		{
			if (name.StartsWith("@"))
			{
				parm.ParameterName = name;
			}
			else
			{
				parm.ParameterName = "@" + name;
			}
		}

		parm.Direction = direction;

		if (value != null)
		{
			parm.Value = value;
		}
		else
		{
			parm.IsNullable = true;
			parm.Value = DBNull.Value;
		}

		return parm;
	}


	private static SqlParameter createNullValueParameter(SqlDbType sqlDbType, string name = null, ParameterDirection direction = ParameterDirection.Input)
	{
		return createParameter(null, sqlDbType, name, direction);
	}




	#region integers

	public static SqlParameter ToSqlParameter(this Int64 input, string name = null, Int64? dbNullMagicValue = null, ParameterDirection direction = ParameterDirection.Input)
	{
		var parm = createParameter(input, SqlDbType.BigInt, name, direction);

		if (dbNullMagicValue.HasValue && input == dbNullMagicValue.Value)
		{
			parm.IsNullable = true;
			parm.Value = DBNull.Value;
		}

		return parm;
	}


	public static SqlParameter ToSqlParameter(this Int64? input, string name = null, ParameterDirection direction = ParameterDirection.Input)
	{
		if (input.HasValue)
		{
			return input.Value.ToSqlParameter(name, null, direction);
		}
		else
		{
			return createNullValueParameter(SqlDbType.BigInt, name, direction);
		}
	}


	public static SqlParameter ToSqlParameter(this Int32 input, SqlServerIntegerType dbType = SqlServerIntegerType.Int, string name = null, Int32? dbNullMagicValue = null, ParameterDirection direction = ParameterDirection.Input)
	{
		var parm = createParameter(input, (SqlDbType)dbType, name, direction);

		if (dbNullMagicValue.HasValue && input == dbNullMagicValue.Value)
		{
			parm.IsNullable = true;
			parm.Value = DBNull.Value;
		}

		return parm;

	}


	public static SqlParameter ToSqlParameter(this Int32? input, SqlServerIntegerType dbType = SqlServerIntegerType.Int, string name = null, ParameterDirection direction = ParameterDirection.Input)
	{
		if (input.HasValue)
		{
			return input.Value.ToSqlParameter(dbType, name, null, direction);
		}
		else
		{
			return createNullValueParameter((SqlDbType)dbType, name, direction);
		}
	}

	//for Int16 and Btye call existing int method

	public static SqlParameter ToSqlParameter(this Int16 input, SqlServerIntegerType dbType = SqlServerIntegerType.SmallInt, string name = null, Int16? dbNullMagicValue = null, ParameterDirection direction = ParameterDirection.Input)
	{
		return Convert.ToInt32(input).ToSqlParameter(dbType, name, (int?)dbNullMagicValue, direction);
	}


	public static SqlParameter ToSqlParameter(this Int16? input, SqlServerIntegerType dbType = SqlServerIntegerType.SmallInt, string name = null, ParameterDirection direction = ParameterDirection.Input)
	{
		if (input.HasValue)
		{
			return input.Value.ToSqlParameter(dbType, name, null, direction);
		}
		else
		{
			return createNullValueParameter((SqlDbType)dbType, name, direction);
		}
	}

	public static SqlParameter ToSqlParameter(this Byte input, SqlServerIntegerType dbType = SqlServerIntegerType.TinyInt, string name = null, Byte? dbNullMagicValue = null, ParameterDirection direction = ParameterDirection.Input)
	{
		return Convert.ToInt32(input).ToSqlParameter(dbType, name, (int?)dbNullMagicValue, direction);
	}

	public static SqlParameter ToSqlParameter(this Byte? input, SqlServerIntegerType dbType = SqlServerIntegerType.TinyInt, string name = null, ParameterDirection direction = ParameterDirection.Input)
	{
		if (input.HasValue)
		{
			return input.Value.ToSqlParameter(dbType, name, null, direction);
		}
		else
		{
			return createNullValueParameter((SqlDbType)dbType, name, direction);
		}
	}

	#endregion // integers


	#region byte[] / binary

	public static SqlParameter ToSqlParameter(this byte[] input, bool treatEmptyAsDbNull = false, string name = null, ParameterDirection direction = ParameterDirection.Input)
	{

		var parm = createParameter(input, SqlDbType.BigInt, name, direction);

		if (treatEmptyAsDbNull && input.LongLength == 0)
		{
			parm.IsNullable = true;
			parm.Value = DBNull.Value;
		}


		return parm;

	}

	#endregion //  byte[] / binary


	#region bool / bit

	public static SqlParameter ToSqlParameter(this bool input, string name = null, ParameterDirection direction = ParameterDirection.Input)
	{
		return createParameter(input, SqlDbType.Bit, name, direction);
	}


	public static SqlParameter ToSqlParameter(this bool? input, string name = null, ParameterDirection direction = ParameterDirection.Input)
	{
		if (input.HasValue)
		{
			return input.Value.ToSqlParameter(name, direction);
		}
		else
		{
			return createNullValueParameter(SqlDbType.Bit, name, direction);
		}
	}


	#endregion //  bool / bit



	#region string

	//for size 0 = use length of input, -1 used for MAX fields such as VARCHAR(MAX), other positive numbers used as param size
	public static SqlParameter ToSqlParameter(this string input, SqlServerStringType dbType = SqlServerStringType.NVarChar, string name = null, int size = 0, bool treatEmptyAsDbNull = false, ParameterDirection direction = ParameterDirection.Input)
	{


		int usefulSizeValue = 0;
		switch (size)
		{
			case 0:
				break; //use 0

			case -1:
				usefulSizeValue = -1;
				break;

			default:
				if (size > 0)
				{
					usefulSizeValue = size;
				}
				break;

		}


		var parm = createParameter(input, (SqlDbType)dbType, name, direction);


		parm.Size = usefulSizeValue;

		if (input == null || (treatEmptyAsDbNull && input.Length == 0))
		{
			parm.IsNullable = true;
			parm.Value = DBNull.Value;
		}


		return parm;

	}


	#endregion //string



	#region datetime


	public static SqlParameter ToSqlParameter(this DateTime input, string name = null, SqlServerDateType dbType = SqlServerDateType.DateTime, DateTime? DbNullMagicValue = null, ParameterDirection direction = ParameterDirection.Input)
	{
		var parm = createParameter(input, (SqlDbType)dbType, name, direction);

		if (DbNullMagicValue.HasValue && input == DbNullMagicValue.Value)
		{
			parm.IsNullable = true;
			parm.Value = DBNull.Value;
		}

		return parm;
	}

	#endregion //datetime


	#region decimal



	//if precision or scale is null the value of input will be used to determine precision and scale	
	public static SqlParameter ToSqlParameter(this decimal input, SqlServerDecimalType dbType = SqlServerDecimalType.Decimal, string name = null, byte? precision = null, byte? scale = null, decimal? dbNullMagicValue = null, ParameterDirection direction = ParameterDirection.Input)
	{
		var parm = createParameter(input, (SqlDbType)dbType, name, direction);

		if (dbNullMagicValue.HasValue && dbNullMagicValue.Value == input)
		{
			parm.IsNullable = true;
			parm.Value = DBNull.Value;
		}


		if (precision.HasValue)
		{
			parm.Precision = precision.Value;
		}

		if (scale.HasValue)
		{
			parm.Scale = scale.Value;
		}



		return parm;
	}


	
	public static SqlParameter ToSqlParameter(this decimal? input, string name = null, SqlServerDecimalType dbType = SqlServerDecimalType.Decimal, byte? precision = null, byte? scale = null, decimal? dbNullMagicValue = null, ParameterDirection direction = ParameterDirection.Input)
	{
		if (input.HasValue)
		{
			return input.Value.ToSqlParameter(dbType, name, precision, scale, dbNullMagicValue, direction);
		}
		else
		{
			return createNullValueParameter((SqlDbType)dbType, name, direction);
		}
	}


	#endregion // decimal



	#region GUID



	public static SqlParameter ToSqlParameter(this Guid input, string name = null, bool convertEmptyGuidToDbNull = false, ParameterDirection direction = ParameterDirection.Input)
	{

		var parm = createParameter(input, SqlDbType.UniqueIdentifier, name, direction);

		if (input == null || (convertEmptyGuidToDbNull && input == Guid.Empty))
		{
			parm.IsNullable = true;
			parm.Value = DBNull.Value;
		}

		return parm;

	}


	public static SqlParameter ToSqlParameter(this Guid? input, string name = null, bool convertEmptyGuidToDbNull = false, ParameterDirection direction = ParameterDirection.Input)
	{
		if (input.HasValue)
		{
			return input.Value.ToSqlParameter(name, convertEmptyGuidToDbNull, direction);
		}
		else
		{
			return createNullValueParameter(SqlDbType.UniqueIdentifier, name, direction);
		}


	}


	#endregion //GUID



	#region Byte array / varbinary / binary / timestamp / rowversion / filestream


	public static SqlParameter ToSqlParameter(this Byte[] input, SqlServerByteArrayType dbType = SqlServerByteArrayType.Varbinary, string name = null, bool convertEmptyArrayToDbNull = false, ParameterDirection direction = ParameterDirection.Input)
	{
		var parm = createParameter(input, (SqlDbType)dbType, name, direction);

		if (convertEmptyArrayToDbNull && input.LongLength == 0)
		{
			parm.IsNullable = true;
			parm.Value = DBNull.Value;
		}

		return parm;
	}

	#endregion // btye array





}  // end class SqlServerDataTypeParameterMapping




#region Enum definitions

/// <summary>
/// Data types that map to .NET byte array
/// </summary>
/// <remarks>Subset of SqlDbType enum values</remarks>
public enum SqlServerByteArrayType
{
	Binary = SqlDbType.Binary,
	Filestream = SqlDbType.VarBinary,
	Rowversion = SqlDbType.Timestamp,
	Timestamp = SqlDbType.Timestamp,
	Varbinary = SqlDbType.VarBinary
}


/// <summary>
/// Data types that map to .NET DateTime
/// </summary>
/// <remarks>Subset of SqlDbType enum values</remarks>
public enum SqlServerDateType
{
	DateTime = SqlDbType.DateTime,
	DateTime2 = SqlDbType.DateTime2,
	Date = SqlDbType.Date,
	DateTimeOffset = SqlDbType.DateTimeOffset
}


/// <summary>
/// Data types that map to .NET decimal
/// </summary>
/// <remarks>Subset of SqlDbType enum values</remarks>
public enum SqlServerDecimalType
{
	Decimal = SqlDbType.Decimal,
	Money = SqlDbType.Money,
	Numeric = SqlDbType.Decimal,
	SmallMoney = SqlDbType.SmallMoney
}


/// <summary>
/// Data types that map to .NET Int16, Int32, Byte types
/// </summary>
/// <remarks>Subset of SqlDbType enum values</remarks>
public enum SqlServerIntegerType
{
	Int = SqlDbType.Int,
	SmallInt = SqlDbType.SmallInt,
	TinyInt = SqlDbType.TinyInt
}


/// <summary>
/// Data types that map to .NET string
/// </summary>
/// <remarks>Subset of SqlDbType enum values</remarks>
public enum SqlServerStringType
{
	Char = SqlDbType.Char,
	NChar = SqlDbType.NChar,
	NText = SqlDbType.NText,
	NVarChar = SqlDbType.NVarChar,
	Text = SqlDbType.Text,
	VarChar = SqlDbType.VarChar,
	Xml = SqlDbType.Xml
}

#endregion //Enum definitions