CODEWITHSUNDEEP
c#mysqlsql
s p
s p

Reputation: 799

Insert datatable into a mysql table using c#

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?

Upvotes: 0

Views: 13039

Answers (5)

M M
M M

Reputation: 21

The answer by Skak2000 is so elegant and works great! However, one comment if I may. Since I populated my source datatable (called "table" in the Start module) from a physical table in a database, not from a list, if I pass that datatable to the BulkInsertMySQL module, the Update method will not insert records from it. I guess, the Fill method does not mark filled records as new, so the Update method does not insert them. I created a new datatable, added all the records from the table datatable into this new datatable using the Add method, and then ran the Update method, and the records inserted into the target database table from this new datatable.

Upvotes: 0

Met
Met

Reputation: 11

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

Upvotes: 0

James Baillie
James Baillie

Reputation: 57

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

Upvotes: -1

Skak2000
Skak2000

Reputation: 84

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

Upvotes: 6

Paul
Paul

Reputation: 3306

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

Upvotes: 0

Related Questions