INSERT if new and UPDATE otherwise

Recently I faced the following problem. I had to insert a big number of records into a PostgreSQL database. I have created a string which contains the various insert statements like below. The primary key is defined on the columns a,b.

INSERT INTO tablename (a,b,c) VALUES (1,2,'abc');
INSERT INTO tablename (a,b,c) VALUES (2,2,'def');
INSERT INTO tablename (a,b,c) VALUES (2,2,'ghi'); --generates an error!!
INSERT INTO tablename (a,b,c) VALUES (3,2,'ghi');

This worked fine until I encountered a duplicate primary key violation. The main problem with this was that after the error, the transaction was aborted and rolled back. So no record was inserted. I wanted instead that if such an error occurs, the existing record gets simply updated with the new values and if no record exists, it should be inserted. I searched the internet and there where different proposals but none of them really convinced me. I then came up with the following function:

CREATE OR REPLACE FUNCTION insert_update_record(a_in int, b_in int, c_in character varying)
RETURNS void AS
$BODY$
BEGIN
          -- try to insert the record
          BEGIN
              INSERT INTO tablename (a,b,c) VALUES (a_in, b_in, c_in);
              RETURN;
          EXCEPTION WHEN unique_violation THEN
              -- if there is a duplicate key exception
              -- update the record
              UPDATE tablename SET c = c_in WHERE a = a_in AND b = b_in;
          END;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

This function simply tries to insert the new record into the table and if there is a duplicate key exception it will update the record. The previous insert statements would then be converted to:

SELECT insert_update_record(1,2,'abc');
SELECT insert_update_record(2,2,'def');
SELECT insert_update_record(2,2,'ghi'); --generates an error but does not abort the transaction
SELECT insert_update_record(3,2,'ghi');

I did not make any performance tests, but I believe that this function is not much slower than the INSERT statements if there are not many duplicates; because it simply makes an insert and returns and only if there is an exception it does some additional work. Since this function is precompiled it may be that it is even faster than the INSERT statement. Anyway the function can help you solve the insert/update problem.

Of course you have to adjust the function according to your needs, but if you have some experience with programming it shouldn't be too difficult ;-)

UPDATE:
Based on the suggestion of Peter I made up a new function which is pratically an implementation for PostgreSQL of his pseudo SQL statements:

UPDATE sample
SET testno = 1;
WHERE test = 'PL/SQL';
IF SQL%ROWCOUNT ==0
THEN
/* Insert Statement */

END IF;

I have created then a table 'test' with the columns 'id' (pk), 'val1' and 'val2'. Then I converted the pseudo SQL into:

CREATE OR REPLACE FUNCTION update_insert(id_in integer, val1_in integer, val2_in integer)
RETURNS void AS
$BODY$
DECLARE
count int;
BEGIN
update test set val1 = val1_in, val2 = val2_in where id = id_in;
    GET DIAGNOSTICS count = ROW_COUNT;

    IF count = 0 THEN
     INSERT INTO test (id, val1, val2) VALUES (id_in, val1_in, val2_in);
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

and provided also the function for this table with the exception handling:

CREATE OR REPLACE FUNCTION insert_update(id_in integer, val1_in integer, val2_in integer)
RETURNS void AS
$BODY$
BEGIN
            -- try to insert the record
            BEGIN
                INSERT INTO test (id,val1,val2) VALUES (id_in, val1_in, val2_in);
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- if there is a duplicate key exception
                -- update the record
                UPDATE test SET val1 = val1_in, val2 = val2_in WHERE id = id_in;
            END;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

Then I created with a small application the SQL statements that call these functions and I created them with differenct percentage of duplicates:

SELECT insert_update(1,101,-99);
and
SELECT update_insert(1,101,-99);

Here are the results of my performance measures:
1 000 statements
tried to insert every id twice -> 500 records in DB
update_insert: 250ms
insert_update: 250ms

100 000 statements
tried to insert every id twice -> 50 000 records in DB
update_insert: 37281ms
insert_update: 45125ms

100 000
every 10th record was already present -> 90 000 records in DB
update_insert: 63875ms
insert_update: 61500ms

