You must use
SCOPE_IDENTITY[
^] in your SQL to get the latest identity value inserted and return that to the application. I don't know the DatabaseConnect class but it should look something like this:
var bookId = db.executescalar("Insert into Book(BookTitle, BookEdition,BookQuantity,BookStatus)Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1'); select SCOPE_IDENTITY()");
Please note that your code is vulnerable to SQL injection. You should use parameters to pass the values from UI to the database.
Edit
I reused your code to extract first value in the dataset to get around the non-existing executescalar(). If SCOPE_IDENTITY is not working for you can try
@@IDENTITY[
^].
DataSet ds = db.executedataset("Insert into Book(BookTitle, BookEdition,BookQuantity,BookStatus)Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1'); select @@IDENTITY");
int bookId = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
Edit 2 - to solve the real problem
You need to provide values for all not null columns like this:
DataSet ds = db.executedataset("Insert into Book (BookTitle, BookEdition, BookQuantity, BookStatus, BookCategoryId, BookAuthorId, BookPublisherId) Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1','" + comboBox1.SelectedValue + "','" + comboBox2.SelectedValue + "','" + comboBox3.SelectedValue + "'); select SCOPE_IDENTITY()");
int bookId = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
I don't know if your DatabaseConnect class supports parameter, but it's something you should really look into.
Edit 3 - change tables
I wasn't paying much attention to the other tables previously. The issue cannot be resolved - there is a chicken-egg problem. To insert a Book you need BookPublisherId. But to insert into the BookPublisher table you need BookId. You need to change your table structure. Let Book reference Publisher, Author, Category directly:
Book table
BookId indentity
AuthorId FK referencing Author table
PublisherId FK referencing Publisher table
CategoryId FK referencing Category table
... other columns ...
Drop tables BookCategory, BookAuthor, BookPublisher they are useless in their current form. This way the last query will work as expected.