Linq to MySql/Oracle/Postgres/Sqlite
与NHibernate相比较，Linq可能并是领域模型的，有数据为中心的思维痕迹，但在语言集成性上面，是其它O/R方案没有可比性的，Linq不仅仅是为对象与关系转换而生，随着Linq技术的日渐成熟，将会在诸多方面有所表现。
开源社区也非常看好Linq的发展前景，在Linq推出后开源界出现了数十种有关Linq的项目，比如DbLinq(Linq to MySql,Oracle,SQLite)，Linq to NHibernate,甚至还有Linq to google等等，这些项目从不同的应用和不同的角度展示出Linq的魅力。
因为关注mono原因，时不时会了解一下mono的最新进展，当然也非常想看到Linq在mono中的应用，不过在mono1.2.6并没有集成,在下一个版本中肯定会有所表现。在Miguel de Icaza的博客中提到了一个DbLinq2007项目，可能会用于mono，目前最高版本是v0.16,试了一下，在vs.net2008beta2环境中的确能用，可到http://code.google.com/p/dblinq2007/下载，目前下载量不到千人，可以先下载尝尝鲜。
DbLinq2007项目包括DbLinq.SqlServer/DbLinq.MySql/DbLinq.Oracle/DbLinq.PostgreSql/
DbLinq.Sqlite几个部分，各部分相对独立的，本人试用了DbLinq.PostgreSql和DbLinq.MySql感觉非常不错，使用非常方便，使用方式也差不多,同Ms的Linq to Sql也几乎没有什么区别。下面以Northwind数据库为例，仅介绍一下DbLinq.MySql的简单应用。
一、在MySql中建立用户与数据库：
1、 建立用户LinqUser：
CREATE USER 'LinqUser'@'%'; SET PASSWORD FOR 'LinqUser'@'%' = PASSWORD('LinqUser');
##
GRANT Select, Insert, Update, Delete, EXECUTE ON `Northwind`.* TO 'LinqUser'@'%';
FLUSH PRIVILEGES;
2、 以LinqUser登录建立数据库Northwind：
/*
MySQL Data Transfer
Source Host: localhost
Source Database: northwind
Target Host: localhost
Target Database: northwind
Date: 2008-1-24 22:23:29
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for categories
-- ----------------------------
CREATE TABLE `categories` (
`CategoryID` int(11) NOT NULL auto_increment,
`CategoryName` varchar(15) NOT NULL,
`Description` text,
`Picture` blob,
PRIMARY KEY (`CategoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for customers
-- ----------------------------
CREATE TABLE `customers` (
`CustomerID` varchar(5) NOT NULL,
`CompanyName` varchar(40) NOT NULL default '',
`ContactName` varchar(30) default NULL,
`ContactTitle` varchar(30) default NULL,
`Address` varchar(60) default NULL,
`City` varchar(15) default NULL,
`Region` varchar(15) default NULL,
`PostalCode` varchar(10) default NULL,
`Country` varchar(15) default NULL,
`Phone` varchar(24) default NULL,
`Fax` varchar(24) default NULL,
PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for employees
-- ----------------------------
CREATE TABLE `employees` (
`EmployeeID` int(11) NOT NULL auto_increment,
`LastName` varchar(20) NOT NULL,
`FirstName` varchar(10) NOT NULL,
`Title` varchar(30) default NULL,
`BirthDate` datetime default NULL,
`HireDate` datetime default NULL,
`Address` varchar(60) default NULL,
`City` varchar(15) default NULL,
`Region` varchar(15) default NULL,
`PostalCode` varchar(10) default NULL,
`Country` varchar(15) default NULL,
`HomePhone` varchar(24) default NULL,
`Photo` blob,
`Notes` text,
`ReportsTo` int(11) default NULL,
PRIMARY KEY (`EmployeeID`),
KEY `FK_Emp_ReportsToEmp` (`ReportsTo`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`ReportsTo`) REFERENCES `employees` (`EmployeeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for employeeterritories
-- ----------------------------
CREATE TABLE `employeeterritories` (
`EmployeeID` int(11) NOT NULL,
`TerritoryID` varchar(20) NOT NULL,
PRIMARY KEY (`EmployeeID`,`TerritoryID`),
KEY `FK_empTerr_terr` (`TerritoryID`),
CONSTRAINT `employeeterritories_ibfk_1` FOREIGN KEY (`EmployeeID`) REFERENCES `employees` (`EmployeeID`),
CONSTRAINT `employeeterritories_ibfk_2` FOREIGN KEY (`TerritoryID`) REFERENCES `territories` (`TerritoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for order details
-- ----------------------------
CREATE TABLE `order details` (
`OrderID` int(11) NOT NULL,
`ProductID` int(11) NOT NULL,
`UnitPrice` decimal(10,0) NOT NULL,
`Quantity` smallint(6) NOT NULL,
`Discount` float NOT NULL,
PRIMARY KEY (`OrderID`,`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for orders
-- ----------------------------
CREATE TABLE `orders` (
`OrderID` int(11) NOT NULL auto_increment,
`CustomerID` varchar(5) default NULL,
`EmployeeID` int(11) default NULL,
`OrderDate` datetime default NULL,
`RequiredDate` datetime default NULL,
`ShippedDate` datetime default NULL,
`ShipVia` int(11) default NULL,
`Freight` decimal(10,0) default NULL,
`ShipName` varchar(40) default NULL,
`ShipAddress` varchar(60) default NULL,
`ShipCity` varchar(15) default NULL,
`ShipRegion` varchar(15) default NULL,
`ShipPostalCode` varchar(10) default NULL,
`ShipCountry` varchar(15) default NULL,
PRIMARY KEY (`OrderID`),
KEY `FK_orders_1` (`CustomerID`),
KEY `FK_orders_emp` (`EmployeeID`),
CONSTRAINT `FK_orders_1` FOREIGN KEY (`CustomerID`) REFERENCES `customers` (`CustomerID`),
CONSTRAINT `FK_orders_emp` FOREIGN KEY (`EmployeeID`) REFERENCES `employees` (`EmployeeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for products
-- ----------------------------
CREATE TABLE `products` (
`ProductID` int(11) NOT NULL auto_increment,
`ProductName` varchar(40) NOT NULL default '',
`SupplierID` int(11) default NULL,
`CategoryID` int(11) default NULL,
`QuantityPerUnit` varchar(20) default NULL,
`UnitPrice` decimal(10,0) default NULL,
`UnitsInStock` smallint(6) default NULL,
`UnitsOnOrder` smallint(6) default NULL,
`ReorderLevel` smallint(6) default NULL,
`Discontinued` bit(1) NOT NULL,
PRIMARY KEY (`ProductID`),
KEY `FK_prod_catg` (`CategoryID`),
KEY `FK_prod_supp` (`SupplierID`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`CategoryID`) REFERENCES `categories` (`CategoryID`),
CONSTRAINT `products_ibfk_2` FOREIGN KEY (`SupplierID`) REFERENCES `suppliers` (`SupplierID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Holds Products';
-- ----------------------------
-- Table structure for region
-- ----------------------------
CREATE TABLE `region` (
`RegionID` int(11) NOT NULL auto_increment,
`RegionDescription` varchar(50) NOT NULL,
PRIMARY KEY (`RegionID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for shippers
-- ----------------------------
CREATE TABLE `shippers` (
`ShipperID` int(11) NOT NULL auto_increment,
`CompanyName` varchar(40) NOT NULL,
`Phone` varchar(24) default NULL,
PRIMARY KEY (`ShipperID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for suppliers
-- ----------------------------
CREATE TABLE `suppliers` (
`SupplierID` int(11) NOT NULL auto_increment,
`CompanyName` varchar(40) NOT NULL default '',
`ContactName` varchar(30) default NULL,
`ContactTitle` varchar(30) default NULL,
`Address` varchar(60) default NULL,
`City` varchar(15) default NULL,
`Region` varchar(15) default NULL,
`PostalCode` varchar(10) default NULL,
`Country` varchar(15) default NULL,
`Phone` varchar(24) default NULL,
`Fax` varchar(24) default NULL,
PRIMARY KEY (`SupplierID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for territories
-- ----------------------------
CREATE TABLE `territories` (
`TerritoryID` varchar(20) NOT NULL,
`TerritoryDescription` varchar(50) NOT NULL,
`RegionID` int(11) NOT NULL,
PRIMARY KEY (`TerritoryID`),
KEY `FK_Terr_Region` (`RegionID`),
CONSTRAINT `territories_ibfk_1` FOREIGN KEY (`RegionID`) REFERENCES `region` (`RegionID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `categories` VALUES ('1', 'Beverages', 'Soft drinks, coffees, teas, beers, and ales', null);
INSERT INTO `categories` VALUES ('2', 'Condiments', 'Sweet and savory sauces, relishes, spreads, and seasonings', null);
INSERT INTO `customers` VALUES ('AIRBU', 'airbus', 'jacques', null, null, 'Paris', null, '10000', 'France', null, null);
INSERT INTO `customers` VALUES ('ALFKI', 'Alfreds Futterkiste', 'Maria Anders', 'Sales Representative', null, 'Berlin', null, '12209', 'Germany', '030-0074321', null);
INSERT INTO `customers` VALUES ('ATT__', 'ATT', 'bob', null, null, 'New York', null, '10021', 'USA', null, null);
INSERT INTO `customers` VALUES ('BONAP', 'Bon something', 'Bon Boss', 'Sales Representative', null, 'Paris', null, '11109', 'France', '033-0074321', null);
INSERT INTO `customers` VALUES ('BT___', 'BT', 'graeme', null, null, 'London', null, 'E14', 'U.K.', null, null);
INSERT INTO `customers` VALUES ('UKMOD', 'MOD', '(secret)', null, null, 'London', null, 'E14', 'U.K.', null, null);
INSERT INTO `customers` VALUES ('WARTH', 'Wartian Herkku', 'Pirkko Koskitalo', 'Accounting Manager', null, 'Oulu', null, '90110', 'Finland', '981-443655', null);
INSERT INTO `employees` VALUES ('1', 'Fuller', 'Andrew', 'Vice President, Sales', '1954-01-01 00:00:00', '1989-01-01 00:00:00', '908 W. Capital Way', 'Tacoma', null, null, null, null, null, null, null);
INSERT INTO `employees` VALUES ('2', 'Davolio', 'Nancy', 'Sales Representative', '1964-01-01 00:00:00', '1994-01-01 00:00:00', '507 - 20th Ave. E. Apt. 2A', 'Seattle', null, null, null, null, null, null, '1');
INSERT INTO `employees` VALUES ('3', 'Builder', 'Bob', 'Handyman', '1964-01-01 00:00:00', '1994-01-01 00:00:00', '666 dark street', 'Seattle', null, null, null, null, null, null, '2');
INSERT INTO `employeeterritories` VALUES ('2', 'US.Northwest');
INSERT INTO `orders` VALUES ('1', 'AIRBU', '1', '2008-02-01 19:27:44', null, null, null, '21', null, null, null, null, null, null);
INSERT INTO `orders` VALUES ('2', 'BT___', '1', '2008-02-01 19:27:44', null, null, null, '11', null, null, null, null, null, null);
INSERT INTO `orders` VALUES ('3', 'BT___', '1', '2008-02-01 19:27:44', null, null, null, '12', null, null, null, null, null, null);
INSERT INTO `orders` VALUES ('4', 'UKMOD', '1', '2008-02-01 19:27:44', null, null, null, '33', null, null, null, null, null, null);
INSERT INTO `products` VALUES ('1', 'Pen', '1', null, '10', null, '12', '2', null, '');
INSERT INTO `products` VALUES ('2', 'Bicycle', '1', null, '1', null, '6', '0', null, '');
INSERT INTO `products` VALUES ('3', 'Phone', null, null, '3', null, '7', '0', null, '');
INSERT INTO `products` VALUES ('4', 'SAM', null, null, '1', null, '51', '11', null, '');
INSERT INTO `products` VALUES ('5', 'iPod', null, null, '0', null, '11', '0', null, '');
INSERT INTO `products` VALUES ('6', 'Toilet Paper', null, null, '2', null, '0', '3', null, '');
INSERT INTO `products` VALUES ('7', 'Fork', null, null, '5', null, '111', '0', null, '');
INSERT INTO `products` VALUES ('8', 'Linq Book', '2', null, '1', null, '0', '26', null, '');
INSERT INTO `region` VALUES ('1', 'North America');
INSERT INTO `region` VALUES ('2', 'Europe');
INSERT INTO `suppliers` VALUES ('1', 'alles AG', 'Harald Reitmeyer', 'Prof', 'Fischergasse 8', 'Heidelberg', 'B-W', null, 'Germany', null, null);
INSERT INTO `suppliers` VALUES ('2', 'Microsoft', 'Mr Allen', 'Monopolist', '1 MS', 'Redmond', 'WA', null, 'USA', null, null);
INSERT INTO `territories` VALUES ('US.Northwest', 'Northwest', '1');
二、将数据库映射为Northwind.cs文件：
在DbLinq2007项目SqlMetal文件夹中找到SqlMetal.exe文件，在DOS状态运行，格式为：
SqlMetal.exe -provider=MySql -database:Northwind -server:localhost -user:LinqUser -password:LinqUser -namespace:nwind -code:c:\Northwind.cs –sprocs
注：-namespace根据自己的实际项目而定。
三、在vs.net2008中创建应用程序项目，没人不会步骤略。将刚生成的Northwind.cs拷到项目中来。添加引用DbLinq2007中的DbLinq.dll / DbLinq.MySql.dll / MySql.data.dll
四、现在可以在自己的项目中使用Linq to MySql了：
using MySql.Data.MySqlClient;
……
string connStr =@"server=localhost;database=Northwind;user=LinqUser;pwd=LinqUser;port=3306";
MySqlConnection conn = new MySqlConnection(connString);
Northwind db = new Northwind(conn);
var data=from p in db.Customers select new {p.CustomerID,p.Phone};
……
在这里每次使用时要申明一个MySqlConnection比较麻烦。可以修改一下先前生成的文件Northwind.cs，
在里面添加using MySql.Data.MySqlClient;
增加一个新的无参数的构造函数
public Northwind(): base( new MySqlConnection(@"server=localhost;database=Northwind;user=LinqUser;pwd=LinqUser;port=3306")) {
}
当然连接字符串也可以放在配置文件中。
修改了Northwind.cs文件以后，使用时只需Northwind db=new Northwind()方便多了。
通过试用DbLinq2007感觉不错，同时也认为还不够成熟，不够稳定,特别是如果你使用的是0.16以前的版本，发现db.customers.select(p=>p)正确，但db.customers.select(p=>new {p.customerID,p.Phone})出错，0.16这个问题已经解决了。目前还不敢在项目中实际应用，先玩一玩再说，DbLinq2007版本更新比较快,期待着更稳定的版本尽快推出。
------------------------------------------------------------------------------------------------------------------------------------------
|
我把bug提交给jiri,现在已经修改好了,反映够快的.
DbLinq2007现在已可以用于Mono了.
Mono2.0即将发布,期待着Mono商用的开始. | ||
| 删除 | ||
