Declerative Data Access

[Angelos Karantzalis]

Hi guys,

for a while now, we've been flirting with the idea of having a single
component in our app that handles all the plumbing for our Db calls (
creating connections, Commands & Parametes etc. ) through an xml definition
of each Db call.

For instance, a call to a Stored Procedure would be described like this:

<data-action id="AddIncident" type="1" data-source="local"
query="sp_AddIncident" cached="false"
cache-timeout="0" sql-type="0">
<params>
<param name="@Name" type="String" required="true" />
<param name="@Descr" type="String" required="false" />
<param name="@StatusID" type="Int64" required="false" />
<param name="@TypeID" type="Int64" required="false" />
<param name="@DtOccured" type="DateTime" required="false" />
<param name="@DtReported" type="DateTime" required="false" />
<param name="@ReportedBy" type="String" required="false" />
<param name="@Place" type="String" required="false" />
<param name="@Cause" type="String" required="false" />
<param name="@OrganizationID" type="Int64" required="false" />
<param name="@Notes" type="String" required="false" />
<param name="@SysUser" type="String" required="false" />
</params>
</data-action>

... and you could use the component in manner like:

DataSet results = DataActionManager.execute("AddIncident", new
object[]{"testname", null, null, 4 ... });

That will certainly cut down on our development time, as well as provide for
some separation of our DB interface from it's actual implementation (e.g. we
could define our db operations, give them a name & then the C# developer &
the db developer could work almost independently, plus we could change data
sources transparently, cache the results etc. etc.).

However, we're concerned with memory usage. A 200K xml file containing 100
db call definitions would have to stay in-memory because it wouldn't make
sence to load/parse it on every call to the component. Isn't that a
significant overhead ?

Our "solution" is to deserialize the xml into objects - that will probably
cut down on memory, but how much we've no idea :?

Our other concern is .. is it worth it ? From a re-usability point of view
and developer-friendlyness i feel it would be very much worth the time, and
the performance/speed trade-off between using the component or straight
ADO.NET code i guess is minimal ... plus I like the idea of having
performance-critical code in one place where we can work on optimizations
and code efficiency rather than leave it up to every junior developer to
handle the db coding.

What it all boils down to is: Although we think it's a good idea, does
anybody out there agree or disagree ? Could you see some potential problems
or reasons that we shouldn't use something like that ?

Cheers,
Angel
O:]

[Jim Hughes]

I would seriously consider using a code generator like CodeSmith
http://www.ericjsmith.net/codesmith/ rather than hand edit xml files as
procs are added.

CodeSmith is a template-based code generator that can generate code for any
ASCII-based language.

One of the many features is the the ability to query a database and generate
stored procedures and data layer code to call those stored procedures.

It can either create a complete class or insert code into an existing class
to allow regeneration as things change.

The generator is FREEWARE and templates can be created with any text editor.
There is a Pro version available that has an intelligent editor.

"Angelos Karantzalis" <ak**********@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

Hi guys,

for a while now, we've been flirting with the idea of having a single
component in our app that handles all the plumbing for our Db calls (
creating connections, Commands & Parametes etc. ) through an xml
definition
of each Db call.

For instance, a call to a Stored Procedure would be described like this:

<data-action id="AddIncident" type="1" data-source="local"
query="sp_AddIncident" cached="false"
cache-timeout="0" sql-type="0">
<params>
<param name="@Name" type="String" required="true" />
<param name="@Descr" type="String" required="false" />
<param name="@StatusID" type="Int64" required="false" />
<param name="@TypeID" type="Int64" required="false" />
<param name="@DtOccured" type="DateTime" required="false" />
<param name="@DtReported" type="DateTime" required="false" />
<param name="@ReportedBy" type="String" required="false" />
<param name="@Place" type="String" required="false" />
<param name="@Cause" type="String" required="false" />
<param name="@OrganizationID" type="Int64" required="false" />
<param name="@Notes" type="String" required="false" />
<param name="@SysUser" type="String" required="false" />
</params>
</data-action>

.. and you could use the component in manner like:

DataSet results = DataActionManager.execute("AddIncident", new
object[]{"testname", null, null, 4 ... });

That will certainly cut down on our development time, as well as provide
for
some separation of our DB interface from it's actual implementation (e.g.
we
could define our db operations, give them a name & then the C# developer &
the db developer could work almost independently, plus we could change
data
sources transparently, cache the results etc. etc.).

However, we're concerned with memory usage. A 200K xml file containing 100
db call definitions would have to stay in-memory because it wouldn't make
sence to load/parse it on every call to the component. Isn't that a
significant overhead ?

