Executing a batch of add or updates: What is the most efficient way, and where am I going wrong?

Question

Let me explain my situation in full.

I have a JavaScript object like

[{"QuestionId":"1","AnswerId":"21","AnswerText":"34"},
 {"QuestionId":"2","AnswerId":"22","AnswerText":"23"},...]

which I need to use to either insert or update rows of a T-SQL table generated by

CREATE TABLE Answers ( 
    id INT IDENTITY (1,1),
    question_id INT NOT NULL,
    partner_id UNIQUEIDENTIFIER NOT NULL,
    val INT,
    PRIMARY KEY (id),
    FOREIGN KEY (question_id) REFERENCES Questions(id),
    FOREIGN KEY (partner_id) REFERENCES Partners(id)
);

The AJAX call is

        var QAs = new Array();
        $('.completeness.for-answer').each(function () {
            QAs.push({
                QuestionId : $(this).attr('data-qstnid'),
                AnswerId : $(this).attr('data-answid'),
                AnswerText : $(this).val(),
            });
        });
        console.log(JSON.stringify(QAs));//TEST
        $.ajax({
            method: 'POST',
            url: '/Answers/SubmitAnswers',
            data : QAs,
            success: function (retobj) {
                console.log(retobj);
            },
            error: function () {
                console.log("Error ...");
            }
        });

Each item in the list of updates, when it is transmitted to the database in the form of executing a stored procedure

CREATE PROCEDURE AddOrUpdateAnswer
    @AnswerId INT,
    @AnswerVal INT,
    @QuestionId INT,
    @PartnerId UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON
    IF EXISTS ( SELECT 1 FROM Answers WHERE id=@AnswerId )
        UPDATE Answers SET val=@AnswerVal WHERE id=@AnswerId
    ELSE
        INSERT INTO Answers (question_id, partner_id, val) VALUES (@QuestionId, @PartnerId, @AnswerVal)
END

The reason for the condition is because the input will be in 1 of 2 forms:

1. @AnswerId will be NULL (the input corresponds to an answer that has not yet been submitted) and the other values will be defined. The other values are needed to
2. @AnswerId will be non-NULL and the other values will be defined although @PartnerId and @QuestionId are irrelevant for the update because that information is already within the table relations.

First question: Is that sproc the most efficient way of doing an "Add or Update"? Is it possible to do an "Add or Update" that brings in all the items at once? Because, you see, I'm calling this on each item.

The controller I'm using to handle the AJAX request that passes in the JavaScript object is

    [HttpPost]
    public ActionResult SubmitAnswers ( List<AnswerSubmission> Answers )
    {
        bool goodSoFar = true;
        string status = "Answers submitted successfully";
        try
        {
            this._Db.SubmitAnswers(Answers, this._Pid);
        }
        catch ( Exception e )
        {
            goodSoFar = false;
            status = String.Format("Exception occured during answer submission: {0}", e.Message);
        }             
        return Json(new { Succeeded = goodSoFar, Message = status } );
    }

where AnswerSubmission is defined by

public class AnswerSubmission
{
    public int? AnswerId { get; set; }
    public int? AnswerVal { get; set; }
    public int QuestionId { get; set; }
}

The function SubmitAnswers looks like

    public void SubmitAnswers ( List<AnswerSubmission> Answers, Guid PartnerId )
    {
        try
        {
            foreach ( AnswerSubmission A in Answers )
            {
                using ( SqlCommand cmd = new SqlCommand("AddOrUpdateAnswer", this._Conn) )
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@AnswerId", A.AnswerId);
                    cmd.Parameters.AddWithValue("@AnswerVal", A.AnswerVal);
                    cmd.Parameters.AddWithValue("@QuestionId", A.QuestionId);
                    cmd.Parameters.AddWithValue("@PartnerId", PartnerId);
                    this._Conn.Open();
                    cmd.ExecuteNonQuery();
                    this._Conn.Close();
                }    
            }

        } catch ( Exception ) { throw; } 
    }

Second question: Is that the correct way, with an Opening and then Closeing of the connection for each item? That seems like it would be inefficient. Is there a better way?

Third question: How do I debug an AJAX method like this? I've set up breakpoints all over the place in my controller, but none of them are getting hit. The exception that is getting thrown, which I see in the object returned by the success function of my JavaScript, says

"Exception occured during answer submission: Object reference not set to an instance of an object."

but doesn't point me to any line in my code. So I have no idea which object reference is not set to an instance of an object.

Any help greatly appreciated here.

Answer 1

1. That SQL is not bad, but it could be better. You could be using a MERGE statement, which is commonly known as an upsert ("Update or Insert"). However, if you have a lot of answers to insert, I would suggest you use a table valued parameter (TVP) to pass all your records at once to SQL Server.

2. Usually you're connection would be wrapped in a using statement as well so that you appropriately dispose of the connection. It's not clear from the code posted how you might be handling that. If you are going to use a TVP, you'll definitely be rewriting the loop a bit.

3. If it's not hitting your controller, then potentially your routing is wrong and you're going to the wrong place, or something in the pipeline (an added handler?) is trying to do something (like log a request) and that is failing before it makes it to your controller

Answer 2

A. I don't see anywhere in your code where you are passing the @PartnerId value. Your QAs object in your AJAX doesn't have that part.

Answering your questions:

1. Your sproc should change a bit to accommodate a null @AnswerId.

   CREATE PROC AddOrUpdateAnswer {
       @PartnerId UNIQUEIDENTIFIER,
       @QuestionId INT,
       @AnswerId INT = NULL,
       @AnswerVal INT = NULL
   }
   

   and then further on...

   IF EXISTS (SELECT Id FROM Answers WHERE Id = ISNULL(@AnswerId, -1))
   

2. Move this._Conn.Open() and this._Conn.Close() outside the foreach statement.

3. I find it difficult to debug AJAX calls since they may run under a different Thread. Put a breakpoint on the entry method of your ajax call - /Answers/SubmitAnswers, then create a simple test form that you can post directly from the view to see if it works.