You have or you need to develop a database. You also need to develop a client application that will use that database. Optionally, you need to develop an XML Web Service to allow the client application to access the database remotely.
You are thinking already about many stored procedures, many SQL database commands, maybe using an SQL Helper… pretty much to work…
From your experience, you know of course that ADO .NET and .NET Framework have support for the disconnected data access (using a DataSet and DataAdapter objects, or an XML Web Service). Of course, it would be interesting to cache the DataSet on the local hard disk of the client machine, so that the application initialization (load) will be a lot faster. And the client should be able to run in different security contexts (such as hosted in Internet Explorer as a Windows Forms User Control), and therefore the need for a special location on the local hard disk, such as the user’s isolated storage.
You are thinking already about saving and loading the data (using XML Serialization) in temporary files on the local disk, in the user’s application domain isolated storage… a lot of work…
More, it would be nice that the changes from the database to be periodically reflected in the local client DataSet, in background (without affecting the user’s activity within the application, as much as possible), and preserving the current changes of the data (that the user has just produced). This function becomes a requirement (at least for a one-time synchronization) if you have already chosen to cache the data on the local disk. Everything must be thread-safe, of course.
You are thinking already about DataAdapter.Fill() and DataSet.Merge(dataSet), which may look easy to use, but will not function this way in this scenario! You need taking care to correctly synchronize data (preserve current changes), locks, checking which data has modified and which has not… more than a lot of work…
And since there is a background synchronization from the database to the local data set, it would be real nice (usually mandatory) that the changes that the current user has done to the DataSet to be automatically sent back to the database, also periodically and in background.
You are thinking already about DataAdapter.Update(changes), which, again, may look very easy to use, but will not always work in this scenario! It’s easy to write a DataAdater.Update call for each table in the DataSet, but when there are constrained (usual) relations between the tables, the order of tables to update becomes critical (for additions, you need to update the parents first, then children, and for deletions, vice-versa). Also, when there are circular relations between the entities, the order of the entity instances to update also becomes critical (so you may need to update only part of the data of a table first – the parent or the children, and the other part later – vice-versa, depending whether the updates are additions or deletions). If you ignore these, DataAdapter.Update fails! You may test it if you want! And don’t forget that you need thread safety also… almost too much work…
Oh, but this is not all. The user interface of the application must be amazing: stunning graphics, dynamic layout, customizable themes, and everything else. And your customer wants a presentation (or a prototype) in 2 days.
You are thinking already: How does she want me to focus on creating a great user interface, since there are so many things to do before that! What presentation can I create in 2 days, when first I would need to review at least tons of documentation for being able to make sure that I can write an application meeting all the requirements above?… Very difficult!…
And… the budget of the project (or your salary) is a lot too low than it should be!
You are thinking already: I quit! This project, this job, whatever! I’d rather enroll in agriculture.
There is a Solution, tough
Before getting to an extreme decision, it’s good to know, tough: there is a software package which can help you very much with finalizing your project in time, and within the budget limits: DlhSoft Smart Client Application Block.
The package contains two components, DataManager and WebDataManager, which provide functions such as automatic local caching and DataSet synchronization with a data source (both ways). The data source may be specified either by setting up a list of DataAdapter objects – the DataManager component, or by an XML Web Service reference which supports standard data access Web Methods (DataSet Get() and void Update(DataSet changes)) – the WebDataManager component.
Once that you have set up a DataConnection, and DataAdapter objects or an XML Web Service that are/is capable of providing data and process data changes (using Visual Studio, for example), you simply need to create a DataManager or WebDataManager instance (which you get from the Smart Client Application Block package), set up its properties, and the local DataSet instance will be automatically synchronized to the database and cached on the local disk, without writing any code! (The DataSet can be either untyped or typed; the latter one can be usually generated from the database tables’ DataAdapters, using a Visual Studio wizard.)
You need to focus only on the user interface, and use the local DataSet as the omnipresent data source through all the user interface!
The Agenda sample application can help you see Smart Client Application Block in action, and better understand how to use the package.
The Agenda sample application can also be downloaded from the DlhSoft web site.