LINQ 学习(三)
来源:互联网 发布:移动4g网络接入点设置 编辑:程序博客网 时间:2024/05/19 16:35
以一种统一的方式操作各种数据源,减少数据访问的复杂性.類似Hibernate的對象級訪問,不考慮後臺數據庫的類型等等。自動生成各種類型的查詢數據源語言。
查询句法
查询句法是使用标准的LINQ查询运算符来表达查询时一个方便的声明式简化写法。该句法能在代码里表达查询时增进可读性和简洁性,读起来容易,也容易让人写对。Visual Studio 对查询句法提供了完整的智能感应和编译时检查支持。编译器在底层把查询句法的表达式翻译成明确的方法调用代码,代码通过新的扩展方法和Lambda表达式语言特性来实现。
var selectperson = from p in persons where p.age >= 20 select p.username.ToUpper();
foreach(var p in selectperson)
Console.WriteLine(p);
上面的查询句法等价于下面的代码:
查询方法
var selectperson = persons.Where(p=>p.age>=20).Select(p=>p.username.ToUpper());
DataContext
使用DataContext类型把实体类和数据库中的数据进行关联。
强类型DataContext
public partial class NorthwindDataContext : DataContext
{
public Table<Customer> Customers;
public NorthwindDataContext(IDbConnection connection) : base(connection) { }
public NorthwindDataContext(string connection) : base(connection) { }
}
<!-- /* Font Definitions */ @font-face{font-family:SimSun;panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-alt:宋体;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;}@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;mso-font-charset:0;mso-generic-font-family:roman;mso-font-pitch:variable;mso-font-signature:-1610611985 1107304683 0 0 159 0;}@font-face{font-family:ˎ̥;panose-1:0 0 0 0 0 0 0 0 0 0;mso-font-alt:"Times New Roman";mso-font-charset:0;mso-generic-font-family:roman;mso-font-format:other;mso-font-pitch:auto;mso-font-signature:0 0 0 0 0 0;}@font-face{font-family:"/@SimSun";panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{mso-style-unhide:no;mso-style-qformat:yes;mso-style-parent:"";margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-font-family:SimSun;mso-font-kerning:1.0pt;mso-fareast-language:ZH-CN;}.MsoChpDefault{mso-style-type:export-only;mso-default-props:yes;font-size:10.0pt;mso-ansi-font-size:10.0pt;mso-bidi-font-size:10.0pt;mso-ascii-font-family:"Times New Roman";mso-fareast-font-family:SimSun;mso-hansi-font-family:"Times New Roman";mso-font-kerning:0pt;} /* Page Definitions */ @page{mso-page-border-surround-header:no;mso-page-border-surround-footer:no;}@page Section1{size:612.0pt 792.0pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;mso-header-margin:36.0pt;mso-footer-margin:36.0pt;mso-paper-source:0;}div.Section1{page:Section1;}-->强类型数据上下文使代码更简洁:
<!-- /* Font Definitions */ @font-face{font-family:SimSun;panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-alt:宋体;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;}@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;mso-font-charset:0;mso-generic-font-family:roman;mso-font-pitch:variable;mso-font-signature:-1610611985 1107304683 0 0 159 0;}@font-face{font-family:ˎ̥;panose-1:0 0 0 0 0 0 0 0 0 0;mso-font-alt:"Times New Roman";mso-font-charset:0;mso-generic-font-family:roman;mso-font-format:other;mso-font-pitch:auto;mso-font-signature:0 0 0 0 0 0;}@font-face{font-family:"/@SimSun";panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{mso-style-unhide:no;mso-style-qformat:yes;mso-style-parent:"";margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-font-family:SimSun;mso-font-kerning:1.0pt;mso-fareast-language:ZH-CN;}.MsoChpDefault{mso-style-type:export-only;mso-default-props:yes;font-size:10.0pt;mso-ansi-font-size:10.0pt;mso-bidi-font-size:10.0pt;mso-ascii-font-family:"Times New Roman";mso-fareast-font-family:SimSun;mso-hansi-font-family:"Times New Roman";mso-font-kerning:0pt;} /* Page Definitions */ @page{mso-page-border-surround-header:no;mso-page-border-surround-footer:no;}@page Section1{size:612.0pt 792.0pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;mso-header-margin:36.0pt;mso-footer-margin:36.0pt;mso-paper-source:0;}div.Section1{page:Section1;}-->NorthwindDataContext ctx = new NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");
GridView1.DataSource = from c in ctx.Customers where c.CustomerID.StartsWith("A") select new { 顾客ID = c.CustomerID, 顾客名 = c.Name, 城市 = c.City };
GridView1.DataBind();
1.日志功能
<!-- /* Font Definitions */ @font-face{font-family:SimSun;panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-alt:宋体;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;}@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;mso-font-charset:0;mso-generic-font-family:roman;mso-font-pitch:variable;mso-font-signature:-1610611985 1107304683 0 0 159 0;}@font-face{font-family:ˎ̥;panose-1:0 0 0 0 0 0 0 0 0 0;mso-font-alt:"Times New Roman";mso-font-charset:0;mso-generic-font-family:roman;mso-font-format:other;mso-font-pitch:auto;mso-font-signature:0 0 0 0 0 0;}@font-face{font-family:"/@SimSun";panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{mso-style-unhide:no;mso-style-qformat:yes;mso-style-parent:"";margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-font-family:SimSun;mso-font-kerning:1.0pt;mso-fareast-language:ZH-CN;}.MsoChpDefault{mso-style-type:export-only;mso-default-props:yes;font-size:10.0pt;mso-ansi-font-size:10.0pt;mso-bidi-font-size:10.0pt;mso-ascii-font-family:"Times New Roman";mso-fareast-font-family:SimSun;mso-hansi-font-family:"Times New Roman";mso-font-kerning:0pt;} /* Page Definitions */ @page{mso-page-border-surround-header:no;mso-page-border-surround-footer:no;}@page Section1{size:612.0pt 792.0pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;mso-header-margin:36.0pt;mso-footer-margin:36.0pt;mso-paper-source:0;}div.Section1{page:Section1;}-->using System.IO;
NorthwindDataContext ctx = new NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");
StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true); // Append
ctx.Log = sw;
GridView1.DataSource = from c in ctx.Customers where c.CustomerID.StartsWith("A") select new { 顾客ID = c.CustomerID, 顾客名 = c.Name, 城市 = c.City };
GridView1.DataBind();
sw.Close();
2.探究查询
<!-- /* Font Definitions */ @font-face{font-family:SimSun;panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-alt:宋体;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;}@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;mso-font-charset:0;mso-generic-font-family:roman;mso-font-pitch:variable;mso-font-signature:-1610611985 1107304683 0 0 159 0;}@font-face{font-family:ˎ̥;panose-1:0 0 0 0 0 0 0 0 0 0;mso-font-alt:"Times New Roman";mso-font-charset:0;mso-generic-font-family:roman;mso-font-format:other;mso-font-pitch:auto;mso-font-signature:0 0 0 0 0 0;}@font-face{font-family:"/@SimSun";panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{mso-style-unhide:no;mso-style-qformat:yes;mso-style-parent:"";margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-font-family:SimSun;mso-font-kerning:1.0pt;mso-fareast-language:ZH-CN;}.MsoChpDefault{mso-style-type:export-only;mso-default-props:yes;font-size:10.0pt;mso-ansi-font-size:10.0pt;mso-bidi-font-size:10.0pt;mso-ascii-font-family:"Times New Roman";mso-fareast-font-family:SimSun;mso-hansi-font-family:"Times New Roman";mso-font-kerning:0pt;} /* Page Definitions */ @page{mso-page-border-surround-header:no;mso-page-border-surround-footer:no;}@page Section1{size:612.0pt 792.0pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;mso-header-margin:36.0pt;mso-footer-margin:36.0pt;mso-paper-source:0;}div.Section1{page:Section1;}-->using System.Data.Common;
using System.Collections.Generic;
NorthwindDataContext ctx = new NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");
var select = from c in ctx.Customers where c.CustomerID.StartsWith("A") select new { 顾客ID = c.CustomerID, 顾客名 = c.Name, 城市 = c.City };
DbCommand cmd = ctx.GetCommand(select);
Response.Write(cmd.CommandText + "<br/>");
foreach (DbParameter parm in cmd.Parameters)
Response.Write(string.Format("参数名:{0},参数值:{1}<br/>", parm.ParameterName, parm.Value));
Customer customer = ctx.Customers.First();
<!-- /* Font Definitions */ @font-face{font-family:SimSun;panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-alt:宋体;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;}@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;mso-font-charset:0;mso-generic-font-family:roman;mso-font-pitch:variable;mso-font-signature:-1610611985 1107304683 0 0 159 0;}@font-face{font-family:ˎ̥;panose-1:0 0 0 0 0 0 0 0 0 0;mso-font-alt:"Times New Roman";mso-font-charset:0;mso-generic-font-family:roman;mso-font-format:other;mso-font-pitch:auto;mso-font-signature:0 0 0 0 0 0;}@font-face{font-family:"/@SimSun";panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{mso-style-unhide:no;mso-style-qformat:yes;mso-style-parent:"";margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-font-family:SimSun;mso-font-kerning:1.0pt;mso-fareast-language:ZH-CN;}.MsoChpDefault{mso-style-type:export-only;mso-default-props:yes;font-size:10.0pt;mso-ansi-font-size:10.0pt;mso-bidi-font-size:10.0pt;mso-ascii-font-family:"Times New Roman";mso-fareast-font-family:SimSun;mso-hansi-font-family:"Times New Roman";mso-font-kerning:0pt;} /* Page Definitions */ @page{mso-page-border-surround-header:no;mso-page-border-surround-footer:no;}@page Section1{size:612.0pt 792.0pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;mso-header-margin:36.0pt;mso-footer-margin:36.0pt;mso-paper-source:0;}div.Section1{page:Section1;}-->通过DataContext的GetCommand方法获取了查询对应的DbCommand,并且输出了CommandText和所有的DbParameter
3.执行查询
<!-- /* Font Definitions */ @font-face{font-family:SimSun;panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-alt:宋体;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;}@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;mso-font-charset:0;mso-generic-font-family:roman;mso-font-pitch:variable;mso-font-signature:-1610611985 1107304683 0 0 159 0;}@font-face{font-family:ˎ̥;panose-1:0 0 0 0 0 0 0 0 0 0;mso-font-alt:"Times New Roman";mso-font-charset:0;mso-generic-font-family:roman;mso-font-format:other;mso-font-pitch:auto;mso-font-signature:0 0 0 0 0 0;}@font-face{font-family:"/@SimSun";panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{mso-style-unhide:no;mso-style-qformat:yes;mso-style-parent:"";margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-font-family:SimSun;mso-font-kerning:1.0pt;mso-fareast-language:ZH-CN;}.MsoChpDefault{mso-style-type:export-only;mso-default-props:yes;font-size:10.0pt;mso-ansi-font-size:10.0pt;mso-bidi-font-size:10.0pt;mso-ascii-font-family:"Times New Roman";mso-fareast-font-family:SimSun;mso-hansi-font-family:"Times New Roman";mso-font-kerning:0pt;} /* Page Definitions */ @page{mso-page-border-surround-header:no;mso-page-border-surround-footer:no;}@page Section1{size:612.0pt 792.0pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;mso-header-margin:36.0pt;mso-footer-margin:36.0pt;mso-paper-source:0;}div.Section1{page:Section1;}-->虽然Linq to sql能实现90%以上的TSQL功能。但是不可否认,对于复杂的查询,使用TSQL能获得更好的效率
4.创建数据库
<!-- /* Font Definitions */ @font-face{font-family:SimSun;panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-alt:宋体;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;}@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;mso-font-charset:0;mso-generic-font-family:roman;mso-font-pitch:variable;mso-font-signature:-1610611985 1107304683 0 0 159 0;}@font-face{font-family:ˎ̥;panose-1:0 0 0 0 0 0 0 0 0 0;mso-font-alt:"Times New Roman";mso-font-charset:0;mso-generic-font-family:roman;mso-font-format:other;mso-font-pitch:auto;mso-font-signature:0 0 0 0 0 0;}@font-face{font-family:"/@SimSun";panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{mso-style-unhide:no;mso-style-qformat:yes;mso-style-parent:"";margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-font-family:SimSun;mso-font-kerning:1.0pt;mso-fareast-language:ZH-CN;}.MsoChpDefault{mso-style-type:export-only;mso-default-props:yes;font-size:10.0pt;mso-ansi-font-size:10.0pt;mso-bidi-font-size:10.0pt;mso-ascii-font-family:"Times New Roman";mso-fareast-font-family:SimSun;mso-hansi-font-family:"Times New Roman";mso-font-kerning:0pt;} /* Page Definitions */ @page{mso-page-border-surround-header:no;mso-page-border-surround-footer:no;}@page Section1{size:612.0pt 792.0pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;mso-header-margin:36.0pt;mso-footer-margin:36.0pt;mso-paper-source:0;}div.Section1{page:Section1;}-->testContext ctx = new testContext("server=xxx;database=testdb;uid=xxx;pwd=xxx");
ctx.CreateDatabase();
[Table(Name = "test")]
public class test
{
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public int ID { get; set; }
[Column(DbType="varchar(20)")]
public string Name { get; set; }
}
public partial class testContext : DataContext
{
public Table<test> test;
public testContext(string connection) : base(connection) { }
}
5.使用DbDataReader
<!-- /* Font Definitions */ @font-face{font-family:SimSun;panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-alt:宋体;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;}@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;mso-font-charset:0;mso-generic-font-family:roman;mso-font-pitch:variable;mso-font-signature:-1610611985 1107304683 0 0 159 0;}@font-face{font-family:ˎ̥;panose-1:0 0 0 0 0 0 0 0 0 0;mso-font-alt:"Times New Roman";mso-font-charset:0;mso-generic-font-family:roman;mso-font-format:other;mso-font-pitch:auto;mso-font-signature:0 0 0 0 0 0;}@font-face{font-family:"/@SimSun";panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{mso-style-unhide:no;mso-style-qformat:yes;mso-style-parent:"";margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:12.0pt;font-family:"Times New Roman","serif";mso-fareast-font-family:SimSun;mso-font-kerning:1.0pt;mso-fareast-language:ZH-CN;}.MsoChpDefault{mso-style-type:export-only;mso-default-props:yes;font-size:10.0pt;mso-ansi-font-size:10.0pt;mso-bidi-font-size:10.0pt;mso-ascii-font-family:"Times New Roman";mso-fareast-font-family:SimSun;mso-hansi-font-family:"Times New Roman";mso-font-kerning:0pt;} /* Page Definitions */ @page{mso-page-border-surround-header:no;mso-page-border-surround-footer:no;}@page Section1{size:612.0pt 792.0pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;mso-header-margin:36.0pt;mso-footer-margin:36.0pt;mso-paper-source:0;}div.Section1{page:Section1;}-->using System.Data.SqlClient;
var conn = new SqlConnection("server=xxx;database=Northwind;uid=xxx;pwd=xxx");
var ctx = new DataContext(conn);
var cmd = new SqlCommand("select * from customers where CustomerID like 'A%'", conn);
conn.Open();
var reader = cmd.ExecuteReader();
GridView1.DataSource = ctx.Translate<Customer>(reader);
GridView1.DataBind();
conn.Close();
- LINQ 学习(三)
- C# 2008 学习笔记 - LINQ基础(三)- LINQ语法
- LINQ学习(三)数据源LINQ to DataSet
- Linq学习笔记(三)
- LINQ查询表达式--学习linq的资料和笔记(三)
- LINQ学习笔记(三)--LinqDataSource+ListView
- LINQ学习笔记(三)--LinqDataSource+ListView
- LINQ学习笔记三-----基本查询操作
- linQ学习笔记之三高级语句
- Linq 学习(一)
- LINQ 学习(二)
- linq学习(from)
- Linq学习(join)
- LINQ(3):使用LINQPad学习LINQ
- LINQ(3):使用LINQPad学习LINQ
- Linq学习笔记(一) -- 初识Linq
- LinQ&EF任我行(三)--LinQ to SQL
- LINQ学习心得分享------(三)LINQ语法详解2
- Form与Form之间的通信
- 巧用搜狗浏览器的代理服务器加速上网
- 认识程序集:1. 程序集的生成
- 偶要开始写写博了~~
- Socket中的TIME_WAIT状态(转)
- LINQ 学习(三)
- 什么是奇偶校验
- json 时间转换
- 华为管理时间
- http://psing.org photoshop,javascript,ajax,web design,web develop,tutorial
- Windows server2003下安装Informix数据库
- “上海式贫困”与“北京式富裕”
- 有关调音台的专业英文词汇
- VC对话创业者