Updating

PetaPoco supports very flexible methods for updating data. The easiest way to demonstrate how to update data is probably through a couple of working examples. However, first, we should cover the API as these working examples will be of course making use of it.

The API

/// <summary>
///     Performs an SQL update
/// </summary>
/// <param name="tableName">The name of the table to update</param>
/// <param name="primaryKeyName">The name of the primary key column of the table</param>
/// <param name="poco">The POCO object that specifies the column values to be updated</param>
/// <param name="primaryKeyValue">The primary key of the record to be updated</param>
/// <returns>The number of affected records</returns>
int Update(string tableName, string primaryKeyName, object poco, object primaryKeyValue);

/// <summary>
///     Performs an SQL update
/// </summary>
/// <param name="tableName">The name of the table to update</param>
/// <param name="primaryKeyName">The name of the primary key column of the table</param>
/// <param name="poco">The POCO object that specifies the column values to be updated</param>
/// <param name="primaryKeyValue">The primary key of the record to be updated</param>
/// <param name="columns">The column names of the columns to be updated, or null for all</param>
/// <returns>The number of affected rows</returns>
int Update(string tableName, string primaryKeyName, object poco, object primaryKeyValue, IEnumerable<string> columns);

/// <summary>
///     Performs an SQL update
/// </summary>
/// <param name="tableName">The name of the table to update</param>
/// <param name="primaryKeyName">The name of the primary key column of the table</param>
/// <param name="poco">The POCO object that specifies the column values to be updated</param>
/// <returns>The number of affected rows</returns>
int Update(string tableName, string primaryKeyName, object poco);

/// <summary>
///     Performs an SQL update
/// </summary>
/// <param name="tableName">The name of the table to update</param>
/// <param name="primaryKeyName">The name of the primary key column of the table</param>
/// <param name="poco">The POCO object that specifies the column values to be updated</param>
/// <param name="columns">The column names of the columns to be updated, or null for all</param>
/// <returns>The number of affected rows</returns>
int Update(string tableName, string primaryKeyName, object poco, IEnumerable<string> columns);

/// <summary>
///     Performs an SQL update
/// </summary>
/// <param name="poco">The POCO object that specifies the column values to be updated</param>
/// <param name="columns">The column names of the columns to be updated, or null for all</param>
/// <returns>The number of affected rows</returns>
int Update(object poco, IEnumerable<string> columns);

/// <summary>
///     Performs an SQL update
/// </summary>
/// <param name="poco">The POCO object that specifies the column values to be updated</param>
/// <returns>The number of affected rows</returns>
int Update(object poco);

/// <summary>
///     Performs an SQL update
/// </summary>
/// <param name="poco">The POCO object that specifies the column values to be updated</param>
/// <param name="primaryKeyValue">The primary key of the record to be updated</param>
/// <returns>The number of affected rows</returns>
int Update(object poco, object primaryKeyValue);

/// <summary>
///     Performs an SQL update
/// </summary>
/// <param name="poco">The POCO object that specifies the column values to be updated</param>
/// <param name="primaryKeyValue">The primary key of the record to be updated</param>
/// <param name="columns">The column names of the columns to be updated, or null for all</param>
/// <returns>The number of affected rows</returns>
int Update(object poco, object primaryKeyValue, IEnumerable<string> columns);

/// <summary>
///     Performs an SQL update
/// </summary>
/// <typeparam name="T">The POCO class who's attributes specify the name of the table to update</typeparam>
/// <param name="sql">The SQL update and condition clause (ie: everything after "UPDATE tablename"</param>
/// <param name="args">Arguments to any embedded parameters in the SQL</param>
/// <returns>The number of affected rows</returns>
int Update<T>(string sql, params object[] args);

/// <summary>
///     Performs an SQL update
/// </summary>
/// <typeparam name="T">The POCO class who's attributes specify the name of the table to update</typeparam>
/// <param name="sql">
///     An SQL builder object representing the SQL update and condition clause (ie: everything after "UPDATE
///     tablename"
/// </param>
/// <returns>The number of affected rows</returns>
int Update<T>(Sql sql);

Examples

The examples below are taken from our integration tests.

[Collection("MssqlTests")]
public class Updates : BaseDatabase
{
    public Updates()
        : base(new MssqlDBTestProvider())
    {
        PocoData.FlushCaches();
    }

