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 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 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 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 DataTable getBookByClass(string bookClass)
{
String str = "select * from Books where bookClass=@message order by bookID desc";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@message", SqlDbType.NVarChar, 50, "bookAuthor", bookClass)
};
return(SQLDbHelper.ExecuteDt(str, param));
}
public DataTable getBookByAuthor(string author)
{
String str = "select * from Books where bookAuthor like '%'+@message+'%' order by bookID desc";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@message", SqlDbType.NVarChar, 50, "bookAuthor", author)
};
return(SQLDbHelper.ExecuteDt(str, param));
}
public DataTable getBookByName(string bookName)
{
String str = "select 1 from Books where bookName=@bookName";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@bookName", SqlDbType.NVarChar, 50, "bookName", bookName)
};
return(SQLDbHelper.ExecuteDt(str, param));
}
public DataTable getOrderByUserName(string userName)
{
String str = "select * from Orders where userName=@userName order by orderID desc";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@userName", SqlDbType.NVarChar, 100, "userName", userName)
};
return(SQLDbHelper.ExecuteDt(str, param));;
}
public DataTable getBookByID(string Id)
{
String str = "select * from Books where bookID=@id";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@id", SqlDbType.Int, 32, "bookID", Convert.ToInt32(Id))
};
return(SQLDbHelper.ExecuteDt(str, param));
}
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 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 DataTable validUser(string username, string password)
{
string str = "select * from Users where userName=@username and userPwd=@password";
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@username", SqlDbType.NVarChar, 100, "userName", username),
SQLDbHelper.GetParameter("@password", SqlDbType.NVarChar, 100, "userPwd", password),
};
return(SQLDbHelper.ExecuteDt(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 DataTable SocialInfor(string name)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from association where joker=@social");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@social", SqlDbType.NVarChar, 30, "username", name)
};
return(SQLDbHelper.ExecuteDt(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 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 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 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 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 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);
}
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 DataTable SelectAllNotesByNotePerson(MNote objNote)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from tb_note where notePerson =@NotePerson order by noteTime desc");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@NotePerson", SqlDbType.VarChar, 20, "notePerson", objNote.NotePerson)
};
DataTable dt = SQLDbHelper.ExecuteDt(sb.ToString(), param);
return(dt);
}
public DataTable SelectFilesByFileID(MFile objfiles)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from tb_file where fileID=@FileID");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@FileID", SqlDbType.Int, 4, "fileID", objfiles.ID)
};
DataTable dt = SQLDbHelper.ExecuteDt(sb.ToString(), param);
return(dt);
}
public DataTable SelectAllFilesByAccepter(MFile objfiles)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from tb_file where fileAccepter=@FileAccepter order by fileTime desc");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@FileAccepter", SqlDbType.VarChar, 20, "fileAccepter", objfiles.FileAccepter)
};
DataTable dt = SQLDbHelper.ExecuteDt(sb.ToString(), param);
return(dt);
}
public DataTable SelectAllFilesByExamine(MFile objfiles)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from tb_file where examine=@Examine order by fileTime desc");
SqlParameter[] param =
{
SQLDbHelper.GetParameter("@Examine", SqlDbType.VarChar, 10, "examine", objfiles.Examine)
};
DataTable dt = SQLDbHelper.ExecuteDt(sb.ToString(), param);
return(dt);
}
