private static void CustomAttribute()
{
Console.WriteLine("");
Console.WriteLine("#### Custom Attribute Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
ConnectionString = Config.ConnectionString,
DbType = DatabaseType.SqlServer,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute,
ConfigureExternalServices = new ConfigureExternalServices()
{
EntityService = (property, column) =>
{
var attributes = property.GetCustomAttributes(true); //get all attributes
if (attributes.Any(it => it is KeyAttribute)) // by attribute set primarykey
{
column.IsPrimarykey = true;
}
},
EntityNameService = (type, entity) =>
{
var attributes = type.GetCustomAttributes(true);
if (attributes.Any(it => it is TableAttribute))
{
entity.DbTableName = (attributes.First(it => it is TableAttribute) as TableAttribute).Name;
}
}
}
});
db.CodeFirst.InitTables <AttributeTable>();//Create Table
db.Insertable(new AttributeTable()
{
Id = Guid.NewGuid().ToString(), Name = "Name"
}).ExecuteCommand();
var list = db.Queryable <AttributeTable>().ToList();
Console.WriteLine("#### Custom Attribute End ####");
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### CodeFirst Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
DbType = DatabaseType.SqlServer,
ConnectionString = Config.ConnectionString3,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true
});
db.DbMaintenance.CreateDatabase();
db.CodeFirst.InitTables(typeof(CodeFirstTable1));//Create CodeFirstTable1
db.Insertable(new CodeFirstTable1()
{
Name = "a", Text = "a"
}).ExecuteCommand();
var list = db.Queryable <CodeFirstTable1>().ToList();
Console.WriteLine("#### CodeFirst end ####");
}
private static void MasterSlave()
{
Console.WriteLine("");
Console.WriteLine("#### MasterSlave Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
ConnectionString = Config.ConnectionString,//Master Connection
DbType = DatabaseType.SqlServer,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
SlaveConnectionConfigs = new List <SlaveConnectionConfig>()
{
new SlaveConnectionConfig()
{
HitRate = 10, ConnectionString = Config.ConnectionString2
},
new SlaveConnectionConfig()
{
HitRate = 10, ConnectionString = Config.ConnectionString2
}
}
});
db.Aop.OnLogExecuted = (s, p) =>
{
Console.WriteLine(db.Ado.Connection.ConnectionString);
};
Console.WriteLine("Master:");
db.Insertable(new Order()
{
Name = "abc", CustomId = 1, CreateTime = DateTime.Now
}).ExecuteCommand();
Console.WriteLine("Slave:");
db.Queryable <Order>().First();
Console.WriteLine("#### MasterSlave End ####");
}
private static void QueryConditions()
{
Console.WriteLine("");
Console.WriteLine("#### Query Conditions Start ####");
AgileClient db = GetInstance();
/*** By expression***/
//id=@id
var list = db.Queryable <Order>().Where(it => it.Id == 1).ToList();
//id=@id or name like '%'+@name+'%'
var list2 = db.Queryable <Order>().Where(it => it.Id == 1 || it.Name.Contains("jack")).ToList();
//Create expression
var exp = Expressionable.Create <Order>()
.And(it => it.Id == 1)
.Or(it => it.Name.Contains("jack")).ToExpression();
var list3 = db.Queryable <Order>().Where(exp).ToList();
/*** By sql***/
//id=@id
var list4 = db.Queryable <Order>().Where("id=@id", new { id = 1 }).ToList();
//id=@id or name like '%'+@name+'%'
var list5 = db.Queryable <Order>().Where("id=@id or name like '%'+@name+'%' ", new { id = 1, name = "jack" }).ToList();
/*** By dynamic***/
//id=1
var conModels = new List <IConditionalModel>();
conModels.Add(new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1"
}); //id=1
var student = db.Queryable <Order>().Where(conModels).ToList();
//Complex use case
List <IConditionalModel> Order = new List <IConditionalModel>();
conModels.Add(new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1"
}); //id=1
conModels.Add(new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.Like, FieldValue = "1"
}); // id like '%1%'
conModels.Add(new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.IsNullOrEmpty
});
conModels.Add(new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.In, FieldValue = "1,2,3"
});
conModels.Add(new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.NotIn, FieldValue = "1,2,3"
});
conModels.Add(new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.NoEqual, FieldValue = "1,2,3"
});
conModels.Add(new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.IsNot, FieldValue = null
}); // id is not null
conModels.Add(new ConditionalCollections()
{
ConditionalList = new List <KeyValuePair <WhereType, ConditionalModel> >()// (id=1 or id=2 and id=1)
{
//new KeyValuePair<WhereType, ConditionalModel>( WhereType.And ,new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }),
new KeyValuePair <WhereType, ConditionalModel> (WhereType.Or, new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2"
}),
new KeyValuePair <WhereType, ConditionalModel> (WhereType.And, new ConditionalModel()
{
FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2"
})
}
});
var list6 = db.Queryable <Order>().Where(conModels).ToList();
/*** Conditional builder ***/
// use whereif
string name = "";
int id = 1;
var query = db.Queryable <Order>()
.WhereIF(!string.IsNullOrEmpty(name), it => it.Name.Contains(name))
.WhereIF(id > 0, it => it.Id == id).ToList();
//clone new Queryable
var query2 = db.Queryable <Order>().Where(it => it.Id == 1);
var list7 = query2.Clone().Where(it => it.Name == "jack").ToList(); //id=1 and name = jack
var list8 = query2.Clone().Where(it => it.Name == "tom").ToList(); //id=1 and name = tom
Console.WriteLine("#### Condition Screening End ####");
}
private static void DistributedTransactionExample()
{
Console.WriteLine("");
Console.WriteLine("#### Distributed TransactionExample Start ####");
AgileClient db = new AgileClient(new List <ConnectionConfig>()
{
new ConnectionConfig()
{
ConfigId = "1", DbType = DatabaseType.SqlServer, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true
},
new ConnectionConfig()
{
ConfigId = "2", DbType = DatabaseType.SqlServer, ConnectionString = Config.ConnectionString2, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true
}
});
//use db1
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem));//
db.Insertable(new Order()
{
Name = "order1", CreateTime = DateTime.Now
}).ExecuteCommand();
Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable <Order>().Count());
//use db2
db.ChangeDatabase("2");
db.DbMaintenance.CreateDatabase();//Create Database2
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem));
db.Insertable(new Order()
{
Name = "order1", CreateTime = DateTime.Now
}).ExecuteCommand();
Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable <Order>().Count());
// Example 1
Console.WriteLine("Example 1");
try
{
db.BeginTran();
db.ChangeDatabase("1");//use db1
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception();
db.CommitTran();
}
catch
{
db.RollbackTran();
Console.WriteLine("---Roll back");
db.ChangeDatabase("1");//use db1
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
}
// Example 2
Console.WriteLine("Example 2");
var result = db.UseTran(() =>
{
db.ChangeDatabase("1");//use db1
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception("");
});
if (result.IsSuccess == false)
{
Console.WriteLine("---Roll back");
db.ChangeDatabase("1");//use db1
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
}
// Example 3
Console.WriteLine("Example 3");
var result2 = db.UseTranAsync(() =>
{
db.ChangeDatabase("1");//use db1
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
db.Deleteable <Order>().ExecuteCommand();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception("");
});
result2.Wait();
if (result.IsSuccess == false)
{
Console.WriteLine("---Roll back");
db.ChangeDatabase("1");//use sqlserver
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use mysql
Console.WriteLine(db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
}
Console.WriteLine("#### Distributed TransactionExample End ####");
}