Make Insert generic for Dataset[Answered] RSS

3 replies

Last post Aug 06, 2020 02:20 AM by XuDong Peng

‹ Previous Thread|Next Thread ›

Shortcuts

neerajkumarm...

Member

56 Points

169 Posts

Make Insert generic for Dataset

Aug 03, 2020 06:57 AM|neerajkumarmodi|LINK

Hi Support,

Below Insert works well for single datatable, I need to make it generic so that I can pass a Dataset and all datatable gets inserted one by one.

Please help me making this working for dataset. Datatable to be read and columns name to be pass dynamically and insert should work within transaction

 public static void BulkToMySQL1(DataTable dt)
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            StringBuilder sCommand = new StringBuilder("INSERT INTO queue (a, b,c,d,e,f,g,h) VALUES ");
            using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
            {
                List<string> Rows = new List<string>();

                foreach (DataRow row in dt.Rows)
                {
                    Rows.Add(string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", MySqlHelper.EscapeString(row["a"].ToString()), MySqlHelper.EscapeString(row["b"].ToString()), MySqlHelper.EscapeString(row["c"].ToString()), MySqlHelper.EscapeString(row["d"].ToString()), MySqlHelper.EscapeString(row["e"].ToString()), null, null, null));
                }

                sCommand.Append(string.Join(",", Rows));
                sCommand.Append(";");
                mConnection.Open();
                using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))
                {
                    myCmd.CommandType = CommandType.Text;
                    myCmd.ExecuteNonQuery();
                }
            }
        }

Thanks in advance!

Manish

XuDong Peng

Participant

1050 Points

319 Posts

Re: Make Insert generic for Dataset

Aug 04, 2020 09:44 AM|XuDong Peng|LINK

Hi neerajkumarmodi,

According to your description, I think you can achieve your requirements by traversing the DataTable in the DataSet. I wrote a simple example using the code you provided and modified part of the code.

Please refer to the following code:

protected void Page_Load(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            DataTable dt1 = new DataTable();
            dt1.TableName = "TestTable1";
            dt1.Columns.Add("col1", typeof(String));
            dt1.Columns.Add("col2", typeof(String));
            dt1.Columns.Add("col3", typeof(String));
            dt1.Rows.Add("111", "aaa", "xxx");
            dt1.Rows.Add("222", "bbb", "yyy");
            dt1.Rows.Add("333", "ccc", "zzz");
            ds.Tables.Add(dt1);
            DataTable dt2 = new DataTable();
            dt2.TableName = "TestTable2";
            dt2.Columns.Add("col4", typeof(String));
            dt2.Columns.Add("col5", typeof(String));
            dt2.Columns.Add("col6", typeof(String));
            dt2.Columns.Add("col7", typeof(String));
            dt2.Rows.Add("444", "ddd", "ttt");
            dt2.Rows.Add("555", "eee", "rrr");
            ds.Tables.Add(dt2);
            DataTable dt3 = new DataTable();
            dt3.TableName = "TestTable3";
            dt3.Columns.Add("col8", typeof(String));
            dt3.Columns.Add("col9", typeof(String));
            dt3.Columns.Add("col10", typeof(String));
            dt3.Columns.Add("col11", typeof(String));
            dt3.Columns.Add("col12", typeof(String));
            dt3.Rows.Add("666", "fff", "ooo");
            dt3.Rows.Add("777", "ggg", "ppp");
            dt3.Rows.Add("888", "hhh", "qqq");
            dt3.Rows.Add("999", "iii", "kkk");
            ds.Tables.Add(dt3);

            BulkToMySQL1(ds);
        }

        public static void BulkToMySQL1(DataSet ds)
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            StringBuilder allBuilder = new StringBuilder();
            foreach (DataTable dt in ds.Tables)
            {
                //get DataTable name
                string tableName = dt.TableName.ToString();
                int ColCount = dt.Columns.Count;
                //concat the query string
                string query = "insert into " + tableName + " (";

                for (int i = 0; i < ColCount; i++)
                {
                    query += dt.Columns[i].ColumnName.ToString() + ",";
                }
                query = query.Substring(0, query.Length - 1);
                query += ") values ";
                StringBuilder builder = new StringBuilder(query);

                List<string> Rows = new List<string>();
                foreach (DataRow row in dt.Rows)
                {
                    string query2 = "(";
                    for (int i = 0; i < ColCount; i++)
                    {
                        //query2 += "'" + ((char)(i + 97)).ToString() + "',";
                        query2 += "'" + MySqlHelper.EscapeString(row[((char)(i + 97)).ToString()].ToString()) + "',";
                    }
                    query2 = query2.Substring(0, query2.Length - 1);
                    query2 += ")";
                    Rows.Add(query2);
                }

                builder.Append(string.Join(",", Rows));
                builder.Append(";");
                allBuilder.Append(builder.ToString());
            }
            using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
            //using (SqlConnection mConnection = new SqlConnection(ConnectionString))
            {
                mConnection.Open();
                using (MySqlCommand myCmd = new MySqlCommand(allBuilder.ToString(), mConnection))
                //using (SqlCommand myCmd = new SqlCommand(allBuilder.ToString(), mConnection))
                {
                    myCmd.CommandType = CommandType.Text;
                    myCmd.ExecuteNonQuery();
                }
            }
        }

Result:

If I misunderstood what you mean, please let me know.

