Writing to a relationship based table

[DaveRook]

Hi

I think I'm just after advice on best practice with this!

I have a database which has 2 tables. The first table has ID, Name, Address and the second table has ID and JobType.

Table 02 can list multiple types of JobType so we could have

Table01:
1. D Smith. Address01
2. S.Jones. Address02

Table02
1. Accounts
1. Sales Ledger
1. Chartered Accountant
2. IT
2. Web Design
2. Graphic Design
2. 1st line support

A join gets them together and all works perfectly.

However, the content will be updated when some one fills in a web form. Do get the form to send results to both tables, I assume I have to open the database twice or can I open it once and open each table one at a time? I know I could practice this and not ask the question but I am interested to know what is the most efficient way.

I would also need to know what ID to use. So when I populate Table02 it uses the correct ID from Table01. At the moment Table01 ID field is incremented automatically. Would you suggest I always assign the ID myself, or would I need to query the database, find out what ID number is currently in use and then increment it by 1 and assign the new number to an ID variable which then populates the Table's Field? In which case, am I opening the database again or trying to perform all 2/3 stages in one go?

Thanks

Dave

[Frinavale]

You should always close the connection to the database when you're finished with it. Do not open a database connection and leave it open after you have sent the page to the client (aka the web browser). If the user walks away or closes the browser...then there is a very good chance that that database connection will remain open. There are some major problems with this and it could cripple your server resources and/or your application. Look into connection pools and database connections to get a better understanding of what I'm getting at here.

I think you should also look into using stored procedures. If you worried about how many times you're opening and closing connections to the database to do your processing, these can really help you. This has nothing to do with ASP.NET; you do this in your database.

To give you more help on the topic of assigning unique IDs, it would really help if you mentioned what type of database you were using....SQL Server? MySql? Oracle???

-Frinny

[Curtis Rutland]

Ok, I think you basically have two questions:

1) Where should you generate the auto-id at? DB or client?

2) If DB, how do I get the generated ID to use in a related table?

You do want to allow the DB to generate your auto id's for you. I've run into a similar situation, and tried the idea you had (finding the current max id, incrementing it and using that) and I ran into some issues, namely simultaneous updates creating the same id number.

Then I discovered the SCOPE_IDENTITY( ) system function of T-SQL. This is assuming that you are using MS SQL Server, but I believe that most RDBMs have similar capabilities.

You can write a stored procedure to update table 1 and return the SCOPE_IDENTITY( ) value, and use that value to update table 2.

As to opening and closing connections, it depends on how you are doing it. For example, if you are using LINQ, it will automatically manage connection states for you. If you are doing it manually, I would suggest opening the connection, running both update queries, then closing it. No need to open/close/open/close when you're performing sequential operations. Don't leave it hanging open though. Close it when you're done with the current set of operations.

Hope that helps.

[DaveRook]

Hi Frinavale and insertAlias

Thank you both for your suggestion.. I will go and read up on Scope_Identity

I don't understand how I could use stored procedures in this instance as the informtion to be written on the fly as it depends on what the user enters as to which fields in the database do/don't get updated.

May be I have mis-understood stored procedures and will also re look into this.

I know LINQ is a fairly new langauge but if it manages connection states automatically would it not be better for me (some one still learning) to continue with SQL as part of my education to ensure I understand the ado.net procedure?

Thank you both, as always, problem solved :)