how to store bulk data to mysql database

Question

Here i'm storing bulk data to database, i got sample code for MSSQL database but i need to store data in mysql database. how can i store it please help me.

Himanshu Gupta · Answer

Hi Fareed, You have need to create a Tabled valued dataType into the database and create those columns in it that's value you want to store. And then in codebase create a dataTable Put the column name and type same as Table Valued Data Type. Now you can pass this dataTable As Parameter into you ADO.Net Parameter if you use It. e.g, InSql Create parameter, @paramterName TableValuedDataType READONLY, Pass datatable from your codebase to this parameter. You can take help from this link: http://www.c-sharpcorner.com/UploadFile/ff2f08/table-value-parameter-use-with-C-Sharp/

Sandy · Answer

Hi, Create a function which take Datatable as Parameter and create same funciton in BAL and DAL. Thanks

fareed fa · Answer

this code working fine, but i'm using 3 tier application in my project so i need to send this data to business layer and to data access layer. so please help me, that how can send this code to business logic DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[3] { new DataColumn("ProductCode", typeof(string)), new DataColumn("PackOf", typeof(string)), new DataColumn("Price",typeof(string)) }); StringBuilder sCommand = new StringBuilder("INSERT INTO esugamstock (ProductCode, PackOf,Price) VALUES "); List Rows = new List (); foreach (GridViewRow row in GrvProduct.Rows) { Rows.Add(string.Format("('{0}','{1}','{2}')", row.Cells[1].Text, row.Cells[3].Text, row.Cells[4].Text)); string pcode = row.Cells[1].Text; string pack = row.Cells[3].Text; string price = row.Cells[4].Text; dt.Rows.Add(pcode, pack, price); } if (Rows.Count > 0) { sCommand.Append(string.Join(",", Rows)); sCommand.Append(";"); using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["Panel"].ToString())) { con.Open(); using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), con)) { myCmd.CommandType = CommandType.Text; myCmd.ExecuteNonQuery(); } con.Close(); } }

Sandy · Answer

Hi Fareed , It is same as SQLBulkCopy your application is 2tier or n-tier it doesn't matter and also we did not user StoreProcedure in SQLBulkCopy. If you have any issue than Kindly explain more if it is related to this else create a new question. Thanks

fareed fa · Answer

i got that code and it is working fine but here i'm using 3 tier application using stored proceduce so how can i send datatable data to database using 3 tier application

Sandy · Answer

HI Fareed, Try below code DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[3] { new DataColumn("ProductCode", typeof(string)), new DataColumn("PackOf", typeof(string)), new DataColumn("Price",typeof(string)) }); StringBuilder sCommand = new StringBuilder("INSERT INTO User (ProductCode, PackOf,Price) VALUES "); List Rows = new List (); foreach (GridViewRow row in GrvProduct.Rows) { Rows.Add(string.Format("('{0}','{1}','{2}')", row.Cells[2].Text,row.Cells[2].Text,row.Cells[3].Text)); string pcode = row.Cells[2].Text; string pack = row.Cells[2].Text; string price = row.Cells[3].Text; dt.Rows.Add(pcode, pack, price); } if( Rows.Count>0) { sCommand.Append(string.Join(",", Rows)); sCommand.Append(";"); using (MySqlConnection con = new MySqlConnection(consString)) { con.Open(); using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection)) { myCmd.CommandType = CommandType.Text; myCmd.ExecuteNonQuery(); } con.Close(); } This code insert 100k records in 3 seconds. Thanks

fareed fa · Answer

this is the code of MSSQL, but i need to use MySQl DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[3] { new DataColumn("ProductCode", typeof(string)), new DataColumn("PackOf", typeof(string)), new DataColumn("Price",typeof(string)) }); foreach (GridViewRow row in GrvProduct.Rows) { string pcode = row.Cells[2].Text; string pack = row.Cells[2].Text; string price = row.Cells[3].Text; dt.Rows.Add(pcode, pack, price); } if (dt.Rows.Count > 0) { string consString = ConfigurationManager.ConnectionStrings["panel"].ConnectionString; using (MySqlConnection con = new MySqlConnection(consString)) { using (MySqlBulkLoader sqlBulkCopy = new MySqlBulkLoader(con)) { //Set the database table name sqlBulkCopy.TableName = "esugamstock"; //[OPTIONAL]: Map the DataTable columns with that of the database table sqlBulkCopy.ColumnMappings.Add("ProductCode", "ProductCode"); sqlBulkCopy.ColumnMappings.Add("PackOf", "PackOf"); sqlBulkCopy.ColumnMappings.Add("Price", "Price"); con.Open(); sqlBulkCopy.WriteToServer(dt); con.Close(); } } }

Sandy · Answer

Hi Fareed, You can create the comma separated MySql Query and insert the mulitple rows in one hit. E.g. INSERT INTO Table1 (column1, column2, column3, column4) VALUES (1, 'Test 1', 'Name 1', 'LastName 1'), (2, 'Test 2', 'Name 2', 'LastName 2'), (3, 'Test 3', 'Name 3', 'LastName 3') Thanks