How to call a stored procedure using a shared database connection in C #?

Question

I need to call stored procs in C # (.Net 2.0), sometimes using an ODBC connection, sometimes with SQLClient. In the future, we could also talk to Oracle.

My stored procedures have I / O parameters and a return value.

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

My problem is that I cannot find a way to keep a team that can be shared by any client. I am using an IDbCommand object.

With an ODBC connection, I have to determine:

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

In the context of SQLclient:

 objCmd.CommandText = "myProc"; 

I really do not want to disassemble my team, I am sure that there is a better way to get a general character.

To help people reproduce, you can find below how I made a general database connection. In my context, the provider object is determined from the 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 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 } 

Thank you for your time.

Regards, Thibaut.

Answer 1

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

 //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 the parameters, your team is ready to execute. You can either open it through a property, or execute it outside of this class, or extend the API and add several methods to execute it and get the results. Whatever you find convenient.

Note. . In your IDisposable implementation, you must remove the IDbCommand instance (if not null). This is very important, otherwise you will be a memory leak.

If the proposal is not clear, let me know and I will try to fill it with more detailed information. I would say that it is simple. You can go further and create an abstraction layer on top of all the ADO.NET support commands and options, but I don’t think it is really necessary here. This is not like you are not writing an entire data provider.