ASP.NET and Stored procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ASP.NET and Stored procedures

Author

Topic

GS1
Starting Member

27 Posts

Posted - 2003-07-29 : 06:16:52

Hello

I am currently designing an ASP.Net app and have a stored procedure question. In my application, users enter a meeting, along with attendees for the meeting. As A meeting can have multiple attendees, the back-end database consists of 3 tables:

Activities

Activity_ID (autoincremented int)
Date
Cost
Notes

Attendees

Attendee_ID (int)
First Name
Last Name
Company ID
Company Name

NB - all attendee data comes from an external system, so the attendee_ID is presupplied

Junction
Activity_ID
Attendee_ID

I am wondering how best to insert data into the tables using a stored procedure. The only way that I can think of is to:

1. Enter the data into the Activity table, returning the ID of the new activity

2. Loop through the Rows of attendees in the asp.net page itself, firing an insert statement for each user (if not exists) to add them to the Attendees table.

3. Loop through the Rows of attendees in the asp.net page for a second time, firing an insert statement adding the returned value from step 1 and the id of the current attendee into the Junction table.

This seems like a hell of a lot of statements needing to be sent from the asp page to the db.

Could someone let me know if there is a more elegant solution?

Also, a minor design question - would there be any advantage in putting the company names in a seperate table with a 1-1 relationship to Attendees? I couldn't see any reason to, but maybe there is?

Thanks!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-29 : 06:33:55

Attendee_ID (int)
First Name
Last Name
Company ID
Company Name

I presume you also have a table Company which might look like

Company ID
Name
Address

...therefore there is absolutely no need to store the name of the Company in the Attendee table. Remember what happens when the name of the company changes etc..

Also, I don't think you need to loop through the Attendee list twice. You could create an insert trigger to fire when Attendee ID does not exist and then insert the new record within the same loop.

---------------
Shadow to Light

GS1
Starting Member

27 Posts

Posted - 2003-07-29 : 07:06:03

Hi Amethystium

In fact, as all attendee data comes from an external system (a third party sql database), the company details are not held at all. I have decided to keep a company ID as well as the name for possible use in the .aspx pages of my web app.

I think I follow you about using a trigger - I will look into this further.

Thanks!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-29 : 09:29:36

quote:

In fact, as all attendee data comes from an external system (a third party sql database), the company details are not held at all. I have decided to keep a company ID as well as the name for possible use in the .aspx pages of my web app.

I still think you should separate the Company name from your Attendees table. Create a Company table and a trigger inserts a new Company if it not found in your table. Like I said, if a company decided to change it's name then you will have to update all records in the Attendee table to reflect the new name. Having the Company table will allow you to run one simple UPDATE statement to do the same job.

Hope this helps.

---------------
Shadow to Light

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-29 : 18:47:50

You could minimize round trips from .NET to SQL by passing all the attendees in a comma delimited string.

The SP can split the string elegantly using a Tally table. There should be no reason to do any Looping or otherwise.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources