Bulk insert of data

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 A
Table B has a foreign key to Table A
Table C has a foreign key to Table B
which 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.

Very Simple Notes Application

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.

Localizing .NET applications

Most applications nowadays have to be localized in order to provide user interfaces in the language of the user who uses the application. The .NET framework offers a very handy mechanism to provide localization for your apps. It uses so called resource files (.resx) to store keys and the corresponding translated texts or images. If you want to know more on how to work with this translation mechanism take a look at this post.

The main problem with these files is to keep them in sync (for example when adding new strings) and to give translators an easy tool to make translation without requiring them to have visual studio installed on their computers.
I found a tool called "ResEx ... the composite, translation friendly .NET Resource editor" which can be found here that meets exactly this requriements. It provides a nice spreadsheet interface which makes it also for non programmers easy to translate the resources in the files. It is also possible to group resources together by using an underscore e.g. Menu_Home, Menu_Customers; they will all be shown under the tree node "Menu" which makes it easier to understand where the translations are used. Even adding and deleting localization languages is very easy.
The only thing which is missing is to have an automatic translation by using one of the several online translators available nowadays. I will suggest it on the forum and maybe this feature will be available in a future release.

Another very useful tool when working with resource files is the Resoucre Refactoring Tool on CodePlex. It helps you by localize strings in your source code by putting the string into the resource file and replacing the corresponding call in order to get a translated string.

Working with XML files

During my work as developer I have quite often to do with XML files. They are very popular and many data exchange is done with XML and also the file format of Office 2007 is based on XML. To provide a compatibility and avoid parsing errors it is a good idea to create an XML Schema file (.xsd) which describes which structure a XML must have. This allows then to validate an XML file and check if there are errors. Since I am not very good in writing XML Schema files (and often I have no time) I use a tool of Microsoft to generate an XML Schema file from an already existing XML file. The tool is included in the .NET frameworks 1.1 to 3.5. You can simply search in your "C:\Program files" folder for "xsd.exe" and you will find it.

The tool is a command line tool but very very easy to use. To get a schema out of an xml file perform the following steps:

1. Create an XML file with the desired structure (for simplicity on the desktop)
2. Search and copy the file "xsd.exe" to the desktop
3. Open a command line window (Windows key + R -> type "cmd") or (Start -> Run... -> type "cmd")
4. Navigate to your desktop folder
5. Then type "xsd.exe myfile.xml"
6. Use the created file "myfile.xsd" as base for your xml file

Even simpler is to use the freeware tool "XMLFox Advance". You can download it from here.

This applications helps you to create xml files and afterwards you can create the XML Schema file from it.

To validate an existing XML file against an existing XML Schema file with XMLFox you have to modify the preferences of the tool. Go to "Tools->Preferences" and check their the point "Perform validation against XSD schema". After this you can open an XML file with the application, go to the "Script" tab and click on validate. You are asked now which xsd file you want to use. After you selected one the validation is performed and possible errors are displayed in the list at the bottom.