Hello Jonathan,
This is something still I think about, even though I have constructed a lot of
data access layers, and re-used existing ones. Depends on the project, what is
requested and considering what Cor said above, and 'cascading' a design from that.
I will briefly state some examples of the way I have approached this, relative to
the task at hand. Relatively extreme examples to what I have done.
1) Custom desktop application, to an access database. The application stored it's
data (and a few project level settings) in a access database, with a custom
extension (not mdb,..), referred to as a project. Projects were specific to the
user, and task, and one project could only be open once, as in one instance.
Though the application could open many different projects (access databases).
This solution I wrapped the following OleDb classes into an assembly;
OleDbConnection, OleDbCommand, OleDbParameterCollection, OleDbParameter and
OleDbTransaction, utilising some of the abstract classes and interfaces associated
with that assembly space, and the System.Data namespace. I only exposed methods
and properties the application would require for it's database operations. This
way, I could do all appropriate exception handling for these classes once. The
connection encapsulated, the commands and transaction, and the command class
encapsulated that parameters object.
Basically I constructed a namespace called DataAccess.MSAccess, given the nature
of the application, being desktop, only one particular project at a time, the
classes were instantiated (i.e. not static), and the connection to a projected
remained opened, until the application instance associated with that projected was
itself closed, or a menu request was sent to close the project. Type strong data
access components called on the DataAccess.MSAccess assembly, but did not need to
call on the System.Data.OleDb namespace, in that case you need to ensure you wrap
all you need to expose, especially when it comes to enums. Hence this methodology
requires quite a lot of work, which need (should not) be the case.
2) SQLServer Database, several users and, two simple ASP applications interacting
with the database.
Constructed a Static databroker, similar to MS Application Data Access Blocked,
but watered down, like waterfalled down. Ended up with five base static function,
maybe 15 when overloaded. Since this broker was used on the same database, and
tables, and multiple users at any one time, I did want to keep instantiating a
data broker. The type strong data access classes that accessed the data broker, is
instantiated so each user has there own data space, and dispose it when finished.
Wasn't so tight encapsulating the Sql namespaces and assemblies into a custom
assembly.
One more thing, use *.config for your connection strings, stored procedure names,
parameter names...
Hope this is the type of discussion-approached you wanted to hear, like you I
would love to hear the way others approach this as well.
Cheers Jonathan.
Regards,
- SpotNet
|I was just wondering how most of you organize your data-access code.
|
| I know there's stored procedures, data source objects, data controls, and
| plain old C# and VB that we can choose from. Although I prefer a simple
| approach, it looks like a multi-tiered approach is warranted and I just
| wanted to hear how others are approaching this.
|
| Thanks.
|
| Jonathan
|
