Table Value Parameters in an Update

Table Value Parameters are really powerful, but there’s almost no documentation (here’s the best I found, and this helps a bit) on how to use them in an Update. So here we go:

First create a type in the database (not a table). You do this as follows:

CREATE TYPE ParamsUserStats AS TABLE 

( UserId INT,

  BandwidthThisMonth BIGINT,

  MailsHeldThisMonth BIGINT,

  MostRecentEmail SMALLDATETIME

);

Then you code it up as follows:

protected virtual void _UpdateStatus(IDbConnection conn, IEnumerable<UserStatus> userStatus)

{

 

    // put the params in a table

    DataTable table = new DataTable();

    table.Columns.Add("UserId", typeof(int));

    table.Columns.Add("BandwidthThisMonth", typeof(long));

    table.Columns.Add("MailsHeldThisMonth", typeof (long));

    table.Columns.Add("MostRecentEmail", typeof (DateTime));

    foreach (UserStatus statOn in userStatus)

        table.Rows.Add(statOn.UserId, statOn.AdditionalBandwidth, statOn.AdditionalMailsHeld, statOn.MostRecentEmail);

 

    using (IDbCommand cmd = conn.CreateCommand())

    {

        cmd.CommandText = "update [User] set " +

                          "[User].BandwidthThisMonth = [User].BandwidthThisMonth + stats.BandwidthThisMonth, " +

                          "[User].MailsHeldThisMonth = [User].MailsHeldThisMonth + stats.MailsHeldThisMonth, " +

                          "[User].MostRecentEmail = stats.MostRecentEmail " +

                          "from [User] inner join @TableStats as stats on [User].UserId = stats.UserId";

 

        cmd.Parameters.Add(

            new SqlParameter()

            {

                ParameterName = "@TableStats",

                SqlDbType = SqlDbType.Structured,

                TypeName = "ParamsUserStats",

                Value = table,

            });

 

        cmd.ExecuteNonQuery();

    }

}

 

You are creating a DataTable that matches the type you added to the database. You then join this passed in table to the table you are updating, and the rest is pretty straightforward.