What is the best way to add/update/delete a lookup table in Microsoft SQL with ASP.NET C#?

I'm working on a local city project and have some questions on efficiently creating relationships between "parks" and "activities" in Microsoft SQL 2000. We are using ASP.NET C# to

I have my two tables "Parks" and "Activities." I have also created a lookup table with the proper relationships set on the primary keys of both "Parks" and "Activities." My lookup table is called "ParksActitivies."

We have about 30 activities that we can associate with each park. An intern is going to be managing the website, and the activities will be evaluated every 6 months.

So far I have created an admin tool that allows you to add/edit/delete each park. Adding a park is simple. The data is new, so I simply allow them to edit the park details, and associate "Activities" dynamically pulled from the database. This was done in a repeater control.

Editing works, but I don't feel that its as efficient as it could be. Saving the main park details is no problem, as I simply call Save() on the park instance that I created. However, to remove the stale records in the lookup table I simply DELETE FROM ParksActitivies WHERE ParkID = @ParkID" and then INSERT a record for each of the checked activities.

For my ID column on the lookup table, I have an incrementing integer value, which after quite a bit of testing has got into the thou开发者_开发百科sands. While this does work, I feel that there has to be a better way to update the lookup table.

Can anyone offer some insight on how I may improve this? I am currently using stored procedures, but I'm not the best at very complex statements.

[ParkID | ParkName | Latitude | Longitude ]  
 1      | Freemont | -116.34  | 35.32  
 2      | Jackson  | -116.78  | 34.2    


[ActivityID | ActivityName | Description ]
1           | Picnic       | Blah
2           | Dancing      | Blah
3           | Water Polo   | Blah

[ID | ParkID | ActivityID ]
 1  | 1      | 2
 2  | 2      | 1
 3  | 2      | 2
 4  | 2      | 3

I would prefer to learn how to do it a more universal way as opposed to using Linq-To-SQL or ADO.NET.

would prefer to learn how to do it a more universal way as opposed to using LINQ2SQL or ADO.NET.

You're obviously using ADO.NET Core :). And that's fine I think you should stick to using Stored procedures and DbCommands and such...

If you were using MSSQL 2008 you'd be able to do this using TableValued parameters and the MERGE statement. since you're using MSSQL 200 (why?) what you'd need to do is the following: 1. Send a comma delimited list of the Activity ids (the new ones) along with the ParkId to your stored proc. The ActivityIds parameter would be a varchar(50) for example.

In your stored proc you can split the ids

The strategy would be something like 1. For the Ids passed in, delete records that don't match

The SQL for that would be

DELETE FROM ParkActivities WHERE ActivityId NOT IN (Some List of Ids) WHERE ParkId = @ParkId

Since your list is a string you can do it like this

EXEC('DELETE FROM ParkActivities WHERE ActivityId NOT IN (' + @ActivityIds + ') AND ParkId = ' + @ParkId)

1. Now you can insert those activities that are not already in the table. The simplest way to do this would be to insert the ParkActivity ids into a temp table. To do that you'll need to split the comma delimited list into individual ids and insert them into a temp table. Once you have the data in the temp table you can insert doing a join.

The is a built-in user defined function in MSSQL 2000 that can do the split and return a Table Variable with each value on a seperate row. http://msdn.microsoft.com/en-us/library/Aa496058

What is wrong with LinqToSQL and ADO.NET? I mean, could you specify your doubts about using those technologies

update

if LinqToSQL is not supported for 2000, you can easily upgrade to free 2008 express. It would be definitely enough for purposes you described.