Animal ORM API 使用文档

本文档基于 User 实体，完整说明 Animal ORM 查询 / 分页 / 条件 / CRUD / 调试 等全部常用 API 的使用方式，面向 业务开发者 & 框架使用者。

一、User 实体定义（基础）

​x
[Tiger("sys_user")]
public class User
{
    [Koala(true)]
    [Cat("id")]
    public int Id { get; set; }
​
    [Cat("user_name")]
    public string Name { get; set; }
​
    [Cat("phone")]
    public string Phone { get; set; }
​
    [Cat("email")]
    public string Email { get; set; }
​
    [Cat("password_hash")]
    public string Password { get; set; }
​
    [Cat("is_active")]
    public bool IsActive { get; set; }
​
    [Cat("is_deleted")]
    public bool IsDeleted { get; set; }
​
    [Cat("create_time")]
    public DateTime CreateTime { get; set; }
​
    [Cat("update_time")]
    public DateTime? UpdateTime { get; set; }
}

xxxxxxxxxx
CREATE TABLE [dbo].[sys_user] (
    [id] INT IDENTITY(1,1) NOT NULL,
    [user_name] NVARCHAR(100) NOT NULL,
    [phone] NVARCHAR(50) NULL,
    [email] NVARCHAR(100) NULL,
    [password_hash] NVARCHAR(255) NOT NULL,
    [is_active] BIT NOT NULL DEFAULT(1),
    [is_deleted] BIT NOT NULL DEFAULT(0),
    [create_time] DATETIME NOT NULL DEFAULT(GETDATE()),
    [update_time] DATETIME NULL,
    CONSTRAINT [PK_sys_user] PRIMARY KEY CLUSTERED ([id] ASC)
);
--SqlServer

二、基础查询 API

1. 查询全部

xxxxxxxxxx
var constr = "Data Source=localhost;Initial Catalog=test;User Id=sa; Password=*******";
using (var db = AnimalDb.Create(constr, AnimalDbProvider.SqlServer))
{
   var list = db.Query<User>().ToList();
}

2. Where 条件查询（AND）

xxxxxxxxxx
var list = db.Query<User>()
    .Where(x => !x.IsDeleted)
    .Where(x => x.IsActive==true)
    .ToList();

SQL 语义：

xxxxxxxxxx
WHERE IsDeleted = 0 AND IsActive = 1

3. WhereIF（条件成立才生效）

xxxxxxxxxx
bool? isActive = false;
string name = "A";
var list = db.Query<User>()
                 .WhereIF(!string.IsNullOrEmpty(name), x => x.Name == name)
                 .WhereIF(isActive != null, x => x.IsActive == isActive)
                 .ToList();

适用于：

• 查询参数可选
• 不写 if / else

三、OR 条件相关 API

1. OrIF（条件 OR）

xxxxxxxxxx
var keyword = "123";
var list = db.Query<User>()
                 .Where(x => !x.IsDeleted)
                 .OrIF(!string.IsNullOrEmpty(keyword), x => x.Name.Contains(keyword))
                 .OrIF(!string.IsNullOrEmpty(keyword), x => x.Phone.Contains(keyword))
                 .ToList();

2. OrGroup（OR 分组）

xxxxxxxxxx
var keyword = "kw";
var list = db.Query<User>()
                 .Where(x => x.IsDeleted)
                 .OrGroup(g => g
                     .OrIF(!string.IsNullOrEmpty(keyword), x => x.Name.Contains(keyword))
                     .OrIF(!string.IsNullOrEmpty(keyword), x => x.Phone.Contains(keyword))
                 )
                 .ToList();

SQL 语义：

xxxxxxxxxx
WHERE IsDeleted = 0
AND (Name LIKE '%kw%' OR Phone LIKE '%kw%')

四、AND 分组 API（AndGroup）

1. AndGroup（AND 分组）

xxxxxxxxxx
DateTime? start= DateTime.Now;
DateTime? end = null;
var list = db.Query<User>()
                 .Where(x => x.IsDeleted)
                 .AndGroup(g => g
                     .WhereIF(start != null, x => x.CreateTime >= start)
                     .WhereIF(end != null, x => x.CreateTime <= end)
                 )
                 .ToList();

SQL 语义：

xxxxxxxxxx
WHERE IsDeleted = 0
AND (CreateTime >= @start AND CreateTime <= @end)

五、排序 API

1. OrderBy / OrderByDesc

