在.net中调用存储过程的另一种方法
类别: ASP.NET教程 
[存储过程]
CREATE PROCEDURE Test_procedure @cid int ,@y int, @n varchar(10) output
AS
select * from city where cid=@cid
if @y=1
begin
set @n = \'成功！\'
return 0
end
else
begin
set @n = \'失败！\'
return -1
end
GO
[程序中调用存储过程]
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As OleDbConnection
Try
conn = New OleDbConnection(\"provider=sqloledb;data source=localhost;user id=sa;password=;initial catalog=jiang\")
Dim cmd As New OleDbCommand(\" {? = call Test_procedure(?,?,?)}\", conn)
conn.Open()
With cmd
.CommandType = CommandType.Text ‘这句可以省略，因为是默认设置，但这里还是显式地写出以引起注意
.Parameters.Add(\"@retval\", OleDbType.Integer)
.Parameters.Add(\"@cid\", OleDbType.Integer)
.Parameters.Add(\"@y\", OleDbType.Integer)
.Parameters.Add(\"@n\", OleDbType.VarChar, 10)
.Parameters(\"@n\").Direction = ParameterDirection.Output
.Parameters(\"@retval\").Direction = ParameterDirection.ReturnValue
.Parameters(\"@cid\").Value = 1
.Parameters(\"@y\").Value = 1
.ExecuteNonQuery()
MessageBox.Show(.Parameters(\"@n\").Value) ‘测试结果为“成功！”
End With
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
conn.Close()
End Try
End Sub
CREATE PROCEDURE Test_procedure @cid int ,@y int, @n varchar(10) output
AS
select * from city where cid=@cid
if @y=1
begin
set @n = \'成功！\'
return 0
end
else
begin
set @n = \'失败！\'
return -1
end
GO
[程序中调用存储过程]
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As OleDbConnection
Try
conn = New OleDbConnection(\"provider=sqloledb;data source=localhost;user id=sa;password=;initial catalog=jiang\")
Dim cmd As New OleDbCommand(\" {? = call Test_procedure(?,?,?)}\", conn)
conn.Open()
With cmd
.CommandType = CommandType.Text ‘这句可以省略，因为是默认设置，但这里还是显式地写出以引起注意
.Parameters.Add(\"@retval\", OleDbType.Integer)
.Parameters.Add(\"@cid\", OleDbType.Integer)
.Parameters.Add(\"@y\", OleDbType.Integer)
.Parameters.Add(\"@n\", OleDbType.VarChar, 10)
.Parameters(\"@n\").Direction = ParameterDirection.Output
.Parameters(\"@retval\").Direction = ParameterDirection.ReturnValue
.Parameters(\"@cid\").Value = 1
.Parameters(\"@y\").Value = 1
.ExecuteNonQuery()
MessageBox.Show(.Parameters(\"@n\").Value) ‘测试结果为“成功！”
End With
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
conn.Close()
End Try
End Sub
