Click here to Skip to main content
11,632,715 members (91,717 online)
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 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 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

view[^]
  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 OriginalGriff 8,498
1 Sergey Alexandrovich Kryukov 8,149
2 Mika Wendelius 6,260
3 F-ES Sitecore 2,321
4 Suvendu Shekhar Giri 1,995


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