from mssql to mysql

I have considdered to move my shop from one ISP to another, to much downtime. That is really annoying.

Is there an easy way to convert the database from mssql to mysql. ??

Best sharpening ever

I don't think you can use a mysql database with nopCommerce.

I think you can, but it'll require some source code changes (minor)
http://stackoverflow.com/questions/3283820/using-mysql-with-entity-framework-4-and-the-code-first-development-ctp

I think you'll also have to tweak the stored proc (search products) and the update scripts.

Its more problematic.
I'm trying to do it for 2 days already (but i'm not experienced with entity framework).
I'v started from installation.
Here is a summary of what I'v found already:

public class NopObjectContext :  DbContext, IDbContext is used by entity framework.

It is connected to database here:

var initializer = new CreateCTablesIfNotExist<NopObjectContext>(tablesToValidate, customCommands.ToArray());
Database.SetInitializer<NopObjectContext>(initializer);

CreateCTablesIfNotExist is used to create tables in database.

InstallController.cs is used to create database and insert initial data in it.

First to debug nop.web I'v opened its project and added projects nop.core, nop.data, nop.services, nop.web.framework (before this you need to run prepare.bat once to link some modules).

Then I’v added provider and connection string into web.config as follows:

<connectionStrings>
    <add name="NopObjectContext"
         connectionString="server=localhost;User Id=root;password=mypassword;database=shop;Persist Security Info=true;"
         providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>

<DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
      <add
        name="MySQL Data Provider"
        invariant="MySql.Data.MySqlClient"
        description=".Net Framework Data Provider for MySQL"
        type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.4.4.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D" />
    </DbProviderFactories>

For this to work connection string should be named as NopObjectContext class (this I’v found in google, but maybe better approach exist).

Then you need to open NopObjectContext.cs and change this:

public NopObjectContext()
            : base()

(remove “string” from brackets). If you will not do this then ms sql provider will be used instead of mysql.

Also you need to change to this string in DependancyRegistrar.cs:
builder.Register<IDbContext>(c => new NopObjectContext()).InstancePerHttpRequest();
(you remove dataProviderSettings.DataConnectionString and dataSettingsManager.LoadSettings().DataConnectionString)
This is also needed for mysql connection string from web.config to be used.

Then also I’v changed the view install.cshtml and added ability to select mysql server there so that if mysql selected, then my dataprovider is named “mysqlserver”.

Then I’v added some code in installcontroller.cs for database creation:

