DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Generalized Code

  1. #1
    Paul Singh Guest

    Generalized Code

    Hi there,
    Can anyone tell me what is the best approach for writing an application to
    talk to database when database schema will change in future? At present we
    have an older version which will get upgraded to a new one and some data
    columns might change. I want to design a data layer that should be flexible
    enough to work with changed schema as well. I am thinking stored procedures
    might be one option but then again that will get tied to one database? Any
    other suggestions? Generating dynamic SQL statements? Any ideas regarding
    how to design this? I am kindda stuck. Thanks for your feedback.

    paul.



  2. #2
    Michael Guest

    Re: Generalized Code

    Paul,

    I'm hoping a DBA or equivalent experience will also have some input here
    butt...

    I suggest first querying the system databases or [sysobjects] to get a table
    name and then its properties like columns, data types etc, then based on
    that, loop thru the objects and create a SQL query from that loop. You
    should then be able to query the table despite a possible change. Good luck!

    --

    Michael A Sanchez
    Runtime Web Development
    Dynamic Websites
    Business Applications
    Web: http://www.RuntimeWebDev.com
    eMail: MSanchez@RuntimeWebDev.com


    "Paul Singh" <psingh@trendmls.com> wrote in message
    news:3ef1c9fb$1@tnews.web.devx.com...
    > Hi there,
    > Can anyone tell me what is the best approach for writing an application to
    > talk to database when database schema will change in future? At present we
    > have an older version which will get upgraded to a new one and some data
    > columns might change. I want to design a data layer that should be

    flexible
    > enough to work with changed schema as well. I am thinking stored

    procedures
    > might be one option but then again that will get tied to one database? Any
    > other suggestions? Generating dynamic SQL statements? Any ideas regarding
    > how to design this? I am kindda stuck. Thanks for your feedback.
    >
    > paul.
    >
    >




  3. #3
    Paul Singh Guest

    Re: Generalized Code

    Hi Michael,
    Thanks for the reply. I was thinking along the same lines as well. But
    couple of problems came to mind. Current database we have was exported from
    an old software so it doesn't have all the relationships (foreign keys)
    required to do the necessary joins. So a mapping will have to be created to
    define the relationships. Then based on sysobjects or syshelp, db schema can
    be queried.
    However this seems like too much work. Will creating stored procedures work
    here? For new version i guess someone will have to tweak them to make them
    work. But the whole application won't have to be stopped for this, only the
    db part. How about creating db views? Input from user is going to be dynamic
    e.g. select this column and drop this. Even if i create dynamic SQL
    statements querying sysobjects, chances of error are a lot more and it might
    not be that efficient.

    Any ideas, thoughts? Thanks a lot for feedback!

    Paul.


    "Michael" <msanchez@runtimewebdev.com> wrote in message
    news:3ef390b4$1@tnews.web.devx.com...
    > Paul,
    >
    > I'm hoping a DBA or equivalent experience will also have some input here
    > butt...
    >
    > I suggest first querying the system databases or [sysobjects] to get a

    table
    > name and then its properties like columns, data types etc, then based on
    > that, loop thru the objects and create a SQL query from that loop. You
    > should then be able to query the table despite a possible change. Good

    luck!
    >
    > --
    >
    > Michael A Sanchez
    > Runtime Web Development
    > Dynamic Websites
    > Business Applications
    > Web: http://www.RuntimeWebDev.com
    > eMail: MSanchez@RuntimeWebDev.com
    >
    >
    > "Paul Singh" <psingh@trendmls.com> wrote in message
    > news:3ef1c9fb$1@tnews.web.devx.com...
    > > Hi there,
    > > Can anyone tell me what is the best approach for writing an application

    to
    > > talk to database when database schema will change in future? At present

    we
    > > have an older version which will get upgraded to a new one and some data
    > > columns might change. I want to design a data layer that should be

    > flexible
    > > enough to work with changed schema as well. I am thinking stored

    > procedures
    > > might be one option but then again that will get tied to one database?

    Any
    > > other suggestions? Generating dynamic SQL statements? Any ideas

    regarding
    > > how to design this? I am kindda stuck. Thanks for your feedback.
    > >
    > > paul.
    > >
    > >

    >
    >




  4. #4
    Michael Guest

    Re: Generalized Code

    Paul,

    This is still racing around my brain but a couple of quick ideas that will
    hopefully get you by are this,

    If you know what the column names always are then in your select statements
    just always use the CAST method on each column to change the datatype to
    what you want. Such as SELECT CAST(col1 as varchar), CAST(col2 as int).

    If you can't be sure of what each column is then do a SELECT * on the table
    and as you loop or extract data from the record set, cast it as the data
    type you want such as, myVar1 = cStr(objRS(0)), myVar2 = cInt(objRS(1)).

    I hope this helps to some degree for now. Have you snooped around the
    discussions on sqlmag.com? They have some excellent DBAs and developers on
    there.

    Michael A Sanchez
    Runtime Web Development
    Dynamic Websites
    Business Applications
    Web: http://www.RuntimeWebDev.com
    eMail: MSanchez@RuntimeWebDev.com

    "Paul Singh" <psingh@trendmls.com> wrote in message
    news:3ef6e413$1@tnews.web.devx.com...
    > Hi Michael,
    > Thanks for the reply. I was thinking along the same lines as well. But
    > couple of problems came to mind. Current database we have was exported

    from
    > an old software so it doesn't have all the relationships (foreign keys)
    > required to do the necessary joins. So a mapping will have to be created

    to
    > define the relationships. Then based on sysobjects or syshelp, db schema

    can
    > be queried.
    > However this seems like too much work. Will creating stored procedures

    work
    > here? For new version i guess someone will have to tweak them to make them
    > work. But the whole application won't have to be stopped for this, only

    the
    > db part. How about creating db views? Input from user is going to be

    dynamic
    > e.g. select this column and drop this. Even if i create dynamic SQL
    > statements querying sysobjects, chances of error are a lot more and it

    might
    > not be that efficient.
    >
    > Any ideas, thoughts? Thanks a lot for feedback!
    >
    > Paul.
    >
    >
    > "Michael" <msanchez@runtimewebdev.com> wrote in message
    > news:3ef390b4$1@tnews.web.devx.com...
    > > Paul,
    > >
    > > I'm hoping a DBA or equivalent experience will also have some input here
    > > butt...
    > >
    > > I suggest first querying the system databases or [sysobjects] to get a

    > table
    > > name and then its properties like columns, data types etc, then based on
    > > that, loop thru the objects and create a SQL query from that loop. You
    > > should then be able to query the table despite a possible change. Good

    > luck!
    > >
    > > --
    > >
    > > Michael A Sanchez
    > > Runtime Web Development
    > > Dynamic Websites
    > > Business Applications
    > > Web: http://www.RuntimeWebDev.com
    > > eMail: MSanchez@RuntimeWebDev.com
    > >
    > >
    > > "Paul Singh" <psingh@trendmls.com> wrote in message
    > > news:3ef1c9fb$1@tnews.web.devx.com...
    > > > Hi there,
    > > > Can anyone tell me what is the best approach for writing an

    application
    > to
    > > > talk to database when database schema will change in future? At

    present
    > we
    > > > have an older version which will get upgraded to a new one and some

    data
    > > > columns might change. I want to design a data layer that should be

    > > flexible
    > > > enough to work with changed schema as well. I am thinking stored

    > > procedures
    > > > might be one option but then again that will get tied to one database?

    > Any
    > > > other suggestions? Generating dynamic SQL statements? Any ideas

    > regarding
    > > > how to design this? I am kindda stuck. Thanks for your feedback.
    > > >
    > > > paul.
    > > >
    > > >

    > >
    > >

    >
    >




  5. #5
    Harlow Guest

    Re: Generalized Code



    The "best" approach will always depend on the specific requirements of your
    project but a good resource for guidance in designing data layers can be
    found at:

    http://msdn.microsoft.com/library/de...AppArchCh2.asp

    Section: "Designing Data Layers"

    This a .NET architecture article but the principles are abstract enough to
    apply to any data access project, especially in subsection "Recommendations
    for Data Access Logic Component Design."


    - Harlow.
    "Paul Singh" <psingh@trendmls.com> wrote:
    >Hi there,
    >Can anyone tell me what is the best approach for writing an application

    to
    >talk to database when database schema will change in future? At present

    we
    >have an older version which will get upgraded to a new one and some data
    >columns might change. I want to design a data layer that should be flexible
    >enough to work with changed schema as well. I am thinking stored procedures
    >might be one option but then again that will get tied to one database? Any
    >other suggestions? Generating dynamic SQL statements? Any ideas regarding
    >how to design this? I am kindda stuck. Thanks for your feedback.
    >
    >paul.
    >
    >



  6. #6
    Zequel Guest

    Re: Generalized Code


    Just some thoughts,
    dynamic sql statements are always appealing but the problem with them
    is that stored procedures give a good performance boost because they cache
    the query plan. I would consider mapping your data objects's properties to
    column names in a text or xml file. The xml file would contain the property
    name, column name and data type. Write a code generator/stored proc. generator
    that reads the text/xml file. That way if columns change you can merely change
    the xml and re-generate your classes and procedures.

    Albert Grimes


    "Paul Singh" <psingh@trendmls.com> wrote:
    >Hi there,
    >Can anyone tell me what is the best approach for writing an application

    to
    >talk to database when database schema will change in future? At present

    we
    >have an older version which will get upgraded to a new one and some data
    >columns might change. I want to design a data layer that should be flexible
    >enough to work with changed schema as well. I am thinking stored procedures
    >might be one option but then again that will get tied to one database? Any
    >other suggestions? Generating dynamic SQL statements? Any ideas regarding
    >how to design this? I am kindda stuck. Thanks for your feedback.
    >
    >paul.
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center