ASP.NET MVC, Entity Framework Code First與MySql筆記

ASP.NET MVC, Entity Framework Code First與MySql的筆記

在一次機會中,需要在ASP.NET MVC去接MySql資料庫,試玩一下之後,發現在ASP.NET MVC與Entity Framework環境下去對接MySql資料庫並不會太難,以下為簡單的筆記。

連線MySql三個重要元件

其實走對第一步,對接MySql資料庫已經完成了80%說。 :)

首先你要安裝三個重要的元件:

add MySql.Data MySql.Data.Entity MySql.Web
  • MySql.Data
  • MySql.Data.Entity
  • MySql.Web

你可以選擇從 MySql 下載是安裝 Connector/Net 或從 NuGet 下載安裝,不過強烈建議使用 NuGet 來安裝,因為透過 NuGet 安裝 MySql 元件時會順便幫你把 web.config 組態給設定好。記得自己在Azure上取得的MySql connectionString設定進去。

 <?xml version="1.0" encoding="utf-8"?>
 <!--
   For more information on how to configure your ASP.NET application, please visit
   http://go.microsoft.com/fwlink/?LinkId=301880
   -->
 <configuration>
   <configSections>
     <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
     <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
   </configSections>
   <appSettings>
     <add key="webpages:Version" value="3.0.0.0" />
     <add key="webpages:Enabled" value="false" />
     <add key="ClientValidationEnabled" value="true" />
     <add key="UnobtrusiveJavaScriptEnabled" value="true" />
   </appSettings>
   <system.web>
     <compilation debug="true" targetFramework="4.6.1" />
     <httpRuntime targetFramework="4.6.1" />
     <membership defaultProvider="MySQLMembershipProvider">
       <providers>
         <remove name="MySQLMembershipProvider" />
         <add name="MySQLMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
       </providers>
     </membership>
     <profile defaultProvider="MySQLProfileProvider">
       <providers>
         <remove name="MySQLProfileProvider" />
         <add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
       </providers>
     </profile>
     <roleManager defaultProvider="MySQLRoleProvider">
       <providers>
         <remove name="MySQLRoleProvider" />
         <add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
       </providers>
     </roleManager>
     <siteMap defaultProvider="MySqlSiteMapProvider">
       <providers>
         <remove name="MySqlSiteMapProvider" />
         <add name="MySqlSiteMapProvider" type="MySql.Web.SiteMap.MySqlSiteMapProvider, MySql.Web, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
       </providers>
     </siteMap>
     <webParts>
       <personalization defaultProvider="MySQLPersonalizationProvider">
         <providers>
           <remove name="MySQLPersonalizationProvider" />
           <add name="MySQLPersonalizationProvider" type="MySql.Web.Personalization.MySqlPersonalizationProvider, MySql.Web, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
         </providers>
       </personalization>
     </webParts>
   </system.web>
   <runtime>
     <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
       <dependentAssembly>
         <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
         <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
       </dependentAssembly>
       <dependentAssembly>
         <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
         <bindingRedirect oldVersion="0.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
       </dependentAssembly>
       <dependentAssembly>
         <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
         <bindingRedirect oldVersion="1.0.0.0-5.2.3.0" newVersion="5.2.3.0" />
       </dependentAssembly>
       <dependentAssembly>
         <assemblyIdentity name="MySql.Data" publicKeyToken="c5687fc88969c44d" culture="neutral" />
         <bindingRedirect oldVersion="0.0.0.0-6.9.8.0" newVersion="6.9.8.0" />
       </dependentAssembly>
     </assemblyBinding>
   </runtime>
   <system.data>
     <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.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
     </DbProviderFactories>
   </system.data>
   <entityFramework>
     <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
       <parameters>
         <parameter value="v12.0" />
       </parameters>
     </defaultConnectionFactory>
     <providers>
       <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
       <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
     </providers>
   </entityFramework>
   <connectionStrings>
     <add name="MySqlModel" providerName="MySql.Data.MySqlClient" connectionString="Your MySql ConnString" />
     <add name="MsSqlModel" connectionString="Your MsSql ConnString" providerName="System.Data.SqlClient" />
   </connectionStrings>
   <system.codedom>
     <compilers>
       <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:6 /nowarn:1659;1699;1701" />
       <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:14 /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+" />
     </compilers>
   </system.codedom>
 </configuration>  
 

除了connectionStrings之外,可以發現透過 NuGet 可以省去許多 web.config 設置的動作。

新增MySqlContext

這邊我們模仿Entity Framework Code First的寫法,新增一個MySqlContext 並繼承 DbContext,依樣畫葫蘆:

    public class MySqlContext : DbContext
    {
        public MySqlContext() : base("MySqlModel")
        { }

        public DbSet<total> total { get; set; }
    }  
 

到這裡已完成所有連線MySql所需要的動作。簡單吧。 :)

呼叫 MySqlContext 存取 MySql

 public ActionResult Home()
 {
     MySqlContext db = new MySqlContext();
     var total = db.total;
     return Json(total, JsonRequestBehavior.AllowGet);
 } 
 

如同使用Entity Framework一樣,可以直接透過 LINQ 來存取 MySql 即可。

小結

其實我嘗試的時間比寫這篇筆記的時間還多,尤其是網路一些文章都會叫你去下載 Connector/Net 元件,然後就…試不出來 -_-|||

直到透過 Nuget 正確安裝好 MySql 三個元件後,後面使用就和 Entity Framework / MS SQL 存取沒有什麼差異了。

沒有留言:

張貼留言

感謝您的留言,如果我的文章你喜歡或對你有幫助,按個「讚」或「分享」它,我會很高興的。