//삭제 버튼 클릭
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"
*/
}
