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.
MultiQuote
| 