Language-Integrated Query (LINQ), allows .NET programs to connect to databases. It is an Object-Relational mapping tool, with some similarities to Hibernate or LLBLGen. LINQ is type-safe, queries get compiled into MSIL on the fly, and your C# WHERE clauses are translated into SQL and sent to SQL server for execution. In short, it makes design of data access layers safer and faster.
Today I have work with LINQ to MySQL, and arrived at
DbLinq.
DbLinq is the LINQ provider that allows to use common databases with an API close to Linq to SQL. DbLinq is a reimplementation of System.Data.Linq.dll for use with SQL servers. It supports MySQL, Oracle, PostgreSQL, SQLite, Ingres, Firebird, and SQL Server.
This code use DbLinq 0.20.1 (released 2010-04-16).
1. Download and extract
DbLinq to c:\DbLinq-0.20.1
2. Copy
run_myMetal.bat to
runme.bat, and modify it to
DbMetal.exe /conn:"server=localhost;user id=myname;password=a; port=3306; database=mydb" /provider=MySql /code:FerDBLinq.cs /language:C#
4. Execute
runme.bat so we get
FerDBLinq.cs. Copy the file into your project folder then use
Add Existing items from
Solution Explorer.
5. Add reference to
DbLinq.dll,
DbLinq.MySql.dll6. Open
FerDBLing.cs file, add these in the beginning:
using DbLinq.MySql;
using DbLinq.Data.Linq;
using System.Data.Linq.Mapping;
using MySql.Data.MySqlClient;
Here code snippets that follows:
public partial class myDBS: MySqlDataContext
{
#region Extensibility Method Declarations
partial void OnCreated();
#endregion
public myDBS(string conStr) : base(new MySqlConnection(conStr)) { }
public myDBS(MySqlConnection conn): base(conn){}
public DbLinq.Data.Linq.Table<BUkUBeSaR> BUkUBeSaR
{
get { return this.GetTable<BUkUBeSaR>();}
}
5. Creata a function for testing purpose.
private void linqing()
{
using (MySqlConnection conn = new MySqlConnection(GconString))
{
try
{
conn.Open();
myDBS db = new myDBS(conn);
var q = (from p in db.PerSoNaL where p.member=="Y";
Console.WriteLine("Output #1:");
foreach (var a in q)
Console.WriteLine("id= {0}, ket= {1}", a.PK, a.sex);
var r = from p in db.PerSoNaL group p by p.sex into g
select new { sex = g.Key, Count = g.Count() };
Console.WriteLine("Output #2:");
ObjectDumper.Write(r);
var t = db.PerSoNaL.GroupBy(p => p.sex);
var u = t.Select(g => new {g.Key, Count = g.Count()});
Console.WriteLine("Output #3:");
foreach (var a in u)
Console.WriteLine("sex={0}, count={1}", a.Key, a.Count);
int cnt = (from p in db.PerSoNaL group p by p.sex).Count();
Console.WriteLine("Output #4:\nCount={0}", cnt);
var s=db.PerSoNaL.GroupBy(c=> c.sex).ToList().AsQueryable();
Console.WriteLine("Output #5:");
foreach (var a in s)
Console.WriteLine("sex "+a.Key+ ", count=" +a.Count());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
The output:
Dblinq works for me, but I could not resolve about the
count(), for
Male(L) and
Female(P) always return 1. I've read issue
157 and
158, but still locked. Do you have any idea?
