开源项目 12 ServiceStack.OrmLite
using ServiceStack; using ServiceStack.DataAnnotations; using ServiceStack.OrmLite; using ServiceStack.Text; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp2.test1 { public class Class12 { //ServiceStack.OrmLite //原文:https://www.cnblogs.com/xxfcz/p/7045808.html //原文:https://www.cnblogs.com/wang2650/category/780821.html public void test1() { //查询数据 string connString = "data source=.\\sqlexpress;initial catalog=MyDbContext;user id=sa;password=sa;"; var dbFactory = new OrmLiteConnectionFactory(connString, SqlServerDialect.Provider); using (var db = dbFactory.Open()) { var roles = db.Select<Book>(); "Roles: {0}".Print(roles.Dump());//输出到控制台 } } public void test2() { //创建表,如果表已存在,那么删除后再创建 OrmLiteConfig.DialectProvider = SqlServerDialect.Provider; string connString = "data source=.\\sqlexpress;initial catalog=MyDbContext;user id=sa;password=sa;"; using (var db = connString.OpenDbConnection()) { db.DropAndCreateTable<Book2>(); } } public void test3() { //创建表,并插入一条数据。表如果已存在,则不插入数据 string connectionString = "data source=.\\sqlexpress;initial catalog=MyDbContext;user id=sa;password=sa;"; var dbFactory = new OrmLiteConnectionFactory(connectionString, SqlServerDialect.Provider); using (var db = dbFactory.Open()) { if (db.CreateTableIfNotExists<Book2>()) { db.Insert(new Book2 { Id = 1, Name = "Seed Data" }); } } } public void test4() { //插入 string connectionString = "data source=.\\sqlexpress;initial catalog=MyDbContext;user id=sa;password=sa;"; var dbFactory = new OrmLiteConnectionFactory(connectionString, SqlServerDialect.Provider); using (var db = dbFactory.Open()) { //db.CreateTableIfNotExists<Book2>(); //db.Insert(new Book2 { Id = 1, Name = "Seed Data1" }); //db.Insert(new Book2 { Id = 2, Name = "Seed Data2" }); //db.Insert(new Book2 { Id = 3, Name = "Seed Data3" }); //db.Insert(new Book2 { Id = 4, Name = "Seed Data4" }); //db.Insert(new Book2 { Id = 5, Name = "Seed Data5" }); ////异步 新增 //Task<long> task = db.InsertAsync(new Book2 { Id = 6, Name = "Seed Data6" }); //Console.WriteLine("The Sum is:" + task.Result); ////只新增 指定字段 //db.InsertOnly<Book2>(new Book2 { Id = 6, Name = "Seed Data6" }, c => c.Id); ////新增数据 返回自增长ID。 这里需要修改一下环境,数据库表设置主键、设置自增长,Model的Id增加特性AutoIncrement、PrimaryKey //long id = db.Insert(new Book2 { Name = "Seed Data5" }, selectIdentity: true); //Console.WriteLine(id); ////插入数据的时候,自动插入创建时间等 通用字段 ////全局的 插入时 过滤 //OrmLiteConfig.InsertFilter = (dbCmd, row) => { // var auditRow = row as BaseEntity; // if (auditRow != null) // auditRow.CreatedDate = auditRow.ModifiedDate = DateTime.UtcNow; //}; ////db.CreateTableIfNotExists<Book3>(); //db.Insert(new Book3 { Id = 3, Name = "Seed Data3" }); //db.Insert(new Book3 { Id = 4, Name = "Seed Data4" }); //db.Insert(new Book3 { Id = 5, Name = "Seed Data5" }); ////直接执行sql语句 //db.ExecuteSql("INSERT INTO Book2 (Id, Name) VALUES (@Id, @Name)", new Book2 { Id = 5, Name = "Seed Data5" }); //批量添加 int i = 0; var rows = "A,B,B,C,C,C,D,D,E".Split(',').Map(x => new Book2 { Name = x, Id = (i++) }); db.InsertAll(rows); } } public void test5() { //删除 string connectionString = "data source=.\\sqlexpress;initial catalog=MyDbContext;user id=sa;password=sa;"; var dbFactory = new OrmLiteConnectionFactory(connectionString, SqlServerDialect.Provider); using (var db = dbFactory.Open()) { //db.DeleteById<Book2>(0); //db.Delete<Book2>(new Book2 { Id = 1, Name = "B" }); //db.Delete<Book2>(p => p.Name == "D"); ////这里没有测试成功,原因未知 //var updatedRow = db.SingleById<Book4>(1); // fresh version //db.DeleteById<Book4>(id: updatedRow.Id, rowVersion: updatedRow.RowVersion); } } public void test6() { string connectionString = "data source=.\\sqlexpress;initial catalog=MyDbContext;user id=sa;password=sa;"; var dbFactory = new OrmLiteConnectionFactory(connectionString, SqlServerDialect.Provider); using (var db = dbFactory.Open()) { //db.Update(new Book2 { Id = 2, Name="BB" }); // sql:UPDATE "Person" SET "FirstName" = 'Jimi',"LastName" = 'Hendrix',"Age" = 27 WHERE "Id" = 1 //db.Update(new Book2 { Id=1, Name = "CCCC" }, p => p.Name == "BBCCB"); //Console.WriteLine(db.GetLastSql()); ////部分更新 //db.UpdateNonDefaults(new Book2 { Name = "CC" }, p => p.Name == "CCCC"); ////部分更新 //db.UpdateOnly(new Book2 { Name = "DD" }, //onlyFields: p => new { p.Name }, //where: p => p.Id == 1); //先获取 再更新 var updatedRow = db.SingleById<Book2>(1); updatedRow.Name = "AA"; db.Update(updatedRow); //打印SQL语句 Console.WriteLine(db.GetLastSql()); } } public void test7() { } public class Book4 { public int Id { get; set; } public string Name { get; set; } public ulong RowVersion { get; set; } } public class BaseEntity { public int Id { get; set; } public DateTime CreatedDate { get; set; } public DateTime ModifiedDate { get; set; } public string ModifiedBy { get; set; } } public class Book3 : BaseEntity { public string Name { get; set; } } [Serializable] [Alias("Book2")] public class Book2 { //[AutoIncrement] //[PrimaryKey] public int Id { get; set; } public string Name { get; set; } } [Serializable] [Alias("Book")] public class Book { public int Id { get; set; } public string Name { get; set; } } } }