xxxxxxxxxx
db.Query<User>()
      .OrderBy(x => x.Name)
      .ToList();
​
db.Query<User>()
      .OrderByDesc(x => x.Id)
      .ToList();

六、分页 API

1. Page + ToList

xxxxxxxxxx
var page = new Pandas { PIndex = 1, PSize = 20 };
​
var list = db.Query<User>()
                 .Where(x => !x.IsDeleted)
                 .OrderByDesc(x => x.Id)
                 .Page(page)
                 .ToList();

2. ToPageList（推荐）

xxxxxxxxxx
int total;
var list = db.Query<User>()
                 .Where(x => !x.IsDeleted)
                 .ToPageList(new Pandas { PIndex = 1, PSize = 20 }, out total);

3. 多表分页（推荐）

xxxxxxxxxx
 Pandas page = new Pandas()
 {
     PIndex = 1,
     PSize = 10,
     POrderby = "id asc"  // The SqlServer POrderby must be assigned a value
 };
string sql = @"SELECT U.*
                      FROM sys_user U
                      Join sys_user U2 on U.id=U2.id
                      where U.id>@id";
var resultList = db.FlyPigs<User>(page, sql, new { id = 1 });
//Total query records
var totalRecords = page.PRecords;

七、单值查询 API

1. FirstBlood/ SingleDog/HasData/ Count

xxxxxxxxxx
var user = db.Query<User>()
                 .Where(x => x.Id > 10)
                 .FirstBlood();   // need check user is not null
​
var user = db.SingleDog<User>(1);
​
bool exists = db.Query<User>()
                    .Where(x => x.Phone == "13899988880")
                    .HasData();
​
int count = db.Query<User>()
                  .Where(x => x.IsActive)
                  .Count();
​

八、CRUD API

1. IAnimalDbConnection（核心接口）

xxxxxxxxxx
   bool Add<T>(T t) where T : class,new();
   bool Save<T>(T t) where T : class, new();
   bool Delete<T>(object keyValue) where T : class, new();
   bool Delete<TResult>(Expression<Func<TResult, bool>> func=null ) where TResult : class, new();

2. Add

xxxxxxxxxx
db.Add(new User
{
    Name = "Tom",
    Password = "13456",
    IsActive = true,
    IsDeleted = false,
    CreateTime = DateTime.Now
});

3. Save

xxxxxxxxxx
var user = db.SingleDog<User>(4);  // 或 user=db.Query<User>().Where(x => x.Id == 4).OneFirst()
user.Name = "Tommy";
user.UpdateTime = DateTime.Now;
db.Save(user);

4. Delete（按主键）

xxxxxxxxxx
db.Delete<User>(1);

5. Delete（按条件）

xxxxxxxxxx
db.Delete<User>(x => x.IsDeleted == true);

九、调试 API

1. DebugWhereTree

xxxxxxxxxx
var q = db.Query<User>()
              .Where(x => x.IsDeleted == false)
              .AndGroup(g => g
                      .Where(x => x.CreateTime >= DateTime.Now.AddDays(-1))
                      .Where(x => x.CreateTime <= DateTime.Now)
             );
​
Console.WriteLine(q.DebugWhereTree());

输出示例：

xxxxxxxxxx
AND
  [is_deleted] = @p0
  AND
    ([create_time] >= @p0)
    ([create_time] <= @p0)

2. DebugSql / DebugParameters

xxxxxxxxxx
string sql = q.DebugSql();
var parameters = q.DebugParameters();

十、ADO.NET 基础能力（底层执行层）

Animal ORM 并不是“脱离 ADO.NET 的黑盒 ORM”，而是完全构建在 ADO.NET 之上，对连接、命令、参数、事务都有清晰可控的封装。

1. IAnimalDbConnection（核心接口）

xxxxxxxxxx
public interface IAnimalDbConnection : IDisposable
{
    int ExeNQuery(string sql, IDbDataParameter[] paras = null);
    int ExeNQuery(string sql, object param  = null);
    
    object ExeScalar(string sql, IDbDataParameter[] paras = null);
    object ExeScalar(string sql, object param  = null);
    
    IDataReader ExeReader(string sql, IDbDataParameter[] paras = null);
    IDataReader ExeReader(string sql, object param  = null);
    
    IDbDataAdapter ExeAdapter(string sql, IDbDataParameter[] paras = null);
    IDbDataAdapter ExeAdapter(string sql, object param  = null);
}

说明：

