• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Best Method for Saving Data from a ASP.NET Web Form (not MVC) to Multiple Related SQL Server Tables

Hi Experts,

I'm learning, so please bear with me.  This is a non-MVC project so please make the solutions relevant to ASP.NET 4.5.1 Website projects not using Entity Framework.  (I'll get there one of these days but I'm not there yet.)

I've created an unbound web form that contains client data which needs to be saved in multiple related tables, starting with the client record.  Once that is saved I need to use the client ID from the newly saved client record as the foreign key in the client address, telephone, and email records.

Step 1:  Save client name.
Step 2:  Get the Id (primary key) of the new client record.
Step 3:  Save client address (using primary key from step 2 as foreign key in the address record).
Step 4:  Save client phone number (using primary key from step 2 as foreign key in the phone record).
Step 5:  Save client email address (using primary key from step 2 as foreign key in the email record).

I don't need to be told step by step how to do this (I think), but I do need to know the best way to get started with this.

Thanks!
0
penlandt
Asked:
penlandt
  • 2
  • 2
1 Solution
 
sudheeshthegreatCommented:
Create a stored proc that gets all the data in the web form. Within the stored proc, insert into first table, use SCOPE_IDENTITY() to get the id that was just inserted, and use that when inserting into the other tables. Also, have all the insert statements within a transaction using TRY, COMMIT and ROLLBACK statements.
0
 
penlandtAuthor Commented:
OK, I've done that before.  I will need an example of the C# code to execute the stored procedure though.  This is my first time doing this sort of thing so I'm not yet familiar with that.  Thanks!
0
 
sudheeshthegreatCommented:
If you are using a SQL Server database, you need to use classes in the System.Data.SqlClient namespace.

A sample code can be found here:
http://msdn.microsoft.com/en-us/library/tyy0sz6b(v=vs.110).aspx

Since in your case, you are posting data instead of getting it back, you need to use ExecuteNonQuery instead of ExecuteReader.
0
 
penlandtAuthor Commented:
Thank you very much.  I've successfully executed the stored procedure from my app so I'm on my way.  I appreciate the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now