1.Create Table (eg.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
)
2.Generate By AnCode++ Tools(Download)
[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.Download
Animal.ORM.Core DownLoad
1.Application Target Framework Version >= .NET CORE 2.1
2.NUGET Install Command:
PM> Install-Package Animal.ORM.Core -Version 1.1.5
3.Install the client of the corresponding type database:
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 DownLoad
1.Application Target Framework Version >= .NET Framework 4.6.1
2.NUGET Install Command:
PM> Install-Package Animal.ORM.Framework -Version 1.1.5
3.Install the client of the corresponding type database:
SqlServer
Not need register client
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
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);
}
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
}
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
}
5.ADO.NET
ExeNQuery
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
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
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);
var eve = new EventEntity()
{
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 (1.1.4)
List <string> sqlRecords=animal.GetAllSqlRecords();
}
catch (Exception ex)
{
Console.Write(ex.Message);
animal.RollbackTrans();
}
}
8.Single table pagination
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;
}
9.Muti table pagination
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 Client POrderby must be valued
};
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())
{
}
//Total query records
var totalRecords = page.PRecords;
}