//삭제 버튼 클릭 private void ButtonClickDelete() { List <members> selectedItems = MemberCollection.Where(x => x.IsSelect == true).ToList(); string showMassage = "선택 하신 " + selectedItems.Count.ToString() + " 건 의 데이터를 지우시겠습니까?"; if (MessageBox.Show(showMassage, "삭제", MessageBoxButton.YesNo) == MessageBoxResult.Yes) { foreach (members user in selectedItems) { string deleteQuery = "DELETE FROM TB_MEMBERS where ID = '" + user.ID + "'"; string errorMassage; DBSqlite.QueryExecute(deleteQuery, out errorMassage); if (!string.IsNullOrEmpty(errorMassage)) { MessageBox.Show(errorMassage); return; } } } }
private void GetQuestion() { string query = "SELECT QUESTION_ID, QUESTION_USER_ID, QUESTION_TEXT, ROW_INDEX FROM TB_USER_QUESTION"; using (DataTable dt = DBSqlite.GetSelectData(query)) { if (dt.Rows.Count > 0) { QuestionCollection.Clear(); foreach (DataRow dr in dt.Rows) { question Question = new question() { QUESTION_ID = dr["QUESTION_ID"].ToString(), QUESTION_USER_ID = dr["QUESTION_USER_ID"].ToString(), QUESTION_TEXT = dr["QUESTION_TEXT"].ToString(), ROW_INDEX = (int)dr["ROW_INDEX"] }; QuestionCollection.Add(Question); } } } }
public void 添加部分字段_含子查询_测试lt() { var name = "木头人3名称必须唯一" + DateTime.Now.ToString(); string colm = "img", val = "(select value from skin limit 1 offset 1)"; DateTime cdate = DateTime.Now; var insert = DBSqlite <Users> .Insert().AddColumn(p => new bool[] { p.UserName == name, p.Password == "666", p.CreateTime == cdate , SM.Sql(colm, val), SM.Sql(p.Remark, "(select '荒野高尔夫')") }); Console.WriteLine(insert.RawSqlParams().Item1); var efrow = insert.ExecuteInsert(); Console.WriteLine(efrow); string guid = Guid.NewGuid().ToString(); var efrow2 = DBSqlite <LockPers> .Insert().AddColumn(p => new bool[] { p.Id == guid, p.Name == "木头人1", p.Content == "这是棉花好多好多", p.InsertTime == DateTime.Now, p.IsDel == false, p.UserId == 3 }).ExecuteInsert(); Console.WriteLine(efrow2); }
private void RefreshDataGrid() { string query = "SELECT ID, NAME, AUTH, PASSWORD FROM TB_MEMBERS"; using (DataTable dt = DBSqlite.GetSelectData(query)) { if (dt.Rows.Count > 0) { MemberCollection.Clear(); foreach (DataRow dr in dt.Rows) { members user = new members() { IsSelect = false, ID = dr["ID"].ToString(), NAME = dr["NAME"].ToString(), AUTH = dr["AUTH"].ToString(), PASSWORD = dr["PASSWORD"].ToString() }; MemberCollection.Add(user); } } } }
//적용 버튼 클릭 private void ButtonClickApply() { List <members> users = MemberCollection.ToList(); string showMassage = users.Count.ToString() + " 건 의 데이터를 업데이트 하시겠습니까?"; if (MessageBox.Show(showMassage, "적용", MessageBoxButton.YesNo) == MessageBoxResult.Yes) { foreach (members user in users) { string deleteQuery = "UPDATE TB_MEMBERS " + "SET NAME = '" + user.NAME + "', AUTH = '" + user.AUTH + "', PASSWORD = '******' " + "WHERE ID = '" + user.ID + "'"; string errorMassage; DBSqlite.QueryExecute(deleteQuery, out errorMassage); if (!string.IsNullOrEmpty(errorMassage)) { MessageBox.Show(errorMassage); return; } } } MessageBox.Show("업데이트 완료"); }
private static void Main() { Application.EnableVisualStyles(); //db = new DBSqlServer(); db = new DBSqlite(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new frmMain()); }
public void EditColumnSqlTest() { string colm = "img", val = "(select value from skin limit 1 offset 1)"; DateTime cdate = DateTime.Now; var update = DBSqlite <Users> .Update().EditColumn(p => new bool[] { SM.Sql(colm, val), SM.Sql(p.Remark, "(select '奥德赛 终于改好了')") }).Where(p => p.Id == 6 && SM.SQL("IsDel == 0")); WriteSqlParams(update.RawSqlParams()); }
public void AddColumnSqlTest() { string colm = "img", val = "(select value from skin limit 1 offset 1)"; var insert = DBSqlite <Users> .Insert().AddColumn(p => new bool[] { SM.Sql(colm, val), SM.Sql(p.Remark, "(select '荒野高尔夫')"), }); WriteSqlParams(insert.RawSqlParams()); }
public void 除数据_含子查询_测试lt() { var sql = " UserId = ( select Id from users where UserName = '******' )"; var delete = DBSqlite <LockPers> .Delete().Where(p => p.Name == "H$E22222" && SM.SQL(sql) && SM.SQL(" IsDel = '1' ")); var efrow = delete.ExecuteDelete(); Console.WriteLine(efrow); Console.WriteLine(delete.RawSqlParams().Item1); }
public void EditShortTest() { short stnum = 1; var edit = DBSqlite <EsySysUser_> .Update().EditColumn(p => new bool[] { p.Gen == stnum, SM.Sql(p.Gen, " 1 "), p.F_UserId == "001" }).Where(p => p.F_UserId == "001"); var rawsp = edit.RawSqlParams(); WriteSqlParams(rawsp); //.ExecuteUpdate(); }
public void 根据主键ID更新整个实体lt() { var model = DBSqlite <LockPers> .Select().Column().From().Where(p => p.Name == "测试bool修改2 xxxxxx").ExecuteQuery <LockPers>().FirstOrDefault(); model.Content = "棉花棉花棉花棉花棉花"; model.ContentOld = "忽略Write(false)标记字段"; model.Prompt = "xxxxxxxxxxx"; var issucs = DBSqliteFuncs.New.Updat <LockPers>(model); Console.WriteLine(issucs); }
//유저 아이디 판별 private bool ConfirmUserID(members newUser) { string query = "SELECT ID FROM TB_MEMBERS WHERE ID = '" + newUser.ID + "'"; using (DataTable dt = DBSqlite.GetSelectData(query)) { if (dt.Rows.Count <= 0) { return(true); } } return(false); }
public void 更新部分字段_含子查询_测试lt() { var model = new Users() { CreateTime = DateTime.Now }; string colm = "img", val = "(select value from skin limit 1 offset 1)"; DateTime cdate = DateTime.Now; var update = DBSqlite <Users> .Update().EditColumn(p => new bool[] { p.UserName == "几十行代码几十个错 调一步改一步....", p.Password == "bug制造者" , p.CreateTime == model.CreateTime, SM.Sql(p.Remark, "(select '奥德赛 终于改好了')") }).Where(p => p.Id == 6 && SM.SQL("IsDel == 0")); Console.WriteLine(update.RawSqlParams().Item1); var efrow = update.ExecuteUpdate(); Console.WriteLine(efrow); }
private void ComboBoxBinding() { Members = new ObservableCollection <members>(); string qeury = "SELECT ID, NAME FROM TB_MEMBERS"; using (DataTable dt = DBSqlite.GetSelectData(qeury)) { foreach (DataRow dr in dt.Rows) { Members.Add(new members() { ID = dr["ID"].ToString(), NAME = dr["NAME"].ToString() }); } } }
private bool LoginConfirmation(string userID, string userPassWord) { string query = "SELECT * from TB_MEMBERS WHERE ID = '" + userID + "' AND PASSWORD = '******'"; using (DataTable dt = DBSqlite.GetSelectData(query)) { if (dt.Rows.Count > 0) { Application.Current.Resources["id"] = userID; return(true); } else { return(false); } } }
public void 动态添加不同字段测试() { /* * 测试名称: 动态添加不同字段测试 * 测试结果: 已通过 * 结果 的标准输出: insert into Users (img,Remark) values ((select value from skin limit 1 offset 1),(select '荒野高尔夫')) (age) values ((select 1)) * * 问题 把多个AddColumn合并到1个 * 将AddColumn里的sql和parms添加到 addList集合中 最后AddColumnEnd子句中执行 字段和values的join */ string colm = "img", val = "(select value from skin limit 1 offset 1)"; var insert = DBSqlite <Users> .Insert().AddColumn(p => new bool[] { SM.Sql(colm, val), SM.Sql(p.Remark, "(select '荒野高尔夫')"), }) .AddColumn(p => new bool[] { SM.Sql("age", "(select 1)") }); WriteSqlParams(insert.RawSqlParams()); }
//데이터 입력 private void InsertUser(members newUser) { string query = "INSERT INTO TB_MEMBERS(ID, NAME, AUTH, PASSWORD) " + "VALUES ('" + newUser.ID + "','" + newUser.NAME + "','" + newUser.AUTH + "','" + newUser.PASSWORD + "')"; string errMsg; if (DBSqlite.QueryExecute(query, out errMsg)) { if (MessageBox.Show("가입이 완료되었습니다.", "완료", MessageBoxButton.OK) == MessageBoxResult.OK) { joinWindow.Close(); } } if (!string.IsNullOrEmpty(errMsg)) { MessageBox.Show(errMsg); } }
public void 先查再修改指定字段() { LockPers p = new LockPers() { Id = "028e7910-6431-4e95-a50f-b9190801933b" }; var query = DBSqlite <LockPers> .Select().Column(c => new { c.Content, c.EditCount }).From().Where(m => m.Id == p.Id); var old = query.ExecuteQuery <LockPers>().FirstOrDefault(); old._IsWriteFiled = true; // 标记开始记录赋值字段 注意上面查询LockPers 要再默认构造函数里把 标识改为false 查出的数据不要记录赋值字段 old.Name = "蛋蛋蛋蛋H$E22222"; old.Prompt = "好多多读都多大"; old.UpdateTime = DateTime.Now; var id = old.Id; var t = DBSqliteFuncs.New.Update <LockPers>(old, w => w.Id == p.Id); }
public void 修改字段_多次拼接_20_6_5() { string colm = "img", val = "(select value from skin limit 1 offset 1)"; DateTime cdate = DateTime.Now; var update = DBSqlite <Users> .Update() .EditColumn(p => new bool[] { p.UserName == "123" }) .EditColumn(p => new bool[] { SM.Sql(colm, val) }) .EditColumn(p => new bool[] { SM.Sql(p.Remark, "(select '奥德赛 终于改好了')") }) .EditColumn(p => new bool[] { p.numx == 123 }) //.EditColumnEnd() .Where(p => p.Id == 6 && SM.SQL("IsDel == 0")); WriteSqlParams(update.RawSqlParams()); /* * 测试名称: 动态拼修改的字段_20_6_5 * 测试结果: 已通过 * 结果 的标准输出: * update Users set UserName=@UserName1_ , img=(select value from skin limit 1 offset 1) , Remark=(select '奥德赛 终于改好了') , numx=@numx1_ where Id = @Id0 and IsDel == 0 * "UserName1_ -- \"123\"" * "numx1_ -- 123.0" * "Id0 -- 6" */ }