LINQ with MySQL

LINQ with MySQL

LINQ to SQL from Microsoft supports only their SQL Server. How about other popular DBMS such as MySQL ?
I investigate the first result returned from Google using "LINQ with MySQL" query: DbLinq. It's an opensource project which provides "LINQ to SQL" for other databases include MySQL, PostgreSQL, Oracle and SQLite.
However, the project is still at its very beginning stage and I think it will take some time before we can use it in production environment, IMHO.
Here is my setup of DbLinq with MySQL

• Visual Studio 2008 Professional
• MySQL 5
• DbLinq 0.18
• Windows Vista

1. I first have my database prepared, its name is "Items".
2. Run DbMetal which is a tool comes with DbLinq in "build" folder. I use the following command for my MySQL server. I have user root with no password.

C:\Downloads\DbLinq-0.18\DbLinq-0.18\build>DbMetal /server:127.0.0.1 /database:items /provider:MySQL /user:root /code:Items.cs
DbLinq Database mapping generator 2008 version 0.18.0.0
for Microsoft (R) .NET Framework version 3.5
Distributed under the MIT licence (http://linq.to/db/license)
>>> Reading schema from MySQL database
<<<>

3. You will have a C# code file, Items.cs in my case, automatically generated. This file contains C# classes generated from database schema and the mapping attributes.
4. You may want to compile the project now. I found some errors in my cases:

• There is no type specified for fields of MySQL's Enum type. You can fix this easily by insert C#'s "string" type. In my case: private _gender; to private string _gender;
• EntitySet cannot be resolved. Change every occurances to System.Data.Linq.EntitySet instead.

If you can compile your project, you can proceed to the next step.
5. Here is my code for querying from MySQL database. From my setup, it's an ASP.NET web application with a single GridView on the Default.aspx page.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

protected void Page_Load(object sender, EventArgs e)

{

string connStr =

"server=127.0.0.1; user id=root; database=items";

Durian.Items db = new Durian.Items(

new MySqlConnection(connStr));

var products = from p in db.Item

where p.UnitPriceCredit > 10

select new { p.ItemID, p.Name, p.Remaining };

GridView1.DataSource = products;

GridView1.DataBind();

}

6. Run and see the result : )