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.

Why should you use Rules in SQL Sever?

Validating data is one of the most important sections in data-driven applications. It’s a critical task to control and validate data that are inserting or updating. Most of developers are using constrains (Default, Check, Unique) in order to do so; most of them are using Check constrains in order to validate fields.
Sometimes, a developer needs to use a validation frequently. For example, imaging you’re going to validate email address in SQL Sever. In order to do so, you’ll need to write a complex regular expression as a check constraint. But there is one better way; you can use Rules to store a validation expression and then use it where ever you want.

Creating a rule is simple:

CREATE RULE EmailValidator
AS
begin

@value like '%@%.[a-z][a-z].[a-z][a-z]';

end

Once you create a rule, you can use frequently. In order to do so, you need to make use of sp_bindrule system stored-procedure.

The structure of sp_bindrule property is something like this:

sp_bindrule [ @rulename = ] 'rule' , 
[ @objname = ] 'object_name' 
[ , [ @futureonly = ] 'futureonly_flag' ]

And here’s an example. Note that today is the name of our rule:

USE master;
GO
EXEC sp_bindrule 'today', 'HumanResources.Employee.HireDate'

For more information about sp_bindrule you can out its MSDN article: http://msdn.microsoft.com/en-us/library/ms176063/

How to convert DateTime to a specific culture using CLR integration

During my Pocket PC project development, I needed to convert Gregorian date to Persian date. It’s easy to do such a thing in Windows or Web applications using System.Globalization namespace (PersianCalendar class) but this feature is not available when developing a Pocket PC application. I’ve tried a lot of things such as web services, windows services, etc but none of them is as good as converting it in your database and fortunately my database is Microsoft SQL Server 2005. So I decided to use CLR integration and create an UDF (User-Defined Function) in order to do so. In this post I wanted to show you how you can use CLR to create a function in .NET environment and execute it in SQL Server 2005-2008 environments.

The first thing you need is to create a SQL Server project which is available in Database node in new Project window:

Creating a SQLServer Project

Note that I used .NET Framework 2 because I wanted to create a CLR function in SQL Server 2005. If you’re using SQL Server 2008, there’s no problem to use .NET Framework 3.5 (SP1).

After you create a SQL Server project you can add any object that is common in SQL Server to your project by right click on the project and point to add; so you add a UDF, SP, etc:

Adding a new object 

As I mentioned before, we want to create an application which converts DateTime to another culture so I need to create a UDF. I select User-Defined Function and name it “PersianDateConvertor”. As you can see, Visual Studio will create a class named UserDefinedFunctions and add a method with the name you just specified:

using System;using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString PersianDateConvertor()
{
}
};

Now you need to convert a DateTime to a Persian DateTime like before using System.Globalization.PersianCalendar class. So our class will be something like this:

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString PersianDateConvertor(DateTime dt)
{
PersianCalendar p = new PersianCalendar();
return String.Format("{0}/{1}/{2}",
p.GetYear(dt).ToString(),
p.GetMonth(dt).ToString(),
p.GetDayOfMonth(dt).ToString());
}

By the way, As you can see our method is a static method and it returns a SqlString. You can change the return type if you want.

You’ve created you CLR library! Now it’s time to use it in Microsoft SQL Server. By default, you cannot use CLR libraries in SQL Server unless you enable it. In order to enable CLR integration, you need to make use of sp_configure system stored-procedure:

EXECUTE sp_configure 'clr enabled' , '1'

Note: In order to enable CLR, you need administrative privileges.

In order to make use of an external assembly in SQL Server, you have to create an assembly like following:

CREATE ASSEMBLY DateConvertorFROM 'D:\MMR.CRL.DateConvertor.dll'WITH PERMISSION_SET = SAFE;GO

After you’ve created your assembly, you need to use it in an UDF, SP, etc:

CREATE FUNCTION dbo.GetPersianDate(@date datetime)
RETURNS nvarchar(max)AS EXTERNAL NAME DateConvertor.UserDefinedFunctions.PersianDateConvertor;
GO

You’re DONE! now you can use your UDF and the result will be something like this:

SELECT dbo.GetPersianDate(GetDate())

Result:

————————————————————-

1387/12/2

(1 row(s) affected)

Split function in SQL Server

A while ago, when I was working in a company with my friend Omid Mafakher, he developed a table function which works just link split function in other languages such as VB.NET, C#, C++, etc.

After I get permission from Omid, I decided to publish it as a blog post. Here’s the function:

-- This stored procedure splits a string value with the specified delimiter.
-- Copyright 2007 Omid Mafakher.
Create Function [dbo].[Split] (@String nvarchar(MAX), @Delimiter char(1))
Returns @Results Table (Items nvarchar(MAX))
As
Begin
    Declare @Index int
    Declare @Slice nvarchar(4000) 

    Set @Index = 1
    If @String Is NULL Return 

    While @Index != 0 Begin
        Select @Index = CharIndex(@Delimiter, @String)
        If (@Index != 0) Begin
            Select @Slice = left(@String, @Index - 1)
        End else Begin
            Select @Slice = @String
        End
        Insert into @Results(Items) Values (@Slice)
        Select @String = right(@String, Len(@String) - @Index) 

        If Len(@String) = 0 break
    End
    Return
End

SplitFunction.sql (1.38 kb)

