Page 1 of 1

Using local XML file for DataSet based lookup table Rate Topic: -----

#1 ravenswood1000  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 22-June 10

Posted 03 July 2011 - 06:14 PM

In so many different projects we often refer to the Database for nothing more than support information in the form of 'lookup tables'. Items such as inventory parameters, zipcodes/citys/states, and combo box values are read over and over again from the Database during the course of software use and this can take a good amount of time and network resources.

Consider the use of auto fill zipcode/city/state fields where a user enters a zipcode and the software fills in the city and state textboxes. Sure this will not take long to query the Database but the pause is noticible to the user and if this lookup takes place hundreds of times a day, this wasted time will add up.

To avoid this time we have two solutions. The first being, query the Database at program start and fill a DataSet with all the zipcode/city/state information. This is not a very good solution though in that zipcodes rarely change and the recordset would amount to about 49,000 rows. The second and best solution would be to write everything to an XML file, store it locally, then fill a DataSet from the XML.

To accomplish this we need a Database with all our zipcode information, a routine to write the XML and a routine to read it.

Lets assume you have a MySql Database with all the Zipcode, City, State, AreaCode information installed in it with the Zipcode as the primary key. The code to write the XML looks like:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdWriteXML.Click
        Dim dbdbase As String = "zipcodes" 'set up the database parameters
        Dim dbhost As String = "localhost"
        Dim dbuser As String = "root"
        Dim dbpass As String = ""
        Dim path As String = Application.StartupPath 'set the destination path
        Dim sql As String = "select zipcode,city,state,areacode from zipcodes" 'sql to pull the desired items
        Try 'try and open the database
            conn = New MySqlConnection("Server='" + dbhost + "';Database='" + dbdbase + "';UID='" + dbuser + "';Pwd='" + dbpass + "'")
            conn.Open()
            Dim myDataSet As New DataSet("zipcodes") 'create a new dataset
            Dim myAdaptor As New MySqlDataAdapter(sql, conn) 'cram that into a data adaptor
            myAdaptor.Fill(myDataSet, "zipcodes") 'fill up the data adaptor
            myAdaptor.FillSchema(myDataSet, SchemaType.Source, "zipcodes") 'fill up the data adaptor with table schema (primary key)
            myDataSet.WriteXml(path + "\zipcodes.xml", XmlWriteMode.WriteSchema) 'write it all out in a datatable sort of way to the filesystem
            myDataSet.Dispose() 'destroy our dataset
            myAdaptor.Dispose() 'destroy our dataadaptor
            conn.Close()
            conn.Dispose()
        Catch ex As MySqlException
            'do whatever you want to.
        End Try
    End Sub


The database connection as presented here, is pretty much just that, presented so you can see we do read the information from the Database and how it might be layed out. Same with the save path. Your milage may vary.

The major points to this are:

myAdaptor.Fill(myDataSet, "zipcodes")
myAdaptor.FillSchema(myDataSet, SchemaType.Source, "zipcodes")
myDataSet.WriteXml(path + "\zipcodes.xml", XmlWriteMode.WriteSchema)



First we fill the datadaptor, then fill it with the Database Schema. This is crutial because it allows us to search the zipcodes as if they were the primary key. In the next line we write it all to XML including that Database Schema.

Next we need to read this XML data and place it into a usable DataSet. Loading of the DataSet might take place at program start, form load, or a zipcode textbox event. Reading and building this could not be simpler.

mZipcodeDataset = New DataSet
mZipcodeDataset.ReadXml(Application.StartupPath + "\zipcodes.xml", XmlReadMode.ReadSchema)


Again the XML path is up to you but obviously it must be accurate. Now that we have read it, we need to use it. The code for that might look like:

Private Sub txtZipcode_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles txtZipcode.Validating
        Dim ZipcodeLookup As String = txtZipcode.Text
        Dim foundRows As DataRow = mZipcodeDataset.Tables("zipcodes").Rows.Find(ZipcodeLookup)
        If foundRows IsNot Nothing Then
            txtCity.Text = foundRows(1).ToString
            txtState.Text = foundRows(2).ToString
            txtAreaCode.Text = foundRows(3).ToString
        Else
            txtCity.Text = String.Empty
            txtState.Text = String.Empty
            txtAreaCode.Text = String.Empty
        End If
End Sub


In the example above we assume that the DataSet is already loaded (at program start maybe) and the contents of the txtzipcode textbox is looked up on the validation event. If found, the txtCity, txtState and txtAreaCode textboxes are populated with the lookup results, else they are filled with an empty string. Timing the lookups have shown lookup results in results in a few thousandths of a second.

While zipcode lookup has been presented here, a vary powerful use for this might be in a PointOfSale program where there might be hundreds of products with hundreds of product attributes that all need to be displayed right now. The use for this is limitless.

Is This A Good Question/Topic? 0
  • +

Page 1 of 1