没有一种不通过蔑视、忍受和奋斗就可以征服的命运。
Program.cs主程序
class Program
{
static void Main()
{
SetupSqlServer();
RunTestsSqlServer();
Console.ReadKey();
}
private static void SetupSqlServer()
{
using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=Master;Persist Security Info=True;User ID=sa;Password=123456"))
{
connection.Open();
try
{
connection.Execute(@" DROP DATABASE DapperSimpleCrudTestDb; ");
}
catch (Exception)
{ }
connection.Execute(@" CREATE DATABASE DapperSimpleCrudTestDb; ");
}
using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=DapperSimpleCrudTestDb;Persist Security Info=True;User ID=sa;Password=123456"))
{
connection.Open();
connection.Execute(@" create table Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null, ScheduledDayOff int null, CreatedDate datetime DEFAULT(getdate())) ");
connection.Execute(@" create table Car (CarId int IDENTITY(1,1) not null, Id int null, Make nvarchar(100) not null, Model nvarchar(100) not null) ");
connection.Execute(@" create table BigCar (CarId bigint IDENTITY(2147483650,1) not null, Make nvarchar(100) not null, Model nvarchar(100) not null) ");
connection.Execute(@" create table City (Name nvarchar(100) not null, Population int not null) ");
connection.Execute(@" CREATE SCHEMA Log; ");
connection.Execute(@" create table Log.CarLog (Id int IDENTITY(1,1) not null, LogNotes nvarchar(100) NOT NULL) ");
connection.Execute(@" CREATE TABLE [dbo].[GUIDTest]([Id] [uniqueidentifier] NOT NULL,[name] [varchar](50) NOT NULL, CONSTRAINT [PK_GUIDTest] PRIMARY KEY CLUSTERED ([Id] ASC))");
connection.Execute(@" create table StrangeColumnNames (ItemId int IDENTITY(1,1) not null Primary Key, word nvarchar(100) not null, colstringstrangeword nvarchar(100) not null) ");
connection.Execute(@" create table UserWithoutAutoIdentity (Id int not null Primary Key, Name nvarchar(100) not null, Age int not null) ");
connection.Execute(@" create table IgnoreColumns (Id int IDENTITY(1,1) not null Primary Key, IgnoreInsert nvarchar(100) null, IgnoreUpdate nvarchar(100) null, IgnoreSelect nvarchar(100) null, IgnoreAll nvarchar(100) null) ");
}
Console.WriteLine("创建数据库!");
}
private static void RunTestsSqlServer()
{
var stopwatch = Stopwatch.StartNew();
var sqltester = new Tests(SimpleCRUD.Dialect.SQLServer);
foreach (var method in typeof(Tests).GetMethods(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly))
{
var testwatch = Stopwatch.StartNew();
Console.Write("运行 " + method.Name + " 在 sql server");
method.Invoke(sqltester, null);
testwatch.Stop();
Console.WriteLine(" - OK! {0}ms", testwatch.ElapsedMilliseconds);
}
stopwatch.Stop();
// 输出结果
Console.WriteLine("花费时间: {0}", stopwatch.Elapsed);
using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=Master;Persist Security Info=True;User ID=sa;Password=123456"))
{
connection.Open();
try
{
//删除任何剩余的连接,然后删除该数据库
connection.Execute(@" alter database DapperSimpleCrudTestDb set single_user with rollback immediate; DROP DATABASE DapperSimpleCrudTestDb; ");
}
catch (Exception)
{ }
}
Console.Write("SQL Server的测试完成。");
Console.ReadKey();
}
}
DTO类
#region DTO类
[Table("Users")]
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public DayOfWeek? ScheduledDayOff { get; set; }
[ReadOnly(true)]
public DateTime CreatedDate { get; set; }
}
[Table("Users")]
public class User1
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public int? ScheduledDayOff { get; set; }
}
public class Car
{
#region DatabaseFields
[Key]
public int CarId { get; set; }
public int? Id { get; set; }
public string Make { get; set; }
public string Model { get; set; }
#endregion
#region RelatedTables
public List<User> Users { get; set; }
#endregion
#region AdditionalFields
[Editable(false)]
public string MakeWithModel
{
get { return Make + " (" + Model + ")"; }
}
#endregion
}
public class BigCar
{
#region DatabaseFields
[Key]
public long CarId { get; set; }
public string Make { get; set; }
public string Model { get; set; }
#endregion
}
[Table("CarLog", Schema = "Log")]
public class CarLog
{
public int Id { get; set; }
public string LogNotes { get; set; }
}
[Table("CarLog")]
public class SchemalessCarLog
{
public int Id { get; set; }
public string LogNotes { get; set; }
}
public class City
{
[Key]
public string Name { get; set; }
public int Population { get; set; }
}
public class GUIDTest
{
[Key]
public Guid Id { get; set; }
public string Name { get; set; }
}
public class StrangeColumnNames
{
[Key]
[Column("ItemId")]
public int Id { get; set; }
public string Word { get; set; }
[Column("colstringstrangeword")]
public string StrangeWord { get; set; }
[Editable(false)]
public string ExtraProperty { get; set; }
}
public class IgnoreColumns
{
[Key]
public int Id { get; set; }
[IgnoreInsert]
public string IgnoreInsert { get; set; }
[IgnoreUpdate]
public string IgnoreUpdate { get; set; }
[IgnoreSelect]
public string IgnoreSelect { get; set; }
[IgnoreInsert]
[IgnoreUpdate]
[IgnoreSelect]
public string IgnoreAll { get; set; }
}
public class UserWithoutAutoIdentity
{
[Key]
[Required]
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
#endregion
Tests类:
public class Tests
{
private readonly SimpleCRUD.Dialect _dbtype;
public Tests(SimpleCRUD.Dialect dbtype)
{
_dbtype = dbtype;
}
private IDbConnection GetOpenConnection()
{
IDbConnection connection;
if (_dbtype == SimpleCRUD.Dialect.PostgreSQL)
{
connection =
new NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
"localhost", "5432", "postgres", "postgrespass", "testdb"));
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
}
else if (_dbtype == SimpleCRUD.Dialect.SQLite)
{
connection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
}
else if (_dbtype == SimpleCRUD.Dialect.MySQL)
{
connection =
new MySqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
"localhost", "3306", "admin", "admin", "testdb"));
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
}
else
{
connection =
new SqlConnection(
@"Data Source=.;Initial Catalog=DapperSimpleCrudTestDb;Persist Security Info=True;User ID=sa;Password=123456;MultipleActiveResultSets=true;");
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLServer);
}
connection.Open();
return connection;
}
public void InsertWithSpecifiedTableName()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new User {Name = "User1", Age = 10});
id.IsEqualTo(1);
connection.Delete<User>(id);
}
}
public void InsertUsingBigIntPrimaryKey()
{
using (IDbConnection connection = GetOpenConnection())
{
var id = connection.Insert<long>(new BigCar {Make = "Big", Model = "Car"});
id.IsEqualTo(2147483650);
connection.Delete<BigCar>(id);
}
}
public void TestSimpleGet()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new User {Name = "UserTestSimpleGet", Age = 10});
var user = connection.Get<User>(id);
user.Name.IsEqualTo("UserTestSimpleGet");
connection.Delete<User>(id);
}
}
public void TestDeleteById()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new User {Name = "UserTestDeleteById", Age = 10});
connection.Delete<User>(id);
connection.Get<User>(id).IsNull();
}
}
public void TestDeleteByObject()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new User {Name = "TestDeleteByObject", Age = 10});
var user = connection.Get<User>(id);
connection.Delete(user);
connection.Get<User>(id).IsNull();
}
}
public void TestSimpleGetList()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new User {Name = "TestSimpleGetList1", Age = 10});
connection.Insert(new User {Name = "TestSimpleGetList2", Age = 10});
IEnumerable<User> user = connection.GetList<User>(new {});
user.Count().IsEqualTo(2);
connection.Execute("Delete from Users");
}
}
public void TestFilteredGetList()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new User {Name = "TestFilteredGetList1", Age = 10});
connection.Insert(new User {Name = "TestFilteredGetList2", Age = 10});
connection.Insert(new User {Name = "TestFilteredGetList3", Age = 10});
connection.Insert(new User {Name = "TestFilteredGetList4", Age = 11});
IEnumerable<User> user = connection.GetList<User>(new {Age = 10});
user.Count().IsEqualTo(3);
connection.Execute("Delete from Users");
}
}
public void TestFilteredWithSQLGetList()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new User {Name = "TestFilteredWithSQLGetList1", Age = 10});
connection.Insert(new User {Name = "TestFilteredWithSQLGetList2", Age = 10});
connection.Insert(new User {Name = "TestFilteredWithSQLGetList3", Age = 10});
connection.Insert(new User {Name = "TestFilteredWithSQLGetList4", Age = 11});
IEnumerable<User> user =
connection.GetList<User>("where Name like 'TestFilteredWithSQLGetList%' and Age = 10");
user.Count().IsEqualTo(3);
connection.Execute("Delete from Users");
}
}
public void TestGetListWithNullWhere()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new User {Name = "TestGetListWithNullWhere", Age = 10});
IEnumerable<User> user = connection.GetList<User>(null);
user.Count().IsEqualTo(1);
connection.Execute("Delete from Users");
}
}
public void TestGetListWithoutWhere()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new User {Name = "TestGetListWithoutWhere", Age = 10});
IEnumerable<User> user = connection.GetList<User>();
user.Count().IsEqualTo(1);
connection.Execute("Delete from Users");
}
}
public void TestGetWithReadonlyProperty()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new User {Name = "TestGetWithReadonlyProperty", Age = 10});
var user = connection.Get<User>(id);
user.CreatedDate.Year.IsEqualTo(DateTime.Now.Year);
connection.Execute("Delete from Users");
}
}
public void TestInsertWithReadonlyProperty()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id =
connection.Insert(new User
{
Name = "TestInsertWithReadonlyProperty",
Age = 10,
CreatedDate = new DateTime(2001, 1, 1)
});
var user = connection.Get<User>(id);
user.CreatedDate.Year.IsEqualTo(DateTime.Now.Year);
connection.Execute("Delete from Users");
}
}
public void TestUpdateWithReadonlyProperty()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new User {Name = "TestUpdateWithReadonlyProperty", Age = 10});
var user = connection.Get<User>(id);
user.Age = 11;
user.CreatedDate = new DateTime(2001, 1, 1);
connection.Update(user);
user = connection.Get<User>(id);
user.CreatedDate.Year.IsEqualTo(DateTime.Now.Year);
connection.Execute("Delete from Users");
}
}
public void InsertWithSpecifiedKey()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new Car {Make = "Honda", Model = "Civic"});
id.IsEqualTo(1);
}
}
public void InsertWithExtraPropertiesShouldSkipNonSimpleTypesAndPropertiesMarkedEditableFalse()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id =
connection.Insert(new Car
{
Make = "Honda",
Model = "Civic",
Users = new List<User> {new User {Age = 12, Name = "test"}}
});
id.IsEqualTo(2);
}
}
public void TestUpdate()
{
using (IDbConnection connection = GetOpenConnection())
{
var newid = (int) connection.Insert(new Car {Make = "Honda", Model = "Civic"});
var newitem = connection.Get<Car>(newid);
newitem.Make = "Toyota";
connection.Update(newitem);
var updateditem = connection.Get<Car>(newid);
updateditem.Make.IsEqualTo("Toyota");
connection.Delete<Car>(newid);
}
}
public void TestDeleteByObjectWithAttributes()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new Car {Make = "Honda", Model = "Civic"});
var car = connection.Get<Car>(id);
connection.Delete(car);
connection.Get<Car>(id).IsNull();
}
}
public void TestComplexTypesMarkedEditableAreSaved()
{
using (IDbConnection connection = GetOpenConnection())
{
var id = (int) connection.Insert(new User {Name = "User", Age = 11, ScheduledDayOff = DayOfWeek.Friday});
var user1 = connection.Get<User>(id);
user1.ScheduledDayOff.IsEqualTo(DayOfWeek.Friday);
connection.Delete(user1);
}
}
public void TestNullableSimpleTypesAreSaved()
{
using (IDbConnection connection = GetOpenConnection())
{
var id = (int) connection.Insert(new User1 {Name = "User", Age = 11, ScheduledDayOff = 2});
var user1 = connection.Get<User1>(id);
user1.ScheduledDayOff.IsEqualTo(2);
connection.Delete(user1);
}
}
public void TestInsertIntoDifferentSchema()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new CarLog {LogNotes = "blah blah blah"});
id.IsEqualTo(1);
connection.Delete<CarLog>(id);
}
}
public void TestGetFromDifferentSchema()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new CarLog {LogNotes = "TestGetFromDifferentSchema"});
var carlog = connection.Get<CarLog>(id);
carlog.LogNotes.IsEqualTo("TestGetFromDifferentSchema");
connection.Delete<CarLog>(id);
}
}
public void TestTryingToGetFromTableInSchemaWithoutDataAnnotationShouldFail()
{
using (IDbConnection connection = GetOpenConnection())
{
try
{
connection.Get<SchemalessCarLog>(1);
}
catch (Exception)
{
return;
}
throw new ApplicationException("Expected exception");
}
}
public void TestGetFromTableWithNonIntPrimaryKey()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Execute("INSERT INTO CITY (NAME, POPULATION) VALUES ('Morgantown', 31000)");
var city = connection.Get<City>("Morgantown");
city.Population.IsEqualTo(31000);
}
}
public void TestDeleteFromTableWithNonIntPrimaryKey()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Execute("INSERT INTO CITY (NAME, POPULATION) VALUES ('Fairmont', 18737)");
connection.Delete<City>("Fairmont").IsEqualTo(1);
}
}
public void TestNullableEnumInsert()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new User {Name = "Enum-y", Age = 10, ScheduledDayOff = DayOfWeek.Thursday});
User user = connection.GetList<User>(new {Name = "Enum-y"}).FirstOrDefault() ?? new User();
user.ScheduledDayOff.IsEqualTo(DayOfWeek.Thursday);
connection.Delete<User>(user.Id);
}
}
public void ChangeDialect()
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLServer);
SimpleCRUD.GetDialect().IsEqualTo(SimpleCRUD.Dialect.SQLServer.ToString());
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
SimpleCRUD.GetDialect().IsEqualTo(SimpleCRUD.Dialect.PostgreSQL.ToString());
}
public void InsertIntoTableWithUnspecifiedGuidKey()
{
using (IDbConnection connection = GetOpenConnection())
{
var id = connection.Insert<Guid>(new GUIDTest {Name = "GuidUser"});
id.GetType().Name.IsEqualTo("Guid");
var record = connection.Get<GUIDTest>(id);
record.Name.IsEqualTo("GuidUser");
connection.Delete<GUIDTest>(id);
}
}
public void InsertIntoTableWithGuidKey()
{
using (IDbConnection connection = GetOpenConnection())
{
var guid = new Guid("1a6fb33d-7141-47a0-b9fa-86a1a1945da9");
var id = connection.Insert<Guid>(new GUIDTest {Name = "InsertIntoTableWithGuidKey", Id = guid});
id.IsEqualTo(guid);
connection.Delete<GUIDTest>(id);
}
}
public void GetRecordWithGuidKey()
{
using (IDbConnection connection = GetOpenConnection())
{
var guid = new Guid("2a6fb33d-7141-47a0-b9fa-86a1a1945da9");
connection.Insert<Guid>(new GUIDTest {Name = "GetRecordWithGuidKey", Id = guid});
Guid id = connection.GetList<GUIDTest>().First().Id;
var record = connection.Get<GUIDTest>(id);
record.Name.IsEqualTo("GetRecordWithGuidKey");
connection.Delete<GUIDTest>(id);
}
}
public void DeleteRecordWithGuidKey()
{
using (IDbConnection connection = GetOpenConnection())
{
var guid = new Guid("3a6fb33d-7141-47a0-b9fa-86a1a1945da9");
connection.Insert<Guid>(new GUIDTest {Name = "DeleteRecordWithGuidKey", Id = guid});
Guid id = connection.GetList<GUIDTest>().First().Id;
connection.Delete<GUIDTest>(id);
connection.Get<GUIDTest>(id).IsNull();
}
}
public void TestMultiInsertASync()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.InsertAsync(new User {Name = "TestMultiInsertASync1", Age = 10});
connection.InsertAsync(new User {Name = "TestMultiInsertASync2", Age = 10});
connection.InsertAsync(new User {Name = "TestMultiInsertASync3", Age = 10});
connection.InsertAsync(new User {Name = "TestMultiInsertASync4", Age = 11});
Thread.Sleep(300);
IEnumerable<User> list = connection.GetList<User>(new {Age = 10});
list.Count().IsEqualTo(3);
connection.Execute("Delete from Users");
}
}
public void MultiInsertWithGuidAsync()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.InsertAsync<Guid>(new GUIDTest {Name = "MultiInsertWithGuidAsync"});
connection.InsertAsync<Guid>(new GUIDTest {Name = "MultiInsertWithGuidAsync"});
connection.InsertAsync<Guid>(new GUIDTest {Name = "MultiInsertWithGuidAsync"});
connection.InsertAsync<Guid>(new GUIDTest {Name = "MultiInsertWithGuidAsync"});
Thread.Sleep(300);
IEnumerable<GUIDTest> list = connection.GetList<GUIDTest>(new {Name = "MultiInsertWithGuidAsync"});
list.Count().IsEqualTo(4);
connection.Execute("Delete from GUIDTest");
}
}
public void TestSimpleGetAsync()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new User {Name = "TestSimpleGetAsync", Age = 10});
Task<User> user = connection.GetAsync<User>(id);
user.Result.Name.IsEqualTo("TestSimpleGetAsync");
connection.Delete<User>(id);
}
}
public void TestDeleteByIdAsync()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new User {Name = "UserAsyncDelete", Age = 10});
connection.DeleteAsync<User>(id);
Thread.Sleep(300);
connection.Get<User>(id).IsNull();
}
}
public void TestDeleteByObjectAsync()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new User {Name = "TestDeleteByObjectAsync", Age = 10});
var user = connection.Get<User>(id);
connection.DeleteAsync(user);
connection.Get<User>(id).IsNull();
connection.Delete<User>(id);
}
}
public void TestSimpleGetListAsync()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new User {Name = "TestSimpleGetListAsync1", Age = 10});
connection.Insert(new User {Name = "TestSimpleGetListAsync2", Age = 10});
Task<IEnumerable<User>> user = connection.GetListAsync<User>(new {});
user.Result.Count().IsEqualTo(2);
connection.Execute("Delete from Users");
}
}
public void TestFilteredGetListAsync()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new User {Name = "TestFilteredGetListAsync1", Age = 10});
connection.Insert(new User {Name = "TestFilteredGetListAsync2", Age = 10});
connection.Insert(new User {Name = "TestFilteredGetListAsync3", Age = 10});
connection.Insert(new User {Name = "TestFilteredGetListAsync4", Age = 11});
Task<IEnumerable<User>> user = connection.GetListAsync<User>(new {Age = 10});
user.Result.Count().IsEqualTo(3);
connection.Execute("Delete from Users");
}
}
public void InsertWithSpecifiedColumnName()
{
using (IDbConnection connection = GetOpenConnection())
{
int? itemId =
connection.Insert(new StrangeColumnNames
{
Word = "InsertWithSpecifiedColumnName",
StrangeWord = "Strange 1"
});
itemId.IsEqualTo(1);
connection.Delete<StrangeColumnNames>(itemId);
}
}
public void TestDeleteByObjectWithSpecifiedColumnName()
{
using (IDbConnection connection = GetOpenConnection())
{
int? itemId =
connection.Insert(new StrangeColumnNames
{
Word = "TestDeleteByObjectWithSpecifiedColumnName",
StrangeWord = "Strange 1"
});
var strange = connection.Get<StrangeColumnNames>(itemId);
connection.Delete(strange);
connection.Get<StrangeColumnNames>(itemId).IsNull();
}
}
public void TestSimpleGetListWithSpecifiedColumnName()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id1 =
connection.Insert(new StrangeColumnNames
{
Word = "TestSimpleGetListWithSpecifiedColumnName1",
StrangeWord = "Strange 2",
});
int? id2 =
connection.Insert(new StrangeColumnNames
{
Word = "TestSimpleGetListWithSpecifiedColumnName2",
StrangeWord = "Strange 3",
});
IEnumerable<StrangeColumnNames> strange = connection.GetList<StrangeColumnNames>(new {});
strange.First().StrangeWord.IsEqualTo("Strange 2");
strange.Count().IsEqualTo(2);
connection.Delete<StrangeColumnNames>(id1);
connection.Delete<StrangeColumnNames>(id2);
}
}
public void TestUpdateWithSpecifiedColumnName()
{
using (IDbConnection connection = GetOpenConnection())
{
var newid =
(int)
connection.Insert(new StrangeColumnNames {Word = "Word Insert", StrangeWord = "Strange Insert"});
var newitem = connection.Get<StrangeColumnNames>(newid);
newitem.Word = "Word Update";
connection.Update(newitem);
var updateditem = connection.Get<StrangeColumnNames>(newid);
updateditem.Word.IsEqualTo("Word Update");
connection.Delete<StrangeColumnNames>(newid);
}
}
public void TestFilteredGetListWithSpecifiedColumnName()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new StrangeColumnNames {Word = "Word 5", StrangeWord = "Strange 1",});
connection.Insert(new StrangeColumnNames {Word = "Word 6", StrangeWord = "Strange 2",});
connection.Insert(new StrangeColumnNames {Word = "Word 7", StrangeWord = "Strange 2",});
connection.Insert(new StrangeColumnNames {Word = "Word 8", StrangeWord = "Strange 2",});
IEnumerable<StrangeColumnNames> strange =
connection.GetList<StrangeColumnNames>(new {StrangeWord = "Strange 2"});
strange.Count().IsEqualTo(3);
connection.Execute("Delete from StrangeColumnNames");
}
}
public void TestGetListPaged()
{
using (IDbConnection connection = GetOpenConnection())
{
int x = 0;
do
{
connection.Insert(new User
{
Name = "Person " + x,
Age = x,
CreatedDate = DateTime.Now,
ScheduledDayOff = DayOfWeek.Thursday
});
x++;
} while (x < 30);
IEnumerable<User> resultlist = connection.GetListPaged<User>(2, 10, null, null);
resultlist.Count().IsEqualTo(10);
resultlist.Skip(4).First().Name.IsEqualTo("Person 14");
connection.Execute("Delete from Users");
}
}
public void TestGetListPagedWithSpecifiedPrimaryKey()
{
using (IDbConnection connection = GetOpenConnection())
{
int x = 0;
do
{
connection.Insert(new StrangeColumnNames {Word = "Word " + x, StrangeWord = "Strange " + x});
x++;
} while (x < 30);
IEnumerable<StrangeColumnNames> resultlist = connection.GetListPaged<StrangeColumnNames>(2, 10, null,
null);
resultlist.Count().IsEqualTo(10);
resultlist.Skip(4).First().Word.IsEqualTo("Word 14");
connection.Execute("Delete from StrangeColumnNames");
}
}
public void TestGetListPagedWithWhereClause()
{
using (IDbConnection connection = GetOpenConnection())
{
int x = 0;
do
{
connection.Insert(new User
{
Name = "Person " + x,
Age = x,
CreatedDate = DateTime.Now,
ScheduledDayOff = DayOfWeek.Thursday
});
x++;
} while (x < 30);
IEnumerable<User> resultlist1 = connection.GetListPaged<User>(1, 3, "Where Name LIKE 'Person 2%'",
"age desc");
resultlist1.Count().IsEqualTo(3);
IEnumerable<User> resultlist = connection.GetListPaged<User>(2, 3, "Where Name LIKE 'Person 2%'",
"age desc");
resultlist.Count().IsEqualTo(3);
resultlist.Skip(1).First().Name.IsEqualTo("Person 25");
connection.Execute("Delete from Users");
}
}
public void TestDeleteListWithWhereClause()
{
using (IDbConnection connection = GetOpenConnection())
{
int x = 0;
do
{
connection.Insert(new User
{
Name = "Person " + x,
Age = x,
CreatedDate = DateTime.Now,
ScheduledDayOff = DayOfWeek.Thursday
});
x++;
} while (x < 30);
connection.DeleteList<User>("Where age > 9");
IEnumerable<User> resultlist = connection.GetList<User>();
resultlist.Count().IsEqualTo(10);
connection.Execute("Delete from Users");
}
}
public void TestDeleteListWithWhereObject()
{
using (IDbConnection connection = GetOpenConnection())
{
int x = 0;
do
{
connection.Insert(new User
{
Name = "Person " + x,
Age = x,
CreatedDate = DateTime.Now,
ScheduledDayOff = DayOfWeek.Thursday
});
x++;
} while (x < 10);
connection.DeleteList<User>(new {age = 9});
IEnumerable<User> resultlist = connection.GetList<User>();
resultlist.Count().IsEqualTo(9);
connection.Execute("Delete from Users");
}
}
public void TestRecordCount()
{
using (IDbConnection connection = GetOpenConnection())
{
int x = 0;
do
{
connection.Insert(new User
{
Name = "Person " + x,
Age = x,
CreatedDate = DateTime.Now,
ScheduledDayOff = DayOfWeek.Thursday
});
x++;
} while (x < 30);
IEnumerable<User> resultlist = connection.GetList<User>();
resultlist.Count().IsEqualTo(30);
connection.RecordCount<User>().IsEqualTo(30);
connection.RecordCount<User>("where age = 10 or age = 11").IsEqualTo(2);
connection.Execute("Delete from Users");
}
}
public void InsertWithSpecifiedPrimaryKey()
{
using (IDbConnection connection = GetOpenConnection())
{
int? id = connection.Insert(new UserWithoutAutoIdentity {Id = 999, Name = "User999", Age = 10});
id.IsEqualTo(999);
var user = connection.Get<UserWithoutAutoIdentity>(999);
user.Name.IsEqualTo("User999");
connection.Execute("Delete from UserWithoutAutoIdentity");
}
}
public void InsertWithSpecifiedPrimaryKeyAsync()
{
using (IDbConnection connection = GetOpenConnection())
{
Task<int?> id =
connection.InsertAsync(new UserWithoutAutoIdentity {Id = 999, Name = "User999Async", Age = 10});
id.Result.IsEqualTo(999);
Task<UserWithoutAutoIdentity> user = connection.GetAsync<UserWithoutAutoIdentity>(999);
user.Result.Name.IsEqualTo("User999Async");
connection.Execute("Delete from UserWithoutAutoIdentity");
}
}
public void TestGetListNullableWhere()
{
using (IDbConnection connection = GetOpenConnection())
{
connection.Insert(new User
{
Name = "TestGetListWithoutWhere",
Age = 10,
ScheduledDayOff = DayOfWeek.Friday
});
connection.Insert(new User {Name = "TestGetListWithoutWhere", Age = 10});
IEnumerable<User> list = connection.GetList<User>(new {ScheduledDayOff = (DayOfWeek?) null});
list.Count().IsEqualTo(1);
list = connection.GetList<User>(new {ScheduledDayOff = DBNull.Value});
list.Count().IsEqualTo(1);
connection.Execute("Delete from Users");
}
}
public void IgnoreProperties()
{
using (IDbConnection connection = GetOpenConnection())
{
int? itemId =
connection.Insert(new IgnoreColumns
{
IgnoreInsert = "OriginalInsert",
IgnoreUpdate = "OriginalUpdate",
IgnoreSelect = "OriginalSelect",
IgnoreAll = "OriginalAll"
});
var item = connection.Get<IgnoreColumns>(itemId);
item.IgnoreInsert.IsNull();
item.IgnoreSelect.IsNull();
IgnoreColumns fromDapper =
connection.Query<IgnoreColumns>("Select * from IgnoreColumns where Id = @Id", new {id = itemId})
.First();
fromDapper.IgnoreSelect.IsEqualTo("OriginalSelect");
item.IgnoreUpdate = "ChangedUpdate";
connection.Update(item);
item = connection.Get<IgnoreColumns>(itemId);
item.IgnoreUpdate.IsEqualTo("OriginalUpdate");
IgnoreColumns allColumnDapper =
connection.Query<IgnoreColumns>("Select IgnoreAll from IgnoreColumns where Id = @Id",
new {id = itemId}).First();
allColumnDapper.IgnoreAll.IsNull();
connection.Delete<IgnoreColumns>(itemId);
}
}
}
结果如图:
![这里写图片描述](https://img-blog.csdn.net/20160912212336447)
![这里写图片描述](https://img-blog.csdn.net/20160912212353351)