Coding Concepts ASP.NET/C#/SQL

[LBJuice]

I'm writing an ASP.net web application in C# for my job which will be used to collaborate on employee termination and new hire tasks.

Currently, I’m using SQL Express to hold the data. The tables are:

Sessions: Holds the hired/terminated employee’s name (Will refer to as ClientName) and a Session ID (Will refer to as SessionID).
Tasks: Has a column for holding the Task (e.g. “Remove VPN Access&#8221 and has a column referencing the SessionID.
NewHireTasks: Table holding all Task’s that must be complete when an employee is hired. (allowing for easy admin update)
TerminatedTasks: Table holding all Task’s that must be complete when an employee is terminated.

I want to now write a method so when you supply it the ClientName and the type of session (hired/terminated) it will:
1.) Add them to the Sessions table.
2.) Get newly generated SessionID. (Primary Key)
3.) Query all the tasks stored in either the NewHireTasks or TerminatedTasks.
4.) Copy them into the Tasks table with the corresponding SessionID.

I’m really looking for the best way to accomplish this, I don’t know if I should do this all in separate stored procedures or combine them into one (If that’s possible)

Example:
1 stored procedure for Adding a new entry to the Sessions table.
1 stored procedure for querying up all tasks in either the NewHireTasks or TerminatedTasks.
…

I have no problem researching anything myself, but I’m just asking any developers, if this is what they were trying to do, what would be the best route, I don't know query language well, so if you suggest doing it in that, please note the statement for me to look into.

 

[mikeblas]

Stored proc or not, you want all of these steps to be a single transaction, for sure.

If you do it in a stored procedure, I think you'll find that the code is a little harder to write or debug. It'll add CPU load to your server, but reduce network load. In the end, I think it'll be a tad bit faster -- but that's just a hunch.

If you do it in C#, the code will be easier to read, modify, and debug. It'll add CPU burden to the database client machine (whatever that is, in your web setup), and cause network load between the client machine and the server. It's not like it'll be painfully slow, but network round-trips aren't free.

Since you're using SQL Express, perhaps we can guess that performance and scalability aren't important concerns for your application -- at least, right now.

Some of the differences between the two approaches are going to come out in the wash. Particularly, step 3 and step 4 aren't two separate statements; they're a single INSERT INTO SELECT FROM. Then, if you code your T-SQL adeptly, you can execute this all as one batch.