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.