    [Fact]
    public void Update()
    {
        // Create and insert the person
        var person = new Person { Id = Guid.NewGuid(), Name = "PetaPoco", Dob = new DateTime(2011, 1, 1), Age = (DateTime.Now.Year - 2011), Height = 242 };
        var id = DB.Insert(person);

        // Update a few properties of the person
        person.Age = 70;
        person.Name = "The PetaPoco";

        // Tell PetaPoco to update the DB
        DB.Update(person);

        // Get a clone/copy from the DB
        var clone = DB.Single<Person>(id);

        // See, the person has been updated
        clone.Id.ShouldBe(person.Id);
        clone.Dob.ShouldBe(person.Dob);
        clone.Height.ShouldBe(person.Height);
        clone.Age.ShouldBe(person.Age);
        clone.Name.ShouldBe(person.Name);
    }

    [Fact]
    public void UpdatePartial()
    {
        // Create and insert the person
        var person = new Person { Id = Guid.NewGuid(), Name = "PetaPoco", Dob = new DateTime(2011, 1, 1), Age = (DateTime.Now.Year - 2011), Height = 242 };
        var id = DB.Insert(person);

        // Update a few properties of the person
        person.Age = 70;
        person.Name = "The PetaPoco";

        // Get the poco data
        var pocoData = PocoData.ForType(person.GetType(), DB.DefaultMapper);

        // Tell PetaPoco to update only the person's name
        // The update statement produced is `UPDATE [People] SET [FullName] = @0 WHERE [Id] = @1`
        DB.Update(person, new [] { pocoData.GetColumnName(nameof(Person.Name)) });

        // Get a clone/copy from the DB
        var clone = DB.Single<Person>(id);

        // See, the person has been updated, but only the name
        clone.Id.ShouldBe(person.Id);
        clone.Dob.ShouldBe(person.Dob);
        clone.Height.ShouldBe(person.Height);

        clone.Age.ShouldNotBe(70);
        clone.Name.ShouldBe("The PetaPoco");
    }

    [Fact]
    public void UpdateToDifferentTable()
    {
        // Create the and insert the person
        var person = new Person { Id = Guid.NewGuid(), Name = "PetaPoco", Dob = new DateTime(2011, 1, 1), Age = (DateTime.Now.Year - 2011), Height = 242 };
        var id = DB.Insert("SpecificPeople", "Id", person);

        // Update a few properties of the person
        person.Age = 70;
        person.Name = "The PetaPoco";

        // Tell PetaPoco to update the DB table SpecificPeople
        // The update statement produced is `UPDATE [SpecificPeople] SET [FullName] = @0, [Age] = @1, [Height] = @2, [Dob] = @3 WHERE [Id] = @4`
        DB.Update("SpecificPeople", "Id", person);

        // We need to get the clone/copy from the correct table
        // Note: we can't use auto select builder here because PetaPoco would create columns such as People.Id
        var clone = DB.Query<Person>("SELECT * FROM [SpecificPeople] sp WHERE sp.[Id] = @0", id).Single();

        // See, the person has been updated
        clone.Id.ShouldBe(person.Id);
        clone.Dob.ShouldBe(person.Dob);
        clone.Height.ShouldBe(person.Height);
        clone.Age.ShouldBe(person.Age);
        clone.Name.ShouldBe(person.Name);
    }

