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?
 
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
linkcube1Author Commented:
You have just made my day Thanks !!!
0
 
käµfm³d 👽Commented:
NP. Glad to help  : )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.