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 ;)

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here