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 in supports many databases:
- MySQL
- Oracle
- PostreSql
- Ingress
- SqlLite
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:
- Installation
- Using DbLinq in a console application
- Using DbLinq in a ASP.NET application
Installation
- Go here and download the ZIP archieve
- Unpack the archive
- Open the Visual Studio solution - DbLinq
- Build the solution
Using DbLinq in a console application
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 favourite 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:
- System.Data.Linq
- DbLinq.dll (located in \dblinq2007\DbLinq.MySql\bin\release\)
- DbLinq.MySql.dll (located in \dblinq2007\DbLinq.MySql\bin\release\)
- MySql.Data.dll (located in \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();
Using DbLinq in an ASP.NET application
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>
Conclusion
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.