C# class that creates 'INSERT INTO' SQL queries

Introduction

This article describes a C# class that creates INSERT INTO SQL queries.

Some days ago, I was forced to work on an old application written in FoxPro for DOS. That meant struggling with DBF files. I used OleDB for FoxPro. It worked all okay, but I had trouble when I needed to insert rows into a table that had 50 (or more) columns. I had to specify all columns in the INSERT query. Horrible.

So I've written a small class that creates the query for me. It is short and here is the code:

public class Insert
{
    Hashtable args = new Hashtable();
    string table;

    /// <summary>
    /// Constructs Insert object
    /// </summary>
    /// <param name="table">table name to insert to</param>
    public Insert(string table)
    {
        this.table = table;
    }

    /// <summary>
    /// Adds item to Insert object
    /// </summary>
    /// <param name="name">item name</param>
    /// <param name="val">item value</param>
    public void Add(string name, object val)
    {
        args.Add(name, val);
    }

    /// <summary>
    /// Removes item from Insert object
    /// </summary>
    /// <param name="name">item name</param>
    public void Remove(string name)
    {
        try
        {
            args.Remove(name);
        }
        catch
        {
            throw (new Exception("No such item"));
        }
    }

    /// <summary>
    /// Test representatnion of the Insert object (SQL query)
    /// </summary>
    /// <returns>System.String</returns>
    public override string ToString()
    {
        StringBuilder s1 = new StringBuilder();
        StringBuilder s2 = new StringBuilder();

        IDictionaryEnumerator enumInterface = args.GetEnumerator();
        bool first = true;
        while(enumInterface.MoveNext())
        {
            if (first) first = false;
            else
            {
                s1.Append(", ");
                s2.Append(", ");
            }
            s1.Append(enumInterface.Key.ToString());
            s2.Append(enumInterface.Value.ToString());
        }

        return "INSERT INTO " + table + " (" + s1 + ") VALUES (" + s2 + ");";
    }

    /// <summary>
    /// Gets or sets item into Insert object
    /// </summary>
    object this[string key]
    {
        get
        {
            Debug.Assert(args.Contains(key), "Key not found");
            return args[key];
        }
        set {args[key]=value;}
    }
}

You can use it this way:

Insert q = new Insert("table_name");

q.Add("column1", "value1");
q.Add("column2", "value2");

string query = q.ToString();

Handy, if you have 50+ columns, each one calculated some way. Sorry for my bad English ;)