存储过程调用.doc

### 存储过程调用详解 #### 一、目的与要求 存储过程是数据库中预编译好的SQL脚本，可以提高数据处理效率并增强安全性。本次内容的主要目的是让学习者能够熟练掌握如何创建、调用及删除存储过程，并通过实践加深对存储过程的理解。 - **熟练掌握存储过程的创建、调用和删除** - 创建存储过程涉及定义存储过程的逻辑、参数等； - 调用存储过程则是通过合适的命令或函数来执行存储过程中定义的操作； - 删除存储过程则是通过DROP PROCEDURE命令来移除不再需要的存储过程。 - **了解应用C#调用存储过程** - 在.NET框架下，利用ADO.NET进行数据库操作时，可以通过SqlCommand对象来调用存储过程，实现复杂的数据处理逻辑。 #### 二、内容和步骤详解 ##### 1. 统计安徽省各地区的总面积 - **创建存储过程** `exp1`： - 需要查询安徽省所有地区的面积，并计算总面积。 - 示例代码如下： ```sql CREATE PROCEDURE exp1 AS BEGIN SELECT RegionName, SUM(Area) AS TotalArea FROM Regions WHERE Province = '安徽省' GROUP BY RegionName END GO ``` - **调用存储过程** `exp1`： - 使用SQL Server Management Studio或其他数据库管理工具执行该存储过程。 - **观察结果**： - 查看每个地区的总面积。 ##### 2. 查询地区总面积排名 - **创建存储过程** `exp2`： - 接受一个参数，即地区行政代码的前四位。 - 输出该地区的总面积在全省范围内的排名。 - 示例代码如下： ```sql CREATE PROCEDURE exp2 @AdminCode NVARCHAR(4) AS BEGIN WITH RankedRegions AS ( SELECT RegionName, Area, RANK() OVER (ORDER BY Area DESC) AS Rank FROM Regions WHERE Province = '安徽省' ) SELECT RegionName, Area, Rank FROM RankedRegions WHERE LEFT(RegionName, 4) = @AdminCode END GO ``` - **调用存储过程** `exp2`： - 传入地区行政代码前四位作为参数。 - **观察结果**： - 查看该地区的总面积排名。 ##### 3. 计算安徽省县（市）数量 - **创建存储过程** `exp3`： - 首先检查是否存在同名存储过程，若存在则删除。 - 创建新的存储过程以输出安徽省有多少个县（市）。 - 示例代码如下： ```sql IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'exp3') DROP PROCEDURE exp3 GO CREATE PROCEDURE exp3 AS BEGIN SELECT COUNT(*) AS CountyCount FROM Regions WHERE Province = '安徽省' AND Type = '县/市' END GO ``` - **为exp3加密**： - 使用WITH ENCRYPTION关键字可以在创建存储过程时加密其定义。 - 示例代码如下： ```sql IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'exp3') DROP PROCEDURE exp3 GO CREATE PROCEDURE exp3 WITH ENCRYPTION AS BEGIN -- 加密后的代码无法直接查看 SELECT COUNT(*) AS CountyCount FROM Regions WHERE Province = '安徽省' AND Type = '县/市' END GO ``` ##### 4. C#调用存储过程示例 - **返回单一记录集的存储过程** `Categoriestest1`： - 创建SqlConnection对象用于连接数据库。 - 创建SqlCommand对象并设置其属性。 - 使用SqlDataAdapter填充DataSet对象。 - 示例代码如下： ```csharp SqlConnection sqlconn = new SqlConnection(conn); SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlconn; cmd.CommandText = "Categoriestest1"; cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter dp = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); dp.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); ``` - **无输入输出参数的存储过程** `Categoriestest2`： - 插入一条新记录到Categories表中，并返回受影响的行数。 - 示例代码如下： ```csharp SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlconn; cmd.CommandText = "Categoriestest2"; cmd.CommandType = CommandType.StoredProcedure; sqlconn.Open(); Label1.Text = cmd.ExecuteNonQuery().ToString(); sqlconn.Close(); ``` - **有返回值的存储过程** `Categoriestest3`： - 创建参数并设置方向为返回值。 - 执行存储过程并获取返回值。 - 示例代码如下： ```csharp IDataParameter[] parameters = { new SqlParameter("rval", SqlDbType.Int, 4) }; parameters[0].Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(parameters[0]); sqlconn.Open(); Label1.Text = cmd.ExecuteNonQuery().ToString() + "-" + parameters[0].Value.ToString(); sqlconn.Close(); ``` - **有输入参数和输出参数的存储过程** `Categoriestest4`： - 定义输入参数和输出参数。 - 设置参数值并执行存储过程。 - 示例代码如下： ```csharp SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlconn; cmd.CommandText = "Categoriestest4"; cmd.CommandType = CommandType.StoredProcedure; // 添加输入参数 cmd.Parameters.Add(new SqlParameter("@inputParam", SqlDbType.NVarChar, 50)); cmd.Parameters["@inputParam"].Value = "测试输入参数"; // 添加输出参数 cmd.Parameters.Add(new SqlParameter("@outputParam", SqlDbType.Int)); cmd.Parameters["@outputParam"].Direction = ParameterDirection.Output; sqlconn.Open(); cmd.ExecuteNonQuery(); sqlconn.Close(); // 获取输出参数的值 int outputValue = (int)cmd.Parameters["@outputParam"].Value; ``` 通过以上详细解释和示例代码，读者可以更加深入地理解存储过程的创建、调用及其在C#中的应用。这些示例不仅有助于学习者掌握存储过程的基本概念，还能够帮助他们更好地理解和运用数据库编程技术。