|
Store Large Lookup Tables in DataSets
Persist DataSets of lookup information as local XML files so disconnected laptop and Pocket PC users can search and update DataSets quickly.
by Roger Jennings
Posted May 3, 2004
Technology Toolbox: VB.NET, SQL Server 2000, XML, Visual Studio .NET 2003
A best practice when generating DataSet objects is to minimize the number of records returned by SqlDataAdapter objects. This works for VS.NET applications whose users have direct network connections to the underlying databases. However, this recommendation doesn't apply to seldom-connected mobile users who must look up records and edit data while they're in customers' offices, at pipeline pumping stations, on tankers at offshore terminals, or in other remote locations. Offline updates and inserts must be preserved between device reboots and survive updates that fail from concurrency conflicts.
One approach is to set up merge replication with the Microsoft SQL Server Desktop Engine (MSDE) 2000 on laptops or SQL Server CE on Pocket PCs. An alternative method is to persist large DataSets of lookup information, such as product specs, customer records, recent order data, and pending updates and inserts as local XML files. In this article, I'll describe the benefits and drawbacks of saving, loading, and refreshing locally persisted DataSets that range in size from 250K to more than 20 MB. You can run your own tests with the OakLeaf Consumer Electronics' (OCE) OCETestClient VB.NET project I've included with this article (download the project here). It generates resource consumption and performance metrics, which I'll discuss later.
Caching product data for reference or order-entry/order-editing operations is a common requirement of usually disconnected client apps. In most cases, users download an initial set of catalog information and refresh the data periodically when connected to the database by a VPN. Users without a LAN or VPN connection can consume a simple SQLXML 3.0 stored procedure Web service that returns individual DataSets for product categories and line items or a nested DataSet with categories and items tables. Secure Sockets Layer/Transport Layer Security (SSL/TLS) encryption protects confidential information over a direct client to Web service end-point Internet connection. Alternatively, you can use Web Service Extensions 1.0 or later to encrypt the Simple Object Access Protocol (SOAP) return messages.
Write code similar to this to create a new DataSet (dsProds) from a simple Web method (getProds) that delivers a single product DataSet and persists its schema and data as an XML file:
'Create the DataSet and XML file from an
'SQLXML3 Web service
Dim prxGetProds As New getProds.ProdWS
dsProds = New DataSet
dsProds = prxGetProds.getProds(0)
dsProds.WriteXml(strFile, _
XmlWriteMode.WriteSchema)
The OCE_Prods sample database's ProdWS Web service delivers detailed data for 695 consumer electronics products and generates a 195K dsProds.xml file (see Listing A). Invoking the getProdCats Web method creates a 3K dsProdCats.xml file with 16 category codes and descriptions (see Listing B). A dsProds.ReadXml(strFile, XmlReadMode.ReadSchema) statement loads the persisted file into a new DataSet when users start a session. Ordinary users don't update product catalogs, so you needn't be concerned with tag names, primary or foreign keys, and relations. Use integer table and column indexes to populate dropdown lists or DataGrids and avoid the resource consumption of strongly typed DataSets.
Create Editable DataSets
Creating editable DataSets from Web services or by using the SqlDataAdapter.Fill() method requires conforming Web service rowset tag names to the SqlDataAdapter's NewDataSet and removing a spurious Web service attribute (see Listing 1). The OCE_Cust sample database's Customers table uses an identity column as the primary key (see Listing C). These statements initialize the dsCusts DataSet with the updated data and set the PrimaryKey and AutoIncrement properties of the first (CustID) column:
dsCusts.AcceptChanges()
tblCusts = dsCusts.Tables(0)
Dim datCol(1) As DataColumn
datCol(0) = tblCusts.Columns(0)
tblCusts.PrimaryKey = datCol
datCol(0).AutoIncrement = True
Back to top
|