    [Fact]
    public void UpdateConventionalPoco()
    {
        // Clear out any notes and reset the ID sequence counter
        DB.Execute("TRUNCATE TABLE [Note]");

        // Insert some notes using all APIs
        var note1 = new Note { Text = "PetaPoco's note", CreatedOn = new DateTime(1948, 1, 11, 4, 2, 4, DateTimeKind.Utc) };
        var note2 = new Note { Text = "PetaPoco's note", CreatedOn = new DateTime(1948, 1, 11, 4, 2, 4, DateTimeKind.Utc) };
        var note3 = new Note { Text = "PetaPoco's note", CreatedOn = new DateTime(1948, 1, 11, 4, 2, 4, DateTimeKind.Utc) };
        var note4 = new Note { Text = "PetaPoco's note", CreatedOn = new DateTime(1948, 1, 11, 4, 2, 4, DateTimeKind.Utc) };
        var note5 = new Note { Text = "PetaPoco's note", CreatedOn = new DateTime(1948, 1, 11, 4, 2, 4, DateTimeKind.Utc) };

        // Each of the API usages here is effectively the same, as PetaPoco is providing the correct unknown values.
        // This is because the poco has been mapped by convention and therefore PetaPoco understands how to do this.
        DB.Insert(note1);
        DB.Insert(note2);
        DB.Insert(note3);
        DB.Insert(note4);
        DB.Insert(note5);

        //Update the notes
        note1.Text += " some more text";
        note2.Text += " some more text";
        note3.Text += " some more text";
        note4.Text += " some more text";
        note5.Text += " some more text";

        // Get the poco data
        var pocoData = PocoData.ForType(typeof(Note), DB.DefaultMapper);

        // Update all notes using all APIs
        DB.Update(note1);
        DB.Update(note2, note2.Id);
        DB.Update(note3, note3.Id, pocoData.UpdateColumns);
        var sql1 = $"SET {DB.Provider.EscapeSqlIdentifier(pocoData.GetColumnName(nameof(Note.Text)))} = @1 " +
                    $"WHERE {DB.Provider.EscapeSqlIdentifier(pocoData.TableInfo.PrimaryKey)} = @0";
        DB.Update<Note>(sql1, note4.Id, note4.Text);
        var sql2 = new Sql($"SET {DB.Provider.EscapeSqlIdentifier(pocoData.GetColumnName(nameof(Note.Text)))} = @1 " +
                            $"WHERE {DB.Provider.EscapeSqlIdentifier(pocoData.TableInfo.PrimaryKey)} = @0", note5.Id, note5.Text);
        DB.Update<Note>(sql2);

        // Just to be sure
        DB.ExecuteScalar<int>("SELECT COUNT(*) FROM [Note] WHERE CAST(Text AS NVARCHAR(MAX)) = @0", "PetaPoco's note some more text").ShouldBe(5);
    }

