nopcommerce mysql_nopCommerce如何支持MySQL

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

在工程Easy.Data中：

1、添加MySqlConnectionFactory和MySqlDataProvider

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

MySqlConnectionFactory：

usingMySql.Data.MySqlClient;usingSystem;usingSystem.Collections.Generic;usingSystem.Data.Common;usingSystem.Data.Entity.Infrastructure;usingSystem.Linq;usingSystem.Text;namespaceEasy.Data

{public classMySqlConnectionFactory : IDbConnectionFactory

{private readonly string_baseConnectionString;private Func_providerFactoryCreator;publicMySqlConnectionFactory()

{

}public MySqlConnectionFactory(stringbaseConnectionString)

{this._baseConnectionString =baseConnectionString;

}public DbConnection CreateConnection(stringnameOrConnectionString)

{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= newMySqlConnection(connectionString);

}returnconnection;

}public stringBaseConnectionString

{get{return this._baseConnectionString;

}

}internal FuncProviderFactory

{get{

Func func1 = this._providerFactoryCreator;return delegate(stringname)

{returnDbProviderFactories.GetFactory(name);

};

}set{this._providerFactoryCreator =value;

}

}

}

}

View Code

MySqlDataProvider：

usingEasy.Data.Initializers;usingMySql.Data.MySqlClient;usingSystem;usingSystem.Collections.Generic;usingSystem.Data.Common;usingSystem.Data.Entity;usingSystem.Data.Entity.Infrastructure;usingSystem.IO;usingSystem.Linq;usingSystem.Text;usingSystem.Web.Hosting;namespaceEasy.Data

{public classMySqlDataProvider : BaseEfDataProvider

{///

///Get connection factory///

/// Connection factory

public overrideIDbConnectionFactory GetConnectionFactory()

{return newMySqlConnectionFactory();

}///

///Set database initializer///

public override voidSetDatabaseInitializer()

{//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();//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(tablesToValidate, customCommands.ToArray());

Database.SetInitializer(initializer);

}protected virtual string[] ParseCommands(string filePath, boolthrowExceptionIfNonExists)

{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();using (var stream =File.OpenRead(filePath))using (var reader = newStreamReader(stream))

{var statement = “”;while ((statement = readNextStatementFromStream(reader)) != null)

{

statements.Add(statement);

}

}returnstatements.ToArray();

}protected virtual stringreadNextStatementFromStream(StreamReader reader)

{var sb = newStringBuilder();stringlineOfText;while (true)

{

lineOfText=reader.ReadLine();if (lineOfText == null)

{if (sb.Length > 0)returnsb.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);

}returnsb.ToString();

}///

///A value indicating whether this data provider supports stored procedures///

public override boolStoredProceduredSupported

{get { return true; }

}///

///Gets a support database parameter object (used by stored procedures)///

/// Parameter

public overrideDbParameter GetParameter()

{return newMySqlParameter();

}

}

}

View Code

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(context.Database.SqlQuery(“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(“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(context.Database.SqlQuery(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(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);

}

}

}

View Code

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 properties

public 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; }

View Code

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

private bool mySqlDatabaseExists(stringconnectionString)

{try{//just try to connect

using (var conn = newMySqlConnection(connectionString))

{

conn.Open();

}return true;

}catch{return false;

}

}private string createMySqlDatabase(stringconnectionString)

{try{//parse database name

var builder = newMySqlConnectionStringBuilder(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 = newMySqlConnection(masterCatalogConnectionString))

{

conn.Open();using (var command = newMySqlCommand(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 = newMySqlConnectionStringBuilder();

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;returnbuilder.ConnectionString;

}

View Code

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