Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Changing Databases Using XML

By Dinesh Asanka,

Introduction and Scope

As a database developer , you may have being fed up with the never ending customer changes. Actually this is not only relevant to database developers but for all of the other developers. But for database developers has to meet the customer requirements they may need to change the databases. But if these changes are done by manually you must list out all the changes to be done and do it perfectly. It is not sound methodical isn't it? So we have to find out an elegant way of doing it.

What are the changes?

What are the changes that you may need to do for the databases? They can be creating a new table, dropping an existing the table, altering an existing table, deleting existing data of a table, inserting some data. This all the changes need not to be done to only for one database. Their can be more than one database depending on the customer's needs. For example I have a done a project which contains configuration data in one database and day to day transactions are saved in some other database.

How can we do this?

We can simply write a VB application so that it can change the databases. These changes may not be done at once. There can be changes for two three times. If we use a simple VB application every time we need to change application so that it does the necessary changes to the relevant database. Problem will become more worse if you are having several customers, and they are asking several changes. Then you will have to keep more than one EXE for this simple and straight forward task. We, our self  have made our life uneasy. Haven't we?

If we can write an exe so that it reads some other source which users can change and do the changes it will be handy. We just need to change the source file rather than the VB application. Text file would do the job for us. But XML file be the most suitable one. This gives you another advantage over the VB application. That is XML file size will be just over 2-3 KB which you can send it through an e-mail.

XML File

Let's first create the XML file.

Figure 1

Figure 1 is the listing of the XML file. we may call it SCRIPT.XML.

There are are several things in the XML file which I have not discussed earlier. First of them is the Version. This is for the reference. Version would be enough to identify the changes that has done to the database. As I discussed earlier commands are separated for the databases. Here I have used two databases which are Config and Transaction. As you can see there is a another XML tag called CopyData. Sometimes you need to store some information which may to be entered by users. For an example if you want to store country list in a table (of course you can get the country list from the windows registry) which you haven't send it to customers earlier. Still you can use INSERT command and do it. But better way is sending the template data in a MSAccess table and copy those data with the aid of a VB application.

VB Application

For this VB application I have used Microsoft XML ,v 3.0 and Microsoft Activex Data Objects (ADO) 2.7 library.

Figure 2

Above function will load the XML and read it's content. From the XMLNode2.baseName it is identify whether it has to change the database or it has to copy the data.  Depending on that it calls the two functions.

                                                            Figure 3

Figure 3 shows how it changes the database which is self introductionery. TARGET_CONFIG_CONNCETION_STRING, TARGET_TRANS_CONNCETION_STRING are connection strings which you can build depending on your database.

Figure 4

Figure 4 shows how to copy the template data to the customers table.

Improvements

This can be improved to keep the updated version in the customers computer say in windows registry so that when you are running the next upgrade you can check the existing version. Better if you can keep the date as well there.

Conclusion

My idea is to introduces a simple way to the database upgrades with using the power of XML. You may have great ideas than this. Fell free to share your experience and creative ideas with us and Let's make database upgrade an normal work from what it looks like a mess!

Total article views: 5546 | Views in the last 30 days: 1
 
Related Articles
FORUM

Which Stored Procedure changed

Which Stored Procedure changed

ARTICLE

Customer Database Update A Practical Solution

If you deliver applications to a customer, then you have struggled with the challenges of updating s...

FORUM

Database table Change

Database table Change

FORUM

Database Security with Distribuatable Application

How can we secure a Database which is distributed with an Application - (using MS Sql Server 2005 / ...

ARTICLE

Is Your Database Application DeadLock and Timeout Resistent?

Is your application scalable under increased activity? Timothy Claason brings us a methodology for t...

Tags
ado    
programming    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones