65.9K
CodeProject is changing. Read more.
Home

C# class that creates 'INSERT INTO' SQL queries

starIconstarIcon
emptyStarIcon
starIcon
emptyStarIconemptyStarIcon

2.49/5 (23 votes)

Dec 16, 2004

viewsIcon

174882

downloadIcon

2

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