public IEnumerable <dynamic> GetDynamicPageList <TResult>(LayuiPaginationIn p, SqlLam <TResult> sql)
{
int totalCount = 0;
sql.QueryPage(p.page, p.limit);
var reader = client.GetReader(sql.GetSql(), sql.GetParameters());
var list = reader.Read();
totalCount = reader.Read <int>().FirstOrDefault();
p.total = totalCount;
return(list);
}
public void Do(ProviderType type)
{
SqlLam <Test> sql = new SqlLam <Test>("u", type);
//sql.Top(10);
DbClient db = new DbClient();
db.Error = (s, e) =>
{
Console.WriteLine(e.Message);
};
db.Log = (s) =>
{
Log.WriteLog(1, "Insert_Log", s.Text);
};
//var list = db.GetList<Test>(sql.GetSql(), sql.GetParameters());
//foreach (var item in list)
//{
// Console.WriteLine(string.Format("id: {0}, int 16: {1} , double: {2}, datetime: {3} , guid: {4}, string: {5}", item.Id, item.F_Int16, item.F_Double, item.F_DateTime, item.F_Guid, item.F_String));
//}
DateTime begin = DateTime.Now;
Console.WriteLine(string.Format("当前时间: {0}", begin));
//遍历入库
//for (int i = 0; i < 10000; i++)
//{
// sql.Clear();
// sql.Insert(new Test());
// string insertSql = sql.GetSql();
// int count = db.ExecuteNonQuery(insertSql, sql.GetParameters());
//}
//一次性入库
sql.Clear();
List <Test> list = new List <Test>(100);
for (int i = 0; i < 200; i++)
{
list.Add(new Test());
}
sql.Insert(list);
string insertSql = sql.GetSql();
int count = db.ExecuteNonQuery(insertSql, sql.GetParameters());
TimeSpan ts = DateTime.Now - begin;
Console.WriteLine(string.Format("共用时: {0}", ts.TotalMilliseconds));
}
public IEnumerable <dynamic> GetDynamicPageList <TResult>(PaginationIn p, SqlLam <TResult> sql)
{
p.WatchStart();
int totalCount = 0;
sql.QueryPage(p.page, p.rows);
var reader = client.GetReader(sql.GetSql(), sql.GetParameters());
var list = reader.Read();
totalCount = reader.Read <int>().FirstOrDefault();
p.records = totalCount;
p.WatchEnd();
return(list);
}
static void Main5(string[] args)
{
//GlobalConfig.UseDb(ProviderType.Oracle);
SqlLam <User> sql = new SqlLam <User>("u", ProviderType.SQLite);
sql.QueryPage(2).OrderBy(m => m.F_Email);
string sqlString = sql.GetSql();
Console.WriteLine(string.Format("SQL: {0}", sqlString));
var ps = sql.GetParameters();
foreach (var item in ps)
{
Console.WriteLine(string.Format("Key: {0}, Value: {1}", item.Key, item.Value));
}
//测试字典
Console.Read();
}
public dynamic GetDynamicEntity(Expression <Func <T, bool> > where = null)
{
return(client.GetList(sql.GetSql(), sql.GetParameters()).FirstOrDefault());
}
static void Main2(string[] args)
{
//List<SqlColumnEntity> userColumns = new List<SqlColumnEntity>();
//userColumns.Add(new SqlColumnEntity("age", "t_age"));
GlobalConfig.AddTable(new SqlTableEntity(typeof(User), "Sys_User"));
GlobalConfig.AddTable(new SqlTableEntity(typeof(Area), "Sys_Area"));
List <SqlColumnEntity> testColumns = new List <SqlColumnEntity>();
testColumns.Add(new SqlColumnEntity("Other")
{
Ignore = true
});
//testColumns.Add(new SqlColumnEntity("Id") { Key = true });
GlobalConfig.AddTable(new SqlTableEntity(typeof(Test2))
{
});
GlobalConfig.UseDb(ProviderType.MySql);
//var sql = new SqlLam<Area>().As("a");
//sql = sql.GroupBy(m => m.F_Layers).Select(m => new { Layers = m.F_Layers }).Count(m => new { Count = m.F_Id }).Having().OrderByDescending(m => m.F_Layers);
//SqlLam<User> sql = new SqlLam<User>().As("u");
//sql = sql.Distinct(m => m.F_Layers).Select(m => new { A = m.F_ParentId });
//var sql = new SqlLam<User>().As("u").Where(m => m.F_Account == "abc").Select(m => m.F_Account, m => m.F_Birthday);
//var sqlJoin = sql.Join<UserLogon>((a, u) => a.F_Id == u.F_UserId && a.F_Account == u.F_AnswerQuestion, aliasName: "ul").Where(m => m.F_Language == "cc").Select(m => m.F_LockEndDate, m => m.F_LogOnCount);
//sql = sql.Select(m => m.F_CreatorTime);
//sqlJoin.Join<Area>((u, a) => u.F_Id == a.F_Id, SqlJoinType.RIGHT, aliasName: "a");
//sql.OrderBy<string>(m => m.F_DepartmentId, m => m.F_Id);
//var sql = new SqlLam<UserLogon>("ul");
//sql.Having(m => !m.F_MultiUserLogin, m => m.F_LogOnCount > 1).OrderBy(m => m.F_LogOnCount);
//sql.Where(m => m.F_MultiUserLogin);
DateTime dt = DateTime.Now;
//sql.Select(m => m.F_LogOnCount).SelectDistinct(m => m.F_LastVisitTime)
// .Where(m => m.F_LogOnCount > 100 || m.F_AllowStartTime == dt).And(m => m.F_Language == "abc" || m.F_Id == "a")
// .GroupBy(m => m.F_UserId, m => m.F_Theme).SelectFunction(SqlFunctionType.AVG, m => m.F_LogOnCount).OrderBy(m => m.F_Question);
//sql.Where(m => m.F_LogOnCount > 100).Begin().And(m => m.F_Language == "abc").Or(m => m.F_AnswerQuestion == "c").End()
// .GroupBy(m => m.F_UserId).OrderBy(m => m.F_Question);
#region join
//string[] array = new string[] { "a", "b", "c" };
//var sql = new SqlLam<User>("u");
//sql.Join<UserLogon>((u, ul) => u.F_Id == ul.F_UserId, aliasName: "ul").Where(m => m.F_UserId == "abc");
//sql.Where(m => array.Contains(m.F_HeadIcon));
//sql.In<string>(m => m.F_Language, array).NotIn(m => m.F_Question, array, false);
//sql.Where(m => array.Contains(m.F_AnswerQuestion) == !m.F_MultiUserLogin);
#endregion
#region where in sql
//var sqlUser = new SqlLam<User>("u");
//sqlUser.Select(m => m.F_Id).Where(m => m.F_NickName == "test");
//var sql = new SqlLam<UserLogon>("ul");
//sql.In(m => m.F_UserId, sqlUser).And(m => m.F_UserOnLine);
#endregion
#region delete
//var sqlUser = new SqlLam<User>("u");
//sqlUser.Select(m => m.F_Id).Where(m => m.F_NickName == "test");
//var sql = new SqlLam<UserLogon>("ul");
//sql.Delete().In(m => m.F_UserId, sqlUser, false).And(m => m.F_Theme == "bb");
//sql.Truncate();
//sql.Update(m => new { m.Id, m.Name }, null, new { Id = 2 });
#endregion
#region update
Test2 model = new Test2();
model.Id = 2;
model.Name = "Roc";
model.FullName = "Chengpeng333444";
var sql = new SqlLam <Test2>("t");
//sql.Update(new { }, m => m.Name).Where(m => m.Id == 1);
//sql.Update(model, m => m.Name).Where(m => m.Id == model.Id);
//sql.Update(model).Where(m => m.Id == model.Id);
//sql.Update(model, m => m.FullName, m => m.Name).Where(m => m.Id == model.Id);
//sql.Update(model, m => new { m.FullName, m.Name, m.AddTime }).Where(m => m.Id == model.Id);
//User user = new User();
//user.Id = 1;
//user.Name = "www";
//user.F_WeChat = "微信";
////sql.Update(new { Id = 1, Name = "aaa" }).Where(m => m.Id);
//sql.Update(user).Where(m => m.Id);
#endregion
string sqlString = sql.GetSql();
Console.WriteLine(string.Format("SQL: {0}", sqlString));
var ps = sql.GetParameters();
foreach (var item in ps)
{
Console.WriteLine(string.Format("Key: {0}, Value: {1}", item.Key, item.Value));
}
int count = ExecuteNoQuery(sqlString, ps);//insert update delete 使用
Console.WriteLine(string.Format("受影响的行数: {0}", count));
//测试字典
Console.Read();
}
static void Main1(string[] args)
{
//GlobalConfig.UseDb(ProviderType.MySql);
SqlLam <User> sql = new SqlLam <User>("u");
sql.Select(m => new { m.F_Account, m.F_Birthday });
//sql.Where(m => m.F_CreatorTime == DateTime.Now || m.F_DepartmentId == "aaa");
////sql.And(m => m.F_DeleteUserId == "a");
sql.And().Begin();
sql.Or(m => m.F_Description == "bbb");
sql.Or(m => m.F_DutyId == "333");
sql.End();
//sql.Select(m => m.F_SecurityLevel, m => m.F_RoleId).GroupBy(m => m.F_NickName);
//sql.Where(m => m.F_RoleId == "a").OrderBy(m => m.Id);
//sql.Delete(m => m.Id == 1);
//sql.Truncate();// 有问题
int age = 10;
UserLogon ul = new UserLogon();
ul.F_Language = age.ToString();
User u = new User();
u.Id = 1;
u.Name = "roc";
u.F_Account = "不知道";
//sql.Select(m => new { m.F_Account, m.Name, m.F_WeChat });
//sql.Where(m => m.F_LastModifyTime == DateTime.Now.AddDays(3));
//sql.Delete().Join<UserLogon>((a, b) => a.F_Id == u.Name, aliasName: "ul").Where(m => m.F_UserId == u.F_Account);
//sql.Where(m => m.Id == 1);
//sql.Update(u, m => new { m.F_Account, m.F_RoleId }).Join<UserLogon>((a, b) => a.F_Id == u.Name, aliasName: "ul").Where(m => m.F_UserId == u.F_Account);
//sql.Where(m => m.Id == 1);
List <User> areas = new List <User>();
areas.Add(new User()
{
F_Id = "1"
});
areas.Add(new User()
{
F_CreatorTime = DateTime.Now
});
//User[] us = new User[] { };
//sql.Insert(areas, m => new { m.Id, m.Name, m.F_WeChat });
//sql.Insert(areas, m => new { m.F_WeChat, m.Name, m.F_SortCode });
//sql.Insert(new { Id = 1, Name = "Roc" }, increment: true);
//SqlLam<UserLogon> sqlUl = new SqlLam<UserLogon>("ul");
//sqlUl.Select(m => new { F_WeChat = m.F_UserId, Name = m.F_Theme, F_SortCode = m.F_Question }).Where(m => m.F_UserId == "aa");
//sql.InsertWithQuery(m => new { m.F_WeChat, m.Name, m.F_SortCode }, sqlUl);
string sqlString = sql.GetSql();
Console.WriteLine(string.Format("SQL: {0}", sqlString));
var ps = sql.GetParameters();
foreach (var item in ps)
{
Console.WriteLine(string.Format("Key: {0}, Value: {1}", item.Key, item.Value));
}
Console.Read();
}
