Click here to Skip to main content
15,868,016 members
Articles / Database Development / MySQL
Tip/Trick

Using MySQL with Entity Framework

Rate me:
Please Sign up or sign in to vote.
4.83/5 (43 votes)
23 Jul 2012CPOL2 min read 297.1K   59   26
Explains how to use MySQL with the Entity Framework.

Introduction

Entity Framework is the next level of database programming and is much more flexible and adoptable than earlier methods like ADO.NET and LINQ to SQL.

Prerequisites

  • Install Visual Studio 2008 or Visual Studio 2010
  • Install MySQL database on your local machine
  • MySQL database admin tool that allows you to create a database and run SQL statements. I am using phpMyAdmin which is a web interface.
  • Download and install MySQL Connector.

Getting Started

Run the XAMPP application and it will automatically install Apache server, MySQL database, and FileZilla. After installing check whether these services are running or not. The following XAMPP control panel shows which of those services are currently running.

Image 1

The following steps will show how to connect to a MySQL database using C#.

Step 1

Open MySQL Admin page and create a new database.

Image 2

Step 2

After creating the new database, create a new table.

SQL
CREATE TABLE `employee` (
  `EmpId` bigint (20) NOT NULL,
  `EmpName` varchar (200) default NULL,
  `EmpAddress` varchar (255) default NULL,
  PRIMARY KEY (`EmpId `)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 3

After creating the new table, open Visual Studio and click on New Project and name the project. It will open the new project, then click on Solution Explorer (F4), right click on “Reference” to add a new reference into the project. Reference those two .dll files to the project (MySql.dll (Win apps), MySql.Data.Entity.dll).

Image 3

Step 4

In Solution Explorer, go to “System.Data” properties set “Copy Local” property is true.

The Copy Local property (corresponding to CopyLocal) determines whether a reference is copied to the local bin path. At run time, a reference must exist in either the Global Assembly Cache (GAC) or the output path of the project. If this property is set to true, the reference is copied to the output path of the project at run time.

Image 4

Step 5

Add new Entity Data Model into the project.

Image 5

Step 6

Select “Generate from database” in Entity Data Model Wizard.

Image 6

Step 7

Select MySQL database.

Image 7

Step 8

Set Connection properties for MySQL.

Image 8

Step 9

This step will display all the tables, views, stored procedure.

Image 9

Step 10

By double clicking on Model1.edmx, it will open EDM designer which displays all the entities for selected tables.

Image 10

Step 11

The following code will insert the data into MySQL table.

C#
private void btnInsert_Click(object sender, EventArgs e)
{
    testdbEntities testcontext = new testdbEntities();
    try
    {

        employee emp = new employee

                   {
                       EmpId = int.Parse(txtId.Text),
                       EmpName = txtName.Text,
                       EmpAddress = txtAddress.Text
                   };
        testcontext.employee.AddObject(emp);
        testcontext.SaveChanges();
        MessageBox.Show("Record Inserted successfully.");
        LoadToGrid();
    }
    catch (Exception ex)
    {

        MessageBox.Show(ex.InnerException.ToString());
    }
}

Step 12

The following code will update the data into MySQL table.

C#
private void btnUpdate_Click(object sender, EventArgs e)
{
    int EmpId;
    string s = txtId.Text;
    int.TryParse(s, out EmpId);
    testdbEntities testcontext = new testdbEntities();
    try
    {
        employee emp = testcontext.employee.First(i => i.EmpId == EmpId);
        {
            emp.EmpName = txtName.Text;
            emp.EmpAddress = txtAddress.Text;
            testcontext.SaveChanges();
            MessageBox.Show("Record Updated successfully.");
            LoadToGrid();
        };
    }
    catch (Exception ex)
    {

        MessageBox.Show(ex.InnerException.ToString());
    }
}

Step 13

The following code will delete the data into MySQL table.

C#
private void btnDelete_Click(object sender, EventArgs e)
{
    int EmpId;
    string s = txtId.Text;
    int.TryParse(s, out EmpId);
    testdbEntities testcontext = new testdbEntities();
    try
    {
        employee emp = testcontext.employee.First(i => i.EmpId == EmpId);
        testcontext.employee.DeleteObject(emp);
        testcontext.SaveChanges();
         MessageBox.Show("Record Deleted successfully.");
        LoadToGrid();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.InnerException.ToString());
    }
}

