Database programming in a disconnected environment

This is a pre-release post!

How many time have you encounter with this problem? During the course of my carrier 99.9% of the time I need to write database application in a disconnected manner. I decided to write this up and if you got some gut to try it as your assignment ;)



  • Application that run on a single workstation
  • Be able to consolidate data from multiple data from the workstation (incremental dataset)
  • Allow DBA to consolidate & tracking changed records from each station (for various reasons).
that sound simple enough, now let talk about the infrastructure:
  • No WAN available (CRUD process on a single machine)
  • Each station rely on slow e-mail connection to submit update dataset
Let try to solve this problem base on OOP language
  • ASP.NET 2.0 (? I hope one day all station got will be on WAN)
  • Data Transfer Object (DTO) + use internally by DAL
  • Implement Data Access Layer (DAL) + support Import/Export functionality.
  • Business Logic Layer (BLL) -- interact from user interface (form), use DAL internally
Data Structure Convention:
  • Avoid using composite primary key
  • Recommended field name:
    IsActive = T/F,
    IsDeleted = T/F, -- logical deletion
    CreatedBy NVARCHAR(50),
    CreatedOn DATETIME,
    ModifiedBy NVARCHAR(50),
    ModifiedOn DATETIME -- using this field to extract data into DTO object
  • DataSyn table to store the status of data extraction
  • Obsolated table to store deleted record of each table.