Universal SqlBuilder class (SQL Server, Access, MySQL, SQLite and more)

• Download source code - 5.63 KB

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;

//build connection string
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);

//connect to a specific database
SqlBuilder.Instance.connect(new SqliteDbFactory(), connection);

Test test = new Test();
//Verify whether table exists - if not create from the object 'test'
builder.verifyTableExists(test);
//insert new record
test.Name = "Joe";
test.Number = 124.56f;
builder.insert(test);

//select record
test.Id = 1;
builder.select(test);

//update record
test.Name = "Rob";
test.Number = 12345.56f;
builder.update(test);

//Get insert string
builder.getInsert(test);
//Get update string
builder.getUpdate(test);
//Get delete string
builder.getDelete(test);
//Get select string
builder.getSelect(test);
//Get CreateTable string
builder.getCreateTable(test);

           
//Build list of test objects
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;
}
//insert list into database
builder.insert(list);
//select list from database
list.Clear();
builder.select(list, new Test());//equivalent to select * from tblTest
//another select list
builder.select(list, new Test(), "select Name,Number from tblTest");


//Autosynchronization mechanism
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;
}

// Some database object 
[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; }
    }
    //This interface must be implemented and passed
    //to the SQLBuilder so it can work 
    //with specified database type 

    public interface IDBFactory
    {
        IDbConnection CreateConnection(string connection_string);
        string NetTypeToDBType(string netType);
        bool IsTableExist(string data);

    }
    //One of the possible implementation is for the Sqlite database
    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);
//get xml of table correspondent to the object obj