Click here to Skip to main content
Click here to Skip to main content

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

, 28 Aug 2007
Rate this:
Please Sign up or sign in to vote.
The SqlBuilder class automates and simplifies database operations.

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 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Igor Sokolsky
Software Developer (Senior)
Canada Canada
No Biography provided

Comments and Discussions

 
QuestionSql Server? PinmemberDewey28-Aug-07 11:42 
AnswerRe: Sql Server? Pinmembercigsmh29-Aug-07 2:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 28 Aug 2007
Article Copyright 2007 by Igor Sokolsky
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid