/// <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(); }
/// <summary> /// 创建SQL实体文件,指定表名 /// </summary> public void CreateClassFilesByTableNames(SqlSugarClient db, string fileDirectory, string nameSpace, params string[] tableNames) { var isLog = db.IsEnableLogEvent; db.IsEnableLogEvent = false; string sql = SqlSugarTool.GetCreateClassSql(null); var tables = db.GetDataTable(sql); if (!FileSugar.IsExistDirectory(fileDirectory)) { FileSugar.CreateDirectory(fileDirectory); } if (tables != null && tables.Rows.Count > 0) { foreach (DataRow dr in tables.Rows) { string tableName = dr["name"].ToString(); if (tableNames.Any(it => it.ToLower() == tableName.ToLower())) { var currentTable = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), GetTableNameWithSchema(db,tableName).GetTranslationSqlName())); var tableColumns = GetTableColumns(db, tableName); string className = db.GetClassTypeByTableName(tableName); var classCode = DataTableToClass(currentTable, className, nameSpace, tableColumns); FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8); } } } db.IsEnableLogEvent = isLog; }
public static SqlSugarClient GetInstance() { var db = new SqlSugarClient(ConnectionString); db.IsEnableLogEvent = true;//启用日志事件 db.LogEventStarting = (sql, par) => { Console.WriteLine(sql + " " + par+"\r\n"); }; return db; }
public static SqlSugarClient GetInstance() { string connection = System.Configuration.ConfigurationManager.ConnectionStrings[@"sqlConn"].ToString(); //这里可以动态根据cookies或session实现多库切换 var reval= new SqlSugarClient(connection); reval.SetMappingTables(_mappingTables); return reval; }
protected void Page_Load(object sender, EventArgs e) { string connStr = "server=.;uid=sa;password=sasa;database=nfd"; using (SqlSugarClient db = new SqlSugarClient(connStr)) { db.BeginTran(); db.Sqlable.IsNoLock = true; try { var sql = db.Sqlable.MappingTable<CutBill, CutBillShipment>("t1.c_id=t2.c_id").SelectToSql("t1.*"); var dt = db.GetDataTable(sql); var id = db.Insert(new test() { name = "哈哈" + DateTime.Now }); var del = db.Delete<test>(21); var update = db.Update<test>(new { name = "5555" + DateTime.Now }, new { id=1 }); } catch (Exception) { db.RollbackTran(); } } ; //var xx = SqlTool.CreateMappingTable(20); Console.Read(); }
private static void DeleteAddDatas() { using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString)) { conn.Delete<Test>(it => it.F_String == "Test"); } }
protected void btnCreateClassCode_Click(object sender, EventArgs e) { string connection = ConfigSugar.GetConfigString("connstring"); //这里可以动态根据cookies或session实现多库切换 var db = new SqlSugarClient(connection); txtResult.Value = db.ClassGenerating.SqlToClass(db, txtSql.Text, txtClassName.Text); db.Dispose(); }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(1000); using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { //ado.GetDataTable pt.Execute(i => { db.GetDataTable(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "ado.DateTable 纯SQL写法"); //dapper var conn = db.GetConnection(); pt.Execute(i => { conn.Query<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Sqlable().Form("Student", "s") .Join("School","sc","sc.id","s.sch_id",JoinType.INNER) .Join("subject","sb","sb.sid","s.id",JoinType.LEFT).SelectToList<Models.Student>("*"); }, m => { }, "sqlSugar SQL语法糖"); //sqlSugar pt.Execute(i => { db.SqlQuery<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "sqlSugar 纯SQL写法"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { db.ExecuteStoreQuery<WebTest.TestLib.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "EF4.0+sql05 纯SQL写法"); } GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
public static SqlSugarClient GetInstance() { var db = new SqlSugarClient(SugarDao.ConnectionString); db.IsEnableLogEvent = true;//启用日志事件 db.LogEventStarting = SugarConfigs.LogEventStarting; db.LogEventCompleted = SugarConfigs.LogEventCompleted; return db; }
protected void btnCreate_Click(object sender, EventArgs e) { string connection = ConfigSugar.GetConfigString("connstring"); //这里可以动态根据cookies或session实现多库切换 var db = new SqlSugarClient(connection); ; db.ClassGenerating.CreateClassFiles(db, txtPath.Text, txtNS.Text); db.Dispose(); //还有其它方法我这边只是最简单的 //db.ClassGenerating.CreateClassFilesByTableNames //db.ClassGenerating.... }
public void AddTest(PerformanceTest pt, SqlSugarClient db) { //sqlSuagr pt.Execute(i => { db.Insert<Models.InsertTest>(GetData()); }, m => { }, "添加记录"); }
/// <summary> /// 解析表达式 /// </summary> /// <param name="re">当前解析对象</param> /// <param name="exp">要解析的表达式</param> /// <param name="db">数据库访问对象</param> public void ResolveExpression(ResolveExpress re, Expression exp, SqlSugarClient db) { DB = db; //初始化表达式 Init(re, exp); //设置PageSize foreach (var par in Paras) { SqlSugarTool.SetParSize(par); } }
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); }
/// <summary> /// 创建SQL实体文件 /// </summary> public void CreateClassFiles(SqlSugarClient db, string fileDirectory, string nameSpace = null) { var tables = db.GetDataTable("select name from sysobjects where xtype in ('U','V') "); if (tables != null && tables.Rows.Count > 0) { foreach (DataRow dr in tables.Rows) { string tableName = dr["name"].ToString(); var currentTable = db.GetDataTable(string.Format("select top 1 * from {0}", tableName)); var classCode = DataTableToClass(currentTable, tableName, nameSpace); FileSugar.WriteText(fileDirectory.TrimEnd('\\') + "\\" + tableName + ".cs", classCode); } } }
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); }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(1100);//设置循环次数 using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { TuncateTable(db); AddTest(pt, db); //sqlSugar pt.Execute(i => { db.Delete<Models.InsertTest>(it=>it.id==i); }, m => { }, "sqlSugar"); TuncateTable(db); AddTest(pt, db); //ado.net pt.Execute(i => { db.ExecuteCommand("delete InsertTest where id=@id", new SqlParameter("@id", i)); }, m => { }, "ado.net"); TuncateTable(db); AddTest(pt, db); //dapper pt.Execute(i => { db.GetConnection().Execute("delete InsertTest where id=@id", new { id=i}); }, m => { }, "dapper"); } //输出测试页面 GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
public static SqlSugarClient GetInstance() { string connection = SugarDao.ConnectionString; //这里可以动态根据cookies或session实现多库切换 var db = new SqlSugarClient(connection); //支持sqlable和queryable db.SetFilterItems(_filterRos); //列过滤只支持queryable db.SetFilterItems(_filterColumns); db.IsEnableLogEvent = true;//启用日志事件 db.LogEventStarting = (sql, par) => { Console.WriteLine(sql + " " + par + "\r\n"); }; return db; }
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(); }
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> /// 获取拉姆达表达式的字段值多个T模式 /// </summary> /// <param name="exp"></param> /// <param name="db">数据库访问对象</param> /// <returns></returns> public string GetExpressionRightFieldByNT(Expression exp, SqlSugarClient db) { DB = db; string reval = ""; LambdaExpression lambda = exp as LambdaExpression; var isConvet = lambda.Body.NodeType.IsIn(ExpressionType.Convert); var isMember = lambda.Body.NodeType.IsIn(ExpressionType.MemberAccess); if (!isConvet && !isMember) { throw new SqlSugarException(FileldErrorMessage); } try { if (isConvet) { var memberExpr = ((UnaryExpression)lambda.Body).Operand as MemberExpression; reval= memberExpr.ToString(); } else//isMember { reval= lambda.Body.ToString(); } } catch (Exception) { throw new SqlSugarException(FileldErrorMessage); } if (DB != null && DB.IsEnableAttributeMapping && DB._mappingColumns.IsValuable()) { if (DB._mappingColumns.Any(it => reval.EndsWith("."+it.Key))) { var preName = reval.Split('.').First(); var dbName = DB._mappingColumns.Single(it => reval.EndsWith("." + it.Key)).Value; return preName+"."+dbName; } } return reval; }
/// <summary> /// 创建实体文件 /// </summary> /// <param name="db"></param> /// <param name="fileDirectory"></param> /// <param name="nameSpace">命名空间(默认:system)</param> /// <param name="tableOrView">是生成视图文件还是表文件,null生成表和视图,true生成表,false生成视图(默认为:null)</param> /// <param name="callBack">生成文件后的处理,参数string为实体名</param> /// <param name="preAction">生成文件前的处理,参数string为表名</param> public void CreateClassFiles(SqlSugarClient db, string fileDirectory, string nameSpace = null, bool? tableOrView = null, Action<string> callBack = null, Action<string> preAction = null) { var isLog = db.IsEnableLogEvent; db.IsEnableLogEvent = false; string sql = SqlSugarTool.GetCreateClassSql(tableOrView); var tables = db.GetDataTable(sql); if (tables != null && tables.Rows.Count > 0) { foreach (DataRow dr in tables.Rows) { string tableName = dr["name"].ToString(); if (preAction != null) { preAction(tableName); } var currentTable = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), GetTableNameWithSchema(db,tableName).GetTranslationSqlName())); if (callBack != null) { var tableColumns = GetTableColumns(db, tableName); var classCode = DataTableToClass(currentTable, tableName, nameSpace, tableColumns); string className = db.GetClassTypeByTableName(tableName); classCode = classCode.Replace("class " + tableName, "class " + className); FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8); callBack(className); } else { var tableColumns = GetTableColumns(db, tableName); string className = db.GetClassTypeByTableName(tableName); var classCode = DataTableToClass(currentTable, className, nameSpace, tableColumns); FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8); } } } db.IsEnableLogEvent = isLog; }
public AliyunConfigController(SqlSugarClient _dbClient) : base(_dbClient) { }
public BaseRepository(SqlSugarClient dbContext) { _db = dbContext ?? throw new ArgumentNullException(nameof(dbContext)); }
/// <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="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(); }
public bool Insert(SqlSugarClient client, T t) { return(client.Insertable(t).ExecuteCommand() > 0); }
public ActionResult submitComment(FormCollection form) { if (Session["user_id"] == null) { return(Redirect("~/login/index")); } else { SqlSugarClient db = new SqlSugarClient( new ConnectionConfig() { ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"], DbType = DbType.Oracle, //设置数据库类型 IsAutoCloseConnection = true, //自动释放数据务,如果存在事务,在事务结束后释放 InitKeyType = InitKeyType.SystemTable //从实体特性中读取主键自增列信息 }); JObject isSuccess = new JObject(); try { DateTime dt = DateTime.Now; var ActComData = new ActivityComment() { activity_order_id = Convert.ToInt32(Request.Form["orderID"]), grade = Convert.ToDouble(Request.Form["rate"]), user_id = Session["user_id"].ToString(), comment_text = Request.Form["comment_content"], times = dt }; if (Convert.ToInt16(Request.Form["accessWay"]) == 2) { if (db.Insertable <HomestayComment>(ActComData).ExecuteCommand() == 1) { Session["message"] = "订单评价成功!"; return(Redirect("~/Admin")); } else { Session["message"] = "添加评论失败!"; return(Redirect("~/Admin")); } } else if (Convert.ToInt16(Request.Form["accessWay"]) == 1) { if (db.Insertable <ActivityComment>(ActComData).ExecuteCommand() == 1) { Session["message"] = "订单评价成功!"; return(Redirect("~/Admin")); } else { Session["message"] = "添加评论失败!"; return(Redirect("~/Admin")); } } else { Session["message"] = "参数错误!"; return(Redirect("~/Admin")); } } catch (Exception ex) { Session["message"] = "操作失败!"; return(Redirect("~/Admin")); } finally { } } }
public ActionResult ViewActivityInstance() { if (Session["user_id"] == null) { return(Redirect("~/login/index")); } else { SqlSugarClient db = new SqlSugarClient( new ConnectionConfig() { ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"], DbType = DbType.Oracle, //设置数据库类型 IsAutoCloseConnection = true, //自动释放数据务,如果存在事务,在事务结束后释放 InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息 }); try { var activityResult = db.Queryable <ActivityInstance, Activity>((st, sc) => new object[] { JoinType.Inner, st.activity_id == sc.activity_id }) .Where((st, sc) => sc.user_id == Session["user_id"].ToString()) .Select((st, sc) => new { activity_id = st.activity_id, activity_name = sc.activity_name, activity_instance_id = st.activity_instance_id, start_time = st.start_time, end_time = st.end_time, is_booked = st.is_booked, price = st.price }) .ToArray(); List <ActivityInstanceReturn> returnList = new List <ActivityInstanceReturn>(); for (int i = 0; i < activityResult.Length; i++) { ActivityInstanceReturn temp = new ActivityInstanceReturn { activity_id = activityResult[i].activity_id, activity_name = activityResult[i].activity_name, activity_instance_id = activityResult[i].activity_instance_id, start_time = activityResult[i].start_time, end_time = activityResult[i].end_time, is_booked = activityResult[i].is_booked, price = activityResult[i].price }; returnList.Add(temp); } ViewBag.isSuccess = 1; ViewBag.returnList = returnList.ToArray(); //ViewBag.activityList = activityResult; } catch (Exception ex) { ViewBag.returnList = null; return(View()); } finally { } return(View()); } }
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 s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new SqlParameter("@id1", "1"), new SqlParameter("@id2", "2"), 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 s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new { id1 = 1, id2 = 2, b = 11, e = 20 }).ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Sqlable().Form("Student", "s") .Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER) .Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).Where("s.id>@id1").Where("s.id>@id2") .SelectToPageList<Models.Student>("s.*", "s.id asc,s.name desc", 2, 10, new { id1=1,id2=2 }); }, m => { }, "sqlSugar SQL语法糖"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { var reval = (from s in db.Student join sc in db.School on s.sch_id equals sc.id join sb in db.Subject on s.id equals sb.sid into ssb from sb2 in ssb.DefaultIfEmpty() select new { s.id, s.name, s.sch_id, s.sex }).Where(c=>c.id>1).Where(c=>c.id>2).OrderBy(c=>c.id).ThenByDescending(c=>c.name).Skip(10).Take(10).ToList(); }, m => { }, "EF4.0+sql05 LINQ TO SQL"); } GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
/// <summary> /// 功能描述:获取数据库处理对象 /// 作 者:Blog.Core /// </summary> /// <param name="db">db</param> /// <returns>返回值</returns> public SimpleClient <T> GetEntityDB <T>(SqlSugarClient db) where T : class, new() { return(new SimpleClient <T>(db)); }
public ContentController(SqlSugarClient _dbClient) : base(_dbClient) { }
/// <summary> /// 构造函数 /// </summary> public DbFirstController(ISqlSugarClient sqlSugarClient, IWebHostEnvironment env) { _sqlSugarClient = sqlSugarClient as SqlSugarClient; Env = env; }
/// <summary> /// /// </summary> /// <param name="client"></param> public SugarWDeletable(SqlSugarClient client) { _deletable = client.Deleteable <T>(); }
public BaseBackendController(SqlSugarClient _dbClient) : base(_dbClient) { dbClient = _dbClient; modelMapper = new ModelMapper(dbClient); }
/// <summary> /// 新容器转换 /// </summary> private void QueryableSelectNewClass() { using (SqlSugarClient db = SugarDao.GetInstance()) { //单表操作将Student转换成V_Student 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(); var json = queryable.ToJson(); var dynamic = queryable.ToDynamic(); //多表操作将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(); var json2 = queryable2.ToJson(); var dynamic2 = queryable2.ToDynamic(); //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(); //select字符串 转换成Json var json3 = db.Queryable <Student>() .JoinTable <School>((s1, s2) => s1.sch_id == s2.id) .Where(s1 => s1.id <= 3) .Select <V_Student>("s1.*,s2.name SchoolName") .ToJson(); //select字符串 转换成Json var dynamic3 = db.Queryable <Student>() .JoinTable <School>((s1, s2) => s1.sch_id == s2.id) .Where(s1 => s1.id <= 3) .Select <V_Student>("s1.*,s2.name SchoolName") .ToDynamic(); //新容器转换函数的支持 只支持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(); } }
public ConfigBusniess(SqlSugarClient dbContext) { _dbContext = dbContext; }
public Sys_roleServices(ISys_menuServices menuServices, ISys_rolemenuServices rolemenuServices, SqlSugarClient client, ISys_roleRepository repository) : base(repository) { _repository = repository; _client = client; _rolemenuServices = rolemenuServices; _menuServices = menuServices; }
public BaseController(SqlSugarClient _dbClient) { dbClient = _dbClient; Config = new Dictionary <string, string>(); InitBaseController(); }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### Updateable 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))); } } }); /*** 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(); var levelCode = "123213123131321"; db.Updateable <Order>(a => a.Name == "a") .Where(a => SqlFunc.StartsWith(a.Name, levelCode)) .AddQueue(); db.SaveQueues(); Console.WriteLine("#### Updateable End ####"); }
public ActionResult checkMyHomestayOrder() { if (Session["user_id"] == null) { return(Redirect("~/login/index")); } else { SqlSugarClient db = new SqlSugarClient( new ConnectionConfig() { ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"], DbType = DbType.Oracle, //设置数据库类型 IsAutoCloseConnection = true, //自动释放数据务,如果存在事务,在事务结束后释放 InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息 }); try { //查询我预定活动结果 var bookedList = db.Queryable <HomestayOrder, Homestay>((st, sc) => new object[] { JoinType.Inner, st.homestay_id == sc.homestay_id, }) .OrderBy((st) => st.create_time) .Where((st, sc) => sc.user_id == Session["user_id"].ToString()) .Select((st, sc) => new { homestay_order_id = st.homestay_order_id, customer_id = st.customer_id, homestay_id = st.homestay_id, status = st.status, create_time = st.create_time, start_time = st.start_time, end_time = st.end_time, total_price = st.total_price, platform_fee = st.platform_fee, homestay_name = sc.homestay_name }).ToArray(); List <Ordered_Homestay_Return> returnList = new List <Ordered_Homestay_Return>(); for (int i = 0; i < bookedList.Length; i++) { Ordered_Homestay_Return temp = new Ordered_Homestay_Return { homestay_order_id = bookedList[i].homestay_order_id, customer_id = bookedList[i].customer_id, homestay_id = bookedList[i].homestay_id, status = bookedList[i].status, create_time = bookedList[i].create_time, start_time = bookedList[i].start_time, end_time = bookedList[i].end_time, total_price = bookedList[i].total_price, platform_fee = bookedList[i].platform_fee, homestay_name = bookedList[i].homestay_name }; returnList.Add(temp); } ViewBag.isSuccess = 1; ViewBag.returnList = returnList.ToArray(); } catch (Exception ex) { ViewBag.isSuccess = 0; throw ex; } return(View()); } }
public Wms_mastaskRepository(SqlSugarClient dbContext) : base(dbContext) { }
public ActionResult CheckComment() { 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 { if (Convert.ToInt16(Request.Form["accessWay"]) == 2) { var checkStatus = db.Queryable <HomestayOrder>().InSingle(Convert.ToInt32(Request.Form["orderID"])); if (checkStatus.status == 1) { var isComment = db.Queryable <HomestayOrder>().InSingle(Convert.ToInt32(Request.Form["orderID"])); if (isComment == null) { isSuccess.Add("isSuccess", true); } else { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "您已评价过此订单,谢谢!"); } } else { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "此订单未完成,无法添加评价!"); } } else if (Convert.ToInt16(Request.Form["accessWay"]) == 1) { var checkStatus = db.Queryable <ActivityOrder>().InSingle(Convert.ToInt32(Request.Form["orderID"])); if (checkStatus.status == 1) { var isComment = db.Queryable <ActivityComment>().InSingle(Convert.ToInt32(Request.Form["orderID"])); if (isComment == null) { isSuccess.Add("isSuccess", true); } else { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "您已评价过此订单,谢谢!"); } } else { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "此订单未完成,无法添加评价!"); } } 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))); } finally { } return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); }
//禁止实例化 public SugarDao() { string connection = ConfigSugar.GetConfigString("connstring"); //这里可以动态根据cookies或session实现多库切换 this.db = new SqlSugarClient(connection); }
public OracleDbOperation(SqlSugarClient _db) : base(_db) { ContainForeignTable = true; }
public AuthController(SqlSugarClient dbContext, ILogger <AuthController> logger, IServiceClient serviceClient) { _dbContext = dbContext; _logger = logger; _serviceClient = serviceClient; }
/// <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); } }
public ActionResult homestayEdit(FormCollection form) { DateTime dt = DateTime.Now; string folder = Server.MapPath("~/images/Homestay"); var filePath = folder + "\\" + dt.ToFileTime().ToString() + Request.Files["myfile"].FileName.Substring(Request.Files["myfile"].FileName.LastIndexOf("\\") + 1); var fileSave = Request.Files["myfile"].FileName; if (Request.Files["myfile"].FileName != "") { fileSave = "../images/Homestay/" + dt.ToFileTime().ToString() + Request.Files["myfile"].FileName.Substring(Request.Files["myfile"].FileName.LastIndexOf("\\") + 1); } else { fileSave = (string)Session["homestay_pic"]; } if (System.IO.File.Exists(filePath)) { System.IO.File.Delete(filePath); } Request.Files["myfile"].SaveAs(filePath); SqlSugarClient db = new SqlSugarClient( new ConnectionConfig() { ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"], DbType = DbType.Oracle, //设置数据库类型 IsAutoCloseConnection = true, //自动释放数据务,如果存在事务,在事务结束后释放 InitKeyType = InitKeyType.SystemTable //从实体特性中读取主键自增列信息 }); JObject isSuccess = new JObject(); DateTimeFormatInfo dtFormat = new DateTimeFormatInfo(); dtFormat.ShortDatePattern = "yyyy-MM-dd"; DateTime dt_last = Convert.ToDateTime(Request.Form["form-latest_schedulable_date"], dtFormat); try { var data = new Homestay() { homestay_id = Convert.ToInt16(Request.Form["form-homestay_id"]), user_id = (string)Session["user_id"], homestay_name = Request.Form["form-homestay_name"], introduction = Request.Form["form-introduction"], num_of_bedrooms = Convert.ToInt16(Request.Form["form-num_of_bedrooms"]), num_of_beds = Convert.ToInt16(Request.Form["form-num_of_beds"]), num_of_bathrooms = Convert.ToInt16(Request.Form["form-num_of_bathrooms"]), max_member_limit = Convert.ToInt16(Request.Form["form-max_member_limit"]), default_price = Convert.ToInt16(Request.Form["form-default_price"]), address = Request.Form["form-address"], homestay_pictures = fileSave, house_regulations = Request.Form["form-house_regulations"], cancellation_policy = Request.Form["form-cancellation_policy"], latest_schedulable_date = dt_last, check_in_method = Request.Form["form-check_in_method"], convenience_facilities = Request.Form["form-convenience_facilities"], homestay_type = Request.Form["form-homestay_type"] }; if (db.Updateable(data).ExecuteCommand() == 1) { int productID = db.Queryable <Homestay>().Max(it => it.homestay_id); ViewBag.errorMessage = "房源更新成功!房源ID:" + productID; ViewBag.flag = 1; ViewBag.productID = productID; return(Redirect("~/Homestay/Stayinfo?productID=" + Request.Form["form-homestay_id"])); } else { ViewBag.errorMessage = "房源更新失败"; ViewBag.flag = 0; return(Redirect("~/Homestay/EditHomestay?productID=" + Request.Form["form-homestay_id"])); } } catch (Exception ex) { ViewBag.errorMessage = "房源更新失败"; ViewBag.flag = 0; return(Redirect("~/Homestay/EditHomestay?productID=" + Request.Form["form-homestay_id"])); } finally { } }
public static SqlSugarClient GetInstance() { var db = new SqlSugarClient(SugarDao.ConnectionString); db.IsEnableAttributeMapping = true;//启用属性映射 db.IsIgnoreErrorColumns = true;//忽略非数据库列 db.IsEnableLogEvent = true;//启用日志事件 db.LogEventStarting = (sql, par) => { Console.WriteLine(sql + " " + par + "\r\n"); }; return db; }
public ActionResult AddFavourites(int productID = -1) { JObject isSuccess = new JObject(); SqlSugarClient db = new SqlSugarClient( new ConnectionConfig() { ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"], DbType = DbType.Oracle, //设置数据库类型 IsAutoCloseConnection = true, //自动释放数据务,如果存在事务,在事务结束后释放 InitKeyType = InitKeyType.SystemTable //从实体特性中读取主键自增列信息 }); if (Session["user_id"] == null || productID == -1) { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "添加失败!你还没有登陆!"); return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); } else { DateTime dt_create = DateTime.Now; var queryResult = db.Queryable <HomestayFavorite>().Where(it => it.user_id == (string)Session["user_id"] && it.homestay_id == productID).ToArray(); if (queryResult.Length == 0) { try { var data = new HomestayFavorite() { user_id = (string)Session["user_id"], homestay_id = productID, times = dt_create }; if (db.Insertable(data).ExecuteCommand() >= 1) { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "添加成功!"); return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); } else { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "添加失败!这个活动已经在你的心愿单了!"); return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); } } catch (Exception ex) { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "添加失败!!"); return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); } finally { } } else { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "添加失败!这个活动已经在你的心愿单了!"); return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); } } }
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 ids = GetDeleteList().Select(it => it.Id).ToArray(); var list = conn.Delete<Test,int>(ids); } }); }
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 ####"); }
/// <summary> /// 功能描述:获取一个自定义的数据库处理对象 /// </summary> /// <param name="sugarClient">sugarClient</param> /// <returns>返回值</returns> public static SimpleClient <T> GetCustomEntityDB <T>(SqlSugarClient sugarClient) where T : class, new() { return(new SimpleClient <T>(sugarClient)); }
/// <summary> /// 功能描述:获取一个自定义的数据库处理对象 /// </summary> /// <param name="config">config</param> /// <returns>返回值</returns> public static SimpleClient <T> GetCustomEntityDB <T>(ConnectionConfig config) where T : class, new() { SqlSugarClient sugarClient = GetCustomDB(config); return(GetCustomEntityDB <T>(sugarClient)); }
//清空数据表 private static void TuncateTable(SqlSugarClient db) { db.ExecuteCommand("truncate table InsertTest"); System.Threading.Thread.Sleep(1000); }
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 } }); //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(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> /// 审核用户是否存在并保存至Person表 /// </summary> /// <param name="userModel"></param> /// <param name="userID"></param> /// <param name="position"></param> /// <returns></returns> public string CheckUser(AllEmployee userModel, string userID, string position, string mobilePhone) { try { using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_LandaVSql.GetInstance()) { bool result = false; AllEmployee landaUser = null; Queryable <AllEmployee> listAllEmployees = _dbMsSql.Queryable <AllEmployee>().Where(i => i.IDCard == userModel.IDCard); if (listAllEmployees.Count() > 0) { landaUser = listAllEmployees.First(); } if (landaUser != null) { Guid vguid = Guid.NewGuid(); Business_Personnel_Information personInfo = new Business_Personnel_Information(); switch (position) { case "司机": personInfo.DepartmenManager = 1; break; case "普通员工": personInfo.DepartmenManager = 2; break; case "管理人员": personInfo.DepartmenManager = 3; break; } //DateTime now = DateTime.Now; //if (!string.IsNullOrEmpty(landaUser.BirthDay)) //{ // DateTime birthday = DateTime.Parse(landaUser.BirthDay); // int age = now.Year - birthday.Year; // if (now.Month < birthday.Month || (now.Month == birthday.Month && now.Day < birthday.Day)) // age--; // personInfo.Age = age; //年龄 //} personInfo.Name = landaUser.Name; personInfo.Vguid = vguid; string gender = "1"; if (landaUser.Gender.HasValue) { gender = landaUser.Gender.Value.ToString(); //性别 } personInfo.Sex = gender; personInfo.OwnedFleet = Guid.Parse(userModel.OrganizationID); //所属部门 //判断微信带过来的手机号是否为空(如果为空取人员系统中的手机号,不为空则取微信中的手机号) if (string.IsNullOrEmpty(mobilePhone)) { personInfo.PhoneNumber = landaUser.MobilePhone; //人员系统手机号 } else { personInfo.PhoneNumber = mobilePhone; //微信带过来的手机号 } personInfo.UserID = userID; personInfo.ID = userID; personInfo.IDNumber = landaUser.IDCard; //身份证号 personInfo.JobNumber = landaUser.EmployeeNO; //工号 personInfo.LicensePlate = landaUser.DrivingLicense; //车牌号 personInfo.ApprovalStatus = 2; //已审核 personInfo.ApprovalType = 1; //系统审核 personInfo.CreatedDate = DateTime.Now; personInfo.ChangeDate = DateTime.Now; result = SavePersonInfo(personInfo); if (result) { return("1"); //Person表保存成功 } else { _dbMsSql.Update <Business_Personnel_Information>(new { ApprovalStatus = 1 }, it => it.Vguid == vguid); return("2"); //Person表保存失败 } } else { //Business_Personnel_Information personInfo = new Business_Personnel_Information(); //switch (position) //{ // case "司机": // personInfo.DepartmenManager = 1; // break; // case "普通员工": // personInfo.DepartmenManager = 2; // break; // case "管理人员": // personInfo.DepartmenManager = 3; // break; //} //DateTime now = DateTime.Now; ////if (!string.IsNullOrEmpty(landaUser.BirthDay)) ////{ //// DateTime birthday = DateTime.Parse(landaUser.BirthDay); //// int age = now.Year - birthday.Year; //// if (now.Month < birthday.Month || (now.Month == birthday.Month && now.Day < birthday.Day)) //// age--; //// personInfo.Age = age.ToString(); //年龄 ////} //personInfo.Vguid = Guid.NewGuid(); //personInfo.OwnedFleet = Guid.Parse(userModel.OrganizationID); //所属部门 ////判断微信带过来的手机号是否为空(如果为空取人员系统中的手机号,不为空则取微信中的手机号) //if (string.IsNullOrEmpty(mobilePhone)) //{ // personInfo.PhoneNumber = landaUser.MobilePhone; //人员系统手机号 //} //else //{ // personInfo.PhoneNumber = mobilePhone; //微信带过来的手机号 //} //personInfo.UserID = userID; //personInfo.ID = userID; //personInfo.IDNumber = userModel.IDCard; //身份证号 //if (personInfo.IDNumber.Length == 18) //{ // int gender; // bool isSuccess = int.TryParse(personInfo.IDNumber.Substring(16, 1), out gender); // if (isSuccess) // { // personInfo.Sex = gender % 2 == 0 ? "2" : "1"; // } // string strYear = personInfo.IDNumber.Substring(6, 8).Insert(4, "-").Insert(7, "-"); //提取出生年份 // TimeSpan ts = DateTime.Now.Subtract(Convert.ToDateTime(strYear)); // personInfo.Age = ts.Days / 365; //} //personInfo.JobNumber = userModel.EmployeeNO; //工号 //personInfo.ApprovalStatus = 1; //未审核 //personInfo.ApprovalType = 2; //手动关注 //personInfo.CreatedDate = DateTime.Now; //personInfo.ChangeDate = DateTime.Now; //result = SavePersonInfo(personInfo); return("3"); //LandaV9库中不存在 } } } catch (Exception ex) { Common.LogHelper.LogHelper.WriteLog("二次验证:" + ex.Message); return("3"); //LandaV9库中不存在 } }
public UserGroupRepository(SqlSugarClient sqlSugarClient) : base(sqlSugarClient) { }
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.SqlBulkCopy(GetList); } }); }
public ConfigController(IDbFactory factory) { _dbContext = factory.GetDbContext("sqlite"); }