Click here to Skip to main content
13,356,318 members (68,179 online)
Click here to Skip to main content
Add your own
alternative version


34 bookmarked
Posted 28 Aug 2007

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.


This is a very simple and useful SqlBuilder class. It saves a lot of time writing code.


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(
string db_path = string.Format("{0}\\my_db.db3", cur_dir);
string connection = 
  string.Format("Data Source={0};Version=3;New=True;Compress=True;", 

//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'
//insert new record
test.Name = "Joe";
test.Number = 124.56f;

//select record
test.Id = 1;;

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

//Get insert string
//Get update string
//Get delete string
//Get select string
//Get CreateTable string

//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
//select list from database
list.Clear();, new Test());//equivalent to select * from tblTest
//another select list, new Test(), "select Name,Number from tblTest");

//Autosynchronization mechanism
Test test = new Test();
test.Name = "dog";
test.Number = 15;

builder.OnSynchronized += new OnSynchronizedD(OnSynchronized);
int key = builder.addSynchObject(test, SynchType.SELECT);
builder.startSynchronization(true, 1000);
builder.startSynchronization(false, 0);

public void OnSynchronized(SynchObject obj)
    Test t = (Test)obj.SynchronizationObject;

// Some database object 
class Test
    private int m_Id;
    private string m_Name;
    private double m_Number;
    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";
                    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;

            throw new DataException("It shouldn't be here");


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 


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


About the Author

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

You may also be interested in...


Comments and Discussions

QuestionSql Server? Pin
Dewey28-Aug-07 12:42
memberDewey28-Aug-07 12:42 
AnswerRe: Sql Server? Pin
cigsmh29-Aug-07 3:08
membercigsmh29-Aug-07 3:08 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180111.1 | Last Updated 28 Aug 2007
Article Copyright 2007 by Igor Sokolsky
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid