public static List<Book> GetBookReferences()
        {
            List<Book> books = new List<Book>();

            SqlConnection bookCon = new SqlConnection(GetConnectionString());
            string bookSelect = "SELECT * FROM Book";
            SqlCommand bookCmd = new SqlCommand(bookSelect, bookCon);
            bookCon.Open();
            SqlDataReader bookDr = bookCmd.ExecuteReader(CommandBehavior.CloseConnection);

            Book book;

            //read data from the book table
            while (bookDr.Read()) {
                book = new Book();

                book.refID = (int)bookDr["refID"];

                book.PaperID = (int)bookDr["PaperID"];

                try{
                    book.edition = (int)bookDr["edition"];
                }
                catch (InvalidCastException) {
                    book.edition = 0;
                }

                try {
                    book.volume = (int)bookDr["volume"];
                }
                catch (InvalidCastException) {
                    book.volume = 0;
                }

                //book.anonymous = (bool)bookDr["anonymous"];
                //book.corporateAuthor = (bool)bookDr["corporateAuthor"];
                try {
                    book.translation = (bool)bookDr["translation"];
                }
                catch (InvalidCastException) {
                    book.translation = false;
                }

                book.publicationDate = (DateTime)bookDr["publicationDate"];

                book.title = bookDr["title"].ToString().Trim();
                book.subtitle = bookDr["subtitle"].ToString().Trim();
                book.city = bookDr["city"].ToString().Trim();
                book.state = bookDr["state"].ToString().Trim();
                book.publisher = bookDr["publisher"].ToString().Trim();
                book.URL = bookDr["URL"].ToString().Trim();
                book.DOI = bookDr["DOI"].ToString().Trim();

                GetAuthors(book);
                GetEditors(book);

                books.Add(book);
            }
            bookDr.Close();

            books.Sort();
            return books;
        }
        private static void GetEditors(Book book)
        {
            //read all data from editors table
            SqlConnection con = new SqlConnection(GetConnectionString());
            string sel = "SELECT * FROM Editor WHERE refID = @refID";
            SqlCommand cmd = new SqlCommand(sel, con);
            cmd.Parameters.AddWithValue("refID", book.refID);
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            List<Editor> editors = new List<Editor>();

            Editor editor;
            while (dr.Read()) {
                editor = new Editor();

                editor.refID = (int)dr["refID"];
                editor.editorID = (int)dr["editorID"];
                editor.fname = dr["fname"].ToString().Trim();
                editor.mname = dr["mname"].ToString().Trim();
                editor.lname = dr["lname"].ToString().Trim();
                editor.suffix = dr["suffix"].ToString().Trim();

                editors.Add(editor);
            }

            dr.Close();
            editors.Sort();
            book.editors = editors;
        }
        public static int UpdateBookReferences(Book original_Book, Book book)
        {
            SqlConnection con = new SqlConnection(GetConnectionString());
            string up = "UPDATE Book "
            + "SET edition = @edition, "
            + "volume = @volume, "
            + "anonymous = @anonymous, "
            + "corporateAuthor = @corporateAuthor, "
            + "translation = @translation, "
            + "publicationDate = @publicationDate, "
            + "title = @title, "
            + "subtitle = @subtitle, "
            + "city = @city, "
            + "state = @state, "
            + "publisher = @publisher, "
            + "URL = @URL, "
            + "DOI = @DOI "
            + "WHERE refID = @original_refID";

            SqlCommand cmd = new SqlCommand(up, con);
            cmd.Parameters.AddWithValue("edition", book.edition);
            cmd.Parameters.AddWithValue("volume", book.volume);
            //cmd.Parameters.AddWithValue("anonymous", book.anonymous);
            //cmd.Parameters.AddWithValue("corporateAuthor", book.corporateAuthor);
            cmd.Parameters.AddWithValue("translation", book.translation);
            cmd.Parameters.AddWithValue("publicationDate", book.publicationDate);
            cmd.Parameters.AddWithValue("title", book.title);
            cmd.Parameters.AddWithValue("subtitle", book.subtitle);
            cmd.Parameters.AddWithValue("city", book.city);
            cmd.Parameters.AddWithValue("state", book.state);
            cmd.Parameters.AddWithValue("publisher", book.publisher);
            cmd.Parameters.AddWithValue("URL", book.URL);
            cmd.Parameters.AddWithValue("DOI", book.DOI);
            cmd.Parameters.AddWithValue("original_refID", original_Book.refID);

            for (int i = 0; i < book.authors.Count; i++)
                UpdateAuthors(original_Book.authors[i], book.authors[i]);

                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;
        }
 private static String getBookLname(Book book)
 {
     String str1 = "";
     if (book.authors.Count > 0) { str1 = book.authors[0].lname; }
     else if (book.editors.Count > 0) { str1 = book.editors[0].lname; }
     return str1;
 }