Posted: 01/14/2015

In the past I've published some generic AddWithValue extensions to the IDbCommand interface that allow you go simplify your code on ADO.Net providers that don't support AddWithValue. Today I wanted to provide an extension that is specifically for the SqlCommand object that allows the passing in of an explicit SqlDbType instead of the generic DbType which should provide a more fine tuned experience for those that predominantly deal with SQL Server.

The following extensions would be used something like this:

VB.Net Usage

    cmd.AddWithValue("@stack_trace", SqlDbType.VarChar, 1024, ex.StackTrace)

C# Usage

    cmd.AddWithValue("@stack_trace", SqlDbType.VarChar, 1024, ex.StackTrace);

VB.Net

''' <summary>
''' Adds a parameter with length and value into the commands parameter collection.
''' </summary>
''' <param name="cmd"></param>
''' <param name="paramName"></param>
''' <param name="dbType"></param>
''' <param name="size"></param>
''' <param name="value"></param>
''' <remarks></remarks>
<Extension()> _
Public Sub AddWithValue(cmd As SqlCommand, paramName As String, dbType As SqlDbType, size As Integer, value As Object)
    Dim param As New SqlParameter(paramName, dbType, size)
    param.Value = value
    cmd.Parameters.Add(param)
End Sub

C#

/// <summary>
/// Adds a parameter with length and value into the commands parameter collection.
/// </summary>
/// <param name="cmd"></param>
/// <param name="paramName"></param>
/// <param name="dbType"></param>
/// <param name="size"></param>
/// <param name="value"></param>
/// <remarks></remarks>
[Extension()]
public void AddWithValue(SqlCommand cmd, string paramName, SqlDbType dbType, int size, object value)
{
    SqlParameter param = new SqlParameter(paramName, dbType, size);
    param.Value = value;
    cmd.Parameters.Add(param);
}