Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)归海一刀published in(发表于) 2014/1/30 1:13:02 第二篇：使用Visual，Studio，2008进行数据层开发_[Asp.Net教程]
LINQ是Visual Studio 2008在重要内容之一，本节也主要是介绍如何利用LINQ来构建我们的数据层。在本篇文章中用到了C#3.0的新特性，不了解的朋友可以到我的另一个系列《C#3.0 为我们带来什么》中查看，本篇中不再做过多介绍。在数据库中建立City、Module、Province、User、UserGroup 5个表，如图1。
图1
数据库脚本Codeif 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 , '济南 ' , 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 , '用户管理 ' , 'ManageModule ' ) INSERT [Modules] ( [ModuleID] , [Name] , [Comments] , [Code] ) VALUES ( 2 , '用户组管理22 ' , ' ' , 'ModulesManager ' )
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('[sysdiagrams]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [sysdiagrams] CREATE TABLE [sysdiagrams] ( [name] [nvarchar] (128) NOT NULL , [principal_id] [int] NOT NULL , [diagram_id] [int] IDENTITY (1, 1) NOT NULL , [version] [int] NULL , [definition] [image] NULL ) ALTER TABLE [sysdiagrams] WITH NOCHECK ADD CONSTRAINT [PK_sysdiagrams] PRIMARY KEY NONCLUSTERED ( [diagram_id] ) CREATE UNIQUE INDEX [UK_principal_name] ON [sysdiagrams] ( principal_id ) CREATE UNIQUE INDEX [UK_principal_name] ON [sysdiagrams] ( name )
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 , '普通用户 ' , '普通用户 ' , 1 , 'pt ' ) INSERT [UserGroups] ( [GroupID] , [Name] , [Comments] , [ModuleID] , [Code] ) VALUES ( 2 , '管理员1 ' , '管理员 ' , 1 , 'admin ' )
SET IDENTITY_INSERT [UserGroups] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[UserGroupsModulesRelations]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [UserGroupsModulesRelations] CREATE TABLE [UserGroupsModulesRelations] ( [GroupID] [int] NOT NULL , [ModuleID] [int] NOT NULL ) ALTER TABLE [UserGroupsModulesRelations] WITH NOCHECK ADD CONSTRAINT [PK_UserGroupsModulesRelations] PRIMARY KEY NONCLUSTERED ( [GroupID] ) CREATE UNIQUE CLUSTERED INDEX [PK_UserGroupsModulesRelation] ON [UserGroupsModulesRelations] ( ModuleID )
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 ' , '我就读的第一所学校的' , ' ' , '1982010' , 0 ) INSERT [Users] ( [UserID] , [UserName] , [Password] , [NickName] , [EMail] , [Question] , [Answer] , [Birthday] , [Sex] ) VALUES ( 4 , 'tianyamoon ' , '19820603 ' , ' ' , 'tiger133@163.com ' , '我就读的第一所学校的' , ' ' , '1982010' , 0 )
SET IDENTITY_INSERT [Users] OFF
打开上次创建的项目，按下Ctrl+W再按下Ctrl+L使用组合快捷键打开Server Explorer面板。 图2
点击黄色区域，添加数据链接，弹出如图3所示窗口.
图3
输入服务器名称及用户名、密码，并选择执行脚本的数据库，确定完成。通过图4中展开的菜单，建立数据库连接。将上面的SQL脚本粘贴进打开的编辑器，按下F5执行向数据表插入演示数据。
图4
选中我们的项目使用快捷键Ctrl+Shift+A 选中Data，选中Linq to Sql Classes类型的文件，输入名称SecurityData.dbml按下确定。
将图5中的5个表拖放到图6中的黄色位置。 图5
图6
保存文件，这样我们的数据库映射就建好了。
好，我们介绍下如何使用这个映射。创建一个WebForm，并在WebForm的OnLoad函数中输入下列代码来测试。
1 SecurityDataDataContext db = new SecurityDataDataContext();2 var a = db.Users;3 var b = a.GetNewBindingList();在第一行设置断点。将刚创建的webform设为起始叶，按F5调试程序。打开Sql Server Profiler 连接到我们的数据库并开始跟踪。按下F10程序运行完第一行，通过Sql Server Profiler可以发现此时并未执行任何sql操作。F10继续运行第二行依然没有执行，继续。可以发现此时我们的程序建立了数据库连接，并且执行了下列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]说明LINQ通过数据映射生成了SQL，并返回了数据。好，我们看看它返回给我们什么。
图7
通过快速监视可以看到，返回的是一个User对象的集合。下面我们来看下如何插入数据，通过初始化器创建User对象.
Users u = new Users(){UserName = "tianyamoon",NickName = "岳亮",Password = "aaaaaa",EMail = "yueliang0603@163.com",Birthday = "19820101",Sex = true }; 通过db.Users.InsertOnSubmit(u);向集合插入一个对象。然后再执行submit操作db.SubmitChanges();。大家可以通过上面的方法跟踪SQL执行的情况。那么LINQ是如何执行Updata操作的呢，其实这个更简单。只要我们修改了User对象，只要执行db.SubmitChanges();函数就可以将新的数据更新入数据库了。
还有一个问题，我们的连接字符串是如何处理的呢？那么我就来揭示这个小秘密吧，打开项目的 Properties文件夹，秘密就在Settings.settings里。
赞