public int CheckAvailability(string bookISBN, int bookCopy) //Kontrollerar om boken är utlånad
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("checkAvailability", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@BookISBN", bookISBN);
            cmd.Parameters.AddWithValue("@BookCopy", bookCopy);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();

            da.Fill(dt);
            conn.Close();
            foreach (DataRow row in dt.Rows)
            {
                int count = (int)row["nrLoans"];
                return(count);
            }

            return(0);
        }
        public DataTable AllAuthors() //Returnera alla författare
        {
            Stopwatch sw = new Stopwatch();

            sw.Start();



            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand("allAuthors", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();
            DataSet        ds = new DataSet();

            da.Fill(dt);
            conn.Close();

            sw.Stop();

            System.Console.WriteLine("Elapsed={0}", sw.Elapsed);
            return(dt);
        }
        public int GetCopyNbr(string isbn) //Hämta books exemplarsnummer
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand("getCopyNbr", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@ISBN", isbn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();

            da.Fill(dt);
            conn.Close();
            foreach (DataRow row in dt.Rows)
            {
                int count = (int)row["nrCopy"];
                return(count + 1);
            }

            return(0);
        }
        public string ReturnAuthorName(int authorId) //Returnera författarens namn
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand("returnAuthorName", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@AuthorID", authorId);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();

            da.Fill(dt);
            conn.Close();
            foreach (DataRow row in dt.Rows)
            {
                string name = row["AuthorName"].ToString();
                return(name);
            }

            return(null);
        }
        public void DeleteUser(string userID) //Radera en användare
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("deleteUser", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@UserID", userID);

            cmd.ExecuteNonQuery();
            MessageBox.Show("Användaren är borttagen");
            conn.Close();
        }
        public void AddAuthor(int authorId, string authorName) //Lägg till författare
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand("addAuthor", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@AuthorID", authorId);
            cmd.Parameters.AddWithValue("@AuthorName", authorName);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public void DeleteAuthor(int authorID) //Radera en författare
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("deleteAuthor", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@AuthorID", authorID);

            cmd.ExecuteNonQuery();
            MessageBox.Show("Författaren är borttagen");
            conn.Close();
        }
        public void DeleteBook(string isbn, int copyNbr) //Radera en bok
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("deleteBook", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@ISBN", isbn);
            cmd.Parameters.AddWithValue("@CopyNbr", copyNbr);

            cmd.ExecuteNonQuery();
            MessageBox.Show("Boken är raderad");
            conn.Close();
        }
        public void EndLoan(string bookISNB, int bookCopy) //Avsluta ett lån
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("endLoan", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@ISBN", bookISNB);
            cmd.Parameters.AddWithValue("@CopyNbr", bookCopy);

            cmd.ExecuteNonQuery();
            MessageBox.Show("Lånet är avslutat");
            conn.Close();
        }
        public void EditAuthor(int authorId, string authorName) //Uppdatera en författare
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("editAuthor", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@AuthorName", authorName);
            cmd.Parameters.AddWithValue("@AuthorId", authorId);

            cmd.ExecuteNonQuery();
            MessageBox.Show("Namnet är ändrat");
            conn.Close();
        }
        public DataTable AllUsers() //Returnera alla användare
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand("allUsers", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();
            DataSet        ds = new DataSet();

            da.Fill(dt);
            conn.Close();
            return(dt);
        }
        public void AddNewAuthor(Author a) //Lägg till ny författare
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand("addNewAuthor", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@AuthorID", a.AuthorId);
            cmd.Parameters.AddWithValue("@AuthorName", a.AuthorName);

            cmd.ExecuteNonQuery();
            conn.Close();
            MessageBox.Show("Författaren är registrerad");
        }
        public void ChangePassword(string id, string newPw) //Byt lösenord
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();

            SqlCommand cmd = new SqlCommand("changePassword", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@UserID", id);
            cmd.Parameters.AddWithValue("@UserPassword", newPw);

            cmd.ExecuteNonQuery();
            MessageBox.Show("Lösenordet är ändrat");
            conn.Close();
        }
        public DataTable SearchTitle(string searchTitle) //Hämta lista med böcker utifrån Titel
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand(
                    "searchTitle", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@searchTitle", searchTitle);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();

            da.Fill(dt);
            conn.Close();
            return(dt);
        }
        public DataTable GetUserInfo(string userId) //Hämta info om användare
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("userID", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@UserID", userId);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();
            DataSet        ds = new DataSet();

            da.Fill(dt);
            conn.Close();
            return(dt);
        }
        public void EditUser(User u) //Uppdatera en användare
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("editUser", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@UserFirstName", u.UserFirstName);
            cmd.Parameters.AddWithValue("@UserLastName", u.UserLastName);
            cmd.Parameters.AddWithValue("@UserDebt", u.Debt);
            cmd.Parameters.AddWithValue("@UserPassword", u.UserPassword);
            cmd.Parameters.AddWithValue("@UserAdmin", u.Admin);
            cmd.Parameters.AddWithValue("@UserID", u.UserId);

            cmd.ExecuteNonQuery();
            MessageBox.Show("Uppgifterna är ändrade");
            conn.Close();
        }
        public void AddNewBook(Book b) //Lägg till ny bok
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand(
                    "addNewBook", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@Title", b.Title);
            cmd.Parameters.AddWithValue("@AuthorID", b.Author);
            cmd.Parameters.AddWithValue("@ISBN", b.Isbn);
            cmd.Parameters.AddWithValue("@CopyNbr", b.CopyNumber);

            cmd.ExecuteNonQuery();
            conn.Close();
            MessageBox.Show("Boken är registrerad");
        }
        public void AddNewLoan(Loan loan) //Registrera ett nytt Lån
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand("addNewLoan", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@BookISBN", loan.BookIsbn);
            cmd.Parameters.AddWithValue("@BookCopy", loan.BookCopy);
            cmd.Parameters.AddWithValue("@UserID", loan.UserId);
            cmd.Parameters.AddWithValue("@LoanDate", loan.LoanDate);
            cmd.Parameters.AddWithValue("@ExpiryDate", loan.ExpiryDate);

            cmd.ExecuteNonQuery();
            conn.Close();
            MessageBox.Show("Lånet är registrerat");
        }
        public int GetNextAuthorId() //Hämta nästa författarID
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("getNextAuthorId", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();

            da.Fill(dt);
            conn.Close();
            foreach (DataRow row in dt.Rows)
            {
                int id = (int)row["maxID"];
                return(id + 1);
            }

            return(0);
        }
        public void AddNewUser(User u) //Lägg till ny användare
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand(
                    "addNewUser", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@UserFirstName", u.UserFirstName);
            cmd.Parameters.AddWithValue("@UserLastName", u.UserLastName);
            cmd.Parameters.AddWithValue("@UserDebt", u.Debt);
            cmd.Parameters.AddWithValue("@UserPassword", u.UserPassword);
            cmd.Parameters.AddWithValue("@UserAdmin", u.Admin);
            cmd.Parameters.AddWithValue("@UserID", u.UserId);

            cmd.ExecuteNonQuery();
            conn.Close();
            MessageBox.Show("Användaren är registrerad");
        }
        public DataSet Login(string userId, string userPassword) //Kontrollera loginuppgifter
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    =
                new SqlCommand(
                    "login", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@UserID", userId);
            cmd.Parameters.AddWithValue("@UserPassword", userPassword);

            SqlDataAdapter adapt = new SqlDataAdapter(cmd);



            DataSet ds = new DataSet();

            adapt.Fill(ds);
            return(ds);
        }
        public int UserNbrOfLoans(string userID) //Returnera antal lån som en användare har
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("userNbrOfLoans", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@UserID", userID);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();

            da.Fill(dt);
            conn.Close();
            foreach (DataRow row in dt.Rows)
            {
                int count = (int)row["nrLoans"];
                return(count);
            }

            return(0);
        }
        public int AuthorNbrOfBooks(int authorID) //Returnera antal böcker som en författare skrivit
        {
            BibliotechConnector dbConn = new BibliotechConnector();
            SqlConnection       conn   = dbConn.Connect();
            SqlCommand          cmd    = new SqlCommand("authorNbrOfLoans", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            cmd.Parameters.AddWithValue("@AuthorID", authorID);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable      dt = new DataTable();

            da.Fill(dt);
            conn.Close();
            foreach (DataRow row in dt.Rows)
            {
                int count = (int)row["nrBooks"];
                return(count);
            }

            return(0);
        }