[AccessD] New database design for MS SQL

The concept of a dataset in .NET is that of a database container. The
dataset contains datatables. In essence you can copy the whole database
structure into memory so that you can minimize roundtrips to the server.
Once you have the tables or recordsets in memory your app can process data,
apply business rules, etc... And then you can connect back to the database
and update the database tables using your updated recordsets or datatables.

In web-based apps I've seen code that creates an instance of the dataset
using session variables (a pointer to the actual dataset). Anytime the
application needs the dataset it just needs to use the session variable that
represents the dataset and perform any operations.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Friday, April 06, 2007 9:05 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] New database design for MS SQL

Our executable installs on the client at this point in its existence, and
our clients are *frequently* thick!  We haven't split the business logic off
to the server because there isn't always a server.  When I mentioned Access,
I was talking about the data store, not the front end.
Our XML files have nothing to do with Access.  FE is VS.Net but the app is
designed to work locally (say, on a rig) or with the data store in SQL
Server on a server (usually an office installation).  The application has to
be flexible enough to run either way and to be capable of switching between
the two, if need be (they tend to move their licenses around!).  Rather than
creating different versions of the app for the varying environments, we
simply built one that would work across the board.  That may change someday,
but we're still in the process of migrating our apps and our clients into
the new century. LOL

A dataset is a disconnected datastore, so it doesn't care where the BE is or
what engine it runs in.  A _recordset_ is like a query, a flat list of rows,
but a dataset represents the separate sets of rows from one or more tables.
A TYPED Dataset (as opposed to untyped) is a kind of class and models the
details of the data structure in code.  You can create them from scratch or
using the built in tools to "generate" one.
They then become objects in the project that our entity classes wrap,
exposing only the programming methods and events we want to make available.
Drop an entity on a form, and you have automatically connected the form to
the datastore the entity represents, so you can program to its columns,
methods, and properties as if it were a recordsource in an Access form.

Charlotte Foust

 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Friday, April 06, 2007 3:34 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] New database design for MS SQL

Hi Charlotte:

I will comment inline

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Thursday, April 05, 2007 2:46 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] New database design for MS SQL

No white papers.  We started rolling our own and called in a consulting firm
to review the fledgling solution and point out deficiencies.  They did admit
when they came back for a later review that they'd never seen a Win-based
.Net app this large and complex, but we aren't ready (maybe I should say our
clients aren't ready) to convert totally to web-based yet.

Comment: Sounds very impressive.  

We're using either SQL 2005 or Access 2000 format BE because some of our
clients are on drilling rigs in the middle of the ocean and don't have SQL
Server handy.  The embedded SQL for the provider adapters is stored in XML
files and we read it in using a streamreader and pass it back to the calling
data provider routine.  The only business logic on the server is in
cascading updates and deletes, etc., nothing else except relationships at
this point because we still have to handle both SQL Server and Access back
ends.  The business logic is in its own assembly that's part of the overall
solution.  Not sure what you mean by a desktop design.

Comment: 'Desktop design' is my description of an application that has it
core functionality supported at the client end; or win-based or thick client
(this could also be describing the actual client?) ...As opposed to thin
client application where most of my work seems to be migrating.
 
We create and supply software to the drilling industry--oil, gas,
geothermal--so a well is a hole in the ground plus everything associated
with that.  A wellbore, is the hole itself, which may be the original hole
in the ground or a sidetrack drilled as an offshoot of another wellbore.

Comment: A very good explanation of your business. I would be interested in
how the data tier actually works or maybe just an overview ...without
divulging any trade secrets of course... like how the data tier layer
abstracts the actual data structures. Is your office using the XML option in
.Net datasets, through classes to connect via an OLE (third
party?) to the actual tables being either MDB or MS SQL? (Handling XML in MS
SQL 2005 is built-in but I was unaware that Access had the same
capabilities.) I am not interested in specifics just the concepts...
Maybe a bird's-eye view?
 
Charlotte Foust

MTIA

Jim
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.26/748 - Release Date: 4/5/2007
3:33 PM