LINQ to SQL is a great tool that saves a lot of time. However, it has one disadvantage – it supports only SQL Server. Anyway, it's possible to write your own LINQ provider. Sure, there is one for MySQL. It is called DbLinq, in fact, it supports many databases:
It’s not finished yet, however, you can still try to use it. I’m going to explain how to use it in this article; we’ll go through 3 steps:
DbLinq.Ok, let’s try it in action, we are going to create a console application that will retrieve, insert, update, and delete records from the database.
First, we need to generate the data context and the table classes. Unfortunately, you cannot do this in Visual Studio, but there is a command line utility called DbMetal and its visual analogue – Visual Metal that is written in WPF, by the way.
Go to the \dblinq2007\DbMetal\ folder, there are several BAT files, open in your favorite text editor run_myMetal.bat, since we are dealing with MySQL, and change the necessary parameters, save and run it. You must have got a generated CS file, copy it to your console application project.
Then, it’s time to add the references, you should add the following:
\dblinq2007\DbLinq.MySql\bin\release\)\dblinq2007\DbLinq.MySql\bin\release\)\dblinq2007\lib\)Since the table is called Products, I want the class that represents a stored entity to be called Product, not Products, as it was called by DbMetal. So, I change the generated code.
Compile the project to check if there are no errors. If you get any error, check if there are all the necessary references.
Let’s write some code; basically, we want just to display the data:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient; //Don't forget to add this line
namespace MySqlLinqTest
{
class Program
{
static void Main(string[] args)
{
LinqTest db = new LinqTest(new MySqlConnection("Database=<your database>;Data Source=localhost;User Id=<user>;Password=<password>"));
var products = from p in db.Products
where p.Category == "Sport cars"
select p;
foreach (var product in products)
{
Console.WriteLine(product.Name);
}
}
}
}
That works. What about inserting a new record? No problems!
db.Products.InsertOnSubmit(new Product { Name = "Some car", Category = "Some category" });
db.SubmitChanges();
var products = from p in db.Products
orderby p.Name
select p;
foreach (var product in products)
{
Console.WriteLine(product.Name);
}
Ok, let’s remove the newly added record:
Product someCar = (from p in db.Products
where p.Name == "Some car"
select p).First();
db.Products.DeleteOnSubmit(someCar);
db.SubmitChanges();
Let’s update something:
Product bentley = (from p in db.Products
where p.Name == "Bentley"
select p).First();
bentley.Name = "Bentley Continental";
db.SubmitChanges();
It was funny enough, but the console applications are not very useful when dealing with the database, let’s write something that is closer to a real world – an ASP.NET application.
Create a C# web site; copy the generated file we used in the previous example.
Don’t forget to add the necessary references.
We are going to add a GridView and bind the data retrieved from the database.
Default.aspx
<asp:GridView ID="gridProducts" runat="server"> </asp:GridView>
Default.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
LinqTest db = new LinqTest(new MySqlConnection("Database=<your database>;Data Source=localhost;User Id=<user>;Password=<password>"));
gridProducts.DataSource = from p in db.Products
orderby p.Name
select p;
gridProducts.DataBind();
}
It works.
What about LinqDataSource``, can we use it with DbLinq? Yes, we can, but it’s a bit tricky. If we add a LinqDataSource, we’ll get an error saying that there’s no parametless constructor in our DataContext. That is a thing that hopefully will be fixed in the new versions, but now we have to write a wrapper around a generated DataContext`.
App_Code/Products.cs
public class MyDataContext
{
private LinqTest _db;
public MyDataContext()
{
_db = new LinqTest(new MySqlConnection("Database=<your database>;Data Source=localhost;User Id=<user>;Password=<password>"));
}
public Table<Product> Products { get { return _db.GetTable<Product>(); } }
}
Then, we can use LinqDataSource as usual:
<asp:LinqDataSource
ID="dsProducts"
runat="server"
ContextTypeName="MysqlLinqTest.MyDataContext"
TableName="Products"
>
</asp:LinqDataSource>
<asp:GridView ID="gridProducts" runat="server" DataSourceID="dsProducts"
>
</asp:GridView>
As you see you can use LINQ to work with MySQL, unfortunately sometimes it’s not very convenient and we have to wait for the new versions or wait for a LINQ to MySQL provider from MySQL.
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way. My personal thoughts tend to change, hence the articles in this blog might not provide an accurate reflection of my present standpoint.
© Mike Borozdin