The power of client/server database engine making it the number one choice for database system, especially in organizations consist of more than a couple of people. But there are a lot of times that we want our data to be available for manipulation (at least for viewing) while being disconnected to the server. Data for traveling salesmen would be the obvious example.
Data being available without actual connection to the server usually referred as briefcase mode/feature. To support briefcase mode, database library must have at least these four abilities.
- Ability to manipulate records (view, update, insert, and delete) when being disconnected.
- Ability to store records in file.
- Ability to load records from file.
- Ability to apply changes (updates, inserts, and deletes) when connection to server becomes available.
Delphi veterans would immediately yell ClientDataset (yeah, yeah, I hear you!). But do you know that Delphi ADO library also has these abilities?
1. Ability to manipulate records when being disconnected
For this ability, you must set these 3 properties of your ADO datasets.
- CursorLocation must be clUseClient.
- LockType must be ltBatchOptimistic.
- Connection must be nil.
For connection, you can set it in runtime after you have retrieved records from database server. The other two can be specified any time, including design time.
2. Ability to store records in file
TCustomADODataset, the ancestor of all ADO dataset components, has SaveToFile method. We can use this method to store the content of the corresponding ADO dataset to a file. We can save the records in ADTG (Advanced Data Table Gram) or in XML (eXtensible Markup Language) format.
3. Ability to load records from file
TCustomADODataset also has LoadFromFile method, which we can use to load an ADTG or XML file into the corresponding ADO dataset.
4. Ability to apply changes When Connection Becomes Available
TCustomADODataset also has UpdateBatch method which will apply changes detected in the "briefcased" ADO dataset to database server. Of course previously we need to specify proper and active TADOConnection.
Demo project for this tutorial is quite complex, since I want to show you both modes ("normal" and briefcase modes). So please download the demo project from the link in the end of this tutorial and use it to examine the briefcase mode explained here. Of course before you run the demo project, you need to "install" the database used in this demo project. The DDL and sample data files is included in the zipped demo project source.
A. Setup the Database and Table
Find file named tables.sql in the attached zip file. Now log on to your SQL Server instance, then create a new database. I named mine CodeCall. You could use that name, but of course you are free to use any name you like.
Use that database, and then execute the scripts inside tables.sql to get a new table named Members filled with some sample records.
B. Setup the Demo Project
- Extract the demo project source codes to the folder of your choice.
- Open the demo project in your Delphi IDE.
- Adjust the connection string of ADOConnection1 component in the main form to point to your SQL Server instance and that database you have setup in step A.
- Save that project, and compile.
2. Running the Demo Project
- Upon running the demo project, you would get something like the following.
- Click on both Open buttons. The datagrids will be filled, like shown below.
- Now click on the briefcase datagrid, scroll to the last record using, and then scroll down one more time. This will add new record to the datagrid. Type in "Usr01" under User Name column, and "Usr01 Fullname" under Fullname column (without the double quotes, of course).
- Scroll down one more time using the down arrow key to add new record and post the previous record. Type in "Usr02" under User Name column, and "Usr02 Fullname" under Fullname column (again, without the double quotes). After that scroll up using up arrow key. This will post that record.
You will get something like this.
- Note that the last two records in the briefcase datagrid do not have value in their ID field. That because the field is an identity field which content generates automatically by the server. The fact that the field is empty means that the records have not been processed by the server.
You can click on the Open button in the Real Time section, and you will still get the current content of Members table in the real time datagrid. And it won't have the new two records.
- Now click on Save to File button. The content of briefcase datagrid will be stored in a file named briefcase.dat in the same folder as the demo project executable.
- Close the demo project.
- Re-run the demo project. And click on Load from File button. This will load the content of briefcase.dat file back to the briefcase datagrid. You would get something like shown below.
Note the Open Connection button? That caption indicates that the connection to the SQL Server is not active. Means there is no way the briefcase dataset has active connection to the SQL Server instance. Means the shown records are offline.
- Do steps describes in #3 again, but this time type "Usr03" under User Name, and "User03 Fullname" under Fullname field. Scroll up one time to post this new record.
- Click on the Open Connection button to create active connection to our SQL Server instance. The caption of that button should change into "Close Connection".
- Now that active connection to our SQL Server instance is available, let's submit the changes to the actual database. Click on Apply Changes button. You would get something like shown below.
Note that the ID field of the new records still show 0s. That is because the changes were sent to the database, but the current values still have not been retrieved. Let's retrieve the current records of Members table.
- Click on briefcase Close button. And then click on both Open button. Now you will get the same records in real time and briefcase datagrids. This means applying update process has been done correctly.
And here is the source code of the demo project along with Members table DDL and some sample records.
demo.zip 1.22MB 669 downloads
Edited by Luthfi, 28 June 2013 - 05:47 AM.