此方法支持nopCommerce2.4以上版本(缺少的代码,可参照nopCommerce2.6源码)

在工程Easy.Data中:

1、添加MySqlConnectionFactory和MySqlDataProvider

在Easy.Data目录下添加两个Class,MySqlConnectionFactory和MySqlDataProvider,

MySqlConnectionFactory:

using MySql.Data.MySqlClient;using System;using System.Collections.Generic;using System.Data.Common;using System.Data.Entity.Infrastructure;using System.Linq;using System.Text;namespace Easy.Data
{    public class MySqlConnectionFactory : IDbConnectionFactory
    {        private readonly string _baseConnectionString;        private Func<string, DbProviderFactory> _providerFactoryCreator;        public MySqlConnectionFactory()
        {
        }        public MySqlConnectionFactory(string baseConnectionString)
        {            this._baseConnectionString = baseConnectionString;
        }        public DbConnection CreateConnection(string nameOrConnectionString)
        {            string connectionString = nameOrConnectionString;            bool treatAsConnectionString = nameOrConnectionString.IndexOf('=') >= 0;            if (!treatAsConnectionString)
            {
                MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder(this.BaseConnectionString);
                builder.Server = nameOrConnectionString;
                connectionString = builder.ConnectionString;
            }
            DbConnection connection = null;            try
            {
                connection = this.ProviderFactory("MySql.Data.MySqlClient").CreateConnection();
                connection.ConnectionString = connectionString;
            }            catch
            {
                connection = new MySqlConnection(connectionString);
            }            return connection;
        }        public string BaseConnectionString
        {            get
            {                return this._baseConnectionString;
            }
        }        internal Func<string, DbProviderFactory> ProviderFactory
        {            get
            {
                Func<string, DbProviderFactory> func1 = this._providerFactoryCreator;                return delegate(string name)
                {                    return DbProviderFactories.GetFactory(name);
                };
            }            set
            {                this._providerFactoryCreator = value;
            }
        }
    }
}

View Code

MySqlDataProvider:

using Easy.Data.Initializers;using MySql.Data.MySqlClient;using System;using System.Collections.Generic;using System.Data.Common;using System.Data.Entity;using System.Data.Entity.Infrastructure;using System.IO;using System.Linq;using System.Text;using System.Web.Hosting;namespace Easy.Data
{    public class MySqlDataProvider : BaseEfDataProvider
    {        /// <summary>
        /// Get connection factory        /// </summary>
        /// <returns>Connection factory</returns>
        public override IDbConnectionFactory GetConnectionFactory()
        {            return new MySqlConnectionFactory();
        }        /// <summary>
        /// Set database initializer        /// </summary>
        public override void SetDatabaseInitializer()
        {            //pass some table names to ensure that we have nopCommerce 2.X installed
            var tablesToValidate = new[] { "Customer", "Discount", "Order", "Product", "ShoppingCartItem" };            //custom commands (stored proedures, indexes)

            var customCommands = new List<string>();            //use webHelper.MapPath instead of HostingEnvironment.MapPath which is not available in unit tests
            customCommands.AddRange(ParseCommands(HostingEnvironment.MapPath("~/App_Data/MySql.Indexes.sql"), false));            //use webHelper.MapPath instead of HostingEnvironment.MapPath which is not available in unit tests
            customCommands.AddRange(ParseCommands(HostingEnvironment.MapPath("~/App_Data/MySql.StoredProcedures.sql"), false));            var initializer = new CreateTablesIfNotExist<NopObjectContext>(tablesToValidate, customCommands.ToArray());
            Database.SetInitializer(initializer);
        }        protected virtual string[] ParseCommands(string filePath, bool throwExceptionIfNonExists)
        {            if (!File.Exists(filePath))
            {                if (throwExceptionIfNonExists)                    throw new ArgumentException(string.Format("Specified file doesn't exist - {0}", filePath));                else
                    return new string[0];
            }            var statements = new List<string>();            using (var stream = File.OpenRead(filePath))            using (var reader = new StreamReader(stream))
            {                var statement = "";                while ((statement = readNextStatementFromStream(reader)) != null)
                {
                    statements.Add(statement);
                }
            }            return statements.ToArray();
        }        protected virtual string readNextStatementFromStream(StreamReader reader)
        {            var sb = new StringBuilder();            string lineOfText;            while (true)
            {
                lineOfText = reader.ReadLine();                if (lineOfText == null)
                {                    if (sb.Length > 0)                        return sb.ToString();                    else
                        return null;
                }                //MySql doesn't support GO, so just use a commented out GO as the separator
                if (lineOfText.TrimEnd().ToUpper() == "-- GO")                    break;

                sb.Append(lineOfText + Environment.NewLine);
            }            return sb.ToString();
        }        /// <summary>
        /// A value indicating whether this data provider supports stored procedures        /// </summary>
        public override bool StoredProceduredSupported
        {            get { return true; }
        }        /// <summary>
        /// Gets a support database parameter object (used by stored procedures)        /// </summary>
        /// <returns>Parameter</returns>
        public override DbParameter GetParameter()
        {            return new MySqlParameter();
        }
    }
}


2、在EfDataProviderManager.LoadDataProvider中添加一条case语句:

case "mysql":        return new MySqlDataProvider();

3、在Easy.Data.Initializers.CreateTablesIfNotExist中,对InitializeDatabase函数进行修改

将以下代码

