Kris Krause .NET Meister

"If it is fast and ugly, they will use it and curse you; if it is slow, they will not use it."
- David Cheriton, The Art of Computer Systems Performance Analysis

Wednesday, April 08, 2009

MySql Connector .NET MySqlBulkLoader Example

Here is a MySqlBulkLoader example I quickly wrote to import 65,000 records into a mySql 5.1 database using the mySql Connector 5.2.5. And yes, its fast as snot (the lead programmer at my first programming job out of college invented the term).

.NET Reflector helped me figure out the follow details:

- the default FieldTerminator is "\t"
- the default LineTerminator is "\n"
- the default FieldQuotationCharacter is '\0'
- the .Load() method both opens and closes the connection

Also, the mySql documentation obviously helped to explain a lot including the "\r\n" Win32 line terminator in my example (I used Excel to export the CSV). The database user in this example needs "insert" permission.

Download the VB.NET, Sql, and CSV files here.

Labels: ,

posted by Kristofer Krause at 11:22:00 AM

6 Comments:

  • Hi there, I have tried out your code sample but keep getting the following error message:

    Object reference not set to an instance of an object.

    result = loader.Load();

    Do you have any idea why this might happen. I wondered if there is just a problem opening the file.

    Cheers,


    Nick

    By Anonymous Anonymous, at 7:12 AM  

  • The 'Object reference not set to an instance of an object.' error was being caused as the ASPNET user account did not have permission to access the file.

    Once permissions were set I was able to load the data successfully.

    Thanks for the code sample!

    Nice blog as well by the way! It is good to see other .NET developers working with mySQL.

    Cheers,


    Nick

    By Anonymous Anonymous, at 8:17 AM  

  • Set a break point... and double check that your "conn" and "loader" are initialized correctly.

    Which version of the connector are you using?

    By Blogger Kristofer Krause, at 9:30 AM  

  • Thanks Nick! And yes, mySQL is a very effective storage option for the .NET Framework. However, with Linq to mySQL and/or the Entity framework... things get a little hairy.

    By Blogger Kristofer Krause, at 10:15 AM  

  • I've been banging my head against a wall trying to figure out what's wrong with my MySQL 5.1 connector .net with asp .net 2.0. The website I'm working on is intended to be a read-only search and display database accessible to Everyone. I can view it when on my campus domain or logged in on the VPN, but when I try access from a remote web address, no go. I'm just about to see if I can turn on trace to see if it gets that far. Can you suggest where I can go to get some help for the nebiw with .Net and MySQL? Thanks.

    By Anonymous Anonymous, at 8:33 PM  

  • Sounds like a security issue, not a connector issue. Double check to make sure that you can access the database through the mysql command prompt.

    By Blogger Kristofer Krause, at 8:36 PM  

Post a Comment

<< Home