public static List<Author> GetAuthors()
{
List<Author> authors = new List<Author>();
SqlConnection con = new SqlConnection(GetConnectionString());
string sel = "SELECT * FROM Author";
SqlCommand cmd = new SqlCommand(sel, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Author author;
while (dr.Read()) {
author = new Author();
author.refID = (int)dr["refID"];
author.authorID = (int)dr["authorID"];
author.fname = dr["fname"].ToString().Trim();
author.mname = dr["mname"].ToString().Trim();
author.lname = dr["lname"].ToString().Trim();
author.suffix = dr["suffix"].ToString().Trim();
authors.Add(author);
}
dr.Close();
return authors;
}
public static int UpdateAuthors(Author original_Author, Author author)
{
SqlConnection con = new SqlConnection(GetConnectionString());
string up = "UPDATE Author "
+ "SET fname = @fname, "
+ "mname = @mname, "
+ "lname = @lname, "
+ "suffix = @suffix, "
+ "WHERE refID = @original_refID AND authorID = @original_authorID";
SqlCommand cmd = new SqlCommand(up, con);
cmd.Parameters.AddWithValue("fname", author.fname);
cmd.Parameters.AddWithValue("mname", author.mname);
cmd.Parameters.AddWithValue("lname", author.lname);
cmd.Parameters.AddWithValue("suffix", author.suffix);
cmd.Parameters.AddWithValue("original_refID", original_Author.refID);
cmd.Parameters.AddWithValue("original_authorID", original_Author.authorID);
con.Open();
return cmd.ExecuteNonQuery();//return update count
}
private static void GetAuthors(Book book)
{
//read all data from Author table
SqlConnection authorCon = new SqlConnection(GetConnectionString());
string authorSelect = "SELECT * FROM Author WHERE refID = @refID";
SqlCommand authorCmd = new SqlCommand(authorSelect, authorCon);
authorCmd.Parameters.AddWithValue("refID", book.refID);
authorCon.Open();
SqlDataReader authorDr = authorCmd.ExecuteReader(CommandBehavior.CloseConnection);
List<Author> authors = new List<Author>();
Author author;
while (authorDr.Read()) {
author = new Author();
author.refID = (int)authorDr["refID"];
author.authorID = (int)authorDr["authorID"];
author.fname = authorDr["fname"].ToString().Trim();
author.mname = authorDr["mname"].ToString().Trim();
author.lname = authorDr["lname"].ToString().Trim();
author.suffix = authorDr["suffix"].ToString().Trim();
authors.Add(author);
}
authorDr.Close();
authors.Sort();
book.authors = authors;
}
