public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Saveable Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
DbType = DatabaseType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
//insert or update
db.Saveable <Order>(new Order()
{
Id = 1, Name = "jack"
}).ExecuteReturnEntity();
//insert or update
db.Saveable <Order>(new Order()
{
Id = 1000, Name = "jack", CreateTime = DateTime.Now
})
.InsertColumns(it => new { it.Name, it.CreateTime, it.Price }) //if insert into name,CreateTime,Price
.UpdateColumns(it => new { it.Name, it.CreateTime }) //if update set name CreateTime
.ExecuteReturnEntity();
Console.WriteLine("");
Console.WriteLine("#### Saveable End ####");
}
private static void AgileClient()
{
//Create db
Console.WriteLine("#### AgileClient Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
DbType = DatabaseType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
//If no exist create datebase
db.DbMaintenance.CreateDatabase();
//Use db query
var dt = db.Ado.GetDataTable("select 1");
//Create tables
db.CodeFirst.InitTables(typeof(OrderItem), typeof(Order));
var id = db.Insertable(new Order()
{
Name = "order1", CustomId = 1, Price = 0, CreateTime = DateTime.Now
}).ExecuteReturnIdentity();
//Insert data
db.Insertable(new OrderItem()
{
OrderId = id, Price = 0, CreateTime = DateTime.Now
}).ExecuteCommand();
Console.WriteLine("#### AgileClient 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 ####");
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Deleteable Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
DbType = DatabaseType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
//by entity
db.Deleteable <Order>().Where(new Order()
{
Id = 1111
}).ExecuteCommand();
//by primary key
db.Deleteable <Order>().In(1111).ExecuteCommand();
//by primary key array
db.Deleteable <Order>().In(new int[] { 1111, 2222 }).ExecuteCommand();
//by expression
db.Deleteable <Order>().Where(it => it.Id == 11111).ExecuteCommand();
Console.WriteLine("#### Deleteable End ####");
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### SimpleClient Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
DbType = DatabaseType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
Console.WriteLine("#### SimpleClient End ####");
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### SqlQueryable Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
DbType = DatabaseType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true
});
int total = 0;
var list = db.SqlQueryable <Order>("select * from [order]").ToPageList(1, 2, ref total);
//by expression
var list2 = db.SqlQueryable <Order>("select * from [order]").Where(it => it.Id == 1).ToPageList(1, 2);
//by sql
var list3 = db.SqlQueryable <Order>("select * from [order]").Where("id=@id", new { id = 1 }).ToPageList(1, 2);
Console.WriteLine("#### SqlQueryable 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 ####");
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Updateable Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
DbType = DatabaseType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
/*** 1.entity or List ***/
var updateObj = new Order()
{
Id = 1, Name = "order1"
};
var updateObjs = new List <Order> {
new Order()
{
Id = 11, Name = "order11"
},
new Order()
{
Id = 12, Name = "order12"
}
};
//update all columns by primary key
var result = db.Updateable(updateObj).ExecuteCommand(); //update single
var result2 = db.Updateable(updateObjs).ExecuteCommand(); //update List<Class>
//Ignore Name and Price
var result3 = db.Updateable(updateObj).IgnoreColumns(it => new { it.CreateTime, it.Price }).ExecuteCommand();
//only update Name and CreateTime
var result4 = db.Updateable(updateObj).UpdateColumns(it => new { it.Name, it.CreateTime }).ExecuteCommand();
//If there is no primary key
var result5 = db.Updateable(updateObj).WhereColumns(it => new { it.Id }).ExecuteCommand(); //update single by id
var result6 = db.Updateable(updateObjs).WhereColumns(it => new { it.Id }).ExecuteCommand(); //update List<Class> by id
/*** 2.by expression ***/
//update name,createtime
var result7 = db.Updateable <Order>(it => new Order()
{
Name = "a", CreateTime = DateTime.Now
}).Where(it => it.Id == 11).ExecuteCommand();
var result71 = db.Updateable <Order>().SetColumns(it => new Order()
{
Name = "a", CreateTime = DateTime.Now
}).Where(it => it.Id == 11).ExecuteCommand();
//only update name
var result8 = db.Updateable <Order>(it => it.Name == "Name" + "1").Where(it => it.Id == 1).ExecuteCommand();
var result81 = db.Updateable <Order>().SetColumns(it => it.Name == "Name" + "1").Where(it => it.Id == 1).ExecuteCommand();
//
/*** 3.by Dictionary ***/
var dt = new Dictionary <string, object>();
dt.Add("id", 1);
dt.Add("name", "abc");
dt.Add("createTime", DateTime.Now);
var dtList = new List <Dictionary <string, object> >();
dtList.Add(dt);
var t66 = db.Updateable(dt).AS("[Order]").WhereColumns("id").ExecuteCommand();
var t666 = db.Updateable(dtList).AS("[Order]").WhereColumns("id").ExecuteCommand();
/*** 4.Other instructions ***/
var caseValue = "1";
//Do not update NULL columns
db.Updateable(updateObj).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
//if 1 update name else if 2 update name,createtime
db.Updateable(updateObj)
.UpdateColumnsIF(caseValue == "1", it => new { it.Name })
.UpdateColumnsIF(caseValue == "2", it => new { it.Name, it.CreateTime })
.ExecuteCommand();
//Use Lock
db.Updateable(updateObj).With(SqlWith.UpdLock).ExecuteCommand();
//Where Sql
db.Updateable(updateObj).Where("id=@x", new { x = "1" }).ExecuteCommand();
Console.WriteLine("#### Updateable End ####");
}
public static void Init()
{
Console.WriteLine();
Console.WriteLine("#### DbFirst Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
DbType = DatabaseType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true
});
db.DbFirst.CreateClassFile("c:\\Demo\\1", "Models");
db.DbFirst.Where("Student").CreateClassFile("c:\\Demo\\2", "Models");
db.DbFirst.Where(it => it.ToLower().StartsWith("view")).CreateClassFile("c:\\Demo\\3", "Models");
db.DbFirst.Where(it => it.ToLower().StartsWith("view")).CreateClassFile("c:\\Demo\\4", "Models");
db.DbFirst.IsCreateAttribute().CreateClassFile("c:\\Demo\\5", "Models");
db.DbFirst.IsCreateDefaultValue().CreateClassFile("c:\\Demo\\6", "Demo.Models");
db.DbFirst.SettingClassTemplate(old => { return(old); })
.SettingNamespaceTemplate(old => { return(old); })
.SettingPropertyDescriptionTemplate(old =>
{
return(@" /// <summary>
/// Desc_New:{PropertyDescription}
/// Default_New:{DefaultValue}
/// Nullable_New:{IsNullable}
/// </summary>");
})
.SettingPropertyTemplate(old => { return(old); })
.SettingConstructorTemplate(old => { return(old); })
.CreateClassFile("c:\\Demo\\7");
foreach (var item in db.DbMaintenance.GetTableInfoList())
{
string entityName = item.Name.ToUpper();/*Format class name*/
db.MappingTables.Add(entityName, item.Name);
foreach (var col in db.DbMaintenance.GetColumnInfosByTableName(item.Name))
{
db.MappingColumns.Add(col.DbColumnName.ToUpper() /*Format class property name*/, col.DbColumnName, entityName);
}
}
db.DbFirst.IsCreateAttribute().CreateClassFile("c:\\Demo\\8", "Models");
//Use Razor Template
//db.DbFirst.UseRazorAnalysis(RazorFirst.DefaultRazorClassTemplate).CreateClassFile("");
Console.WriteLine("#### DbFirst 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 ####");
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Ado Start ####");
AgileClient db = new AgileClient(new ConnectionConfig()
{
DbType = DatabaseType.SqlServer,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
//sql
var dt = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new List <AgileParameter>()
{
new AgileParameter("@id", 1),
new AgileParameter("@name", "2")
});
//sql
var dt2 = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new { id = 1, name = "2" });
//Stored Procedure
//var dt3 = db.Ado.UseStoredProcedure().GetDataTable("sp_school", new { name = "张三", age = 0 });
//var nameP = new AgileParameter("@name", "张三");
//var ageP = new AgileParameter("@age", null, true);//isOutput=true
//var dt4 = db.Ado.UseStoredProcedure().GetDataTable("sp_school", nameP, ageP);
//There are many methods to under db.ado
var list = db.Ado.SqlQuery <Order>("select * from [order] ");
var intValue = db.Ado.SqlQuerySingle <int>("select 1");
db.Ado.ExecuteCommand("delete [order] where id>1000");
//db.Ado.xxx
Console.WriteLine("#### Ado End ####");
Console.WriteLine("#### Ado sqlmap Start ####");
//sqlmap sql param
Dictionary <string, object> dicParams = new Dictionary <string, object>();
dicParams.Add("OrderId", 1);
dicParams.Add("Price", 0);
var cmd = db.Ado.GetSqlMap(new SQLMapConfig("SqlMap")
{
SQLMapFileName = "AgileSqlMap.xml", Code = "test5", Parameters = dicParams
});
var dt5 = db.Ado.GetDataTable(cmd.TransferedSQL, cmd.ParamNames);
Console.WriteLine(dt5.Rows.Count);
Console.WriteLine("#### Ado sqlmap 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 ####");
}
public virtual List <string> GetDataBaseList(AgileClient db)
{
return(db.Ado.SqlQuery <string>(this.GetDataBaseSql));
}