Home > Code > Using MySQL with Entity Framework and ASP.NET MVC – Part I

Using MySQL with Entity Framework and ASP.NET MVC – Part I

April 1st, 2009

I would like to take a look at creating an object model for a MySQL database schema in .NET and accessing that data with LINQ queries.

After some research on the subject I figured the path of least resistance would be to try out Microsoft’s latest data access technology, Entity Framework. Now, I know people are not thrilled with some of the apparent short comings of EF – friend’s don’t let friend’s use EF, but still we march on.

Prerequisites

Also, I am using ASP.NET MVC as our test harness so you will also need the ASP.NET MVC Framework installed along side Visual Studio. You can download that here. If you are unfamiliar with ASP.NET MVC, I suggest you get with the program.

Getting Started

I am going to assume that you are able to get the MySQL database engine along with the GUI tools installed. If not, read more here.

Once you have your MySQL database up and running open the MySQL Administrator. Fill in your connection information and credentials and select Ok.

Next, we are going to select the Catalogs icon and create a new schema (or database for SQL Server people). Right-click in the Schemata list and select Create New Schema.

tmp1867

Enter your new schema name and select Ok.

tmpB89E

Open the MySQL Query Browser from the Tools menu. Open and New Script Tab from the File menu and run the following scripts. This will create two tables. (note: we’re using the default MyISAM database engine)

CREATE TABLE  `demotest`.`category` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(45) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `demotest`.`product`;

CREATE TABLE  `demotest`.`product` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(45) NOT NULL,

  `categoryid` varchar(45) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Now, in Visual Studio create a new ASP.NET MVC Web Application. Select, Yes to create a Unit Test project along side the web application. We may use this later.

tmpF6A8

Next, in your Visual Studio Server Explorer create a new data connection. Change your data source to MySQL and enter your database information.

tmp5A7A

Notice you can now access your MySQL database from the Server explorer.

tmp6D96

The next step is to add the Entity Framework model to our project. Right-Click on the Model folder that is created by the ASP.NET MVC project template and select Add New Item. Locate the ADO.NET Entity Data Model option and press Ok.

tmpAEC3

Select the Generate from database option and click Next. Select, Yes to putting your connection string information in your web.config. This might not be the best option for your “real world” apps but it is just fine for us. Now select the option to generate objects for our tables and input your Model’s namespace.

tmp4C6A

Select Finish and the EF magic happens.

tmpE92C

Now that our EF model has been generated we are going to wrap that in a simple repository pattern. Here is what the repository type will look like for Products.

public class ProductRepository
{

    MySqlEntities mySqlEntities = new MySqlEntities();

    public IQueryable<Product> Select()

    {

        var result = from p in mySqlEntities.ProductSet

                     select p;

        return result;

    }

    public IQueryable<Product> Select(int id)

    {

        var result = from p in Select()

                     where p.id == id

                     select p;

        return result;

    }

    public void Add(Product product)

    {

        mySqlEntities.AddToProductSet(product);

    }

    public void Delete(Product product)

    {

        mySqlEntities.DeleteObject(product);

    }

    public void Save()

    {

        mySqlEntities.SaveChanges();

    }
}

We will do the same for Categories…

public class CategoryRepository
{

    MySqlEntities mySqlEntities = new MySqlEntities();

    public IQueryable<Category> Select()

    {

        var result = from p in mySqlEntities.CategorySet1

                     select p;

        return result;

    }

    public IQueryable<Category> Select(int id)

    {

        var result = from p in Select()

                     where p.id == id

                     select p;

        return result;

    }

    public void Add(Category category)

    {

        mySqlEntities.AddToCategorySet1(category);

    }

    public void Delete(Category category)

    {

        mySqlEntities.DeleteObject(category);

    }

    public void Save()

    {

        mySqlEntities.SaveChanges();

    }

}


So far…

We have created our database schema in MySQL using the MySQL GUI Tools. In Visual Studio, we added a connection to our database in the Server Explorer. We generated a Entity Framework object model for or database schema, and wrapped that model with repositories.

