locked
Bulk Insert asp.net Datatable to Mysql RRS feed

  • Question

  • User-1971168174 posted

    Hi Support,

    I am trying to do bulk insert my datatable to mysql table.

    Below code works for inserting table when fetching from table ..but i need to pass Datatable and insert..pl suggest or if you can provide working sample

    using (MySqlTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
    {
        using (MySqlCommand cmd = new MySqlCommand())
        {
            cmd.Connection = conn;
            cmd.Transaction = tran;
            cmd.CommandText = "SELECT * FROM testtable";
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                da.UpdateBatchSize = 1000;
                using (MySqlCommandBuilder cb = new MySqlCommandBuilder(da))
                {
                    da.Update(rawData);
                    tran.Commit();
                }
            }
        }
    }
    Monday, July 27, 2020 6:41 PM

Answers

  • User-1971168174 posted

    Got this working with following code..

    using MySql.Data.MySqlClient;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Net;
    using System.Net.Security;
    using System.Text;
    using System.Configuration;
    using System.Threading;
    using System.Threading.Tasks;
    
    using System.Xml;
    using Newtonsoft.Json;
    using System.Globalization;
    
    namespace Data
    {
       
        class Program
        {
            static void Main(string[] args)
            {
                DataSet ds = SampleData();
                DataTable dt = ds.Tables[0];
    
                BulkInsertMySQL(dt, "sample.SampleData");
                // Enjoy
            }
    
    
    
           
    
            public static void BulkInsertMySQL(DataTable table, string tableName)
            {
                string ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                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();
                                }
                            };
                        }
                    }
                }
            }
    
            private static DataSet SampleData()
            {
                DataSet sampleDataSet = new DataSet();
                sampleDataSet.Locale = CultureInfo.InvariantCulture;
                DataTable sampleDataTable = sampleDataSet.Tables.Add("SampleData");
    
                sampleDataTable.Columns.Add("FirstColumn", typeof(string));
                sampleDataTable.Columns.Add("SecondColumn", typeof(string));
                DataRow sampleDataRow;
                for (int i = 1; i <= 1; i++)
                {
                    sampleDataRow = sampleDataTable.NewRow();
                    sampleDataRow["FirstColumn"] = "Cell1: " + i.ToString(CultureInfo.CurrentCulture);
                    sampleDataRow["SecondColumn"] = "Cell2: " + i.ToString(CultureInfo.CurrentCulture);
                    sampleDataTable.Rows.Add(sampleDataRow);
                }
    
                return sampleDataSet;
            }
        }
    
        
    }
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 29, 2020 1:26 PM