Introduction
This is a very simple and useful SqlBuilder
class. It saves a lot of time writing code.
Background
I was overwhelmed by the amount of SQL code I had to generate for my small database application. So, I came up with a generic mechanism to work with databases (using ADO 2.0).
Using the code
Here are some examples of SqlBuilder
class usage:
SqlBuilder builder = SqlBuilder.Instance;
string cur_dir = Path.GetDirectoryName(
System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
string db_path = string.Format("{0}\\my_db.db3", cur_dir);
string connection =
string.Format("Data Source={0};Version=3;New=True;Compress=True;",
db_path);
SqlBuilder.Instance.connect(new SqliteDbFactory(), connection);
Test test = new Test();
builder.verifyTableExists(test);
test.Name = "Joe";
test.Number = 124.56f;
builder.insert(test);
test.Id = 1;
builder.select(test);
test.Name = "Rob";
test.Number = 12345.56f;
builder.update(test);
builder.getInsert(test);
builder.getUpdate(test);
builder.getDelete(test);
builder.getSelect(test);
builder.getCreateTable(test);
List<Test> list = new List<Test>();
for (int i = 0; i < 10; i++)
{
Test t = new Test();
t.Name = string.Format("test{0}", i);
t.Number = i;
}
builder.insert(list);
list.Clear();
builder.select(list, new Test());
builder.select(list, new Test(), "select Name,Number from tblTest");
Test test = new Test();
test.Name = "dog";
test.Number = 15;
builder.insert(test);
builder.OnSynchronized += new OnSynchronizedD(OnSynchronized);
int key = builder.addSynchObject(test, SynchType.SELECT);
builder.startSynchronization(true, 1000);
builder.startSynchronization(false, 0);
builder.deleteSynchObject(key);
public void OnSynchronized(SynchObject obj)
{
Test t = (Test)obj.SynchronizationObject;
}
[dbTable("tblTest")]
class Test
{
private int m_Id;
private string m_Name;
private double m_Number;
[db(true,true,true,"")]
public int Id
{
get { return m_Id; }
set { m_Id = value; }
}
[db(false, false, false, "")]
public string Name
{
get { return m_Name; }
set { m_Name = value; }
}
[db(false, false, false, "")]
public double Number
{
get { return m_Number; }
set { m_Number = value; }
}
public interface IDBFactory
{
IDbConnection CreateConnection(string connection_string);
string NetTypeToDBType(string netType);
bool IsTableExist(string data);
}
public class SqliteDbFactory : IDBFactory
{
IDbConnection m_Connection;
#region IDBFactory Members
IDbConnection IDBFactory.CreateConnection(string connection_string)
{
m_Connection = new SQLiteConnection(connection_string);
return m_Connection;
}
string IDBFactory.NetTypeToDBType(string netType)
{
switch (netType)
{
case "string":
return "text";
case "double":
case "float":
case "single":
return "float";
case "int16":
case "int32":
case "int64":
case "uint16":
case "uint32":
case "uint64":
case "boolean":
case "byte":
return "integer";
case "datetime":
return "datetime";
default:
return "integer";
}
}
bool IDBFactory.IsTableExist(string data)
{
using(IDbCommand cmd = m_Connection.CreateCommand())
{
cmd.CommandText = data;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return reader.GetInt32(0) > 0;
}
reader.Close();
}
}
throw new DataException("It shouldn't be here");
}
#endregion
}
}
History
Initially I created a SqliteBuilder
, but after I got some comments, I came up with a generic SqlBuilder
class that could work with any database type that supports ADO interfaces.
Added features
These new methods were added later on:
string getTableXml(string table_name);
string getTableXml(object obj);