Storing Data From Front End Page to SQL Server. What is the best approach??????????

Last Post 16 Apr 2012 01:54 AM by karthik. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
Imran Khan
New Member
New Member

15 Apr 2012 01:56 PM

Imagine a situation where a record is going to be inserted first time in master table and then subsequent child tables. And IDs for the master records are not known. I need to pass master record string values from textboxes (Sorry forgot to mention it before). Once these are stored then I'll store child records as per the master Ids.

In the meanwhile there are number of titles shown on front end are already po***ted in the back end master tables. Now, I am passing these titles and selected values from drop downs against these titles as character strings to stored procedure using Table Value Parameters. Once, received in stored procedure I join TableValueParameter table to master table on character values to find out their IDs and then use then use these master IDs to store record in child tables.

You can imagine the kind of situation it is now. For the 1st situation where master ids are not known I have to pass character strings to back end stored procedure to create master record. Can you please suggest is this the valid approach or is there any other option available to do it and I am not aware of it.

And for 2nd situation where master records are stored and Ids can be extracted to store in data in child tables. The approach I am using is capturing all character string values from the front end (And instead of quering database tables to find Ids of these strings I am) storing them in a sort of denormalized TableValueParameter type table and passing it to stored procedure. Where I am joining received string values to master tables to find out their IDs

For Example.
From front end I am passing

insert into @TVP (ClientName, PortionName, SubPortionName,PortionVisibility,SubPortionVisibility,
Values ('ExpertExchange','Databases','SQL Server',1,1,1,0,1)


Now after receiving these values in stored procedure to find out Ids;

I join
ClientName with ClientName in ClientTable and get its id and I do the same for to get all other master ids.
Once I have Ids for
ClientName,PortionName,SubPortionName I use them to store in GroupPermission tables.

Hope I have explained the my question in detail. Now what I want to know is my approach valid?

I really appreciate the time and effort you put in to read my question. Waiting for your valuable comments and suggestions.

New Member
New Member

16 Apr 2012 01:54 AM
Hi Khan,

This is Initial process for importing data(master data and child data). My idea is better do it in Database side(by DB script)look below my example

There are three table tbl_country(Master table), tbl_user(child table),source_users(user list to be imported)


EX DB script:

INSERT INTO tbl_country (country_name) VALUES('US')

INSERT INTO tbl_user (user_last_name,country_id)
SELECT s.user_last_name,c.country_id
FROM source_users s
LEFT JOIN tbl_country ON s.country_name = c.country_name

Acceptable Use Policy