C# DataTable to MySQL

I had a very basic problem while coding on a project and it was simply bulkinsert from a .NET DataTable to the MySQL Server and do this FAST! After crawling the net for a while I came to the conclusion that every one of those methods where either slow or didn’t fit my needs so I hacked together my own Solution!

/*
* ----------------------------------------------------------------------------
* "THE BEER-WARE LICENSE" (Revision 42):
* <fg@code-works.de> wrote this file. As long as you retain this notice you
* can do whatever you want with this stuff. If we meet some day, and you think
* this stuff is worth it, you can buy me a beer in return. Frank Gehann
* ----------------------------------------------------------------------------
*/

using System;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace DB
{
    class DBHelper
    {
        /// <summary>
        /// Creates a multivalue insert for MySQL from a given DataTable
        /// </summary>
        /// <param name="table">reference to the Datatable we're building our String on</param>
        /// <param name="table_name">name of the table the insert is created for</param>
        /// <returns>Multivalue insert String</returns>
        public static String BulkInsert(ref DataTable table, String table_name)
        {
            try
            {
                StringBuilder queryBuilder = new StringBuilder();
                DateTime dt;

                queryBuilder.AppendFormat("INSERT INTO `{0}` (", table_name);

                // more than 1 column required and 1 or more rows
                if (table.Columns.Count > 1 && table.Rows.Count > 0)
                {
                    // build all columns
                    queryBuilder.AppendFormat("`{0}`", table.Columns[0].ColumnName);

                    if (table.Columns.Count > 1)
                    {
                        for (int i = 1; i < table.Columns.Count; i++)
                        {
                            queryBuilder.AppendFormat(", `{0}` ", table.Columns[i].ColumnName);
                        }
                    }

                    queryBuilder.AppendFormat(") VALUES (", table_name);

                    // build all values for the first row
                    // escape String & Datetime values!
                    if (table.Columns[0].DataType == typeof(String))
                    {
                        queryBuilder.AppendFormat("'{0}'", MySqlHelper.EscapeString(table.Rows[0][table.Columns[0].ColumnName].ToString()));
                    }
                    else if (table.Columns[0].DataType == typeof(DateTime))
                    {
                        dt = (DateTime)table.Rows[0][table.Columns[0].ColumnName];
                        queryBuilder.AppendFormat("'{0}'", dt.ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else if (table.Columns[0].DataType == typeof(Int32))
                    {
                        queryBuilder.AppendFormat("{0}", table.Rows[0].Field<Int32?>(table.Columns[0].ColumnName) ?? 0);
                    }
                    else
                    {
                        queryBuilder.AppendFormat(", {0}", table.Rows[0][table.Columns[0].ColumnName].ToString());
                    }

                    for (int i = 1; i < table.Columns.Count; i++)
                    {
                        // escape String & Datetime values!
                        if (table.Columns[i].DataType == typeof(String))
                        {
                            queryBuilder.AppendFormat(", '{0}'", MySqlHelper.EscapeString(table.Rows[0][table.Columns[i].ColumnName].ToString()));
                        }
                        else if (table.Columns[i].DataType == typeof(DateTime))
                        {
                            dt = (DateTime)table.Rows[0][table.Columns[i].ColumnName];
                            queryBuilder.AppendFormat(", '{0}'", dt.ToString("yyyy-MM-dd HH:mm:ss"));

                        }
                        else if (table.Columns[i].DataType == typeof(Int32))
                        {
                            queryBuilder.AppendFormat(", {0}", table.Rows[0].Field<Int32?>(table.Columns[i].ColumnName) ?? 0);
                        }
                        else
                        {
                            queryBuilder.AppendFormat(", {0}", table.Rows[0][table.Columns[i].ColumnName].ToString());
                        }
                    }

                    queryBuilder.Append(")");
                    queryBuilder.AppendLine();

                    // build all values all remaining rows
                    if (table.Rows.Count > 1)
                    {
                        // iterate over the rows
                        for (int row = 1; row < table.Rows.Count; row++)
                        {
                            // open value block
                            queryBuilder.Append(", (");

                            // escape String & Datetime values!
                            if (table.Columns[0].DataType == typeof(String))
                            {
                                queryBuilder.AppendFormat("'{0}'", MySqlHelper.EscapeString(table.Rows[row][table.Columns[0].ColumnName].ToString()));
                            }
                            else if (table.Columns[0].DataType == typeof(DateTime))
                            {
                                dt = (DateTime)table.Rows[row][table.Columns[0].ColumnName];
                                queryBuilder.AppendFormat("'{0}'", dt.ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                            else if (table.Columns[0].DataType == typeof(Int32))
                            {
                                queryBuilder.AppendFormat("{0}", table.Rows[row].Field<Int32?>(table.Columns[0].ColumnName) ?? 0);
                            }
                            else
                            {
                                queryBuilder.AppendFormat(", {0}", table.Rows[row][table.Columns[0].ColumnName].ToString());
                            }

                            for (int col = 1; col < table.Columns.Count; col++)
                            {
                                // escape String & Datetime values!
                                if (table.Columns[col].DataType == typeof(String))
                                {
                                    queryBuilder.AppendFormat(", '{0}'", MySqlHelper.EscapeString(table.Rows[row][table.Columns[col].ColumnName].ToString()));
                                }
                                else if (table.Columns[col].DataType == typeof(DateTime))
                                {
                                    dt = (DateTime)table.Rows[row][table.Columns[col].ColumnName];
                                    queryBuilder.AppendFormat(", '{0}'", dt.ToString("yyyy-MM-dd HH:mm:ss"));
                                }
                                else if (table.Columns[col].DataType == typeof(Int32))
                                {
                                    queryBuilder.AppendFormat(", {0}", table.Rows[row].Field<Int32?>(table.Columns[col].ColumnName) ?? 0);
                                }
                                else
                                {
                                    queryBuilder.AppendFormat(", {0}", table.Rows[row][table.Columns[col].ColumnName].ToString());
                                }
                            } // end for (int i = 1; i < table.Columns.Count; i++)

                            // close value block
                            queryBuilder.Append(")");
                            queryBuilder.AppendLine();

                        } // end for (int r = 1; r < table.Rows.Count; r++)

                        // sql delimiter =)
                        queryBuilder.Append(";");

                    } // end if (table.Rows.Count > 1)

                    return queryBuilder.ToString();
                } 
                else
                {
                    return "";
                } // end if(table.Columns.Count > 1 && table.Rows.Count > 0)
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return "";
            }
        }
    }
}

Tested this within my application. Took around 3 seconds for ~30K rows with this!! Not beatable by anything i’ve found :O

there you go, I hope this helps some ppl out there!

 

Edit: some polishing 😉

12 Antworten zu “C# DataTable to MySQL”

  1. Could you please provide an example of the usage of this? I do not see connection part. Also when I try to use the name from the target mysql table it won’t allow it.

  2. I do agree with all the ideas you have introduced for your post. They are really convincing and can certainly work. Still, the posts are very brief for newbies. May you please extend them a bit from subsequent time? Thank you for the post.

    1. Well yeah, but these posts are no guides and I don’t intend to write them as such. I post them first of all for myself so I don’t forget it and obviously that I might help ppl searching for similar solutions. But I don’t target newbies and probably never will since I’m way to lazy :(. If you have some suggestions to posts feel free to comment and I’ll add them!

  3. Oh my goodness! Amazing article dude! Thanks, However
    I am encountering troubles with your RSS. I don’t know why I am unable to subscribe to it.

    Is there anyone else getting the same RSS issues?
    Anybody who knows the solution will you kindly respond?
    Thanx!!

  4. I am really impressed with your writing skills as well as with the layout on your weblog. Is this a paid theme or did you modify it yourself? Anyway keep up the excellent quality writing, its rare to see a great blog like this one nowadays

  5. Hey would you mind letting me know which web host you’re working with? I’ve loaded your blog in 3 completely different internet browsers and I must say this blog loads a lot faster then most. Can you suggest a good internet hosting provider at a fair price? Kudos, I appreciate it!

    1. Oh that’s my own private vServer hosted by the german provider netcup (netcup.de). It’s fairly cheap (I’m paying 9€/Month for 1 vCPU with 4 GB RAM and 240 GB HDD space). As for the tech side: The Server is running on nginx with php-fpm and spdy 3.1 enabled. Hope this helps you a little!

Kommentar verfassen

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.