linq主要有3種，linq to sql,linq to XML,Linq to Object

linq to sql。

這里沒有通過相應的類，生成相應的數據庫中的表。沒有用流行的編碼優先。

只是為了自己的練習。

通過生成的linq?類，把數據庫中的表，存儲過程，視圖等映射出來。其中數據上下文是鏈接實體類和數據庫的橋梁，這是非常重要的。

現在開始Linq to?sql之旅。數據庫中的代碼如下所示：

--查詢數據庫中是否含有數據庫DB_Student,有則刪除
if exists(select 1 from sys.sysdatabases where [name]='DB_Student')
?? ?drop database DB_Student
go
--創建學生是數據庫
create database DB_Student
go
--查看是否存在學生表，有則刪除
use DB_Student
if exists(select 1 from sys.sysobjects where [name]='T_Student')
?? ?drop table T_Student
go
--創建學生表
use DB_Student
create table T_Student
(
?? ?stuNumber varchar(8) not null,
?? ?stuName varchar(20) not null,
?? ?stuAge int not null,
?? ?stuSex char(2) not null
)
go
--添加主鍵約束
use DB_Student
alter table T_Student
add constraint PK_T_Student_stuNumber primary key(stuNumber)
go

--查看是否存在教師表，有則刪除
use DB_Student
if exists(select 1 from sys.sysobjects where [name]='T_Teacher')
?? ?drop table T_Teacher
go
--創建教師表
use DB_Student
create table T_Teacher
(
?? ?teacherNumber varchar(6) not null,
?? ?teacherName varchar(20) not null
)
go
--為教師表創建主鍵約束
use DB_Student
alter table T_Teacher
add constraint PK_T_Teacher_teacherNumber primary key(teacherNumber)
go
--查看是否存在課程表，有則刪除
use DB_Student
if exists(select 1 from sys.sysobjects where [name]='T_Cource')
?? ?drop table T_Cource
go
--創建課程表
use DB_Student
create table T_Cource
(
?? ?courceNumber varchar(4) not null,
?? ?courceName varchar(20) not null,
?? ?teacherNumber varchar(6) not null
)
go

--為課程表添加主鍵約束和外鍵約束
use DB_Student
alter table T_Cource
?? ?add constraint PK_T_Cource_courceNumber primary key(courceNumber),
?? ??? ?constraint FK_T_Cource_T_Teacher_teacherNumber foreign key(teacherNumber) references ?? ?T_Teacher(teacherNumber)
go?? ??? ?

--查看是否存在成績表，有則刪除
use DB_Student
if exists(select 1 from sys.sysobjects where [name]='T_Score')
?? ?drop table T_Score
go

--創建成績表
use DB_Student
create table T_Score
(
?? ?stuNumber varchar(8) not null,
?? ?courceNumber varchar(4) not null,
?? ?score float
)
go

--為成績表添加主鍵約束和外鍵約束
use DB_Student
alter table T_Score
?? ?add constraint PK_T_Score_stuNumber_courceNumber primary key(stuNumber,courceNumber),
?? ??? ?constraint FK_T_Score_T_Student_stuNumber foreign key(stuNumber) references T_Student(stuNumber),
?? ??? ?constraint FK_T_Score_T_Cource_courceNumber foreign key(courceNumber) references T_Cource(courceNumber)
go?? ?

--為學生表插入數據
insert into T_Student
?? ?select '2091727','李陽','18','男' union
?? ?select '2091728','李芳','19','女' union
?? ?select '2091729','李丹','18','男' union
?? ?select '2091721','黃陽','17','女' union
?? ?select '2091722','黃渤','18','男' union
?? ?select '2091723','黃波','20','男' union
?? ?select '2091724','何潔','19','女' union
?? ?select '2091725','何丹','17','男' union
?? ?select '2091726','何宇','19','女'
go

--為教師表插入數據
insert into T_Teacher
?? ?select '01','李剛' union
?? ?select '02','李雙江' union
?? ?select '03','鄧楠'
go
--為課程表插入數據
insert into T_Cource
?? ?select '001','微機原理','01' union
?? ?select '002','西方音樂歷史','02' union
?? ?select '003','會計學','03'
go
--為成績表插入數據?? ?
insert into T_Score
?? ?select '2091721','001','92' union
?? ?select '2091721','002','59' union
?? ?select '2091721','003','81' union
?? ?select '2091722','001','58' union
?? ?select '2091722','002','66' union
?? ?select '2091722','003','83' union
?? ?select '2091723','001','82' union
?? ?select '2091723','002','64' union
?? ?select '2091723','003','71' union
?? ?select '2091724','001','77' union
?? ?select '2091724','002','53' union
?? ?select '2091724','003','56' union
?? ?select '2091725','001','55' union
?? ?select '2091725','002','57' union
?? ?select '2091725','003','71' union
?? ?select '2091726','001','90' union
?? ?select '2091726','002','88' union
?? ?select '2091726','003','82' union
?? ?select '2091727','001','57' union
?? ?select '2091727','002','83' union
?? ?select '2091727','003','59' union
?? ?select '2091728','001','54' union
?? ?select '2091728','002','93' union
?? ?select '2091728','003','48' union
?? ?select '2091729','001','61' union
?? ?select '2091729','002','58' union
?? ?select '2091729','003','93'
go
?? ?
?? ?
select *
from T_Cource
select *
from T_Score
select *
from T_Student
select *
from T_Teacher

?

通過數據庫生成數據上下文開始在VS里編碼：

public partial class Practice1 : System.Web.UI.Page
??? {
??????? DB_StudentDataContext ctx = new DB_StudentDataContext();
??????? protected void Page_Load(object sender, EventArgs e)
??????? {
??????????? dataBind();
??????? }

??????? void dataBind()
??????? {
??????????? //獲取數據庫中的T_Student表數據
??????????? var list = ctx.T_Student.ToList();
??????????? GridView1.DataSource = list;
??????????? GridView1.DataBind();
??????? }
??? }

這就是后臺代碼，前臺顯示如下：

看只需要var list = ctx.T_Student.ToList();這樣的一句代碼，就能夠綁定數據，和以前ADO.NET讀取數據填充到dataset數據集相比省了很多代碼，看是不是很方便。

開始where 練習

? /**************where 練習*******************/
??????????? //獲取數據庫中的T_Student表數據
??????????? var list = ctx.T_Student.Where(s => s.stuName == "黃陽");
??????????? //或者
??????????? var list1 = from s in ctx.T_Student
??????????????????????? where s.stuName == "黃陽"
??????????????????????? select s;
??????????? //查詢名字為"黃陽"且性別為女的同學
??????????? var list3 = ctx.T_Student.Where(s => (s.stuName == "黃陽")&&(s.stuSex=="女"));
??????????? var list4 = from s in ctx.T_Student
??????????????????????? where s.stuName == "黃陽"
??????????????????????????????? && s.stuSex == "女"
??????????????????????? select s;

??????????? var list5 = ctx.T_Student.Where(s => s.stuName == "黃陽").Where(t => t.stuSex == "女");
??????????????? //上邊三個都是一樣的結果

???? 　　//查詢表中的第一條記錄
??????????? var list6 = ctx.T_Student.First();
??????????? //查詢名字為“黃陽”的第一條記錄
??????????? var list7 = ctx.T_Student.First(s=>s.stuName=="黃陽");

Linq無聊練習系列1--where練習