Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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
Sergey Alexandrovich Kryukov at 8-Jan-13 21:12pm
   
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 at 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 ^], 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 at 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 at 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
 
^]
  Permalink  
Comments
jibesh at 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
0 mhegazy94 488
1 Sergey Alexandrovich Kryukov 375
2 Kornfeld Eliyahu Peter 285
3 Sibeesh KV 251
4 Gihan Liyanage 189
0 Sergey Alexandrovich Kryukov 6,775
1 OriginalGriff 6,696
2 CPallini 5,345
3 George Jonsson 3,599
4 Gihan Liyanage 2,751


Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2014
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