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:
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(',', '.');
else
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 |
BindingFlags.Public);
}
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.
CAUTION: DO NOT USE RESERVED KEYWORDS OF THE SQL LANGUAGE BECAUSE THIS IS NOT CHECKED AND MAY CAUSE UNEXPECTED PROBLEMS!!
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:
CAUTION: DO NOT USE RESERVED KEYWORDS OF THE SQL LANGUAGE BECAUSE THIS IS NOT CHECKED AND MAY CAUSE UNEXPECTED PROBLEMS!!
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:
[Table("myTable")]
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; }
}
[Column("datetime")]
public DateTime Datetime
{
get { return datetime; }
set { datetime = value; }
}
[Column("number")]
public int Number
{
get { return number; }
set { number = value; }
}
[Column("text")]
public string Text
{
get { return text; }
set { text = value; }
}
[Column("boolean")]
public bool Boolean
{
get { return boolean; }
set { boolean = value; }
}
[Column("decimalnum")]
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
