C#C# 调用存款和储蓄进程操作 OUTPUT参数和Return重临值

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

C#,积存进度是存放在在数据库服务器上的预先编写翻译好的sql语句。使用存款和储蓄进度,可以一向在数据库中储存并运转效率强大的天职。存款和储蓄进度在第3应用程序执行时进行语法检查和编写翻译,编写翻译好的本子保存在高速缓存中。在推行重复任务时,存款和储蓄进程可以抓实质量和一致性。由于存款和储蓄进度能够将一多种对数据库的操作放在数据库服务器上实施,由此能够减低Web服务器的负荷,升高整系列统的习性。

 

一、创造存款和储蓄进程

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'

  

 

二、调用存储进程

 

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();
            }
        }

  

 

三、输出参数

储存进程能够经过输出参数来再次来到值。

 

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 返回值

使用再次来到值表示存款和储蓄进程的实市价况,它相仿于出口参数,其不一样:

(一)、再次来到值只可以回到sql整数值;

(二)、再次回到值不可能在储存进程里面宣称,它接纳Transcat-SQL的RETUPAJERON语句重返;

 

 

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();
            }

  

 

 

相关文章