Insert datatable into a mysql table using c#

Question

I am having a mysql table and I need to update it with datatable using C#.

I achieved this in sql using the following code:

DataTable table;
using (SqlConnection connection = new SqlConnection(connectionString))  
{
    connection.Open();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
       {
           bulkCopy.DestinationTableName = destinationTable;
           bulkCopy.WriteToServer(table);
       }
 }

Is there a way to achieve this for mysql?

score 6 · Accepted Answer · answered Apr 14 '19 at 13:16

Bulk Insert MySQL (like SqlBulkCopy from MS SQL)

This will do the trick:

    public void Start(string tableName, List<ClsLink> linkList)
    {            
        DataTable table = new DataTable();

        // Getting datatable layout from database
        table = GetDataTableLayout(tableName);

        // Pupulate datatable
        foreach (ClsLink link in linkList)
        {
            DataRow row = table.NewRow();                
            //row["LinkURL"] = link.LinkURL;
            //row["CreateDate"] = link.CreateDate;
            //row["Titel"] = link.Titel;
            table.Rows.Add(row);
        }

        BulkInsertMySQL(table, tableName);
        // Enjoy
    } 


    public DataTable GetDataTableLayout(string tableName)
    {
        DataTable table = new DataTable();

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            // Select * is not a good thing, but in this cases is is very usefull to make the code dynamic/reusable 
            // We get the tabel layout for our DataTable
            string query = $"SELECT * FROM " + tableName + " limit 0";
            using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection))
            {
                adapter.Fill(table);
            };
        }

        return table;
    }

    public void BulkInsertMySQL(DataTable table, string tableName)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            using (MySqlTransaction tran = connection.BeginTransaction(IsolationLevel.Serializable))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.Connection = connection;
                    cmd.Transaction = tran;
                    cmd.CommandText = $"SELECT * FROM " + tableName + " limit 0";

                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                    {
                        adapter.UpdateBatchSize = 10000;
                        using (MySqlCommandBuilder cb = new MySqlCommandBuilder(adapter))
                        {
                            cb.SetAllValues = true;
                            adapter.Update(table);
                            tran.Commit();
                        }
                    };
                }
            }
        }
    }

// Enjoy

score 0 · Answer 2 · answered Dec 29 '17 at 07:52

0

You can use the MySqlBulkLoader class.

An example of its usage is available on the MySql website https://dev.mysql.com/doc/connector-net/en/connector-net-programming-bulk-loader.html

answered Dec 29 '17 at 07:52

pmcilreavy

3,076
2
28
37

score 0 · Answer 3 · answered Feb 17 '22 at 14:37

