public List<Model.BookCategory> GetDistinct(Int64 _BookCategoryID, Int64 _CategoryID, Int64 _BookID, int _Status, int _Start, int _Limit)
{
List<Model.BookCategory> lModel = new List<Model.BookCategory>();
try
{
SqlParameter[] p = {
new SqlParameter("@BookCategoryID", SqlDbType.Int, 4),
new SqlParameter("@CategoryID", SqlDbType.Int, 4),
new SqlParameter("@BookID", SqlDbType.Int, 4),
new SqlParameter("@Status", SqlDbType.Int, 4),
new SqlParameter("@Start", SqlDbType.Int, 4),
new SqlParameter("@Limit", SqlDbType.Int, 4)
};
p[0].Value = _BookCategoryID;
p[1].Value = _CategoryID;
p[2].Value = _BookID;
p[3].Value = _Status;
p[4].Value = _Start;
p[5].Value = _Limit;
DataTable dt = db.ExcuteSelectReturnDataTable("BookCategory_Distinct", CommandType.StoredProcedure, p);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
Model.BookCategory model = new Model.BookCategory();
model.Ind = Convert.ToInt32(dr["Ind"]);
model.BookID = Convert.ToInt32(dr["BookID"]);
model.Title = dr["Title"].ToString();
model.TitleUrl = dr["TitleUrl"].ToString();
model.SmallPicture = dr["SmallPicture"].ToString();
model.Author = dr["Author"].ToString();
model.Price = Convert.ToInt32(dr["Price"]);
model.PriceText = dr["PriceText"].ToString();
model.Status = Convert.ToInt16(dr["Status"]);
model.CategoryName = string.Empty;
model.CategoryNameUrl = string.Empty;
model.CreateBy = string.Empty;
model.CreateTime = string.Empty;
model.UpdateBy = string.Empty;
model.UpdateTime = string.Empty;
model.TotalRow = Convert.ToInt64(dr["TotalRow"]);
lModel.Add(model);
}
}
}
catch (Exception ex)
{
PTSLog.Error(ex.Message);
}
return lModel;
}
public static void InitTestDBContext(ECAEntities @this)
{
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(@"../../TestData.xlsx", false))
{
var sheets = mySpreadsheet.WorkbookPart.Workbook.Sheets;
SharedStringTable stringTable = mySpreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
//Author
Sheet sAuthor = sheets.Descendants<Sheet>().Where(s => s.Name == "Author").FirstOrDefault();
WorksheetPart wsPart = mySpreadsheet.WorkbookPart.GetPartById(sAuthor.Id) as WorksheetPart;
SheetData sheetData = wsPart.Worksheet.Elements<SheetData>().First();
@this.Authors = new FakeDbSet<Model.Author>();
foreach (Row r in sheetData.Elements<Row>())
{
if (r.RowIndex == 1)
continue;
ECA.Model.Author author = new Model.Author();
author.AuthorId = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(0), 0);
author.AuthorName = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(1), 1);
@this.Authors.Add(author);
}
//Book
Sheet sBook = sheets.Descendants<Sheet>().Where(s => s.Name == "Book").FirstOrDefault();
wsPart = mySpreadsheet.WorkbookPart.GetPartById(sBook.Id) as WorksheetPart;
sheetData = wsPart.Worksheet.Elements<SheetData>().First();
@this.Books = new FakeDbSet<Model.Book>();
foreach (Row r in sheetData.Elements<Row>())
{
if (r.RowIndex == 1)
continue;
ECA.Model.Book book = new Model.Book();
book.ID = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(0), 0);
book.Title = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(1), 1);
book.Description = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(2), 2);
book.AuthorId = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(3), 3);
book.GenreId = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(4), 4);
book.CategoryId = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(5), 5);
book.ISBN = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(6), 6);
@this.Books.Add(book);
}
//Book Category
Sheet sBookCategory = sheets.Descendants<Sheet>().Where(s => s.Name == "BookCategory").FirstOrDefault();
wsPart = mySpreadsheet.WorkbookPart.GetPartById(sBookCategory.Id) as WorksheetPart;
sheetData = wsPart.Worksheet.Elements<SheetData>().First();
@this.BookCategories = new FakeDbSet<Model.BookCategory>();
foreach (Row r in sheetData.Elements<Row>())
{
if (r.RowIndex == 1)
continue;
ECA.Model.BookCategory category = new Model.BookCategory();
category.CategoryId = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(0), 0);
category.CategoryName = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(1), 1);
@this.BookCategories.Add(category);
}
//Genre
Sheet sGenre = sheets.Descendants<Sheet>().Where(s => s.Name == "Genre").FirstOrDefault();
wsPart = mySpreadsheet.WorkbookPart.GetPartById(sGenre.Id) as WorksheetPart;
sheetData = wsPart.Worksheet.Elements<SheetData>().First();
@this.Genres = new FakeDbSet<Model.Genre>();
foreach (Row r in sheetData.Elements<Row>())
{
if (r.RowIndex == 1)
continue;
ECA.Model.Genre genre = new Model.Genre();
genre.GenreId = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(0), 0);
genre.GenreName = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(1), 1);
@this.Genres.Add(genre);
}
//Shopping Cart
Sheet sCart = sheets.Descendants<Sheet>().Where(s => s.Name == "Cart").FirstOrDefault();
wsPart = mySpreadsheet.WorkbookPart.GetPartById(sCart.Id) as WorksheetPart;
sheetData = wsPart.Worksheet.Elements<SheetData>().First();
@this.Carts = new FakeDbSet<Model.Cart>();
foreach (Row r in sheetData.Elements<Row>())
{
if (r.RowIndex == 1)
continue;
ECA.Model.Cart cart = new Model.Cart();
cart.UserId = Convert.ToInt32( r.Elements<Cell>().ElementAt(0).CellValue.Text);
cart.ItemCode = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(1), 1);
cart.Quantity = Convert.ToInt32(r.Elements<Cell>().ElementAt(2).CellValue.Text);
@this.Carts.Add(cart);
}
//Users
Sheet sUser = sheets.Descendants<Sheet>().Where(s => s.Name == "User").FirstOrDefault();
wsPart = mySpreadsheet.WorkbookPart.GetPartById(sUser.Id) as WorksheetPart;
sheetData = wsPart.Worksheet.Elements<SheetData>().First();
@this.Users = new FakeDbSet<Model.User>();
foreach (Row r in sheetData.Elements<Row>())
{
if (r.RowIndex == 1)
continue;
ECA.Model.User user = new Model.User();
user.UserId = Convert.ToInt32(r.Elements<Cell>().ElementAt(0).CellValue.Text);
user.UserName = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(1), 1);
@this.Users.Add(user);
}
//Roles
Sheet sRoles = sheets.Descendants<Sheet>().Where(s => s.Name == "Role").FirstOrDefault();
wsPart = mySpreadsheet.WorkbookPart.GetPartById(sRoles.Id) as WorksheetPart;
sheetData = wsPart.Worksheet.Elements<SheetData>().First();
@this.webpages_Roles = new FakeDbSet<Model.webpages_Roles>();
foreach (Row r in sheetData.Elements<Row>())
{
if (r.RowIndex == 1)
continue;
ECA.Model.webpages_Roles role = new Model.webpages_Roles();
role.RoleId = Convert.ToInt32(r.Elements<Cell>().ElementAt(0).CellValue.Text);
role.RoleName = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(1), 1);
@this.webpages_Roles.Add(role);
}
@this.webpages_Roles.Where(r => r.RoleId == 1).FirstOrDefault().Users = new List<User>()
{
new User(){ UserId = 1, UserName = "******"},
new User(){ UserId = 2,UserName = "******"}
};
@this.webpages_Roles.Where(r => r.RoleId == 2).FirstOrDefault().Users = new List<User>()
{
new User(){ UserId = 3, UserName = "******"}
};
//Membership
Sheet sMembership = sheets.Descendants<Sheet>().Where(s => s.Name == "Membership").FirstOrDefault();
wsPart = mySpreadsheet.WorkbookPart.GetPartById(sMembership.Id) as WorksheetPart;
sheetData = wsPart.Worksheet.Elements<SheetData>().First();
@this.webpages_Membership = new FakeDbSet<Model.webpages_Membership>();
foreach (Row r in sheetData.Elements<Row>())
{
if (r.RowIndex == 1)
continue;
ECA.Model.webpages_Membership membership = new Model.webpages_Membership();
membership.UserId = Convert.ToInt32(r.Elements<Cell>().ElementAt(0).CellValue.Text);
membership.Password = GetStringValue(stringTable, r.Elements<Cell>().ElementAt(6), 6);
@this.webpages_Membership.Add(membership);
}
}
}
