Deleting

PetaPoco supports very flexible methods for deleting data. The easiest way to demonstrate how to delete 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 and SQL Delete
/// </summary>
/// <param name="tableName">The name of the table to delete from</param>
/// <param name="primaryKeyName">The name of the primary key column</param>
/// <param name="poco">The POCO object whose primary key value will be used to delete the row</param>
/// <returns>The number of rows affected</returns>
int Delete(string tableName, string primaryKeyName, object poco);
Task<int> DeleteAsync(string tableName, string primaryKeyName, object poco);

/// <summary>
///     Performs and SQL Delete
/// </summary>
/// <param name="tableName">The name of the table to delete from</param>
/// <param name="primaryKeyName">The name of the primary key column</param>
/// <param name="poco">
///     The POCO object whose primary key value will be used to delete the row (or null to use the supplied
///     primary key value)
/// </param>
/// <param name="primaryKeyValue">
///     The value of the primary key identifying the record to be deleted (or null, or get this
///     value from the POCO instance)
/// </param>
/// <returns>The number of rows affected</returns>
int Delete(string tableName, string primaryKeyName, object poco, object primaryKeyValue);
Task<int> DeleteAsync(string tableName, string primaryKeyName, object poco, object primaryKeyValue);

/// <summary>
///     Performs an SQL Delete
/// </summary>
/// <param name="poco">The POCO object specifying the table name and primary key value of the row to be deleted</param>
/// <returns>The number of rows affected</returns>
int Delete(object poco);
Task<int> DeleteAsync(object poco);

/// <summary>
///     Performs an SQL Delete
/// </summary>
/// <typeparam name="T">The POCO class whose attributes identify the table and primary key to be used in the delete</typeparam>
/// <param name="pocoOrPrimaryKey">The value of the primary key of the row to delete</param>
/// <returns></returns>
int Delete<T>(object pocoOrPrimaryKey);
Task<int> DeleteAsync<T>(object pocoOrPrimaryKey);

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

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

Examples

The examples below are taken from our integration tests.

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

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

        // Tell PetaPoco to insert it
        DB.Insert(person);

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

        // Tell PetaPoco to delete it
        DB.Delete(person);

        // Obviously, we should now have none in the db
        count = DB.ExecuteScalar<int>("SELECT COUNT([Id]) FROM [People] WHERE [Id] = @0", person.Id);
        count.ShouldBe(0);
    }

    [Fact]
    public void DeleteByPrimaryKey()
    {
        // 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.Insert(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);

        // Now, tell PetaPoco to delete a note with the id of 1
        DB.Delete<Note>(note.Id);

        // Obviously, we should now have none in the db
        count = DB.ExecuteScalar<int>("SELECT COUNT([Id]) FROM [Note] WHERE [Id] = @0", note.Id);
        count.ShouldBe(0);
    }

    [Fact]
    public void DeleteCustomWhere()
    {
        // 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.Insert(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);

        // Now, we'll tell PetaPoco how to delete the note
        DB.Delete<Note>("WHERE [Id] = @Id", new { note.Id });

        // Obviously, we should now have none in the db
        count = DB.ExecuteScalar<int>("SELECT COUNT([Id]) FROM [Note] WHERE [Id] = @0", note.Id);
        count.ShouldBe(0);
    }

    [Fact]
    public void DeleteCustomSqlWhere()
    {
        // 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.Insert(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);

        // Now, we'll tell PetaPoco how to delete the note
        var sql = new Sql();
        sql.Where("[Id] = @Id", new { note.Id });
        DB.Delete<Note>(sql);

        // Obviously, we should now have none in the db
        count = DB.ExecuteScalar<int>("SELECT COUNT([Id]) FROM [Note] WHERE [Id] = @0", note.Id);
        count.ShouldBe(0);
    }

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

        // Tell PetaPoco to insert it, but to the table SpecificPeople and not People
        DB.Insert("SpecificPeople", person);

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

        // Tell PetaPoco to delete it, but in the table SpecificPeople and not People
        DB.Delete("SpecificPeople", "Id", person);

        // Obviously, we should now have none in the db
        count = DB.ExecuteScalar<int>("SELECT COUNT([Id]) FROM [SpecificPeople] WHERE [Id] = @0", person.Id);
        count.ShouldBe(0);
    }
}

[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
)