public static int AddBookOnly(FullBook fullBook, SqlConnection connection)
{
string queryInsertNewBook = "INSERT INTO Book (Title, Description, Left_In_Stock) " +
"VALUES(@Title, @Description, @LeftInStock); SELECT CAST(scope_identity() AS int)";
SqlCommand command = new SqlCommand(queryInsertNewBook, connection);
command.Parameters.AddWithValue("@Title", fullBook.Title);
command.Parameters.AddWithValue("@Description", fullBook.Description);
command.Parameters.AddWithValue("@LeftInStock", fullBook.LeftInStock);
Int32 bookId = 0;
try
{
connection.Open();
bookId = (Int32)command.ExecuteScalar();
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(bookId);
}
public int AddBook(FullBook fullBook)
{
if (User.Identity.IsAuthenticated && User.Identity.Name.Equals(ConfigurationManager.AppSettings["adminUserName"].ToString()))
{
return(BookRepository.AddBook(fullBook));
}
return(-1);
}
public static bool BookExists(FullBook fullBook, string[] authorsArr, SqlConnection connection)
{
string queryGetExistingBooks = "SELECT Book.Id, Book.Title, STUFF(" +
"(SELECT ', ' + Author.Name AS[text()] " +
"FROM BookAuthorConnect " +
"LEFT JOIN Author " +
"ON Author.Id = BookAuthorConnect.Author_Id " +
"WHERE Book.Id = BookAuthorConnect.Book_Id " +
"ORDER BY Author.Name " +
"FOR XML PATH('')), 1, 2, '') AS Authors " +
"FROM Book WHERE Book.Title = @Title";
SqlCommand command = new SqlCommand(queryGetExistingBooks, connection);
command.Parameters.AddWithValue("@Title", fullBook.Title);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(reader);
reader.Close();
List <Book> listOfBooks = dataTable.AsEnumerable().Select(m => new Book
{
Id = m.Field <int>("Id"),
Title = m.Field <string>("Title"),
Authors = m.Field <string>("Authors").Replace(" ,", ","),
}).ToList();
string sortedAuthors = String.Join(", ", authorsArr);
Console.WriteLine(sortedAuthors);
foreach (Book book in listOfBooks)
{
Console.WriteLine(book.Authors);
if (book.Authors.Equals(sortedAuthors) || book.Authors.Equals(sortedAuthors + " "))
{
return(true);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
}
return(false);
}
public static int AddBook(FullBook fullBook)
{
string[] authorsArr = new string[0];
if (fullBook.Description == null)
{
fullBook.Description = "";
}
if (fullBook.Authors != null)
{
authorsArr = fullBook.Authors.Split(',');
for (int i = 0; i < authorsArr.Length; i++)
{
authorsArr[i] = Regex.Replace(authorsArr[i].Trim(), @"\s+", " ");
}
Array.Sort(authorsArr);
}
using (SqlConnection connection =
new SqlConnection(connectionString))
{
//Check if the book already exist in the database.
if (BookExists(fullBook, authorsArr, connection))
{
return(-1);
}
//Check if mentioned authors already exist in the database.
List <Author> listOfAuthors = GetAuthors(authorsArr, connection);
if (listOfAuthors == null)
{
listOfAuthors = new List <Author>();
}
//Add new authors to the database
foreach (string author in authorsArr)
{
bool inList = false;
foreach (Author authorModel in listOfAuthors)
{
if (authorModel.Name.Equals(author))
{
inList = true;
break;
}
}
if (inList)
{
continue;
}
listOfAuthors.Add(AddAuthor(author, connection));
}
//Insert new book to the database
int bookId = AddBookOnly(fullBook, connection);
//Insert connections between book and authors
bool connectionAdded = false;
if (bookId > 0)
{
connectionAdded = AddBookAuthorConnect(bookId, listOfAuthors, connection);
}
return(bookId);
}
}
public static FullBook GetBook(int id)
{
// Create and open the connection in a using block. This
// ensures that all resources will be closed and disposed
// when the code exits.
string queryString = "SELECT Book.Id, Book.Title, Book.Description, Book.Left_In_Stock, STUFF(" +
"(" +
"SELECT ', ' + Author.Name AS[text()] " +
"FROM BookAuthorConnect " +
"LEFT JOIN Author " +
"ON Author.Id = BookAuthorConnect.Author_Id " +
"WHERE Book.Id = BookAuthorConnect.Book_Id " +
"ORDER BY Author.Name " +
"FOR XML PATH('')" +
"), 1, 2, '') AS Authors " +
"FROM Book WHERE Book.Id=@id";
using (SqlConnection connection =
new SqlConnection(connectionString))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@id", id);
// Open the connection in a try/catch block.
// Create and execute the DataReader, writing the result
// set to the console window.
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
FullBook fullBook = null;
if (reader.Read())
{
fullBook = new FullBook
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Title = reader.GetString(reader.GetOrdinal("Title")),
Authors = reader.GetString(reader.GetOrdinal("Authors")).Replace(" ,", ","),
Description = reader.GetString(reader.GetOrdinal("Description")),
LeftInStock = reader.GetInt32(reader.GetOrdinal("Left_In_Stock")),
};
}
reader.Close();
return(fullBook);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
}
return(null);
}
}
