Deleting
Stelio Kontos edited this page Sep 25, 2023
·
3 revisions
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.
/// <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);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; }
}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
)PetaPoco is proudly maintained by the Collaborating Platypus group and originally the brainchild of Brad Robinson