作者:未知 请作者速与本人联系
数据表定义了如何在数据库中存储数据,但没有说明如何存取数据。我们还需要了解读写记录以便从表中再次调用选定行和列的详细信息。开发人员通常会在其代码中编写一些特殊的查询语句,用于读写数据。这不仅会导致效率低下,还会带来安全性问题。在本应用中,所有数据存取工作都将通过 SQL Server 存储过程(stored procedures,有时称作“stored procs”或“sprocs”)来处理。使用存储过程可以提高解决方案的性能并使之更安全。此外,使用存储过程可以增加数据层的抽象级别,从而保护解决方案的其他部分不受小的数据布局和格式变化带来的影响。这样可使您的解决方案更可靠,更易于维护。
为什么不使用特殊的查询语句
我们经常会看到如下所示的文章和代码示例:
<ccid_nobr><table cellspacing="0" bordercolordark="#ffffff" cellpadding="2" width="400" align="center" bordercolorlight="black" border="1"><tbody><tr><td class="code" style="FONT-SIZE: 9pt" bgcolor="#e6e6e6"><pre><ccid_code>Private Function GetSomeData(ByVal ID As Integer) As SqlDataReader
Dim strSQL As String
strSQL = "SELECT * FROM MyTable WHERE ID=" & ID.ToString()
cd = New SqlCommand
With cd
.CommandText = strSQL
.CommandType = CommandType.Text
.Connection = cn
.Connection.Open()
Return .ExecuteReader(CommandBehavior.CloseConnection)
End With
End Function</ccid_code></pre></td></tr></tbody></table></ccid_nobr>
上述代码不符合要求的原因有以下几个。首先,如果将 SQL 查询语句嵌套在代码中,那么只要数据层发生任何变化,都必须编辑并重新编译代码层。这样就会带来诸多不便。还可能会导致其他错误,而且通常会造成数据服务和代码之间的混乱。
其次,如果使用不经过输入验证的字符串连接 ("...WHERE ID=" & ID.ToString()),将可能使您的应用程序暴露在黑客的攻击之下。更重要的是,这样就会为恶意用户提供了在您的代码中添加其他 SQL 关键字的机会。例如,根据您的输入模式,恶意用户不仅可以输入 13 或 21 作为有效的表 ID,还可以输入 13; DELETE FROM USERS 或其他可能会带来危害的语句。完善的输入验证可以保护您的系统免受大多数 SQL 插入代码的攻击,所以最好将所有内置的 SQL 语句完全删除,使攻击者很难滥用您的应用程序数据。
最后,内置 SQL 语句的执行速度要比存储过程慢得多。创建存储过程并将其存储到数据库中时,SQL Server 会对其文本进行评估并以优化的形式进行存储,从而使之更容易在运行时为 SQL Server 所用。如果使用内置的特殊查询语句,就必须在每次运行该代码之前进行这种评估。对于那些供大量用户使用的应用程序而言,每分钟就可能需要对同一查询语句进行数百次评估。
相反,存储过程可以保持代码的简洁明了,可以提供额外的安全保护,并能提高解决方案的性能。这些都是摒弃内置查询语句而使用存储过程的原因。
将存储过程添加到 Visual Studio .NET 数据库项目中
使用 Visual Studio .NET 2003 创建存储过程非常简单。首先,您需要打开一个数据库项目。这一操作已在《使用VS.NET2003创建数据库图》中完成。然后,您可以使用代码模板创建存储过程,也可以针对 Server Explorer(服务器资源管理器)窗口中连接的数据库,使用 Visual Studio .NET 2003 直接编辑新的存储过程。本文重点介绍如何针对连接的数据库服务器直接编辑存储过程。稍后会介绍如何为以后的远程服务器安装生成所有结果脚本。
介绍使用 Visual Studio .NET 2003 编写存储过程的机制之前,还要重点强调一下与创建可靠的存储过程相关的几个一般问题。首先,最好将创建和执行存储过程的整个过程看作是多层应用程序模型的一个成熟成员。存储过程提供了一种对您的数据存取进行编程的方法。这样,您可以更好地控制整个解决方案并提高其效率。也就是说,应将存储过程集合看作是应用程序中一个独立的层。优秀的数据存取策略应允许存储过程作为独立的组件而存在。也就是说,存储过程层中需要具备安全性、错误处理以及其他构成优秀组件层的详细内容。更重要的是,应像在其他高级编程环境中那样访问 T-SQL 语言,而不是仅仅将其作为一种生成数据库查询的方式。
注意:现在,我怀疑有些读者可能在想他们并不打算对 SQL Server 进行编程,或者认为这项工作最好留给那些 DBA 们来完成。虽然具备数据库管理员经验会有所帮助,但并一定非要成为火箭科学家(这里指技艺高超的编程专家)才能很好地完成 SQL Server 编程工作。像其他语言一样,这种语言也需要花费一定的时间并通过一定的实践才能熟练掌握,在这一点上它与其他语言并没有太大的不同。如果您能够在 Microsoft Visual Basic? .NET 中编程,也就能够在 T-SQL 中编程。
使用 Visual Studio .NET 添加存储过程
下面详细介绍如何在 Visual Studio .NET 2003 中将存储过程添加到现有 SQL Server 数据库中。您需要使用服务器资源管理器打开一个新的存储过程模板,进行编辑,然后再将其保存到数据库中。下面是分步实现这一过程的示例:
·打开 Visual Studio .NET,然后打开一个现有的数据库项目(如本文前面所启动的项目)或启动一个新项目。 ·在 Server Explorer(服务器资源管理器)中,展开 Data Connections(数据连接)树,找到您要使用的数据库 (DotNetKB),然后在 Stored Procedures(存储过程)节点上单击鼠标右键,打开上下文相关菜单。 ·从上下文相关菜单中选择 New Stored Procedure(新建存储过程),在 Visual Studio .NET 编辑器空间中打开一个存储过程模板。现在,可以键入内容了。 ·完成编辑后,只需关闭编辑器中正在编辑的页面,Visual Studio .NET 将使用存储过程的名称将该项内容保存到数据库中。如果键入的内容有误,编辑器会向您报告这些错误,您可以在保存存储过程之前修正这些错误。
下面是存储过程的一个简单示例,它返回一个主题列表。
<ccid_nobr><table cellspacing="0" bordercolordark="#ffffff" cellpadding="2" width="400" align="center" bordercolorlight="black" border="1"><tbody><tr><td class="code" style="FONT-SIZE: 9pt" bgcolor="#e6e6e6"><pre><ccid_code>CREATE PROCEDURE TopicsGetList
AS
SET NOCOUNT ON -- 不返回受影响行的值
SELECT
ID,
Title,
Description
FROM
Topics
ORDER BY
Title
RETURN @@ERROR</ccid_code></pre></td></tr></tbody></table></ccid_nobr>
在本示例中,有几点需要指出。首先,请注意 SET NOCOUNT ON 行。它告诉 SQL Server 停止为该查询计算受影响的行数,并停止向调用函数返回该值。这是一项不必要的额外工作。其次,结尾处的 RETURN @@ERROR 一行很重要。此行代码返回 SQL Server 中发生的错误的整数值。您可以在调用例程中使用此代码完成其他诊断和错误处理操作。您现在并不需要执行任何操作,但它们是创建存储过程时应该遵循的两个好习惯。
下面是一个更复杂的存储过程。此过程用于从数据库中检索单条主题记录。您会发现一些附加项,包括输入参数、返回特定值的输出参数,以及检查输入参数并在需要时返回错误的某些程序代码。
<ccid_nobr><table cellspacing="0" bordercolordark="#ffffff" cellpadding="2" width="400" align="center" bordercolorlight="black" border="1"><tbody><tr><td class="code" style="FONT-SIZE: 9pt" bgcolor="#e6e6e6"><pre><ccid_code>CREATE PROCEDURE TopicsGetItem
(
@AdminCode char(3),
@ID int,
@Title varchar(30) OUTPUT,
@Description varchar(500) OUTPUT
)
AS
SET NOCOUNT ON -- 不返回受影响行的值
-- 确保是一个 Admin 用户
IF @AdminCode<>'adm'
BEGIN
RETURN 100 -- 无效 admin 错误
END
-- 检查记录是否存在
IF (SELECT Count(ID) FROM Topics WHERE ID=@ID)=0
BEGIN
RETURN 101 --- 无效 ID 代码
END
-- 继续执行并返回该记录
SELECT
@Title=Title,
@Description=Description
FROM
Topics
WHERE
ID=@ID
-- 返回错误,如果成功则返回 0
RETURN @@ERROR</ccid_code></pre></td></tr></tbody></table></ccid_nobr>
在本示例中,还有几点需要指出。首先,您会在存储过程顶端看到一个参数列表。除前两个参数外,其他参数均被标记为 OUTPUT 参数。这些参数用于返回选定记录的值。使用一条记录的返回值要比返回带有所有字段的记录集合更为高效。
其次,您会发现用于检查 @AdminCode 参数值的 T-SQL 数据块,以确保传递正确的代码。如果传递的代码不正确,则传递返回代码 100 并停止执行该过程。再其次,您会发现检查 @ID 参数,以确保其代表一条现有记录。如果不是现有记录,则传送返回代码 101 并终止执行。最后,如果输入变量都有效,存储过程将尝试选择记录并返回相应的值。如果此时发生任何错误,将由该过程的最后一行代码进行处理。
注意:通常情况下,最好将自定义错误代码及其含义保存在数据库中的一个单独的表格中,或保存在解决方案可以访问的文本文件中。这样就可以轻松更新这些错误代码,并与解决方案中的其他子系统共享。因为这只是一个短小的示例,其中只使用了两个错误代码,所以我决定创建一个包含大量代码和消息的文档,以供其他子系统参考。
该解决方案中包含的存储过程超过 25 个。本文仅举一例进行说明,其他代码可以通过本文开始处的链接进行下载。最后这个示例使用一个自定义的内置标量函数。
使用自定义标量函数
有时,单独一个存储过程不足以解决问题。例如,我们的用户方案中就有一个方案要求列出某个问题的解答数目。解决此问题的方法之一是生成一个对问题的解答进行计数的子查询。另外一种方法是生成一个自定义函数,返回标量值并将其包含在问题查询中。这种方法还有一个好处,那就是我们可以在其他存储过程中再次使用该标量函数。
添加自定义函数的操作类似于添加存储过程。在 Server Explorer(服务器资源管理器)树中,在选定数据库的 Functions(函数)节点上单击鼠标右键,然后从上下文相关菜单中选择 New Scalar-Valued Function(新建标量值函数)。然后在编辑器中编辑该文档,并像保存存储过程那样保存该文档。
以下是自定义函数的代码:
<ccid_nobr><table cellspacing="0" bordercolordark="#ffffff" cellpadding="2" width="400" align="center" bordercolorlight="black" border="1"><tbody><tr><td class="code" style="FONT-SIZE: 9pt" bgcolor="#e6e6e6"><pre><ccid_code>CREATE FUNCTION dbo.fn_QuestionsGetResponseCount
(
@ID int
)
RETURNS int
AS
BEGIN
DECLARE @ResponseCount int
Set @ResponseCount =
(
SELECT
COUNT(Responses.ID)
FROM
Responses
WHERE
Responses.QuestionID=@ID
)
RETURN @ResponseCount
END</ccid_code></pre></td></tr></tbody></table></ccid_nobr>
以下是使用自定义函数的存储过程:
<ccid_nobr><table cellspacing="0" bordercolordark="#ffffff" cellpadding="2" width="400" align="center" bordercolorlight="black" border="1"><tbody><tr><td class="code" style="FONT-SIZE: 9pt" bgcolor="#e6e6e6"><pre><ccid_code>CREATE PROCEDURE QuestionsGetCountWithNoResponses
(
@Total int OUTPUT
)
AS
SET NOCOUNT ON -- 不返回受影响行的值
SELECT
@Total=Count(ID)
FROM
Questions
WHERE
dbo.fn_QuestionsGetResponseCount(Questions.ID)=0
RETURN @@ERROR</ccid_code></pre></td></tr></tbody></table></ccid_nobr> |
相关推荐
使用存储过程查询时间段数据 编程小实例,C++.net源代码编写
有关sqlserver存储过程的编写及例子,并且对其他如.net中的存储过程有一点例子。
ADModify 使用 Microsoft Visual C#.NET 2003 编写。其 2.0 版本经过改进,执行相同修改时所花费的时间仅仅是先前版本所需时间的一半。其功能集允许管理员批量修改任何 Active Directory 分区中的任何 Active ...
目录 简介 使用VisualStudio.NET2003创建数据库图 使用VisualStudio.NET2003编写存储过程 IIS、ASP.NET和SQLServer的安全性问题 创建ASP.NET测试页 生成源代码和安装脚本 小结 简介 在“进入ASP.NET世界...
包括存过日志加载,分区创建,已经存过的使用
七、如何使用存储过程输出参数来检索单个行 八、使用存储过程输出参数来检索单个行 九、使用 SqlDataReader 对象来返回单个行 十、如何使用 ExecuteScalar 来检索单个项 十一、使用 ExecuteScalar 来检索单个项 十二...
ADO.NET调用存储过程,有ppt、sql。包括无参和带参存储过程的编写及其调用。学习之用。一通百通。有ADO.NET和SQL的基础知识更容易看懂。
使用ASP.NET 2.0 Profile存储用户信息[翻译] Level 200 [ASP.NET 2.0]PageParser.GetCompiledPageInstance中存在一个Bug 如何在DotNet 2的登录组件中检索用户的锁定状态及解锁? ASP.NET 2.0, 想说爱你不容易 ...
Server2005及以上版本编写存储过程、触发器和存储过程的,不过之前开发的系统要么因为历史原因用的是SQL2000要么根本用不着在SQL Server中启用CLR,所以一直没有尝试。最近因为项目的原因,在这方面做了一个调研,...
C#,Sql Server2005存储过程,触发器,Div+Css,jQuery, 系统实现功能: 1,后台新闻分类增删改; 2,后台新闻内容增删改查,添加新闻用到FreeTextBox控件,分页用到AspNetPager分页控件,源码中都带有最新版的相关...
《iPhone高级编程:使用Mono Touch和.NET/C#》从基本的概念入手,全面地介绍了基于monotouch使用C#/.net进行iphone应用程序开发。从monotouch和.net开发环境到objective-c和c#开发语言,从基于monotouch进行iphone...
C .net源代码编写的文件操作编程实例大集合,运行于VisualStudio.net2005及以上版本环境。这些实例主要有: 如何复制多层文件夹及文件 如何过滤指定目录下的文件 如何过滤指定目录下的子目录 如何获取...
一款好用桌面管理系统,用.net语言编写用到的有C#和VB代码,皮肤设置,“定时关机系统”,“资源管理”控制面板,任务管理,注册表,“闰年判断”开机自启动等。以存储网址、管理邮件、通讯和工具等。
完全代码集成-使用Microsoft Visual Studio.NET编辑器用C#或VB.NET编写报表的代码,非常灵活。 报表向导-报表向导帮助您开始创建使用集成设计器来定制的简单报表。 图片文件名 运用优势 最终用户报表设计器 ...
1、资料包含了作者编写的实际应用场景中使用的存储过程代码,共计118个案例,有完整注释说明。旨在帮助读者通过案例学习,快速掌握存储过程的应用方法和技巧。 2、博主博客:...
Paint.NET主要使用C#编写,部份安装文件及集成Windows功能则以C++编写。Paint.NET本地图像格式为.PDN,一种已压缩,表达软件内部对象的格式,用以存储图层及其他数据。[2]Paint.NET除安装程序、程序所用文字及图像外...
为 .NET 数据提供程序编写通用代码:提供有关如何使用 ADO.NET 所提供的通用接口来编写一组对于任何 .NET 数据提供程序都将运行的代码的信息。 ADO.NET 示例应用程序:提供 ADO.NET 应用程序的示例,该示例从数据库中...
数据调用,全部用数据库存储过程,页面上不存在 一般的sql语言 并且大多都考虑到了并发控制。当然有什么问题可以直接发我邮箱进行询问 ,我会很认真回答你的问题。毕竟都是成长阶段。希望对喜欢这方面的人有所帮助。...
使用存储过程1. 有返回值2. 输入参数 3. 输出参数ii. 使用触发器iii. 小结第四章 内存中的数据库 i. 字段映射ii. 数据表iii. 表间关系 iv. 表间约束v. DataSet和XML 第五章响应ADO.NET的事件 i. DataSet Events ii....