asked on

SQl Statement

Ok, I have a books database that servers as a cache. I'm looking for some help. Is it possible to have a select statement to find an IBNNumber in the database and if its not found in any rows add the book strait up into that database . If the isbn number is found, update the other columns in the table which are ( title, description and author) and leave ISBN the same. If this is possible or there is another workaround can someone give me some example code to solve this? Thanks!

kaufmed

Like this (for example)?

IF EXISTS (SELECT 1 FROM books WHERE isbn = @isbn)
    UPDATE books SET pageCount = @pgCount, title = @title WHERE isbn = @isbn
ELSE
    INSERT INTO books (isbn, pageCount, title) VALUES (@isbn, @pgCount, @title)

Open in new window

kaufmed

P.S.

That query was targeted toward SQL Server. I'm not sure which other DBs (if any) support the "IF Exists" syntax.

linkcube1

ASKER

I tried to code what you told me...I'm new to making these sql methods any suggestions?

public Book addOrUpdateBook(Book b)
        {
          try
            {
                SqlConnection con = new SqlConnection("Data Source=localhost\\SqlExpress;Initial Catalog=MMABooks;" +
                   "Integrated Security=True");
                {
                    con.Open();
                   IF Exists("SELECT * FROM Books WHERE WHERE ISBNNumber = '{0}'", b.ISBNNumber, con);
                 SqlCommand selectCommand =
                new SqlCommand(selectStatement, con);    
                "UPDATE Books SET " +
               "Title = @Title, " +
                "Author= @NewAuthor, " +
                "ISBNNumber = @NewISBNNumber, " +
                "Description = @NewDescription " +
                "WHERE ISBNNumber = @ISBNNumber";
              SqlCommand updateCommand =
                new SqlCommand(updateStatement, con);
            updateCommand.Parameters.AddWithValue(
                "@Title", b.Title);
            updateCommand.Parameters.AddWithValue(
                "@Author", b.Author);
            updateCommand.Parameters.AddWithValue(
                "@ISBNNumber", b.ISBNNumber);
            updateCommand.Parameters.AddWithValue(
                "@Description", b.Description);
                }
              else
                {
      string insertStatement =
                "INSERT Books " +
                "(Title, Author, ISBNNumber, Description) " +
                "VALUES (@Title, @Author, @ISBNNumber, @Description)";
            SqlCommand insertCommand =
                new SqlCommand(insertStatement, con);
            insertCommand.Parameters.AddWithValue(
                "@Title", b.Title);
            insertCommand.Parameters.AddWithValue(
                "@Author", b.Author);
            insertCommand.Parameters.AddWithValue(
                "@ISBNNumber", b.ISBNNumber);
            insertCommand.Parameters.AddWithValue(
                "@Description", b.Description);

            }
              return b;
          }
}

Open in new window

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

linkcube1

ASKER

You have just made my day Thanks !!!

kaufmed

NP. Glad to help : )