asp.net 存储过程调用 |
时间:2015-01-29 来源:互联网 作者:佚名 |
|
1.调用存储过程,但无返回值 复制代码 代码如下: Private Function SqlProc1(ByVal ProcName As String) As Boolean '定义数据链接部分省略, myConn为链接对象 ProcName为存储过程名 Dim myCommand As New SqlClient.SqlCommand(ProcName, myConn) With myCommand .CommandType = CommandType.StoredProcedure .Parameters.Add("@CodeType", SqlDbType.VarChar, 20).Value = "年级编码" Try .ExecuteNonQuery() Return True Catch ex As Exception Return False End Try End Function
2.调用存储过程,返回普通值 复制代码 代码如下: Private Function SqlProc1(ByVal ProcName As String) As String '定义数据链接部分省略, myConn为链接对象 Dim myCommand As New SqlClient.SqlCommand(ProcName, myConn) With myCommand .CommandType = CommandType.StoredProcedure .Parameters.Add("@CodeType", SqlDbType.VarChar, 20).Value = "年级编码" .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.调用存储过程,返回数据集 'VB.NET代码 复制代码 代码如下: Private Function SqlProc2(ByVal ProcName As String, ByVal Param1 As String) As DataSet '定义命令对象,并使用储存过程 Dim myCommand As New SqlClient.SqlCommand myCommand.CommandType = CommandType.StoredProcedure myCommand.CommandText = ProcName myCommand.Connection = myConn '定义一个数据适配器,并设置参数 Dim myDapter As New SqlClient.SqlDataAdapter(myCommand) myDapter.SelectCommand.Parameters.Add("@name", SqlDbType.VarChar, 20).Value = Param1 '定义一个数据集对象,并填充数据集 Dim myDataSet As New DataSet Try myDapter.Fill(myDataSet) Catch ex As Exception End Try Return myDataSet End Function '存储过程代码 Create Proc Test @name varchar(20) As Select * From EC_Grade where cGradeName=@name GO ***如果将存储过程修改部分内容,可以做为查询使用 CREATE Proc Test @name varchar(200)='' --此处应该注意200为查询条件的长度,可以根据实际情况而定;但不建议用于过长的查询条件 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
|
|
|
|