Db Parameters for Store Procedure

Question

Hi,

The application I am working with needs to make multiple store procedure calls to the database.  Each store procedure accepts different set of parameters. 

I was wondering what the proper way do this would be? Is it possible to pass a list of parameters to a function then have a loop to add it to SqlCommand parameters list?

I would like to just write one function that does all the calls to the database.  

If anyone could provide code examples, it would be appreciated.

Thanks!

Answer 1

Yes you can pass a list of parameters.  What we do is expose a method off our main DAL object that accepts a DbCommand (or, in our case, a custom version) that we can then execute. It is up to the caller to create the command (and any parameters) but the actual execution occurs in only 1 place. If you want to wrap that up even more then add methods to your DAL that creates the command based upon parameters and then passes it along to the main method. If you don't want to touch the DAL then an extension method is a great way to go.

public class MyMainDALObject
{
   public void ExecuteNonQuery ( DbCommand command )
   {
      //Run command
   }

   //If desired
   public void CallSomeSproc ( int parm1, string parm2 )
   {
      //Build up DbCommand
      var cmd = ...;

      ExecuteNonQuery(cmd);      
   }
}

Michael Taylor
http://msmvps.com/blogs/p3net

Answer 2

Maybe you can work around this code and make it work, just convert it to SqlCommand

        public OracleCommand FillCommand(Hashtable hash, OracleCommand cmd)
        {
            foreach (DictionaryEntry entry in hash)
            {
                OracleParameter p = new OracleParameter();
                p.ParameterName = entry.Key.ToString();
                p.Value = entry.Value;
                p.DbType = GetOracleDbType(entry.Value);

                cmd.Parameters.Add(entry.Key, entry.Value);
            }
            return cmd;
        }

        private OracleDbType GetOracleDbType(object o)
        {
            if (o is string) return OracleDbType.Varchar2;
            if (o is DateTime) return OracleDbType.Date;
            if (o is Int64) return OracleDbType.Int64;
            if (o is Int32) return OracleDbType.Int32;
            if (o is Int16) return OracleDbType.Int16;
            if (o is byte) return OracleDbType.Byte;
            if (o is decimal) return OracleDbType.Decimal;
            if (o is float) return OracleDbType.Single;
            if (o is double) return OracleDbType.Double;
            if (o is byte[]) return OracleDbType.Blob;

            return OracleDbType.Varchar2;
        }

---

If you get your question answered, please come back and Mark As Answer.
Web Developer