/
Delete.cs
139 lines (110 loc) · 5.17 KB
/
Delete.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
using System;
using PetaPoco.Core;
using PetaPoco.Tests.Integration.Databases;
using PetaPoco.Tests.Integration.Databases.MSSQL;
using PetaPoco.Tests.Integration.Documentation.Pocos;
using Shouldly;
using Xunit;
namespace PetaPoco.Tests.Integration.Documentation
{
[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);
}
}
}