public DataTable SelectSysUserByUserNameAndUserPwd(MSysUser objSysUser)
{
StringBuilder sb = new StringBuilder();
sb.Append("select top 1 * from tb_sysUser where userName=@UserName and userPwd=@UserPwd and IsSystemManager=@IsSysManager");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@UserName", SqlDbType.VarChar, 20, "userName", objSysUser.UserName),
SQLDbHelper.GetParameter("@UserPwd", SqlDbType.VarChar, 50, "userPwd", objSysUser.UserPwd),
SQLDbHelper.GetParameter("@IsSysManager", SqlDbType.Bit, "IsSystemManager", objSysUser.IsSystemManager)
};
DataTable dt = SQLDbHelper.ExecuteDt(sb.ToString(), param);
return(dt);
}
public void modifSoialInfor(string[] array)
{
StringBuilder sb2 = new StringBuilder();
sb2.Append("update association set size=@siz,sketch=@sk,manager=@man where joker=@username");
SqlParameter[] param2 =
{
SQLDbHelper.GetParameter("@siz", SqlDbType.NVarChar, 30, "username", array[0]),
SQLDbHelper.GetParameter("@sk", SqlDbType.NVarChar, 30, "usernamef", array[1]),
SQLDbHelper.GetParameter("@man", SqlDbType.NVarChar, 30, "username1", array[2]),
SQLDbHelper.GetParameter("@username", SqlDbType.NVarChar, 30, "username2", array[3])
};
SQLDbHelper.ExecuteSql(sb2.ToString(), param2);
}
public void InsertSocial2(string[] array)
{
StringBuilder sb2 = new StringBuilder();
sb2.Append("INSERT INTO association (socname, joker, sketch, size,pic) VALUES (@name,@joker,@sk,@si,@picurl)");
SqlParameter[] param2 =
{
SQLDbHelper.GetParameter("@name", SqlDbType.NVarChar, 30, "username", array[0]),
SQLDbHelper.GetParameter("@joker", SqlDbType.NVarChar, 30, "usernamef", array[1]),
SQLDbHelper.GetParameter("@sk", SqlDbType.NVarChar, 30, "username1", array[2]),
SQLDbHelper.GetParameter("@si", SqlDbType.NVarChar, 30, "username2", array[3]),
SQLDbHelper.GetParameter("@picurl", SqlDbType.NVarChar, 30, "username3", array[4])
};
SQLDbHelper.ExecuteSql(sb2.ToString(), param2);
}
//注册新用户函数 内部调用
public bool InsertUsers(string username, string pwd, string sex, string phone, string mail)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO userlogin (id, userpwd, sex, pnumber,email) VALUES (@name,@password,@sex,@pnum,@mail)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@name", SqlDbType.NVarChar, 30, "username", username),
SQLDbHelper.GetParameter("@password", SqlDbType.NVarChar, 30, "password", pwd),
SQLDbHelper.GetParameter("@sex", SqlDbType.NVarChar, 30, "sex", sex),
SQLDbHelper.GetParameter("@pnum", SqlDbType.NVarChar, 30, "pnum", phone),
SQLDbHelper.GetParameter("@mail", SqlDbType.NVarChar, 30, "mail", mail)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
return(true);
}
public void addBook(string bookName, string bookAuthor, string bookClass, string bookSetPrice, string bookPrice, string bookImage, string bookSummary, string bookPreview)
{
double temp = 0;
String str = "insert into Books (bookName,bookAuthor,bookClass,bookSetPrice,bookPrice,bookImage,bookSummary,bookPreview) values (@bookName,@bookAuthor,@bookClass,@bookSetPrice,@bookPrice,@bookImage,@bookSummary,@bookPreview)";
SqlParameter[] param = new SqlParameter[8];
param[0] = new SqlParameter("@bookName", bookName);
param[1] = new SqlParameter("@bookAuthor", bookAuthor);
param[2] = new SqlParameter("@bookClass", bookClass);
double.TryParse(bookSetPrice, out temp);
param[3] = new SqlParameter("@bookSetPrice", temp);
double.TryParse(bookPrice, out temp);
param[4] = new SqlParameter("@bookPrice", temp);
param[5] = new SqlParameter("@bookImage", bookImage);
param[6] = new SqlParameter("@bookSummary", bookSummary);
param[7] = new SqlParameter("@bookPreview", bookPreview);
SQLDbHelper.ExecuteSql(str, param);
}
private async void createProject_Click(object sender, System.Windows.RoutedEventArgs e)
{
var project = new Project {
Name = VM.ProjectName, ID = Guid.NewGuid().ToString()
};
VM.Projects.Add(project);
VM.IsCreatingProject = false;
VM.IsProjectNameEmpty = false;
VM.ProjectName = string.Empty;
DBStatus status = await SQLDbHelper.AddProject(project);
if (status == DBStatus.Failed)
{
System.Diagnostics.Debug.WriteLine($"Storing project information failed.");
}
}
//修改密码
public bool savePwd(string id, string pwd, string newpwd)
{
if (validUser(id, pwd))
{
StringBuilder sb = new StringBuilder();
sb.Append("update userlogin set userpwd=@pwd where ID=@username");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@pwd", SqlDbType.NVarChar, 30, "username", newpwd),
SQLDbHelper.GetParameter("@username", SqlDbType.NVarChar, 30, "username", id)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
return(true);
}
else
{
return(false);
}
}
public void updateBook(string bookName, string bookAuthor, string bookClass, string bookSetPrice, string bookPrice, string bookImage, string bookSummary, string bookPreview, string id)
{
double temp = 0;
String str = "update Books set bookName=@bookName,bookAuthor=@bookAuthor,bookClass=@bookClass,bookSetPrice=@bookSetPrice,bookPrice=@bookPrice,bookImage=@bookImage,bookSummary=@bookSummary,bookPreview=@bookPreview where bookID=@id";
SqlParameter[] param = new SqlParameter[9];
param[0] = new SqlParameter("@bookName", bookName);
param[1] = new SqlParameter("@bookAuthor", bookAuthor);
param[2] = new SqlParameter("@bookClass", bookClass);
double.TryParse(bookSetPrice, out temp);
param[3] = new SqlParameter("@bookSetPrice", temp);
double.TryParse(bookPrice, out temp);
param[4] = new SqlParameter("@bookPrice", temp);
param[5] = new SqlParameter("@bookImage", bookImage);
param[6] = new SqlParameter("@bookSummary", bookSummary);
param[7] = new SqlParameter("@bookPreview", bookPreview);
param[8] = new SqlParameter("@id", Convert.ToInt32(id));
SQLDbHelper.ExecuteSql(str, param);
}
public bool isManagerTwo(string name)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from association where manager=@social");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@social", SqlDbType.NVarChar, 30, "username", name)
};
DataTable table = SQLDbHelper.ExecuteDt(sb.ToString(), param);
if (table.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool DeleteEmployeeByID(MEmployee emp)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete from tb_employee where ID=@ID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "ID", emp.ID)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool DeleteNoticeByID(MNotice objNotice)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete from tb_notice where noticeID=@ID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "noticeID", objNotice.ID)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool InsertIntoDepartment(MDepartment objdepartment)
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into tb_department (name,duty_description) values(@DeptName,@Duty)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@DeptName", SqlDbType.VarChar, 50, "name", objdepartment.Name),
SQLDbHelper.GetParameter("@Duty", SqlDbType.Text, "duty_description", objdepartment.Duty_description)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
//注册 函数
public bool InserUser(string username, string pwd, string sex, string phone, string mail)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM userlogin WHERE id =@username ");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@username", SqlDbType.NVarChar, 30, "username", username)
};
DataTable table = SQLDbHelper.ExecuteDt(sb.ToString(), param);
if (table.Rows.Count > 0)
{
return(false);
}
else
{
return(InsertUsers(username, pwd, sex, phone, mail));
}
}
//登录
public bool validUser(string username, string password)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM userlogin WHERE ID =@username and userpwd =@password");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@username", SqlDbType.NVarChar, 30, "username", username),
SQLDbHelper.GetParameter("@password", SqlDbType.VarChar, 20, "userpwd", password)
};
DataTable table = SQLDbHelper.ExecuteDt(sb.ToString(), param);
if (table.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateVoteDisagreeQtyByID(MVote objvote)
{
StringBuilder sb = new StringBuilder();
sb.Append("update tb_vote set disagreeQty=@DisagreeQty where ID=@ID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@DisagreeQty", SqlDbType.Int, 4, "agreeQty", objvote.DisagreeQty),
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "ID", objvote.ID)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateRuleContentByID(MRule objrules)
{
StringBuilder sb = new StringBuilder();
sb.Append("update tb_rule set content=@RuleContent where id=@ID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@RuleContent", SqlDbType.Text, "content", objrules.RuleContent),
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "id", objrules.ID)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateSignStateByID(MSignState objsignstate)
{
StringBuilder sb = new StringBuilder();
sb.Append("update tb_signState set [time]=@SignTime where signStateID=@SignID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@SignTime", SqlDbType.DateTime, "[time]", objsignstate.Time),
SQLDbHelper.GetParameter("@SignID", SqlDbType.Int, 4, "ID", objsignstate.ID)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool InsertIntoSignState(MSignState objsignstate)
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into tb_signState (signstate_describe,[time]) values(@SignState,@Time)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@SignState", SqlDbType.VarChar, 50, "signstate_describe", objsignstate.Signstate_describe),
SQLDbHelper.GetParameter("@SignTime", SqlDbType.DateTime, "[time]", objsignstate.Time)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool InsertIntoRule(MRule objrules)
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into tb_rule (id,content) values(@ID,@RuleContent)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "id", objrules.ID),
SQLDbHelper.GetParameter("@RuleContent", SqlDbType.Text, "content", objrules.RuleContent)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateSysUserPwdByUserName(MSysUser objSysUser)
{
StringBuilder sb = new StringBuilder();
sb.Append("update tb_sysUser set userPwd=@UserPwd where userName=@UserName");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@UserName", SqlDbType.VarChar, 20, "userName", objSysUser.UserName),
SQLDbHelper.GetParameter("@UserPwd", SqlDbType.VarChar, 50, "userPwd", objSysUser.UserPwd)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool InsertIntoVote(MVote objvote)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO tb_vote (voteTitle, voteContent,agreeQty,disagreeQty) VALUES (@VoteTitle,@VoteContent,0,0)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@VoteTitle", SqlDbType.VarChar, 50, "voteTitle", objvote.VoteTitle),
SQLDbHelper.GetParameter("@VoteContent", SqlDbType.Text, "voteContent", objvote.VoteContent)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public virtual TEntity GetModelInfoByKeys(string[] keys)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from ");
sb.Append(this.TableName);
sb.Append(" where ");
//找出主键字段，顺序对应keys的顺序
IEnumerable <KeyValuePair <string, ColumAttr> > tempkey = this.ColumInfo.Where(c => c.Value.IsPrim);
for (int i = 0; i < keys.Count(); i++)
{
sb.Append("[");
sb.Append(tempkey.ElementAt(i).Value.Name);
sb.Append("]='");
sb.Append(keys.ElementAt(i));
sb.Append("' and");
}
sb = sb.Remove(sb.Length - "and".Length, "and".Length);
TEntity t = new TEntity();
PropertyInfo[] proarr = t.GetType().GetProperties().Where(p => this.ColumInfo.Where(c => c.Key == p.Name).Count() > 0).ToArray();
using (SqlDataReader sr = SQLDbHelper.ExecuteReader(sb.ToString()))
{
if (sr.Read())
{
foreach (var item in proarr)
{
item.SetValue(t, sr[this.ColumInfo[item.Name].Name], null);
}
return(t);
}
}
return(null);
}
public virtual int Update(TEntity entity)
{
//剔除主键列
IEnumerable <KeyValuePair <string, ColumAttr> > templist = this.ColumInfo.Where(c => !c.Value.IsPrim);
//得到主键列
IEnumerable <KeyValuePair <string, ColumAttr> > keylist = this.ColumInfo.Where(c => c.Value.IsPrim);
IEnumerable <PropertyInfo> proarray = entity.GetType().GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append("update ");
sb.Append(this.TableName);
sb.Append(" set ");
foreach (var item in templist)
{
sb.Append("[");
sb.Append(item.Value.Name);
sb.Append("]='");
sb.Append(proarray.First(p => p.Name == item.Key).GetValue(entity, null));
sb.Append("',");
}
sb = sb.Remove(sb.Length - 1, 1);
sb.Append(" where ");
foreach (var item in keylist)
{
sb.Append("[");
sb.Append(item.Value.Name);
sb.Append("]='");
sb.Append(proarray.First(p => p.Name == item.Key).GetValue(entity, null));
sb.Append("',");
}
sb = sb.Remove(sb.Length - 1, 1);
return(SQLDbHelper.ExecuteSql(sb.ToString()));
}
public virtual IList <TEntity> GetModelInfoList(Expression <Func <TEntity, bool> > exp, int pageszie, int currentindex)
{
StringBuilder sb = new StringBuilder();
sb.Append("select top ");
sb.Append(pageszie);
sb.Append(" * from (select *, row_number() over(order by [");
sb.Append(this.ColumInfo.First(c => c.Value.IsSort).Value.Name);
sb.Append("] desc) as ROW from ");
sb.Append(this.TableName);
sb.Append(")tempdb where tempdb.ROW>");
sb.Append((currentindex - 1) * pageszie);
sb.Append(" and ");
sb.Append(GetWhereStr(exp));
sb.Append(" order by ");
sb.Append(this.ColumInfo.Single(c => c.Value.IsSort).Value.Name);
sb.Append(" desc");
List <TEntity> list = new List <TEntity>();
using (SqlDataReader sr = SQLDbHelper.ExecuteReader(sb.ToString()))
{
while (sr.Read())
{
TEntity t = new TEntity();
PropertyInfo[] proarr = t.GetType().GetProperties().Where(p => this.ColumInfo.Where(c => c.Key == p.Name).Count() > 0).ToArray();
foreach (var item in proarr)
{
item.SetValue(t, sr[this.ColumInfo[item.Name].Name], null);
}
list.Add(t);
}
}
return(list);
}
public bool UpdateDepartmentByID(MDepartment objdept)
{
StringBuilder sb = new StringBuilder();
sb.Append("update tb_department set Name=@DeptName,duty_description=@Duty where ID=@ID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@DeptName", SqlDbType.VarChar, 50, "name", objdept.Name),
SQLDbHelper.GetParameter("@Duty", SqlDbType.Text, "duty_description", objdept.Duty_description),
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "ID", objdept.ID)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public virtual int Delete(string[] keys)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete ");
sb.Append(this.TableName);
sb.Append(" where ");
//找出主键字段，顺序对应keys的顺序
IEnumerable <KeyValuePair <string, ColumAttr> > tempkey = this.ColumInfo.Where(c => c.Value.IsPrim);
for (int i = 0; i < keys.Count(); i++)
{
sb.Append("[");
sb.Append(tempkey.ElementAt(i).Value.Name);
sb.Append("]='");
sb.Append(keys.ElementAt(i));
sb.Append("' and");
}
sb = sb.Remove(sb.Length - "and".Length, "and".Length);
return(SQLDbHelper.ExecuteSql(sb.ToString()));
}
public bool UpdateNoticeByID(MNotice objNotice)
{
StringBuilder sb = new StringBuilder();
sb.Append("update tb_notice set noticeTitle=@Title,noticePerson=@NoticePerson,noticeContent=@NoticeContent where noticeID=@ID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@Title", SqlDbType.VarChar, 40, "noticeTitle", objNotice.NoticeTitle),
SQLDbHelper.GetParameter("@NoticePerson", SqlDbType.VarChar, 20, "noticePerson", objNotice.NoticePerson),
SQLDbHelper.GetParameter("@NoticeContent", SqlDbType.Text, "noticeContent", objNotice.NoticeContent),
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "noticeID", objNotice.ID)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool InsertIntoNote(MNote objNote)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO tb_note (title, noteContent, noteTime, notePerson)VALUES(@Title,@NoteContent,@NoteTime,@NotePerson)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@Title", SqlDbType.VarChar, 50, "title", objNote.Title),
SQLDbHelper.GetParameter("@NoteContent", SqlDbType.Text, "noteContent", objNote.NoteContent),
SQLDbHelper.GetParameter("@NoteTime", SqlDbType.DateTime, "noteTime", objNote.NoteTime),
SQLDbHelper.GetParameter("@NotePerson", SqlDbType.VarChar, 20, "notePerson", objNote.NotePerson)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool InsertSocial(string[] array)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM association WHERE socname =@name ");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@name", SqlDbType.NVarChar, 30, "username", array[0])
};
DataTable table = SQLDbHelper.ExecuteDt(sb.ToString(), param);
if (table.Rows.Count > 0)
{
return(false);
}
else
{
InsertSocial2(array);
StringBuilder sb2 = new StringBuilder();
sb2.Append("SELECT * FROM association WHERE socname =@name ");
SqlParameter[] param2 =
{
SQLDbHelper.GetParameter("@name", SqlDbType.NVarChar, 30, "username", array[0])
};
DataTable table2 = SQLDbHelper.ExecuteDt(sb.ToString(), param2);
string assocID = "0";
if (table2.Rows.Count > 0)
{
assocID = table2.Rows[0]["social"].ToString();
}
signOut(array[1], assocID);
return(true);
}
}
public virtual int Add(TEntity entity)
{
//剔除自增列
IEnumerable <KeyValuePair <string, ColumAttr> > templist = this.ColumInfo.Where(c => !c.Value.IsIdentity);
//如果传进来的是User 是八个属性
IEnumerable <PropertyInfo> prolist = entity.GetType().GetProperties().ToList();
StringBuilder sb = new StringBuilder();
sb.Append("insert into ");
sb.Append(this.TableName);
sb.Append(" (");
foreach (var item in templist)
{
sb.Append("[");
sb.Append(item.Value.Name);
sb.Append("],");
}
sb = sb.Remove(sb.Length - 1, 1);
sb.Append(") values(");
//循环的目标是字段列表 Id ,LoginName,LoginPwd ,RealName HeadImage CreateTime IsEffc
//实体类属性列表 Prolist LoginName,RealName ,HeadImgae IsEffc ,Id
foreach (var item in templist)
{
sb.Append("'");
//获取entity里面属性的值
sb.Append(prolist.First(p => p.Name == item.Key).GetValue(entity, null));
sb.Append("',");
}
sb = sb.Remove(sb.Length - 1, 1);
sb.Append(")");
return(SQLDbHelper.ExecuteSql(sb.ToString()));
}
