Thursday, January 31, 2008

Object - Relational converter

Are you also bored of writing all these sql statements which are needed to persistently store your objects into a database? 80% of these sql statements are inserts, updates or deletes and can be a quite an amount of work to write them especially if you have objects with more than 10 properties. Additionally there is maybe a file which is often called something like “database manager” which contains all this stuff and grows and grows with every new class that you write.
I was faced with the same problem and wanted to do something against this problem. If you are starting to build a project I would suggest you to use a free object relational mapper such as Castle Active Record which takes care of all the database stuff and makes your life a lot easier. It supports by far more features and supports different DBMS. If you are already within development or you do not want to use an object relational mapper or you simply do not want to inherit all your classes from a base class (which is used often used by object relational mapper) you could consider to use my approach.
I created a small object relational converter which is able to generate insert, update and delete statements for you based on an object that you pass as parameter. Additionally it provides the possibility to create objects from a datatable or datareader object.
I developed this code for Microsoft SQL Server Express Edition and therefore if you use another DBMS or you need to add another data type you have to change the “ToSQL” method which is responsible to convert the different data types into their correct sql representation:

/* This method is responsible to convert the datatypes into their corresponding */
/* sql representation. Change it if you encounter problems or if a data type */
/* needs special treatmend. */
private static string ToSQL(object obj)
if (obj is string || obj is Boolean)
return "'" + obj.ToString() + "'";
else if (obj is DateTime)
return "'" + ((DateTime)obj).ToString(DATE_TIME_FORMAT) + "'";
else if (obj is decimal)
return obj.ToString().Replace(',', '.');
return obj.ToString();

To realize my object relational converter I used reflection and additionally the objects which should be converted have to provide some additional information such as the table name, the column name for each property and the primary key. With reflection I obtain all public, not inherited properties and use them to create the SQL statements. If you want a different behaviour such as you want to include also inherited members then you have to modify the “GetProperties” method according to your needs:

/* This method is responsible to obtain all public, not inherited properties of an object. */
private static PropertyInfo[] GetProperties(object obj)
return obj.GetType().GetProperties(
BindingFlags.DeclaredOnly |
BindingFlags.Instance |

Another thing that you should know is that you have to take care to establish a connection to your DBMS and to execute the queries. My code does only the conversions.
Finally I would like to state how a class must look like in order to use my object relational converter. You have to specify for the class the table name and for each property it’s associated column name in the database.
If the property represents the primary key of the table then you have to add a true because the primary key is needed for the update and delete statement. Here is now a sample class:

public class TestObject
private int table_pk;
private string text;
private DateTime datetime;
private int number;
private bool boolean;
private decimal decimalNum;

[Column("mytable_pk", true)]
public int Table_PK
get { return table_pk; }
set { table_pk = value; }

public DateTime Datetime
get { return datetime; }
set { datetime = value; }

public int Number
get { return number; }
set { number = value; }

public string Text
get { return text; }
set { text = value; }

public bool Boolean
get { return boolean; }
set { boolean = value; }

public decimal DecimalNum
get { return decimalNum; }
set { decimalNum = value; }

public TestObject()
this.table_pk = 999;
this.number = 5;
this.text = "this is a test string";
this.datetime = DateTime.Now;
this.boolean = true;
this.decimalNum = new decimal(1.5);

public override string ToString()
return "pk = " + this.Table_PK + ", datetime = " + this.Datetime.ToLongDateString() + ", number = " + this.Number.ToString() + ", text = " + this.Text + ", boolean = " + this.Boolean.ToString() + ", decimal = " + this.DecimalNum.ToString() ";";
And here some sample code on how to use the Object - Relational converter:

TestObject a = new TestObject();
string sql = ObjectToSqlConverter.CreateInsertStatement(a);
sql = ObjectToSqlConverter.CreateUpdateStatement(a);
sql = ObjectToSqlConverter.CreateDeleteStatement(a);

You can download the whole project and a small sample from here.

If you have suggestions or you found an error feel free to write a comment and tell me about them.
I give you this code “as is” without any warranty that it works or that it does not cause problems or delete/modify important data. Use it at your own risk in your private and commercial projects and feel free to modify it.

Related links:
SQL Zoo (provides SQL statements for actual DBMSes)
Reserved words in standard SQL
Microsoft SQL Server datatypes and corresponding .NET datatypes

Hide selection in DataGridView

Today I was faced at work with the problem that when there is a datagridview always the first cell/row/column depending on the selection mode is selected. I looked therefore for a property or method to have nothing selected. The data grid view provides a method called “clear selection” which can be called with


but for some reason this did not work for me. I tried to create a new project and to do the same thing and suddenly the “clear selection” method worked perfectly. To resolve my problem I finally used the following code:

foreach (DataGridViewCell cell in dataGridView1.SelectedCells)
cell.Selected = false;

This simply takes the list of selected cells and sets the "Selected"-property to false. This can also be done with only the rows or columns with the following code snippets:

foreach (DataGridViewRow row in dataGridView1.SelectedRows)
row.Selected = false;

foreach (DataGridViewColumn column in dataGridView1.SelectedColumns)
column.Selected = false;

If you use the version for individual cells you cover also the row and column selection. So it is not needed to have all three loops.
I tried also with the code
if(dataGridView1.SelectedCells.Count > 0)
dataGridView1.SelectedCells[0].Selected = false;

Which surprisingly did produce an error in the project but worked when I tried it in the new "clean" project. Please note that this code can only be used if multiple selection is not allowed!
I could not find out what where the differences between the empty project and my other project but if you encounter a similar behaviour the foreach loop should solve the issue; at least id did for me :-)

Tested with:
Visual Studio 2005 Express Edition
.NET Framework 2.0

Sunday, January 20, 2008

Site layout improved and syntax highlighting added

