Coders4Fun

Every time that we need to create SQL queries in a programming language, often we do something like:

int id = GetTableID();
string place = GetPlace();
DateTime timestamp = GetLastActionDate();
string strTimestamp = Utils.GetSQLDate(DateTime.Now);
string sql = "INSERT INTO table VALUES(" + id + ",'" + place + "'," + timestamp + "')";

During the string creation we must add , ‘ and ” with attention.
In this article we will create a function that takes as input a c# Object and gives as output the formatted SQL String:

public static string GetSQLObject(object value)

For example, the following source code:

DateTime date = DateTime.Now;
string str = "prova";
int n = 3;
MyEnum enum = MyEnum.EnumValue;
bool b = true;
string sql = "INSERT INTO table VALUES(" + GetSQLObject(date) + "," + GetSQLObject(str) + "," +GetSQLObject(n) + "," + GetSQLObject(enum) + "," + GetSQLObject(b) + ")";
Console.Writeline(sql);

will print:

INSERT INTO table VALUES data,'prova',

This method isn’t really useful if we create hard-coded SQL strings, but becomes fundamental if we want to create SQL programmatically.
The function is:

public static string GetSQLObject(object value)
{
// if the object is a string
if (value is String)
return "'" + Utils.GetSQLCleanString((String)value) + "'";
// if it's a char
else if (value is char)
return "'" + Utils.GetSQLCleanString(value) + "'";
// if it's a date
else if (value is DateTime)
return "'" + Utils.GetSQLDate((DateTime)value) + "'";
// follows a lot of casts ....
// ....
}

The method checks the type of the object and create the SQL for every type: for example if the object is a string or a char, invokes the method GetSQLCleanString (read Get a clean SQL String) and adds the inverted commas first and after the string, while for the date invokes GetSQLDate (read Convert a DateTime C# object in a SQL field).

Of course in the source code are included the casts for the other type, eg a byte array is saved as a Blob through the method described in the post Store a MySQL Blob in C#, a boolean is converted in the numeric value 0-1 and so on.

Download GetSQLObject. Downloads: 321

digg_url =”http://digg.com/programming/Convert_a_C_Object_to_a_SQL_string”;

Se sei interessato a questo post, potresti anche provare a leggere:

• No related posts