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.

Posted in Uncategorized | Comments Off on Table Value Parameters in an Update