Solved

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

Posted on 2015-01-11
4
209 Views
Last Modified: 2015-01-11
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
Comment
Question by:penlandt
  • 2
  • 2
4 Comments
 
LVL 9

Expert Comment

by:sudheeshthegreat
ID: 40543700
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
 
LVL 1

Author Comment

by:penlandt
ID: 40543716
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
 
LVL 9

Accepted Solution

by:
sudheeshthegreat earned 500 total points
ID: 40543778
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
 
LVL 1

Author Closing Comment

by:penlandt
ID: 40543789
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question