Entity Framework6 訪問MySQL

標籤：

先用PM命令安裝EF6

Install-Package EntityFramework

配置修改如下

<?xml version="1.0" encoding="utf-8"?><configuration>  <configSections>    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data" />    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />  </configSections>  <connectionStrings>    <add name="myDb" providerName="MySql.Data.MySqlClient" connectionString="server=192.168.0.2;uid=root;pwd=123456;database=mydb2;" />  </connectionStrings>  <entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />    <providers>      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />    </providers>  </entityFramework>  <startup>    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />  </startup></configuration>

資料庫實體及上下文定義

 [DbConfigurationType(typeof(MySqlEFConfiguration))]    public class Parking : DbContext    {        public DbSet<Car> Cars { get; set; }        public Parking()            : base("name=myDb")        {        }        // Constructor to use on a DbConnection that is already opened        public Parking(DbConnection existingConnection, bool contextOwnsConnection)            : base(existingConnection, contextOwnsConnection)        {        }        protected override void OnModelCreating(DbModelBuilder modelBuilder)        {            base.OnModelCreating(modelBuilder);            modelBuilder.Entity<Car>().MapToStoredProcedures();        }    }    public class Car    {        public int CarId { get; set; }        [MaxLength(100)]        public string Model { get; set; }        public int Year { get; set; }        [MaxLength(200)]        public string Manufacturer { get; set; }    }

EF調用樣本

   public static void EFExecuteExample()        {            //string connectionString = ConfigurationManager.ConnectionStrings["mydb"].ConnectionString;            //using (MySqlConnection connection = new MySqlConnection(connectionString))            //{            //    // Create database if not exists            //    using (Parking contextDB = new Parking(connection, false))            //    {            //        contextDB.Database.CreateIfNotExists();            //    }            //    connection.Open();            //    //MySqlTransaction transaction = connection.BeginTransaction();            //    try            //    {            //        // DbConnection that is already opened            //        using (Parking context = new Parking(connection, false))            //        {            //            // Interception/SQL logging            //            context.Database.Log = (string message) => { Console.WriteLine(message); };            //            // Passing an existing transaction to the context            //           // context.Database.UseTransaction(transaction);            //            // DbSet.AddRange            //            List<Car> cars = new List<Car>();            //            cars.Add(new Car { Manufacturer = "Nissan", Model = "370Z", Year = 2012 });            //            cars.Add(new Car { Manufacturer = "Ford", Model = "Mustang", Year = 2013 });            //            cars.Add(new Car { Manufacturer = "Chevrolet", Model = "Camaro", Year = 2012 });            //            cars.Add(new Car { Manufacturer = "Dodge", Model = "Charger", Year = 2013 });            //            context.Cars.AddRange(cars);            //            context.SaveChanges();            //        }            //        //transaction.Commit();            //    }            //    catch(Exception ex)            //    {            //        Console.WriteLine(ex.Message);            //        //transaction.Rollback();            //        throw;            //    }            //}            //try            //{            //    Database.SetInitializer(new DropCreateDatabaseIfModelChanges<Parking>());            var context = new Parking();            //    //插入一行值            //    context.Cars.Add(new Car { Manufacturer = "Nissan", Model = "370Z", Year = 2012 });            //    int result = context.SaveChanges();            //}            //catch (Exception ex)            //{            //    Console.WriteLine(ex.Message);            //}            Car car = context.Cars.First(item => item.CarId == 1); //根據ID查詢            var cars = context.Cars.Where(item => item.Model == "370Z"); // 條件尋找
　　　　　　
　　　　　　　cars = context.Cars.Where(item => item.Year > 2012);　
        }

MySQL原生訪問

 static void MySqlDbTest()        {            MySql.Data.MySqlClient.MySqlConnection conn;            string myConnectionString;            myConnectionString = ConfigurationManager.ConnectionStrings["myDb"].ConnectionString;            try            {                conn = new MySql.Data.MySqlClient.MySqlConnection();                conn.ConnectionString = myConnectionString;                MySqlCommand cmd = conn.CreateCommand();                cmd.CommandType = System.Data.CommandType.Text;                cmd.CommandText = "select * from users";                conn.Open();                using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))                {                    while (reader.Read())                    {                        Console.WriteLine("id={0},firstname={1},lastname={2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2));                    }                }            }            catch (MySql.Data.MySqlClient.MySqlException ex)            {                Console.WriteLine(ex.Message);            }        }

 

Entity Framework6 訪問MySQL