1

Right now I have the following C#/T-SQL code for adding or updating an individual element in a table

    public async Task AddOrUpdateAnswer ( AnswerSubmission Answer )
    {
        await this._Conn.OpenAsync();
        using (SqlCommand cmd = new SqlCommand("AddOrUpdateAnswer", this._Conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@AnswerVal", Answer.AnswerVal);
            cmd.Parameters.AddWithValue("@QuestionId", Answer.QuestionId);
            cmd.Parameters.AddWithValue("@PartnerId", Answer.PartnerId);
            await cmd.ExecuteNonQueryAsync();
        }
        this._Conn.Close();
    }

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

which is a method I use to auto-save information in an online form that is filled out. But I also want a procedure that I call on page unload that updates all the answers on the page (just in case an individual update was missed) by sending them all to the server in a single HTTP request and ideally updating them in the data base.

So, how do I change this to a method that does the same thing except with a List<AnswerSubmission> and a single stored procedure (of course I can still do the individual answer updates -- I'll just do those with a List<AnswerSubmission> containing 1 element)??? Can I pass in a "table" to cmd?

Subpar Web Dev
  • 3,210
  • 7
  • 21
  • 35
  • You *could* pass XML from your controller to the database, deserialise it to a temp table and merge the data... – Rowland Shaw Mar 11 '16 at 17:22
  • yes you can send a Datatable to a SQL Procedure, to do that you need to do: create a user table datatype on SQL server, then use it in your procedure as a readonly parameter, then you can handle it as a regular table varible if you need to get data from it you just do a simple select on it and thats it. – thepanch Mar 15 '16 at 15:43

0 Answers0