Public Function BulkCopyMySql(ByRef table As DataTable, ByVal table_name As String) As String
    Try
        Dim queryBuilder As StringBuilder = New StringBuilder()
        Dim dt As DateTime
        queryBuilder.AppendFormat("INSERT INTO `{0}` (", table_name)

        If table.Columns.Count > 1 AndAlso table.Rows.Count > 0 Then
            queryBuilder.AppendFormat("`{0}`", table.Columns(0).ColumnName)

            If table.Columns.Count > 1 Then

                For i As Integer = 1 To table.Columns.Count - 1
                    queryBuilder.AppendFormat(", `{0}` ", table.Columns(i).ColumnName)
                Next
            End If

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

            If table.Columns(0).DataType = GetType(String) Then
                queryBuilder.AppendFormat("'{0}'", MySqlHelper.EscapeString(table.Rows(0)(table.Columns(0).ColumnName).ToString()))
            ElseIf table.Columns(0).DataType = GetType(DateTime) Then
                dt = CType(table.Rows(0)(table.Columns(0).ColumnName), DateTime)
                queryBuilder.AppendFormat("'{0}'", dt.ToString("yyyy-MM-dd HH:mm:ss"))
            ElseIf table.Columns(0).DataType = GetType(Int32) Then
                queryBuilder.AppendFormat("{0}", If(table.Rows(0).Field(Of Int32?)(table.Columns(0).ColumnName), 0))
            Else
                queryBuilder.AppendFormat(", {0}", table.Rows(0)(table.Columns(0).ColumnName).ToString())
            End If

            For i As Integer = 1 To table.Columns.Count - 1

                If table.Columns(i).DataType = GetType(String) Then
                    queryBuilder.AppendFormat(", '{0}'", MySqlHelper.EscapeString(table.Rows(0)(table.Columns(i).ColumnName).ToString()))
                ElseIf table.Columns(i).DataType = GetType(DateTime) Then
                    dt = CType(table.Rows(0)(table.Columns(i).ColumnName), DateTime)
                    queryBuilder.AppendFormat(", '{0}'", dt.ToString("yyyy-MM-dd HH:mm:ss"))
                ElseIf table.Columns(i).DataType = GetType(Int32) Then
                    queryBuilder.AppendFormat(", {0}", If(table.Rows(0).Field(Of Int32?)(table.Columns(i).ColumnName), 0))
                Else
                    queryBuilder.AppendFormat(", {0}", table.Rows(0)(table.Columns(i).ColumnName).ToString())
                End If
            Next

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

            If table.Rows.Count > 1 Then

                For row As Integer = 1 To table.Rows.Count - 1
                    queryBuilder.Append(", (")

                    If table.Columns(0).DataType = GetType(String) Then
                        queryBuilder.AppendFormat("'{0}'", MySqlHelper.EscapeString(table.Rows(row)(table.Columns(0).ColumnName).ToString()))
                    ElseIf table.Columns(0).DataType = GetType(DateTime) Then
                        dt = CType(table.Rows(row)(table.Columns(0).ColumnName), DateTime)
                        queryBuilder.AppendFormat("'{0}'", dt.ToString("yyyy-MM-dd HH:mm:ss"))
                    ElseIf table.Columns(0).DataType = GetType(Int32) Then
                        queryBuilder.AppendFormat("{0}", If(table.Rows(row).Field(Of Int32?)(table.Columns(0).ColumnName), 0))
                    Else
                        queryBuilder.AppendFormat(", {0}", table.Rows(row)(table.Columns(0).ColumnName).ToString())
                    End If

                    For col As Integer = 1 To table.Columns.Count - 1

                        If table.Columns(col).DataType = GetType(String) Then
                            queryBuilder.AppendFormat(", '{0}'", MySqlHelper.EscapeString(table.Rows(row)(table.Columns(col).ColumnName).ToString()))
                        ElseIf table.Columns(col).DataType = GetType(DateTime) Then
                            dt = CType(table.Rows(row)(table.Columns(col).ColumnName), DateTime)
                            queryBuilder.AppendFormat(", '{0}'", dt.ToString("yyyy-MM-dd HH:mm:ss"))
                        ElseIf table.Columns(col).DataType = GetType(Int32) Then
                            queryBuilder.AppendFormat(", {0}", If(table.Rows(row).Field(Of Int32?)(table.Columns(col).ColumnName), 0))
                        Else
                            queryBuilder.AppendFormat(", {0}", table.Rows(row)(table.Columns(col).ColumnName).ToString())
                        End If
                    Next

                    queryBuilder.Append(")")
                    queryBuilder.AppendLine()
                Next

                queryBuilder.Append(";")
            End If


            Dim str As String = createMYsqltable(table, table_name)
            GetMYSQL(str)

            GetMYSQL(queryBuilder.ToString())

            Return queryBuilder.ToString()
        Else
            Return ""
        End If

    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try
End Function

Hi and thanks for your answer. That is a lot of code with little explanation at what it does. For the benefit of everyone, a wel ldocumented and explained answer is much more valuable to the community than a lone blob of code. It would thous be great if you could write a short explanation at what your code does and how it solves the OPs problem — Simas Joneliunas, Feb 23 '22 at 02:15

Ahmad Omar · Answer 4 · 2022-09-02T21:15:34.610

I writed this method to insert a datatable once into a database table.

Note: don't forget to declare missing variables before intialize it

public static void InsertDataTable (string tableName, DataTable dataTable)
        {
            try
            {
                string strSQl = "SELECT * FROM " + clsDAL.dbName + "." + tableName;
                using (TransactionScope scope = new TransactionScope())
                {
                    con = new MySqlConnection(strConnection);
                    dataAdapter = new MySqlDataAdapter(strSQl, con);
                    con.Open();
                    cmdBuilder = new MySqlCommandBuilder(dataAdapter);
                    dataAdapter.Update(dataTable);
                    con.Close();
                    scope.Complete();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

Insert datatable into a mysql table using c#

4 Answers4

Linked