Click here to Skip to main content
12,396,095 members (57,189 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# C#4.0
Hello Team,
I have a requirement where i have a datagridview, into which user copies data of 30,000 records, i need to save this to mySql Database

since insertion of each record from the grid to database is very slow and taking time, i am seaching for a solution like bulk insertion of datatable into mySql database

is there any possibility of inserting datatable to mySql Database table in bulk
Posted 8-Jan-13 15:05pm
Comments
   
Too late: if you have a DataGridView with 30,000 records (really? without paging? if pages, backed by what?), you already did something wrong. :-)
—SA
Kollu Lakshmi 8-Jan-13 21:16pm
   
I'm using telerik gridview which is providng the facility to copy and paste from excel, and also i am not doing web applications its a datagridview in winforms

Now i have 30,000 records in my grid, i need to save to mySql Database, i am searching something like bulk insert

if you have any solution please help me

thank you
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You need to first download the MySQL Connector for .NET which can be download from here[^], considering you have selected all the records from the table means you already have it.

once you are done with the installation of MySQL connector, use the following code

var bl = new MySqlBulkLoader(connection);
bl.TableName = "mytable";
bl.FieldTerminator = ",";
bl.LineTerminator = "\r\n";
bl.FileName = "myfileformytable.csv";
bl.NumberOfLinesToSkip = 1;
var inserted = bl.Load();
Debug.Print(inserted + " rows inserted.");
  Permalink  
Comments
Kollu Lakshmi 8-Jan-13 21:39pm
   
Hello Hasham
i have the data in my grid, i converted the grid datasource to datatable,
now i want to insert the datatable to mySql database in bulk, the solution you have given is for insertion of data from .csv, i need it from datatable, is there any possibility
Kollu Lakshmi 10-Jan-13 1:32am
   
MySqlBulkLoader not filtering the data of csv with OPTIONALLY ENCLOSED BY """"

data is importing into table as "1" "2" "3", i mean with quotes

is there any option in bulk loader to select the data of csv without enclosed quotes

there is an option in LOAD DATA INFILE, but there is no option of "OPTIONALLY ENCLOSED BY """" with MySQLBulkLOader class
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Please have a look at the following post, seems like it will be helpful for you

view[^]
  Permalink  
Comments
jibesh 8-Jan-13 23:26pm
   
do not post your solutions as individual solution here.

If you have any modification to your solution use 'Improve Solution' widget.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100