SQL Server Return an Integer from database and store it in a session variable

0qx6xfy6 于 12个月前发布在其他

关注(0)|答案(1)|浏览(114)

In the database I generate a random winning box number and store it. However, I also need to pass @winningBox back to the VB code and store it in Session("winningBox"). I've tried a few different ways but cant figure it. I tried adding:

Dim DR As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While DR.Read
    session("winningBox") = DR("winningBox")
End While

But I'm unsure how or where exactly to add it.

MS SQL

ALTER PROCEDURE [dbo].[addEntrant]
    @accountID   uniqueidentifier,
    @fileID int,
    @boxNo int,
    @buyPrice int

    AS
    BEGIN

    IF NOT EXISTS (SELECT boxNo FROM tblEntrants WHERE fileID=@fileID AND boxNo=@boxNo)
    BEGIN
        INSERT INTO tblentrants (accountID, fileID, boxNo, dateAdded) VALUES (@accountID, @fileID, @boxNo, getDate() )
        UPDATE tblAccounts SET coins=coins-@buyPrice WHERE accountID=@accountID

        IF (SELECT COUNT(*) FROM tblEntrants WHERE fileID=@fileID)=3
        BEGIN
            UPDATE tblItems SET winner =(SELECT ABS(CHECKSUM(NEWID()) % 3) + 1) WHERE fileID=@fileID

            UPDATE tblItems SET sold='c', dateBought=GETDATE() WHERE fileID =@fileID

            UPDATE I
            SET I.buyersAccountID = E.accountID
            FROM tblItems AS I
            INNER JOIN tblEntrants AS E ON I.fileID = @fileID
            AND I.winner = E.boxNo

            DECLARE @winningBox int
            SET @winningBox = (SELECT winner from tblItems WHERE fileID=@fileID)
            RETURN @winningBox

        END
    END
END

VB.Net

Dim DBConnect As New DBConn
Using db As DbConnection = DBConnect.Conn("DBConnectionString")
    Dim cmd As SqlCommand = DBConnect.Command(db, "addEntrant")
    cmd.Parameters.Add(New SqlParameter("accountID", SqlDbType.UniqueIdentifier, ParameterDirection.Input)).Value = acc.accountID
    cmd.Parameters.Add(New SqlParameter("fileID", SqlDbType.Int, ParameterDirection.Input)).Value = Request.QueryString("oid")
    cmd.Parameters.Add(New SqlParameter("boxNo", SqlDbType.Int, ParameterDirection.Input)).Value = boxNo
    cmd.Parameters.Add(New SqlParameter("buyPrice", SqlDbType.Int, ParameterDirection.Input)).Value = CInt(session("buyPrice"))
    db.Open()
    cmd.ExecuteNonQuery()
    cmd.Dispose()
    cmd = Nothing
    db.Dispose()
    db.Close()
        
End Using

sql-server

来源：https://stackoverflow.com/questions/77574521/return-an-integer-from-database-and-store-it-in-a-session-variable

1条答案

按热度按时间

nukf8bse1#

Return in an SQL procedure can work, but it's limited to only integer values. . It really intended for other purposes.

Or you could use an OUTPUT parameter, as suggested by others. Just remember: the OUTPUT parameters must still be defined at the beginning of the procedure, just like the INPUT parameters.

Personally, I find it easiest in most cases to SELECT the data so it's included with a result set.

So let's change the end of the SQL procedure from RETURN @winningBox to SELECT @winningBox . Now you can update the VB.Net like this:

Dim DBConnect As New DBConn
Using db  As DbConnection = DBConnect.Conn("DBConnectionString"), _
      cmd As SqlCommand = DBConnect.Command(db, "addEntrant")

    cmd.Parameters.Add("@accountID", SqlDbType.UniqueIdentifier).Value = acc.accountID
    cmd.Parameters.Add("@fileID", SqlDbType.Int).Value = Request.QueryString("oid")
    cmd.Parameters.Add("@boxNo", SqlDbType.Int).Value = boxNo
    cmd.Parameters.Add("@buyPrice", SqlDbType.Int).Value = CInt(session("buyPrice"))

    db.Open()
    Using DR As SqlDataReader = cmd.ExecuteReader()
       If DR.Read() THEN session("winningBox") = DR("winningBox")          
    End Using      
End Using

Note the extra setting cmd to Nothing at the end of a method harks back to the old VB6 era. This was important back then, but is no longer ever in any way helpful for .Net, and in some rare cases can be actively harmful. You should not set variables to Nothing at the end of a method or scope block anymore; let the garbage collector do it's job in the normal way. In this case, the Using block means you don't even need any of the Close() or Dispose() calls.

But I'm concerned this still won't fix the issue. The RETURN / SELECT is nested two levels deep in IF blocks, so there's no guarantee the line will ever run. You may want to have some kind of fall-back to better account for the possibility no box is selected.

I'm also concerned this is moving "coins" around in multiple steps separate from any transaction. This code cries out for a TRANSACTION with a TRY/CATCH where the last line of the TRY block does a COMMIT and the CATCH block does a ROLLBACK.

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Return an Integer from database and store it in a session variable

1条答案

相关问题

热门标签

最新问答