if (dbExists)
{    bool createTables = false;    if (_tablesToValidate != null && _tablesToValidate.Length > 0)
    {        //we have some table names to validate
        var existingTableNames = new List<string>(context.Database.SqlQuery<string>("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE'"));
        createTables = existingTableNames.Intersect(_tablesToValidate, StringComparer.InvariantCultureIgnoreCase).Count() == 0;
    }    else
    {        //check whether tables are already created
        int numberOfTables = 0;        foreach (var t1 in context.Database.SqlQuery<int>("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' "))
            numberOfTables = t1;

        createTables = numberOfTables == 0;
    }    if (createTables)
    {        //create all tables
        var dbCreationScript = ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();
        context.Database.ExecuteSqlCommand(dbCreationScript);        //Seed(context);        context.SaveChanges();        if (_customCommands != null && _customCommands.Length > 0)
        {            foreach (var command in _customCommands)
                context.Database.ExecuteSqlCommand(command);
        }
    }
}

View Code

修改至

if (dbExists)
{    string sql = string.Empty;    string countSql = string.Empty;    if (context.Database.Connection.GetType() == typeof(MySqlConnection))
    {
        sql = string.Format("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND table_schema = '{0}'", context.Database.Connection.Database);
        countSql = string.Format("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND table_schema = '{0}'", context.Database.Connection.Database);
    }    else
    {
        sql = @"SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE'";
        countSql = @"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' ";
    }    bool createTables = false;    if (_tablesToValidate != null && _tablesToValidate.Length > 0)
    {        //we have some table names to validate
        var existingTableNames = new List<string>(context.Database.SqlQuery<string>(sql));
        createTables = existingTableNames.Intersect(_tablesToValidate, StringComparer.InvariantCultureIgnoreCase).Count() == 0;
    }    else
    {        //check whether tables are already created
        int numberOfTables = 0;        foreach (var t1 in context.Database.SqlQuery<int>(countSql))
            numberOfTables = t1;

        createTables = numberOfTables == 0;
    }    if (createTables)
    {        //create all tables
        var dbCreationScript = ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();        //Need to fix some of the script for MySql
        if (context.Database.Connection.GetType() == typeof(MySqlConnection))
        {            //MySql doesn't support varbinary(MAX) so it generates the script with varbinary only without            //a size specified, so change to longblob...could probably do this in the mapping for these properties instead
            dbCreationScript = dbCreationScript.Replace("`PictureBinary` varbinary,", "`PictureBinary` LONGBLOB,");
            dbCreationScript = dbCreationScript.Replace("`DownloadBinary` varbinary,", "`DownloadBinary` LONGBLOB,");            //Order is a keyword so need to put in quotes
            dbCreationScript = dbCreationScript.Replace("REFERENCES Order (Id)", "REFERENCES `Order` (Id)");            //Some of the constraint names are too long for MySql, so shorten them            //dbCreationScript = dbCreationScript.Replace("ProductReview_TypeConstraint_From_CustomerContent_To_ProductReview", "ProductReview_CustomerContent_ProductReview");            //dbCreationScript = dbCreationScript.Replace("PollVotingRecord_TypeConstraint_From_CustomerContent_To_PollVotingRecord", "PollVotingRecord_CustomerContent_PollVotingRecord");            //dbCreationScript = dbCreationScript.Replace("ProductReviewHelpfulness_TypeConstraint_From_CustomerContent_To_ProductReviewHelpfulness", "ProductReviewHelpfulnes_CustomerContent_ProductReviewHelpfulnes");        }

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


4、在领域Model中,一些属性Mapping需要更改,因为MySQL将字符串创建成Text/MediumText/LongText,而这些格式不支持索引,所以需要将这些Mapping修改成varchar,如将

this.Property(u => u.Username).HasMaxLength(1000);this.Property(u => u.Email).HasMaxLength(1000);

修改成

this.Property(u => u.Username).HasMaxLength(1000).HasColumnType("varchar");this.Property(u => u.Email).HasMaxLength(1000).HasColumnType("varchar");

5、最后,在Easy.Web.Models.Install.InstallModel中添加MySQL相关属性


//MySql propertiespublic string MySqlConnectionInfo { get; set; }
[AllowHtml]public string MySqlServerName { get; set; }
[AllowHtml]public string MySqlDatabaseName { get; set; }
[AllowHtml]public string MySqlUsername { get; set; }
[AllowHtml]public string MySqlPassword { get; set; }public bool MySqlServerCreateDatabase { get; set; }
[AllowHtml]public string MySqlDatabaseConnectionString { get; set; }


然后,在Easy.Web.Controllers.InstallController中添加MySQL相关的函数


private bool mySqlDatabaseExists(string connectionString)
{    try
    {        //just try to connect
        using (var conn = new MySqlConnection(connectionString))
        {
            conn.Open();
        }        return true;
    }    catch
    {        return false;
    }
}private string createMySqlDatabase(string connectionString)
{    try
    {        //parse database name
        var builder = new MySqlConnectionStringBuilder(connectionString);        var databaseName = builder.Database;        //now create connection string to 'master' dabatase. It always exists.
        builder.Database = string.Empty; // = "master";
        var masterCatalogConnectionString = builder.ToString();        string query = string.Format("CREATE DATABASE {0} COLLATE utf8_unicode_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 createMySqlConnectionString(string serverName, string databaseName, string userName, string password, UInt32 timeout = 0)
{    var builder = new MySqlConnectionStringBuilder();
    builder.Server = serverName;
    builder.Database = databaseName.ToLower();
    builder.UserID = userName;
    builder.Password = password;
    builder.PersistSecurityInfo = false;
    builder.AllowUserVariables = true;
    builder.DefaultCommandTimeout = 30000;

    builder.ConnectionTimeout = timeout;    return builder.ConnectionString;
}


最后,在Easy.Web.Views.Install.Index.cshtml中,添加MySQL的用户交互界面即可。

下载文件: Nop_Mysql (2).zip