10 000
all ids were the same -> 1 record in DB
update_insert 10016ms
insert_update 18703ms

I did the tests on my laptop; I know that they may not be precise because of running services in the background, but at least they give an intuition.

The result of my test:
- High number of duplicates -> the update/insert function is much faster
- Low number of duplicates -> the two functions are almost equally fast

I was surprised that there is not more difference between these two functions. I expected that the function with the exception would be much slower than the other one. I would suggest to use the update/insert function as proposed by Peter because exceptions should only be used in exceptional situations and avoided when possible.
Thanks Peter for the proposal...

Extension Methods in C#

There are some new features in C# 3 which are introduced to make LINQ possible.
One of them are extension methods which I wanted to try out. Extension methods are static methods which can be applied to any object type which is specified as "special parameter". For example if you want to extend the type string with the method "Greet" you write something like this:

public static void Greet(this String s)
{
Console.WriteLine("Hello " +  s);
}

Then you can call it like:

string name = "Tom";
name.Greet();

This will produce the following on the console: "Hello Tom"

I played a bit with this extension mehtods and came up with the following extensions for object which I believe can be helpful.
Assume the following object:

Customer testCustomer = new Customer() { Name = "John", Age = 35, LastName = "Smith" };

PrintConsole() -> prints the string representation of an object to the console
Code:

testCustomer.PrintConsole();

Produces:
"ExtensionMethodTest.Customer"

PrintMessageBox() -> shows the string representation of an object in a message box
Code:

testCustomer.PrintMessageBox();

Produces:


PrintPropertiesConsole() -> Prints all public non inherited properties and their values of the object to the console
Code:

testCustomer.PrintPropertiesConsole();

Produces:
Properties of 'ExtensionMethodTest.Customer'
Name: John
LastName: Smith
Age: 35

PrintPropertiesMessageBox() -> Shows all public non inherited properties and their values of the object in a message box
Code:

testCustomer.PrintPropertiesMessageBox();

Produces:

string ToXml() -> Serializes the object to XML and returns the XML string
Code:

testCustomer.ToXml();

Produces:

John
Smith
35

ToXml(Stream stream) -> Serializes the object to XML and writes it to the stream
Code:

testCustomer.ToXml(myStream);

ToXml(string fileName) -> Serializes the object to XML and writes the XML string to the file specified.
Code:

