locked
Make Insert generic for Dataset RRS feed

  • Question

  • User-1971168174 posted

    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!

    Monday, August 3, 2020 6:57 AM

Answers

  • User-939850651 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 6, 2020 2:20 AM

All replies

  • User-939850651 posted

    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

    Tuesday, August 4, 2020 9:44 AM
  • User-1971168174 posted

    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)
                {
    
                }
               
            }
    
           
        }
        
    }
    
    

    Wednesday, August 5, 2020 8:20 AM
  • User-939850651 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 6, 2020 2:20 AM