public Gbook GetDataById(int Id)
{
Gbook SearchData = new Gbook();
string sql = "SELECT * FROM Guestbook WHERE Id=" + Id;
using (SqlConnection conn = new SqlConnection(cnstr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
SearchData.Id = Convert.ToInt32(rd[0]);
SearchData.Name = rd[1].ToString();
SearchData.Content = rd[2].ToString();
SearchData.CreateTime = Convert.ToDateTime(rd[3]);
SearchData.Reply = rd[4].ToString();
if (!string.IsNullOrEmpty(rd[5].ToString()))
{
SearchData.ReplyTime = Convert.ToDateTime(rd[5]);
}
}
}
}
return(SearchData);
}
/*public List<Gbook> GetAllGuestbooks(ForPaging forPaging, string Search = "")
* {
* List<Gbook> result = new List<Gbook>();
* string sql_AllData = "SELECT * FROM Guestbook";
* using (SqlConnection conn = new SqlConnection(cnstr))
* {
* conn.Open();
* using (SqlCommand cmd = new SqlCommand(sql_AllData, conn))
* {
* SqlDataReader rd = cmd.ExecuteReader();
* while (rd.Read())
* {
* Gbook gbook = new Gbook();
* gbook.Id = Convert.ToInt32(rd[0]);
* gbook.Name = rd[1].ToString();
* gbook.Content = rd[2].ToString();
* gbook.CreateTime = Convert.ToDateTime(rd[3]);
* gbook.Reply = rd[4].ToString();
* if (!Convert.IsDBNull(rd[5]))
* {
* gbook.ReplyTime = Convert.ToDateTime(rd[5]);
* }
* result.Add(gbook);
* }
* rd.Close();
* }
* conn.Close();
* }
* return result;
* }*/
#endregion
#region Index 加入搜尋
/*public List<Gbook> GetAllGuestbooks(ForPaging forPaging, string Search = "")
* {
* List<Gbook> result = new List<Gbook>();
* //字串過長，使用StringBuilder
* StringBuilder sql = new StringBuilder();
* //搜尋 -> WHERE 條件 || 分頁 -> ROW_NUMBER() OVER(ORDER BY Id) as Sort
* sql.Append(@"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Id) as Sort,* FROM Guestbook WHERE Name LIKE '%' + '" + Search + "' + '%' or Content LIKE '%' + '" + Search + "' + '%' or Reply LIKE '%' + '" + Search + "' + '%' ) AS Guestbook ");
* using (SqlConnection conn = new SqlConnection(cnstr))
* {
* conn.Open();
* using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
* {
* SqlDataReader rd = cmd.ExecuteReader();
* while (rd.Read())
* {
* Gbook gbook = new Gbook();
* gbook.Id = Convert.ToInt32(rd[0]);
* gbook.Name = rd[1].ToString();
* gbook.Content = rd[2].ToString();
* gbook.CreateTime = Convert.ToDateTime(rd[3]);
* gbook.Reply = rd[4].ToString();
* if (!Convert.IsDBNull(rd[5]))
* {
* gbook.ReplyTime = Convert.ToDateTime(rd[5]);
* }
* result.Add(gbook);
* }
* rd.Close();
* }
* conn.Close();
* }
* return result;
* }*/
#endregion
#region Index 加入搜尋和分頁
public List <Gbook> GetAllGuestbooks(ForPaging forPaging, string Search = "")
{
List <Gbook> result = new List <Gbook>();
//字串過長，使用StringBuilder
StringBuilder sql = new StringBuilder();
//搜尋 -> WHERE 條件 || 分頁 -> ROW_NUMBER() OVER(ORDER BY Id) as Sort
sql.Append(@"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Id) as Sort,* FROM Guestbook WHERE Name LIKE '%' + '" + Search + "' + '%' or Content LIKE '%' + '" + Search + "' + '%' or Reply LIKE '%' + '" + Search + "' + '%' ) AS Guestbook ");
sql.Append(@"WHERE Guestbook.Sort BETWEEN " + ((forPaging.NowPage - 1) * forPaging.ItemNum + 1) + " AND " + forPaging.NowPage * forPaging.ItemNum);
string sql_AllData = "SELECT * FROM Guestbook";
using (SqlConnection conn = new SqlConnection(cnstr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
{
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
Gbook gbook = new Gbook();
gbook.Id = Convert.ToInt32(rd[1]);
gbook.Name = rd[2].ToString();
gbook.Content = rd[3].ToString();
gbook.CreateTime = Convert.ToDateTime(rd[4]);
gbook.Reply = rd[5].ToString();
if (!Convert.IsDBNull(rd[6]))
{
gbook.ReplyTime = Convert.ToDateTime(rd[6]);
}
result.Add(gbook);
}
rd.Close();
}
//計算列數
int RowCount = 0;
using (SqlCommand cmd = new SqlCommand(sql_AllData, conn))
{
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
RowCount++;
}
rd.Close();
}
forPaging.MaxPage = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(RowCount) / forPaging.ItemNum));
forPaging.SetRightPage();
conn.Close();
}
return(result);
}
public void InsertNewGuestbook(Gbook Data)
{
Data.CreateTime = DateTime.Now;
string sql = "INSERT INTO Guestbook(Name,Content,CreateTime) VALUES ('" + Data.Name + "','" + Data.Content + "','" + Data.CreateTime.ToString("yyyy/MM/dd HH:mm:ss") + "')";
using (SqlConnection conn = new SqlConnection(cnstr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public void EditGuestbook(Gbook EditData)
{
if (string.IsNullOrEmpty(EditData.Reply))
{
string sql = "UPDATE Guestbook SET Name='" + EditData.Name + "',Content='" + EditData.Content + "' WHERE Id=" + EditData.Id;
using (SqlConnection conn = new SqlConnection(cnstr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
else
{
return;
}
}
public void ReplyGuestbook(Gbook ReplyData)
{
if (!string.IsNullOrEmpty(ReplyData.Reply))
{
ReplyData.ReplyTime = DateTime.Now;
string sql = "UPDATE Guestbook SET Reply='" + ReplyData.Reply + "',ReplyTime ='" + ReplyData.ReplyTime.ToString("yyyy/MM/dd HH:mm:ss") + "' WHERE Id=" + ReplyData.Id;
using (SqlConnection conn = new SqlConnection(cnstr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
else
{
return;
}
}
public ActionResult Reply(Gbook ReplyData)
{
guestbookService.ReplyGuestbook(ReplyData);
return(RedirectToAction("Index"));
}
public ActionResult Reply(int Id)
{
Gbook Data = guestbookService.GetDataById(Id);
return(View(Data));
}
public ActionResult Edit(Gbook EditData)
{
guestbookService.EditGuestbook(EditData);
return(RedirectToAction("Index"));
}
public ActionResult Add(Gbook Data)
{
guestbookService.InsertNewGuestbook(Data);
return(RedirectToAction("Index"));
}
