CODEWITHSUNDEEP
c#databasestored-proceduresado.netbulkinsert
Kobek
Kobek

Reputation: 1211

ADO.NET Call stored procedure efficiently

I have a stored procedure that inserts/updates entries in a DB table. I also have a web service that needs to pass a lot of new updates every couple of seconds into the db.

I want to accomplish this without a major performance impact, however I am not sure how to best approach the issue.

Here is my code (simplified as much as possible)

*Note: I use the datatable since one of the params that the stored procedure uses is a custom User-Defined table type from the DB. 

public class DBWorker
{
    public void Update(IEnumerable<UpdateModel> updates)
    {
        using (SqlConnection connection = new SqlConnection(this._connectionString))
        using (SqlCommand command = new SqlCommand(this._storedProcedureString, connection) { CommandType = CommandType.StoredProcedure })
        {
            foreach (var update in updates)
            {
                command.Parameters.Add("@EntryID", SqlDbType.Int).Value = update.EntryID;
                command.Parameters.Add("@Type", SqlDbType.Int).Value = update.Type;

                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("ParamID");
                dataTable.Columns.Add("Value");
                DataRow row = dataTable.NewRow();
                row["ParamID"] = update.ParamID;
                row["Value"] = update.Value;
                dataTable.Rows.Add(row);

                command.Parameters.Add("@NameValues", SqlDbType.Structured).Value = dataTable;
                command.ExecuteNonQuery();
            }
        }
    }
}

public class UpdateModel
{
    public int EntryID { get; set; }

    public int Type { get; set; }

    public int ParamID { get; set; }

    public string Value { get; set; }
}

My main concern is that the above code will make a query for every update and will dramatically slow down my DP if i have a lot of updates. Is there any way I can call the stored procedure in some "bulk" mode.

I was thinking of creating a datatable with all my results and then somehow passing that to the stored procedure but I am not sure if this will have an actual performance improvement, nor have an idea of how to implement it.

Not sure if it matters but this is the structure of the table that the SP actually updates:

Upvotes: 2

Views: 2039

Answers (1)

Black Light
Black Light

Reputation: 2404

Your going down the right road here, but...

  1. You're creating the datatable, adding a single row, and passing the datatable to the stored procedure. For each UpdateModel. Better to create a row per update (with all four columns - from the properties of your update object), then pass the datatable (now with many rows) to the SP.
  2. Think about what's going on with that command - specifically how may parameters you will add (given that you're re-adding the parameters every time you go around the loop).
  3. Give the columns in the datatable some explicit datatype

For the sequence of updates, say 20, you want to have a datatable with 20 rows, four columns, a command with one structured argument, and call the SP once.

Upvotes: 2

Related Questions