by Ross Mack - GUI Computing
Verona, Night time.
Chuckles and Tubblio, two guards, are standing on a bridge.
Chuckles: Behold, Tubblio. My grid is arranged, just as I would like it.
Tubblio: In sooth, Chuckles, the arrangement of your columns is a pleasure to behold. It remains only to find safekeeping for this layout. For if you do not I fear it shall be lost to you.
Chuckles: Mayhap I shalt stream it out to yonder hard drive where it may remain safely concealed. From thence I can retrieve it later with great ease. ADO 2 will suit my purpose.
In recent months ADO has become the database programming interface of choice. It's lightweight, easier to use, and actually has many more features than it predecessors (DAO and RDO, mostly). One of the features I was very excited to see included in ADO 2.0 (and now 2.1) was the ability to persist recordsets to local files. However, I went for a long time before I found a use for this feature.
The obvious sort of work you do with databases is connect to a big one somewhere and let your user interact with it by adding and deleting records or doing queries and so on. So, why would you want to save a recordset to your file system? Well, most of the time you wouldn't. But here is one circumstance where it was very useful and some sample code to illustrate how it is done. It might inspire you with a few uses of your own. The code also uses a few other features of ADO that you may find of interest.
Working on a large project we encountered a situation where our acceptance testers came up with a requirement where they would like their grid layouts saved. Different users were interested in different information to be displayed in the application's main data grids. They weren't that different, but different enough. Additionally, implementing this sort of feature would allow users to save their column widths and such so that they could always see what they wanted. It was also a nice feature to add to make everyone feel warm and fuzzy inside.
Our initial investigations indicated that the custom control we used for grids would allow us to save whatever the current layout was in its own format. Great ! Except it didn't seem to work very well when you tried to switch from one saved layout to another. So, we started on our own solution.
We initially decided to write out a simple text file of column names, their position, database field and so on in a simple tab-delimited format. This worked great for a while. However, it turned out that to get the translation of the saved layout to work correctly we would need to read the format into an array, sort it and then apply the column configuration in the correct order.
Yuck. This was starting to sound like something I wasn't keen on doing. The scope of this change had crept up from a dozen or so lines of code to… well… probably several dozen. There had to be an easier solution and it struck me that ADO was it (no need to act surprised).
And so it was that I arrived at work one morning with a plan. I figured a little caffeine intake and an hour or so of coding ought to have this thing sorted. First of all I needed to generate the recordset structure, this would be taken care of once the first layout was saved, but the code would need to deal with the first time around for each user as well.
So, I created some code to create the initial recordset.
Dim rs As New ADODB.Recordset rs.CursorLocation = adUseClient rs.CursorType = adOpenDynamic rs.Fields.Append "LayoutName", adVarChar, 255, adFldIsNullable rs.Fields.Append "Index", adInteger, , adFldIsNullable rs.Fields.Append "Caption", adVarChar, 255, adFldIsNullable rs.Fields.Append "DataField", adVarChar, 255, adFldIsNullable rs.Fields.Append "Width", adDouble, , adFldIsNullable rs.Fields.Append "Order", adInteger, , adFldIsNullable rs.Open rs.Save "username.grd", adPersistADTG
As you can see it is fairly simple. First of all we create the recordset object. As it will never be attached to a database we don't use any connection information, but we do need to establish it with a client side cursor. This makes ADO use its own cursor provider as a cursor provider from a back end database is not available (due to the lack of a back end). Then we simply add the fields we want to the recordset's fields collection. We are actually creating these fields out of thin air - this is not a representation of an existing table somewhere.
A couple of quick notes about the creation of these fields. I used a length of 255 for all the string/varchar type fields as it would be more than enough for anything we would want to store. There is no reason why I couldn't have used 200 or 300 as their size, I am just used to 255. You will also note that I don't specify the size of the integer columns. This is because ADO knows how big they are and I can't override it anyway, so I just let ADO nut it out. I have also passed a simple value indicating that any of these fields can be null, just to make things easier.
Once the fields in the recordset have been created, you use the Open method to turn what you have set up into a real recordset.
Then you can use the Save method to write it out to a file. The first argument to the Save method is the filename. We actually used the username of the currently logged on user as the filename and GRD as the extension. Any name is fine, ADO doesn't really care. The second argument to the Save method is an identifier specifying the format of the file. Only one format is supported, so it wasn't difficult to figure out what value to use.
So, now that we have the structure of the storage set up the code needed to be added to actually save the data. This code was adjusted a couple of times before we got it right, so there are one or two tricks. Here it is.
Sub SaveLayout(ByVal sLayoutName As String, grd As TrueOleDBGrid60.TDBGrid) Dim rs As New Recordset Dim col As TrueOleDBGrid60.Column rs.CursorLocation = adUseClient rs.CursorType = adOpenDynamic rs.Open "username.grd", "PROVIDER=MSPersist" rs.Filter = "LayoutName = '" & sLayoutName & "'" Do While Not rs.EOF rs.Delete rs.MoveFirst Loop For Each col In grd.Columns rs.AddNew rs.Fields("LayoutName").Value = sLayoutName rs.Fields("Index").Value = col.ColIndex rs.Fields("Caption").Value = col.Caption rs.Fields("DataField").Value = col.DataField rs.Fields("Width").Value = col.Width rs.Fields("Order").Value = col.Order rs.Update Next rs.Filter = "" rs.save "username.grd", adPersistADTG rs.Close Set rs = Nothing End Sub
Our whole app uses the OLEDB grid version of TrueDBGrid 6.0. We took advantage of this to closely type the input arguments. You could easily adjust this for whatever grid you use.
Essentially we first set up the recordset object, putting it into an appropriate lock mode and ensuring a client side cursor is used. Then we simply load the recordset from file using the Open method. We pass the filename as the first parameter and the Connection information as the second, in this case as we are using persisted recordsets as the data store as opposed to a real database we use
"PROVIDER=MSPersist" as the connection argument.
Once the recordset is open we then need to restrict down to just the records we are interested in. In this case we have a field called LayoutName which identifies each layout we might store in the recordset. We use the inbuilt Filter property of the recordset object to filter the recordset back to just the records concerning the layout we want. If the layout has never been saved before this will result in no records being visible in the database, if it has been saved before we will end up with only the records with that LayoutName being visible in the recordset. Without being able to execute a statement like
"SELECT * FROM username.grd WHERE LayoutName = 'Guff' " against a database back end this is the easiest way to identify those rows in the recordset. As we want to replace those rows we then iterate through to the end of the recordset, deleting each row. Remember that only the rows with a matching LayoutName will be visible in the recordset at this time, so until we clear the filter property that is all we can effect.
We then simply use a For Each statement to iterate through the columns collection of the grid we passed to this function. We extract the various attributes that we want and write them into the recordset. Pretty simple stuff.
Once all the new data we want added has been written to the recordset we need to clear the filter property so that all the data (the records we just added and the records relating to other LayoutNames) is visible. If we don't do this before we Save the recordset again we only get the data that the Filter property makes visible. This would mean we would lose any data associated with any other LayoutName. Remember the Save method writes a completely new file to the same filename overwriting whatever else may have previously been there. For this reason you need to Load and Save all the data you want to keep every time. Writing to a local file is very quick, though, so performance isn't much of an issue.
Now we come to the loading of the data back into the grid. Or into the grid's column configuration, at least. This is again fairly simple. In fact the code is not dissimilar to the Save procedure.
Sub LoadLayout(ByVal sLayoutName As String, grd As TrueOleDBGrid60.TDBGrid) Dim rs As New Recordset Dim nNum As Integer rs.Open "username.grd", "PROVIDER=MSPersist" rs.Sort = "Order" rs.Filter = "LayoutName = '" & sLayoutName & "'" If rs.RecordCount > 0 Then rs.MoveFirst End If Do While Not rs.EOF nNum = rs!Index grd.Columns(nNum).Caption = rs!Caption grd.Columns(nNum).Width = rs!Width grd.Columns(nNum).DataField = rs!DataField grd.Columns(nNum).Order = rs!Order rs.MoveNext Loop rs.Close Set rs = Nothing End Sub
As you can see, it too is pretty simple. It really demonstrates the cool thing about these features of ADO, in that once you have a recordset, it's a recordset just like any other. That gives you a lot of flexibility.
This time when we open the recordset we first of all sort it so that when we iterate through the column information we have stored we do it in the order we want. Then we apply a filter so that the only data visible in the recordset is that concerning the layout we are interested in. If this resulted in getting any records at all we then issue a MoveFirst to ensure we get all the columns for this layout. Once we have the data all set up the way we need it we just iterate through applying the column attributes. It is important to use the index property of the column that was saved earlier to locate the column to adjust. This ensures that you only manipulate each column once. This is a bug we ran into a couple of times. Because you are rearranging objects, if you just blindly iterate through them you can be effected by previous order changes and apply modifications to the wrong column. Once all the column properties have been applied you can simply shut the recordset.
As you can see, using persisted recordsets is a viable and useful option for some tasks with the advantage that they have almost all the convenience of a normal database based recordset. If you are already using ADO in your application the overhead in using it for this sort of work is minimal. If you are not using ADO in your application, I think you should be asking yourself why not? A module containing all the code in this article is available for download.