testCustomer.ToXml("c://test.xml);

If you are interested in my project you can download it from here.
Or you can simply download the Extensions.dll from here and import it into your project.

All extension methods are in the namespace System. I know that this is not the way how to do it but so you don't have to add a using directive because System is by default present.

XPath

For them of you which do not know what XPath is; it is a very powerful query language for XML data. But it can also more than querying data; it can also compute values, find the max or min of an attribute and even produce a different output format. For example it is possible to write HTML code with XML data. C# provides different classes to execute XPath queries on XML files which are located in the "System.Xml.Xpath" namespace.

The problem, at least for me, is that I do not use XPath so often as that I always remember the sometimes tricky XPath-Syntax. Here I found it very helpful to have a graphical tool where I can type in my query and see the results of the query. I found such a tool which is freeware; Sketch Path. It is a very powerful tool which not only shows you the result of a query but also assists you when writing a query by providing common operations and selectors.

I can only encourage you to try XPath when working with XML data because it really makes it easier to get the data you want. Especially if you use XML files instead of a database. XPath can be seen as the SQL for XML files.

Here there is a screenshot of Sketch Path where I searched in a list of customers for all customers which are older than 50. The query is quite simple:
//Customer[Age>50]


Another nice thing which is very helpful is that if you select a node then it shows you the path to this node which can help you creating your query:



Here you can find a tutorial on XPath Syntax.

TreeView node font problem

Recently I encountered the following problem. I needed to set the font style of some nodes in a treeview at runtime to bold; but after setting the NodeFont property of the node the font changed correctly, but the text of the node was cut off.

This screenshot shows the problem:


The solution to the problem is to add an empty string to the Text property after setting the NodeFont property:

this.treeView1.SelectedNode.NodeFont = new Font(this.treeView1.Font, FontStyle.Bold);
this.treeView1.SelectedNode.Text += string.Empty;

The problem is also known at Microsoft and I found this workaround here.

This screenshot shows the correct text:

Enhanced combobox with readonly and working value member

As I had several problems with the standard combo box of the .Net 2.0 Framework such as the missing read only property or the not working value member property (if not using a datasource), I decided to write my own improved combo box. To change the default combobox to my needs I overwrote the OnPaint() mehtod and provided some additional methods.

With the enhanced combo box you can finally specify a value member without having to use a datasource as you can see in the code below:

Customer customer1 = new Customer();
customer1.FirstName = "John";
customer1.ID = 1;
customer1.LastName = "Smith";

Customer customer2 = new Customer();
customer2.FirstName = "Marco";
customer2.ID = 2;
customer2.LastName = "Polo";

this.cmbTest.Items.Add(customer1);
this.cmbTest.Items.Add(customer2);

this.cmbTest.DisplayMember = "FirstName";
this.cmbTest.ValueMember = "ID";

Or you can use a datasource:

Customer customer1 = new Customer();
customer1.FirstName = "John";
customer1.ID = 1;
customer1.LastName = "Smith";

Customer customer2 = new Customer();
customer2.FirstName = "Marco";
customer2.ID = 2;
customer2.LastName = "Polo";

Customer customer3 = new Customer();
customer3.FirstName = "Tom";
customer3.ID = 3;
customer3.LastName = "Hanks";

List customers = new List();
customers.Add(customer1);
customers.Add(customer2);
customers.Add(customer3);

this.cmbTest.DataSource = objects;

this.cmbTest.DisplayMember = "FullName";
this.cmbTest.ValueMember = "ID";

Additionally you can register for different object types a display and value member. This gives you a lot of flexibility when you have to show information of different objects in the same combobox.

To register a new type call the "RegisterType" method and pass it the type of the object, the name of the property which is used as display member and the name of the property which is used as value member.

this.cmbTest.RegisterType(typeof(Customer), "FirstName", "PK");

To unregister a registered type call "UnregisterType" and pass it the type you want to unregister.

this.cmbTest.UnregisterType(typeof(Customer));

To modify the display and/or value member of a registered type call "ChangeValueMember" or "ChangeDisplayMember" respectively and pass it the new name of the property you want to use.

this.cmbTest.ChangeDisplayMember(typeof(Customer), "FullName");
this.cmbTest.ChangeValueMember(typeof(Customer), "LastName");

Of course all this operations can be performed at runtime and the changes are immediately visible in the combo.

To obtain the value of the selected item, simply call "SelectedValue".

Console.WriteLine(this.cmbTest.SelectedValue.ToString());

To get the selected object simply call "SelectedItem" and cast it to the correct type.

Customer selectedCustomer = this.cmbTest.SelectedItem as Customer;
if (selectedCustomer != null)
{
...
}

NOTE: If the combobox has to visualize a type which is not registered it calls the ToString() method of the object and uses that string.

Now I'd like to show you the read only property. The enhanced combobox provides a read only property which disables the drop down button and changes the back color of the combobox to "Control". The read only property is not a real read only because it is not possible to select and then copy the visible text. The most important point is that you can change the fore- and backcolor of the combobox when it is in read only because it is not very easy to read the text of a disabled combo box. The images below show what I mean.

This is a disabled ComboBox:

This is a read only ComboBox:

You can set any color as read only back- and fore color you like:

That's all about my enhanced Combo Box. I hope it helps someone to achieve what he wants. If you have suggestions on how to improve it or you found a bug, feel free to leave a comment.

You can download the DLL from here and simply drag and drop it to your visual studio toolbox.

Or you can get the source code with a sample tool which shows how to use it from here.

Problems with Google Earth

Lastly I updated Google Earth to the latest version and after I started it I saw the following:


After searching the Internet I found the page of Google where they give some points which should be checked; such as: install the latest display driver, ...
But I already had the latest drivers.
While browsing trough the options of Google Earth I found that the display mode can be changed. After changing it to DirectX it worked correctly.

To change the setting:
1. go to the options


2. And then change the graphics mode to DirectX


Related links:
Google Earth

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.