This project has moved. For the latest updates, please go here.

MySQL support

Jun 19, 2014 at 2:37 PM
Is there a provider for MySQL? I know I could write one, but I'd rather not re-invent the wheel.
Jun 19, 2014 at 4:47 PM
you can use the mysql/net connector and its provider
Jun 19, 2014 at 4:58 PM
Excuse my ignorance, but how would I do that?

I am getting the following exception:
Message=BulkInsertProvider not found for 'MySql.Data.MySqlClient.MySqlConnection.
To register new provider use EntityFramework.BulkInsert.ProviderFactory.Register() method'

but I don't know what to pass to the Register method.

Thanks for your help.
Jun 19, 2014 at 8:34 PM
Edited Jun 19, 2014 at 8:43 PM
ah yes, I'm just got that same error message when trying to bulkinsert data to mysql.

I think the problem is with his ProviderFactory class method where it only has

Register<EfSqlBulkInsertProviderWithMappedDataReader>("System.Data.SqlClient.SqlConnection");

I think we need to add something like

Register<EfSqlBulkInsertProviderWithMappedDataReader>("MySql.Data.MySqlClient");
Jun 20, 2014 at 12:55 PM
I added

EntityFramework.BulkInsert.ProviderFactory.Register<EfSqlBulkInsertProviderWithMappedDataReader>("MySql.Data.MySqlClient.MySqlConnection");

but then I got the following error:

Field _connectionString was not found in Type MySql.Data.MySqlClient.MySqlConnection
Jun 25, 2014 at 12:26 PM
Unfortunately there is no easy way to do this.

You have to create your own provider and implement mysql bulk insert. Because EfSqlBulkInsertProviderWithMappedDataReader uses SqlBulkCopy under the hood -which works only with sql server.

MySql provider should look something like this
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.ExceptionServices;
using System.Text;
using System.Threading.Tasks;
using EntityFramework.BulkInsert.Helpers;
using EntityFramework.BulkInsert.Providers;
using EntityFramework.MappingAPI;
using MySql.Data.MySqlClient;

namespace EntityFramework.BulkInsert.MySql
{
    public class Class1 : ProviderBase<MySqlConnection, MySqlTransaction>
    {
        private const string FieldTerminator = "\t";
        private const string LineTerminator = "\n";

        public override object GetSqlGeography(string wkt, int srid)
        {
            throw new NotImplementedException();
        }

        public override object GetSqlGeometry(string wkt, int srid)
        {
            throw new NotImplementedException();
        }

        protected override MySqlConnection CreateConnection()
        {
            return new MySqlConnection(ConnectionString);
        }

        protected override string ConnectionString
        {
            get { return DbConnection.ConnectionString; }
        }

        public override void Run<T>(IEnumerable<T> entities, MySqlTransaction transaction)
        {
            var keepIdentity = (SqlBulkCopyOptions.KeepIdentity & Options.SqlBulkCopyOptions) > 0;

            using (var reader = new MappedDataReader<T>(entities, this))
            {
                var csvPath = "path/where/you/want/to/store/temp/CSVs";

                Dictionary<int, IPropertyMap> propertyMaps = reader.Cols
                    .Where(x => !x.Value.IsIdentity || keepIdentity)
                    .ToDictionary(x => x.Key, x => x.Value);

                var csv = new StringBuilder();

                while (reader.Read())
                {
                    foreach (var kvp in propertyMaps)
                    {
                        var value = reader.GetValue(kvp.Key);
                        if (value == null)
                        {
                            csv
                                .Append("null")
                                .Append(FieldTerminator);
                        }
                        else
                        {
                            // todo - escape "'"
                            csv
                                .AppendFormat("'{0}'", value)
                                .Append(FieldTerminator);
                        }

                    }
                    csv.Append(LineTerminator);
                }

                // todo - save csv

                // upload csv
                var mySqlBulkLoader = new MySqlBulkLoader(transaction.Connection);
                mySqlBulkLoader.TableName = string.Format("[{0}].[{1}]", reader.SchemaName, reader.TableName);
                mySqlBulkLoader.FieldTerminator = FieldTerminator;
                mySqlBulkLoader.LineTerminator = LineTerminator;
                mySqlBulkLoader.FileName = csvPath;

                foreach (var kvp in propertyMaps)
                {
                    mySqlBulkLoader.Columns.Add(kvp.Value.ColumnName);
                }

                int count = mySqlBulkLoader.Load();
            }
        }
    }
}
maybe some day i will implement it properly
Jun 25, 2014 at 1:41 PM
Thank you.