VB.NET如何调用带返回参数和数据集的“存储过程”
VB.NET如何调用带返回参数和数据集的“存储过程”比如下面的存储过程
--------------------编程问答-------------------- /// 执行一个存储过程，并且返回存储过程的返回值。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[SSD_sp_AS_scjh]
@AAA char(2),
@lcErrMsg varchar(500) output
@dsfs varchar(40) output
AS
SELECT @dsfs='dfas'
IF @AAA=''
BEGIN
SELECT @lcErrMsg='ASSADFSAD'
RETURN
END
select * from asdfasd
/// 存储过程中必须包含 RETURN 语句，否则方法返回 null。
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameters">存储过程的参数</param>
/// <returns>如果存储过程无返回值或返回值为 null，则方法返回 null。否则方法返回存储过程的 RETURN 值。</returns>
public static object GetProcReturnValue(string procedureName, SqlParameter[] parameters) {
object obj = null;
using (SqlConnection con = new SqlConnection(ConnectionString)) {
SqlCommand cmd = CreateCommand(procedureName, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pReturnValue = cmd.CreateParameter();
pReturnValue.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(pReturnValue);
if (parameters != null)
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
obj = pReturnValue.Value;
}
return obj;
} --------------------编程问答-------------------- 刚好我正在写你看下吧
Private sqlcon As SqlConnection
Private sqlcmd As SqlCommand
Private sqladp As SqlDataAdapter
Private ID As Integer
Public Property _ID() As Integer
Get
Return ID
End Get
Set(ByVal value As Integer)
Me.ID = value
End Set
End Property
Private GroupID As String
Public Property _GroupID() As String
Get
Return Me.GroupID
End Get
Set(ByVal value As String)
Me.GroupID = value
End Set
End Property
Private ItemID As String
Public Property _ItemID() As String
Get
Return Me.ItemID
End Get
Set(ByVal value As String)
Me.ItemID = value
End Set
End Property
Private Rights As String
Public Property _Rights() As String
Get
Return Me.Rights
End Get
Set(ByVal value As String)
Me.Rights = value
End Set
End Property
Public Function select_Rights() As DataSet
Return GetDataSet("ss", Nothing)
End Function
Private Function CreateCmd(ByRef sqlcon As SqlConnection, ByVal sqltxt As String, ByRef paras() As IDataParameter) As SqlCommand
Me.sqlcmd = New SqlCommand(sqltxt, sqlcon)
Me.sqlcmd.CommandType = CommandType.StoredProcedure
If Not paras Is Nothing Then
For Each para As SqlParameter In paras
Me.sqlcmd.Parameters.Add(para)
Next
End If
Return Me.sqlcmd
End Function
Private Function ExecuteNonQuery(ByRef sqlcontxt As String, ByRef paras() As IDataParameter) As Integer
Me.sqlcon = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Me.sqlcmd = CreateCmd(Me.sqlcon, sqlcontxt, paras)
Try
Me.sqlcon.Open()
Return Me.sqlcmd.ExecuteNonQuery()
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Private Function GetDataSet(ByRef sqlcontxt As String, ByRef paras() As IDataParameter) As DataSet
Me.sqlcon = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Me.sqlcmd = CreateCmd(Me.sqlcon, sqlcontxt, paras)
Me.sqladp = New SqlDataAdapter(Me.sqlcmd)
Dim ds As New DataSet()
sqladp.Fill(ds)
Return ds
End Function
没写注释凑合看吧 --------------------编程问答-------------------- Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlServer").ToString())
conn.Open()
Dim MyCommand As New SqlCommand("MYSQL", conn)
MyCommand.CommandType = CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlParameter("@a", SqlDbType.Int))
MyCommand.Parameters("@a").Value = 20
MyCommand.Parameters.Add(New SqlParameter("@b", SqlDbType.Int))
MyCommand.Parameters("@b").Direction = ParameterDirection.Output
MyCommand.ExecuteNonQuery()
Response.Write(MyCommand.Parameters("@b").Value.ToString())
End Using
补充：.NET技术 , VB.NET
