How to use Entity Framework to connect to a MySQL database

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.

Getting Started

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:

My SQL Workbench table scheme

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:

  1. 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.
  2. 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:

Select ADO.NET Entity Data Model

By clicking Add button, Entity Data Model Wizard will be opened:

Entity Data Model Wizard

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:

changedatasourcepage

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:

mysqlConnected_Test

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

Choosing the best database platform for your project

Choosing the best platforms which covers all of your project’s needs is one of the most important tasks to do before starting every project. You have a very important decision to make because it’s the platform you’re going to work with until end of project. Many decisions need to be made before getting started but this post covers one of them; the database!

Beside operating system, database platform is the most expensive part of your project. Some of the databases’ licenses are very expensive. For example, if you take a look at Microsoft SQL Server pricing page you will notice that the enterprise edition of SQL Server 2012 price is $6,874 per core! Which means, if you want SQL Server to use all of your server’s resources and assume that your server has a four-core processor, you’ll have to pay about $27,496 which is seriously expensive.

Unfortunately, In my country (Iran), most of developers don’t care about pricing because the lake of copyright. A few month ago, I saw a web server in Iran which had SQL Server 2012 Data Center edition without paying ever $1! Most Iranian developers prefer to use SQL Server in their projects because of three main reasons:

  • .NET Framework is very very popular among Iranian developers. It’s also very easy to learn.
  • Pricing/Licensing is not important due to the lake of copyright.
  • SQL Server is integrated with Microsoft technologies like .NET. As a result, it’s the easiest choice for .NET developers.

Anyway and beside pricing/licensing, choosing a database platform depends on lots of things. Here are some:

Size of your application

Unfortunately, some developers don’t care about it often. The want the best for all of their solutions. For instance, a while ago, I saw a personal website which was using SQL Server 2008 Enterprise edition as its database back-end. It’s obvious that a personal website doesn’t need a database this big! However, if you’re developing a large application such as banking and accounting apps you will need to use a powerful database. My previous blog was using XML as data source. It was enough for my website and I never felt I need more. If you think you want more than XML, you can try using SQLite or MySQL which are both free. SQL Server also has an Express edition which is free too.

Your server’s hardware configuration

Believe me, there’s no need to install a very heavy database platform on a server with only 1 GB of RAM! While Enterprise editions have the most, they use more resources as well. For example, if you’re using a VPS with only 2 GB or RAM and a single core of CPU to host your website, you should not install Oracle Enterprise edition. It slows down your server so other calculations will get slow too.

Your server’s OS

Another important things to consider is the server’s operating system. Some of database platforms are cross-platform (which means they can be installed on any operating system) and some are not! For example, Microsoft SQL Server can be only installed on Windows and you cannot install it on other operating systems like Linux or Mac whereas Oracle is cross-platform. You can design and implement your database on a windows machine and then deploy it on a Unix server which is pretty cool.

Conclusion

Choosing a database platform is very important before starting a project. There are many factors you should consider while choosing a database platform. More than 80% of websites and applications are small-sized; therefore, you should not use heavy and expensive databases. I believe MySQL is the best choice. It’s open-source, free and highly scalable. It covers all of my needs and it can be installed on any server no matter what operation system is installed.

I think you should reconsider about the database you’re using and change it if you think it’s more than you want ASAP before it gets too late.

If you’re using Flurry, update it before submitting your app to the AppStore

Previously, I blogged about how awesome is Flurry, a free analytics system you can use in your mobile apps. I also blogged about why Apple rejected my app and made me resubmit my app. In that post, I’ve described the reason Apple rejected my app was because of using AdSupport.framework.

After cleaning my app from AdSupport.framework and resubmitting it to the AppStore, I got rejected again! So I started to argue with apple and also searched a lot on the internet until I found this StackOverflow question. People were arguing that Apple also rejected their apps too because of using AdSupport.framework whereas they haven’t used it in their applications!

As a result, I decided to contact Flurry support to see if there’s any problem with their SDK. When I logged in to my Flurry account, I saw this:

FlurryMessage

It seems Flurry was also using AdSupport.framework for device identification and since Apple forbids it, they updated their SDK as well 🙂

Therefore, if you’re using third-party SDKs such as Flurry you need to update to the latest version before submitting your app to the AppStore. As far as I know, By the time, other third-party libraries like Google Analytics, Facebook SDK and TestFlight are also having this issue and you should get their latest SDK to prevent your app from being rejected.

The difference between advertisingIdentifier and identifierForVendor in iOS

Last week I resubmitted my first iOS app to the AppStore due to its rejection for using AdSupport.framework while there was no ad functionality in it. Since using AdSupport.framework is forbidden in my app which doesn’t have ad functionality, I used the identifierForVendor property from UIDevice to get device’s unique identifier instead of advertisingIdentifier as Apple described here.

Unfortunately, identifierForVender property does not return the device unique identifier. In fact, this identifier changes if the app change!

The value of this property is the same for apps that come from the same vendor running on the same device. A different value is returned for apps on the same device that come from different vendors, and for apps on different devices regardless of vendor. Normally, the vendor is determined by data provided by the App Store. If the app was not installed from the app store (such as when the app is still in development), the vendor is determined based on the app’s bundle ID. The bundle ID is assumed to be in reverse-DNS format, and the first two components are used to generate a vendor ID. For example,com.example.app1 and com.example.app2 would appear to have the same vendor ID.

The value of identifierForVender property will return a same static identifier value but if a user reinstall the application, value of this property will change! WHAT A FAIL! This is not so good at all! As a result, the value of this property is not device-dependent anymore and it’s impossible to track users’ devices.

advertisingIdentifier property will always return a value no matter what application you’re developing on but identifierForVender changes every time user install an app!

Hope it helps.

My first ever iOS app has rejected by Apple. But Why?

Near a week ago I submitted my first ever iOS app to the App Store. As you may know, it takes about 5 business days for Apple to review your app and it will be approved if everything is OK!

But, things are not as good as  you expect all the time. Yes, my very first app has rejected! But why? The apple told me that I’ve included a library in my application but haven’t used it.

We found your app uses the iOS Advertising Identifier but does not include ad functionality. This does not comply with the terms of the iOS Developer Program License Agreement, as required by the App Store Review Guidelines.

According to the above issue I started to search my application to see if I have used this library. Actually, the best way to do so is to remove the library reference and rebuild the app. If you haven’t been used the specified library in your application’s code it will be built successfully. But in my case, I HAVE been used the library! In fact when I removed the AdSupport.framework reference from my application, the build process failed!

The reason I have used this library in the app was to get device identifier for security purposes. Below is the code I was used to get device identifier:

NSString *deviceIdentifier = [ASIdentifierManager.advertisingIdentifier UUIDString]

As I searched the web to find a solution for this issue I found an article which was released on August 22, 2013 by Apple:

If your apps use the MAC address to identify an iOS device, the system will return the same static value for all devices running iOS 7. Please update your apps to use the identifierForVendor property of UIDevice. If you need an identifier for advertising purposes, use the advertisingIdentifier property of ASIdentifierManager.

So I changed my code to this:

NSString *deviceIdentifier = [[UIDevice currentDevice].identifierForVendor UUIDString]

Both methods will return device’s identifier but the last method doesn’t require AdSupport.framework.

Anyway, I resubmitted my app to the App Store and should wait for another 5 business days to see if Apple approves it.