Need starting advice...

  • Thread starter Thread starter Kurt Hill
  • Start date Start date
K

Kurt Hill

I am writing a database application for use in disconnected situations.
The application will be installed on laptops. The intention is for
the users to connect to the central SQL Server 2000 database, get the
data, then go into the field and use it. They may be disconnected for
several days. They data needs to persist through application closings,
power offs, etc. When they are done, they will reconnect and their
changes will be sent back to the database.

I am new to .NET, so am looking for some advice on how to best approach
this.

Thanks,
Kurt
 
1. Set up MSDE (SQL Server 2000 free) or SQL Express 2005 (this is fine as
long as you do not use the SQL 2005 features) on the laptop. This is your
primary data store. Thsi covers persistence. You could also use CSV files,
XML files, etc., but SQL Server is useful at a later step.

2. Look for articles on mutli-homing of a computer. They will provide the
easiest method of having the computer know where it is at and only reply
when hooked up on the correct network. The goal here is having the computer
know where it is at and then try to connect. The main goal here is only
attempting to synch when you know you are on the right network. It is not
mandatory, but certainly makes things easier, as you do not have to
multi-thread the application to get work done while it is looking for
something it will never find.

3. Even if #2 is not completed, you will have to see if you can connect to
the server. If so, syncronize the data. Most likely, you will need a two-way
synch. Transactional replication works well for this (SQL Server specific).
If not, you will have to upload information and then grab a complete
snapshot for the laptop.

In many ways, I prefer SQL Express to MSDE, even if your main data store is
SQL Server 2000, as it has a few nice features that can help you with other
aspects of programming. I am also not completely sure MSDE can easily
participate in a synch, so Express may be better here.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************
 
Gregory,

You are suggesting a subscription model, is that correct? I've been
considering that as well. In the books, it is stated that ADO.NET
captures not just the data, but the relations as well, however in
looking at the XML schema, I find no indication of this -- I am guessing
it captures relations based only on the SQL used to populate the
dataset, not the relations from the underlying tables... For instance,
a single table select has no relation information, but a JOIN select
would contain the relationship info in the schema??
 
Bill,

Yes -- it kind of confirms what I wound up thinking last night -- that
replication would be the way to go -- let the DBMS handle the ugly
details of relationships, etc. However, I am having some trouble
finding code to get me started... I need to have the client computers
subscribe automatically, so I need the TSQL/VB code to get that started.
The articles I found on MSDN start by saying "Ensure the client has
the schema..." (meaning the first step is to restore a backup copy of
the database). Isn't there a way in TSQL to create a subscription and
use the snapshot on the server as the initial source of data/schema?
Lastly, the example I find to start the agent are command-line examples.
I need to be able to start the agents programatically -- preferably
without starting a shell to run a batch file (I find that ugly...!)

Thanks again!
Kurt
 
Frankly, over the last 10 hours I'm beginning to think that replication
might be more trouble than it's worth. I've heard from an expert (that's
done it many times) that it can take 200 lines of code to setup SQL Server
Express to handle replication. While it has taken far less (about 50 lines)
to handle most of the issues with SQL Server Everywhere, it's still pretty
fragile.

Perhaps a hand-rolled approach would be better. I need to think about it for
awhile longer before going that far, but from what I've experienced over the
last 10 days fighting with replication, I'm leaning that way.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I've had my share of "battles" with replication -- getting it to run
stablely, dealing with necessary permissions (rather than the general
approach of everyone is SA)... Dealing with replication over the
internet to non domain servers as well as domain servers... I have yet
to find a book that adequately addresses the "real world" where nothing
is every as nice and easy as they would have you believe ;)

Although there may be many, many lines of SQL code to call to create a
subscription, it should be mostly boilerplate, I would think -- the
things that would change are the connection string, credentials, pub
name and destination database... It seems like those 200 lines of code
should be wrapped in a component and forgotten! Wanna co-author one
with me?

For now, I am taking the quick and dirty path -- a few routines to copy
data into a local Access MDF. I neet to get version 1.0 out the door in
a month or so...
 
Ah no. Never ask a woman about to give birth if she wants more kids. While
I'm not a woman and not about to give birth, I've spent two years on this
book and I'm ready to do something else. ;)
If you write your book, I encourage you to put yourself in the reader's
shoes. Writers that know the technology backwards and forwards might not be
able to empathize with the reader and their state of understanding. Get
someone who does not know the concepts to review it. Write about how to
solve a problem, not how to instantiate objects and set properties.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Ah no. Never ask a woman about to give birth if she wants more kids. While
I'm not a woman and not about to give birth, I've spent two years on this
book and I'm ready to do something else. ;)
If you write your book, I encourage you to put yourself in the reader's
shoes. Writers that know the technology backwards and forwards might not be
able to empathize with the reader and their state of understanding. Get
someone who does not know the concepts to review it. Write about how to
solve a problem, not how to instantiate objects and set properties.
Click to expand...

Amen Bill!

Instantiate; That means create doesn't it?
Snicker, Snicker ;o)
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
Back
Top