C# 调用存储过程操作 OUTPUT参数和Return返回值

本文转载:http://www.cnblogs.com/libingql/archive/2010/05/02/1726104.html  

积存过程是存放于数据库服务器上的预编译好的sql语句。使用存储过程,可以一直以数据库被储存并运行功能强大的职责。存储过程在率先应用程序执行时开展语法检查及编译,编译好的版保存在高速缓存中。在执行还任务时,存储过程得增长性能及一致性。由于存储过程可用同多级对数据库的操作放在数据库服务器上实施,因而可落Web服务器的负荷,提高全体系统的属性。

 

1、创建存储过程

CREATE PROC [DBO].[GetOrdersByOrderDate]
    @OrderDate DATETIME
AS
    SELECT
        [OrderID],[CustomerID],[OrderDate]
    FROM
        [Orders]
    WHERE
        [OrderDate] >= @OrderDate


USE Northwind
GO
EXEC GetByOrderDate '1996-10-10 0:00:00'

  

 

2、调用存储过程

 

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "[GetOrdersByOrderDate]";
                cmd.Connection = conn;

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@OrderDate", SqlDbType.DateTime, 8).Value = "1998-5-5";

                conn.Open();

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;

                DataSet ds = new DataSet();

                da.Fill(ds);

                gvOrders.DataSource = ds;
                gvOrders.DataBind();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conn.Close();
            }
        }

  

 

3、输出参数

囤过程可以经输出参数来回到值。

 

CREATE PROC [DBO].[GetOrderDetailsTotalByProductID]
    @ProductID INT,
    @Total MONEY OUTPUT
AS
    SELECT @Total = SUM([UnitPrice]*[Quantity])
    FROM
        [Order Details]
    WHERE
        [ProductID] = @ProductID

 

SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "[GetOrderDetailsTotalByProductID]";
                cmd.Connection = conn;

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@ProductID", SqlDbType.Int, 4).Value = 21;
                cmd.Parameters.Add("@Total", SqlDbType.Money).Direction = ParameterDirection.Output;

                conn.Open();
                cmd.ExecuteNonQuery();

                lblTotal.Text = cmd.Parameters["@Total"].Value.ToString();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conn.Close();
            }

  

  

 

4、Return 返回值

使返回值表示存储过程的实施状态,它仿佛于出口参数,其区别:

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

(2)、返回值未能够于囤过程中宣称,它采取Transcat-SQL的RETURN语句返回;

 

 

CREATE PROC [DBO].[GetOrdersByCustomerID]
    @CustomerID INT,
    @OrderDate DATETIME OUTPUT
AS
    SELECT @OrderDate = MAX([OrderDate])
    FROM
        [Orders]
    WHERE
        [CustomerID] = @CustomerID

    IF @OrderDate IS NULL
        RETURN 0
    ELSE
        RETURN 1

 

SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "[GetOrdersByEmployeeID]";
                cmd.Connection = conn;

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add("@EmployeeID", SqlDbType.Int, 4).Value = 1;
                cmd.Parameters.Add("@OrderDate", SqlDbType.DateTime).Direction = ParameterDirection.Output;

                conn.Open();
                cmd.ExecuteNonQuery();

                lblReturnValue.Text = cmd.Parameters["@ReturnValue"].Value.ToString();
                lblOrderDate.Text = cmd.Parameters["@OrderDate"].Value.ToString();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conn.Close();
            }

  

 

 

相关文章