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.

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(Pricetypeof(string)) }); StringBuilder sCommand = new StringBuilder(INSERT INTO esugamstock (ProductCode PackOfPrice) 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(Pricetypeof(string)) }); StringBuilder sCommand = new StringBuilder(INSERT INTO User (ProductCode PackOfPrice) VALUES ); List Rows = new List (); foreach (GridViewRow row in GrvProduct.Rows) { Rows.Add(string.Format(('{0}''{1}''{2}') row.Cells[2].Textrow.Cells[2].Textrow.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(Pricetypeof(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

Himanshu Jaiswal · 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/