快捷導(dǎo)航

asp.net 存儲(chǔ)過程調(diào)用

更新時(shí)間：2009年07月13日 21:07:11 作者：

調(diào)用存儲(chǔ)過程，但無返回值調(diào)用存儲(chǔ)過程，返回普通值調(diào)用存儲(chǔ)過程，返回?cái)?shù)據(jù)集的實(shí)現(xiàn)代碼。

1．調(diào)用存儲(chǔ)過程，但無返回值

Private Function SqlProc1(ByVal ProcName As String) As Boolean 
'定義數(shù)據(jù)鏈接部分省略, myConn為鏈接對(duì)象 ProcName為存儲(chǔ)過程名 
Dim myCommand As New SqlClient.SqlCommand(ProcName, myConn) 
With myCommand 
.CommandType = CommandType.StoredProcedure 
.Parameters.Add("@CodeType", SqlDbType.VarChar, 20).Value = "年級(jí)編碼" 
Try 
.ExecuteNonQuery() 
Return True 
Catch ex As Exception 
Return False 
End Try 
End Function 

2．調(diào)用存儲(chǔ)過程，返回普通值

復(fù)制代碼代碼如下:

Private Function SqlProc1(ByVal ProcName As String) As String 
'定義數(shù)據(jù)鏈接部分省略, myConn為鏈接對(duì)象 
Dim myCommand As New SqlClient.SqlCommand(ProcName, myConn) 
With myCommand 
.CommandType = CommandType.StoredProcedure 
.Parameters.Add("@CodeType", SqlDbType.VarChar, 20).Value = "年級(jí)編碼" 
.Parameters.Add("@NewCode", SqlDbType.VarChar, 20).Direction = ParameterDirection.Output 
Try 
.ExecuteNonQuery() 
Return .Parameters(1).Value() 
Catch ex As Exception 
Return "無編碼生成" 
End Try 
End Function 

3．調(diào)用存儲(chǔ)過程，返回?cái)?shù)據(jù)集
'VB.NET代碼

復(fù)制代碼代碼如下:

Private Function SqlProc2(ByVal ProcName As String, ByVal Param1 As String) As DataSet 
'定義命令對(duì)象，并使用儲(chǔ)存過程 
Dim myCommand As New SqlClient.SqlCommand 
myCommand.CommandType = CommandType.StoredProcedure 
myCommand.CommandText = ProcName 
myCommand.Connection = myConn 
'定義一個(gè)數(shù)據(jù)適配器，并設(shè)置參數(shù) 
Dim myDapter As New SqlClient.SqlDataAdapter(myCommand) 
myDapter.SelectCommand.Parameters.Add("@name", SqlDbType.VarChar, 20).Value = Param1 
'定義一個(gè)數(shù)據(jù)集對(duì)象，并填充數(shù)據(jù)集 
Dim myDataSet As New DataSet 
Try 
myDapter.Fill(myDataSet) 
Catch ex As Exception 
End Try 
Return myDataSet 
End Function 
'存儲(chǔ)過程代碼 
Create Proc Test @name varchar(20) As 
Select * From EC_Grade where cGradeName=@name 
GO 
***如果將存儲(chǔ)過程修改部分內(nèi)容,可以做為查詢使用 
CREATE Proc Test 
@name varchar(200)='' 
--此處應(yīng)該注意200為查詢條件的長度,可以根據(jù)實(shí)際情況而定;但不建議用于過長的查詢條件 
As 
Declare @sql1 varchar(8000) 
if @name<>'' 
Select @sql1='Select * From EC_Grade where '+ @name 
else 
Select @sql1='Select * From EC_Grade' 
exec(@sql1) 
GO 