Thanks to the article about a syntax highlighting plug in of my friend Juri I was finally able to improve the code parts on my blog. It is now better readable and in addition the syntax is highlighted which makes the code even better readable and understandable. Another change that I made which you may have noticed while reading this post, is that i enlarged the content of my page. There is now more space for the posts which make them better readable. I would like to thank my friend Juri which helped me to realize this. Thanks :-)

Enjoy my blog!!

Thursday, January 3, 2008

Free installer for your applications

If you are a software developer like me and you developed some applications at home that you want to provide to others it is a good idea to have an installer which makes it easier to distribute your software. Since installers are often quite expensively I would like to point you to a free application that I use.
The tool is called "Create Install Free" and is available as free but also as commercial version which provides some additional features which are related mainly with branding. This tool takes a folder which contains the files of your program and creates an executable file which basically copies your files to the directory specified by the user. Additionally it provides the possibility to add a short cut to Start -> Programs as well as a desktop short cut. Some other features are to add a license text which has to be accepted by the user, a readme text, an uninstaller, inserting keys into the registry and customizing the executable file symbol. An important feature is that you can select the installer language from a list of many languages to adjust it for your needs.
I used it for several projects and I can only suggest it. After all it is freeware so there is nothing that prevents you from trying it out.

Some screenshots:

Here you see the output settings where you can specify the folder, the name of the executable, the icon of the executable and the type of setup.

This screenshot shows the possibility of the tool to set up the installer language and the name of the application.

The dialog settings allow you to provide a readme and a license file which are shown to the user during installation. Additionally you can set up some properties which dialogs should be shown.

Here we see the Run settings which basically lets you specify which file should be started after installation.

This screenshot shows the uninstaller properties which gives you the ability to include an uninstaller to your application which should definitely be there.

Related links:
Homepage of Create Install Free

Portable Apps

I own an external hard drive and since I have to help quite often kin and kith with their computer problems I thought it would be nice to have the most important applications with me. I looked therefore for a possibility to put applications on the external hard drive in order to run them without installation. After looking around the internet I found a very interesting tool which is called Portable Apps. It is installed on the external hard disk and an autorun mechanism is added in order to start it when the hard disk is plugged in. The Portable Apps tool adds an icon to the systray (or better notification area) which opens a menu which looks like the windows menu when clicking on start.

This menu now provides access to all the different applications which are present on the external hard drive. By selecting one of them the according application is started without the need of any installation on the host computer. This has the advantage that the registry of the host computer is not affected and also no hard disk space is required. Additionally to the applications there are folders to easily access documents, music, pictures or videos.

Additionally it provides a backup function to easily back up or restore files on the external hard drive as well as a search functionality to search the drive, the computer or the web. At the bottom of the menu the free disk space is shown.

The list of applications in the list can be extended as needed. For some applications there exists a “Portable Apps version” which can be downloaded and installed on the homepage of portable apps but it is also possible and very easy to integrate other applications. As I tried to integrate different applications I can say that applications which do not require an activation key can be added to the list of applications because the activation key is often stored inside the registry and requires an installation routine. The most easy way to add a new application is to download it as archive without installer. It can then be put into the “PortableApps” folder on the external hard disk. The only thing to know is that in the menu there will be shown all exe files which are on the following path: Z:\PortableApps\application folder\application.exe; the name of the exe file is listed in the list of applications. Some system related applications such as firewalls can not be made portable because they have to directly interact with the underlying system and require an installation.

A quite large and good list of portable applications can be found here. From that site I took most of the applications that are present on my application list.

After I installed all applications that I need on the external hard drive I started to miss them when the external hard drive was not plugged in. I simply had to copy the “PortableApps” folder and the file “StartPortableApps.exe” to my local hard disk and it works fine. I like this because for example all of the sudden I get an Open Office file and to open it I do not want to install Open Office on my computer. So I start portable apps and there I have Open Office available and I can open, modify and save Open Offices files without having to install it.

Her now a list of applications that are present on my application list:

7-Zip Portable: utility for manipulating archives

Abakt: simple backup utility with many configuration options

Auto-Backup Win: a tool to backup all important files of a windows installation such as activation files, Outlook Express E-Mails, My Documents, and many others…

CCleaner: a tool to clean the registry, the hard disk and the autostart entries

CDex Application: a tool to rip an audio cd to mp3 files

ClamWin Portable: a portable virus scanner

Data Recovery Application: a tool to recover deleted data

File Assasin: a tool to delete locked files from the system; useful to remove trojans

Firefox Portable: the successful internet browser Firefox

Gaim Portable: instant messenger which supports different protocols

JkDefrag GUI version: a very good defragmenter application

Notepad++ Portable: text editor which provides syntax highlighting for many different programming languages

Open Office Portable: open source office suite

ReNamer: a tool to rename files and folder according to rules

Safarp: simple and fast uninstall utility which makes the slow windows uninstall dialog obvious

Siginet’s Driver Grabber: a tool to backup all installed drivers; useful when formatting the hard drive and reinstalling windows

SilentNight Micro Burner: a tool to burn cd’s and dvd’s

StartUp Lite: a tool which checks the autostart entries and makes suggestions which entries can be removed in order to accelerate the windows start

Sudoku Portable: sudoku game to pass the time

Sumatra PDF Portable: simple PDF reader

System Information for Windows: a very good tool to get information about hardware and software; provides additional tools such as uncovering password fields,…

Thunderbird Portable: very good e-mail client

Toucan: a tool for advanced users to synchronise, backup and encrypt their data

VLC Media Player Portable: a media player which plays most of the actual video and audio formats

XP-AntiSpy: a tool which makes some settings to prevent windows from “talking” with Microsoft

Related links:
Homepage Portable Apps
Homepage The Portable Freeware