Fastest way to Bulk Insert Complete Dataset with 6 Datatables with Rollback

Question

User-1971168174 posted

Hi ,

Can someone share a working example to to insert complete dataset with six datatables in to sql database in respective tables.

In case of error everything to be rolled back.

Thanks!

Answer 1

User1535942433 posted

Hi neerajkumarmodi,

Accroding to your description,I'm guessing that your database is mysql.I suggest you could use Bulk Insert MySQL just like SqlBulkCopy from MS SQL.

I create a demo with insert one  datatable into one database table.

More details,you could refer to below codes:

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

However,if you have datatables to push into database,the best way is  to write it to a .csv and use LOAD DATA INFILE in mysql.

More details,you could refer to below article:

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

Best regards,

Yijing Sun

 

Answer 2

User-1971168174 posted

Thanks or taking out time! I got this working with your help.

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

    
}