|
Summary
In this article I will demonstrate how to create an XML based cache system in COM using Visual Basic. This COM object is using the function Save in ADO to create XML based recordsets.
Creating An XML Based Cache Using COM
In this article I will demonstrate how to create an XML based cache system in COM using Visual Basic. This COM object is using the function Save in ADO to create XML based recordsets.
View the complete article with images at:
http://www.aspalliance.com/binarybros
The Objective of XMLCache
The objective of this COM object is to speed up the communication between ASP and your data. With XMLCache you don't have to query your database server but instead, let the XMLCache take care of your recordsets.
What do We Use?
We use Visual Basic 6 to create the COM object called BinaryBros.XMLCache. We use ADO to connect to the database server and to create and read the XML based recordsets. Finally we use ASP to connect the COM object and display the information. As long as you have ADO 2.1 or higher installed you can use any scripting language to connect to and use XMLCache.
Which information should be passed from ASP to COM?
First we need to pass a connections string to the COM object, the component needs to know where the database server is located, which database we will use, which provider or ODBC source, which user and password that will be passed. All this is passed in the property connectionstring.
Then we must provide the SQL statement. We do this in the property called sqlstring. The third property should be the filepath to our XML based recordsets. E.g. if you execute the query select * from customers your xml filepath could be:
C:\domains\yourdomain\xmlrecords\nwindcustomers.xml
The fourth and last property is the interval; for how long should your application read records from the xml files instead of the database server. You set this parameter in minutes and each recordset can have it's own intervals. If you want to clear the cache just delete all the xml files and the component will create new ones.
The COM Project Starts Here
First, we launch Visual Basic 6 and choose to create an ActiveX DLL project; this is shown in figure 1.
We know need to add some references to our Visual Basic project. Open the project menu in VB and then choose references. We must add ActiveX Data Objects and Microsoft Scripting Runtime. ADO is for all data communication and Scripting is needed for the FileSystemObject, we use that to check date- and time properties on the XML-files; see the dump in figure 2. We also need to change the project's name to BinaryBros and the class name to XMLCache; this is called the ProgID or the Class String and you use this when you execute the Server.CreateObject function.
Now we need to add some code to our project. First of all we use Option Explicit in all our projects. That is because if you haven't declared your variables properly, the VB engine have to do that for you and it takes time. Thus, always use Option Explicit and declare all objects and variables properly.
Now, we have to declare objects and variables. We declare them using Private, this means that they can only be used inside the project and they are not reachable outside the COM object as public properties. We then declare the objects, ADODB.Recordset, ADODB.Connection and Scripting.FileSystemObject. We use late binding in this project and declare them only in the beginning of the code and then use the Set egeg = New ADODB.Recordset when we use it. This is faster than early binding.
Option Explicit
Private mvarConnectionString As String 'local copy
Private mvarSqlString As String 'local copy
Private mvarFilePath As String 'local copy
Private mvarInterval As Integer 'local copy
Private recset As ADODB.Recordset
Private conn As ADODB.Connection
Private fs As Scripting.FileSystemObject
Now to the properties, how do we set them, declare them and use them?
As you might be able to see in Figure 4 we use the Public Property LET to declare a public property. We use LET when we want the property to be able to receive data from the outside and GET when we want the property to deliver data to the outside. The outside can be an ASP page, a Visual Basic application or another COM object. The properties receive data into the variable vData and we then store that data in the mvarinterval and so on, as local copies of that data. There are several ways to deliver and receive data from and to COM objects. This is the standard way used by Microsoft and several tools on the market.
Public Property Let filepath(ByVal vData As String)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.FilePath = 5
mvarFilePath = vData
End Property
Public Property Let SqlString(ByVal vData As String)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.SqlString = 5
mvarSqlString = vData
End Property
Public Property Let ConnectionString(ByVal vData As String)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.ConnectionString = 5
mvarConnectionString = vData
End Property
Public Property Let Interval(ByVal vData As Integer)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.ConnectionString = 5
mvarInterval = vData
End Property
We will now develop two functions for our COM object. The first function will receive a filepath and return how old that file is (in minutes).
We call the function checkdate and we receive the filepath in the filepath variable as a string. We have declared that the function will return an integer. Then we use the VB function DateDiff to check the difference between two dates, the parameter n in that function tells it to return the results in minutes.
Private Function checkdate(filepath As String) As Integer
checkdate = DateDiff(n, fs.GetFile(filepath).DateLastModified, Now(), vbUseSystemDayOfWeek, vbUseSystem)
End Function
Now we will build the main function called ExecuteCache. This function returns a full ADODB.Recordset to the calling application or page. We don't pass any information to this function because we have stored that information in local variables in the project.
First we create all the objects that we will need in this function.
Set recset = New ADODB.Recordset
Set conn = New ADODB.Connection
Set fs = New Scripting.FileSystemObject
' Check if the file exists
If fs.FileExists(mvarFilePath) Then
' If it exists, connect the recordset and return it
Debug.Print XML File exists
' Check the time on the file
If mvarInterval < checkdate(mvarFilePath) Then
Debug.Print XML File is old
conn.Open mvarConnectionString
Set recset = conn.Execute(mvarSqlString)
fs.DeleteFile (mvarFilePath)
recset.Save mvarFilePath, adPersistXML
Set ExecuteCache = recset
Else
Debug.Print XML File is fresh
recset.Open mvarFilePath
Set ExecuteCache = recset
End If
Else
If the file doesn't exists we need to connect to the database server and then execute the sql statement and finally save the xml file and return the recordset to the function.
' If file doesn't exists, connect, create and return recordset
Debug.Print There is no XML file
conn.Open mvarConnectionString
Set recset = conn.Execute(mvarSqlString)
recset.Save mvarFilePath, adPersistXML
Set ExecuteCache = recset
End If
Set recset = Nothing
Set conn = Nothing
Set fs = Nothing
Always debug your COM objects locally and with the settings above when you run it the first time.
The ASP page Left To Create
We need to build an ASP page that creates our COM object with Server.CreateObject and the fill in the properties with accurate information and then creates a recordset and fill that recordset with data from XML or your database server.
The connetion string that I provide in this sample page uses my local SQL Server to connect to Northwind with SA as user name and an empty password.
<%
Set obj = Server.CreateObject(BinaryBros.XMLCache)
obj.ConnectionString = Provider=SQLOLEDB;Data Source=MAVERICK;Database=Northwind;UID=sa;PWD=
Now to the properties, as you can see this recordset will cache only one minute before it refreshes itself.
obj.SqlString = SELECT * FROM Customers
obj.FilePath = e:\customers.xml
obj.Interval = 1
Set rs = obj.ExecuteCache()
do while rs.eof = False
Response.Write rs.Fields(0).Value & <br>
rs.movenext
loop
rs.close
Set obj = Nothing
%>
Author
Andreas Kviby
Binary Bros
Co-author
Robert Kviby
Binary Bros
|
Email TopXML