定义数据链接部分省略, myConn为链接对象 ProcName为存储过程名
1&＃xff0e;调用存储过程&＃xff0c;但无返回值
Private Function SqlProc1(ByVal ProcName As String) As Boolean
&＃39;定义数据链接部分省略, myConn为链接对象 ProcName为存储过程名
Dim myCommand As New SqlClient.SqlCommand(ProcName, myConn)
With myCommand
.CommandType &＃61; CommandType.StoredProcedure
.Parameters.Add("&＃64;CodeType", SqlDbType.VarChar, 20).Value &＃61; "年级编码"
Try
.ExecuteNonQuery()
Return True
Catch ex As Exception
Return False
End Try
End Function
2&＃xff0e;调用存储过程&＃xff0c;返回普通值
Private Function SqlProc1(ByVal ProcName As String) As String
&＃39;定义数据链接部分省略, myConn为链接对象
Dim myCommand As New SqlClient.SqlCommand(ProcName, myConn)
With myCommand
.CommandType &＃61; CommandType.StoredProcedure
.Parameters.Add("&＃64;CodeType", SqlDbType.VarChar, 20).Value &＃61; "年级编码"
.Parameters.Add("&＃64;NewCode", SqlDbType.VarChar, 20).Direction &＃61; ParameterDirection.Output
Try
.ExecuteNonQuery()
Return .Parameters(1).Value()
Catch ex As Exception
Return "无编码生成"
End Try
End Function
3&＃xff0e;调用存储过程&＃xff0c;返回数据集
&＃39;VB.NET代码
Private Function SqlProc2(ByVal ProcName As String, ByVal Param1 As String) As DataSet
&＃39;定义命令对象&＃xff0c;并使用储存过程
Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandType &＃61; CommandType.StoredProcedure
myCommand.CommandText &＃61; ProcName
myCommand.Connection &＃61; myConn
&＃39;定义一个数据适配器&＃xff0c;并设置参数
Dim myDapter As New SqlClient.SqlDataAdapter(myCommand)
myDapter.SelectCommand.Parameters.Add("&＃64;name", SqlDbType.VarChar, 20).Value &＃61; Param1
&＃39;定义一个数据集对象&＃xff0c;并填充数据集
Dim myDataSet As New DataSet
Try
myDapter.Fill(myDataSet)
Catch ex As Exception
End Try
Return myDataSet
End Function
&＃39;存储过程代码
Create Proc Test &＃64;name varchar(20)As
Select * From EC_Grade where cGradeName&＃61;&＃64;name
GO
***如果将存储过程修改部分内容,可以做为查询使用
| CREATEProc Test &＃64;name varchar(200)&＃61;&＃39;&＃39; --此处应该注意200为查询条件的长度,可以根据实际情况而定;但不建议用于过长的查询条件 As Declare &＃64;sql1 varchar(8000) if &＃64;name<>&＃39;&＃39; Select &＃64;sql1&＃61;&＃39;Select * From EC_Grade where &＃39;&＃43; &＃64;name else Select &＃64;sql1&＃61;&＃39;Select * From EC_Grade&＃39; exec(&＃64;sql1) GO |
京公网安备 11010802041100号