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

let me explain situation in full.

i have javascript object like

[{"questionid":"1","answerid":"21","answertext":"34"},  {"questionid":"2","answerid":"22","answertext":"23"},...] 

which need use either insert or update rows of 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 list of updates, when transmitted database in form of executing stored procedure

create procedure addorupdateanswer     @answerid int,     @answerval int,     @questionid int,     @partnerid uniqueidentifier begin     set nocount on     if exists ( select 1 answers id=@answerid )         update answers set val=@answerval id=@answerid     else         insert answers (question_id, partner_id, val) values (@questionid, @partnerid, @answerval) end 

the reason condition because input in 1 of 2 forms:

1. @answerid null (the input corresponds answer has not yet been submitted) , other values defined. other values needed
2. @answerid non-null , other values defined although @partnerid , @questionid irrelevant update because information within table relations.

first question: sproc efficient way of doing "add or update"? possible "add or update" brings in items @ once? because, see, i'm calling on each item.

the controller i'm using handle ajax request passes in 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 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 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: correct way, opening , closeing of connection each item? seems inefficient. there better way?

third question: how debug ajax method this? i've set breakpoints on place in controller, none of them getting hit. exception getting thrown, see in object returned success function of javascript, says

"exception occured during answer submission: object reference not set instance of object."

but doesn't point me line in code. have no idea object reference not set instance of object.

any appreciated here.

1. that sql not bad, better. using merge statement, commonly known upsert ("update or insert"). however, if have lot of answers insert, suggest use table valued parameter (tvp) pass records @ once sql server.

2. usually you're connection wrapped in using statement appropriately dispose of connection. it's not clear code posted how might handling that. if going use tvp, you'll rewriting loop bit.

3. if it's not hitting controller, potentially routing wrong , you're going wrong place, or in pipeline (an added handler?) trying (like log request) , failing before makes controller