How do I create generic SQL parameters in .NET?

Question

According to Microsoft documentation on Configuring Parameters, "the .NET Framework data providers handle naming and specifying parameters and parameter placeholders differently".

• System.Data.SqlClient uses named parameters in the format @parametername
• System.Data.OleDb and System.Data.Odbc use positional parameter markers indicated by a question mark (?)
• System.Data.OracleClient uses named parameters in the format :parmname (or parmname)

I'm writing methods to return SQL that will be used for parameterised statements. If I use standard SQL these statements should be portable to a wide variety of databases. How can I create parameters that are generally valid without leakage of concerns from the data provider to the SQL components?

Answer 1

Just avoid the @ and using System.Data.SqlClient /System.Data.OracleClient

Btw, if you want it portable, use either System.Data.IDbCommand or System.Data.Common.DbCommand

Whether you use the interface or the abstract class is a matter of taste.
The interface is more proper, the abstract class however has a few additional methods that aren't available in the interface (e.g. DataReader.HasRows). I used the interface, but retrospecively, that was an error. I should have used the abstract class instead (System.Data.Common.DbAnything instead of System.Data.IAnything).

You can either write an extension method "AddParameter" to these, or you can turn your DAL into an abstract class, and add a method AddParameter, where you can overwrite the variable name for the respecitive instance.

public abstract class cDAL
{


    // From Type to DBType
    protected virtual System.Data.DbType GetDbType(Type type)
    {
        // http://social.msdn.microsoft.com/Forums/en/winforms/thread/c6f3ab91-2198-402a-9a18-66ce442333a6
        string strTypeName = type.Name;
        System.Data.DbType DBtype = System.Data.DbType.String; // default value

        try
        {
            if (object.ReferenceEquals(type, typeof(System.DBNull)))
            {
                return DBtype;
            }

            if (object.ReferenceEquals(type, typeof(System.Byte[])))
            {
                return System.Data.DbType.Binary;
            }

            DBtype = (System.Data.DbType)Enum.Parse(typeof(System.Data.DbType), strTypeName, true);

            // Es ist keine Zuordnung von DbType UInt64 zu einem bekannten SqlDbType vorhanden.
            // http://msdn.microsoft.com/en-us/library/bbw6zyha(v=vs.71).aspx
            if (DBtype == System.Data.DbType.UInt64)
                DBtype = System.Data.DbType.Int64;
        }
        catch (Exception)
        {
            // add error handling to suit your taste
        }

        return DBtype;
    } // End Function GetDbType


    public virtual System.Data.IDbDataParameter AddParameter(System.Data.IDbCommand command, string strParameterName, object objValue)
    {
        return AddParameter(command, strParameterName, objValue, System.Data.ParameterDirection.Input);
    } // End Function AddParameter


    public virtual System.Data.IDbDataParameter AddParameter(System.Data.IDbCommand command, string strParameterName, object objValue, System.Data.ParameterDirection pad)
    {
        if (objValue == null)
        {
            //throw new ArgumentNullException("objValue");
            objValue = System.DBNull.Value;
        } // End if (objValue == null)

        System.Type tDataType = objValue.GetType();
        System.Data.DbType dbType = GetDbType(tDataType);

        return AddParameter(command, strParameterName, objValue, pad, dbType);
    } // End Function AddParameter


    public virtual System.Data.IDbDataParameter AddParameter(System.Data.IDbCommand command, string strParameterName, object objValue, System.Data.ParameterDirection pad, System.Data.DbType dbType)
    {
        System.Data.IDbDataParameter parameter = command.CreateParameter();

        if (!strParameterName.StartsWith("@"))
        {
            strParameterName = "@" + strParameterName;
        } // End if (!strParameterName.StartsWith("@"))

        parameter.ParameterName = strParameterName;
        parameter.DbType = dbType;
        parameter.Direction = pad;

        // Es ist keine Zuordnung von DbType UInt64 zu einem bekannten SqlDbType vorhanden.
        // No association  DbType UInt64 to a known SqlDbType

        if (objValue == null)
            parameter.Value = System.DBNull.Value;
        else
            parameter.Value = objValue;

        command.Parameters.Add(parameter);
        return parameter;
    } // End Function AddParameter


    public virtual T GetParameterValue<T>(System.Data.IDbCommand idbc, string strParameterName)
    {
        if (!strParameterName.StartsWith("@"))
        {
            strParameterName = "@" + strParameterName;
        }

        return InlineTypeAssignHelper<T>(((System.Data.IDbDataParameter)idbc.Parameters[strParameterName]).Value);
    } // End Function GetParameterValue<T>


}

And then overwrite it in the respective provider :

public class cOleDb : cDAL
{




   public overrideSystem.Data.IDbDataParameter AddParameter(System.Data.IDbCommand command, string strParameterName, object objValue)
    {
        strParameterName = "?";
        return AddParameter(command, strParameterName, objValue, System.Data.ParameterDirection.Input);
    } // End Function AddParameter


    // Etc.

}

And you can create a respective class instance by reading provider in the connection string web.config entry.

PS: System.Data.OracleClient is deprecated, use ODP.NET (you still need the native OracleClient dll's from Oracle installed [free download])