How to call a stored proc using a generic DB connection in C#?

Question

I have to call stored procs in C# (.Net 2.0) sometimes using an ODBC connection, sometime with SQLClient. I the future we could have to communicate with Oracle as well.

My stored procedures have input/output parameters and a return value.

CREATE PROCEDURE myProc (@MyInputArg varchar(10), @MyOutputArg varchar(20) output) AS (...) return @@ERROR

My problem is I can't find a way to store a command which could be generic whatever the client. I'm using an IDbCommand object.

With an ODBC connection I must define:

objCmd.CommandText = "? = CALL myProc (?,?)";

In an SQLclient context:

objCmd.CommandText = "myProc";

I do not really want to parse my command, I'm sure there is a better way to have a generic one!

In order to help people to reproduce, you can find below how I made the generic DB connection. In my context, the provider object is defined from a configuration file.

// My DB connection string, init is done from a configuration file
string myConnectionStr = "";

// Provider which defined the connection type, init from config file
//object objProvider = new OdbcConnection(); //ODBC
object objProvider = new SqlConnection(); // SQLClient

// My query -- must be adapted switch ODBC or SQLClient -- that's my problem!
//string myQuery = "? = CALL myProc (?,?)"; // ODBC
string myQuery = "myProc"; // SQLClient

// Prepare the connection
using (IDbConnection conn = (IDbConnection)Activator.CreateInstance(typeof(objProvider), myConnectionStr ))
{
    // Command creation
    IDbCommand objCmd = (IDbCommand)Activator.CreateInstance(typeof(objProvider));
    objCmd.Connection = conn;

    // Define the command
    objCmd.CommandType = CommandType.StoredProcedure;
    objCmd.CommandTimeout = 30;
    objCmd.CommandText = myQuery;

    IDbDataParameter param;

    // Return value
    param = (IDbDataParameter)Activator.CreateInstance(typeof(objProvider));
    param.ParameterName = "RETURN_VALUE";
    param.DbType = DbType.Int32;
    param.Direction = ParameterDirection.ReturnValue;
    objCmd.Parameters.Add(param);

    // Param 1 (input)
    param = (IDbDataParameter)Activator.CreateInstance(typeof(objProvider));
    param.ParameterName = "@MyInputArg";
    param.DbType = DbType.AnsiString;
    param.Size = 10;
    param.Direction = ParameterDirection.Input;
    param.Value = "myInputValue";
    objCmd.Parameters.Add(param);

    // Param 2 (output)
    param = (IDbDataParameter)Activator.CreateInstance(typeof(objProvider));
    param.ParameterName = "@MyOutputArg";
    param.DbType = DbType.AnsiString;
    param.Size = 20;
    param.Direction = ParameterDirection.Output;
    objCmd.Parameters.Add(param);

    // Open and execute the command
    objCmd.Connection.Open();
    objCmd.ExecuteReader(CommandBehavior.SingleResult);
    (...) // Treatment
}

Thanks for your time.

Regards, Thibaut.

Answer 1

You could create a wrapper around IDbCommand and implementations (but only if you want to :) ).

//Note the IDisposable interface
public class MultiSqlCommand : IDisposable
{
  //DbConnectionType  is a custom enum
  public MultiSqlCommand(DbConnectionType connType, DbConnection conn)
  {
    //initialize members
    ...
    switch(connType) 
    {
      case ADO:
        _cmd = new SqlCommand(_connection);
        break;
      case ODBC:
        ...
    }
  }

  //As param name you pass the undecorated parameter name (no @, ?, etc.)
  public void AddParameter(string strippedName, object value) 
  {
    //this should be an internal function which gives you an SqlParameter formatted for your specific DbConnectionType
    object parameter = GetSqlParam(strippedName, value);
    _cmd.Parameters.Add(object);
  }
}

After adding parameters you have your command ready for execution. You can either expose it through a property and execute it outside of this class or extend the API and add a few methods to execute it and get results. Whatever you find convenient.

Note: In your IDisposable implementation you should dispose the IDbCommand instance (if not null). This is very important, otherwise you'll leak memory.

If the suggestion is not clear, then let me know and I'll try to fill it up with more details. The solution is simple, I would say. You could go further and create an abstraction layer on top of the entire ADO.NET commands and parameters support, but I don't think is really necessary here. It's not like you're not writing an entire data provider.