100165601.htm
来自「C#高级编程(第三版),顶死你们。。 。up」· HTM 代码 · 共 164 行 · 第 1/2 页
HTM
164 行
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><title>
21.3.2 调用存储过程
</title></head>
<body>
<div class="area">
<div class="col1">
<div class="lineBlue">
</div>
<!-- title -->
<div class="arcTitle">
<h1>
<a href="../16">
C#高级编程（第3版）
</a>
</h1>
<div style="text-align: center; font-size: 15px">
<a href="100165601.htm">
21.3.2 调用存储过程
</a>
</div>
<div style="text-align: center; font-size: 15px">
<a class="url" href="../../default.htm">http://book.csdn.net/</a>
2006-10-13 14:41:00
</div>
<div style="margin: 0px auto; width: 700px; border: solid 1px #0b5f98;">
<div style="float: left; width: 16px; background-color: #0b5f98; color: White; padding: 1px;">
图书导读
</div>
<div style="float: right; width: 670px; text-align: left; line-height: 16pt; padding-left: 2px">
<!--导读-->
<h1 id="divCurrentNode" style="color: #b83507; width: 100%; text-align: left; font-size: 12px; padding-left: 2px">当前章节:<a href='100165601.htm'><font color='red'>21.3.2 调用存储过程</font></a></h1>
<div id="divRelateNode" style="padding-left: 2px">
<div style='float:left;width:49%'>·<a href='100165598.htm'>21.2.2 事务处理</a></div><div style='float:right;width:49%'>·<a href='100165599.htm'>21.3 命令</a></div><div style='float:left;width:49%'>·<a href='100165600.htm'>21.3.1 执行命令</a></div><div style='float:right;width:49%'>·<a href='100165602.htm'>21.4 快速数据访问：数据读取器</a></div><div style='float:left;width:49%'>·<a href='100165603.htm'>21.5 管理数据和关系：DataSet类</a></div><div style='float:right;width:49%'>·<a href='100165604.htm'>21.5.1 数据表</a></div></div>
</div>
</div>
</div>
<!-- main -->
<div id="main">
<div id="text">
<div id="csdn_zhaig_ad_yahoo_2"></div>
<link href="css.css" rel="stylesheet" type="text/css" /><h3 style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 0cm; MARGIN-RIGHT: 0cm; FTEL: 8.15pt"><a ftel="_Toc507815162"><span lang="EN-GB">21.3.2 </span></a><span style="FONT-FAMILY: 黑体">调用存储过程</span></h3>
<p class="MsoNormal"><a ftel="storedprocedures"><span style="FONT-FAMILY: 宋体">用一个命令对象调用存储过程，就是定义存储过程的名称，给过程的每个参数添加参数定义，然后用上一节中给出的方法执行命令。</span></a></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">为了使本节的示例更有说服力，下面定义一组可以用于插入、更新和删除</span><span lang="EN-US">Northwind</span><span style="FONT-FAMILY: 宋体">示例数据库中</span><span lang="EN-US">Region</span><span style="FONT-FAMILY: 宋体">表的记录的存储过程，这个表尽管很小，但可以用于给每种常见的存储过程编写示例。</span></p>
<h4 style="FTEL: 21.45pt"><span lang="EN-GB">1. </span><span style="FONT-FAMILY: 黑体">调用没有返回值的存储过程</span></h4>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">调用存储过程的最简单示例是不给调用者返回任何值。下面定义了两个这样的存储过程，一个用于更新现有的</span><span lang="EN-US">Region</span><span style="FONT-FAMILY: 宋体">记录，另一个用于删除指定的</span><span lang="EN-US">Region</span><span style="FONT-FAMILY: 宋体">记录。</span></p>
<p class="MsoNormal"><span lang="EN-US">(1) </span><span style="FONT-FAMILY: 宋体">记录的更新</span></p>
<p class="MsoNormal"><a ftel="recordupdating"><span style="FONT-FAMILY: 宋体">更新</span><span lang="EN-US">Region</span></a><span style="FONT-FAMILY: 宋体">记录是很简单的，因为</span><span lang="EN-US">(</span><span style="FONT-FAMILY: 宋体">假定主键码不能被更新</span><span lang="EN-US">)</span><span style="FONT-FAMILY: 宋体">只有一个列可以更新。直接在</span><span lang="EN-US">SQL Server</span><span style="FONT-FAMILY: 宋体">查询分析器中键入这些示例，或者运行本章下载代码中的</span><span lang="EN-US">StoredProcs.sql</span><span style="FONT-FAMILY: 宋体">文件，在该文件中包含本节中的所有存储过程：</span></p>
<p class="2" style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-US">CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> @RegionDescription NCHAR(50)) AS</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> SET NOCOUNT OFF</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> UPDATE Region</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> SET RegionDescription = @RegionDescription</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> WHERE RegionID = @RegionID</span></p>
<p class="2" style="MARGIN-TOP: 0cm; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-US">GO</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">给真实世界中的表执行更新命令，需要重复选择和返回已更新的记录。这个存储过程有两个输入参数</span><span lang="EN-US">(@RegionID</span><span style="FONT-FAMILY: 宋体">和</span><span lang="EN-US">@RegionDescription)</span><span style="FONT-FAMILY: 宋体">，对数据库执行</span><span lang="EN-US">UPDATE</span><span style="FONT-FAMILY: 宋体">语句。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">要在</span><span lang="EN-US">.NET</span><span style="FONT-FAMILY: 宋体">代码中运行这个存储过程，需要定义一个</span><span lang="EN-US">SQL</span><span style="FONT-FAMILY: 宋体">命令，并执行它：</span></p>
<p class="2" style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-US">SqlCommand aCommand = new SqlCommand("RegionUpdate", conn);</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> </span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US">aCommand.CommandType = CommandType.StoredProcedure</span><span lang="EN-GB">;</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US">aCommand.Parameters.Add(new SqlParameter ("@RegionID", </span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> SqlDbType.Int, </span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> 0, </span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> "RegionID"));</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US">aCommand.Parameters.Add(new SqlParameter("@RegionDescription", </span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> SqlDbType.NChar, </span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> 50, </span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US"> "RegionDescription"));</span></p>
<p class="2" style="MARGIN-TOP: 0cm; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-US">aCommand.UpdatedRowSource = UpdateRowSource.None;</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">这段代码创建了一个新</span><span lang="EN-US">SqlCommand</span><span style="FONT-FAMILY: 宋体">对象</span><span lang="EN-US">aCommand</span><span style="FONT-FAMILY: 宋体">，并把它定义为一个存储过程。然后，依次添加每个参数，最后把存储过程的结果设置为</span><span lang="EN-US">UpdateRowSource</span><span style="FONT-FAMILY: 宋体">枚举中的一个值。</span></p>
<p class="MsoNormal"><span style="FONT-FAMILY: 宋体">该存储过程有两个参数：要更新的</span><span lang="EN-US">Region</span><span style="FONT-FAMILY: 宋体">记录的惟一主键码；给这个记录的新描述。创建好命令后，就可以用下面的命令执行它：</span></p>
<p class="2" style="MARGIN-TOP: 8.15pt; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-US">aCommand.Parameters[0].Value = 999;</span></p>
<p class="2" style="MARGIN-LEFT: 21.45pt; FTEL: 18.45pt"><span lang="EN-US">aCommand.Parameters[1].Value = "South Western England";</span></p>
<p class="2" style="MARGIN-TOP: 0cm; MARGIN-LEFT: 21.45pt; MARGIN-RIGHT: 0cm; FTEL: 18.45pt"><span lang="EN-US">aCommand.ExecuteNonQuery();</span></p>
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?