    [Fact]
    public void UpdateUnconventionalPoco()
    {
        // Create the UnconventionalPocos table
        DB.Execute(@"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA = 'dbo' AND t.TABLE_NAME = 'TBL_UnconventionalPocos')
                            DROP TABLE dbo.[TBL_UnconventionalPocos]

                        CREATE TABLE dbo.[TBL_UnconventionalPocos] (
                            [PrimaryKey] INT IDENTITY(1,1) PRIMARY KEY,
                            [Text] NTEXT NOT NULL
                        )");

        // This POCO is unconventional because, when using the default conventional mapper, PetaPoco won't understand how this poco maps to the database.
        // To understand the power of unconventional mapping, a developer could configure it to work in this situation.
        var poco = new UnconventionalPoco { Text = "PetaPoco" };

        // Insert the poco
        var id = DB.Insert("TBL_UnconventionalPocos", "PrimaryKey", true, poco);

        // Update the poco
        poco.Text += " some more text";
        DB.Update("TBL_UnconventionalPocos", "PrimaryKey", poco);

        // Get a clone/copy from the DB
        var clone = DB.Query<UnconventionalPoco>("SELECT * FROM [TBL_UnconventionalPocos] WHERE [PrimaryKey] = @0", id).Single();

        // Just to be sure
        poco.PrimaryKey.ShouldBe(clone.PrimaryKey);
        poco.Text.ShouldBe(clone.Text);
    }

    [Fact]
    public void UpdateConventionalUnconventionalPoco()
    {
        // Create the UnconventionalPocos table
        DB.Execute(@"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA = 'dbo' AND t.TABLE_NAME = 'TBL_UnconventionalPocos')
                            DROP TABLE dbo.[TBL_UnconventionalPocos]

                        CREATE TABLE dbo.[TBL_UnconventionalPocos] (
                            [PrimaryKey] INT IDENTITY(1,1) PRIMARY KEY,
                            [Text] NTEXT NOT NULL
                        )");

        // Reconfigure the convention mapper
        // Note: I can't think of a valid reason, other than for a purpose such as this, where you would configure the convention mapper in this way.
        ((ConventionMapper) DB.DefaultMapper).MapPrimaryKey = (ti, t) =>
        {
            var prop =  t.GetProperties().FirstOrDefault(p => p.Name == "PrimaryKey");

            if (prop == null)
                return false;

            ti.PrimaryKey = prop.Name;
            ti.AutoIncrement = ((ConventionMapper)DB.DefaultMapper).IsPrimaryKeyAutoIncrement(prop.PropertyType);
            return true;
        };
        ((ConventionMapper) DB.DefaultMapper).InflectTableName = (i, tn) => "TBL_" + tn + "s";

        // Create the POCO
        var poco = new UnconventionalPoco { Text = "PetaPoco" };

        // Tell PetaPoco to insert it
        var id = DB.Insert(poco);

        // Get a clone/copy from the DB
        var clone = DB.SingleOrDefault<UnconventionalPoco>(id);

        // See, they're are the same
        clone.ShouldBe(poco);

        // Update the original poco
        poco.Text += " some more text";

        // Update the poco
        DB.Update(poco);

        // Get the clone from teh database again
        clone = DB.SingleOrDefault<UnconventionalPoco>(id);

        // Confirm the text was updated
        clone.Text.ShouldBe("PetaPoco some more text");
    }

    [Fact]
    public void UpdateAnonymousPocoWithConventionalNaming()
    {
        // Create the table for our unknown but conventional POCO
        DB.Execute(@"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA = 'dbo' AND t.TABLE_NAME = 'XFiles')
                            DROP TABLE dbo.[XFiles]

                        CREATE TABLE dbo.[XFiles] (
                            [Id] INT IDENTITY(1,1) PRIMARY KEY,
                            [FileName] VARCHAR(255) NOT NULL
                        )");

        // Anonymous type are friend of PetaPoco
        var xfile = new { FileName = "Agent Mulder.sec" };

        // Tell PetaPoco to insert it
        var id = DB.Insert("XFiles", "Id", true, xfile);

        // Update the poco
        xfile = new { FileName = "Agent Mulder.sec" };

        // Update the database
        DB.Update("XFiles", "Id", xfile);

        // Get a clone/copy from the DB
        // Note: Check out the name parameters - cool eh?
        var clone = DB.Query<dynamic>("SELECT * FROM [XFiles] WHERE [Id] = @Id", new { Id = id }).Single();

        // See, they're are the same
        id.ShouldBe((int)clone.Id);
        xfile.FileName.ShouldBe((string)clone.FileName);
    }

    [Fact]
    public void InsertDynamicUnknownPocoWithConventionalNaming()
    {
        // Create the table for our unknown but conventional POCO
        DB.Execute(@"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_SCHEMA = 'dbo' AND t.TABLE_NAME = 'XFiles')
                            DROP TABLE dbo.[XFiles]

                        CREATE TABLE dbo.[XFiles] (
                            [Id] INT IDENTITY(1,1) PRIMARY KEY,
                            [FileName] VARCHAR(255) NOT NULL
                        )");

        // Dynamics type are friend of PetaPoco
        dynamic xfile = new System.Dynamic.ExpandoObject();
        xfile.FileName = "Agent Mulder.sec";

        // Tell PetaPoco to insert it
        var id = DB.Insert("XFiles", "Id", true, (object) xfile);

        // Update the poco
        xfile.FileName = "Agent Mulder.sec" ;

        // Update the database
        DB.Update("XFiles", "Id", (object) xfile);

        // Get a clone/copy from the DB
        // Note: Check out the name parameters - cool eh?
        var clone = DB.Query<dynamic>("SELECT * FROM [XFiles] WHERE [Id] = @Id", new { Id = id }).Single();

        // See, they're are the same
        id.ShouldBe((int)clone.Id);
        ((string)xfile.FileName).ShouldBe((string)clone.FileName);
    }
}

[TableName("People")]
[PrimaryKey("Id", AutoIncrement = false)]
public class Person
{
    [Column]
    public Guid Id { get; set; }

    [Column(Name = "FullName")]
    public string Name { get; set; }

    [Column]
    public long Age { get; set; }

    [Column]
    public int Height { get; set; }

    [Column]
    public DateTime? Dob { get; set; }

    [Ignore]
    public string NameAndAge => $"{Name} is of {Age}";

    public void ShouldBe(Person other)
    {
        Id.ShouldBe(other.Id);
        Name.ShouldBe(other.Name);
        Age.ShouldBe(other.Age);
        Height.ShouldBe(other.Height);
        Dob.ShouldBe(other.Dob);
    }
}

public class Note
{
    public int Id { get; set; }

    public DateTime CreatedOn { get; set; }

    public string Text { get; set; }
}

[ExplicitColumns]
[TableName("Orders")]
[PrimaryKey("Id")]
public class Order
{
    [Column]
    public int Id { get; set; }

    [Column]
    public Guid PersonId { get; set; }

    [Column]
    public string PoNumber { get; set; }

    [Column]
    public DateTime CreatedOn { get; set; }

    [Column]
    public string CreatedBy { get; set; }

    [Column("OrderStatus")]
    public OrderStatus Status { get; set; }

    public void ShouldBe(Order other)
    {
        Id.ShouldBe(other.Id);
        PersonId.ShouldBe(other.PersonId);
        PoNumber.ShouldBe(other.PoNumber);
        Status.ShouldBe(other.Status);
        CreatedOn.ShouldBe(other.CreatedOn);
        CreatedBy.ShouldBe(other.CreatedBy);
    }
}

public enum OrderStatus
{
    Pending,
    Accepted,
    Rejected,
    Deleted
}

[TableName("OrderLines")]
[PrimaryKey("Id")]
public class OrderLine
{
    [Column]
    public int Id { get; set; }

    [Column]
    public int OrderId { get; set; }

    [Column(Name = "Qty")]
    public short Quantity { get; set; }

    [Column]
    public decimal SellPrice { get; set; }

    [ResultColumn]
    public decimal Total { get; set; }
}

Table Definitions

The database table definitions used by PetaPoco for the MSSQL documentation and integration tests are shown below.

CREATE TABLE dbo.[People] (
	[Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	[FullName] NVARCHAR(255),
	[Age] BIGINT NOT NULL,
	[Height] INT NOT NULL,
	[Dob] DATETIME NULL
)

CREATE TABLE dbo.[Orders] (
	[Id] INT IDENTITY(1,1) PRIMARY KEY,
	[PersonId] UNIQUEIDENTIFIER FOREIGN KEY REFERENCES dbo.[People](Id),
	[PoNumber] NVARCHAR(15) NOT NULL,
	[OrderStatus] INT NOT NULL,
	[CreatedOn] DATETIME NOT NULL,
	[CreatedBy] NVARCHAR(255) NOT NULL
)

CREATE TABLE dbo.[OrderLines] (
	[Id] INT IDENTITY(1,1) PRIMARY KEY,
	[OrderId] INT NOT NULL FOREIGN KEY REFERENCES dbo.[Orders](Id),
	[Qty] SMALLINT NOT NULL,
	[Status] TINYINT NOT NULL,
	[SellPrice] NUMERIC(10, 4) NOT NULL
)

CREATE TABLE dbo.[SpecificPeople] (
	[Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	[FullName] NVARCHAR(255),
	[Age] BIGINT NOT NULL,
	[Height] INT NOT NULL,
	[Dob] DATETIME NULL
)

CREATE TABLE dbo.[SpecificOrders] (
	[Id] INT IDENTITY(1,1) PRIMARY KEY,
	[PersonId] UNIQUEIDENTIFIER FOREIGN KEY REFERENCES dbo.[SpecificPeople](Id),
	[PoNumber] NVARCHAR(15) NOT NULL,
	[OrderStatus] INT NOT NULL,
	[CreatedOn] DATETIME NOT NULL,
	[CreatedBy] NVARCHAR(255) NOT NULL
)

CREATE TABLE dbo.[SpecificOrderLines] (
	[Id] INT IDENTITY(1,1) PRIMARY KEY,
	[OrderId] INT NOT NULL FOREIGN KEY REFERENCES dbo.[SpecificOrders](Id),
	[Qty] SMALLINT NOT NULL,
	[Status] TINYINT NOT NULL,
	[SellPrice] NUMERIC(10, 4) NOT NULL
)

CREATE TABLE dbo.[TransactionLogs] (
	[Description] NTEXT,
	[CreatedOn] DATETIME NOT NULL
)

CREATE TABLE dbo.[Note] (
	[Id] INT IDENTITY(1,1) PRIMARY KEY,
	[Text] NTEXT NOT NULL,
	[CreatedOn] DATETIME NOT NULL
)