How to encrypt data using Microsoft SQL Server

Today I had a problem with Encryption. I wanted to encrypt some data and then compare to the thing that user entered. It’s so easy to do it in .NET environment using System.Security.Cryptography but the problem was, the company had been developed an application using Visual Basic 6 and there was no way to change it.

That company had been developed an encryption algorithm to use in their own products and I didn’t know the structure. I called the old software’s developer and he told me that “I cannot change my structure!”. “The only thing I can do for you is to pass you passwords which users entered”, he said. After lots of challenges, He accepted to add some codes to his application in order to save another password is another database field. So I decided to create a User-Defined Function in order to encrypt my data using MD5 algorithm.

As a matter of fact, I was gotten so far from SQL Server programming since the release of LINQ and after this long time, I got back to the SQL Server T-SQL programming.

In this post, I’m going to tell you how to encrypt data using Microsoft SQL Server.

Encrypting data in SQL Server is much easier than .NET, in my opinion. The HashBytes function will enables you to do so!

Note: HashBytes function is only available in Microsoft SQL Server 2005 and 2008.

Here is the structure:

HashBytes ( <algorithm>, { @input | 'input' } )

And here is the sample:

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('SHA1', @HashThis);

You can also convert it to HEX. To do this you need to make a use of a UDF in the master database named “fn_varbintohexstr” witch do it for you!

This is one of those functions that look simple but you cannot believe how much time I spent in order find it out :-); because I couldn’t find it even in MSDN!

So the code will change a little:

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT master.dbo.fn_varbintohexstr(HashBytes('SHA1', @HashThis));

SQL Server 2005 SP3 is now up

As you may know, my main focus in programming based on databases such as SQL Server, DB2, Oracle, etc. and it’s about 3 years I’m in depth to Microsoft SQL Server.

When I started working with SQL Server 2008, I realized it has a lot of bugs and I stopped working on my new SQL Server 2008-based project until Microsoft released a Cumulative update package for SQL Server 2008 that has solved lots of bugs and more. So I continued my work on my SQL Server 2008 project. The problem was that, I’ve done a lot of projects based on SQL Server 2005 and cannot upgrade them to 2008 because of customer’s situation.

Recently, Microsoft released SP3 of SQL Server 2005 and now it’s ready to download.

SQL Server 2005 SP3 is now available for download and includes the following updates:

  • Roll-up of Quick Fix Engineering (QFE) updates completed since SQL Server 2005 SP2, as well as fixes to critical issues reported through the SQL Server community. Per revised servicing strategy for SQL Server releases, SP3 does not introduce major new features.
  • Feature Pack for SQL Server 2005 SP3 has been refreshed along with this release.
  • SP3 can be used to upgrade from any previous instance of SQL Server 2005
  • SP3 applies to:
    • SQL Server 2005 Enterprise Edition
    • SQL Server 2005 Enterprise Evaluation Edition
    • SQL Server 2005 Standard Edition
    • SQL Server 2005 Workgroup Edition
    • SQL Server 2005 Developer Edition

As Microsoft said, “SQL Server 2005 SP3 designed to work with Windows Vista and Windows Server 2008”.

Oxite, An open source CMS based on ASP.NET MVC by Microsoft

 Oxite Logo - Photo taken from http://www.codeplex.com/oxite I know it’s a little old but in this post I want to talk about Oxite.

Oxite is a new open source blogging system based on ASP.NET MVC which built by Erik Porter, Nathan Heskew, Mike Sampson and Duncan Mackenzie.

This is a simple blog engine written using ASP.NET MVC, and is designed with two main goals:

· To provide a sample of ‘core blog functionality’ in a reusable fashion. Blogs are simple and well understood by many developers, but the set of basic functions that a blog needs to implement (trackbacks, RSS, comments, etc.) are fairly complex. Hopefully this code helps.

· To provide a real-world sample written using ASP.NET MVC.

As they said, this blog engine built not only for developers and as sample and this has been used in MIX Online too.

Here is some other information from http://visitmix.com/lab/oxite:

Oxite was developed carefully and painstakingly to be a great blogging platform, or a starting point for your own web site project with CMS needs. Its line-up of sexy attributes includes: provider-based architecture allowing you to swap out database and search providers (SQL Server DB, local and Live search providers included), built for testability and hence most likely to be voted "hottest in class" by TDD fans (repositories, everything has an interface, etc.), database file and string resource storage so that files get stored in database and strings stored for localization, built to take full advantage of ASP.NET MVC but broken into assemblies so that even ASP.NET WebForm developers can use the data backend and utility code, supports use of Visual Studio Team Suite (DB Pro, Test, etc.), and Background Services Architecture (sending trackbacks, emails, etc. all done as a background process to prevent delays on the web site itself).

Download it now and get some other information by click here

Lots of bugs fixed on SQL Server 2008

After SQL Server 2008 RTM released, most of database developers started working with it because of its outstanding features. But unfortunately it had lots of bugs.

Recently, Microsoft released Cumulative update package 1 for SQL Server 2008 that contains a lot of HotFixes for Microsoft SQL Server 2008 RTM and it’s available for download.

As Microsoft said, these hotfixes applies to:

  • Microsoft SQL Server 2008 Analysis Services
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 Reporting Services
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup

For more details, you can checkout Cumulative update package 1 for SQL Server 2008