In my previous post I blogged about the importance of choosing the best database platform for your project. As database platforms are usually very expensive and more than 80% of the applications you’re developing are small size, you need to choose an affordable database platform and as I described in my previous post, MySQL is one of the best choices.
There’s no doubt that Microsoft.NET framework is one of the best framework for developing apps no matter what kind of app you’re going to develop. But sometimes you need to use a different database platform. Most of .NET developers are using Microsoft SQL Server as their database back-end. In this post I want to tell you how to connect to a MySQL database and use it alongside Entity Framework.
Note: I am using Microsoft Windows in this post. You cannot use this post’s instructions if you’re using Linux or Mac because as the time of writing this post, Entity Framework cannot be installed and used with Mono Develop or Xamarin Studio.
The first thing you need is obviously MySQL Server. MySQL Server is a service which runs on your machine and enables you to have MySQL functionality and it’s available to download from MySQL download center.
After installing MySQL Server it’s possible to connect to it via Windows Command Prompt but it’s such a pain in the neck! So you need to use something like Microsoft SQL Server Management Studio (SSMS) to connect to your database server. There are bunch of free/open source applications you can use which work like SSMS. For example, phpMyAdmin is a free tool you can use to access your MySQL database on the web. But I recommend you to use MySQL Workbench!
MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.
It’s very cool and easy to use tool to connect to your MySQL database and it can be downloaded from here.
Now it’s possible to just connect to your database by using MySQL Workbench and then create you first database. While this post is not going to cover T-SQL stuff, I assume that you know T-SQL enough as you can create a database named “test”, a table named “test_table” with two columns as shown below in it:
Also add a new record with id of “1” and name of “It works!” to the table you’ve just created so we can test whether we’re connect to the database or not.
Now your database is ready. The next step is to make Visual Studio ready as well! In order to connect to a MySQL database, you’ll need to install two things:
- MySQL connector for Visual Studio: This is the main library to access MySQL from Visual Studio. Even if you’re not going to use Entity Framework, you need to install this component.
- MySQL for Visual Studio: It’s a Visual Studio plugin that has to be installed when want to connect to a MySQL database via Entity Framework.
After installing MySQL tools for Visual Studio, it’s time to launch Visual Studio and create a console application and call it mySqlTest.
After new application created, right-click on your project in Solution Explorer, point to Add and select New Item. In the window appears, select “ADO.NET Entity Data Model” from the list,change the name to “DataModel.edmx” and click Add as shown below:
By clicking Add button, Entity Data Model Wizard will be opened:
Select “Generate from database” and click next. A new window will be opened and ask you for the database connection.
This is the most important part of connecting to a MySQL database. Click on “New Connection” button. The “Connection Properties” page will be open to ask you the server name you’re going to connect to. The first field in the Connection Properties page is the data source. By default data source field is set to “Microsoft SQL Server (SqlClient)” but to connect to a MySQL database, you need to change it; so, click on “Change” button. By clicking the “Change” button, “Change Data Source” Window will be appeared:
Select “MySQL Database” and then click “OK”. When “OK” is clicked, a new window will be opened to ask you about your database credentials which has 3 major fields.
The first field is “Server name”. If MySQL is installed on your local machine, just type “localhost” in the “Server name” field. The next two fields are database username and password. These fields are required to connect to your database. After filling all 3 fields, click “Test Connection” to see if everything is OK and the click “OK”. As a result, Visual Studio creates the connection string for you. just click “Next”.
The next window asks about the database objects you’re going to add to the data model. As we have only one table “test_table”, just check Tables node and click “Finish”.
You’re almost done! Now open “program.cs” file and add the following lines of codes to the main function:
DataModelEntities db = new DataModelEntities(); Console.WriteLine(db.test_table.First().name); Console.ReadLine();
Now run the app by pressing F5. If everything is correct, you must see the following:
Now you app is connected to a MySQL server via Entity Framework. You can now add,edit,delete or do anything else using entity framework.
Hope it helps