How to Sync Dataset to Database on SQL Server? Help!

G

Guest

Hi C# experts,

I am to C# and .NET.
I am writing a database application using C# and Dataset. In Sql server, I
have a Acount table which contains over 100,000 records. This table will be
accessed and updated by many users at same time. So the data in this table
keeps changing and growning.
I want to keep each user's local Dataset updated every time he does a update
or does a query. My code is like this:

myDataAdapter.update(myDataset, "Account");
//empty all the tables in myDataset
myDataset.clear();
myDataAdapter.fill(myDataset, "Account"); // fill in 100,000 records.
//myDataAdapter fills other tables.

Now I have two problems:
1. It is too slow. It takes a fows minutes to fill the database. So every
time, user changes data and updates, system hangs. User cannot bear it.
Question: how to fill in only the new records modified records in SQL server
to local dataset?

2. In the application, I have datagrid and other controls to represent data.
Every time when I refill the dataset, the cursor position is changed.
Question: How to keep the cursor at same position at same place after
refilling? Because I have many controls for different tables, it is not a
good option to first remember all the curser positions and go back after
refilling.

Thanks,
 
C

Cor Ligthert

Karl,

The biggest problem even when you use timestamps is to get the deleted rows
from your database.

Therefor are not much standard solutions in my opinion.

This is in my opinion one of the reasons why huge datasets don't work in a
kind of online operations in ADONET.

Just my thought,

Cor
 
G

Guest

Hi Cor,

So is there any general guidline on when to use ADONET or when to use
connected layer of ADONet and when to use Dataset?

Thanks,

Karl
 
P

Peter Bromberg [MVP]

I think the concept of having a local application that keeps a DataSet of
100,000 rows and expecting to be able to synchronize multiple users with
this same arrangement is an architectural design flaw, to begin with.

Why is it that every user must have a complete set of the data to work on
locally? Can't they work with a small set of it on an "as needed" basis?

Just my 2 cents.
--Peter
 
C

Cor Ligthert

Karl,
So is there any general guidline on when to use ADONET or when to use
connected layer of ADONet and when to use Dataset?

Not that I know and I cannot say that you will not need huge datasets. (by
instance in disconnected scenario's).

However in general is the opinion that let say in semi connected scenario's
the goal is to keep the dataset as small as possible. (Only that what is
needed.)

I hope this helps

Cor
 
G

Guest

Hi all,

Thank you all for your opinion.

I would say that my design might have some flaw. I probablly should not put
all the records in dataset. The reason I did so is that the system will be
used in multi-user enveronment, and it is very often that the user A will use
the ID created by User B 5 minutes ago. Using the subset of record for
dataset, I still somehow have to solve this problem. I will try to use
datareader to search the ID created by User B, and if it is there, then
refresh Dataset. I hope it will work. If you see something wrong in this
logic, please let me know.

Thanks again everybody!

Karl
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top