Step 14

The following function will load the data from the table and bind it into a GridView.

C#
private void LoadToGrid()
{
    testdbEntities testcontext = new testdbEntities();
    var load = from g in testcontext.employee select g;
    if (load != null)
    {
        dataGridView1.DataSource = load.ToList();
    }
}

Step 15

Final result:

Image 11

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
Generalc# mysql entityframework detailed guide below Pin
Yash27379-Dec-18 0:51
Yash27379-Dec-18 0:51 
QuestionERR Pin
Sanoop Sathyapalan4-Jun-17 21:12
Sanoop Sathyapalan4-Jun-17 21:12 
Questionsource code Pin
Member 1249299920-May-16 5:17
Member 1249299920-May-16 5:17 
QuestionCRUD in C# using MySQL for newbies Pin
Ehtesham Astute15-Feb-16 17:45
Ehtesham Astute15-Feb-16 17:45 
QuestionNice Article Pin
Santhakumar Munuswamy @ Chennai10-May-15 18:26
professionalSanthakumar Munuswamy @ Chennai10-May-15 18:26 
QuestionHelp? Pin
Member 994491030-Nov-14 22:27
Member 994491030-Nov-14 22:27 
AnswerRe: Help? Pin
Yash27379-Dec-18 0:52
Yash27379-Dec-18 0:52 
Questionwamp Pin
Dhamx31-Aug-14 5:36
Dhamx31-Aug-14 5:36 
SuggestionStep 12 Code Pin
DamithSL29-Apr-14 16:22
professionalDamithSL29-Apr-14 16:22 
GeneralRe: Step 12 Code Pin
Member 1234104017-Jun-16 0:30
Member 1234104017-Jun-16 0:30 
QuestionMySql.dll Pin
Hugo E.28-Apr-14 5:19
Hugo E.28-Apr-14 5:19 
QuestionHow to run program in other PC? Pin
aolin.wang17-Feb-14 16:27
aolin.wang17-Feb-14 16:27 
GeneralThank you Pin
ashenupendra20-Jan-14 18:06
ashenupendra20-Jan-14 18:06 
QuestionStoked on Step 7 Pin
Saed.NET20-Sep-13 4:01
Saed.NET20-Sep-13 4:01 
AnswerRe: Stoked on Step 7 Pin
maev2-Oct-13 16:58
professionalmaev2-Oct-13 16:58 
GeneralRe: Stoked on Step 7 Pin
atifstyle6-Nov-14 18:48
atifstyle6-Nov-14 18:48 
GeneralMy vote of 5 Pin
Humberto Quijano8-Aug-13 7:00
Humberto Quijano8-Aug-13 7:00 
QuestionVery nice Artical... Pin
ShailendraP5-Jul-13 23:56
ShailendraP5-Jul-13 23:56 
AnswerRe: Very nice Artical... Pin
Ravindra T C6-Jul-13 6:35
professionalRavindra T C6-Jul-13 6:35 
QuestionGood one Pin
demouser7437-Dec-12 21:53
demouser7437-Dec-12 21:53 
QuestionMore about this ... Pin
Dado_sylca17-Nov-12 0:29
Dado_sylca17-Nov-12 0:29 
AnswerRe: More about this ... Pin
Yash27379-Dec-18 0:53
Yash27379-Dec-18 0:53 
GeneralMy vote of 5 Pin
2374130-Oct-12 6:05
2374130-Oct-12 6:05 
GeneralMy vote of 5 Pin
KbrKnight24-Jul-12 23:53
KbrKnight24-Jul-12 23:53 
GeneralRe: My vote of 5 Pin
Ravindra T C25-Jul-12 0:15
professionalRavindra T C25-Jul-12 0:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.