How do I access a MySQL database with Entity Framework

Question

Background: I have already create a ASP.net MVC 4 app and a mysql database. I used code-first to generate the databases. The Web App is going to be used as the UI for interacting with the data. However the data is created in a separate C# program. Which gets test data from a serial device.

Problem: I can't figure out how to connect to the database with entity framework in my c# application.

I have already create a new model for the C# app to use. I have read that using shared .DLL isn't the best idea.

this is what I have so far.

    private ReportDBContext db = new ReportDBContext();

    private void saveReport()
    {
        string connStr = "server=RnD-Chopper;user=CWXDAQ;database=ReportDB;port=3306;password=QADXWC;";
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {

            db.Reports.Add();

            // Perform database operations
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
        conn.Close();
        Console.WriteLine("Done.");




        unlockGUI();
    }

report model

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using MySql.Data.Entity;
using MySql.Data.MySqlClient;
using System.Linq;
using System.Web;

namespace CXW__DAQ___Record.Models
{
    public class Report
    {
        [Display(Name = "Test ID #")]
        public int ID { get; set; }

        [Required(ErrorMessage = "Test Name is Required")]
        [Display(Name = "Test Name")]
        public string Name { get; set; }

        [Display(Name = "Date Tested")]
        public DateTime TestDate { get; set; }

        [Display(Name="Test Done For")]
        public string TestFor { get; set; }

        public string Comment { get; set; }

        public enum enumForceUnits { lbs };
        public enum enumTimeUnits { mS };

        [Required(ErrorMessage = "Unit of Force (ForceUnit) is required")]
        public enumForceUnits forceUnit;

        [Required(ErrorMessage = "Unit of Time (TimeUnit) is required")]
        public enumTimeUnits timeUnit;

        public virtual ICollection<Reading> Readings { get; set; }

    }


    public class Reading
    {
        public int ID { get; set; }

        public virtual Report Report { get; set; }

        public long Time { get; set; }
        public double Force { get; set; }
    }


    public class ReportDBContext : DbContext
    {
        public DbSet<Report> Reports { get; set; }
        public DbSet<Reading> Readings { get; set; }
    }
}

I Used Code First in ASP.net MVC 4, (that app is working fine) and then I just copied over the .cs file and removed models/tables I didn't need. I can't find a tutorial on doing this so I just flying by the seat of my pants. — TheColonel26
– TheColonel26, Commented Aug 1, 2013 at 18:17

Cam Bruce · Accepted Answer · 2013-08-01 18:21:44Z

1

You need to use the connector - http://dev.mysql.com/downloads/connector/net

Regenerate your model using the connector, too.

answered Aug 1, 2013 at 18:21

Cam Bruce

5,68924 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TheColonel26 Over a year ago

I am that's what MySqlConnection is part of.

Cam Bruce Over a year ago

The Entity Framework manages the connections. You access the database through the DatabaseContext class that was generated as part of your model.

TheColonel26 Over a year ago

So do I just need to add a connection string to my web.config ? Then it handles the rest? just like ASP.net?

TheColonel26 Over a year ago

How do I generate the models from an existing database?

TheColonel26 · Accepted Answer · 2013-08-05 21:33:25Z

I figured it out myself. You need to add the connection string to the the app.config file and then install Entity Framework Power Tools then use that to reverse engineer the models from the database.

When creating the connection string make sure to set Persist Security Info=True

Also another problem I ran in to was that Visual Studios kept trying to use the 6.6.5 version of the connector instead of the version that was install which is 6.7.4

so I had to add this line to the app.config file as well

<runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="MySql.Data" publicKeyToken="c5687fc88969c44d" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-6.6.5.0" newVersion="6.7.4.0" />           
      </dependentAssembly>
    </assemblyBinding>
  </runtime>

Collectives™ on Stack Overflow

How do I access a MySQL database with Entity Framework

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related