Data Access Software Architecture?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tim Smith (timasmith_at_hotmail.com)
Date: 03/14/04

  • Next message: Bryan Tang: "Re: GetFolderPath Problem" 
    Date: 13 Mar 2004 17:39:26 -0800

    Hi,

    Does anyone have any professional experience on how to create business
    applicaitons that are heavy on database manipulation. Specifically
    managing the business logic which involves pulling reference data from
    many tables and updates/inserts into many activity tables.

    At one point I thought it might be useful to have a class per table
    which provides the basic insert/select/update/delete functionality.
      public class MyTable {
          private int my_table_id = 0;
          private String my_description = "";
          ...
          public insert();
          ...
          public select(int pkey_table_id);
      }
    Then you could have classes that are composed of many table classes or
    arrays of table classes. Those classes represent the data model
    hierarchy in some fashion.

    However this is perhaps not efficient for updating large numbers of
    rows and the base class would need to get regenerated after any schema
    changes.
    --------------
    Perhaps a more efficient way is to move all the direct data access to
    stored procedures in the database. Throughout the class code we call
    the SP, passing and returning data values (and hoping the conversion
    works out ok I guess). I suppose you could have a class that groups
    related tables and has its own set of stored procedures. At least you
    would have compile time checking of your SQL.
    --------------
    I sometimes find not being able to see SQL in my code a little
    frustrating when you are reading code - and god forbid if you start
    sharing a Stored Procedure...

    Some languages like Powerbuilder are able to retreive views or tables
    joined, update the resultset and save back to the server. Are there
    any options for this kind of functionality?
    --------------

    At the end of the day I want to work with objects, but they must
    retreive and store data efficiently. I need compile time checking of
    my SQL and I need to have it easier to work with a hierarchy of
    related tables for updating. If I update one field only, an resulting
    SQL update of one time only should be generated. I do not believe the
    DataSet does the updating back if it came from multiple tables.

    tia

    Tim

  • Next message: Bryan Tang: "Re: GetFolderPath Problem"

    Relevant Pages

    • Data Access Layer Architecture?
      ... applicaitons that are heavy on database manipulation. ... related tables and has its own set of stored procedures. ... would have compile time checking of your SQL. ... I sometimes find not being able to see SQL in my code a little ...
      (comp.lang.java.programmer)
    • Data Access Architecture w/ OOP
      ... applicaitons that are heavy on database manipulation. ... related tables and has its own set of stored procedures. ... would have compile time checking of your SQL. ... I sometimes find not being able to see SQL in my code a little ...
      (comp.object)
    • How do I do Paging through a large dataset via Stored Procedures
      ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Help with Stored Procedure
      ... I did mean stuff like system stored procedures (even ... build the query, compile it, and optimize it, then, then this is less ... very not easy using dynamic sql. ...
      (microsoft.public.sqlserver.programming)
    • Re: choices regarding where to place code - in the database or middle tier
      ... Sure, the DBMS is a good place for simple referential integrity constraints, ... to 4 separately-running-but-pipelined stored procedures, ... A typical user would enact a 100 or so business functions per day. ... own stored procedures' by storing the SQL for every business query in the DBMS ...
      (comp.lang.java.programmer)