Saving

PetaPoco supports very flexible methods for saving data. The easiest way to demonstrate how to save 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>
///     Check if a poco represents a new row
/// </summary>
/// <param name="primaryKeyName">The name of the primary key column</param>
/// <param name="poco">The object instance whose "newness" is to be tested</param>
/// <returns>True if the POCO represents a record already in the database</returns>
/// <remarks>This method simply tests if the POCO's primary key column property has been set to something non-zero.</remarks>
bool IsNew(string primaryKeyName, object poco);

/// <summary>
///     Check if a poco represents a new row
/// </summary>
/// <param name="poco">The object instance whose "newness" is to be tested</param>
/// <returns>True if the POCO represents a record already in the database</returns>
/// <remarks>This method simply tests if the POCO's primary key column property has been set to something non-zero.</remarks>
bool IsNew(object poco);

/// <summary>
///     Saves a POCO by either performing either an SQL Insert or SQL Update
/// </summary>
/// <param name="tableName">The name of the table to be updated</param>
/// <param name="primaryKeyName">The name of the primary key column</param>
/// <param name="poco">The POCO object to be saved</param>
void Save(string tableName, string primaryKeyName, object poco);
Task SaveAsync(string tableName, string primaryKeyName, object poco);

/// <summary>
///     Saves a POCO by either performing either an SQL Insert or SQL Update
/// </summary>
/// <param name="poco">The POCO object to be saved</param>
void Save(object poco);
Task SaveAsync(object poco);

Examples

The examples below are taken from our integration tests.

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

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

        // Add a note
        var note = new Note { Text = "This is my note", CreatedOn = DateTime.UtcNow };
        DB.Save(note);

        // As note.id is auto increment, we should have an id of 1
        note.Id.ShouldBe(1);

        // Obviously, we should find only one matching note in the db
        var count = DB.ExecuteScalar<int>("SELECT COUNT([Id]) FROM [Note] WHERE [Id] = @Id", new { note.Id });
        count.ShouldBe(1);

        // Fetch a new copy of note
        var noteFromDb = DB.Single<Note>(note.Id);

        // They are the same
        note.Id.ShouldBe(noteFromDb.Id);
        note.Text.ShouldBe(noteFromDb.Text);
        note.CreatedOn.Ticks.ShouldBe(noteFromDb.CreatedOn.Ticks);
    }

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

        // Add a note
        var note = new Note { Text = "This is my note", CreatedOn = DateTime.UtcNow };
        DB.Save(note);

        // Update the note
        note.Text += " and this is my update";
        DB.Save(note);

        // Fetch a new copy of note
        var noteFromDb = DB.Single<Note>(note.Id);

        // The note text is the same
        note.Text.ShouldBe(noteFromDb.Text);
        note.Text.ShouldContain(" and this is my update");
    }
}

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] DATETIME2 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] DATETIME2 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] DATETIME2 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] DATETIME2 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] DATETIME2 NOT NULL
)

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