LINQ是Visual Studio 2008在重要內(nèi)容之一，本節(jié)也主要是介紹如何利用LINQ來(lái)構(gòu)建我們的數(shù)據(jù)層。
在本篇文章中用到了C#3.0的新特性，不了解的朋友可以到我的另一個(gè)系列《 C#3.0 為我們帶來(lái)什么 》中查看，本篇中不再做過(guò)多介紹。
在數(shù)據(jù)庫(kù)中建立City、Module、Province、User、UserGroup 5個(gè)表，如圖1。
數(shù)據(jù)庫(kù)腳本
打開(kāi)上次創(chuàng)建的項(xiàng)目，按下Ctrl+W再按下Ctrl+L使用組合快捷鍵打開(kāi)Server Explorer面板。
通過(guò)圖4中展開(kāi)的菜單，建立數(shù)據(jù)庫(kù)連接。將上面的SQL腳本粘貼進(jìn)打開(kāi)的編輯器，按下F5執(zhí)行向數(shù)據(jù)表插入演示數(shù)據(jù)。
好，我們介紹下如何使用這個(gè)映射。創(chuàng)建一個(gè)WebForm，并在WebForm的OnLoad函數(shù)中輸入下列代碼來(lái)測(cè)試。
1
SecurityDataDataContext db =
new SecurityDataDataContext();
2
var a = db.Users;
3
var b = a.GetNewBindingList();
在第一行設(shè)置斷點(diǎn)。將剛創(chuàng)建的webform設(shè)為起始葉，按F5調(diào)試程序。打開(kāi)Sql Server Profiler 連接到我們的數(shù)據(jù)庫(kù)并開(kāi)始跟蹤。按下F10程序運(yùn)行完第一行，通過(guò)Sql Server Profiler可以發(fā)現(xiàn)此時(shí)并未執(zhí)行任何sql操作。F10繼續(xù)運(yùn)行第二行依然沒(méi)有執(zhí)行，繼續(xù)。可以發(fā)現(xiàn)此時(shí)我們的程序建立了數(shù)據(jù)庫(kù)連接，并且執(zhí)行了下列sql。
SELECT
[t0].[UserID], [t0].[UserName], [t0].[Password], [t0].[NickName], [t0].[GroupID], [t0].[ProvinceID], [t0].[CityID], [t0].[EMail], [t0].[Question], [t0].[Answer], [t0].[Birthday], [t0].[RealName], [t0].[Sex]
FROM
[dbo].[Users]
AS
[t0]
說(shuō)明LINQ通過(guò)數(shù)據(jù)映射生成了SQL，并返回了數(shù)據(jù)。好，我們看看它返回給我們什么。
下面我們來(lái)看下如何插入數(shù)據(jù)，通過(guò) 初始化器 創(chuàng)建User對(duì)象.
Users u =
new Users()
{
UserName =
"tianyamoon",
NickName =
"岳亮",
Password =
"aaaaaa",
EMail =
"yueliang0603@163.com",
Birthday =
"19820101",
Sex =
true
};
通過(guò)db.Users.InsertOnSubmit(u);向集合插入一個(gè)對(duì)象。然后再執(zhí)行submit操作db.SubmitChanges();。
大家可以通過(guò)上面的方法跟蹤SQL執(zhí)行的情況。
那么LINQ是如何執(zhí)行Updata操作的呢，其實(shí)這個(gè)更簡(jiǎn)單。只要我們修改了User對(duì)象，只要執(zhí)行db.SubmitChanges();函數(shù)就可以將新的數(shù)據(jù)更新入數(shù)據(jù)庫(kù)了。
還有一個(gè)問(wèn)題，我們的連接字符串是如何處理的呢？那么我就來(lái)揭示這個(gè)小秘密吧，打開(kāi)項(xiàng)目的 Properties文件夾，秘密就在Settings.settings里。
在本篇文章中用到了C#3.0的新特性，不了解的朋友可以到我的另一個(gè)系列《 C#3.0 為我們帶來(lái)什么 》中查看，本篇中不再做過(guò)多介紹。
在數(shù)據(jù)庫(kù)中建立City、Module、Province、User、UserGroup 5個(gè)表，如圖1。
圖1
數(shù)據(jù)庫(kù)腳本
CODE:
if exists (select * from sysobjects where id = OBJECT_ID('[Citys]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Citys]
CREATE TABLE [Citys] ( [CityID] [int] IDENTITY (1, 1) NOT NULL , [Name] [nchar] (10) NULL , [ProvinceID] [int] NULL , [Code] [nchar] (20) NOT NULL )
ALTER TABLE [Citys] WITH NOCHECK ADD CONSTRAINT [PK_Citys] PRIMARY KEY NONCLUSTERED ( [CityID] )
SET IDENTITY_INSERT [Citys] ON
INSERT [Citys] ( [CityID] , [Name] , [ProvinceID] , [Code] ) VALUES ( 6 , '濟(jì)南 ' , 1 , 'jinan ' )
INSERT [Citys] ( [CityID] , [Name] , [ProvinceID] , [Code] ) VALUES ( 7 , '青島 ' , 1 , 'qingdao ' )
INSERT [Citys] ( [CityID] , [Name] , [ProvinceID] , [Code] ) VALUES ( 8 , '泰安 ' , 1 , 'taian ' )
SET IDENTITY_INSERT [Citys] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[Modules]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Modules]
CREATE TABLE [Modules] ( [ModuleID] [int] IDENTITY (1, 1) NOT NULL , [Name] [nchar] (20) NULL , [Comments] [nchar] (50) NULL , [Code] [nchar] (20) NOT NULL )
ALTER TABLE [Modules] WITH NOCHECK ADD CONSTRAINT [PK_Modules] PRIMARY KEY NONCLUSTERED ( [ModuleID] )
SET IDENTITY_INSERT [Modules] ON
INSERT [Modules] ( [ModuleID] , [Name] , [Code] ) VALUES ( 1 , '用戶(hù)管理 ' , 'ManageModule ' )
SET IDENTITY_INSERT [Modules] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[Provinces]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Provinces]
CREATE TABLE [Provinces] ( [ProvinceID] [int] IDENTITY (1, 1) NOT NULL , [Name] [nchar] (10) NULL , [Code] [nchar] (20) NOT NULL )
ALTER TABLE [Provinces] WITH NOCHECK ADD CONSTRAINT [PK_Provinces] PRIMARY KEY NONCLUSTERED ( [ProvinceID] )
SET IDENTITY_INSERT [Provinces] ON
INSERT [Provinces] ( [ProvinceID] , [Name] , [Code] ) VALUES ( 1 , '山東 ' , 'shandong ' )
INSERT [Provinces] ( [ProvinceID] , [Name] , [Code] ) VALUES ( 2 , '北京 ' , 'beijing ' )
INSERT [Provinces] ( [ProvinceID] , [Name] , [Code] ) VALUES ( 3 , '上海 ' , 'shanghai ' )
SET IDENTITY_INSERT [Provinces] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[UserGroups]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [UserGroups]
CREATE TABLE [UserGroups] ( [GroupID] [int] IDENTITY (1, 1) NOT NULL , [Name] [nchar] (10) NULL , [Comments] [nchar] (100) NULL , [ModuleID] [int] NULL , [Code] [nchar] (20) NULL )
ALTER TABLE [UserGroups] WITH NOCHECK ADD CONSTRAINT [PK_UserGroups] PRIMARY KEY NONCLUSTERED ( [GroupID] )
SET IDENTITY_INSERT [UserGroups] ON
INSERT [UserGroups] ( [GroupID] , [Name] , [Comments] , [ModuleID] , [Code] ) VALUES ( 1 , '普通用戶(hù) ' , '普通用戶(hù) ' , 1 , 'pt ' )
INSERT [UserGroups] ( [GroupID] , [Name] , [Comments] , [ModuleID] , [Code] ) VALUES ( 2 , '管理員 ' , '管理員 ' , 1 , 'admin ' )
SET IDENTITY_INSERT [UserGroups] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[Users]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Users]
CREATE TABLE [Users] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [UserName] [nchar] (50) NULL , [Password] [nchar] (50) NULL , [NickName] [nchar] (50) NULL , [GroupID] [int] NULL , [ProvinceID] [int] NULL , [CityID] [int] NULL , [EMail] [nchar] (50) NULL , [Question] [nchar] (20) NULL , [Answer] [nchar] (20) NULL , [Birthday] [nchar] (8) NULL , [RealName] [nchar] (10) NULL , [Sex] [bit] NULL )
ALTER TABLE [Users] WITH NOCHECK ADD CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED ( [UserID] )
SET IDENTITY_INSERT [Users] ON
INSERT [Users] ( [UserID] , [UserName] , [Password] , [GroupID] , [ProvinceID] , [CityID] ) VALUES ( 2 , 'admin ' , 'admin ' , 1 , 1 , 6 )
INSERT [Users] ( [UserID] , [UserName] , [Password] , [NickName] , [EMail] , [Question] , [Answer] , [Birthday] , [Sex] ) VALUES ( 3 , 'tianyamoon ' , '19820603 ' , '岳亮 ' , 'tiger133@163.com ' , '我就讀的第一所學(xué)校的' , ' ' , '1982010' , 0 )
INSERT [Users] ( [UserID] , [UserName] , [Password] , [NickName] , [EMail] , [Question] , [Answer] , [Birthday] , [Sex] ) VALUES ( 4 , 'tianyamoon ' , '19820603 ' , ' ' , 'tiger133@163.com ' , '我就讀的第一所學(xué)校的' , ' ' , '1982010' , 0 )
SET IDENTITY_INSERT [Users] OFF
CREATE TABLE [Citys] ( [CityID] [int] IDENTITY (1, 1) NOT NULL , [Name] [nchar] (10) NULL , [ProvinceID] [int] NULL , [Code] [nchar] (20) NOT NULL )
ALTER TABLE [Citys] WITH NOCHECK ADD CONSTRAINT [PK_Citys] PRIMARY KEY NONCLUSTERED ( [CityID] )
SET IDENTITY_INSERT [Citys] ON
INSERT [Citys] ( [CityID] , [Name] , [ProvinceID] , [Code] ) VALUES ( 6 , '濟(jì)南 ' , 1 , 'jinan ' )
INSERT [Citys] ( [CityID] , [Name] , [ProvinceID] , [Code] ) VALUES ( 7 , '青島 ' , 1 , 'qingdao ' )
INSERT [Citys] ( [CityID] , [Name] , [ProvinceID] , [Code] ) VALUES ( 8 , '泰安 ' , 1 , 'taian ' )
SET IDENTITY_INSERT [Citys] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[Modules]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Modules]
CREATE TABLE [Modules] ( [ModuleID] [int] IDENTITY (1, 1) NOT NULL , [Name] [nchar] (20) NULL , [Comments] [nchar] (50) NULL , [Code] [nchar] (20) NOT NULL )
ALTER TABLE [Modules] WITH NOCHECK ADD CONSTRAINT [PK_Modules] PRIMARY KEY NONCLUSTERED ( [ModuleID] )
SET IDENTITY_INSERT [Modules] ON
INSERT [Modules] ( [ModuleID] , [Name] , [Code] ) VALUES ( 1 , '用戶(hù)管理 ' , 'ManageModule ' )
SET IDENTITY_INSERT [Modules] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[Provinces]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Provinces]
CREATE TABLE [Provinces] ( [ProvinceID] [int] IDENTITY (1, 1) NOT NULL , [Name] [nchar] (10) NULL , [Code] [nchar] (20) NOT NULL )
ALTER TABLE [Provinces] WITH NOCHECK ADD CONSTRAINT [PK_Provinces] PRIMARY KEY NONCLUSTERED ( [ProvinceID] )
SET IDENTITY_INSERT [Provinces] ON
INSERT [Provinces] ( [ProvinceID] , [Name] , [Code] ) VALUES ( 1 , '山東 ' , 'shandong ' )
INSERT [Provinces] ( [ProvinceID] , [Name] , [Code] ) VALUES ( 2 , '北京 ' , 'beijing ' )
INSERT [Provinces] ( [ProvinceID] , [Name] , [Code] ) VALUES ( 3 , '上海 ' , 'shanghai ' )
SET IDENTITY_INSERT [Provinces] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[UserGroups]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [UserGroups]
CREATE TABLE [UserGroups] ( [GroupID] [int] IDENTITY (1, 1) NOT NULL , [Name] [nchar] (10) NULL , [Comments] [nchar] (100) NULL , [ModuleID] [int] NULL , [Code] [nchar] (20) NULL )
ALTER TABLE [UserGroups] WITH NOCHECK ADD CONSTRAINT [PK_UserGroups] PRIMARY KEY NONCLUSTERED ( [GroupID] )
SET IDENTITY_INSERT [UserGroups] ON
INSERT [UserGroups] ( [GroupID] , [Name] , [Comments] , [ModuleID] , [Code] ) VALUES ( 1 , '普通用戶(hù) ' , '普通用戶(hù) ' , 1 , 'pt ' )
INSERT [UserGroups] ( [GroupID] , [Name] , [Comments] , [ModuleID] , [Code] ) VALUES ( 2 , '管理員 ' , '管理員 ' , 1 , 'admin ' )
SET IDENTITY_INSERT [UserGroups] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[Users]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Users]
CREATE TABLE [Users] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [UserName] [nchar] (50) NULL , [Password] [nchar] (50) NULL , [NickName] [nchar] (50) NULL , [GroupID] [int] NULL , [ProvinceID] [int] NULL , [CityID] [int] NULL , [EMail] [nchar] (50) NULL , [Question] [nchar] (20) NULL , [Answer] [nchar] (20) NULL , [Birthday] [nchar] (8) NULL , [RealName] [nchar] (10) NULL , [Sex] [bit] NULL )
ALTER TABLE [Users] WITH NOCHECK ADD CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED ( [UserID] )
SET IDENTITY_INSERT [Users] ON
INSERT [Users] ( [UserID] , [UserName] , [Password] , [GroupID] , [ProvinceID] , [CityID] ) VALUES ( 2 , 'admin ' , 'admin ' , 1 , 1 , 6 )
INSERT [Users] ( [UserID] , [UserName] , [Password] , [NickName] , [EMail] , [Question] , [Answer] , [Birthday] , [Sex] ) VALUES ( 3 , 'tianyamoon ' , '19820603 ' , '岳亮 ' , 'tiger133@163.com ' , '我就讀的第一所學(xué)校的' , ' ' , '1982010' , 0 )
INSERT [Users] ( [UserID] , [UserName] , [Password] , [NickName] , [EMail] , [Question] , [Answer] , [Birthday] , [Sex] ) VALUES ( 4 , 'tianyamoon ' , '19820603 ' , ' ' , 'tiger133@163.com ' , '我就讀的第一所學(xué)校的' , ' ' , '1982010' , 0 )
SET IDENTITY_INSERT [Users] OFF
打開(kāi)上次創(chuàng)建的項(xiàng)目，按下Ctrl+W再按下Ctrl+L使用組合快捷鍵打開(kāi)Server Explorer面板。
圖2
圖3
通過(guò)圖4中展開(kāi)的菜單，建立數(shù)據(jù)庫(kù)連接。將上面的SQL腳本粘貼進(jìn)打開(kāi)的編輯器，按下F5執(zhí)行向數(shù)據(jù)表插入演示數(shù)據(jù)。
圖4
圖4
圖5
圖6
好，我們介紹下如何使用這個(gè)映射。創(chuàng)建一個(gè)WebForm，并在WebForm的OnLoad函數(shù)中輸入下列代碼來(lái)測(cè)試。
1
SecurityDataDataContext db =
new SecurityDataDataContext();
2
var a = db.Users;
3
var b = a.GetNewBindingList();
在第一行設(shè)置斷點(diǎn)。將剛創(chuàng)建的webform設(shè)為起始葉，按F5調(diào)試程序。打開(kāi)Sql Server Profiler 連接到我們的數(shù)據(jù)庫(kù)并開(kāi)始跟蹤。按下F10程序運(yùn)行完第一行，通過(guò)Sql Server Profiler可以發(fā)現(xiàn)此時(shí)并未執(zhí)行任何sql操作。F10繼續(xù)運(yùn)行第二行依然沒(méi)有執(zhí)行，繼續(xù)。可以發(fā)現(xiàn)此時(shí)我們的程序建立了數(shù)據(jù)庫(kù)連接，并且執(zhí)行了下列sql。
SELECT
[t0].[UserID], [t0].[UserName], [t0].[Password], [t0].[NickName], [t0].[GroupID], [t0].[ProvinceID], [t0].[CityID], [t0].[EMail], [t0].[Question], [t0].[Answer], [t0].[Birthday], [t0].[RealName], [t0].[Sex]
FROM
[dbo].[Users]
AS
[t0]
說(shuō)明LINQ通過(guò)數(shù)據(jù)映射生成了SQL，并返回了數(shù)據(jù)。好，我們看看它返回給我們什么。
圖7
下面我們來(lái)看下如何插入數(shù)據(jù)，通過(guò) 初始化器 創(chuàng)建User對(duì)象.
Users u =
new Users()
{
UserName =
"tianyamoon",
NickName =
"岳亮",
Password =
"aaaaaa",
EMail =
"yueliang0603@163.com",
Birthday =
"19820101",
Sex =
true
};
通過(guò)db.Users.InsertOnSubmit(u);向集合插入一個(gè)對(duì)象。然后再執(zhí)行submit操作db.SubmitChanges();。
大家可以通過(guò)上面的方法跟蹤SQL執(zhí)行的情況。
那么LINQ是如何執(zhí)行Updata操作的呢，其實(shí)這個(gè)更簡(jiǎn)單。只要我們修改了User對(duì)象，只要執(zhí)行db.SubmitChanges();函數(shù)就可以將新的數(shù)據(jù)更新入數(shù)據(jù)庫(kù)了。
還有一個(gè)問(wèn)題，我們的連接字符串是如何處理的呢？那么我就來(lái)揭示這個(gè)小秘密吧，打開(kāi)項(xiàng)目的 Properties文件夾，秘密就在Settings.settings里。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