Our "solution" is to deserialize the xml into objects - that will probably
cut down on memory, but how much we've no idea :?

Our other concern is .. is it worth it ? From a re-usability point of
view
and developer-friendlyness i feel it would be very much worth the time,
and
the performance/speed trade-off between using the component or straight
ADO.NET code i guess is minimal ... plus I like the idea of having
performance-critical code in one place where we can work on optimizations
and code efficiency rather than leave it up to every junior developer to
handle the db coding.

What it all boils down to is: Although we think it's a good idea, does
anybody out there agree or disagree ? Could you see some potential
problems
or reasons that we shouldn't use something like that ?

Cheers,
Angel
O:]

[Angelos Karantzalis]

Well, you have to understand that I'm aiming at the most flexible solution
possible. If I can manage it, I don't want to ever re-compile or re-deploy
anything ( I know .. that's impossible, but I think I can avoid redeployment
just to add another Db call, no ? )

Code-generation has a serious limitation .. to change stuff, I need to
re-compile & re-deploy. In contrast, if I want to add another Db proc on my
component during runtime, all I need to do is edit the xml file & call a
"reload" on my running component ... and voila ! Instant added functionality
:)

.... plus, I've already written a basic code-generator that creates my data
access classes from the original xml desriptions. Since it's XSLT-based, I
can generate the code for whatever language I want too, and it took me less
than 30 mins to write it so ... :)

Thanks for the pointer though.

Angel
O:]
"Jim Hughes" <NO*********@Hotmail.com> wrote in message
news:u$**************@tk2msftngp13.phx.gbl...

I would seriously consider using a code generator like CodeSmith
http://www.ericjsmith.net/codesmith/ rather than hand edit xml files as
procs are added.

CodeSmith is a template-based code generator that can generate code for any ASCII-based language.

One of the many features is the the ability to query a database and generate stored procedures and data layer code to call those stored procedures.

It can either create a complete class or insert code into an existing class to allow regeneration as things change.

The generator is FREEWARE and templates can be created with any text editor. There is a Pro version available that has an intelligent editor.

"Angelos Karantzalis" <ak**********@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

Hi guys,

for a while now, we've been flirting with the idea of having a single
component in our app that handles all the plumbing for our Db calls (
creating connections, Commands & Parametes etc. ) through an xml
definition
of each Db call.

For instance, a call to a Stored Procedure would be described like this:

<data-action id="AddIncident" type="1" data-source="local"
query="sp_AddIncident" cached="false"
cache-timeout="0" sql-type="0">
<params>
<param name="@Name" type="String" required="true" />
<param name="@Descr" type="String" required="false" />
<param name="@StatusID" type="Int64" required="false" />
<param name="@TypeID" type="Int64" required="false" />
<param name="@DtOccured" type="DateTime" required="false" />
<param name="@DtReported" type="DateTime" required="false" />
<param name="@ReportedBy" type="String" required="false" />
<param name="@Place" type="String" required="false" />
<param name="@Cause" type="String" required="false" />
<param name="@OrganizationID" type="Int64" required="false" />
<param name="@Notes" type="String" required="false" />
<param name="@SysUser" type="String" required="false" />
</params>
</data-action>

.. and you could use the component in manner like:

DataSet results = DataActionManager.execute("AddIncident", new
object[]{"testname", null, null, 4 ... });

That will certainly cut down on our development time, as well as provide
for
some separation of our DB interface from it's actual implementation (e.g. we
could define our db operations, give them a name & then the C# developer & the db developer could work almost independently, plus we could change
data
sources transparently, cache the results etc. etc.).

However, we're concerned with memory usage. A 200K xml file containing 100 db call definitions would have to stay in-memory because it wouldn't make sence to load/parse it on every call to the component. Isn't that a
significant overhead ?

Our "solution" is to deserialize the xml into objects - that will probably cut down on memory, but how much we've no idea :?

Our other concern is .. is it worth it ? From a re-usability point of
view
and developer-friendlyness i feel it would be very much worth the time,
and
the performance/speed trade-off between using the component or straight
ADO.NET code i guess is minimal ... plus I like the idea of having
performance-critical code in one place where we can work on optimizations and code efficiency rather than leave it up to every junior developer to
handle the db coding.

What it all boils down to is: Although we think it's a good idea, does
anybody out there agree or disagree ? Could you see some potential
problems
or reasons that we shouldn't use something like that ?

Cheers,
Angel
O:]