calling a stored procedure from C# using SqlDataAdapter

Question

I have a stored procedure which has been well tested and works perfectly from SQL Server Management Studio. All the procedure does is check for the existence of a record in a table, return it if it exists, or create it and then return it if it doesn't.

The procedure looks like this:

CREATE proc [dbo].[spInsertSerialBatch] 

@MOS_JOB varchar(12), --PASSED COMMAND LINE
@MOS_LOT varchar(4) = NULL, --PASSED COMMAND LINE
@MES_USER varchar(12) = 'NOT PASSED',--PASSED COMMAND LINE
@COMPUTERNAME varchar(100) = 'NOT PASSED' --ENVIRONMENT VARIABLE

as ....

I use a SqlDataAdapter, which I have used repeatedly without any problems. The setup looks like this:

    using (SqlCommand sqlComm = new SqlCommand("dbo.spInsertSerialBatch", serialBatchDataConnection))
                    {
                        if (serialBatchDataConnection.State != ConnectionState.Open)
                        {
                            serialBatchDataConnection.Open();
                        }



sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.Parameters.AddWithValue("@MOS_JOB", options.jobNumber);
                    sqlComm.Parameters.AddWithValue("@MOS_LOT", options.lotNumber);
                    sqlComm.Parameters.AddWithValue("@MES_USER", options.userId);
                    sqlComm.Parameters.AddWithValue("@COMPUTERNAME", System.Environment.MachineName);

                    SqlDataAdapter sda = new SqlDataAdapter(sqlComm);
                    DataTable dt = new DataTable();

                    int rowsAffected = sda.Fill(dt);

}

I then examine the results of the table after Fill is executed. It works fine when the row exists in the table, but if it doesn't, and the stored proc needs to generate it, Fill returns 0 rows and the data table remains empty. No errors/exceptions are thrown, I just get no results.

I suppose I could change the code to use ExecuteNonQuery and not use the DataAdapter, but I see no reason why this shouldn't just work; I prefer having a data table (which may result in more than a single row in some cases) than using a data reader and looping over the data to get the results.

Any suggestions as to why this might fail? I've looked over several posts on this and other sites that are similar, but haven't found a satisfactory answer. Any suggestions are greatly appreciated.

Thanks,

Gary

The entrire sp is quite large and probably too proprietary to publish...

        --return inserted rows
        SELECT 'CREATED' as [spRESULT], o.* 
        FROM @output o 

        END         

/*
    * Return existing SerialBatch(s)
    */
    BEGIN

        SELECT 'RETRIEVED' as [spRESULT], s.* 
        FROM SerialBatch s
        WHERE SerialBatchId = @formattedId

        UNION 
        /* 
        * pull in products that have components that need labels as well
        */
        SELECT 'RETRIEVED' as [spRESULT],s.* 
        FROM SerialBatch s
        WHERE SerialBatchParentId = @formattedId
    END 

This is the end of the stored procedure. I tried executing a DataReader instead and the result is the same...I get no results for the case when the sp has to create it, but again it runs perfectly stand-alone in SQL Server Management Studio.

Answer 1

Problem solved. Turns out that the OpenQuery string passed to Oracle was converting an empty string to a NULL and preventing the new row from being returned. All I need to add was a check for both NULL and empty string:

if @MOS_LOT IS NULL or @MOS_LOT = ''
    set @MOS_LOT = ' ' --EMPTY STRINGS BEING EQUIVALENT TO NULLS