ASP.NET 存储过程操作

　　存储过程是存放在数据库服务器上的预先编译好的sql语句。使用存储过程，可以直接在数据库中存储并运行功能强大的任务。存储过程在第一应用程序执行时进行语法检查和编译，编译好的版本保存在高速缓存中。在执行重复任务时，存储过程可以提高性能和一致性。由于存储过程可以将一系列对数据库的操作放在数据库服务器上执行，因而可以降低Web服务器的负载，提高整个系统的性能。

 

1、创建存储过程

 1 USE Northwind
 2 GO
 3 CREATE PROC [DBO].[GetOrdersByOrderDate]
 4     @OrderDate DATETIME
 5 AS
 6     SELECT
 7         [OrderID],[CustomerID],[OrderDate]
 8     FROM
 9         [Orders]
10     WHERE
11         [OrderDate] >= @OrderDate
12 
13 
14 USE Northwind
15 GO
16 EXEC GetByOrderDate '1996-10-10 0:00:00'
17 

2、调用存储过程

 1             SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);
 2 
 3             try
 4             {
 5                 SqlCommand cmd = new SqlCommand();
 6                 cmd.CommandText = "[GetOrdersByOrderDate]";
 7                 cmd.Connection = conn;
 8 
 9                 cmd.CommandType = CommandType.StoredProcedure;
10 
11                 cmd.Parameters.Add("@OrderDate", SqlDbType.DateTime, 8).Value = "1998-5-5";
12 
13                 conn.Open();
14 
15                 SqlDataAdapter da = new SqlDataAdapter();
16                 da.SelectCommand = cmd;
17 
18                 DataSet ds = new DataSet();
19 
20                 da.Fill(ds);
21 
22                 gvOrders.DataSource = ds;
23                 gvOrders.DataBind();
24             }
25             catch (SqlException ex)
26             {
27                 throw new Exception(ex.Message, ex);
28             }
29             finally
30             {
31                 conn.Close();
32             }
33         }
34 

3、输出参数

存储过程可以通过输出参数来返回值。

 

 1 USE Northwind
 2 GO
 3 CREATE PROC [DBO].[GetOrderDetailsTotalByProductID]
 4     @ProductID INT,
 5     @Total MONEY OUTPUT
 6 AS
 7     SELECT @Total = SUM([UnitPrice]*[Quantity])
 8     FROM
 9         [Order Details]
10     WHERE
11         [ProductID] = @ProductID
12 

 1 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);
 2 
 3             try
 4             {
 5                 SqlCommand cmd = new SqlCommand();
 6                 cmd.CommandText = "[GetOrderDetailsTotalByProductID]";
 7                 cmd.Connection = conn;
 8 
 9                 cmd.CommandType = CommandType.StoredProcedure;
10 
11                 cmd.Parameters.Add("@ProductID", SqlDbType.Int, 4).Value = 21;
12                 cmd.Parameters.Add("@Total", SqlDbType.Money).Direction = ParameterDirection.Output;
13 
14                 conn.Open();
15                 cmd.ExecuteNonQuery();
16 
17                 lblTotal.Text = cmd.Parameters["@Total"].Value.ToString();
18             }
19             catch (SqlException ex)
20             {
21                 throw new Exception(ex.Message, ex);
22             }
23             finally
24             {
25                 conn.Close();
26             }
27 

4、Return 返回值

使用返回值表示存储过程的执行状态，它类似于输出参数，其区别：

(1)、返回值只能返回sql整数值；

(2)、返回值不能在存储过程内部声明，它使用Transcat-SQL的RETURN语句返回；

 

 1 USE Northwind
 2 GO
 3 CREATE PROC [DBO].[GetOrdersByCustomerID]
 4     @CustomerID INT,
 5     @OrderDate DATETIME OUTPUT
 6 AS
 7     SELECT @OrderDate = MAX([OrderDate])
 8     FROM
 9         [Orders]
10     WHERE
11         [CustomerID] = @CustomerID
12 
13     IF @OrderDate IS NULL
14         RETURN 0
15     ELSE
16         RETURN 1
17 

 1 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);
 2 
 3             try
 4             {
 5                 SqlCommand cmd = new SqlCommand();
 6                 cmd.CommandText = "[GetOrdersByEmployeeID]";
 7                 cmd.Connection = conn;
 8 
 9                 cmd.CommandType = CommandType.StoredProcedure;
10 
11                 cmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;
12                 cmd.Parameters.Add("@EmployeeID", SqlDbType.Int, 4).Value = 1;
13                 cmd.Parameters.Add("@OrderDate", SqlDbType.DateTime).Direction = ParameterDirection.Output;
14 
15                 conn.Open();
16                 cmd.ExecuteNonQuery();
17 
18                 lblReturnValue.Text = cmd.Parameters["@ReturnValue"].Value.ToString();
19                 lblOrderDate.Text = cmd.Parameters["@OrderDate"].Value.ToString();
20             }
21             catch (SqlException ex)
22             {
23                 throw new Exception(ex.Message, ex);
24             }
25             finally
26             {
27                 conn.Close();
28             }
29