using MySql.Data.MySqlClient; //remember to add references to mysql.data, mysql.web, mysql.data.entity

  Added database exist check for mysql:

       private bool sqlServerDatabaseExists(string dataprovider, string connectionString)
        {
            try
            {
                //just try to connect
                if (dataprovider == "mysqlserver")
                {
                    using (var conn = new MySqlConnection(connectionString))
                    {
                        conn.Open();
                    }
                }
                else
                {
                    using (var conn = new SqlConnection(connectionString))
                    {
                        conn.Open();
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

  Also as you can see “string dataprovider” is added to function to see what provider is used.

private string createDatabase(string dataprovider, string connectionString)
        {
            try
            {
                if (dataprovider != "mysqlserver")
                {

                    //parse database name
                    var builder = new SqlConnectionStringBuilder(connectionString);
                    var databaseName = builder.InitialCatalog;
                    //now create connection string to 'master' dabatase. It always exists.
                    builder.InitialCatalog = "master";
                    var masterCatalogConnectionString = builder.ToString();
                    string query = string.Format("CREATE DATABASE [{0}] COLLATE SQL_Latin1_General_CP1_CI_AS", databaseName);

                    using (var conn = new SqlConnection(masterCatalogConnectionString))
                    {
                        conn.Open();
                        using (var command = new SqlCommand(query, conn))
                        {
                            command.ExecuteNonQuery();
                        }
                    }

                    return string.Empty;
                }
                else
                {
                    //parse database name
                    var builder = new MySqlConnectionStringBuilder(connectionString);
                    var databaseName = builder.Database;
                    //now create connection string to root dabatase. It always exists.
                    builder.Database = "";
                    var masterCatalogConnectionString = builder.ToString();
                    string query = string.Format("CREATE DATABASE {0} COLLATE latin1_general_ci", databaseName);

                    using (var conn = new MySqlConnection(masterCatalogConnectionString))
                    {
                        conn.Open();
                        using (var command = new MySqlCommand(query, conn))
                        {
                            command.ExecuteNonQuery();
                        }
                    }

                    return string.Empty;


                }
            }
            catch (Exception ex)
            {
                return string.Format("An error occured when creating database: {0}", ex.Message);
            }
        }

        private string MySQLcreateConnectionString(string serverName, string databaseName, string userName, string password, uint timeout = 0)
        {
            var builder = new MySqlConnectionStringBuilder();
            builder.Server = serverName;
            builder.Database = databaseName;
            builder.UserID = userName;
            builder.Password = password;
            builder.PersistSecurityInfo = true;
            if (timeout > 0) builder.ConnectionTimeout = timeout;
            return builder.ConnectionString;
        }

Then I’v also added specific code for mysqlserver like for sqlserver:

if (model.DataProvider.Equals("mysqlserver", StringComparison.InvariantCultureIgnoreCase))
                    {
                      
                        //SQL Server

                        if (model.SqlConnectionInfo.Equals("sqlconnectioninfo_raw", StringComparison.InvariantCultureIgnoreCase))
                        {
                            //raw connection string
                            connectionString = model.DatabaseConnectionString;
                        }
                        else
                        {
                            //values
                            connectionString = MySQLcreateConnectionString(model.SqlServerName, model.SqlDatabaseName,model.SqlServerUsername, model.SqlServerPassword);
                        }
                        
                        if (model.SqlServerCreateDatabase)
                        {
                            if (!sqlServerDatabaseExists(model.DataProvider,connectionString))
                            {
                                //create database
                                var errorCreatingDatabase = createDatabase(model.DataProvider, connectionString);
                                if (!String.IsNullOrEmpty(errorCreatingDatabase))
                                    throw new Exception(errorCreatingDatabase);
                                else
                                {
                                    //Database cannot be created sometimes. Weird! Seems to be Entity Framework issue
                                    //that's just wait 3 seconds
                                    Thread.Sleep(3000);
                                }
                            }
                        }
                        else
                        {
                            //check whether database exists
                            if (!sqlServerDatabaseExists(model.DataProvider,connectionString))
                                throw new Exception("Database does not exist or you don't have permissions to connect to it");
                        }

So now it connects to server and creates a database.
But then more fun begins.

Once execution will go to efrepository.cs and it tries to insert new entity into database
                this.Entities.Add(entity);
                this._context.SaveChanges();

Then tables creation will occur in CreateCTablesIfNotExist.
And here I’v stuck for now.

var dbCreationScript = ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();

This one creates a script for tables creation and then it is executed by context.Database.ExecuteSqlCommand(dbCreationScript);

But by unknown reason the created script is incorrect.
For example some entity classes contain variables of type byte[].
They are converted to “varbinary” type in script. But with such type script throws exception of incorrect script. This can be fixed by specifying varbinary size like “varbinary(0)”.
But also there is one more problem.
For example we have:

public partial class ShippingMethod : BaseEntity, ILocalizedEntity
    {
        private ICollection<Country> _restrictedCountries;

        /// <summary>
        /// Gets or sets the name
        /// </summary>
        public virtual string Name { get; set; }

        /// <summary>
        /// Gets or sets the description
        /// </summary>
        public virtual string Description { get; set; }

        /// <summary>
        /// Gets or sets the display order
        /// </summary>
        public virtual int DisplayOrder { get; set; }

        /// <summary>
        /// Gets or sets the restricted countries
        /// </summary>
        public virtual ICollection<Country> RestrictedCountries
        {
            get { return _restrictedCountries ?? (_restrictedCountries = new List<Country>()); }
            protected set { _restrictedCountries = value; }
        }
    }

It is used to created shippingmethod table in database and this table will be created fine.
But script also contains ALTER TABLE command pointing to ShippingMethodCountry table that will never be exist (and should not). This will generate not exist table exception.
I don’t know why it is so – looks like a bug, so I’v submitted it to mysql team:
http://bugs.mysql.com/bug.php?id=64216

Even if to skip this incorrect alter table commands we have this later:

                  if (_customCommands != null && _customCommands.Length > 0)
                    {
                        foreach (var command in _customCommands)
                            context.Database.ExecuteSqlCommand(command);
                    }

This are used to execute commands that are in SqlServer.Indexes.sql & SqlServer.StoredProcedures.sql. These should be also translated to work for mysql.
That’s all for now.