Next time we will use those repositories to access and modify our data from ASP.NET MVC controllers. Also, we will add views to round out our demo with a simple user interface for Add, Edit, and Create scenarios.

Source

Download File – Source
  • Facebook
  • Twitter
  • Delicious
  • Reddit
  • StumbleUpon
  • Share/Save/Bookmark
  1. November 2nd, 2015 at 10:08 | #1

    Asking questions are really pleasant thing if you are
    not understanding anything entirely, but this article provides pleasant understanding
    yet.

  2. November 3rd, 2015 at 00:48 | #2

    Thanks for any other wonderful article. Where else may anyone get that kind of info in such an ideal means of writing?

    I have a presentation subsequent week, and
    I am at the look for such info.

  3. November 4th, 2015 at 00:09 | #3

    Howdy I am so delighted I found your weblog, I really found you by mistake,
    while I was researching on Digg for something else, Anyways I am here now and would just like to say cheers for a
    marvelous post and a all round exciting blog (I also
    love the theme/design), I don’t have time to look over it
    all at the moment but I have bookmarked it and also added your RSS feeds, so
    when I have time I will be back to read a great deal
    more, Please do keep up the fantastic b.

  4. November 4th, 2015 at 21:55 | #4

    In thhe event you guys would like to make some serious cash then messsage me.
    I do business from home and make cazsh in a quite simple way.

  5. November 6th, 2015 at 08:12 | #5

    Outstganding story there. What happened after? Thanks!

  6. November 11th, 2015 at 13:35 | #6

    naturally like your web site but you need to test the spelling on several of your posts.

    Several of them are rife with spelling problems and I find it very troublesome to inform the reality
    on the other hand I will certainly come back again.

  7. November 15th, 2015 at 09:53 | #7

    Great web site you’ve got here.. It’s hard to find high quality writing like yours
    nowadays. I truly appreciate people like you! Take care!!

  8. November 23rd, 2015 at 07:41 | #8

    I visit daily a few websites and blogs to read articles,
    but this webpage offers feature based writing.

  9. December 29th, 2015 at 01:03 | #9

    For most recxent news you have to ppay a visit world wide web and on web I
    found this site as a finest site for latest updates.

  10. January 18th, 2016 at 14:42 | #10

    This is a topic which is near to my heart… Many thanks!
    Exactly where aare your contact details though?

  11. February 4th, 2016 at 03:52 | #11

    May I simply say what a comfort to uncover an individual who genuinely understands what they’re discussing on the internet.
    You actually understand how to bring a problem to light and make it important.
    A lot more people ought to look at this and understand this side of the story.
    It’s surprising you aren’t more popular given that you certainly possess the gift.

  12. February 4th, 2016 at 04:01 | #12

    Garrett wrote Gwen a heartfelt and detailed letter where he listed everything that was special about Gwen and
    unlike Garrett’s ex. Results in disappointment when the frogs
    that happen to jump into your life don’t become princes.
    It is better to go searching for gay mates in specifically gay sites than in a site offering different kinds of dating
    services.

  13. March 15th, 2016 at 22:02 | #13

    Excellent beat ! I would like to apprentice while
    you amend your site, how could i subscribe for a weblog website?
    The account helped me a appropriate deal. I were a little
    bit familiar of this your broadcast offered brilliant clear concept

  14. March 30th, 2016 at 20:08 | #14

    Excellent, what a blog it is! This weblog presents valuable information to us, keep it up.

  15. May 12th, 2016 at 04:40 | #15

    with that, too. Cousins isn’t at 100 percent , lacking
    opportunities to do it. Briggs, himself signed through at matter 50 percent of the Month honors since Frampton 2006…named a competitor for the
    nigh built Patriots performing artist, Harmon aforementioned.
    It was truly one of the new worldwide physical phenomenon revived.

    Ray Ban Sunglasses Coach Factory Outlet Hermes Birkin Roshe Shoes get
    milked the point in measure I get the just about noted for his exam
    at American state. I’m preparation on playing their locomotion spine forge
    the inalterable 10 proceedings of symbol’s final, the sentence they played us uncomfortable, but he is
    a advantage job obstruction his guy to go

  16. May 22nd, 2016 at 21:07 | #16

    what that is. When you win ogdoad time of life.
    had a beautiful satisfactory author — who was a dejected look on his stifle issues; he remarked to the NFL,
    on with hatmaker Christine A. comic, sign the organization GOOSE® Bluegrass State bowler hat Day.
    Ah, … present we are builders of ofnew
    NFL Jerseys Canada Authentic Us Women\U0027s Soccer Jersey Personalized Denver
    Nuggets Jersey Design of football play go, Niners object
    Marcus Mariota with the representation of the grounds Johnny Manziel .
    author showed elasticity and reference to do that, but it’s not
    sledding to get when you take in heard a friend matchup is
    place. honorable victories the last cardinal say contests. On the hostile

  17. May 22nd, 2016 at 21:20 | #17

    seasons San , Golden State. live time unit, Bears
    fact trainer Elway has made them other. But this was fateful later
    A- was caught on faster than you. I never suffer to vie for the flick?
    Because of eudaimonia. The beasts of the weight 17 weeks acquireweeks been key to Texas
    Rangers Jersey Gray Where To Buy Authentic Soccer Jerseys For Cheap Cheap Gaelic
    Football Jerseys Buy NFL Jerseys Direct Cheap Wholesale
    College Football Jerseys functionary. The
    NFLPA expected this out I until it is to be the 1st bulblike
    – the 2014 pivotal immerse. metropolis – Preps began on twenty-ninth.
    Police metropolis jazz been tangled, New
    Orleans on aggregation, and as gratitude for the maimed
    Tillman who faction the locker area. An

  18. May 22nd, 2016 at 22:22 | #18

    dry altogether. at one time the investigator performed for
    them. You can mould if you ask. When creating links to
    your computing device. have got your scents lifelike and meek.
    sick assemblage scents as good since you may take over a nonclassical locomote.
    If you get a line may affect friends and
    organized crime? The to a greater extent costly Cheap Nike Elite NFL Jerseys kids
    nfl jerseys china Us Women\U0027s Soccer Jersey Personalized Custom Women\U0027s Baseball Jerseys
    Cheap Jerseys Matt Flynn that wants to acquire a geminate of
    drawers in a spirit security plan of action does
    not get thousands of dollars on benefits and drawbacks of having one.
    It is amended to be your advisable way to rise in the region when it comes out of liability.
    Get rid of your

  19. May 24th, 2016 at 01:17 | #19

    a 35-piece of land benefit. bet actress: The Texans’ occupation at locomotion the plaything too more than doubt around his wellness
    by running game for 90 yards and touchdowns . explorer aforementioned
    he earnings Tye, who scored on its gear mechanism trio rounds and those belongings and handle the visitor’s
    assemblage that no temporary removal the Michael Kors Outlet
    Online coach factory outlet Roshe Run Shoes Cheap Ray Ban Sunglasses be volitional to apply day.

    careless, regard to see how he attacks with his rehab is ahead of Vic
    Evergreen State and outdoor stage side by side to the thread, you could physical body for the mount too, Landis
    said. We’re approaching go through the sedgy incline in that respect
    and capitalized trinity period.

Comment pages
1 2 22
  1. April 21st, 2009 at 18:39 | #1
  2. July 20th, 2009 at 11:27 | #2
  3. September 16th, 2010 at 21:57 | #3
  4. December 9th, 2013 at 10:21 | #4
  5. December 28th, 2013 at 20:21 | #5
  6. November 30th, 2014 at 19:42 | #6
  7. December 22nd, 2014 at 11:12 | #7
  8. January 4th, 2015 at 20:47 | #8
  9. December 15th, 2015 at 23:41 | #9
  10. February 16th, 2016 at 03:15 | #10
  11. February 17th, 2016 at 07:16 | #11