Declarative Data Access

  • Thread starter Thread starter Angelos Karantzalis
  • Start date Start date
A

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:]
 
Angelos Karantzalis said:
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 ... });
Click to expand...

The XML is a good first step, but I wouldn't use it at runtime. Build a
small program which will read the XML file and output a source code file
which executes these procedures and compile that file into your project.
Then in your code you could call the procedure like this:

DataSet results = dal.AddIncident("testname",null,null,4,...);

You get the best of both worlds, plus you get typesafe binding of your
application code to your database interface, so if you pass a string where
you should pass an int, the compiler will complain.

David
 
well he could still use the xml but load it at application startup (assuming
this is a web app). that way the overhead of the file access is hidden. good
point though

--
Regards,
Alvin Bruney
[ASP.NET MVP http://mvp.support.microsoft.com/default.aspx]
Got tidbits? Get it here... http://tinyurl.com/27cok
David Browne said:
Angelos Karantzalis said:
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 ... });
Click to expand...

The XML is a good first step, but I wouldn't use it at runtime. Build a
small program which will read the XML file and output a source code file
which executes these procedures and compile that file into your project.
Then in your code you could call the procedure like this:

DataSet results = dal.AddIncident("testname",null,null,4,...);

You get the best of both worlds, plus you get typesafe binding of your
application code to your database interface, so if you pass a string where
you should pass an int, the compiler will complain.

David
Click to expand...
 
Actually, since the xml contains the parameter data-types , I can enable the
"complain in case of bad parameter data type" feature in the component ...
right now, I've flag in place to enable/disable that type check.

Code generation is something I've thought about (actually, i do generate
"wrapper" classes around the data access component in a few cases ), but I
want to avoid re-compiling & re-deploying every time I need to add another
db operation ...

My own workound the xml overhead problem is to deserialize the xml into
objects, and keep them in-memory. That way, I lose the IO overhead (if I
were to read the file on every call), minimize the memory usage (if I were
to keep a 200K xml file in memory ) and still maintain my instant
update-ability ( by having a reload function in the component that I call
when I 've added something in the xml descriptor).

... that way, the component never goes down, and I can update the
functionality of my app(s) at runtime without any other hussles like
compiling & redeploying COM+ components etc etc.

Angel
O:]


David Browne said:
Angelos Karantzalis said:
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 ... });
Click to expand...

The XML is a good first step, but I wouldn't use it at runtime. Build a
small program which will read the XML file and output a source code file
which executes these procedures and compile that file into your project.
Then in your code you could call the procedure like this:

DataSet results = dal.AddIncident("testname",null,null,4,...);

You get the best of both worlds, plus you get typesafe binding of your
application code to your database interface, so if you pass a string where
you should pass an int, the compiler will complain.

David
Click to expand...
 
Angelos Karantzalis said:
Actually, since the xml contains the parameter data-types , I can enable the
"complain in case of bad parameter data type" feature in the component ...
right now, I've flag in place to enable/disable that type check.
Click to expand...

Yes, but at runtime, not compile time.
Code generation is something I've thought about (actually, i do generate
"wrapper" classes around the data access component in a few cases ), but I
want to avoid re-compiling & re-deploying every time I need to add another
db operation ...
Click to expand...

I hear people saying this occasionally and it has always puzzled me. You
wouldn't ever access a .NET library through late-binding or reflection just
to avoid recompiling when the library changes. You wouldn't use function
pointers and your own method signatures in C to avoid including a header
file. Why treat the database differenly?

IMO if your database interface changes you better recompile. And you better
hope that the compiler catches all of the consequences of the change in your
code. Otherwise you application will break after it's deployed.

David
 
Yes, but at runtime, not compile time.
Click to expand...

.... well, yes. That's true. BUt it;s the penalty you pay for the flexibility
I'm afraid.
I hear people saying this occasionally and it has always puzzled me. You
wouldn't ever access a .NET library through late-binding or reflection just
to avoid recompiling when the library changes. You wouldn't use function
pointers and your own method signatures in C to avoid including a header
file. Why treat the database differenly?
Click to expand...

Well, it depends on the type of application you're building. My own is based
on messaging, where I don;t really know what types of message will come inot
play in the future. In that context, I've no problem with loading assemblies
& class instances dynamically ( although all of them implement a well-known
interface, so it's not really using reflection etc etc ).

When my main server is located across town, and it's mission critical that
it remains in operation 27x7, I find the flexibility of updating stuff at
runtime extremely valuable.
IMO if your database interface changes you better recompile. And you better
hope that the compiler catches all of the consequences of the change in your
code. Otherwise you application will break after it's deployed.
Click to expand...

What do you mean by that ? A change in the stored proc parameters for
instance ? Well, yes. If someone comes and pulls tha rug from under your
feet, you'll most probably fall :D I don't see how having db-specific
compiled code would help me there either ... on the other hand, I still
wouldn't have to write a single line of db code ... all the changes would
have to occur at the business object level - the same as if I were using
db-specific compiled code. Only in that case i'd have to write a few
thousand lines of SqlCommand cmd = new SqlCommand() code as well ... :?

To make a long story sort, I prefer not having to write code to do a
tedious, repetitive task like calling a stored procedure. I like the ease of
the external configuration, and the fact that the same carefully created
code will handle all the db calls, which leaves little margin for code
error. Perhaps that will incurr a small performance penalty (?), but i think
that the overall gain justifies it. But that's just me ... :]

Thanks for the comments though, it's all always good to talk :)

Angel
O:]
 
Back
Top