Best regards,

Xudong Peng

neerajkumarm...

Member

56 Points

169 Posts

Re: Make Insert generic for Dataset

Aug 05, 2020 08:20 AM|neerajkumarmodi|LINK

Thank You XuDong for taking out time!

I am getting error in below line , may be column names have to be passed

query2 += "'" + MySqlHelper.EscapeString(row[((char)(i + 97)).ToString()].ToString()) + "',";

My Code

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Globalization;
using System.Text;

namespace DataStagingParsing
{
    static class Program
    {
        static void Main(string[] args)
        {
            

            DataTable dt1 = SampleData("sampledata1");
            DataTable dt2 = SampleData1("sampledata2");
           

            DataSet ds = new DataSet();
            ds.Tables.Add(dt1.Copy());
            ds.Tables.Add(dt2.Copy()); 
            BulkToMySQL1(ds);
        }

        private static DataTable SampleData(string dtname)
        {
            DataSet sampleDataSet = new DataSet();
            sampleDataSet.Locale = CultureInfo.InvariantCulture;
            DataTable sampleDataTable = sampleDataSet.Tables.Add(dtname);

            sampleDataTable.Columns.Add("a", typeof(string));
            sampleDataTable.Columns.Add("b", typeof(string));
            sampleDataTable.Columns.Add("c", typeof(string));
            DataRow sampleDataRow;
            for (int i = 1; i <= 1; i++)
            {
                sampleDataRow = sampleDataTable.NewRow();
                sampleDataRow["a"] = "Good";
                sampleDataRow["b"] = "Good1";
                sampleDataRow["c"] = "Good2";
                sampleDataTable.Rows.Add(sampleDataRow);
            }

            return sampleDataSet.Tables[0];
        }
        private static DataTable SampleData1(string dtname)
        {
            DataSet sampleDataSet = new DataSet();
            sampleDataSet.Locale = CultureInfo.InvariantCulture;
            DataTable sampleDataTable = sampleDataSet.Tables.Add(dtname);

            sampleDataTable.Columns.Add("e", typeof(string));
            sampleDataTable.Columns.Add("f", typeof(string));
            sampleDataTable.Columns.Add("g", typeof(string));
            DataRow sampleDataRow;
            for (int i = 1; i <= 1; i++)
            {
                sampleDataRow = sampleDataTable.NewRow();
                sampleDataRow["e"] = "Good";
                sampleDataRow["f"] = "Good1";
                sampleDataRow["g"] = "Good2";
                sampleDataTable.Rows.Add(sampleDataRow);
            }

            return sampleDataSet.Tables[0];
        }
        private static void BulkToMySQL1(DataSet ds)
        {
            try {
                string ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                StringBuilder allBuilder = new StringBuilder();
                foreach (DataTable dt in ds.Tables)
                {
                    //get DataTable name
                    string tableName = dt.TableName.ToString();
                    int ColCount = dt.Columns.Count;
                    //concat the query string
                    string query = "insert into " + tableName + " (";

                    for (int i = 0; i < ColCount; i++)
                    {
                        query += dt.Columns[i].ColumnName.ToString() + ",";
                    }
                    query = query.Substring(0, query.Length - 1);
                    query += ") values ";
                    StringBuilder builder = new StringBuilder(query);

                    List<string> Rows = new List<string>();
                    foreach (DataRow row in dt.Rows)
                    {
                        string query2 = "(";
                        for (int i = 0; i < ColCount; i++)
                        {
                            //query2 += "'" + ((char)(i + 97)).ToString() + "',";
                            query2 += "'" + MySqlHelper.EscapeString(row[((char)(i + 97)).ToString()].ToString()) + "',";
                        }
                        query2 = query2.Substring(0, query2.Length - 1);
                        query2 += ")";
                        Rows.Add(query2);
                    }

                    builder.Append(string.Join(",", Rows));
                    builder.Append(";");
                    allBuilder.Append(builder.ToString());
                }
                using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
                //using (SqlConnection mConnection = new SqlConnection(ConnectionString))
                {
                    mConnection.Open();
                    using (MySqlCommand myCmd = new MySqlCommand(allBuilder.ToString(), mConnection))
                    //using (SqlCommand myCmd = new SqlCommand(allBuilder.ToString(), mConnection))
                    {
                        myCmd.CommandType = CommandType.Text;
                        myCmd.ExecuteNonQuery();
                    }
                }
            }
            catch(Exception e)
            {

            }
           
        }

       
    }
    
}

Manish

XuDong Peng

Participant

1050 Points

319 Posts

Re: Make Insert generic for Dataset

Aug 06, 2020 02:20 AM|XuDong Peng|LINK

Hi neerajkumarmodi,

The column name as a parameter is obtained from the DataTable. Try this:

foreach (DataRow row in dt.Rows)
                    {
                        string query2 = "(";
                        for (int i = 0; i < ColCount; i++)
                        {
                            //query2 += "'" + ((char)(i + 97)).ToString() + "',";
                            query2 += "'" + MySqlHelper.EscapeString(row[dt.Columns[i].ColumnName.ToString()].ToString()) + "',";
                        }
                        query2 = query2.Substring(0, query2.Length - 1);
                        query2 += ")";
                        Rows.Add(query2);
                    }

Best regards,

Xudong Peng

‹ Previous Thread|Next Thread ›