Delete user records from others table when delete user ID c#

Question

0.00/5 (No votes)

See more:

Hi
The below code is working fine but i am looking to add more feature to this code as i need when i delete the user via below code as well delete all records into database belong to the same UID in others tables: ads, CVs, FavComp, FavJob, FavoCv, favourite, JobApplic, jobs, Orders, ShoppingCart.

C#

protected void DeltUsrbtn_Command(object sender, CommandEventArgs e)
        {
            using (SqlConnection DeltUsrSQLCon = new SqlConnection(sc))
            {
                System.Data.SqlClient.SqlCommand DeltUsrcmd = new System.Data.SqlClient.SqlCommand();
                DeltUsrcmd.CommandType = System.Data.CommandType.Text;

                DeltUsrcmd.CommandText = "DELETE FROM UserInfo WHERE UID = @UID";

                DeltUsrcmd.Parameters.AddWithValue("@UID", e.CommandArgument.ToString());
                DeltUsrcmd.Connection = DeltUsrSQLCon;
                DeltUsrSQLCon.Open();

                DeltUsrcmd.ExecuteNonQuery();
                DeltUsrSQLCon.Close();
                ReBindDeltUsrsGV();
                admusrdeletedlbl.Text = "User has been deleted successfully";
            }
        }

Posted 23-Oct-15 16:45pm

Member 10690878

Add a Solution

Comments

PIEBALDconsult 23-Oct-15 23:02pm

Never delete. Have an attribute that indicates that it is inactive.

Santosh K. Tripathi 24-Oct-15 13:51pm

you are right. in app never delete data just mark it isDeleted = true.

and for more complex logic use stored procedure. :)

jgakenhe 24-Oct-15 0:37am

The you'll need to change your query to delete in multiple tables.
DELETE FROM UserInfo WHERE UID = @UID
GO
DELETE FROM Table2 WHERE UID = @UID.

It would be much more elegant if all of it was in 1 stored procedure.

Arkadeep De 24-Oct-15 1:53am

Either write delete command for all tables or write a trigger which will fire after delete a row from your primary table. And use any flag like IsDelete (bit). and update the row data with making the value of IsDelete true/false.

For trigger visit this one -- http://stackoverflow.com/questions/9996643/sql-server-on-delete-trigger?answertab=active#tab-top

Santosh K. Tripathi 25-Oct-15 3:52am

you are correct.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Afzaal Ahmad Zeeshan · Accepted Answer · 2015-10-23T21:03:00

There are many ways to do this, but since we are talking about this function then the solution would be, (as Arkadeep said in his comment to your question), to add the delete commands for other tables too. Which would delete the data from other tables also, once it has finished deleting from one table.

C#

SqlCommand command = new SqlCommand("DELETE FROM ads WHERE UID = @UID", connection);
SqlCommand command = new SqlCommand("DELETE FROM CVs WHERE UID = @UID", connection);
SqlCommand command = new SqlCommand("DELETE FROM FavComp WHERE UID = @UID", connection);
SqlCommand command = new SqlCommand("DELETE FROM FavJob WHERE UID = @UID", connection);
...

This way, these commands would delete the record for the user with that ID in other tables also.

Another way would be to implement referential integrity[^] and store the UserInfo as Master table. So that the child would hold the data for those records (only!) which exist in the master table. If master deletes or updates on record, other tables update themselves based on the new data. This method would take a bit of time as you may need to learn about the concept first.