执行存储过程
一般用户请忽略本节内容。
假定我们在SQL Server创建了一个存储过程:
CREATE PROCEDURE GetOrders
@客户 nvarchar(8)
AS
BEGIN
SELECT * FROM 订单 WHERE 客户=@客户
END
执行此存储过程有两种方法。
方法一
可以直接用存储过程名执行,此时需要将StoredProcedure属性设置为True:
Dim
cmd As
new
SQLCommand
cmd.ConnectionName
= "数据源名称"
cmd.CommandText
= "GetOrders"
'指定存储过程名
cmd.StoredProcedure
= True
'表示CommandText内容不是标准的SQL语句,而是存储过程名
cmd.Parameters.Add("@客户","联想")
Dim
dt As
DataTable = cmd.ExecuteReader
方法二
也可以用EXEC语句执行存储过程:
Dim
cmd As
new
SQLCommand
cmd.ConnectionName
= "数据源名称"
cmd.CommandText
= "EXEC GetOrders ?"
cmd.Parameters.Add("@客户","联想")
Dim
dt As
DataTable = cmd.ExecuteReader
下面的语句:
EXEC GetOrders ?
表示执行存储过程GetOrders,这个存储过程需要一个参数(SQL语用"?"表示参数,多个参数用逗号分隔)。
存储过程的三种参数
存储过程有两种参数,分别为输入参数和输出参数。
有的存储过程执行完毕后会返回一个值,这个值需要通过参数获取,我们称之为返回参数。
为区分这是这三种参数,首先需要了解一下SQLCommand的Parameters属性的Add方法的完整语法:
Add(Name, Value)
Add(Name, Value, Output)
Add(Name, Value, Output, Size)
Name | 参数名称 |
Value | 参数的值 |
Output | 逻辑型,省略表示输入参数,True表示输出参数,False表示返回参数。 |
Size | 整数型,用于指定输入参数或返回参数的大小,默认为32,只有字符型的参数才需要设定。 |
增加输入参数最为简单,例如:
cmd.Parameters.Add("@客户","联想")
增加输出参数需要将Add方法的第三个参数设置为True,例如:
cmd.Parameters.Add("@日期",Date.today,True)
增加返回参数需要将Add方法的第三个参数设置为False,例如:
cmd.Parameters.Add("@Count", 0, False)
接下来我们开始介绍如何使用输出参数和返回参数。
获取输出参数的值
假定我们创建了一个存储过程,用于获取指定编号订单的日期:
CREATE PROCEDURE GetOrderDate
@订单编号 nvarchar(8),
@日期 datetime OUTPUT
AS
BEGIN
SELECT @日期=日期 FROM 订单 WHERE 订单编号=@订单编号
END
这个存储过程有两个参数,第一个为输入参数,用于指定订单编号,第二个为输出参数,用于获取该编号订单的日期。
在Foxtable使用上述存储过程的代码为:
Dim
cmd As
new
SQLCommand
cmd.ConnectionName
= "数据源名称"
cmd.CommandText
= "GetOrderDate"
cmd.StoredProcedure
= True
cmd.Parameters.Add("@订单编号","1002")
cmd.Parameters.Add("@日期",Date.today,True)
cmd.ExecuteNonQuery
Dim
Val As
Date = cmd.Parameters("@日期")
'获取存储过程返回的日期值
注意增加输出参数必须将Add方法的第三个参数设置为True,例如:
cmd.Parameters.Add("@日期",Date.today,True)
如果采用SQL语句执行,需要在语句中将有关参数标记为output,例如:
Dim
cmd As
new
SQLCommand
cmd.ConnectionName
= "数据源名称"
cmd.CommandText
= "exec GetOrderDate ?,? output"
'注意第二个参数被标记为output了。
cmd.Parameters.Add("@订单编号","1002")
cmd.Parameters.Add("@日期",Date.today,True)
cmd.ExecuteNonQuery
Dim
Val As
Date = cmd.Parameters("@日期")
获取存储过程的返回值
假定我们创建了一个存储过程,用于获取指定日期某客户的全部订单,并返回订单数:
CREATE PROCEDURE GetOrders3 (@CID
nvarchar(10), @DT datetime)
AS
SELECT * FROM {订单} Where 客户 = @CID AND 日期=@DT
RETURN @@RowCount
在Foxtable执行以上存储过程的参考代码:
Dim
cmd As
new
SQLCommand
cmd.ConnectionName
= "数据源名称"
cmd.CommandText
= "GetOrders3"
cmd.StoredProcedure
= True
cmd.Parameters.Add("@Count",0,False)
'这是第一个参数,用于获取存储过程的返回值
cmd.Parameters.Add("@客户","Dell")
'第二个参数指定客户
cmd.Parameters.Add("@日期",#4/13/2018#)
'第三个参数指定日期
Dim
dt As
DataTable = cmd.ExecuteReader
'获取订单生成DataTable
Dim
count As
Integer = Cmd.Parameters("@Count")
'取得存储过程的返回值
注意增加返回参数必须将Add方法的第三个参数设置为False,例如:
cmd.Parameters.Add("@Count",0,False) '这是第一个参数,用于获取存储过程的返回值
如果采用SQL语句执行,参考代码为:
Dim
cmd As
new
SQLCommand
cmd.ConnectionName
= "数据源名称"
cmd.CommandText
= "exec ?=GetOrders3 ?,?"
cmd.Parameters.Add("@Count",0,True)
'这是第一个参数,用于获取存储过程的返回值
cmd.Parameters.Add("@客户","Dell")
'第二个参数指定客户
cmd.Parameters.Add("@日期",#4/13/2018#)
'第三个参数指定日期
Dim
dt As
DataTable = cmd.ExecuteReader
'获取订单生成DataTable
Dim
count As
Integer = Cmd.Parameters("@Count")
'取得存储过程的返回值
以上代码采用SQL语句执行存储过程:
exec ?=GetOrders3 ?,?
这里有三个参数,等号左边的参数用于获取存储过程的返回值(但此时不是返回参数,而是输出参数,只有直接执行存储过程时才有返回参数一说),等号右边的是两个输入参数,分别用于指定客户和日期。
三种参数混合使用示例
假定我们定义了如下的存储过程 ,用户获取指定编号订单的客户名称:
CREATE PROCEDURE
GetOrderByID
@订单编号 varchar(8),
@客户 nvarchar(50) OUTPUT
AS
BEGIN
SELECT @客户=客户 FROM 订单 WHERE 订单编号=@订单编号
IF @@Error <> 0
RETURN -1
ELSE
RETURN 100
END
在Foxtable执行上述存储过程的参考代码:
Dim
cmd As
new
SQLCommand
cmd.ConnectionName
= "数据源名称"
cmd.CommandText
= "GetOrderByID"
cmd.StoredProcedure
= True
cmd.Parameters.Add("@Err",
12, False)
'返回参数
cmd.Parameters.Add("@订单编号","1002")
'输入参数
cmd.Parameters.Add("@客户",
"", True)
'输出参数
cmd.ExecuteNonQuery
Output.Show(
cmd.Parameters("@客户"))
Output.Show(
cmd.Parameters("@Err"))
需要特别注意的是:返回参数必须作为第一个参数添加,否则会出错。
采用SQL语句执行上述存储过程的参考代码:
Dim
cmd As
new
SQLCommand
cmd.ConnectionName
= "数据源名称"
cmd.CommandText
= "Exec ?=GetOrderByID ?,?
output"
cmd.Parameters.Add("@Err",
12, True)
'用于获取存储过程的返回值,注意这是输出参数,不是输入参数
cmd.Parameters.Add("@订单编号","1002")
'输入参数
cmd.Parameters.Add("@客户",
"", True)
'输出参数
cmd.ExecuteNonQuery
Output.Show(
cmd.Parameters("@客户"))
Output.Show(
cmd.Parameters("@Err"))