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; }