Desing pattern question, relating to data access.

[ahaupt]

Hi all,

At the moment we use static methods for retrieving data: Ex.


DataTable supplierDT = DAC.Supplier.GetAll();
DataTable customerDT = DAC.Customer.GetAll();


The reason we use static methods is that it's not really worth the
trouble (which might be a bad design) to instantiate a class each time
you want to do simple query. Ex:


//We'd like to avoid this
DAC.Supplier newSupplierDAC = new DAC.Supplier();
DataTable supplierDT = newSupplierDAC.GetAll();


Concerning desing, is it bad practice to have static methods in this
scenario?

Also, I've found static methods to be quite limiting, since they can't
be declared as abstract. Ex:

I'd like to have a base class with all the shared methods, and just
overide a single accessor in the inherited classes:


//Base class
abstract class Queries
{
static DataTable GetAll()
{
return SomeDataRetriever.ExecuteQuery( QueryString );
}

static QueryString
{
get
{
return "SELECT * MumboJumbo";
}
}
}

//Inherited class
class Supplier : Queries
{
static QueryString
{
get
{
return "SELECT * FROM Suppliers";
}
}
}

//But when I call
Supplier.GetAll(); //it returns "MumboJumbo" and not "Suppliers"

Any help or suggestions regarding this design will be greatly
appreciated.

Thanks, (for still reading!)
Andre

 

[gmiley]

What we have done is create a class in our root "Shared" library called
"DataAccess".

Basically the single DataAccess object contains methods for executing
and returning various types of SQL queries. The DataAccess class stands
alone once its created, but it can obviously be created a second or
more time if needed for some odd reason.

The DA class contains methods such as:
.BeginTransaction()
.CloseTransaction()
.CommitTransaction()
.Rollback()
.SqlExecNonQuery()
.SqlExecScalar()
.SqlExecTable()

And so on, etc...

So assuming we needed to populate a list of items:

Shared.DataAccess da = new Shared.DataAccess(connectionString);
string sqlString = "SELECT item_name, item_value FROM
database.dbo.items";
System.Data.DataTable itemTable = da.SqlExecTable(sqlString);

 

[Vadym Stetsyak]

Non static design pattern is more prefferable. It is simply more flexible.

To override something you have to declare that something as virtual...
Also it cannot be static, so you'll have to create instances

From MSDN:
"A static member 'function' cannot be marked as override, virtual or
abstract
Any method declaration that uses the override, virtual, or abstract keyword
cannot also use the 'static' keyword.
"

 

[ahaupt]

That's a shame.

Thanks for the reply.

Andre