Using LINQ with MySQL

This code demonstrates access to OSCOMMERCE database objects which are hosted on a mysql server, through the enitity framework and LINQ. Additionaly, the connectionstring is overriden at runtime to seperate developent and production environments.

  • Install the MySql connector for visual studio
  • Add a ADO.NET Entity data object to the solution
  • Use the Wizard to create the object context (adding the tables product and products_descriptions for this example
  • If you use VS2012: Remove the .tt files generated under the .edmx file
  • Set the code generation strategy to Default (click on the designer surface / properties)
using System;
using System.Collections.Generic;
using System.Data.EntityClient;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
 
namespace MysqlEntities
{
    class Program
    {
        /// <summary>
        /// Sample OSCOmmerce access using Entity framework and LINQ with Mysql
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
 
            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
            builder.Server = "your.server.ip";
            builder.Database = "oscommerce";
            builder.UserID = "mysqluser";
            builder.Password = "mysqlpassword";
 
            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
            entityBuilder.Provider = "MySql.Data.MySqlClient";
            entityBuilder.ProviderConnectionString = builder.ToString();
            entityBuilder.Metadata = @"res://*/oscommerce.csdl|res://*/oscommerce.ssdl|res://*/oscommerce.msl";
            string entString = entityBuilder.ToString();
 
            oscommerceEntities oscContext = new oscommerceEntities(entString);
             
            // select some random product
            var prod = (from p in oscContext.products
                       where p.products_model == "403"
                       select p).SingleOrDefault();
            if (prod == null) return; // not present
 
            // retrieve description field and display it
            var desc = (from d in oscContext.products_description
                        where d.products_id == prod.products_id
                        select d).Single();
 
            Console.WriteLine(desc.products_name);
            Console.ReadLine();
 
 
 
 
        }
    }
}

If you do not install the MySQL connector on the target machines, you will also need to register the provider, by adding the following section to your web.config or app.config

<system.data>
  <DbProviderFactories>
    <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.6.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </DbProviderFactories>
</sy