Advice requested for SQLCE MDI app

  • Thread starter Thread starter Garry
  • Start date Start date
G

Garry

I am rebuilding/rewriting a VB6 desktop application in VB.NET.

As a strategy, I am loading a complete SQLCE database file into a DataSet,
manipulating the data in various tables and 'pouring' the data including any
changes, back to the database file when the user closes the app.

There will also be a facility to 'time' updating the file according to the
users requirement. Default every 15 mins.

Can anyone see any basic defect in this strategy. Remember, it is a desktop
application. Single user

I already have the prototype up and working without the timer facility and
want to hear any constructive critical comments.

The performance seems good, actually fantastic, but I have not implemented
massive updates yet and I understand that the dataadapter only update those
rows which have been changed..

Database files will rarely be more than 2 MB and mostly about 800 K

Garry
 
The problem is applicaion/OS crash. You'll loose all data.
Plus, your app will consume more memory, and you'll have problems cranking
complex queries (JOINS?)
Why don't you use classic approach instead?
 
Well, I have the vast experience of the VB6 application to draw upon and I
didnt use any complex SQL then, only one JOIN in the whole caboodle.

As far as crashing goes, well, I had considered that and as WORD uses a 10
min period default to automatically save a document that you are working on,
so can I.

After reading a lot of internet material, it is still not completely clear
to me if the System.Data.SqlServerCe.SqlCeDataAdapter only transferes the
Update/Add/Delete rows back to the DataBase file. The .GetChanges function
does not seem to work always returning an empty datatable. Perhaps you could
clarify this issue for me??

It just seems so easy to use da.Update(ds,strDataTableName).

The basic reason for this approach is speed. All done in memory. Modern
machines seem to have plenty of memory available. An outsize database file
for us would be 6 MB judging from the existing Access based VB6
application.. Even that is readily available on todays machines. I have
written a small app to build an .sdf file from an existing Access database.

The user would always have a button available on the main toolbar to save
the application to file at any time.

However, after considering all this in detail, I am still somewhat uneasy
about the strategy.

Garry


Miha Markic said:
The problem is applicaion/OS crash. You'll loose all data.
Plus, your app will consume more memory, and you'll have problems cranking
complex queries (JOINS?)
Why don't you use classic approach instead?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Garry said:
I am rebuilding/rewriting a VB6 desktop application in VB.NET.

As a strategy, I am loading a complete SQLCE database file into a
DataSet, manipulating the data in various tables and 'pouring' the data
including any changes, back to the database file when the user closes the
app.

There will also be a facility to 'time' updating the file according to
the users requirement. Default every 15 mins.

Can anyone see any basic defect in this strategy. Remember, it is a
desktop application. Single user

I already have the prototype up and working without the timer facility
and want to hear any constructive critical comments.

The performance seems good, actually fantastic, but I have not
implemented massive updates yet and I understand that the dataadapter
only update those rows which have been changed..

Database files will rarely be more than 2 MB and mostly about 800 K

Garry
Click to expand...
Click to expand...
 
Hi Garry,

Garry said:
Well, I have the vast experience of the VB6 application to draw upon and I
didnt use any complex SQL then, only one JOIN in the whole caboodle.
Click to expand...

Good time to learn then :-)
As far as crashing goes, well, I had considered that and as WORD uses a 10
min period default to automatically save a document that you are working
on, so can I.

After reading a lot of internet material, it is still not completely clear
to me if the System.Data.SqlServerCe.SqlCeDataAdapter only transferes the
Update/Add/Delete rows back to the DataBase file. The .GetChanges function
does not seem to work always returning an empty datatable. Perhaps you
could clarify this issue for me??
Click to expand...

Adapter updates (transfers) rows based on DataRow.RowState and GetChanges
works on same property. They work fine if RowState is correct.
It just seems so easy to use da.Update(ds,strDataTableName).

The basic reason for this approach is speed. All done in memory. Modern
machines seem to have plenty of memory available. An outsize database file
for us would be 6 MB judging from the existing Access based VB6
application.. Even that is readily available on todays machines. I have
written a small app to build an .sdf file from an existing Access
database.

The user would always have a button available on the main toolbar to save
the application to file at any time.

However, after considering all this in detail, I am still somewhat uneasy
about the strategy.
Click to expand...

You will certainly miss transactions, IOW, if you update more records and
somewhere in between an error occurs you might end up with an inconsistent
database. And I would certainly miss complex queries. And if one day your
database grows or you'll have to switch to multiuser, you'll have problems.
I am sure there are more reasons against all in-memory approach.
However, nothing is wrong with in-memory approach if it suites you and you
are aware of drawbacks.
BTW if you are storing everything in memory you actually don't even need a
database. Just serialize/deserialize the graph and you are fine.
 
Having (just) written an EBook on SQLCe, I think your approach has a lot of
merit. Consider that the SQLCe engine (formerly known as SQL Everywhere and
SQL Mobile) is a full-blown SQL engine that can execute complex JOINs. It's
also robust. It can tolerate being shut down arbitrarily and yes, you'll
lose what's in the DataSet when power fails but not in the database
itself--not with your strategy. The current capacity is 4GB and is expected
to be increased in the near future. Support by the VS tools is there but
limited--again, MS plans to address this problem in SP1 and Orcas. Yes, I
think your application should work fine. Check out the book--it could help
get over some of the issues.

--
____________________________________
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
 
Back
Top