Inserting a record into a Multi-table database using linq to sql in c# [modified]

[C# Programming]

I believe I'm going about this the wrong way and was wondering if there is an easier way to do this. It just appears to be a lot of functions to call for what I would assume would be a simple process.

Here is a break down of what I'm trying to accomplish:
see db diagram (http://img682.imageshack.us/img682/7933/booksdb.jpg)

I want to simply make an Insert_Update functionality when the save button is pressed on a linq to sql c# winform application. I have the insert working per say but I don't think it is the correct way to do it so I'm wondering if there is a better method than doing all the checks separately and calling db.submit() multiple times.



private void saveToolStripButton_Click(object sender, EventArgs e)
{
/*1) Check to see if the author exit
* Method: CheckAuthor(String FirstName, String LastName)
* payload: Use bool to see if Firstname = Author_table.Firstname & LastName = Author_table.Lastname
* IF the author exist
* -- just call CheckBookExit_ADD(String Title)
* ELSE
* -- Add the author to table
* -- CheckBookExit_ADD (String Title)
*2) Check to see if the book exit and add accordingly
* Method: CheckBookExit_ADD (String Title,String Price,String Keywords,Bool Retired)
* Payload: use bool to see if the title exist in the databasebase Book_table.Title = title
* IF the book exist
* -- Add item to BookNumber table.
* AddBookNumber(int BookID)
* -- Check to see if there is instance in the BookAuthor Linker Table and add accordingly
* CheckBookAuthor(int AuthorID, int BookID)
* ELSE
* -- Add the book's title,price,keywords,retired to the book table
* -- Add the book number by calling AddBookNumber() don't pass it a value so you get the next book id
* -- Check to see if there is an instance in BookAuthor Linker and add if needed
* CheckBookAuthor(int AuthorID, int BookID)
*3) Add the booknumber and Surrogatenumber to the booknumber table
* Method: AddBookNumber( int BookID)
* Payload: add bookID to database and add the surrogatenumber's max value + 1
*
* 4) Check to see if the authorID and BookId is in the linker table
* Method: CheckBookAuthor(int AuthorID, int BookID)
* Payload: use bool to see if the values are in the table
* IF
* they are do nothing
* ELSE
* add the values to the table
*
*/
CheckAuthor(authorFirstTextBox.Text, authorLastTextBox.Text);

}

//---------------------------BOOK INSERT METHODS ---------------------------------------
public void CheckAuthor(String FirstName, String LastName)
{
/*
* payload: Use bool to see if Firstname = Author_table.Firstname & LastName = Author_table.Lastname
* IF the author exist
* -- just call CheckBookExit_ADD()
* ELSE
* -- Add the author to table
* -- CheckBookExit_ADD ()
*
*/
using (var db = new momdbDataContext())
{
try
{
StatusLabel.Text = "Adding Item to database";
List authorList = new List();
bool exist = db.Authors.Any(a => a.AuthorFirst == FirstName && a.AuthorLast == LastName);

if (exist)
CheckBookExist_ADD();

else
{
Author authorInsert = new Author { AuthorFirst = authorFirstTextBox.Text, AuthorLast = authorLastTextBox.Text };
authorList.Add(authorInsert);
db.Authors.InsertAllOnSubmit(authorList);
db.SubmitChanges();
Debug.WriteLine(db.Log);

CheckBookExist_ADD();

}
}
catch (Exception oops)
{
MessageBox.Show(oops.ToString());
}

}

}

public void CheckBookExist_ADD()
{
/*Payload: use bool to see if the title exist in the databasebase Book_table.Title = title
* IF the book exist
* -- Add item to BookNumber table.
* AddBookNumber(int BookID)
* -- add instance in the BookAuthor Linker Table and add accordingly
* CheckBookAuthor(int AuthorID, int BookID)
* ELSE
* -- Add the book's title,price,keywords,retired to the book table
* -- Add the book number by calling AddBookNumber() don't pass it a value so you get the next book id
* -- Check to see if there is an instance in BookAuthor Linker and add if needed
* CheckBookAuthor(int AuthorID, int BookID)
*/

using (var db = new momdbDataContext())
{
var originalBook = db.Books.Where(t => t.Title == this.titleTextBox.ToString()).FirstOrDefault();

List bookList = new List();
bool exist = db.Books.Any(b => b.Title == this.titleTextBox.Text);
if (exist)
{
AddBookNumber(originalBook.ID);
var AuthorID = (from a in db.Authors
select a.ID).Max(); // get the last author id inserted
CheckBookAuthor(originalBook.ID, AuthorID);
}
else
{

Book bookInsert = new Book
{
Title = this.titleTextBox.Text,
Keywords = keywordsTextBox.Text,
Price = Convert.ToDecimal(priceTextBox.Text),
Retired = retiredCheckBox.Checked
};
bookList.Add(bookInsert);
db.Books.InsertAllOnSubmit(bookList);
db.SubmitChanges();
var bookID = (from b in db.Books
select b.ID).Max(); // get the last bookid inserted
var AuthorID = (from a in db.Authors
select a.ID).Max(); // get the last author id inserted
CheckBookAuthor(bookID, AuthorID);


}
}

}
public void AddBookNumber(int ID)
{
//Payload: add bookID to database and add the surrogatenumber's max value + 1

using (var db = new momdbDataContext())
{
var max_Surrogate = (from sn in db.BookNumbers
select sn.SurrogateNumber).Max();
List bookNumberList = new List();
BookNumber booknumberInsert = new BookNumber { BookID = ID, SurrogateNumber = max_Surrogate + 1 };
bookNumberList.Add(booknumberInsert);
db.BookNumbers.InsertAllOnSubmit(bookNumberList);
db.SubmitChanges();
}
}

public void CheckBookAuthor(int bid, int aid)
{
using (var db = new momdbDataContext())
{
List bookAuthorList = new List();
bool exist = db.BookAuthors.Any(a => a.BookID == bid && a.AuthorID == aid);
if (exist)
return;
else
{
BookAuthor bookauthorInsert = new BookAuthor { AuthorID = aid, BookID = bid };
bookAuthorList.Add(bookauthorInsert);
db.BookAuthors.InsertAllOnSubmit(bookAuthorList);
db.SubmitChanges();

}
StatusLabel.Text = "Add Complete";
LoadData();
}
}

Please keep in mind that I'm learning this stuff so please don't bash me too hard. Thanks... http://www.barakasoft.com/script/Forums/Images/smiley_biggrin.gif

modified on Wednesday, February 3, 2010 4:18 PM