public static BookDTO getBookTitle(string Title)
{
BookDTO dto = new BookDTO();
string _connectionString = DataSource.GetConnectionString("library2"); // Make possible to define and use different connectionstrings
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM book WHERE title = " + Title, con);
try
{
con.Open();
SqlDataReader dar = cmd.ExecuteReader();
if (dar.Read())
{
dto.isbnNo = dar["ISBN"] as string;
dto.title = dar["Title"] as string;
dto.signId = (int)dar["SignId"];
dto.publicationYear = dar["PublicationYear"] as string;
dto.publisher = dar["Publisher"] as string;
dto.libNumber = (int)dar["LibNo"];
}
}
catch (Exception er)
{
throw er;
}
finally
{
con.Close();
}
return dto;
}
public BookDTO(BookDTO dto)
{
this.loadStatus = dto.loadStatus; // Lazy load
isbnNo = dto.isbnNo;
title = dto.title;
signId = dto.signId;
publicationYear = dto.publicationYear;
publisher = dto.publisher;
libNumber = dto.libNumber;
location = dto.location;
classificationCode = dto.classificationCode;
publicationInfo = dto.publicationInfo;
pages = dto.pages;
}
//A constructor which creates a DTO-object with data from an existing DTO-object
public Book(BookDTO _sourceDTO)
{
_bookDTO = new BookDTO(_sourceDTO);
}
//Initialize a new DTO-object for the transferring data about author
public Book()
{
_bookDTO = new BookDTO();
}
//If the load of data to the database is partial (Ghost) do a full load
private void Load()
{
try
{
if(_bookDTO.loadStatus == LoadStatus.Ghost)
{
_bookDTO = LibraryDataAccess.getBookTitle(_bookDTO.isbnNo);
_bookDTO.loadStatus = LoadStatus.Loaded;
}
}
catch (Exception ex)
{
//Do some error-log functionality with ex.Data
}
}
public static List<BookDTO> getAllAuthorBookDAL(List<string> isbnList)
{
List<BookDTO> dtoList = new List<BookDTO>();
string _connectionString = DataSource.GetConnectionString("library2"); // Make possible to define and use different connectionstrings
SqlConnection con = new SqlConnection(_connectionString);
string isbnListString = "";
// Concatenate all isbn-numbers, seperated with comma, into one string
int itemNo = 0;
foreach(string str in isbnList)
{
itemNo++;
isbnListString += str + (itemNo == isbnList.Count ? "')" : "','");
}
SqlCommand cmd = new SqlCommand("SELECT * FROM book WHERE isbn IN ('" + isbnListString, con);
try
{
con.Open();
SqlDataReader dar = cmd.ExecuteReader();
while (dar.Read())
{
BookDTO dto = new BookDTO();
dto.isbnNo = dar["ISBN"] as string;
dto.title = dar["Title"] as string;
/* newBook.location = dar["Location"] as string;
newBook.classificationCode = dar["ClassificationCode"] as string;
newBook.publicationInfo = dar["PublicationInfo"] as string;
newBook.pages = (dar["Pages"] == DBNull.Value) ? 0 : Convert.ToInt32(dar["Pages"].ToString()); */
dtoList.Add(dto);
}
}
catch (Exception er)
{
throw er;
}
finally
{
con.Close();
}
return dtoList;
}
public static List<BookDTO> getAllBooksDAL()
{
List<BookDTO> dtoList = new List<BookDTO>();
//Connect to the database and read all books
string _connectionString = DataSource.GetConnectionString("library2"); // Make possible to define and use different connectionstrings
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM book", con);
try
{
con.Open();
SqlDataReader dar = cmd.ExecuteReader();
while (dar.Read())
{
BookDTO dto = new BookDTO();
dto.isbnNo = dar["ISBN"] as string;
dto.title = dar["Title"] as string;
/* newBook.location = dar["Location"] as string;
newBook.classificationCode = dar["ClassificationCode"] as string;
newBook.publicationInfo = dar["PublicationInfo"] as string;
newBook.pages = (dar["Pages"] == DBNull.Value) ? 0 : Convert.ToInt32(dar["Pages"].ToString()); */
dtoList.Add(dto);
}
}
catch (Exception er)
{
throw er;
}
finally
{
con.Close();
}
return dtoList;
}
public static BookDTO loadBookDAL(string isbn)
{
BookDTO dto = new BookDTO();
string _connectionString = DataSource.GetConnectionString("library2"); // Make possible to define and use different connectionstrings
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM book WHERE isbn = " + isbn, con);
try
{
con.Open();
SqlDataReader dar = cmd.ExecuteReader();
if (dar.Read())
{
dto.title = dar["Title"] as string;
/* dto.location = dar["Location"] as string;
dto.classificationCode = dar["ClassificationCode"] as string;
dto.publicationInfo = dar["PublicationInfo"] as string;
dto.pages = (dar["Pages"] == DBNull.Value) ? 0 : Convert.ToInt32(dar["Pages"].ToString()); */
}
}
catch (Exception er)
{
throw er;
}
finally
{
con.Close();
}
return dto;
}