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

C# class that creates 'INSERT INTO' SQL queries

, 16 Dec 2004
Rate this:
Please Sign up or sign in to vote.
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 Wink | ;)

License

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

Share

About the Author

Adam Klobukowski
Web Developer
Poland Poland
No Biography provided

Comments and Discussions

 
GeneralInsert File into Microsoft Office Access database field """"""""""""" PinmemberBabita Shivade26-Mar-09 3:29 
GeneralNo! You are setting people up for SQL Injection attacks PinmemberSeattleVegas31-Oct-07 23:29 
GeneralRe: No! You are setting people up for SQL Injection attacks PinmemberWaseem Sindhu21-Nov-08 20:24 
Generalfree tables (dbf) PinmemberAmar Chaudhary18-Oct-06 20:01 
QuestionWhat about using OleDBCommandBuilder ? Pinmembermax295012-Dec-05 7:48 
GeneralInput checking Pinmembermark_e_mark16-Sep-05 6:16 
GeneralRe: Input checking PinmemberAdam Klobukowski16-Sep-05 7:17 
GeneralRe: Input checking Pinmembermark_e_mark16-Sep-05 7:23 
GeneralThanks PinmemberAdam Klobukowski19-Dec-04 22:15 
QuestionWhy do this manually? Pinmembermgnoonan17-Dec-04 8:58 
AnswerRe: Why do this manually? Pinmembermgnoonan17-Dec-04 9:00 
GeneralRe: Why do this manually? Pinmemberjogoolasghy10-Nov-05 16:44 
GeneralRe: Why do this manually? Pinmembermgnoonan13-Nov-05 13:41 
GeneralA couple more suggestions PinprotectorMarc Clifton16-Dec-04 5:52 
Generalsome improvements... PinmemberMrRotzi16-Dec-04 5:37 

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
Web02 | 2.8.140821.2 | Last Updated 16 Dec 2004
Article Copyright 2004 by Adam Klobukowski
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid