//修改
public int Update(LBSearchArg viewresult)
{
string sql = @"
UPDATE dbo.BOOK_DATA
SET
BOOK_NAME=@BOOK_NAME,
BOOK_AUTHOR=@BOOK_AUTHOR,
BOOK_PUBLISHER=@BOOK_PUBLISHER,
BOOK_NOTE=@BOOK_NOTE,
BOOK_BOUGHT_DATE=@BOOK_BOUGHT_DATE,
BOOK_CLASS_ID=@BOOK_CLASS_ID,
BOOK_STATUS=@BOOK_STATUS";
int Id;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", viewresult.BookName));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", viewresult.BookAuthor));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", viewresult.Pubilsher));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", viewresult.BookIntroduce));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", viewresult.BoughtDate));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", viewresult.BookClassName));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", viewresult.BookStatus));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
Id = cmd.ExecuteNonQuery();
conn.Close();
}
return(Id);
}
//新增書籍
public int Insert(LBSearchArg viewresult)
{
string sql = @" INSERT INTO dbo.Post
(
BOOK_NAME,BOOK_AUTHOR,BOOK_PUBLISHER,BOOK_NOTE,BOOK_BOUGHT_DATE,BOOK_CLASS_ID,BOOK_STATUS
)
VALUES
(
@BOOK_NAME,@BOOK_AUTHOR,@BOOK_PUBLISHER,@BOOK_NOTE,@BOOK_BOUGHT_DATE,@BOOK_CLASS_ID,@BOOK_STATUS
)";
int Id;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@PostID", viewresult.PostID));
cmd.Parameters.Add(new SqlParameter("@PostTitle", viewresult.PostTitle));
cmd.Parameters.Add(new SqlParameter("@PostContent", viewresult.PostContent));
cmd.Parameters.Add(new SqlParameter("@Kind", viewresult.Kind));
cmd.Parameters.Add(new SqlParameter("@MeetAddress", viewresult.MeetAddress));
cmd.Parameters.Add(new SqlParameter("@StartTime", viewresult.StartTime));
cmd.Parameters.Add(new SqlParameter("@EndTime", viewresult.EndTime));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
Id = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return(Id);
}
//新增書籍
public int InsertBook(LBSearchArg viewresult)
{
string sql = @" INSERT INTO dbo.BOOK_DATA
(
BOOK_NAME,BOOK_AUTHOR,BOOK_PUBLISHER,BOOK_NOTE,BOOK_BOUGHT_DATE,BOOK_CLASS_ID,BOOK_STATUS
)
VALUES
(
@BOOK_NAME,@BOOK_AUTHOR,@BOOK_PUBLISHER,@BOOK_NOTE,@BOOK_BOUGHT_DATE,@BOOK_CLASS_ID,@BOOK_STATUS
)" ;
int Id;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", viewresult.BookName));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", viewresult.BookAuthor));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", viewresult.Pubilsher));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", viewresult.BookIntroduce));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", viewresult.BoughtDate));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", viewresult.BookClassName));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", viewresult.BookStatus));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
Id = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return(Id);
}
//查詢書籍
public List <LB.Model.LBBooks> SearchBook(LBSearchArg viewresult)
{
DataTable dt = new DataTable();
string sql = @"Select BOOK_CLASS_NAME,BOOK_NAME,BOOK_BOUGHT_DATE,CODE_NAME,USER_CNAME
FROM dbo.BOOK_DATA as e
LEFT JOIN dbo.BOOK_CLASS as bc
ON (e.BOOK_CLASS_ID = bc.BOOK_CLASS_ID)
LEFT JOIN dbo.BOOK_CODE as code
ON (e.BOOK_STATUS = code.CODE_ID)
LEFT JOIN dbo.MEMBER_M as mm
ON (e.BOOK_KEEPER = mm.USER_ID)
Where (e.BOOK_NAME LIKE ('%'+@BOOK_NAME+'%') OR @BOOK_NAME='')
AND (bc.BOOK_CLASS_NAME LIKE ('%'+@BOOK_CLASS_NAME+'%') OR @BOOK_CLASS_NAME='')
AND (mm.USER_CNAME LIKE ('%'+@BOOK_KEEPER+'%') OR @BOOK_KEEPER='')
AND (code.CODE_ID LIKE ('%'+@BOOK_STATUS+'%') OR @BOOK_STATUS='')";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", viewresult.BookName == null ? string.Empty : viewresult.BookName));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_NAME", viewresult.BookClassName == null ? string.Empty : viewresult.BookClassName));
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", viewresult.BookKeeper == null ? string.Empty : viewresult.BookKeeper));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", viewresult.BookStatus == null ? string.Empty : viewresult.BookStatus));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
//新增貼文
public int Insert(LBSearchArg viewresult)
{
string sql = @"INSERT INTO [dbo].Post(
PostID,PostTitle,PostContent,Kind,MeetAddress,StartTime,EndTime,UserID,Status,lat,lng,City,IsShowPhone)
VALUES(
@PostID,@PostTitle,@PostContent,@Kind,@MeetAddress,@StartTime,@EndTime,@UserID,@Status,@PostLat,@PostLong,@City,@IsShowPhone
)";
int Id;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@PostID", "1"));
cmd.Parameters.Add(new SqlParameter("@UserID", viewresult.UserID));
cmd.Parameters.Add(new SqlParameter("@PostTitle", viewresult.PostTitle));
cmd.Parameters.Add(new SqlParameter("@PostContent", viewresult.PostContent));
cmd.Parameters.Add(new SqlParameter("@Kind", viewresult.Kind));
cmd.Parameters.Add(new SqlParameter("@MeetAddress", viewresult.MeetAddress));
cmd.Parameters.Add(new SqlParameter("@StartTime", DateTime.Parse(viewresult.StartTime)));
cmd.Parameters.Add(new SqlParameter("@EndTime", DateTime.Parse(viewresult.EndTime)));
cmd.Parameters.Add(new SqlParameter("@PostLat", viewresult.PostLat));
cmd.Parameters.Add(new SqlParameter("@PostLong", viewresult.PostLong));
cmd.Parameters.Add(new SqlParameter("@City", viewresult.City));
cmd.Parameters.Add(new SqlParameter("@IsShowPhone", viewresult.IsShowPhone));
cmd.Parameters.Add(new SqlParameter("@Status", "unFinish"));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
Id = Convert.ToInt32(cmd.ExecuteNonQuery());
conn.Close();
}
return(Id);
}
public void GetLibraryDataTest()
{
LBSearchArg viewresult = new LBSearchArg();
LBService service = new LBService();
service.lbDao = new TestDao();
var testRes = service.GetLibraryData(viewresult);
//因為建構假Dao時回傳的dt為兩筆，因此count=6時表示成功
Assert.AreEqual(6, testRes.Count());
}
public void IndexTest()
{
LBSearchArg viewresult = new LBSearchArg();
viewresult.BookName = "789";
Mock <ILBService> mockService = new Mock <ILBService>();
mockService.Setup(x => x.InsertBook(viewresult)).Returns(1);
var controller = new LibraryController();
controller.Insert(viewresult);
}
public int InsertBook(LBSearchArg viewresult)
{
DataTable dt = new DataTable();
dt.Columns.Add("BOOK_NAME");
dt.Columns.Add("BOOK_CLASS_NAME");
dt.Columns.Add("BOOK_KEEPER");
dt.Columns.Add("BOOK_STATUS");
dt.Rows.Add(viewresult.BookName, "旅遊類", "王五", "A");
var list = this.MapBookDataToList(dt);
return(list.Count());
}
public void InsertBookTest_Moq()
{
LBSearchArg viewresult = new LBSearchArg();
viewresult.BookName = "789";
Mock <LBService> mockService = new Mock <LBService>();
var Res = mockService.Setup(x => x.InsertBook(viewresult)).Returns(1);
Assert.AreEqual(1, Res);
//var service = new LBService();
//service.Should().NotBeNull();
//Assert.Fail();
}
public List <LBBooks> GetLibraryData(LBSearchArg viewresult)
{
var result = new List <LBBooks>();
result.Add(new LBBooks
{
BookName = "在顛沛流離的世界你還有我啊",
BookClassId = "Life",
BookKeeper = "PeterSu",
BoughtDate = "2018/12/25",
BookStatus = "可以借出"
});
return(result);
}
public List <LBBooks> GetLibraryData(LBSearchArg viewresult)
{
DataTable dt = new DataTable();
dt.Columns.Add("BOOK_NAME");
dt.Columns.Add("BOOK_CLASS_NAME");
dt.Columns.Add("BOOK_KEEPER");
dt.Columns.Add("BOOK_STATUS");
dt.Rows.Add("123", "生活類", "張三", "A");
dt.Rows.Add("456", "旅遊類", "張三", "A");
dt.Rows.Add("XXX", "生活類", "李四", "A");
dt.Rows.Add("YYY", "旅遊類", "李四", "A");
dt.Rows.Add("ZZZ", "生活類", "張三", "A");
dt.Rows.Add("UUU", "旅遊類", "張三", "A");
return(this.MapBookDataToList(dt));
}
public List <LB.Model.LBBooks> SearchBook(LBSearchArg viewresult)
{
//IList<KeyValuePair<string, string>> Prms = new List<KeyValuePair<string, string>>();
//Prms.Add(new KeyValuePair<string, string>("@BOOK_NAME", string.Empty));
//Prms.Add(new KeyValuePair<string, string>("@BOOK_CLASS_NAME", string.Empty));
//Prms.Add(new KeyValuePair<string, string>("@BOOK_KEEPER", "張三"));
//Prms.Add(new KeyValuePair<string, string>("@BOOK_STATUS", string.Empty));
DataTable dt = new DataTable();
dt.Columns.Add("BOOK_NAME");
dt.Columns.Add("BOOK_CLASS_NAME");
dt.Columns.Add("BOOK_KEEPER");
dt.Columns.Add("BOOK_STATUS");
dt.Rows.Add(viewresult.BookName, "旅遊類", "王五", "A");
return(this.MapBookDataToList(dt));
}
public void SearchBookTest()
{
//Arrange
//假設使用者查詢書名"456"
LBSearchArg viewresult = new LBSearchArg();
viewresult.BookName = "456";
LBService service = new LBService();
service.lbDao = new TestDao();
//Act
var testRes = service.SearchBook(viewresult);
//Assert
//和丟入的書名相同
Assert.AreEqual("456", testRes[0].BookName);
}
public void InsertBookTest()
{
//Arrange
//假設使用者新增書名"789"
LBSearchArg viewresult = new LBSearchArg();
viewresult.BookName = "789";
LBService service = new LBService();
service.lbDao = new TestDao();
//Act
var testRes = service.InsertBook(viewresult);
//Assert
//新增一筆
Assert.AreEqual(1, testRes);
}
//載入畫面時GET書籍資料放到kendoGrid
public List <GHPost> GetLibraryData(LBSearchArg viewresult)
{
DataTable dt = new DataTable();
string sql = @"select Nickname,PostTitle,Kind,MeetAddress,StartTime
from Member
inner join Post on Member.UserID = Post.UserID
";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
//載入畫面時GET書籍資料放到kendoGrid
public List <GHPost> GetLibraryData(LBSearchArg viewresult)
{
DataTable dt = new DataTable();
string sql = @"SELECT Post.rowid,Nickname,PostTitle,Kind,MeetAddress,StartTime,EndTime,PostContent,Helper,Status,lat,lng,City,Phone,IsShowPhone
FROM dbo.[Post]
LEFT JOIN dbo.[HelpUser]
ON dbo.[Post].UserID = dbo.[HelpUser].UserID
WHERE Status = 'unfinish'
order by StartTime desc
";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
//載入畫面時GET書籍資料放到kendoGrid
public List <LBBooks> GetLibraryData(LBSearchArg viewresult)
{
DataTable dt = new DataTable();
string sql = @"Select BOOK_CLASS_NAME,BOOK_NAME,BOOK_BOUGHT_DATE,CODE_NAME,USER_CNAME
FROM dbo.BOOK_DATA as e
LEFT JOIN dbo.BOOK_CLASS as bc
ON (e.BOOK_CLASS_ID = bc.BOOK_CLASS_ID)
LEFT JOIN dbo.BOOK_CODE as code
ON (e.BOOK_STATUS = code.CODE_ID)
LEFT JOIN dbo.MEMBER_M as mm
ON (e.BOOK_KEEPER = mm.USER_ID)
";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
//載入畫面時GET書籍資料放到kendoGrid
public List <LBBooks> GetLibraryData(LBSearchArg viewresult)
{
return(lbDao.GetLibraryData(viewresult));
}
public JsonResult Update(LBSearchArg BookId)
{
return(this.Json(lbService.Update(BookId)));
}
public int InsertBook(LBSearchArg viewresult)
{
throw new NotImplementedException();
}
public JsonResult Insert(LBSearchArg insertData)
{
return(this.Json(lbService.InsertBook(insertData)));
}
public JsonResult Search(LBSearchArg viewresult)
{
List <LBBooks> bookList = lbService.SearchBook(viewresult);
return(this.Json(bookList));
}
public JsonResult Index(LBSearchArg viewresult)
{
List <LBBooks> bookList = lbService.GetLibraryData(viewresult);
return(this.Json(bookList));
}
//載入畫面時GET書籍資料放到kendoGrid
public List <GHPost> GetLibraryData(LBSearchArg viewresult)
{
return(lbDao.GetLibraryData(viewresult));
}
public List <LBBooks> SearchBook(LBSearchArg viewresult)
{
throw new NotImplementedException();
}
//修改
public int Update(LBSearchArg viewresult)
{
return(lbDao.Update(viewresult));
}
//查詢書籍
public List <LB.Model.LBBooks> SearchBook(LBSearchArg viewresult)
{
return(lbDao.SearchBook(viewresult));
}
public int Update(LBSearchArg viewresult)
{
throw new NotImplementedException();
}
//新增書籍
public int InsertBook(LBSearchArg viewresult)
{
return(lbDao.InsertBook(viewresult));
}
public JsonResult Insert(string insertData)
{
LBSearchArg viewresult = new LBSearchArg();
return(this.Json(lbService.Insert(viewresult)));
}
