I have a database with around 12 tables, and majority of the tables are connected
to one centeral table
I want to provide deletion interface to the client in my Asp.Net web page
So I want to know, how can I use T-SQL or simple SQL to display all records
that are connected with each other
(the interface will provide a drop down to user that has items similar to table names
in my database
So when the user will select an item, I want to display all the connected records
so that they can be deleted after displaying them for user confirmation
I would write a stored procedure that takes the primary key of the main table as a parameter. Use that value in a select statement for each table. Stored procedures can return multiple record sets. In code declare a data table for each returned record set and
use data table.load(reader) to fill them with the record sets returned from the stored procedure. Then do a for each (data row in data table) to display in your page.
Reporting by definition is different. Otherwise we would just show it on the screen.
Usamn, for "Command to display all records before deletion" part of your question, I would suggest this :
As you already have the where clause that can delete the reocrds, instead of doing just the DELETE operation, you may do SELECT and then can get all those records taht are goin to be deleted, once you've got them, you may prompt for the confirmation to the
user and if user authorizes, you may perform delete on those records, for example, you want to delete all customers from Japan & Belgium, from your Nothwind database, and assume that you are storing all your records in 'CustomerInfo' table, then ...
instead
DELETE * FROM CUSTOMER_INFO WHERE COUNTRY=N'JAPAN' OR COUNTRY=N'BELGIUM'
do this ...
-- or this may be stored procedure that can retrieve all linked records from other tables for the records that meet the following criteria uusing PK and FK
SELECT * FROM CUSTOMER_INFO WHERE COUNTRY=N'JAPAN' OR COUNTRY=N'BELGIUM'
-- prompt the user for confirmation to delete the records
--if YES,
DELETE * FROM CUSTOMER_INFO WHERE COUNTRY=N'JAPAN' OR COUNTRY=N'BELGIUM'
--else, DO NOTHING
usman400
Contributor
3503 Points
721 Posts
Command to display all records before deletion
Dec 09, 2012 01:51 PM|LINK
I have a database with around 12 tables, and majority of the tables are connected
to one centeral table
I want to provide deletion interface to the client in my Asp.Net web page
So I want to know, how can I use T-SQL or simple SQL to display all records
that are connected with each other
(the interface will provide a drop down to user that has items similar to table names
in my database
So when the user will select an item, I want to display all the connected records
so that they can be deleted after displaying them for user confirmation
the database diagram is attached
ryanbesko
Contributor
3561 Points
619 Posts
Re: Command to display all records before deletion
Dec 09, 2012 05:08 PM|LINK
aarsh
Participant
1543 Points
427 Posts
Re: Command to display all records before deletion
Dec 09, 2012 10:31 PM|LINK
Usamn, for "Command to display all records before deletion" part of your question, I would suggest this :
As you already have the where clause that can delete the reocrds, instead of doing just the DELETE operation, you may do SELECT and then can get all those records taht are goin to be deleted, once you've got them, you may prompt for the confirmation to the user and if user authorizes, you may perform delete on those records, for example, you want to delete all customers from Japan & Belgium, from your Nothwind database, and assume that you are storing all your records in 'CustomerInfo' table, then ...
instead
do this ...