Wednesday, March 25, 2009

Recently I faced the problem of inserting a big amount of data into the database. The point is that multiple tables are affected which are connected with foreign keys. After some research I found OpenXML as one possibility. You create a stored procedure where you read your XML string into an "XmlDocument" which you can then query by using XPath and inserting, updating or deleting the tables occordingly.

The other possibility I found is SQLXML which is now available in version 4.1. It is delivered with SQL Server but not with the Express version. If you need it you can download it from here. SQLXML allows you to bulk insert data from an XML file by using a so called annotated XSD schema where the mapping is defined. It is not very difficult to define the schema, only recursion and special cases are a little bit difficult to handle. I was not able to make recursion work with identity values coming from the database. Anyway; I did some performance tests where I had a structure like:Table ATable B has a foreign key to Table ATable C has a foreign key to Table Bwhich corresponds to the following XML structure:<root> <a> <b> <c></c> </b> </a></root>

I created an XML file that contained 100 A's; every A contained 100 B's and every B contained 100 C's. This gives a total of 100 A's, 10.000 B's and 1.000.000 C's. To insert all them and updating the foreign keys it took 84 seconds. This is quite fast for such an amount of data. I think that this is the fastest way to bulk insert data into SQL Server (if I'm wrong You are welcome to coment on this).If you want to use it with .Net add a reference to "Microsoft SQL Server Bulk Upload" or something like that.I don't remember the exact name but as soon as I find the link of the code sample I will post it here.

The problem is that SQLXML cannot be used for updates. There exist so called updategrams or diffgrams but I didn't try them.

My girlfriend asked me look for a small program wher she can add notes and delete them if necessary. I serached the internet and there are a lot of programs out there to do this, but they are all very complex and offer a huge amount of feautres that she does not need. Since I'm a programmer I decided to write one :-)

As the title says it is a really simple program. Here a screenshot of it:

and this is how it looks with some notes:

Obviously by clicking the trash the note is deleted. All the notes are saved in a simple XML file in the isolated storage of the user. This means that you can run the application from a readonly storage (like a CD) and it saves all Your notes in your user profiles folder.

If someone is interested in this very simple tool You can get it from here.