title: How to call a stored proc using a generic DB connection in C#?
body: <p>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.</p>
  
  <p>My stored procedures have input/output parameters and a return value.</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE PROCEDURE myProc (@MyInputArg varchar(10), @MyOutputArg varchar(20) output) AS (...) return @@ERROR
  </code></pre>
  
  <p>My problem is I can't find a way to store a command which could be generic whatever the client. I'm using an <strong>IDbCommand</strong> object.</p>
  
  <p>With an ODBC connection I must define:</p>
  
  <pre class="lang-cs prettyprint-override"><code>objCmd.CommandText = "? = CALL myProc (?,?)";
  </code></pre>
  
  <p>In an SQLclient context:</p>
  
  <pre class="lang-cs prettyprint-override"><code>objCmd.CommandText = "myProc";
  </code></pre>
  
  <p>I do not really want to parse my command, I'm sure there is a better way to have a generic one!</p>
  
  <p>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.</p>
  
  <pre class="lang-cs prettyprint-override"><code>// 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
  }
  </code></pre>
  
  <p>Thanks for your time.</p>
  
  <p>Regards,
  Thibaut.</p>