CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
noelicus
noelicus

Reputation: 15055

Passing many values to a SQL Server stored procedure

I currently have this function (see below) which adds answers and related content (e.g. notes) to 50 questions or so. I'm trying to make the database secure by changing it to a stored procedure so I can remove the write permission from the users. Is this the right approach?

Should I use table-valued parameters? Or should I call the stored procedure 50 times? The former feels like it's right, but the latter would look nicer in code although I expect is inefficient?

Or ... is there a better way/approach?

int AddAnswersToTable()
{
    var Cmd = Db.NewCommand();
    var Str = new StringBuilder("INSERT INTO answers (qu_id,answer,notes) VALUES");
    int i = 1;
    string Join = string.Empty;

    foreach (var Qu in Page)
    {
        Str.Append(Join);
        Str.Append("(");

        Str.Append((Name = "@qu_id" + i) + ",");
        Cmd.Parameters.AddWithValue(Name, Qu.QuId);

        Str.Append((Name = "@ans" + i) + ",");
        Cmd.Parameters.AddWithValue(Name, Qu.Answer.Truncate(499));

        Str.Append((Name = "@notes" + i) + ",");
        Cmd.Parameters.AddWithValue(Name, Qu.Notes.Truncate(499));

        Str.Append(")");
        Join = ",";
        i++;
    }

    return Cmd.ExecuteNonQuery(Str.ToString());
}

Thanks in advance.

Upvotes: 1

Views: 147

Answers (2)

podiluska
podiluska

Reputation: 51494

Whether a stored procedure is the right approach is a religious matter. I believe it is, FWIW.

Calling the procedure 50 times is simpler, but neither approach is wrong, per se.

Upvotes: 1

Björn
Björn

Reputation: 3418

I haven't used Table Valued Parameters so I can't say if that is the way to go. But another option would be to do batch updates using SqlDataAdapter. By setting the "UpdateBatchSize" property you can send multiple values/rows at a time.

You can read about it at MSDN: http://msdn.microsoft.com/en-us/library/aadf8fk2%28v=vs.100%29.aspx

And here is an article where a stored procedure is used: http://www.dotnetspider.com/resources/4467-Multiple-Inserts-Single-Round-trip-using-ADO-NE.aspx

Upvotes: 1

Related Questions