public bool InsertStyleSoc(string[] array)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM stylesocial WHERE theme =@theme ");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@theme", SqlDbType.NVarChar, 30, "username", array[0])
};
DataTable table = SQLDbHelper.ExecuteDt(sb.ToString(), param);
if (table.Rows.Count > 0)
{
return(false);
}
else
{
StringBuilder sb2 = new StringBuilder();
sb2.Append("INSERT INTO stylesocial (theme, con, timeplace, participants,picurl,author,socname) VALUES (@theme,@con,@timep,@par,@picurl,@aut,@socn)");
SqlParameter[] param2 =
{
SQLDbHelper.GetParameter("@theme", SqlDbType.NVarChar, 30, "username", array[0]),
SQLDbHelper.GetParameter("@con", SqlDbType.NVarChar, 30, "usernamef", array[1]),
SQLDbHelper.GetParameter("@timep", SqlDbType.NVarChar, 30, "username1", array[2]),
SQLDbHelper.GetParameter("@par", SqlDbType.NVarChar, 30, "username2", array[3]),
SQLDbHelper.GetParameter("@picurl", SqlDbType.NVarChar, 30, "username3", array[4]),
SQLDbHelper.GetParameter("@aut", SqlDbType.NVarChar, 30, "username4", array[5]),
SQLDbHelper.GetParameter("@socn", SqlDbType.NVarChar, 30, "username5", array[6])
};
SQLDbHelper.ExecuteSql(sb2.ToString(), param2);
return(true);
}
}
public bool UpdateEmployeeByID(MEmployee emp)
{
StringBuilder sb = new StringBuilder();
sb.Append("update tb_employee set name=@EmpName,sex=@Sex,birthday=@Birthday,");
sb.Append("learnDegree=@LearnDegree,post=@Post,dept=@Department,job=@Job,tel=@Tel,");
sb.Append("address=@Address,email=@Email,state=@State where ID=@ID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "ID", emp.ID),
SQLDbHelper.GetParameter("@EmpName", SqlDbType.VarChar, 20, "name", emp.Name),
SQLDbHelper.GetParameter("@Sex", SqlDbType.VarChar, 10, "sex", emp.Sex),
SQLDbHelper.GetParameter("@Birthday", SqlDbType.SmallDateTime, "birthday", emp.Birthday),
SQLDbHelper.GetParameter("@LearnDegree", SqlDbType.VarChar, 50, "learnDegree", emp.LearnDegree),
SQLDbHelper.GetParameter("@Post", SqlDbType.VarChar, 50, "post", emp.Post),
SQLDbHelper.GetParameter("@Department", SqlDbType.VarChar, 50, "dept", emp.Dept),
SQLDbHelper.GetParameter("@Job", SqlDbType.VarChar, 50, "job", emp.Job),
SQLDbHelper.GetParameter("@Tel", SqlDbType.VarChar, 50, "tel", emp.Tel),
SQLDbHelper.GetParameter("@Address", SqlDbType.VarChar, 50, "address", emp.Address),
SQLDbHelper.GetParameter("@Email", SqlDbType.VarChar, 50, "email", emp.Email),
SQLDbHelper.GetParameter("@State", SqlDbType.VarChar, 50, "state", emp.State)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public void InSocialYesNo(string social, string id, int yn)
{
if (yn == 1)
{
StringBuilder sb = new StringBuilder();
sb.Append("update userlogin set member=@num,social=@soc where id=@id");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@num", SqlDbType.Int, 30, "username", 0),
SQLDbHelper.GetParameter("@soc", SqlDbType.NVarChar, 30, "rrrr", social),
SQLDbHelper.GetParameter("@id", SqlDbType.NVarChar, 30, "rffrr", id)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
else
{
StringBuilder sb = new StringBuilder();
sb.Append("update userlogin set member=@num,header=@soc where id=@id");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@num", SqlDbType.Int, 30, "username", 0),
SQLDbHelper.GetParameter("@soc", SqlDbType.NVarChar, 30, "rrrr", social),
SQLDbHelper.GetParameter("@id", SqlDbType.NVarChar, 30, "rffrr", id)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
}
public int saveUserInfo(string username, string password)
{
string str = "SELECT * FROM Users WHERE userName=@username";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@username", SqlDbType.NVarChar, 100, "userName", username)
};
DataTable table = SQLDbHelper.ExecuteDt(str, param);
if (table.Rows.Count > 0)
{
return(0);
}
else
{
str = "INSERT INTO Users (userName, userPwd, lastLogin) VALUES (@name,@password,'" + DateTime.Now + "')";
SqlParameter[] param1 =
{
SQLDbHelper.GetParameter("@name", SqlDbType.NVarChar, 100, "userName", username),
SQLDbHelper.GetParameter("@password", SqlDbType.NVarChar, 100, "userPwd", password)
};
return(SQLDbHelper.ExecuteSql(str, param1));
}
}
public bool InsertIntoEmployee(MEmployee emp)
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into tb_employee (name,sex,birthday,learnDegree,post,dept,job,tel,address,email,state,photoPath) values(@EmpName,@Sex,@Birthday,@LearnDegree,@Post,@Department,@Job,@Tel,@Address,@Email,@State,@PhotoPath)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@EmpName", SqlDbType.VarChar, 20, "name", emp.Name),
SQLDbHelper.GetParameter("@Sex", SqlDbType.VarChar, 10, "sex", emp.Sex),
SQLDbHelper.GetParameter("@Birthday", SqlDbType.SmallDateTime, "birthday", emp.Birthday),
SQLDbHelper.GetParameter("@LearnDegree", SqlDbType.VarChar, 50, "learnDegree", emp.LearnDegree),
SQLDbHelper.GetParameter("@Post", SqlDbType.VarChar, 50, "post", emp.Post),
SQLDbHelper.GetParameter("@Department", SqlDbType.VarChar, 50, "dept", emp.Dept),
SQLDbHelper.GetParameter("@Job", SqlDbType.VarChar, 50, "job", emp.Job),
SQLDbHelper.GetParameter("@Tel", SqlDbType.VarChar, 50, "tel", emp.Tel),
SQLDbHelper.GetParameter("@Address", SqlDbType.VarChar, 50, "address", emp.Address),
SQLDbHelper.GetParameter("@Email", SqlDbType.VarChar, 50, "email", emp.Email),
SQLDbHelper.GetParameter("@State", SqlDbType.VarChar, 50, "state", emp.State),
SQLDbHelper.GetParameter("@PhotoPath", SqlDbType.VarChar, 50, "photoPath", emp.PhotoPath)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public bool InsertIntoFile(MFile objfiles)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO tb_file (fileSender, fileAccepter, fileTitle, fileTime, fileContent, path, examine, fileName) VALUES(@FileSender,@FileAccepter,@FileTitle,@FileTime,@FileContent,@FilePath,@FileExamine,@FileName)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@FileSender", SqlDbType.VarChar, 20, "fileSender", objfiles.FileSender),
SQLDbHelper.GetParameter("@FileAccepter", SqlDbType.VarChar, 20, "fileAccepter", objfiles.FileAccepter),
SQLDbHelper.GetParameter("@FileTitle", SqlDbType.VarChar, 50, "fileTitle", objfiles.FileTitle),
SQLDbHelper.GetParameter("@FileTime", SqlDbType.DateTime, "fileTime", objfiles.FileTime),
SQLDbHelper.GetParameter("@FileContent", SqlDbType.Text, "fileContent", objfiles.FileContent),
SQLDbHelper.GetParameter("@FilePath", SqlDbType.VarChar, 100, "path", objfiles.Path),
SQLDbHelper.GetParameter("@FileExamine", SqlDbType.VarChar, 10, "examine", objfiles.Examine),
SQLDbHelper.GetParameter("@FileName", SqlDbType.VarChar, 50, "fileName", objfiles.FileName)
};
bool is_succeed = SQLDbHelper.ExecuteSql(sb.ToString(), param);
if (is_succeed)
{
return(true);
}
else
{
return(false);
}
}
public int InsertNews(string title, string content, string author)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from news where head=@tit");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@tit", SqlDbType.NVarChar, 30, "username", title)
};
DataTable table = SQLDbHelper.ExecuteDt(sb.ToString(), param);
if (table.Rows.Count > 0)
{
return(100);
}
else
{
StringBuilder sb2 = new StringBuilder();
sb2.Append("insert into news(head,con,author) values(@title,@content,@author)");
SqlParameter[] param2 =
{
SQLDbHelper.GetParameter("@title", SqlDbType.NVarChar, 30, "username", title),
SQLDbHelper.GetParameter("@content", SqlDbType.NVarChar, 30, "usernamef", content),
SQLDbHelper.GetParameter("@author", SqlDbType.NVarChar, 30, "username", author)
};
return(SQLDbHelper.ExecuteSql(sb2.ToString(), param2));
}
}
public void deteleBookByID(string Id)
{
String str = "delete from Books where bookID=@id";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@id", SqlDbType.Int, 32, "bookID", Convert.ToInt32(Id))
};
SQLDbHelper.ExecuteSql(str, param);
}
public void SocialSDelete(string id)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete stylesocial where styleid=@id");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@id", SqlDbType.NVarChar, 30, "id", id)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void DeleteSysUser(MSysUser objSysUser)
{
StringBuilder sb = new StringBuilder();
sb.Append("DELETE FROM tb_sysUser WHERE(userName=@UserName)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@UserName", SqlDbType.VarChar, 20, "userName", objSysUser.UserName)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public virtual int Delete(Expression <Func <TEntity, bool> > exp)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete ");
sb.Append(this.TableName);
sb.Append(" where ");
sb.Append(GetWhereStr(exp));
return(SQLDbHelper.ExecuteSql(sb.ToString()));
}
public void DeleteNoteByID(MNote objNote)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete from tb_note where ID=@ID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "ID", objNote.ID)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void updateBookBought(int id, int bought)
{
String str = "update Books set bookBought=@bookBought where bookID=@id";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@bookBought", SqlDbType.Int, 32, "bookBought", bought),
SQLDbHelper.GetParameter("@id", SqlDbType.Int, 32, "bookID", id)
};
SQLDbHelper.ExecuteSql(str, param);
}
public void DeleteFileByFileID(MFile objfiles)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete from tb_file where fileID=@FileID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@FileID", SqlDbType.Int, 4, "fileID", objfiles.ID)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void DeleteDepartmentByID(MDepartment objdept)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete from tb_department where ID=@ID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@ID", SqlDbType.Int, 4, "ID", objdept.ID)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void deleteNews(string id)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete news where newsid=@id");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@id", SqlDbType.NVarChar, 30, "rffrr", id)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void saveOrder(string bookNames, string userName, double orderPrice)
{
String str = "insert into Orders(bookNames, userName, orderPrice, orderTime) values (@bookNames, @userName, @orderPrice, '" + DateTime.Now + "')";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@bookNames", SqlDbType.NVarChar, 1000, "bookNames", bookNames),
SQLDbHelper.GetParameter("@userName", SqlDbType.NVarChar, 32, "userName", userName),
SQLDbHelper.GetParameter("@orderPrice", SqlDbType.Float, orderPrice)
};
SQLDbHelper.ExecuteSql(str, param);
}
public void UpdateSysUserSignStateByUserName(MSysUser objSysUser)
{
StringBuilder sb = new StringBuilder();
sb.Append("update tb_sysUser set signState=@SignState where userName=@UserName");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@SignState", SqlDbType.Bit, "signState", objSysUser.SignState),
SQLDbHelper.GetParameter("@UserName", SqlDbType.VarChar, 20, "userName", objSysUser.UserName)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void UpdateFileExaminByFileID(MFile objfiles)
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE tb_file SET examine = @Examine WHERE fileID =@FileID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@Examine", SqlDbType.VarChar, 10, "examine", objfiles.Examine),
SQLDbHelper.GetParameter("@FileID", SqlDbType.Int, 4, "fileID", objfiles.ID)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void signOut2(string id, string sid)
{
StringBuilder sb = new StringBuilder();
sb.Append("update userlogin set member=@ssaa where id=@id");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@ssaa", SqlDbType.NVarChar, 30, "username", sid),
SQLDbHelper.GetParameter("@id", SqlDbType.NVarChar, 30, "rffrr", id)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
//modify personal information
public void saveInfor(string id, string phone, string mail)
{
StringBuilder sb = new StringBuilder();
sb.Append("update userlogin set pnumber=@num,email=@email where id=@id");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@num", SqlDbType.NVarChar, 30, "username", phone),
SQLDbHelper.GetParameter("@email", SqlDbType.NVarChar, 30, "rrrr", mail),
SQLDbHelper.GetParameter("@id", SqlDbType.NVarChar, 30, "rffrr", id)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void InsertIntoSysUser(MSysUser objSysUser)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO tb_sysUser (userName, userPwd, loginTime, IsSystemManager) values(@UserName,@UserPwd,'',@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)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void modifyNews(string title, string content, string id)
{
StringBuilder sb = new StringBuilder();
sb.Append("update news set head=@title,con=@content where newsid=@id");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@title", SqlDbType.NVarChar, 30, "title", title),
SQLDbHelper.GetParameter("@content", SqlDbType.NVarChar, 30, "content", content),
SQLDbHelper.GetParameter("@id", SqlDbType.NVarChar, 30, "id", id),
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
public void InsertIntoSign(MSign objsign)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO tb_sign (datetime, employeeName, late, quit) values (@Datetime,@EmployeeName,@Late,@Quit)");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@Datetime", SqlDbType.DateTime, "datetime", objsign.Datetime),
SQLDbHelper.GetParameter("@EmployeeName", SqlDbType.VarChar, 20, "employeeName", objsign.EmployeeName),
SQLDbHelper.GetParameter("@Late", SqlDbType.Bit, "late", objsign.Late),
SQLDbHelper.GetParameter("@Quit", SqlDbType.Bit, "quit", objsign.Quit)
};
SQLDbHelper.ExecuteSql(sb.ToString(), param);
}
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);
}
//修改密码
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);
}
