正文
.NET ORM工具Pax实战
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
Pax是微软的一个ORM读写工具，比NHibernate更好用。可以在项目中用Nuget下载并安装。
可以看到引用中多了
在App.config里设置配置文件如下
<?xml version="1.0" encoding="utf-8" ?><configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" /> </startup> <connectionStrings> <clear/> <add name="Couriers" connectionString="Server=.;User id=sa;password=3179412;Initial Catalog=Couriers;MultipleActiveResultSets=true;Type System Version=SQL Server 2012;" /> </connectionStrings></configuration>
写一个建立与sqlserver2012连接的工具类
public class BaseDb { public IDbConnection GetDb( IOrmLiteDialectProvider provider = null) { var connections = Pax.Lib.Configuration.ConfigUtils.GetConnectionString(ConnectionStr); //??主要作用是如果 ?? 运算符的左操作数非空，该运算符将返回左操作数，否则返回右操作数。 var factory = new OrmLiteConnectionFactory(connections, provider??SqlServerDialect.Provider); return factory.Open(); } public BaseDb(string connectionStr) { ConnectionStr = connectionStr; } private string ConnectionStr; public IDbConnection Db => GetDb(); ~BaseDb()//析构函数 { if (Db.State == ConnectionState.Open) { Db.Close(); Db.Dispose(); } } }
连接前Couriers数据库里什么也没有。
建立一个类，类的字段要对应数据库的字段。
public class ModelWithIdOnly: { public ModelWithIdOnly() { } public ModelWithIdOnly(long id) { Id = id; } // must be long as you cannot have a table with only an autoincrement field public long Id { get; set; } }
在入口处写程序如下:
class Program { static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.Db; Db.DropAndCreateTable(typeof(ModelWithIdOnly)); var success = Db.TableExists(typeof (ModelWithIdOnly).Name.SqlTableRaw()); if (success) { Console.WriteLine("ModelWithIdOnly表插入到了数据库"); Console.ReadKey(); } } }
运行可以看到
表的删除
static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.Db; Db.DropTable<ModelWithIdOnly>(); var success = Db.TableExists(typeof(ModelWithIdOnly).Name.SqlTableRaw());//检查新表是否存在 if (!success) { Console.WriteLine("ModelWithIdOnly表在数据库中删除"); Console.ReadKey(); } }
表的新增
class Program { static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.Db; Db.CreateTable<ModelWithIdOnly>(); var success = Db.TableExists(typeof(ModelWithIdOnly).Name.SqlTableRaw());//检查新表是否存在 if (success) { Console.WriteLine("ModelWithIdOnly表在数据库中新增"); Console.ReadKey(); } } }
插入一列
class Program { static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.Db; )); ) { Console.WriteLine("成功插入了{0}列",success); Console.ReadKey(); } } }
返回表中所有数据
class Program { static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.Db; var rows = Db.Select<ModelWithIdOnly>(); ) { Console.WriteLine(rows); Console.ReadKey(); } } }
输出sql语句
class Program { static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.Db; var rows = Db.Select<ModelWithIdOnly>(); var strSQL = Db.GetLastSql(); ) { Console.WriteLine(strSQL); Console.ReadKey(); } } }
创建新类
public class ModelWithGuid { public long Id { get; set; } public Guid Guid { get; set; } }
简单的插入与查找
class Program { static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.Db; Db.DropAndCreateTable<ModelWithGuid>(); var models = new[] { , Guid = Guid.NewGuid() }, , Guid = Guid.NewGuid() } }; var successNum= Db.SaveAll(models); string strSql1 = Db.GetLastSql();//获得sql语句 ].Id);//取出某列 var newModel2 = Db.Single<ModelWithGuid>(q => q.Guid == models[0].Guid);//取出某列 Db.Update(new ModelWithGuid { Id = models[0].Id, Guid = newGuid });//更新某列 bool has = Db.TableExists(typeof (ModelWithIdOnly).Name.SqlTableRaw());//某表是否存在
var model = new[] { new ModelWithGuid { Id = 3, Guid = Guid.NewGuid() } };
long num= Db.Insert(model);//插入某列，返回插入列数 } }
执行sql语句插class Program
{ static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.GetDb(); var models = new[] { , Guid = Guid.NewGuid() }, , Guid = Guid.NewGuid() } }; Db.ExecuteSql("INSERT INTO {0} ({1},{2}) VALUES (4,{3})" .Fmt("ModelWithGuid".SqlTable(), "Id".SqlColumn(), "Guid".SqlColumn(), models[].Guid.SqlValue())); var normalLastInsertedId = Db.LastInsertId(); var rows = Db.Select<ModelWithGuid>(); var row2 = rows.First(x => x.Id == 4);
var rowIds = new List<int>(new[] { 1, 2, 3 });
rows = Db.SelectByIds<ModelWithGuid>(rowIds);
var dbRowIds = rows.ConvertAll(x => x.Id);
Console.WriteLine(normalLastInsertedId.ToString()); Console.ReadKey(); } }
批量删除
class Program { static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.GetDb(); , }); ], rowIds[] }); Console.WriteLine(success); Console.ReadKey(); } }
选择性删除
class Program { static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.GetDb(); , }); ], rowIds[] }); Db.Delete<ModelWithGuid>(x => x.Id <= ); ); Console.WriteLine(dbRow==null?"没有":dbRow.Guid.ToString()); Console.ReadKey(); } }
条件删除
class Program { static void Main(string[] args) { BaseDb baseDb = new BaseDb("Couriers"); var Db = baseDb.GetDb(); Db.DeleteFmt<ModelWithIdOnly>()); ); Console.WriteLine(dbRow==null?"没有":dbRow.Id.ToString()); Console.ReadKey(); } }
插入与删除
class Program { static void Main(string[] args) { var connections = Pax.Lib.Configuration.ConfigUtils.GetConnectionString("Couriers"); var factory = new OrmLiteConnectionFactory(connections, SqlServerDialect.Provider); IDbConnection Db = factory.Open(); using (Db) { ModelWithIdOnly one = }; Db.Save(one); var rowsAffected = Db.Delete(one); Console.WriteLine(rowsAffected.ToString()); Console.ReadKey(); } } }
批量增删
class Program { static void Main(string[] args) { var connections = Pax.Lib.Configuration.ConfigUtils.GetConnectionString("Couriers"); var factory = new OrmLiteConnectionFactory(connections, SqlServerDialect.Provider); IDbConnection Db = factory.Open(); using (Db) { List <ModelWithIdOnly> ones = },} }; Db.SaveAll(ones); var rowsAffected = Db.Delete(ones.ToArray()); Console.WriteLine(rowsAffected.ToString()); Console.ReadKey(); } } }
按条件更新
class Program { static void Main(string[] args) { var connections = Pax.Lib.Configuration.ConfigUtils.GetConnectionString("Couriers"); var factory = new OrmLiteConnectionFactory(connections, SqlServerDialect.Provider); IDbConnection Db = factory.Open(); using (Db) { Db.DropAndCreateTable<ModelWithName>(); List <ModelWithName> ones = ,Name=,Name= ,Name="zhuhou"} }; Db.SaveAll(ones); ModelWithName one = , Name = "dog"}; var num = Db.Update<ModelWithName>(new {one.Name}, x => x.Id==); Db.UpdateFmt<ModelWithName>(set: "NAME = {0}".SqlFmt("change"), where: "Id".SqlColumn() + " = {0}".SqlFmt(8)); Db.UpdateFmt(table: "ModelWithName".SqlTableRaw(),set: "NAME = {0}".SqlFmt("cat"), where: "Id".SqlColumn() + " = {0}".SqlFmt(13)); Console.WriteLine(num.ToString()); Console.ReadKey(); } } }
同时插入多行
class Program { static void Main(string[] args) { var connections = Pax.Lib.Configuration.ConfigUtils.GetConnectionString("Couriers"); var factory = new OrmLiteConnectionFactory(connections, SqlServerDialect.Provider); IDbConnection Db = factory.Open(); using (Db) { Db.CreateTable<ModelWithIdOnly>(true); ); ); Db.Insert(row1, row2); Db.Update(row1, row2); var list = new List<ModelWithIdOnly> { row1, row2 }; Db.UpdateAll(list); } } }
更新所有列
class Program { static void Main(string[] args) { var connections = Pax.Lib.Configuration.ConfigUtils.GetConnectionString("Couriers"); var factory = new OrmLiteConnectionFactory(connections, SqlServerDialect.Provider); IDbConnection Db = factory.Open(); using (Db) { Db.Insert(, Name = "Car"}); var existingPerson = Db.Select<ModelWithName>().First(); existingPerson.Name = "Love"; //把所有的Name列更新成love Db.UpdateOnly(existingPerson, onlyFields: p => new { p.Name}); var person = Db.Select<ModelWithName>().First(); Console.WriteLine(person.Name); } } }
更新指定列class Program
{ static void Main(string[] args) { var connections = Pax.Lib.Configuration.ConfigUtils.GetConnectionString("Couriers"); var factory = new OrmLiteConnectionFactory(connections, SqlServerDialect.Provider); IDbConnection Db = factory.Open(); using (Db) { ); Console.WriteLine(Num); Db.UpdateOnly(); Db.UpdateOnly(new ModelWithName { Name = "Java" }, q => q.Update(p => p.Name).Where(x => x.Name == "Love")); //只更新Name == "Love"的列} } }
select操作
class Program { static void Main(string[] args) { var connections = Pax.Lib.Configuration.ConfigUtils.GetConnectionString("Couriers"); var factory = new OrmLiteConnectionFactory(connections, SqlServerDialect.Provider); IDbConnection Db = factory.Open(); using (Db) { var temp= Db.Select<ModelWithName>(x => x.Name == "js");//返回list var temp1= Db.Select<ModelWithName>(q => q.Where(x => x.Name == "Java").OrderBy(x => x.Id));//返回list ));//返回list var temp4 = Db.Single<ModelWithName>(x=>x.Name=="java");//只返回第一个符合的元素对象 ));//只返回第一个符合的元素对象 ));//只返回第一个符合的元素对象 //var Num1= Db.Scalar<ModelWithName, int>(x => Sql.Max(Convert.ToInt32(x.Id)));//返回单个计算数据 //var Num2= Db.Scalar<ModelWithName, int>(x => Sql.Max(Convert.ToInt32(x.Id)), x => x.Id < 100);//带条件返回单个计算数据 var temp7 =Db.Select<ModelWithName>("Id > 40"); var temp8 = Db.Select<ModelWithName>("SELECT * FROM ModelWithName WHERE Id > 40"); ) }); }); ); );//相当于select Id from ModelWithName where Id>90 ); );//返回list }); , , });//相当于select * from ModelWithIdOnly where id in (1,2,3) var temp1= Db.SelectNonDefaults(new ModelWithName { Id = 1 });//select * from ModelWithName where id=1 返回list var temp2= Db.SelectNonDefaults("Name = @Name", new ModelWithName { Name = "Java" });//select * from ModelWithName where Name='Java' 返回list }
} }
其他select操作
db.SelectLazy<Person>().ToList(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\"")); db.SelectLazy<Person>( }).ToList(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE Age > @age")); db.SelectLazyFmt<Person>().ToList(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE Age > 40")); db.WhereLazy<Person>( }).ToList(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE \"Age\" = @Age")); db.SingleById<Person>(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE \"Id\" = @Id")); db.Single<Person>( }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE \"Age\" = @Age")); db.Single<Person>( }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE Age = @age")); db.Single<Person>() }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE Age = @age")); db.SingleFmt<Person>(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE Age = 42")); db.SingleById<Person>(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE \"Id\" = @Id")); db.SingleWhere<Person>(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE \"Age\" = @Age")); db.Scalar<)); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) \nFROM \"Person\"\nWHERE (\"Age\" > @0)")); db.Scalar<)); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Count(*) \nFROM \"Person\"\nWHERE (\"Age\" > @0)")); db.Scalar< }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM Person WHERE Age > @age")); db.Scalar<) }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM Person WHERE Age > @age")); db.ScalarFmt<); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM Person WHERE Age > 40")); db.Column<)); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"LastName\" \nFROM \"Person\"\nWHERE (\"Age\" = @0)")); db.Column< }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT LastName FROM Person WHERE Age = @age")); db.Column<) }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT LastName FROM Person WHERE Age = @age")); db.ColumnFmt<); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT LastName FROM Person WHERE Age = 27")); db.ColumnDistinct<)); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Age\" \nFROM \"Person\"\nWHERE (\"Age\" < @0)")); db.ColumnDistinct< }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Age FROM Person WHERE Age < @age")); db.ColumnDistinct<) }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Age FROM Person WHERE Age < @age")); db.ColumnDistinctFmt<); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Age FROM Person WHERE Age < 50"));
db.Lookup<)); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Age\",\"LastName\" \nFROM \"Person\"\nWHERE (\"Age\" < @0)")); db.Lookup< }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Age, LastName FROM Person WHERE Age < @age")); db.LookupFmt<); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Age, LastName FROM Person WHERE Age < 50")); db.Dictionary<)); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\",\"LastName\" \nFROM \"Person\"\nWHERE (\"Age\" < @0)")); db.Dictionary< }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Id, LastName FROM Person WHERE Age < @age")); db.DictionaryFmt<); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT Id, LastName FROM Person WHERE Age < 50"));
db.Exists<Person>(x => x.Age < ); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) \nFROM \"Person\"\nWHERE (\"Age\" < @0)")); db.Exists(db.From<Person>().Where(x => x.Age < )); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) \nFROM \"Person\"\nWHERE (\"Age\" < @0)")); db.Exists<Person>( }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE \"Age\" = @Age")); db.Exists<Person>( }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE Age = @age")); db.Exists<Person>( }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM Person WHERE Age = @age")); db.ExistsFmt<Person>(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"Id\", \"FirstName\", \"LastName\", \"Age\" FROM \"Person\" WHERE Age = 42")); db.ExistsFmt<Person>(); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM Person WHERE Age = 42")); db.SqlList<Person>(db.From<Person>().Select()); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * \nFROM \"Person\"\nWHERE (\"Age\" < @0)")); db.SqlList<Person>( }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM Person WHERE Age < @age")); db.SqlList<Person>() }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM Person WHERE Age < @age")); db.SqlList<Person>( } }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT * FROM Person WHERE Age < @age")); db.SqlColumn<)); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT \"LastName\" \nFROM \"Person\"\nWHERE (\"Age\" < @0)")); db.SqlColumn< }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT LastName FROM Person WHERE Age < @age")); db.SqlColumn<) }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT LastName FROM Person WHERE Age < @age")); db.SqlColumn< } }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT LastName FROM Person WHERE Age < @age")); db.SqlScalar<)); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) \nFROM \"Person\"\nWHERE (\"Age\" < @0)")); db.SqlScalar< }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM Person WHERE Age < @age")); db.SqlScalar<) }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM Person WHERE Age < @age")); db.SqlScalar< } }); Assert.That(db.GetLastSql(), Is.EqualTo("SELECT COUNT(*) FROM Person WHERE Age < @age")); )); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE Person SET LastName='WaterHouse' WHERE Id=7")); rowsAffected = db.ExecuteNonQuery( }); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE Person SET LastName=@name WHERE Id=@id")); db.Insert(, FirstName = }); Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"Person\" (\"Id\",\"FirstName\",\"LastName\",\"Age\") VALUES (@Id,@FirstName,@LastName,@Age)")); db.Insert(, FirstName = }, , FirstName = }); Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"Person\" (\"Id\",\"FirstName\",\"LastName\",\"Age\") VALUES (@Id,@FirstName,@LastName,@Age)")); db.InsertAll(, FirstName = } }); Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"Person\" (\"Id\",\"FirstName\",\"LastName\",\"Age\") VALUES (@Id,@FirstName,@LastName,@Age)")); db.InsertOnly( }, q => q.Insert(x => new { x.FirstName, x.Age })); Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"PersonWithAutoId\" (\"FirstName\",\"Age\") VALUES ('Amy',27)")); db.InsertOnly( }, q => db.From<PersonWithAutoId>().Insert(x => new { x.FirstName, x.Age })); Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"PersonWithAutoId\" (\"FirstName\",\"Age\") VALUES ('Amy',27)")); db.Update(, FirstName = }); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@FirstName, \"LastName\"=@LastName, \"Age\"=@Age WHERE \"Id\"=@Id")); db.Update(, FirstName = }, , FirstName = }); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@FirstName, \"LastName\"=@LastName, \"Age\"=@Age WHERE \"Id\"=@Id")); db.Update(, FirstName = } }); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@FirstName, \"LastName\"=@LastName, \"Age\"=@Age WHERE \"Id\"=@Id")); db.UpdateAll(, FirstName = } }); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@FirstName, \"LastName\"=@LastName, \"Age\"=@Age WHERE \"Id\"=@Id")); db.Update(, FirstName = }, x => x.LastName == "Hendrix"); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"Id\"=@1, \"FirstName\"=@2, \"LastName\"=@3, \"Age\"=@4 WHERE (\"LastName\" = @0)")); db.Update<Person>(new { FirstName = "JJ" }, x => x.LastName == "Hendrix"); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@1 WHERE (\"LastName\" = @0)")); db.UpdateNonDefaults(new Person { FirstName = "JJ" }, x => x.LastName == "Hendrix"); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@1 WHERE (\"LastName\" = @0)")); db.UpdateOnly(new Person { FirstName = "JJ" }, x => x.FirstName); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@0")); db.UpdateOnly(new Person { FirstName = "JJ" }, x => x.FirstName, x => x.LastName == "Hendrix"); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@1 WHERE (\"LastName\" = @0)")); db.UpdateOnly(new Person { FirstName = "JJ", LastName = "Hendo" }, q => q.Update(x => x.FirstName)); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@0")); db.UpdateOnly(new Person { FirstName = "JJ" }, q => q.Update(x => x.FirstName).Where(x => x.FirstName == "Jimi")); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@1 WHERE (\"FirstName\" = @0)")); db.UpdateFmt<Person>(set: "FirstName = {0}".SqlFmt("JJ"), where: "LastName = {0}".SqlFmt("Hendrix")); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET FirstName = 'JJ' WHERE LastName = 'Hendrix'")); db.UpdateFmt(table: "Person", set: "FirstName = {0}".SqlFmt("JJ"), where: "LastName = {0}".SqlFmt("Hendrix")); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET FirstName = 'JJ' WHERE LastName = 'Hendrix'")); db.Delete<Person>( }); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE \"FirstName\"=@FirstName AND \"Age\"=@Age")); db.Delete(, FirstName = }); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE \"Id\"=@Id AND \"FirstName\"=@FirstName AND \"LastName\"=@LastName AND \"Age\"=@Age")); db.DeleteNonDefaults( }); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE \"FirstName\"=@FirstName AND \"Age\"=@Age")); db.DeleteNonDefaults( }, }); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE \"FirstName\"=@FirstName AND \"Age\"=@Age")); db.DeleteById<Person>(); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE \"Id\" = @0")); db.DeleteByIds<Person>(, , }); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE \"Id\" IN (1,2,3)")); db.DeleteFmt<Person>(); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE Age = 27")); db.DeleteFmt(); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE Age = 27")); db.Delete<Person>(x => x.Age == ); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE (\"Age\" = @0)")); db.Delete<Person>(q => q.Where(x => x.Age == )); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE (\"Age\" = @0)")); db.Delete(db.From<Person>().Where(x => x.Age == )); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE (\"Age\" = @0)")); db.DeleteFmt<Person>()); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE Age = 27")); db.DeleteFmt(table: )); Assert.That(db.GetLastSql(), Is.EqualTo("DELETE FROM \"Person\" WHERE Age = 27")); db.Save(, FirstName = }); Assert.That(db.GetLastSql(), Is.EqualTo("INSERT INTO \"Person\" (\"Id\",\"FirstName\",\"LastName\",\"Age\") VALUES (@Id,@FirstName,@LastName,@Age)")); db.Save(, FirstName = }); Assert.That(db.GetLastSql(), Is.EqualTo("UPDATE \"Person\" SET \"FirstName\"=@FirstName, \"LastName\"=@LastName, \"Age\"=@Age WHERE \"Id\"=@Id")); db.Save(, FirstName = }, , FirstName = }); db.SaveAll(, FirstName = }, , FirstName = } });
