Animal ORM 开发指南
【1.创建表脚本(SQLSERVER举例)】
CREATE TABLE [dbo].[Person] (
[Id] int DEFAULT ((0)) Primary key NOT NULL,
[Name] varchar(50) COLLATE Chinese_PRC_CI_AS DEFAULT '' NOT NULL,
[Sex] int DEFAULT ((0)) NOT NULL,
[Age] int DEFAULT ((0)) NOT NULL,
[Birthday] datetime DEFAULT ('1900-01-01') NOT NULL
)
[Id] int DEFAULT ((0)) Primary key NOT NULL,
[Name] varchar(50) COLLATE Chinese_PRC_CI_AS DEFAULT '' NOT NULL,
[Sex] int DEFAULT ((0)) NOT NULL,
[Age] int DEFAULT ((0)) NOT NULL,
[Birthday] datetime DEFAULT ('1900-01-01') NOT NULL
)
【2.创建实体类(AnModel++工具)】
using Animal.ORM.Model;
[Tiger("Person")]
public class PersonEntity
{
[Koala]
[Cat("Id")]
public int? Id { get; set; }
[Cat("Name")]
public string Name { get; set; }
[Cat("Sex")]
public int? Sex { get; set; }
[Cat("Age")]
public int? Age { get; set; }
[Cat("Birthday")]
public DateTime? Birthday { get; set; }
}
[Tiger("Person")]
public class PersonEntity
{
[Koala]
[Cat("Id")]
public int? Id { get; set; }
[Cat("Name")]
public string Name { get; set; }
[Cat("Sex")]
public int? Sex { get; set; }
[Cat("Age")]
public int? Age { get; set; }
[Cat("Birthday")]
public DateTime? Birthday { get; set; }
}
Tiger:表(Table)
Koala:主键(Primary Key)
Koala(ture):自增主键(Primary Key and Self growth)
Cat:列(Column)
【3.下载】
Animal.ORM.Core 下载
1.应用程序(Framework)版本>= .NET CORE 2.1
2.NUGET 安装命令：
PM> Install-Package Animal.ORM.Core -Version 1.1.5
3.选择需要安装的数据库客户端：
SqlServer
PM> Install-Package System.Data.SqlClient -Version 4.6.1
MySql
PM> Install-Package MySql.Data -Version 8.0.17
Oracle
PM> Install-Package Oracle.ManagedDataAccess.Core -Version 2.19.91
SQLite
PM> Install-Package System.Data.SQLite.Core -Version 1.0.111
Animal.ORM.Framework 下载
1.应用程序(Framework)版本>= .NET Framework 4.6.1
2.NUGET 安装命令：
PM> Install-Package Animal.ORM.Framework -Version 1.1.5
3.选择需要安装的数据库客户端：
SqlServer
不需要安装(Framework自带)
MySql
PM> Install-Package MySql.Data -Version 8.0.17
Oracle
PM> Install-Package Oracle.ManagedDataAccess -Version 19.9.0
SQLite
PM> Install-Package System.Data.SQLite -Version 1.0.112
【4.增删改查(CURD)】
Add
using
Animal.ORM;
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using ( IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var person= new PersonEntity() { Id=1, Age = 26, Name = "ketty", Sex = 2, Birthday = new DateTime(2000, 9, 3) };
animal.Add(person);
}
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using ( IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var person= new PersonEntity() { Id=1, Age = 26, Name = "ketty", Sex = 2, Birthday = new DateTime(2000, 9, 3) };
animal.Add(person);
}
Update
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using ( IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var person= new PersonEntity() { Id=1, Age = 26, Name = "Lilei", Sex = 2, Birthday = new DateTime(2000, 9, 3) };
animal.Save(person);
}
using ( IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var person= new PersonEntity() { Id=1, Age = 26, Name = "Lilei", Sex = 2, Birthday = new DateTime(2000, 9, 3) };
animal.Save(person);
}
Read
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using ( IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var person = animal.SingleDog<PersonEntity>(1);
var person2 = animal.SingleDog<PersonEntity>(x=>x.Id==1); //Lamada Expression that return Single Entity
List<PersonEntity> personList = animal.FlyPigs<PersonEntity>(x=>x.Age>20); //Lamada Expression that return List
}
using ( IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var person = animal.SingleDog<PersonEntity>(1);
var person2 = animal.SingleDog<PersonEntity>(x=>x.Id==1); //Lamada Expression that return Single Entity
List<PersonEntity> personList = animal.FlyPigs<PersonEntity>(x=>x.Age>20); //Lamada Expression that return List
}
Delete
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using ( IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var result = animal.Delete<PersonEntity>(1);
var result2 = animal.Delete<PersonEntity>(x=>x.Id==1); //Lambda Expression
}
using ( IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var result = animal.Delete<PersonEntity>(1);
var result2 = animal.Delete<PersonEntity>(x=>x.Id==1); //Lambda Expression
}
【5.ADO.NET】
ExeNQuery
using
Animal.ORM;
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
string sql = "INSERT INTO Person(Id,Name,Sex,Age,Birthday)values(@Id,@Name,@Sex,@Age,@Birthday)";
SqlParameter[] paras =
{
new SqlParameter("@Id", 6)
,new SqlParameter("@Name", "A")
,new SqlParameter("@Sex", 1)
,new SqlParameter("@Age", 1)
,new SqlParameter("@Birthday", DateTime.Parse("2009-01-01"))
};
animal.ExeNQuery(sql, paras);
}
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
string sql = "INSERT INTO Person(Id,Name,Sex,Age,Birthday)values(@Id,@Name,@Sex,@Age,@Birthday)";
SqlParameter[] paras =
{
new SqlParameter("@Id", 6)
,new SqlParameter("@Name", "A")
,new SqlParameter("@Sex", 1)
,new SqlParameter("@Age", 1)
,new SqlParameter("@Birthday", DateTime.Parse("2009-01-01"))
};
animal.ExeNQuery(sql, paras);
}
ExeReader,ExeScalar,ExeAdapter
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var sql = "select Id,Name,Sex from Person where Id=@Id";
SqlParameter[] paras = { new SqlParameter("@Id", 1) };
var obj = animal.ExeReader(sql, paras).ToList< PersonEntity>();
}
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var sql = "select Name from Person where Id=@Id";
SqlParameter[] paras = { new SqlParameter("@Id", 1) };
var obj = animal.ExeScalar(sql, paras);
}
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
var sql = "select * from Person where Id=@Id";
SqlParameter[] paras = { new SqlParameter("@Id", 1) };
var dataAdapter = animal.ExeAdapter(sql, paras);
DataSet ds=new DataSet();
dataAdapter.Fill(ds);
}
【6.日志(TortoiseLog)】
using
Animal.ORM;
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
TortoiseLog log = new TortoiseLog();
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer, log))
{
var person = new PersonEntity() { Id = 32, Age = 26, Name = "Jim", Sex = 2, Birthday = new DateTime(1000, 1, 1) };
animal.Add(person);
Console.WriteLine(log.ErrorSql);
Console.WriteLine(log.ErrorMsg);
Console.WriteLine(log.Success);
//view the sql statement of each step (V1.0.8)
List <string> sqlRecords=log.SqlRecords;
}
Console.Read();
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
TortoiseLog log = new TortoiseLog();
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer, log))
{
var person = new PersonEntity() { Id = 32, Age = 26, Name = "Jim", Sex = 2, Birthday = new DateTime(1000, 1, 1) };
animal.Add(person);
Console.WriteLine(log.ErrorSql);
Console.WriteLine(log.ErrorMsg);
Console.WriteLine(log.Success);
//view the sql statement of each step (V1.0.8)
List <string> sqlRecords=log.SqlRecords;
}
Console.Read();
【7.事务(Transaction)】
using
Animal.ORM;
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer))
{
animal.OpenTrans();
try
{
var batch = new BatchEntity()
{
BatchID = Guid.NewGuid().ToString(),
AddedOn = DateTime.Now,
Action = "BTest"
};
animal.Add(batch);
{
animal.OpenTrans();
try
{
var batch = new BatchEntity()
{
BatchID = Guid.NewGuid().ToString(),
AddedOn = DateTime.Now,
Action = "BTest"
};
animal.Add(batch);
var eve = new EventEntity()
{
EventID = Guid.NewGuid().ToString(),
EventType = "Test",
TimeEntered=DateTime.Parse("2008-09-09"),
BatchID=Guid.NewGuid().ToString()
};
animal.Add(eve);
{
EventID = Guid.NewGuid().ToString(),
EventType = "Test",
TimeEntered=DateTime.Parse("2008-09-09"),
BatchID=Guid.NewGuid().ToString()
};
animal.Add(eve);
animal.CommitTrans();
//view the sql statement of each step (V1.1.4)
List <string> sqlRecords=animal.GetAllSqlRecords();
}
catch (Exception ex)
{
Console.Write(ex.Message);
animal.RollbackTrans();
}
//view the sql statement of each step (V1.1.4)
List <string> sqlRecords=animal.GetAllSqlRecords();
}
catch (Exception ex)
{
Console.Write(ex.Message);
animal.RollbackTrans();
}
}
【8.单表分页(Single table Pagination)】
using
Animal.ORM;
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
TortoiseLog log=new TortoiseLog ();
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer,log))
{
//Init Page
Pandas page = new Pandas()
{
PIndex = 1,
PSize = 10,
POrderby = "RoleName ASC,RoleFlags DESC"
};
List<RolesEntity> listResult = animal.FlyPigs<RolesEntity>(page, x => x.RoleFlags == "0");
//Total query records
var totalRecords = page.PRecords;
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer,log))
{
//Init Page
Pandas page = new Pandas()
{
PIndex = 1,
PSize = 10,
POrderby = "RoleName ASC,RoleFlags DESC"
};
List<RolesEntity> listResult = animal.FlyPigs<RolesEntity>(page, x => x.RoleFlags == "0");
//Total query records
var totalRecords = page.PRecords;
}
【9.多表分页(Muti table pagination)】
using
Animal.ORM;
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
TortoiseLog log=new TortoiseLog ();
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer,log))
{
//Init Page
Pandas page = new Pandas()
{
PIndex = 1,
PSize = 10,
POrderby = "RoleID DESC" // SqlServer 客户端 POrderby 必须赋值
};
string sql=@"SELECT R.[RoleID]
,R.[RoleName]
,R.[Description]
,R.[TaskMask]
,R.[RoleFlags]
FROM [Roles] R
Join [User] U on U.RoleID=R.RoleID
where U.UserID>10";
// Return DataTable
var dtResult = animal.FlyPigs(page, sql);
var totalRecords = page.PRecords;
using Animal.ORM.Model;
var constr = "Data Source=localhost;Initial Catalog=reportserver;User Id=sa; Password=*******";
TortoiseLog log=new TortoiseLog ();
using (IAnimalDbConnection animal = new AnimalDbConnection(constr, AnimalDbProvider.SqlServer,log))
{
//Init Page
Pandas page = new Pandas()
{
PIndex = 1,
PSize = 10,
POrderby = "RoleID DESC" // SqlServer 客户端 POrderby 必须赋值
};
string sql=@"SELECT R.[RoleID]
,R.[RoleName]
,R.[Description]
,R.[TaskMask]
,R.[RoleFlags]
FROM [Roles] R
Join [User] U on U.RoleID=R.RoleID
where U.UserID>10";
// Return DataTable
var dtResult = animal.FlyPigs(page, sql);
if (dtResult.HasRows())
{
}
var totalRecords = page.PRecords;
}