locked
Db Parameters for Store Procedure RRS feed

  • 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!


    Monday, May 12, 2014 1:39 PM

Answers

  • 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

    Monday, May 12, 2014 2:45 PM

All replies

  • 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

    Monday, May 12, 2014 2:45 PM
  • 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 Alternate TextMark As Answer.
    Web Developer

    Monday, May 12, 2014 2:53 PM