浅谈如何使用LINQ检索和操作数据库(续）
1.LinqToObject（使用Linq查询对象）using System;
using System.Collections.Generic;
using System.Linq;
namespace LINQtoObject
{
class Program
{
public static int[] arry = {11,24,31,42,53,64,75,86,97,100,13,147,156,56,67,89,99 };
static void Main(string[] args)
{
Shu2();//能被2整除的数
Shu3();//能被3整除的数
ShuYes2Yes3();//能被2整除也能被3整除的数
ShuNot2Not3();//不能被2整除也不能被3整除的数
Shu7((from a in arry select a).Where(a=>a%7==0));//能被7整除的数
Console.ReadKey();//暂停观看结果
}
public static void Shu2()
{
Console.WriteLine("能被2整除的数有：");
foreach (var ary in (from a in arry where a % 2 == 0 select a))
{
Console.Write("{0} ", ary);
}
}
public static void Shu3()
{
Console.WriteLine("\n能被3整除的数有：");
foreach (var ary in (from a in arry select a).Where(b => b % 3 == 0))
{
Console.Write("{0} ", ary);
}
}
public static void ShuNot2Not3()
{
Console.WriteLine("\n不能被2整除也不能被3整除的数有：");
foreach (var ary in (from a in arry where a % 2 != 0 select a).Where(b=>b%3!=0))
{
Console.Write("{0} ", ary);
}
}
public static void ShuYes2Yes3()
{
Console.WriteLine("\n能被2整除也能被3整除的数有：");
foreach (var ary in (from a in arry select a).Where(b => b % 3 == 0).Where(c=>c%2==0))
{
Console.Write("{0} ", ary);
}
}
public static void Shu7(IEnumerable<int> Query)
{
Console.WriteLine("\n能被7整除的数有：");
foreach (var ary in Query)
{
Console.Write("{0} ", ary);
}
}
}
}
2.LinqToDataSet（使用Linq查询DataSet）
using System;
using System.Linq;//一定要记得添加引用
using System.Data;//用于Dataset
using System.Data.SqlClient;//用于
namespace LinqToDataSet
{
class Program
{
static void Main(string[] args)
{
string conn = @"server=.;database=Northwind;uid=sa;pwd=Windows2008";
string sqlstr = "select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sqlstr,conn );
DataSet ds = new DataSet();
da.Fill(ds,"Customers");
//****************************************************************
IEnumerable<DataRow> dt = ds.Tables["Customers"].AsEnumerable();
var query = from data in dt
where data.Field<string>("City")=="London"
orderby data["CompanyName"]
select new
{
CustomerID = data.Field<string>("CustomerID"),
CompanyName = data.Field<string>("CompanyName")
};
Console.WriteLine("Northwind数据库：");
foreach(var a in query)
{
Console.WriteLine(\t" +a.CustomerID);
}
//****************************************************************
Console.ReadKey();
}
}
}
3.LingToSQL（使用Linq查询SQL数据库）
using System;
using System.Linq;//一定要记得添加引用
using LinqNorthwind;//(可以用sqlmetal.exe工具直接生成，然后引入。)
namespace LinqToSQL
{
class Program
{
public static string connString = @"server=.;database=Northwind;uid=sa;pwd=Windows2008";
public static NorthwindDataContext db = new NorthwindDataContext(connString);
static void Main(string[] args)
{
Console.WriteLine("Customers matching data:\n");
DoLinqSelect(); //查询原始数据
DoLinqInsert(); //插入新数据
DoLinqSelect();
DoLinqUpdate(); //修改新数据
DoLinqSelect();
DoLinqDelete(); //删除新数据
DoLinqSelect();
Console.ReadKey();//暂停观看结果
}
public static void DoLinqSelect() //查询
{
var customers = (from C in db.Customers select C).Where(c => c.City == "London");
foreach (var cus in customers)
{
Console.WriteLine("CustomerID={0},City={1},CompanyName={2}",
cus.CustomerID, cus.City, );
}
Console.WriteLine("Totals: {0}", customers.Count());
}
public static void DoLinqInsert() //插入
{
Customers newCus = new Customers();
= "AdventureWorks Cafe";
newCus.CustomerID = "ADVCA";
newCus.City = "London";
db.Customers.InsertOnSubmit(newCus);
db.SubmitChanges();//与数据库同步
Console.WriteLine("\nCustomers matching 'ADVCA' already inserted.\n");
}
public static void DoLinqUpdate() //更新
{
Customers newCus = db.Customers.Single(a => a.CustomerID == "ADVCA");
= "GloryKing";
db.SubmitChanges();
Console.WriteLine("\nCustomers matching 'ADVCA' already updated.\n");
}
public static void DoLinqDelete() //删除
{
Customers newCus = db.Customers.Single(c => c.CustomerID == "ADVCA");
db.Customers.DeleteOnSubmit(newCus);
db.SubmitChanges(); //与数据库同步
Console.WriteLine("\nCustomers matching 'ADVCA' already deleted.\n");
}
}
}
---------------------------------------------------------------------------------------------------
运行结果：
Customers matching data:
CustomerID=AROUT,City=London,CompanyName=Around the Horn
CustomerID=BSBEV,City=London,CompanyName=B's Beverages
CustomerID=CONSH,City=London,CompanyName=Consolidated Holdings
CustomerID=EASTC,City=London,CompanyName=Eastern Connection
CustomerID=NORTS,City=London,CompanyName=North/South
CustomerID=SEVES,City=London,CompanyName=Seven Seas Imports
Totals: 6
Customers matching 'ADVCA' already inserted.
CustomerID=ADVCA,City=London,CompanyName=AdventureWorks Cafe
CustomerID=AROUT,City=London,CompanyName=Around the Horn
CustomerID=BSBEV,City=London,CompanyName=B's Beverages
CustomerID=CONSH,City=London,CompanyName=Consolidated Holdings
CustomerID=EASTC,City=London,CompanyName=Eastern Connection
CustomerID=NORTS,City=London,CompanyName=North/South
CustomerID=SEVES,City=London,CompanyName=Seven Seas Imports
Totals: 7
Customers matching 'ADVCA' already updated.
CustomerID=ADVCA,City=London,CompanyName=GloryKing
CustomerID=AROUT,City=London,CompanyName=Around the Horn
CustomerID=BSBEV,City=London,CompanyName=B's Beverages
CustomerID=CONSH,City=London,CompanyName=Consolidated Holdings
CustomerID=EASTC,City=London,CompanyName=Eastern Connection
CustomerID=NORTS,City=London,CompanyName=North/South
CustomerID=SEVES,City=London,CompanyName=Seven Seas Imports
Totals: 7
Customers matching 'ADVCA' already deleted.
CustomerID=AROUT,City=London,CompanyName=Around the Horn
CustomerID=BSBEV,City=London,CompanyName=B's Beverages
CustomerID=CONSH,City=London,CompanyName=Consolidated Holdings
CustomerID=EASTC,City=London,CompanyName=Eastern Connection
CustomerID=NORTS,City=London,CompanyName=North/South
CustomerID=SEVES,City=London,CompanyName=Seven Seas Imports
Totals: 6
---------------------------------------------------------------------------------------------------
SqlMetal.exe使用方法（只适用能添加到SQL数据库中的数据，Access数据库不适用）：
1.可以Visual Studio 2008 命令提示符输入：sqlmetal /? 查看帮助信息
2.生成的文件位于 X:\Program Files\Microsoft Visual Studio 9.0\VC 目录下
3.SqlMetal.exe工具位于 X:\Program Files\Microsoft SDKs\Windows\v6.0A\bin 目录下
4.以下是用sqlmetal工具生成代码的实例：
（1）通过 SqlServer 创建cs代码:
SqlMetal /conn:"server=.;database=Northwind;uid=sa;Password=Windows2008" /timeout:5 /views /functions /sprocs /namespace:LinqNorthwind /context:NorthwindDataContext /code:Northwind.cs
（2）通过 SqlServer 生成中间 dbml 文件:
SqlMetal /conn:"server=.;database=Northwind;uid=sa;Password=Windows2008" /timeout:5 /views /functions /sprocs /namespace:LinqNorthwind /context:NorthwindDataContext /dbml:Northwind.dbml
（3)通过 dbml 生成包含外部映射的代码:
SqlMetal /code:Northwind.cs /map:Northwind.map Northwind.dbml
注意：用此命令时必须要保证 Northwind.dbml 在 X:\Program Files\Microsoft Visual Studio 9.0\VC 目录下
(4)通过 SqlCE sdf 文件生成 dbml:
SqlMetal /dbml:Northwind.dbml Northwind.sdf
注意：用此命令时必须要保证 Northwind.sdf 在 X:\Program Files\Microsoft Visual Studio 9.0\VC 目录下
结语：亲爱的朋友们临近新年了，在这里我预祝你们新年快乐，在新的一年里有更多的收获，感谢你们的支持。
支持，好···
