public Author GetAuthorById(int id)
{
Author author = null;
List<Book> books = new List<Book>();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT AuthorID, Name, BookID, Title, BookType FROM Author INNER JOIN Book ON Author.AuthorID = Book.AuthorID WHERE Author.AuthorID = @id",
Connection))
{
cmd.Parameters.Add(new SqlCeParameter("@id", id));
using (var results = cmd.ExecuteResultSet(ResultSetOptions.Insensitive))
{
while (results.Read())
{
if (author == null)
{
author = new Author
{
AuthorID = (int)results["AuthorID"],
Name = (string)results["Name"]
};
}
else if (author.AuthorID != results.GetInt32(m_authorOrdinals["AuthorID"]))
{
// we're on a new author , so we're done
// (shoudln't happen unless we have more than 1 author with the same name)
break;
}
books.Add(new Book
{
BookID = (int)results["BookID"],
Title = (string)results["Title"],
BookType = (BookType)results["BookType"],
AuthorID = author.AuthorID
});
}
}
}
author.Books = books.ToArray();
return author;
}
public void Insert(Author author)
{
// TODO:
return;
}
public void Update(Author author)
{
// TODO:
return;
}
public void Update(Author author)
{
Store.Update(author);
}
public Author GetAuthorByName(string name)
{
Author author = null;
List<Book> books = new List<Book>();
string sql = string.Format("SELECT * FROM Author where Name = '{0}'", name);
using (SqlCeCommand cmd = new SqlCeCommand(sql,
Connection))
{
using (var results = cmd.ExecuteResultSet(ResultSetOptions.Insensitive))
{
if (results.Read())
{
CheckAuthorOrdinals(results);
author = new Author
{
AuthorID = results.GetInt32(m_authorOrdinals["AuthorID"]),
Name = results.GetString(m_authorOrdinals["Name"])
};
sql = string.Format("SELECT * FROM Book WHERE AuthorID = {0}", author.AuthorID);
using (var bookcmd = new SqlCeCommand(sql, Connection))
using (var bookresults = bookcmd.ExecuteResultSet(ResultSetOptions.Insensitive))
{
CheckOrdinals(bookresults);
while (bookresults.Read())
{
books.Add(new Book
{
BookID = bookresults.GetInt32(m_bookOrdinals["BookID"]),
Title = bookresults.GetString(m_bookOrdinals["Title"]),
BookType = (BookType)bookresults.GetInt32(m_bookOrdinals["BookType"]),
AuthorID = author.AuthorID
});
}
}
}
}
}
author.Books = books.ToArray();
return author;
}
public void Insert(Author author)
{
Store.Insert(author, true);
}
public void Update(Author author)
{
throw new NotImplementedException();
}
public void Insert(Author author)
{
throw new NotImplementedException();
}
private void TestCascadingInsert(List<ITestClass> tests)
{
var testBooks = new Book[]
{
new Book
{
Title = "CSS: The Missing Manual",
BookType = BookType.NonFiction
},
new Book
{
Title = "JavaScript: The Missing Manual",
BookType = BookType.NonFiction
},
new Book
{
Title = "Dreamweaver: The Missing Manual",
BookType = BookType.NonFiction
},
};
// ensures that the entity *and its references* get inserted
Author a = new Author
{
Name = "David McFarland",
Books = testBooks
};
foreach (var t in tests)
{
var initialCount = t.GetBookCount();
t.Insert(a);
var author = t.GetAuthorById(a.AuthorID);
var count = t.GetBookCount();
var diff = count - initialCount;
// diff should == 3
if (diff != 3) Debugger.Break();
}
// create a new author with the same books - the books should *not* get re-inserted - plus one new book
List<Book> newList = new List<Book>(testBooks);
newList.Add(
new Book
{
Title = "My Coauthors Book",
BookType = BookType.NonFiction
}
);
Author a2 = new Author
{
Name = "Test CoAuthor",
Books = newList.ToArray()
};
foreach (var t in tests)
{
var initialCount = t.GetBookCount();
t.Insert(a2);
var author = t.GetAuthorById(a.AuthorID);
var count = t.GetBookCount();
var diff = count - initialCount;
// diff should == 1
if (diff != 1) Debugger.Break();
}
}
private void TestCascadingUpdates(List<ITestClass> tests)
{
var author = new Author
{
Name = "Theodore Geisel"
};
foreach (var t in tests)
{
t.Insert(author);
var book = new Book
{
BookType = BookType.Fiction,
Title = "Fox in Sox"
};
author.Books = new Book[] { book };
t.Update(author);
var existing = t.GetAuthorById(author.AuthorID);
// the book should have been inserted, so it will be at index 0 now
existing.Books[0].Title = "Green Eggs and Ham";
// this should cascade update the book title
t.Update(existing);
existing = t.GetAuthorById(author.AuthorID);
}
}
