DAO method with variable number of parameters

[sheila11]

Hi all,

I have to write Data Access Layer, with methods that will invoke stored procedures. I am thinking that if I write a method that accepts variable number of parameters and the procedure name, it will be able to call any stored proc. Something like this:

public DataSet CallProcedure(string StProcName, params object[] OArrParams)
{
DataSet DS = new DataSet();

return DS;
}

Do you see any issue with this approach?
Is there a better way of doing this?

TIA,
Sheila

 

[jmeckley]

I have to write Data Access Layer...
Do you see any issue with this approach?
Is there a better way of doing this?

I cannot stress enough that rolling you own is a bad idea. Data Access is a solved problem.
Nhibernate
Active Record
MS Enterprise Library DataAccess
LLBL Gen Pro ($)

there are many concepts that these frameworks solve that most developers do not know about. entity mapping, cache, unit of work, thread safety, uniqueing, transaction management, versioning, just to name a few.

If you still want/need to roll your own: your signature works a raw base level to reduce repetitive code. however the client code should use specific repositories which reference this generic one. example:

class Parameter
{
    public string name {get; private set;}
    public object value {get; private set;}

    public parameter(string name, object value)
    {
        this.name = name;
        this.value = value;
    }
}

class GenericRepository : IRepository
{
   public DataTable CallProc(string name, params Parameter[] parameters)
   {
       string variables = " ";
       foreach(var p in parameters)
       {
          variables += " @" + p.name;
       }

       var results = new DataTable();
       IDbConnection connection = connection_manager.Current;
       using(IDbCommand command = connection.CreateCommand())
       {
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "exec " + name + variables
          foreach(p in parameters)
          {
              var parameter = command.CreateParameter();
              parameter.Name = p.name;
              parameter.Value = p.value;
              command.Parameters.Add(parameter);
          }
          results.Load(command.ExecuteReader());
       }
       return results;
    }
}

where connection_manager is a ctor argument and is responsible for providing a connection (this object would also manage transactions).
then I would have specific repositories which express intent

public class CustomerRepository
{
   private IRepository repository;

   public CustomerRepository(IRepository repository)
   {
      this.repository = repository;
   }

   public DataTable find_all_customers_in_a_region(int region_id)
   {
      return repository.CallProc("customers_in_region", new Parameter("region", region_id));
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[sheila11]

Thanks Jason. I'll certainly look into the libraries you've mentioned.

I am thinking that if I have to use my function, I can pass an array of SQLParameter objects, and use SQLConnection instead of a generic one.

Thanks again,
Sheila

 

[jmeckley]

I am thinking that if I have to use my function, I can pass an array of SQLParameter objects, and use SQLConnection instead of a generic one.

you could, but this tightly couples objects to one another. for example. should consuming code care about where the data comes from? in the specific instance of databases, should consuming code, care what data it connects to? the answer to both is 'no'. client code (code consuming the object) shouldn't care how it works, only that it does work.

I try to keep my objects as loosely coupled as possible (program to the interface, not the implementation.) it plays very well with SOLID design principles.

Jason Meckley
Programmer
Specialty Bakers, Inc.