• ✔ 完全基于 IDbConnection
• ✔ 不屏蔽 ADO.NET 能力
• ✔ ORM 只是语法糖

1. ExeNQuery（非查询）

xxxxxxxxxx
int rows = db.ExeNQuery("DELETE FROM sys_user WHERE is_deleted=1",null);

等价于：

xxxxxxxxxx
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE ...";
rows = cmd.ExecuteNonQuery();

2. ExeScalar（单值）

xxxxxxxxxx
int count = Convert.ToInt32(db.ExeScalar("SELECT COUNT(1) FROM sys_user",null));

3. ExeReader（IDataReader 原生读取）

xxxxxxxxxx
  var sql = "select * from sys_user where id=@id";
  var userList = db.ExeReader(sql,new { id = 1 }).ToListFast<User>(); 

4. ExeAdapter

xxxxxxxxxx
 var sql = "select * from sys_user where id=@id";
 var dataAdapter = db.ExeAdapter(sql,new { id = 1 }); 
 DataSet ds=new DataSet();
 dataAdapter.Fill(ds);

👉 你可以在 ORM 和纯 ADO.NET 之间自由切换。

5. ORM 与 ADO.NET 混合使用（推荐模式）

xxxxxxxxxx
try
{
    db.OpenTrans();  //Begin Trans
​
    db.Add(new User { Name = "Tom", Password="123456", CreateTime=DateTime.Now, IsActive=true, IsDeleted=false  });
​
    db.ExeNQuery("UPDATE sys_user SET is_active=0 WHERE id=@id",
                 new { id = 1 });
​
    db.CommitTrans();  //Commit Trans
}
catch (Exception ex)
{
    Console.Write(ex.Message);
    db.RollbackTrans(); //Rollback Trans
}

✔ ORM ✔ 原生 SQL ✔ 同一事务

十一、日志(TortoiseLog)能力

xxxxxxxxxx
TortoiseLog log=new TortoiseLog ();
using (var db = AnimalDb.Create(connStr, provider, log))
{
   var list = db.Query<User>().ToList();
   var sucess=log.Success;
   var currentSql=log.SuccessSql;
   List <string> sqlRecords=db.GetAllSqlRecords(); // or sqlRecords=log.SqlRecords
} 

each sql json

xxxxxxxxxx
{
    "Success": "True",
    "Msg": "",
    "SQL": "INSERT INTO Person(Id,Name,Sex,Age,Birthday)values(@Id,@Name,@Sex,@Age,@Birthday)",
    "Paras": {
        "@Id": "1",
        "@Name": "Lilei",
        "@Sex": "1",
        "@Age": "24",
        "@Birthday": "2001/9/3 0:00:00"
    }
}

十二、总结

✔ ORM ≠ 屏蔽 ADO.NET

✔ Animal ORM = Query Builder + CRUD + ADO.NET 执行层

✔ 你可以随时降级为原生 SQL

✔ 适合：

• 高性能场景
• 复杂报表
• 旧系统改造

十三、下载

Animal.ORM.Core 下载

1.应用程序(Core)版本>= .NET CORE 2.1

2.NUGET 安装命令：

xxxxxxxxxx
 PM> Install-Package Animal.ORM.Core -Version 1.2.3

3.选择需要安装的数据库客户端：

SqlServer

xxxxxxxxxx
 PM> Install-Package System.Data.SqlClient -Version 4.6.1

MySql

xxxxxxxxxx
 PM> Install-Package MySql.Data -Version 8.0.17

Oracle

xxxxxxxxxx
 PM> Install-Package Oracle.ManagedDataAccess.Core -Version 2.19.91

SQLite

xxxxxxxxxx
 PM> Install-Package System.Data.SQLite.Core -Version 1.0.111

Animal.ORM.Framework 下载

1.应用程序(Framework)版本>= .NET Framework 4.6.1

2.NUGET 安装命令：

xxxxxxxxxx
 PM> Install-Package Animal.ORM.Framework -Version 1.2.3

3.选择需要安装的数据库客户端：

SqlServer

不需要安装(Framework自带)

MySql

xxxxxxxxxx
 PM> Install-Package MySql.Data -Version 8.0.17

Oracle

xxxxxxxxxx
 PM> Install-Package Oracle.ManagedDataAccess -Version 19.9.0

SQLite

xxxxxxxxxx
 PM> Install-Package System.Data.SQLite -Version 1.0.112

文档完

联系QQ ：455362030