整理Simple.Data使用方法

官方:http://simplefx.org/simpledata/docs/index.html

Insert

var user = db.Users.Insert(Name: "Ford", Password: "hoopy", Age: 29);var user = new User {Name = "Zaphod", Password = "zarquon", Age = 42};
var actual = db.Users.Insert(user);  //插入id自增值方法
var db = DatabaseHelper.Open().WithOptions(new AdoOptions(identityInsert: true));
var user = db.Users.Insert(Id: 42, Name: "Arthur", Password: "Tea", Age: 30);
db.ClearOptions();//批量插入
var users = new[]{new User { Name = "Slartibartfast", Password = "bistromathics", Age = 777 },new User { Name = "Wowbagger", Password = "teatime", Age = int.MaxValue }};
IList<User> actuals = db.Users.Insert(users).ToList<User>(); //User actual  = db.Users.Insert(users);//动态对象
dynamic user = new ExpandoObject();user.Name = "Marvin";user.Password = "diodes";user.Age = 42000000;var actual = db.Users.Insert(user);//异常捕捉
ErrorCallback onError = (o, exception) => passed = true;
IList<dynamic> actuals = db.Users.Insert(users,onError).ToList();//插入图片byte[]
var inserted = db.Images.Insert(Id: 1, TheImage: image);

Update

//通过id修改
db.Users.UpdateById(Id: 1, Name: "Ford", Password: "hoopy", Age: 29);
//通过主键修改
var user = new User { Id = 2, Name = "Zaphod", Password = "zarquon", Age = 42 };
db.Users.Update(user);
//动态对象修改
dynamic user = new ExpandoObject();
user.Id = 3;
user.Name = "Marvin";
user.Password = "diodes";
user.Age = 42000000;
db.Users.Update(user);//修改条件,指定修改列
db.Customers.UpdateAll(db.Customers.Orders.OrderId == 1, Name: "Updated");
db.test.SchemaTable.UpdateAll(db.test.SchemaTable.Id == 1138, Description: "Updated"
db.CompoundKeyMaster.UpdateAll(db.CompoundKeyMaster.CompoundKeyDetail.Value == 1, Description: "Updated");//批量修改
List<Customer> customers = db.Customers.All().ToList<Customer>();
foreach (var customer in customers)
{customer.Address = "Updated";
}
db.Customers.Update(customers);

Delete

//指定Id修改
var count = db.DeleteTest.DeleteById(1);//删除全部
var count = db.DeleteTest.DeleteAll();//事物
var tx = db.BeginTransaction();
tx.DeleteTest.Insert(Id: 1);
var count = tx.DeleteTest.DeleteById(1);
tx.Commit();

Query

//获取总数
var count = db.Users.GetCount();int count = db.Users.GetCount(db.Users.Age > 30);int count = db.Users.GetCountByName("Bob");int max = db.iNethinkCMS_Channel.Select(db.iNethinkCMS_Channel.Id.Max()).ToScalarOrDefault<int>();//存在
db.Users.Exists();
db.Users.Exists(db.Users.Age > 30);
db.Users.Exists(db.Users.Age == -1);
db.Users.ExistsByName("Bob")var actual = db.Users.QueryById(1).Select(db.Users.Name.As("Alias")).First();  //actual.Alias
var actual = db.Users.QueryById(1).Select(db.Users.Name).First();                    //actual.Name//1到100中取前10个
var querys = db.PagingTest.QueryById(1.to(100)).Take(10);
//1到100中取第11个到20个
var querys = db.PagingTest.QueryById(1.to(100)).Skip(10).Take(10);
//排除1到100 ,取101到110
var querys = db.PagingTest.QueryById(1.to(100)).OrderByDescending(db.PagingTest.Id).Skip(0).Take(10);
//总数和集合 同时返回
Promise<int> count;
var list = db.PagingTest.QueryById(1.to(50)).Take(10).WithTotalCount(out count).ToList();List<dynamic> list = db.PagingTest.QueryById(1.to(50)).Select(db.PagingTest.Id).WithTotalCount(out count).Take(10).ToList();List<dynamic> list = db.PagingTest.QueryById(1.to(50)).Select(db.PagingTest.Id).OrderByDescending(db.PagingTest.Id).WithTotalCount(out count).Take(10).ToList();var list = db.PagingTest.QueryById(1.to(50)).WithTotalCount(out count).Take(10).ToList();//查询多条件
var order = db.Customers.QueryByNameAndAddress("Test", "100 Road").Orders.FirstOrDefault();string name = db.Customers.Query().Select(db.Customers.Name).Where(db.Customers.CustomerId == 0) // There is no CustomerId 0
            .OrderByName().Take(1) // Should return only one record no matter what.ToScalarOrDefault<string>();//指定Name生成List
List<string> name = db.Customers.Query().Select(db.Customers.Name).OrderByName().ToScalarList<string>();
string[] name = db.Customers.Query().Select(db.Customers.Name).OrderByName().ToScalarArray<string>();