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.
If I misunderstood what you mean, please let me know.
Best regards,
Xudong Peng
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
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
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
58 Points
176 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
Thanks in advance!
Contributor
2340 Points
736 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:
Result:
If I misunderstood what you mean, please let me know.
Best regards,
Xudong Peng
Member
58 Points
176 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) { } } } }
Contributor
2340 Points
736 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:
Best regards,
Xudong Peng