Saving data and retrieving the id of the data at the same time

Question

I just started sql and have a problem that I have been dealing with for days.

I have two data tables. The first is the book, the second is the author. The fields of the book table:

Book

Book ID (PK)

AuthorID

BookName

...

Author

AuthorID (PK)

Author Name

What I want to do here is to create the database for the author while the book is registered.

1-Book Name, Author name will be written in the textboxes in the Windows form application and the save button will be clicked. The author name will first be saved in the 'author' table and the id as the automatic pk key will be created.

2-The 'author name' written on the textbox and the corresponding id in the author table will be taken.

3- Finally, the title of the book and the author id will be recorded in the "book" table.

To give an example:

To textboxes in the Windows form application:

Book Title: IT

Author Name: Stephen King

Hit the save button.

Author table

AuthorID: 97 (automatically assigned)

Author Name: Stephen King

Book table

Book ID: 11

AuthorID: 97

Book Name: IT

In short, with a single click, there is both an author and a book record and a database at the same time.

I have tried many methods, but I cannot store the Author ID I took.

string sqlcom1 = "INSERT INTO author (authorName) VALUES(@Pauthor)";
            commandObject = new SqlCommand(sqlcom1, connectionObject );
            commandObject.Parameters.AddWithValue("@Pauthor", textBoxauthorName.Text);

string sqlcom2 = "SELECT authorID FROM author WHERE authorName='" + textBoxauthorName.Text + "'");
            commandObject = new SqlCommand(sqlcom2 , connectionObject );
            

string sqlcom3 = "INSERT INTO book (bookName,authorID) VALUES(@PbookName,@PauthorID)";
            commandObject = new SqlCommand(sqlcom3, connectionObject);
            commandObject.Parameters.AddWithValue("@PbookName", textBoxbookName.Text);
            commandObject.Parameters.AddWithValue("@PauthorID", sqlcom2);

connectionObject.Open();
commandObject.ExecuteNonQuery();
connectionObject.Close();

Error: 'Conversion failed when converting the nvarchar value 'SELECT authorID FROM author WHERE authorName='author30'' to data type int.'

author30 --> The value I entered into the textbox for the author name.

Note: I can see that by registering to the author table, names are automatically assigned ids. Registration to the author table is taking place.

The author ID is associated with the author ID in the book table.

Answer 1

SOLUTION

            connectionObject.Open();
            string sqlcom1 = "INSERT INTO author (authorName) VALUES(@Pauthor); SELECT SCOPE_IDENTITY()";
            commandObject= new SqlCommand(sqlcom1, connectionObject);
            commandObject.Parameters.AddWithValue("@Pauthor", textBoxauthorName.Text);
            string SolutionVariable = commandObject.ExecuteScalar().ToString();
            

            string sqlcom3 = "INSERT INTO book (bookName,authorID) VALUES(@PbookName,@PauthorID)";
            commandObject = new SqlCommand(sqlcom3, connectionObject);
            commandObject.Parameters.AddWithValue("@PbookName", textBoxbookName.Text);
            commandObject.Parameters.AddWithValue("@PauthorID", SolutionVariable);

            
            commandObject.ExecuteNonQuery();
            connectionObject.Close();

Answer 2

SQLServer supports a clause called OUTPUT for (amongst other things) INSERT statements, which lets you retrieve a set of rows for all the inserted values. This is more useful than selecting SCOPE_IDENTITY because it allows you to retrieve multiple values; you can insert N rows, and retrieve O columns per inserted row, for example

INSERT INTO T(CreateDate,A,B,C) --imagine that T has a PK int autonumber
OUTPUT inserted.PK,inserted.CreateDate
VALUES (GetUtcDate(),'A','BB','CCC'),(GetUtcDate(),'A2','BB2','CCC2')

ExecuteReader()ing this (rather than NonQuery) will respond with a 2x2 resultset of the inserted values - the PK and the date calc'd by the DB. You can of course use it in 1x1 mode also (and executescalar it) but generally with SQLs we should aim to work in sets of data

Answer 3

You need to use SCOPE_IDENTITY() to get the inserted ID, and pass it through to the other table. Do this in a single command.

You have a number of other issues with your code:

• SQL injection risk
• You are not disposing the connection correctly
• You should use the correct length for varchar parameters

I will demostrate how to do this properly

const string query = @"
INSERT INTO author (authorName)
VALUES(@Pauthor);

INSERT INTO book (bookName, authorID)
VALUES(@PbookName, SCOPE_IDENTITY());
";

using(var connectionObject = new SqlConnection(connString))
using(var commandObject = new SqlCommand(query, connectionObject))
{
    commandObject.Parameters.Add("@Pauthor", SqlDbType.VarChar, 100).Value = textBoxauthorName.Text;
    commandObject.Parameters.Add("@PbookName", SqlDbType.VarChar, 100).Value = textBoxbookName.Text;
    connectionObject.Open();
    commandObject.ExecuteNonQuery();
}

If you have multiple rows then you need to use the OUTPUT clause. Again, you can do this in a single batch command, just send it to a table variable first.