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!
linkcube1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
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

0
käµfm³d 👽Commented:
P.S.

That query was targeted toward SQL Server. I'm not sure which other DBs (if any) support the "IF Exists" syntax.
0
linkcube1Author Commented:
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

0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

käµfm³d 👽Commented:
= )

That was the query!!

Try incorporating it in this manner:
public Book addOrUpdateBook(Book b)
{
    try
    {
        using (SqlConnection con = new SqlConnection("Data Source=localhost\\SqlExpress;Initial Catalog=MMABooks;Integrated Security=True"))
        {
            using (SqlCommand selectCommand = new SqlCommand(string.Empty, con))
            {
                selectCommand.CommandText = "If Exists(SELECT 1 FROM Books WHERE ISBNNumber = @isbn) " +
                                              "UPDATE Books " +
                                              "SET Title = @Title, " +
                                                  "Author= @NewAuthor, " +
                                                  "ISBNNumber = @NewISBNNumber, " +
                                                  "Description = @NewDescription " +
                                              "WHERE ISBNNumber = @ISBNNumber " +
                                            "ELSE INSERT INTO Books (ISBNNumber, Title, Author, Description) " +
                                                 "VALUES (@isbn, @title, @author, @description)";
                                                 
                selectCommand.Parameters.Add(new SqlParameter("@isbn", b.ISBNNumber));
                selectCommand.Parameters.Add(new SqlParameter("@title", b.Title));
                selectCommand.Parameters.Add(new SqlParameter("@author", b.Author));
                selectCommand.Parameters.Add(new SqlParameter("@description", b.Description));
                
                con.Open();
                selectCommand.ExecuteNonQuery();
            }
        }
    }
    catch (SqlException ex)
    {
       // query failed, handle accordingly
    }
    
    return b;
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
linkcube1Author Commented:
You have just made my day Thanks !!!
0
käµfm³d 👽Commented:
NP. Glad to help  : )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.