Unanswered: How do I properly update related tables with a tableadapter?
I am a software engineer over in Japan, and I have been designing an in-house tool for my company. It makes heavy use of the Microsoft Access 2003 format database (mdb).
I taught myself databases in a day, so I am obviously not experienced. Thus, I want to ask a few questions.
First, as the, title states, I want to know how to properly use the tableadapter to update my database.
Right now, I have relations set up like this:
Category -> Subcategory
the "->" is a child relation indicator, and the "," means they are on the same level.
If they are on separate lines, they are not related to each other.
So, what I originally tried to do was update insertions to the Category,Template,People, and Places tables first. I used the Update method of the table adapter, selecting Added rows as what I am updating.
Like so: TableAdapter.Update(Category.Select(AddedRows)).
After I was done updating the insertions to the database for Category,Template,People, and places, I updated the insertions to the Subcategory and ItemTemplate tables.
After that, I updated insertions to the CompanyFromTemplate and TypeTemplate tables.
For deletions and modifications, I updated the children first.
So, I updated the CompanyFromTemplate and TypeTemplate tables first, then ItemTemplate and Subcategory, and then the Category, Template, People, and Places tables.
I thought this would work fine, and it seemed to for everything but insertions.
Using only tableadapters, I received an OleDbException telling me that I am trying to add a subcategory before a category has been added. The exception would only occur if I added a category then a subcategory to that category, then updated. Furthermore, it would not always occur even if I did do that. It was random, as far as I could tell. I had a very hard time reproducing it.
I fixed the error (I think... Perhaps it will pop up again due to its random nature) by switching to using OleDbCommand objects and manually doing ExecuteNonQuery calls against the database with what I want to insert.
I was still able to use the tableadapter's update for deleted datarows as well as modified ones.
So, from what I have given, is there something I am doing that is wrong? Is the tableadapter.update method buggy sometimes, or should it always work properly if the programmer uses it properly?
If I can, I would like to use the table adapter only. It saves a lot of code. Using the oledbcommand objects and executenonquery for inserts added at least 100 lines of code. All my methods are pretty short and to the point, and now I have some 60 line methods with all this oledb nonsense, when I could just be using one line to call a tableadapter.update instead.
I had another, unrelated question as well.
Are there any tricks for using reducing the load and save time of the program when using a database?
Right now, I just fill the datatables using the tableadapter, then I set my various various listboxes and datagridviews to use them as a datasource.
I then just let the user do any operations (editing, adding, deleting), and they press OK or Cancel to commit the changes.
To check for changes, I created my own method "HasChanges", which returns a boolean, which just loops through a collection of datarows given to it and checks the RowState. If the rowstate is not "Unchanged", it automatically returns true. If it makes it all the way through the loop, it returns false.
I don't really notice any big delays anywhere; however, I do notice a slight delay when rejecting changes (user hits the Cancel button), as well as when I edit a subcategory.
For subcategories, a form pops up when you want to edit them. They have a lot of fields, and many are dependent on other datatables. So I set many datasources and such. The delay for the form opening up is noticeable. About 1.5 seconds.
Oh, and also, when I delete a template, I delete all the itemtemplates associated with it, and their templates... Is this needed, if I have them set up as a linked relation (1->infinite and infinite-infinite)? Or, if they are linked, do the children automatically get deleted if i call a .Delete on the parent?
Is this pretty optimal?
I would love to use some tricks provided by the DB gurus here, if you guys have any for me .
You won't find many that have used ADO.NET in conjunction with Access. I have used both loads but never, ever together. It's a bit like strapping a 3 litre engine onto a 2CV.
I would use SQL Server 2005\2008 express for your RDBMS if you are using .NET for your front end. Actually, I would use SQL Server as an RDBMS over Access in every circumstance.
In short - I have no idea as to your main question.
As to your other questions - I would concentrate on one issue at a time and launch a new thread for each distinct question. This is based on experience - you will get a better response than to essays asking multiple questions.
Sadly, I have to use Microsoft Access Database.
It is what my company required to use as the local database for the program.
I have no say in the matter.
So using OleDbCommand on a access database file can cause errors? Too fast for it?
Well, I guess I will make another thread for my few other questions, if I must; however, I would rather someone just read this thread, since it states everything in plain English.
I can't really afford to take too much time. I want to have the project finished in a day or two.
It technically works, and it is quite fast; however, I do not like that I am mixing in oledb with tableadapters, and I dislike even a slight delay anywhere. I will use the form loading trick someone mentioned in a different post to help alleviate any delays; however, during a cancel, they will still be noticable.
If I never get any replies, I guess I will make a separate thread :P. I hope I don't have to, though!