nopCommerce如何支持MySQL
此方法支持nopCommerce2.4以上版本(缺少的代码，可参照nopCommerce2.6源码)
nopCommerce 4.1 如何支持Mysql 请看 Url: http://www.nopcn.com/nopcommerce-blog-95.html
在工程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; } } } }
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); } } }
修改至
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 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; }
然后，在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的用户交互界面即可。
参考地址：http://www.nopcn.com/nopcommerce-detail-attr-course-2.html
