/// <summary>
/// 批量更新
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void Update(Student2 p, SqlSugarClient db)
{
db.UpdateRange(StudentListUpd);
var list = db.Queryable<Student>().ToList();
db.SqlBulkReplace(StudentListUpd2);
list = db.Queryable<Student>().ToList();
}
public void Init()
{
SqlSugarClient db = new SqlSugarClient("server=.;uid=sa;pwd=sasa;database=SqlSugarTest", "server=localhost;uid=sa;pwd=sasa;database=SqlSugarTest");
var l1= db.Queryable<Student>().ToList();//走从
var d1= db.Queryable<Student>().ToDataTable();//走从
var t3 = db.Queryable<Student>().ToList();//走从
db.Insert<Student>(new Student() { name="主" });
db.BeginTran();
var l2 = db.Queryable<Student>().ToList();//走主
db.CommitTran();
var l3 = db.Queryable<Student>().ToList();//走从
db.Dispose();
}
private static void SqlSugar(int eachCount)
{
GC.Collect();//回收资源
System.Threading.Thread.Sleep(2000);//休息2秒
PerHelper.Execute(eachCount, "SqlSugar", () =>
{
using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
{
var list = conn.Queryable<Test>().OrderBy("id").ToPageList(1, 10);
}
});
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(10000);
int id = 1;
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable("select * from Student WHERE ID>@id", new SqlParameter("@id", id));
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query<Models.Student>("select * from Student where id>@id", new { id = id}).ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Queryable<Models.Student>().Where(c => c.id == id).ToList();
}, m => { }, "sqlSugar 拉姆达");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.Student.Where(c => c.id == id).ToList();
}, m => { }, "EF4.0+sql05 拉姆达");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(10000);
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable("select * from(select *,row_number() over(order by id) as r from Student where id>@id) t where t.r between @b and @e", new SqlParameter("@id","0"), new SqlParameter("@b", 11), new SqlParameter("@e", 20));
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query<Models.Student>("select * from(select *,row_number() over(order by id) as r from Student where id>@id) t where t.r between @b and @e", new { id = 0,b=11,e=20 }).ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Queryable<Models.Student>().Where(c => c.id > 0).OrderBy("id").ToPageList(2, 10);
}, m => { }, "sqlSugar 拉姆达");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.Student.Where(c => c.id == 2).OrderBy(c=>c.id).Skip(10).Take(10).ToList();
}, m => { }, "EF4.0+sql05 拉姆达");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
public WarmUp()
{
Console.WriteLine("开启预热");
//预热处理
for (int i = 0; i < 2; i++)
{
using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
{
var list = conn.QueryFirst<Test>("select top 1 * from Test");
conn.Update(new Test());
}
using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
{
var list = conn.Queryable<Test>().Where(it => 1 == 2).ToList();
conn.Update(new Test());
}
}
Console.WriteLine("预热完毕");
Console.WriteLine("----------------比赛开始-------------------");
}
/// <summary>
/// 查询今日余刀
/// 用于查刀和催刀
/// </summary>
/// <returns>余刀表</returns>
public Dictionary <long, int> GetTodayAtkCount()
{
try
{
using SqlSugarClient dbClient = SugarUtils.CreateSqlSugarClient(DBPath);
return(dbClient.Queryable <GuildBattle>()
.AS(BattleTableName)
.Where(attack => attack.Time > Utils.GetUpdateStamp())
.GroupBy(member => member.Uid)
.Select(member => new
{
member.Uid,
times = SqlFunc.AggregateCount(member.Uid)
})
.ToList()
.ToDictionary(member => member.Uid,
member => member.times));
}
catch (Exception e)
{
ConsoleLog.Error("Database error", ConsoleLog.ErrorLogBuilder(e));
return(null);
}
}
/// <summary>
/// 通过code获取组织信息
/// </summary>
/// <param name="orgCode"></param>
/// <returns></returns>
public OrgModel getOrgModelByCode(string orgCode)
{
using (SqlSugarClient db = BaseDB.GetClient())
{
List <OrgModel> res = db.Queryable <Organization, OrganizationOrg>((o, oo) => new object[] {
JoinType.Left, o.id == oo.orgid
})
.Where((o, oo) => o.orgCode == orgCode)
.Select((o, oo) => new OrgModel
{
organization = o,
parentOrgId = oo.parentorgid
}).ToList();
if (res.Count > 0)
{
return(res[0]);
}
else
{
return(null);
}
}
}
/// <summary>
/// 删除某一条角色数据（连同角色有关的权限全部删除掉）
/// </summary>
/// <param name="roleTypeVguid"></param>
/// <returns></returns>
public bool DeleteRoleType(string roleTypeVguid)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
Guid vguid = Guid.Parse(roleTypeVguid);
Sys_Role sysRole = _dbMsSql.Queryable <Sys_Role>().Where(i => i.Vguid == vguid).SingleOrDefault();
string sysRoleJson = JsonHelper.ModelToJson <Sys_Role>(sysRole);
//保存日志
_logLogic.SaveLog(2, 30, Common.CurrentUser.GetCurrentUser().LoginName, "角色", sysRoleJson);
try
{
result = _dbMsSql.Delete <Sys_Role>(i => i.Vguid == vguid);
_dbMsSql.Delete <Sys_Role_Module>(i => i.RoleVGUID == vguid);
}
catch (Exception exp)
{
Common.LogHelper.LogHelper.WriteLog(exp.ToString());
}
return(result);
}
}
/// <summary>
/// 获取数据
/// </summary>
/// <param name="orderFun">排序的表达式</param>
/// <param name="ob">排序方式 默认为正序</param>
/// <param name="PageIndex">页码 从1开始计</param>
/// <param name="PageSize">页容量</param>
/// <param name="where">条件</param>
/// <param name="pars">参数</param>
/// <returns></returns>
public ISugarQueryable <T> GetData(Expression <Func <T, object> > orderFun = null, OrderByType ob = OrderByType.Asc, int PageIndex = 0, int PageSize = 0, string where = "1=1", object pars = null)
{
ISugarQueryable <T> Tempdb = _ssc.Queryable <T>().Where(where, pars);
//需要分页
if (PageIndex > 0)
{
int count = Tempdb.Count();
int Skip = (PageIndex - 1) * PageSize;
int SumPage = Convert.ToInt32(Math.Ceiling(count * 1.0 / PageSize)); //总页数
bool dxp = PageIndex > (SumPage / 2);
if (dxp) //页码大于一半用倒序
{
Tempdb.OrderBy(orderFun, (ob == OrderByType.Asc ? OrderByType.Desc : OrderByType.Asc));
int mod = count % PageSize;
if (PageIndex * PageSize >= count)
{
Skip = 0; PageSize = mod == 0 ? PageSize : mod;
}
else
{
Skip = (SumPage - PageIndex - 1) * PageSize + mod;
}
}
else
{
Tempdb.OrderBy(orderFun, ob);//升序
}
Tempdb.Skip(Skip);
Tempdb.Take(PageSize);
}
return(Tempdb);
}
public CategoryCreateDTO GetModel(int id)
{
using (var db = new SqlSugarClient(Connection))
{
CategoryCreateDTO result = null;
var model = db.Queryable <R_Category>().InSingle(id);
if (model != null)
{
result = new CategoryCreateDTO()
{
Id = model.Id,
Name = model.Name,
Description = model.Description,
DiscountRate = model.DiscountRate,
Pid = model.PId,
IsDiscount = model.IsDiscount,
Sorted = model.Sorted
};
}
return(result);
}
}
public ActionResult CancelhomestayOrder()
{
SqlSugarClient db = new SqlSugarClient(
new ConnectionConfig()
{
ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"],
DbType = DbType.Oracle, //设置数据库类型
IsAutoCloseConnection = true, //自动释放数据务，如果存在事务，在事务结束后释放
InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
});
JObject isSuccess = new JObject();
try
{
//查询房源订单结果
var target = db.Queryable <HomestayOrder>().InSingle(Convert.ToInt32(Request.Form["orderID"]));
if (target.status == -1)
{
target.status = -2;
db.Updateable(target).UpdateColumns(it => new { it.status }).ExecuteCommand();
isSuccess.Add("isSuccess", true);
}
else
{
isSuccess.Add("isSuccess", false);
isSuccess.Add("message", "你的房源订单不处在待出行状态，无法取消");
}
}
catch (Exception ex)
{
isSuccess.Add("isSuccess", false);
isSuccess.Add("message", "操作失败");
return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented)));
}
return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented)));
}
public ActionResult RemoveHomestay(FormCollection form)
{
SqlSugarClient db = new SqlSugarClient(
new ConnectionConfig()
{
ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"],
DbType = DbType.Oracle, //设置数据库类型
IsAutoCloseConnection = true, //自动释放数据务，如果存在事务，在事务结束后释放
InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
});
JObject isSuccess = new JObject();
try
{
//检测房源是否有正处在活跃的订单
var checkif = db.Queryable <HomestayOrder>().Where(it => it.homestay_id == Convert.ToInt32(Request.Form["productID"]) && it.status <= 0).ToArray();
if (checkif.Length == 0)
{
db.Deleteable <SpecialPrice>(it => it.homestay_id == Convert.ToInt32(Request.Form["productID"])).ExecuteCommand();
db.Deleteable <Homestay>().In(Convert.ToInt32(Request.Form["productID"])).ExecuteCommand();
isSuccess.Add("isSuccess", true);
}
else
{
isSuccess.Add("isSuccess", false);
isSuccess.Add("message", "不允许删除！你有房源订单正在处于活跃状态！");
}
}
catch (Exception ex)
{
isSuccess.Add("isSuccess", false);
isSuccess.Add("message", "操作失败");
return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented)));
}
return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented)));
}
/// <summary>
/// 根据餐饮项目id集合获取餐饮项目集合
/// </summary>
/// <param name="projectIdList">餐饮项目id集合</param>
/// <returns>餐饮项目集合</returns>
public List <ProjectJoinDetailDTO> GetProjectListBy(List <int> projectIdList)
{
using (var db = new SqlSugarClient(Connection))
{
List <ProjectJoinDetailDTO> list = db.Queryable <R_Project>()
.JoinTable <R_ProjectDetail>((s1, s2) => s1.Id == s2.R_Project_Id)
.Where <R_ProjectDetail>((s1, s2) => projectIdList.Contains(s2.Id))
.Select <R_ProjectDetail, ProjectJoinDetailDTO>((s1, s2) =>
new ProjectJoinDetailDTO
{
Id = s2.Id,
ProjectName = s1.Name,
CategoryId = s1.R_Category_Id,
CostPrice = s2.CostPrice,
Description = s2.Description,
Price = s2.Price,
Property = s1.Property,
R_Project_Id = s2.R_Project_Id,
Unit = s2.Unit,
UnitRate = s2.UnitRate
}).ToList();
return(list);
}
}
public static void UpdateEsbDepartmentParentID(SqlSugarClient Ddb)
{
Ddb.CodeFirst.InitTables(typeof(DepartmentTrees));
var newlist = Ddb.Queryable <DepartmentTrees, DepartmentTrees>((st, sc) => new object[] { JoinType.Inner, st.ParentDepartmentId == sc.DepartmentId })
.Select
(
(st, sc) =>
new DepartmentTrees
{
Id = st.Id,
DD_Id = st.DD_Id,
DD_ParentId = sc.DD_Id,
DepartmentId = st.DepartmentId,
FullName = st.FullName,
ParentDepartmentId = st.ParentDepartmentId,
CreateDate = st.CreateDate,
level = st.level
}
).ToList();
Ddb.Updateable <DepartmentTrees>(newlist).ExecuteCommand();
Console.Write("\r\n执行完成\r\n");
}
public ExtendCreateDTO GetModel(int id)
{
using (var db = new SqlSugarClient(Connection))
{
ExtendCreateDTO model = null;
var data = db.Queryable <R_ProjectExtend>().InSingle(id);
if (data != null)
{
model = new ExtendCreateDTO()
{
Id = data.Id,
CyxmKzType = data.CyxmKzType,
Description = data.Description,
Name = data.Name,
Price = data.Price,
Unit = data.Unit,
ExtendType = data.R_ProjectExtendType_Id
};
}
return(model);
}
}
/// <summary>
/// 分页查询习题信息列表
/// </summary>
/// <param name="searchParam"></param>
/// <param name="para"></param>
/// <returns></returns>
public JsonResultModel <V_Business_Exercises_Infomation> GetExerciseListBySearch(Business_Exercises_Infomation_Search searchParam, GridParams para)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
JsonResultModel <V_Business_Exercises_Infomation> jsonResult = new JsonResultModel <V_Business_Exercises_Infomation>();
var query = _dbMsSql.Queryable <V_Business_Exercises_Infomation>().Where(i => i.Status == 1);
if (!string.IsNullOrEmpty(searchParam.ExercisesName))
{
query.Where(i => i.ExercisesName.Contains(searchParam.ExercisesName));
}
//if (!string.IsNullOrEmpty(searchParam.Status))
//{
// int status = int.Parse(searchParam.Status);
// query.Where(i => i.Status == status);
//}
if (!string.IsNullOrEmpty(searchParam.InputType))
{
int inputType = int.Parse(searchParam.InputType);
query.Where(i => i.InputType == inputType);
}
if (!string.IsNullOrEmpty(searchParam.EffectiveDate))
{
DateTime effectiveDate = DateTime.Parse(searchParam.EffectiveDate);
query.Where(i => i.EffectiveDate < effectiveDate);
}
query.OrderBy(para.sortdatafield + " " + para.sortorder);
jsonResult.TotalRows = query.Count();
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize);
//存入操作日志表
string logData = JsonHelper.ModelToJson <JsonResultModel <V_Business_Exercises_Infomation> >(jsonResult);
_ll.SaveLog(3, 7, Common.CurrentUser.GetCurrentUser().LoginName, "习题列表", logData);
return(jsonResult);
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
using (SqlSugarClient db = SugarDao.GetInstance()) //开启数据库连接
{
db.IsGetPageParas = true; //使用无参模式直接将Requst中的ID传给@id无需在代码中写出来
var list = db.Queryable <STUDENT>().Where("id=:id").ToList();
//sqlable queryable sqlquery通用
//获取页面所有参数到键值对
//var kvs= SqlSugarTool.GetParameterDictionary();
//以前写法
//var xx = db.Queryable<Student>().Where("id=@id", new { id=Request["id"] }).ToList();
}
}
catch (Exception)
{
throw new Exception("请在当前页面URL地址后面加上参数?id=1");
}
}
private static void MasterSlave()
{
Console.WriteLine("");
Console.WriteLine("#### MasterSlave Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = Config.ConnectionString,//Master Connection
DbType = DbType.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 ####");
}
/// <summary>
/// 获取系统日志分页信息
/// </summary>
/// <param name="logContent"></param>
/// <param name="logType"></param>
/// <param name="opUserName"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="pagination"></param>
/// <returns></returns>
public List <Base_SysLog> GetLogList(string logContent, string logType, string opUserName, DateTime?startTime, DateTime?endTime, Pagination <Base_SysLog> pagination)
{
var whereExp = LinqHelper.True <Base_SysLog>();
if (!logContent.IsNullOrEmpty())
{
whereExp = whereExp.And(x => x.LogContent.Contains(logContent));
}
if (!logType.IsNullOrEmpty())
{
whereExp = whereExp.And(x => x.LogType == logType);
}
if (!opUserName.IsNullOrEmpty())
{
whereExp = whereExp.And(x => x.OpUserName.Contains(opUserName));
}
if (!startTime.IsNullOrEmpty())
{
whereExp = whereExp.And(x => x.OpTime >= startTime);
}
if (!endTime.IsNullOrEmpty())
{
whereExp = whereExp.And(x => x.OpTime <= endTime);
}
// List<Base_SysLog> List = dbSqlClent.Queryable<Base_SysLog>()
// .Where(whereExp).ToDataTablePage(pagination.page, pagination.rows);
// pagination.records = source.Count();
// source = source.OrderBy($"{pagination.sidx} {pagination.sord}");
var total = 0;
var List = dbSqlClent.Queryable <Base_SysLog>()
.Where(whereExp).OrderBy(st => pagination.sidx, (SqlSugar.OrderByType)System.Enum.Parse(typeof(SqlSugar.OrderByType), pagination.sord.ToString()))
.ToPageList(pagination.PageIndex, pagination.PageRows, ref total);
pagination.records = total;
return(List);
}
public void AddNewestRecord(SqlSugarClient db, string groupId, string record)
{
var user = db.Queryable <NEWEST_CHAT_RECORD>().Where(c => c.GROUP_ID == groupId).ToList().FirstOrDefault();
if (user == null)
{
user = new NEWEST_CHAT_RECORD
{
ID = Guid.NewGuid().ToString("N").ToUpper(),
DATETIME_CREATED = DateTime.Now,
USER_CREATED = "SYS",
STATE = "A",
GROUP_ID = groupId,
NEWEST_CHAR_RECORD = record
};
db.Insertable(user).ExecuteCommand();
}
else
{
user.NEWEST_CHAR_RECORD = record;
user.DATETIME_MODIFIED = DateTime.Now;
db.Updateable(user).ExecuteCommand();
}
}
public static void Init()
{
SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = @"PORT=5433;DATABASE=x;HOST=localhost;PASSWORD=haosql;USER ID=postgres",
DbType = DbType.PostgreSQL,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute,
});
//调式代码 用来打印SQL
Db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql);
};
Db.CodeFirst.InitTables(typeof(testmmxxxmm121));
Db.Insertable(new testmmxxxmm121()
{
name = (float)0.01, name2 = 1
}).ExecuteCommand();
var list = Db.Queryable <testmmxxxmm121>().ToList();
}
/// <summary>
/// 根据手术单号查询对应手术单号
/// </summary>
/// <param name="pageDataApo">带分页的pageDataApo</param>
/// <param name="totalCount">返回的总数</param>
/// <returns></returns>
public List <SurgeryOrderDto> GetSurgeryOrderDto(SurgeryOrderApo pageDataApo, out int totalCount)
{
totalCount = 0;
List <SurgeryOrderDto> data;
//查询语句
var queryable = Db.Queryable <SurgeryOrder>()
.Where(it => it.code.StartsWith(pageDataApo.SurgeryOrderCode))
.Select <SurgeryOrderDto>();
//如果小于0，默认查全部
if (pageDataApo.PageSize > 0)
{
data = queryable.ToPageList(pageDataApo.PageIndex, pageDataApo.PageSize, ref totalCount);
}
else
{
data = queryable.ToList();
totalCount = data.Count();
}
return(data);
}
public void Excute(int menuAddressId, int languageId, string uiEngineDir)
{
_uiEngineDir = uiEngineDir;
var data = _db.Queryable <SysBest_MenuAddress>().FirstOrDefault(it => it.Id == menuAddressId);
_data = data;
_languageId = languageId;
string areaBoxDir = FileSugar.MergeUrl(_solutionDir, "Areas", data.AreaName);
string areaDir = FileSugar.MergeUrl(_solutionDir, "Areas", data.AreaName);
string viewsDir = FileSugar.MergeUrl(areaDir, "Views");
string areaRegistrationPath = FileSugar.MergeUrl(areaDir, "{0}AreaRegistration.cs".ToFormat(data.AreaName));
string controllerDir = FileSugar.MergeUrl(areaDir, "Controllers", data.ControllerName + "App");
string controllerDomainDir = FileSugar.MergeUrl(areaDir, "Controllers", data.ControllerName + "App", "Domain");
string controllerDomainPath = FileSugar.MergeUrl(areaDir, "Controllers", data.ControllerName + "App", "Domain", data.ControllerName + "Domain.cs");
string configPath = FileSugar.MergeUrl(viewsDir, "Web.config");
string viewPath = FileSugar.MergeUrl(viewsDir, _data.ControllerName, _data.ActionName + ".cshtml");
var lan = _db.Queryable <SysBest_Language>().FirstOrDefault(it => it.Id == languageId).Suffix;
if (lan.IsValuable() && lan != "zh")
{
viewPath = FileSugar.MergeUrl(viewsDir, _data.ControllerName, _data.ActionName + $"_{lan}.cshtml");
}
string controllerPath = FileSugar.MergeUrl(controllerDir, data.ControllerName + "Controller.cs");
string path = _solutionDir + @"\" + _projectName + ".csproj";
try
{
XDocument doc = XDocument.Load(path);
BuildArea(areaDir, viewsDir, controllerDomainDir, areaRegistrationPath, configPath, doc);
BuildController(controllerDir, controllerPath, controllerDomainPath, doc);
BuildView(viewPath, doc);
BuildDatabaseTableTable();
doc.Save(path);
}
catch (Exception ex)
{
throw ex;
}
}
private static void QueryConditions()
{
Console.WriteLine("");
Console.WriteLine("#### Query Conditions Start ####");
SqlSugarClient 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, SqlSugar.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 override IEnumerable <T_Entity> Query()
{
var list = _sqlSugarClient.Queryable <T_Entity>().Take(QUERY_TAKEN).ToList();
return(list);
}
public override Order Get(Guid key)
{
var order = _sqlClient.Queryable <Model.Order>().Where(x => x.Id == key).Single();
return(Order.Load(order.Id, order.TotalAmount, order.CreateTime));
}
public string PageList(Bootstrap.BootstrapParams bootstrap)
{
var totalNumber = 0;
if (bootstrap.offset != 0)
{
bootstrap.offset = bootstrap.offset / bootstrap.limit + 1;
}
//var order = ExpressionExt.InitO<Sys_user>(bootstrap.sort);
var query = _client.Queryable <Sys_user, Sys_user, Sys_user, Sys_dept, Sys_role>
((s, c, u, d, r) => new object[] {
JoinType.Left, s.CreateBy == c.UserId,
JoinType.Left, s.ModifiedBy == u.UserId,
JoinType.Left, s.DeptId == d.DeptId,
JoinType.Left, s.RoleId == r.RoleId
}).Where((s, c, u, d, r) => s.IsDel == 1 && d.IsDel == 1 && r.IsDel == 1)
.Select((s, c, u, d, r) => new
{
UserId = s.UserId.ToString(),
s.UserName,
s.UserNickname,
d.DeptName,
r.RoleName,
s.Tel,
s.Email,
s.Sex,
s.IsEabled,
s.Remark,
CName = c.UserNickname,
s.CreateDate,
UName = u.UserNickname,
s.ModifiedDate
}).MergeTable();
if (!bootstrap.search.IsEmpty())
{
query.Where((s) => s.UserName == bootstrap.search || s.UserNickname == bootstrap.search);
}
if (!bootstrap.datemin.IsEmpty() && !bootstrap.datemax.IsEmpty())
{
query.Where(s => s.CreateDate > bootstrap.datemin.ToDateTimeB() && s.CreateDate <= bootstrap.datemax.ToDateTimeE());
}
if (bootstrap.order.Equals("desc", StringComparison.OrdinalIgnoreCase))
{
query.OrderBy($"MergeTable.{bootstrap.sort} desc");
}
else
{
query.OrderBy($"MergeTable.{bootstrap.sort} asc");
}
var list = query.ToPageList(bootstrap.offset, bootstrap.limit, ref totalNumber);
//var list = _client.Queryable("Sys_User", "s")
// .AddJoinInfo("Sys_User", "c", "s.CreateBy=c.UserId")
// .AddJoinInfo("Sys_User", "u", "s.ModifiedBy=u.UserId");
//if (!bootstrap.search.IsEmpty())
//{
// list.Where("s.IsDel=@id and s.UserName=@UserName or s.UserNickname=@UserName")
// .AddParameters(new { id = 1, UserName = bootstrap.search });
//}
//else
//{
// list.Where("s.IsDel=@id ")
// .AddParameters(new { id = 1 });
//}
//if (bootstrap.offset != 0)
//{
// bootstrap.offset = bootstrap.offset / bootstrap.limit + 1;
//}
//list.OrderBy($"s.{bootstrap.sort}")
//.Select("s.*,c.UserNickname AS CNAME,u.UserNickname AS UNAME")
//.ToPageList(bootstrap.offset, bootstrap.limit, ref totalNumber);
return(Bootstrap.GridData(list, totalNumber).JilToJson());
}
private static void SubIdentity(SqlSugarClient db)
{
db.CodeFirst.InitTables <Country1, Province1, City1>();
db.DbMaintenance.TruncateTable("Country1");
db.DbMaintenance.TruncateTable("Province1");
db.DbMaintenance.TruncateTable("City1");
db.Insertable(new List <Country1>()
{
new Country1()
{
Id = 1,
Name = "中国",
Provinces = new List <Province1>()
{
new Province1 {
Id = 1001,
Name = "江苏",
citys = new List <City1>()
{
new City1()
{
Id = 1001001, Name = "南通"
},
new City1()
{
Id = 1001002, Name = "南京"
}
}
},
new Province1 {
Id = 1002,
Name = "上海",
citys = new List <City1>()
{
new City1()
{
Id = 1002001, Name = "徐汇"
},
new City1()
{
Id = 1002002, Name = "普陀"
}
}
},
new Province1 {
Id = 1003,
Name = "北京",
citys = new List <City1>()
{
new City1()
{
Id = 1003001, Name = "北京A"
},
new City1()
{
Id = 1003002, Name = "北京B"
}
}
}
}
},
new Country1()
{
Name = "美国",
Id = 2,
Provinces = new List <Province1>()
{
new Province1()
{
Name = "美国小A",
Id = 20001
},
new Province1()
{
Name = "美国小b",
Id = 20002
}
}
},
new Country1()
{
Name = "英国",
Id = 3
}
})
.AddSubList(it => new SubInsertTree()
{
Expression = it.Provinces.First().CountryId,
ChildExpression = new List <SubInsertTree>()
{
new SubInsertTree()
{
Expression = it.Provinces.First().citys.First().ProvinceId
}
}
})
.ExecuteReturnPrimaryKey();
var list = db.Queryable <Country1>()
.Mapper(it => it.Provinces, it => it.Provinces.First().CountryId)
.Mapper(it =>
{
foreach (var item in it.Provinces)
{
item.citys = db.Queryable <City1>().Where(y => y.ProvinceId == item.Id).ToList();
}
})
.ToList();
}
public ISugarQueryable <DBCSTypeChange> LoadEntities(Expression <Func <DBCSTypeChange, bool> > whereLambda)
{
return(db.Queryable <DBCSTypeChange>().WhereIF(whereLambda != null, whereLambda));
}
protected void Page_Load(object sender, EventArgs e)
{
//连接字符串
string connStr = @"Server=.;uid=sa;pwd=sasa;database=SqlSugarTest";
using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接
{
//开启事务，可以不使用事务,也可以使用多个事务
db.BeginTran();
//db.CommitTran 提交事务会,在using结束前自动执行，可以不声名
//db.RollbackTran(); 事务回滚，catch中声名
//查询是允许脏读的，可以声名多个（默认值:不允许）
db.IsNoLock = true;
try
{
/************************************************************************************************************/
/*********************************************1、实体生成****************************************************/
/************************************************************************************************************/
//根据当前数据库生成所有表的实体类文件 （参数：SqlSugarClient ，文件目录，命名空间）
//db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models");
//根据表名生成实体类文件
//db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school");
//根据表名生成class字符串
var str = db.ClassGenerating.TableNameToClass(db, "Student");
//根据SQL语句生成class字符串
var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student");
/************************************************************************************************************/
/*********************************************2、查询********************************************************/
/************************************************************************************************************/
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable<Student>().ToList();
var stud = new Student() { id = 1 };
//查询单条
var single = db.Queryable<Student>().Single(c => c.id==stud.id);
//取10-20条
var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable<Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable<Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1);
//---------Sqlable,创建多表查询---------//
//多表查询
List<School> dataList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList<School>("st.*", new { id = 1 });
//多表分页
List<School> dataPageList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList<School>("st.*", "s.id", 1, 10);
//---------SqlQuery,根据SQL或者存储过程---------//
//用于多用复杂语句查询
var School = db.SqlQuery<Student>("select * from Student");
//获取id
var id = db.SqlQuery<int>("select top 1 id from Student").Single();
//存储过程
//var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 });
/************************************************************************************************************/
/*************************************************3、添加****************************************************/
/************************************************************************************************************/
School s = new School()
{
name = "蓝翔"
};
//插入单条
var id2 = Convert.ToInt32(db.Insert(s));
//插入多条
List<School> sList = new List<School>();
sList.Add(s);
var ids = db.InsertRange(sList);
/************************************************************************************************************/
/*************************************************4、修改****************************************************/
/************************************************************************************************************/
//指定列更新
db.Update<School>(new { name = "蓝翔2" }, it => it.id == id);
//整个实体更新,注意主键必需为实体类的第一个属性
db.Update<School>(new School { id = id, name = "蓝翔2" }, it => it.id == id);
/************************************************************************************************************/
/*************************************************5、删除****************************************************/
/************************************************************************************************************/
db.Delete<School>(10);//注意主键必需为实体类的第一个属性
db.Delete<School>(it => it.id > 100);
db.Delete<School>(new string[] { "100", "101", "102" });
//db.FalseDelete<school>("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.FalseDelete<school>("is_del", it=>it.id==100);
/************************************************************************************************************/
/*************************************************6、基类****************************************************/
/************************************************************************************************************/
string sql = "select * from Student";
db.ExecuteCommand(sql);
db.GetDataTable(sql);
db.GetList<Student>(sql);
db.GetSingle<Student>(sql + " where id=1");
using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader
db.GetScalar(sql);
db.GetString(sql);
db.GetInt(sql);
}
catch (Exception ex)
{
//回滚事务
db.RollbackTran();
throw ex;
}
}//关闭数据库连接
}
/// <summary>
/// 查询出刚插入的1000条数据
/// </summary>
/// <returns></returns>
private static List<Test> GetDeleteList()
{
using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
{
conn.Delete<Test>(it => it.F_String == "Test");
//插入1000条
conn.SqlBulkCopy(GetList);
//查询出插入的1000条
var list = conn.Queryable<Test>().Where(it => it.F_String == "Test").ToList();
return list;
}
}
/// <summary>
/// 删除测试
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void Delete(Student2 p, SqlSugarClient db)
{
db.Delete<Student>(it => it.id == 1);
db.Delete<Student>(new Student() { id=2});
db.Delete<Student,int>(3,4);
db.Delete<Student, int>(it=>it.id,5,6);
var list = db.Queryable<Student>().ToList();
}
private List <BaseIList> schData()
{
UserProc.CheckTime = "";
var exp = Expressionable.Create <BaseIList>().And((b) => b.leibie == "计划表");
if (checkMohu.Checked)
{
if (chkDanHao.chkSel)
{
exp.And((b) => b.dingdanhao.Contains(chkDanHao.txt.Text));
}
if (chkKehu.chkSel)
{
exp.And((b) => b.kehu.Contains(chkKehu.cobodgv.Text));
}
if (chkPinmin.chkSel)
{
exp.And((b) => b.pingmin.Contains(chkPinmin.cobodgv.Text));
}
if (chkZPS.chkSel)
{
exp.And((b) => b.pishu.Contains(chkZPS.txt.Text));
}
if (chkZZ.chkSel)
{
exp.And((b) => b.seming.Contains(chkZZ.cobodgv.Text));
}
if (chkPS.chkSel)
{
exp.And((b) => b.sehao.Contains(chkPS.cobodgv.Text));
}
if (chkTxt1.chkSel)
{
exp.And((b) => b.item0.Contains(chkTxt1.txt.Text));
}
if (chkTxt2.chkSel)
{
exp.And((b) => b.zongliang.Contains(chkTxt2.txt.Text));
}
}
else
{
if (chkDanHao.chkSel)
{
exp.And((b) => b.dingdanhao == (chkDanHao.txt.Text));
}
if (chkKehu.chkSel)
{
exp.And((b) => b.kehu == (chkKehu.cobodgv.Text));
}
if (chkPinmin.chkSel)
{
exp.And((b) => b.pingmin == (chkPinmin.cobodgv.Text));
}
if (chkZPS.chkSel)
{
exp.And((b) => b.pishu == (chkZPS.txt.Text));
}
if (chkZZ.chkSel)
{
exp.And((b) => b.seming == (chkZZ.cobodgv.Text));
}
if (chkPS.chkSel)
{
exp.And((b) => b.sehao == (chkPS.cobodgv.Text));
}
if (chkTxt1.chkSel)
{
exp.And((b) => b.item0 == (chkTxt1.txt.Text));
}
if (chkTxt2.chkSel)
{
exp.And((b) => b.zongliang == (chkTxt2.txt.Text));
}
}
DateTime dateTime;
string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
if (this.chkDtime1.chkFsel)
{
dateTime = chkDtime1.dtimeF.Value;
str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (this.chkDtime1.chkTsel)
{
dateTime = chkDtime1.dtimeT.Value;
str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
exp.And((b) => SqlFunc.Between(b.riqi, str1, str2));
UserProc.CheckTime = str1 + " - " + str2;
}
var getAll1 = db.Queryable <BaseIList>()
.Where(exp.ToExpression())
.OrderBy((b) => b.riqi, OrderByType.Desc)
.ToList();
return(getAll1);
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### DemoN_SplitTable Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.PostgreSQL,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true
});
db.Aop.OnLogExecuted = (s, p) =>
{
Console.WriteLine(s);
};
//初始化分表
db.CodeFirst.SplitTables().InitTables <OrderSpliteTest>();
Console.WriteLine();
//根据最近3个表进行查询
var list = db.Queryable <OrderSpliteTest>().Where(it => it.Pk == Guid.NewGuid())
.SplitTable(tabs => tabs.Take(3))
.Where(it => it.Time == DateTime.Now).ToOffsetPage(1, 2);
Console.WriteLine();
//根据时间选出的表进行查询
var list2 = db.Queryable <OrderSpliteTest>().SplitTable(tabs => tabs.Where(it => it.Date >= DateTime.Now.AddYears(-2))).ToList();
Console.WriteLine();
//删除数据只在最近3张表执行操作
var x = db.Deleteable <OrderSpliteTest>().Where(it => it.Pk == Guid.NewGuid()).SplitTable(tabs => tabs.Take(3)).ExecuteCommand();
Console.WriteLine();
var tableName = db.SplitHelper <OrderSpliteTest>().GetTableName(DateTime.Now.AddDays(-1));
var tableName2 = db.SplitHelper(new OrderSpliteTest()
{
Time = DateTime.Now
}).GetTableNames();
var tableName3 = db.SplitHelper(new List <OrderSpliteTest> {
new OrderSpliteTest()
{
Time = DateTime.Now
},
new OrderSpliteTest()
{
Time = DateTime.Now
},
new OrderSpliteTest()
{
Time = DateTime.Now.AddMonths(-10)
}
}).GetTableNames();
var x2 = db.Updateable <OrderSpliteTest>()
.SetColumns(it => it.Name == "a")
.Where(it => it.Pk == Guid.NewGuid())
.SplitTable(tabs => tabs.InTableNames(tableName2))
.ExecuteCommand();
Console.WriteLine();
//按日分表
var x3 = db.Insertable(new OrderSpliteTest()
{
Name = "A"
}).SplitTable().ExecuteCommand();
Console.WriteLine();
////强制分表类型
var x4 = db.Insertable(new OrderSpliteTest()
{
Name = "A", Time = DateTime.Now.AddDays(-1)
}).SplitTable().ExecuteCommand();
Console.WriteLine("#### CodeFirst end ####");
}
/// <summary>
/// 测试select new
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void SelectNew(Student2 p, SqlSugarClient db)
{
//测试用例
var queryable = db.Queryable <Student>().Where(c => c.id < 10)
.Select <V_Student>(c => new V_Student {
id = c.id, name = c.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET"
});
var list = queryable.ToList();
//多表操作将Student转换成V_Student
var queryable2 = db.Queryable <Student>()
.JoinTable <School>((s1, s2) => s1.sch_id == s2.id)
.Where <School>((s1, s2) => s2.id < 10)
.Select <School, V_Student>((s1, s2) => new V_Student()
{
id = s1.id, name = s1.name, AreaName = "默认地区", SchoolName = s2.name, SubjectName = "NET"
}); //select new 目前只支持这种写法
var list2 = queryable2.ToList();
//select字符串 转换成V_Student
var list3 = db.Queryable <Student>()
.JoinTable <School>((s1, s2) => s1.sch_id == s2.id)
.Where(s1 => s1.id <= 3)
.Select <V_Student>("s1.*,s2.name SchoolName")
.ToList();
//新容器转换函数的支持 只支持ObjToXXX和Convert.ToXXX
var f1 = db.Queryable <InsertTest>().Select <Student>(it => new Student()
{
name = it.d1.ObjToString(),
id = it.int1.ObjToInt() // 支持ObjToXXX 所有函数
}).ToList();
var f2 = db.Queryable <InsertTest>().Select <Student>(it => new Student()
{
name = Convert.ToString(it.d1),//支持Convet.ToXX所有函数
id = it.int1.ObjToInt(),
sex = Convert.ToString(it.d1),
}).ToList();
var f3 = db.Queryable <InsertTest>()
.JoinTable <InsertTest>((i1, i2) => i1.id == i2.id)
.Select <InsertTest, Student>((i1, i2) => new Student()
{
name = Convert.ToString(i1.d1), //多表查询例子
id = 1,
sex = Convert.ToString(i2.d1),
}).ToList();
//Select 外部参数用法
var f4 = db.Queryable <InsertTest>().Where("1=1", new { id = 100 }).Select <Student>(it => new Student()
{
id = "@id".ObjToInt(), //取的是 100 的值
name = "张三", //内部参数可以直接写
sex = it.txt,
sch_id = it.id
}).ToList();
var f6 = db.Queryable <InsertTest>()
.JoinTable <InsertTest>((i1, i2) => i1.id == i2.id)
.Where("1=1", new { id = 100, name = "张三", isOk = true }) //外部传参给@id
.Select <InsertTest, Student>((i1, i2) => new Student()
{
name = "@name".ObjToString(), //多表查询例子
id = "@id".ObjToInt(),
sex = i2.txt,
sch_id = 1,
isOk = "@isOk".ObjToBool()
}).ToList();
try
{
//测试用例
db.Queryable <Student>().Where(c => c.id < 10)
.Select <V_Student>(c => new V_Student {
id = c.id, name = _p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET"
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable <Student>().Where(c => c.id < 10)
.Select <V_Student>(c => new V_Student {
id = c.id, name = p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET"
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable <Student>().Where(c => c.id < 10)
.Select <V_Student>(c => new V_Student {
id = c.id, name = Getp().name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET"
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable <Student>().Where(c => c.id < 10)
.Select <V_Student>(c => new V_Student {
id = c.id, name = Getp2(), AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET"
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable <InsertTest>()
.JoinTable <InsertTest>((i1, i2) => i1.id == i2.id)
.Select <InsertTest, Student>((i1, i2) => new Student()
{
name = Getp2(), //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable <InsertTest>()
.JoinTable <InsertTest>((i1, i2) => i1.id == i2.id)
.Select <InsertTest, Student>((i1, i2) => new Student()
{
name = Getp().name, //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable <InsertTest>()
.JoinTable <InsertTest>((i1, i2) => i1.id == i2.id)
.Select <InsertTest, Student>((i1, i2) => new Student()
{
name = Getp().name, //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable <InsertTest>()
.JoinTable <InsertTest>((i1, i2) => i1.id == i2.id)
.Select <InsertTest, Student>((i1, i2) => new Student()
{
name = Getp().name, //多表查询例子
id = i1.int1.ObjToInt() + 1,
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable <InsertTest>()
.JoinTable <InsertTest>((i1, i2) => i1.id == i2.id)
.Select <InsertTest, Student>((i1, i2) => new Student()
{
name = p.name, //多表查询例子
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static void Init()
{
Console.WriteLine("");
Console.WriteLine("#### Insertable Start ####");
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.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)));
}
}
});
var insertObj = new Order()
{
Id = 1, Name = "order1", Price = 0
};
var insertObjs = new List <Order> {
new Order()
{
Id = 11, Name = "order11", Price = 0
},
new Order()
{
Id = 12, Name = "order12", Price = 0
}
};
//Ignore CreateTime
db.Insertable(insertObj).IgnoreColumns(it => new { it.CreateTime }).ExecuteReturnIdentity();//get identity
db.Insertable(insertObj).IgnoreColumns("CreateTime").ExecuteReturnIdentity();
//Only insert Name and Price
db.Insertable(insertObj).InsertColumns(it => new { it.Name, it.Price }).ExecuteReturnIdentity();
db.Insertable(insertObj).InsertColumns("Name", "Price").ExecuteReturnIdentity();
//ignore null columns
db.Insertable(insertObjs).ExecuteCommand();//get change row count
//Use Lock
db.Insertable(insertObj).With(SqlWith.UpdLock).ExecuteCommand();
insertObjs = new List <Order> {
new Order()
{
Id = 11, Name = "order11", Price = 1
},
new Order()
{
Id = 12, Name = "order12", Price = 20, CreateTime = DateTime.Now, CustomId = 1
}
};
db.Insertable(insertObjs).UseSqlServer().ExecuteBlueCopy();
var dt = db.Queryable <Order>().Take(5).ToDataTable();
dt.TableName = "Order";
db.Insertable(dt).UseSqlServer().ExecuteBlueCopy();
db.CodeFirst.InitTables <RootTable0, TwoItem, TwoItem2, TwoItem3>();
db.CodeFirst.InitTables <ThreeItem2>();
db.DbMaintenance.TruncateTable("RootTable0");
db.DbMaintenance.TruncateTable("TwoItem");
db.DbMaintenance.TruncateTable("TwoItem2");
db.DbMaintenance.TruncateTable("TwoItem3");
db.DbMaintenance.TruncateTable("ThreeItem2");
Console.WriteLine("SubInsert Start");
db.Insertable(new Order()
{
Name = "订单 1",
CustomId = 1,
Price = 100,
CreateTime = DateTime.Now,
Id = 0,
Items = new List <OrderItem>()
{
new OrderItem()
{
CreateTime = DateTime.Now,
OrderId = 0,
Price = 1,
ItemId = 1
},
new OrderItem()
{
CreateTime = DateTime.Now,
OrderId = 0,
Price = 2,
ItemId = 2
}
}
})
.AddSubList(it => it.Items.First().OrderId).ExecuteReturnPrimaryKey();
db.Insertable(new List <RootTable0>()
{
new RootTable0()
{
Name = "aa",
TwoItem2 = new TwoItem2()
{
Id = "1",
ThreeItem2 = new List <ThreeItem2>()
{
new ThreeItem2()
{
Name = "a", TwoItem2Id = "1"
},
new ThreeItem2()
{
Id = 2, Name = "a2", TwoItem2Id = "2"
}
}
},
TwoItem = new TwoItem()
{
Name = "itema",
RootId = 2
},
TwoItem3 = new List <TwoItem3>()
{
new TwoItem3()
{
Id = 0, Name = "a", Desc = ""
},
}
},
new RootTable0()
{
Name = "bb",
TwoItem2 = new TwoItem2()
{
Id = "2"
},
TwoItem = new TwoItem()
{
Name = "itemb",
RootId = 2,
},
TwoItem3 = new List <TwoItem3>()
{
new TwoItem3()
{
Id = 1, Name = "b", Desc = ""
},
new TwoItem3()
{
Id = 2, Name = "b1", Desc = "1"
},
}
}
})
.AddSubList(it => it.TwoItem.RootId)
.AddSubList(it => new SubInsertTree()
{
Expression = it.TwoItem2.RootId,
ChildExpression = new List <SubInsertTree>()
{
new SubInsertTree()
{
Expression = it.TwoItem2.ThreeItem2.First().TwoItem2Id
}
}
})
.AddSubList(it => it.TwoItem3)
.ExecuteReturnPrimaryKey();
SubNoIdentity(db);
SubIdentity(db);
Console.WriteLine("#### Insertable End ####");
}
/// <summary>
/// 测试select new
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void SelectNew(Student2 p, SqlSugarClient db)
{
//测试用例
var queryable = db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = c.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" });
var list = queryable.ToList();
//多表操作将Student转换成V_Student
var queryable2 = db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id)
.Where<School>((s1, s2) => s2.id < 10)
.Select<School, V_Student>((s1, s2) => new V_Student() { id = s1.id, name = s1.name, AreaName = "默认地区", SchoolName = s2.name, SubjectName = "NET" });//select new 目前只支持这种写法
var list2 = queryable2.ToList();
//select字符串 转换成V_Student
var list3 = db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id)
.Where(s1 => s1.id <= 3)
.Select<V_Student>("s1.*,s2.name SchoolName")
.ToList();
//新容器转换函数的支持 只支持ObjToXXX和Convert.ToXXX
var f1 = db.Queryable<InsertTest>().Select<Student>(it => new Student()
{
name = it.d1.ObjToString(),
id = it.int1.ObjToInt() // 支持ObjToXXX 所有函数
}).ToList();
var f2 = db.Queryable<InsertTest>().Select<Student>(it => new Student()
{
name = Convert.ToString(it.d1),//支持Convet.ToXX所有函数
id = it.int1.ObjToInt(),
sex = Convert.ToString(it.d1),
}).ToList();
var f3 = db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Convert.ToString(i1.d1), //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
//Select 外部参数用法
var f4 = db.Queryable<InsertTest>().Where("1=1", new { id = 100 }).Select<Student>(it => new Student()
{
id = "@id".ObjToInt(), //取的是 100 的值
name = "张三",//内部参数可以直接写
sex = it.txt,
sch_id = it.id
}).ToList();
var f6 = db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Where("1=1", new { id = 100, name = "张三", isOk = true }) //外部传参给@id
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = "@name".ObjToString(), //多表查询例子
id = "@id".ObjToInt(),
sex = i2.txt,
sch_id = 1,
isOk = "@isOk".ObjToBool()
}).ToList();
try
{
//测试用例
db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = _p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = Getp().name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = Getp2(), AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Getp2(), //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Getp().name, //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Getp().name, //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Getp().name, //多表查询例子
id = i1.int1.ObjToInt() + 1,
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = p.name, //多表查询例子
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private static void DistributedTransactionExample()
{
Console.WriteLine("");
Console.WriteLine("#### Distributed TransactionExample Start ####");
SqlSugarClient db = new SqlSugarClient(new List <ConnectionConfig>()
{
new ConnectionConfig()
{
ConfigId = "1", DbType = DbType.PostgreSQL, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true
},
new ConnectionConfig()
{
ConfigId = "2", DbType = DbType.PostgreSQL, ConnectionString = Config.ConnectionString2, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true
}
});
var db1 = db.Ado.Connection.Database;
//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");
var db2 = db.Ado.Connection.Database;
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());
if (db2 == db1)
{
return;
}
// 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(async() =>
{
db.ChangeDatabase("1");//use db1
await db.Deleteable <Order>().ExecuteCommandAsync();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
db.ChangeDatabase("2");//use db2
await db.Deleteable <Order>().ExecuteCommandAsync();
Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
Console.WriteLine(db.Queryable <Order>().Count());
throw new Exception("");
});
result2.Wait();
if (result2.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 ####");
}
/// <summary>
/// 拉姆达测试
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void Exp(Student2 p, SqlSugarClient db)
{
//解析拉姆达各种情况组合
//基本
var t1 = db.Queryable<Student>().Where(it => it.id == 1).ToList();
var t2 = db.Queryable<Student>().Where(it => it.id == p.id).ToList();
var t3 = db.Queryable<Student>().Where(it => it.id == _p.id).ToList();
var t4 = db.Queryable<Student>().Where(it => it.id == Getp().id).ToList();
var t5 = db.Queryable<Student>().Where(it => it.id == MyCost.p.id).ToList();
//BOOL
var t11 = db.Queryable<Student2>("Student").Where(it => it.isOk).ToList();
var t21 = db.Queryable<Student2>("Student").Where(it => it.isOk == MyCost.p.isOk).ToList();
var t31 = db.Queryable<Student2>("Student").Where(it => !it.isOk).ToList();
var t41 = db.Queryable<Student2>("Student").Where(it => it.isOk == true).ToList();
var t51 = db.Queryable<Student2>("Student").Where(it => it.isOk == false).ToList();
var t61 = db.Queryable<Student2>("Student").Where(it => it.isOk == _p.isOk).ToList();
var t71 = db.Queryable<Student2>("Student").Where(it => !it.isOk && !p.isOk == it.isOk).ToList();
var t91 = db.Queryable<Student2>("Student").Where(it => _p.isOk == false).ToList();
var t81 = db.Queryable<Student>().Where(it => it.isOk == false).ToList();
var t111 = db.Queryable<Student2>("Student").Where(it => it.isOk && false).ToList();
//length
var c1 = db.Queryable<Student>().Where(c => c.name.Length > 4).ToList();
var c2 = db.Queryable<Student>().Where(c => c.name.Length > _p.name.Length).ToList();
var c3 = db.Queryable<Student>().Where(c => c.name.Length > "aa".Length).ToList();
var c4 = db.Queryable<Student>().Where(c => c.name.Length > Getp().id).ToList();
//Equals
var a1 = db.Queryable<Student>().Where(c => c.name.Equals(null)).ToList();
var x = new InsertTest() { };
var x1 = db.Queryable<Student>().Where(c => c.name.Equals(x.int1)).ToList();
var a2 = db.Queryable<Student>().Where(c => c.name.Equals(p.name)).ToList();
var a4 = db.Queryable<Student>().Where(c => c.name.Equals(Getp().name)).ToList();
//Contains
var s = db.Queryable<Student>().Where(c => c.name.Contains(null)).ToList();
var s0 = new InsertTest() { };
var s1 = db.Queryable<Student>().Where(c => c.name.Contains(x.v1)).ToList();
var s3 = db.Queryable<Student>().Where(c => c.name.Contains(p.name)).ToList();
var s4 = db.Queryable<Student>().Where(c => c.name.Contains(Getp().name)).ToList();
var s5 = db.Queryable<Student>().Where(c => c.name.StartsWith(Getp().name)).ToList();
var s6 = db.Queryable<Student>().Where(c => c.name.EndsWith(Getp().name)).ToList();
//异常处理测试,防止程序中出现未知错误
try
{
var e6 = db.Queryable<Student>().Where(c => Getp().name.StartsWith(c.name)).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
var e6 = db.Queryable<Student>().Where(c => Getp().name.Equals(c.name)).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
var e6 = db.Queryable<Student>().Where(c => c.name.First() != null).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//组合测试
var z = db.Queryable<Student>().Where(c => (c.name.Equals(Getp().name) || c.name == p.name) && true && c.id > 1).ToList();
var z23 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name) || (c.id == 1 || c.name.Contains(p.name))).ToList();
var z2 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name) || !string.IsNullOrEmpty(c.name)).ToList();
}
/// <summary>
/// 拉姆达测试
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void Exp(Student2 p, SqlSugarClient db)
{
//解析拉姆达各种情况组合
//基本
var t1 = db.Queryable <Student>().Where(it => it.id == 1).ToList();
var t2 = db.Queryable <Student>().Where(it => it.id == p.id).ToList();
var t3 = db.Queryable <Student>().Where(it => it.id == _p.id).ToList();
var t4 = db.Queryable <Student>().Where(it => it.id == Getp().id).ToList();
var t5 = db.Queryable <Student>().Where(it => it.id == MyCost.p.id).ToList();
//BOOL
var t11 = db.Queryable <Student2>("Student").Where(it => it.isOk).ToList();
var t21 = db.Queryable <Student2>("Student").Where(it => it.isOk == MyCost.p.isOk).ToList();
var t31 = db.Queryable <Student2>("Student").Where(it => !it.isOk).ToList();
var t41 = db.Queryable <Student2>("Student").Where(it => it.isOk == true).ToList();
var t51 = db.Queryable <Student2>("Student").Where(it => it.isOk == false).ToList();
var t61 = db.Queryable <Student2>("Student").Where(it => it.isOk == _p.isOk).ToList();
var t71 = db.Queryable <Student2>("Student").Where(it => !it.isOk && !p.isOk == it.isOk).ToList();
var t91 = db.Queryable <Student2>("Student").Where(it => _p.isOk == false).ToList();
var t81 = db.Queryable <Student>().Where(it => it.isOk == false).ToList();
var t111 = db.Queryable <Student2>("Student").Where(it => it.isOk && false).ToList();
//length
var c1 = db.Queryable <Student>().Where(c => c.name.Length > 4).ToList();
var c2 = db.Queryable <Student>().Where(c => c.name.Length > _p.name.Length).ToList();
var c3 = db.Queryable <Student>().Where(c => c.name.Length > "aa".Length).ToList();
var c4 = db.Queryable <Student>().Where(c => c.name.Length > Getp().id).ToList();
//Equals
var a1 = db.Queryable <Student>().Where(c => c.name.Equals(null)).ToList();
var x = new InsertTest()
{
};
var x1 = db.Queryable <Student>().Where(c => c.name.Equals(x.int1)).ToList();
var a2 = db.Queryable <Student>().Where(c => c.name.Equals(p.name)).ToList();
var a4 = db.Queryable <Student>().Where(c => c.name.Equals(Getp().name)).ToList();
//Contains
var s = db.Queryable <Student>().Where(c => c.name.Contains(null)).ToList();
var s0 = new InsertTest()
{
};
var s1 = db.Queryable <Student>().Where(c => c.name.Contains(x.v1)).ToList();
var s3 = db.Queryable <Student>().Where(c => c.name.Contains(p.name)).ToList();
var s4 = db.Queryable <Student>().Where(c => c.name.Contains(Getp().name)).ToList();
var s5 = db.Queryable <Student>().Where(c => c.name.StartsWith(Getp().name)).ToList();
var s6 = db.Queryable <Student>().Where(c => c.name.EndsWith(Getp().name)).ToList();
//异常处理测试,防止程序中出现未知错误
try
{
var e6 = db.Queryable <Student>().Where(c => Getp().name.StartsWith(c.name)).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
var e6 = db.Queryable <Student>().Where(c => Getp().name.Equals(c.name)).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
var e6 = db.Queryable <Student>().Where(c => c.name.First() != null).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//组合测试
var z = db.Queryable <Student>().Where(c => (c.name.Equals(Getp().name) || c.name == p.name) && true && c.id > 1).ToList();
var z23 = db.Queryable <Student>().Where(c => !string.IsNullOrEmpty(c.name) || (c.id == 1 || c.name.Contains(p.name))).ToList();
var z2 = db.Queryable <Student>().Where(c => !string.IsNullOrEmpty(c.name) || !string.IsNullOrEmpty(c.name)).ToList();
}
/// <summary>
/// 查询测试
/// </summary>
/// <param name="p"></param>
/// <param name="d"></param>
private void Select(Student2 p, SqlSugarClient db)
{
//查询第一条
var select1= db.Queryable<Student>().Single(it => it.id == p.id);
var select2 = db.Queryable<Student>().Single(it => it.id == _p.id);
//查询分页 id3-id5
var select3 = db.Queryable<Student>().OrderBy(it=>it.id).Skip(2).Take(3).ToList();
//查询分页 id4-id6
var select4 = db.Queryable<Student>().OrderBy(it => it.id).ToPageList(2,3).ToList();
//2表join
var join1 = db.Queryable<Student>().JoinTable<School>((st, sc) => st.sch_id == sc.id)
.Select<School,V_Student>((st, sc) => new V_Student() {SchoolName=sc.name,id=st.id, name=st.name}).ToList();
//3表join
var join2 = db.Queryable<Student>()
.JoinTable<School>((st, sc) => st.sch_id == sc.id)
.JoinTable<School,Area>((st,sc,a)=>sc.AreaId==a.id)
.Select<School,Area, V_Student>((st, sc,a) => new V_Student() { SchoolName = sc.name, id = st.id, name = st.name,AreaName=a.name }).ToList();
}
