Return Resultset from SQL Server to VB.NET application

Question

I need to return a resultset consisting of database errors from a SQL Server stored procedure's CATCH clause but I'm stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried Object and Variant but did not work.

I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH clause:

 while (@I <= @count)
 begin      
    BEGIN TRY       
        -- delete all previous rows inserted in @customerRow for previous counts @I     
        delete from @customerRow

        -- this is inserting the current row that we want to save in database
        insert into @customerRow 
           SELECT  
               [id],[firstname], [lastname], [street], [city],
               [phone],[mobile],[fax], [email], [companyName],
               [licence],[brn], [vat], [companyStreet], [companyCity], [status]
           FROM 
               (SELECT  
                   ROW_NUMBER() OVER (ORDER BY id ASC) AS rownumber,
                   [id], [firstname], [lastname], [street], [city],
                   [phone], [mobile], [fax], [email], [companyName],
                   [licence], [brn], [vat], [companyStreet], [companyCity], [status]
                FROM    
                   @registerDetails) AS foo
          WHERE 
             rownumber = @I                                             

       -- this stored procedure handles the saving of the current customer row just defined above
        -- if there is any error from that sproc, it will jump to CATCH block 
        --save the error message in the temp table and continue 
        --with the next customer row in the while loop.
        exec dbo.sp_SaveCustomer @customerRow
    END TRY
    BEGIN CATCH
        IF @@TranCount = 0
        -- Transaction started in procedure.
        -- Roll back complete transaction.
        ROLLBACK TRANSACTION;
        if XACT_STATE()= -1 rollback transaction

        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;


        SELECT @ErrorMessage = ERROR_MESSAGE() + ' ' + (select firstname from @registerDetails where id=@I)
        SELECT @ErrorSeverity = ERROR_SEVERITY();
        SELECT @ErrorState = ERROR_STATE() 

        INSERT INTO #registrationResults (error,id)
        SELECT @ErrorMessage, @I 

    END CATCH       

    set @I = @I +1              
end 
COMMIT TRANSACTION registerTran

select * from #registrationResults

The above works with one stored procedure when I call it in my vb.net code as :

ta.Fill(registrationErrors, clientDetailsDT)

where registrationErrors and clientDetailsDT are strongly typed data tables.

This one does not :

    begin catch
    IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    DECLARE @ErrorLine INT;


    SELECT @ErrorMessage = ERROR_MESSAGE();
    SELECT @ErrorSeverity = ERROR_SEVERITY();
    SELECT @ErrorState = ERROR_STATE();
    SELECT @ErrorLine = ERROR_Line();

    ****ERROR -- THIS SELECT WAS MESSING ALL UP as it was this select that was being        returned to the .NET and not the select of the desired #temp table after, hence returning 0    resultset as this select was EMPTY. !!  
    select status_indicator from InsertInvoiceTriggerData where session_GUID =   guid**
    delete from InsertInvoiceTriggerData where session_GUID = @guid**

    INSERT INTO #registrationResults (error,id)
    SELECT @ErrorMessage, NULL

    select * from #registrationResults

end catch

Any suggestions how to return resultsets?

Answer 1

I haven't seen your database code, but in my experience the very first error caught by catch means that the entire transaction has to be rolled back. Apart from other things, it also implies that I never have more than 1 error to return in any given situation.

As such, I use 2 scalar output parameters in my stored procedures, that is:

@Error int = null output,
@Message nvarchar(2048) = null output

And I can retrieve them just like any other output variables.

UPD: Even after you have added some code, I still fail to understand what is your problem, exactly. However, I see several problems with your code, so I'll point them out and chances are, one of them will solve the problem. I am commenting only the first snippet, since the last one is too incomplete.

• You should have been started the outermost transaction somewhere before the loop. If not, the code will fail.
• If I guessed correctly, you implemented all savepoint logic inside the dbo.sp_SaveCustomer stored proc. If not, the whole discussion is pointless, since there are no save tran or rollback @savepoint statements in the code you have shown.
• The first catch statement - IF @@TranCount = 0 ROLLBACK TRANSACTION is all wrong. If the condition is successful, it will result in error trying to rollback nonexistent transaction. Should not be here if you rely on savepoints.
• The next after it should result in unconditional break: if XACT_STATE()= -1 begin rollback transaction; break; end;
• The rest of your catch code can be replaced with this: INSERT INTO @registrationResults (error, id) SELECT error_message() + ' ' + firstname, id from @registerDetails where id=@I;
• Also, never use temp tables for this purpose, because rollback will affect them as well. Always use table variables for this, they are non-transactional (just like any other variable).
• The commit should be conditional, because you may end up at this point with no transaction to commit: if @@trancount > 0 commit tran;
• There is no point in specifying savepoint name in the commit statement, it only leads to confusion (though isn't considered an error). Also, there should not any savepoint in this module (unless you have defined it before the loop).

I suspect that's just the tip of the iceberg, since I have no idea what actually happens inside the dbo.SaveCustomer stored procedure.

UPD2: Here is a sample of my VB.NET code which I use to receive recordsets from stored procedures:

Private Function SearchObjectsBase( _
    SearchMode As penum_SEARCH_MODE, SearchCriteria As String
) As DataSet

Dim Cmd As DbCommand, Pr As DbParameter, dda As DbDataAdapter

' Initialise returning dataset object
SearchObjectsBase = New DataSet()

Cmd = MyBase.CreateCommand(String.Format("dbo.{0}", SearchMode))

With Cmd

    ' Parameter definitions
    Pr = .CreateParameter()
    With Pr
        .ParameterName = "@SearchCriteria"
        .DbType = DbType.Xml
        .Value = SearchCriteria
    End With
    .Parameters.Add(Pr)

    ' Create data adapter to use its Fill() method
    dda = DbProviderFactories.GetFactory(.Connection).CreateDataAdapter()
    ' Assign the prepared DbCommand as a select method for the adapter
    dda.SelectCommand = Cmd

    ' A single resultset is expected here
    dda.Fill(SearchObjectsBase)

End With

' Set error vars and get rid of it
Call MyBase.SetErrorOutput(Cmd)

' Check for errors and, if any, discard the dataset
If MyBase.ErrorNumber <> 0 Then SearchObjectsBase.Clear()

End Function

I use .NET 4.5, which has a very nice method to automatically select the most appropriate data adapter based on the actual connection. And here is a call of this function:

Dim XDoc As New XElement("Criteria"), DS As DataSet = Nothing, DT As DataTable
...
DS = .SearchPatients(XDoc.ToString(SaveOptions.None))
' Assign datasource to a grid
Me.dgr_Search.DataSource = DS.Tables.Item(0)

Here, SearchPatients() is a wrapper on top of the SearchObjectsBase().