ASP.NET Identity：使用 MySQL 儲存體搭配 EntityFramework MySQL 提供者(C#)ASP.NET Identity: Using MySQL Storage with an EntityFramework MySQL Provider (C#)
12/10/2013
本文內容
本教學課程說明如何使用 EntityFramework (SQL 用戶端提供者)搭配 MySQL 提供者來取代ASP.NET Identity的預設資料儲存機制。This tutorial shows you how to replace the default data storage mechanism for ASP.NET Identity with EntityFramework (SQL client provider) with a MySQL provider.
本教學課程將涵蓋下列主題：The following topics will be covered in this tutorial:
在 Azure 上建立 MySQL 資料庫Creating a MySQL database on Azure
使用 Visual Studio 2013 MVC 範本建立 MVC 應用程式Creating an MVC application using Visual Studio 2013 MVC template
設定 EntityFramework 以使用 MySQL 資料庫提供者Configuring EntityFramework to work with a MySQL database provider
執行應用程式以驗證結果Running the application to verify the results
在本教學課程結束時，您將會有一個 MVC 應用程式，其中包含的 ASP.NET Identity 存放區會使用裝載于 Azure 中的 MySQL 資料庫。At the end of this tutorial, you will have an MVC application with the ASP.NET Identity store that is using a MySQL database that is hosted in Azure.
在 Azure 上建立 MySQL 資料庫實例Creating a MySQL database instance on Azure
按一下頁面底部的 [新增]，然後選取 [儲存]：Click NEW at the bottom of the page, and then select STORE:
在 [選擇和附加元件] 中，選取 [ ClearDB MySQL 資料庫]，然後按一下框架底部的 [下一步] 箭號：In the Choose and Add-on wizard, select ClearDB MySQL Database, and then click the Next arrow at the bottom of the frame:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
保留預設的 [免費方案]，將名稱變更為IdentityMySQLDatabase，選取最接近您的區域，然後按一下框架底部的下一個箭號：Keep the default Free plan, change the NAME to IdentityMySQLDatabase, select the region that is nearest to you, and then click the Next arrow at the bottom of the frame:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
按一下 [購買] 核取記號，以完成資料庫建立。Click the PURCHASE checkmark to complete the database creation.
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
建立資料庫之後，您可以從管理入口網站的 [附加元件] 索引標籤加以管理。After your database has been created, you can manage it from the ADD-ONS tab in the management portal. 若要取得資料庫的連線資訊，請按一下頁面底部的 [連接資訊]：To retrieve the connection information for your database, click CONNECTION INFO at the bottom of the page:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
按一下 [ CONNECTIONSTRING ] 欄位的 [複製] 按鈕並加以儲存，以複製連接字串。您稍後會在本教學課程中針對 MVC 應用程式使用此資訊：Copy the connection string by clicking on the copy button by the CONNECTIONSTRING field and save it; you will use this information later in this tutorial for your MVC application:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
建立 MVC 應用程式專案Creating an MVC application project
To complete the steps in this section of the tutorial, you will first need to install Visual Studio Express 2013 for Web or Visual Studio 2013. 安裝 Visual Studio 之後，請使用下列步驟來建立新的 MVC 應用程式專案：Once Visual Studio has been installed, use the following steps to create a new MVC application project:
開啟 Visual Studio 2103。Open Visual Studio 2103.
按一下 [開始] 頁面中的 [新增專案]，或按一下 [檔案 ] 功能表， 然後按一下 [新增專案]：Click New Project from the Start page, or you can click the File menu and then New Project:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
當 [新增專案] 對話方塊顯示時，在範本清單中展開 [視覺C#效果]，然後按一下 [ web]，再選取 [ ASP.NET web 應用程式]。When the New Project dialog box is displayed, expand Visual C# in the list of templates, then click Web, and select ASP.NET Web Application. 將專案命名為IdentityMySQLDemo ，然後按一下 [確定] ：Name your project IdentityMySQLDemo and then click OK:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
在 [新增 ASP.NET 專案] 對話方塊中，選取 [ MVC templatewith] 預設選項;這會將個別使用者帳戶設定為驗證方法。In the New ASP.NET Project dialog, select the MVC templatewith the default options; this will configure Individual User Accounts as the authentication method. 按一下 [確定]：Click OK:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
設定 EntityFramework 以使用 MySQL 資料庫Configure EntityFramework to work with a MySQL database
更新專案的 Entity Framework 元件Update the Entity Framework assembly for your project
從 Visual Studio 2013 範本建立的 MVC 應用程式包含EntityFramework 6.0.0套件的參考，但自其發行以來已經更新該元件，其中包含顯著的效能改進。The MVC application that was created from the Visual Studio 2013 template contains a reference to the EntityFramework 6.0.0 package, but there have been updates to that assembly since its release which contain significant performance improvements. 若要在您的應用程式中使用這些最新的更新，請使用下列步驟。In order to use these latest updates in your application, use the following steps.
在 Visual Studio 中開啟您的 MVC 專案。Open your MVC project in Visual Studio.
按一下 [工具]，然後按一下 [ NuGet 套件管理員]，再按一下 [套件管理員主控台]：Click Tools, then click NuGet Package Manager, and then click Package Manager Console:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
[套件管理員主控台] 將會出現在 Visual Studio 的底部區段中。The Package Manager Console will appear in the bottom section of Visual Studio. 輸入 "更新-封裝 EntityFramework"，然後按 enter：Type "Update-Package EntityFramework" and press Enter:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
安裝適用于 EntityFramework 的 MySQL 提供者Install the MySQL provider for EntityFramework
為了讓 EntityFramework 連線到 MySQL 資料庫，您需要安裝 MySQL 提供者。In order for EntityFramework to connect to MySQL database, you need to install a MySQL provider. 若要這麼做，請開啟 [套件管理員主控台]，然後輸入 "Install-Package MySql. Data. 實體-Pre"，然後按 enter 鍵。To do so, open the Package Manager Console and type "Install-Package MySql.Data.Entity -Pre", and then press Enter.
Note
這是元件的發行前版本，因此可能會包含 bug。This is a pre-release version of the assembly, and as such it may contain bugs. 您不應該在生產環境中使用提供者的發行前版本。You should not use a pre-release version of the provider in production.
[按一下下列影像將它展開。][Click the following image to expand it.]
對應用程式的 web.config 檔案進行專案設定變更Making project configuration changes to the Web.config file for your application
在本節中，您會將 Entity Framework 設定為使用您剛安裝的 MySQL 提供者、註冊 MySQL 提供者 factory，以及從 Azure 新增您的連接字串。In this section you will configure the Entity Framework to use the MySQL provider that you just installed, register the MySQL provider factory, and add your connection string from Azure.
Note
下列範例包含 MySql. Data .dll 的特定元件版本。The following examples contain a specific assembly version for MySql.Data.dll. 如果元件版本變更，您將需要以正確的版本修改適當的設定。If the assembly version changes, you will need to modify the appropriate configuration settings with the correct version.
在 Visual Studio 2013 中，開啟專案的 web.config 檔案。Open the Web.config file for your project in Visual Studio 2013.
找出下列設定，以定義 Entity Framework 的預設資料庫提供者和 factory：Locate the following configuration settings, which define the default database provider and factory for the Entity Framework:
type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
invariantName="System.Data.SqlClient"
type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
以下列內容取代這些設定，將 Entity Framework 設定為使用 MySQL 提供者：Replace those configuration settings with the following, which will configure the Entity Framework to use the MySQL provider:
type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity"/>
invariant="MySql.Data.MySqlClient"
description=".Net Framework Data Provider for MySQL"
type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.7.2.0"/>
找出 [] 區段，並以下列程式碼取代它，這會定義裝載于 Azure 上的 MySQL 資料庫的連接字串(請注意，providerName 值也已從原始變更)：Locate the section and replace it with the following code, which will define the connection string for your MySQL database that is hosted on Azure (note that providerName value has also been changed from the original):
providerName="MySql.Data.MySqlClient"
connectionString="[Insert your ConnectionString from Azure here]"/>
新增自訂 MigrationHistory 內容Adding custom MigrationHistory context
Entity Framework Code First 會使用MigrationHistory資料表來追蹤模型變更，並確保資料庫架構與概念架構之間的一致性。Entity Framework Code First uses a MigrationHistory table to keep track of model changes and to ensure the consistency between the database schema and conceptual schema. 不過，根據預設，此資料表不適用於 MySQL，因為主要金鑰太大。However, this table does not work for MySQL by default because the primary key is too large. 若要解決這種情況，您必須壓縮該資料表的金鑰大小。To remedy this situation, you will need to shrink the key size for that table. 若要這樣做，請使用下列步驟：To do so, use the following steps:
此資料表的架構資訊是在HistoryCoNtext中捕捉，可以修改為任何其他DbCoNtext。The schema information for this table is captured in a HistoryContext, which can be modified as any other DbContext. 若要這麼做，請將名為MySqlHistoryCoNtext.cs的新類別檔案新增至專案，並將其內容取代為下列程式碼：To do so, add a new class file named MySqlHistoryContext.cs to the project, and replace its contents with the following code:
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Migrations.History;
namespace IdentityMySQLDemo
{
public class MySqlHistoryContext : HistoryContext
{
public MySqlHistoryContext(
DbConnection existingConnection,
string defaultSchema)
: base(existingConnection, defaultSchema)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();
modelBuilder.Entity().Property(h => h.ContextKey).HasMaxLength(200).IsRequired();
}
}
}
接下來，您必須將 Entity Framework 設定為使用修改過的HistoryCoNtext，而不是預設值。Next you will need to configure Entity Framework to use the modified HistoryContext, rather than default one. 這可以藉由運用以程式碼為基礎的設定功能來完成。This can be done by leveraging code-based configuration features. 若要這麼做，請將名為MySqlConfiguration.cs的新類別檔案新增至您的專案，並將其內容取代為：To do so, add new class file named MySqlConfiguration.cs to your project and replace its contents with:
using System.Data.Entity;
namespace IdentityMySQLDemo
{
public class MySqlConfiguration : DbConfiguration
{
public MySqlConfiguration()
{
SetHistoryContext(
"MySql.Data.MySqlClient", (conn, schema) => new MySqlHistoryContext(conn, schema));
}
}
}
建立 [ApplicationdbcoNtext] 的自訂 EntityFramework 初始化運算式Creating a custom EntityFramework initializer for ApplicationDbContext
本教學課程中所述的 MySQL 提供者目前不支援 Entity Framework 遷移，因此您必須使用模型初始化運算式，才能連接到資料庫。The MySQL provider that is featured in this tutorial does not currently support Entity Framework migrations, so you will need to use model initializers in order to connect to the database. 由於本教學課程使用 Azure 上的 MySQL 實例，因此您必須建立自訂的 Entity Framework 初始化運算式。Because this tutorial is using a MySQL instance on Azure, you will need to create a custom Entity Framework initializer.
Note
如果您要連接到 Azure 上的 SQL Server 實例，或使用裝載于內部部署環境的資料庫，則不需要執行此步驟。This step is not required if you are connecting to a SQL Server instance on Azure or if you are using a database that is hosted on premises.
若要建立 MySQL 的自訂 Entity Framework 初始化運算式，請使用下列步驟：To create a custom Entity Framework initializer for MySQL, use the following steps:
將名為MySqlInitializer.cs的新類別檔案新增至專案，並以下列程式碼取代其內容：Add a new class file named MySqlInitializer.cs to the project, and replace it's contents with the following code:
using IdentityMySQLDemo.Models;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
namespace IdentityMySQLDemo
{
public class MySqlInitializer : IDatabaseInitializer
{
public void InitializeDatabase(ApplicationDbContext context)
{
if (!context.Database.Exists())
{
// if database did not exist before - create it
context.Database.Create();
}
else
{
// query to check if MigrationHistory table is present in the database
var migrationHistoryTableExists = ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery(
"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'IdentityMySQLDatabase' AND table_name = '__MigrationHistory'");
// if MigrationHistory table is not there (which is the case first time we run) - create it
if (migrationHistoryTableExists.FirstOrDefault() == 0)
{
context.Database.Delete();
context.Database.Create();
}
}
}
}
}
開啟專案的IdentityModels.cs檔案(位於 [模型] 目錄中)，並將其內容取代為下列內容：Open the IdentityModels.cs file for your project, which is located in the Models directory, and replace it's contents with the following:
using Microsoft.AspNet.Identity.EntityFramework;
using System.Data.Entity;
namespace IdentityMySQLDemo.Models
{
// You can add profile data for the user by adding more properties to your ApplicationUser
// class, please visit https://go.microsoft.com/fwlink/?LinkID=317594 to learn more.
public class ApplicationUser : IdentityUser
{
}
public class ApplicationDbContext : IdentityDbContext
{
static ApplicationDbContext()
{
Database.SetInitializer(new MySqlInitializer());
}
public ApplicationDbContext()
: base("DefaultConnection")
{
}
}
}
執行應用程式並驗證資料庫Running the application and verifying the database
完成上述各節中的步驟之後，您應該測試您的資料庫。Once you have completed the steps in the preceding sections, you should test your database. 若要這樣做，請使用下列步驟：To do so, use the following steps:
按Ctrl + F5以建立並執行 web 應用程式。Press Ctrl + F5 to build and run the web application.
按一下頁面頂端的 [註冊] 索引標籤：Click the Register tab on the top of the page:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
輸入新的 [使用者名稱] 和 [密碼]，然後按一下 [註冊]：Enter a new user name and password, and then click Register:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
此時，會在 MySQL 資料庫上建立 ASP.NET Identity 的資料表，並將使用者註冊並登入應用程式：At this point the ASP.NET Identity tables are created on the MySQL Database, and the user is registered and logged into the application:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
安裝 MySQL 工作臺工具來驗證資料Installing MySQL Workbench tool to verify the data
從mysql 下載頁面安裝mysql 工作臺工具Install the MySQL Workbench tool from the MySQL downloads page
在安裝精靈中： [特徵選取] 索引標籤上，選取 [應用程式區段下的MySQL 工作臺]。In the installation wizard: Feature Selection tab, select MySQL Workbench under applications section.
啟動應用程式，並使用您在本教學課程乞求所建立的 Azure MySQL 資料庫中的連接字串資料，來新增連接。Launch the app and add a new connection using the connection string data from the Azure MySQL database you created at the begging of this tutorial.
建立連接之後，請檢查 IdentityMySQLDatabase 上建立的ASP.NET Identity資料表 。After establishing the connection, inspect the ASP.NET Identity tables created on the IdentityMySQLDatabase.
您會看到所有 ASP.NET Identity 必要的資料表都已建立，如下圖所示：You will see that all ASP.NET Identity required tables are created as shown in the image below:
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
檢查aspnetusers資料表中的實例，以在您註冊新的使用者時檢查項目。Inspect the aspnetusers table for instance to check for the entries as you register new users.
[按一下下列影像將它展開。[Click the following image to expand it. ] 
]
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
