Delete from multiple SQL tables with C# command

Question

I want to delete from multiple SQL tables with a C# command but I get always error:

Invalid syntax near ",".

Here is code so far:

string connectionString = @"Data Source=" + System.IO.File.ReadAllText("Server.ini") + ";" + "Initial Catalog=" + "lin2world" + ";" + "User ID=" + System.IO.File.ReadAllText("User.ini") + ";" + "Password=" + System.IO.File.ReadAllText("Password.ini");
string sql = "DELETE FROM user_data, user_item, user_ActiveSkill, user_blocklist, user_deleted, user_friend, user_henna, user_history, user_log, user_macro, user_macroinfo, user_newbie, user_nobless, user_punish, user_recipe, user_skill, user_sociality, user_subjob WHERE char_id='" + textBox1.Text + "' ";
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
DataSet ds = new DataSet();
connection.Open();
dataadapter.Fill(ds, "char_id");
connection.Close();
MessageBox.Show("Character Deleted!!");

SQL Server has no syntax to delete from multiple tables in one statement. — Martin Smith, Sep 27 '13 at 12:02
http://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a — Eric Hotinger, Sep 27 '13 at 12:04
That's not the correct syntax for DELETE. It doesn't look like you even have a table name in there anywhere. DELETE is used to delete rows from a table; are you trying to clear the values in certain columns but leave the row in the table? — rory.ap, Sep 27 '13 at 12:04
what I would do is put seperate deletions into a transaction. read this other answer http://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server — Squirrel5853, Sep 27 '13 at 12:06
Write a stored procedure to clear the tables and call it. Also please look into parameterized queries, this is a sql injection vector **nightmare**. — asawyer, Sep 27 '13 at 12:12
You can still do this with a cursor and a select based on your list of names, see below. Also yes as sawyer says, please, please, don't do this without using a parameterized query — Yablargo, Sep 27 '13 at 12:13

score 4 · Answer 1 · answered Sep 27 '13 at 12:03

4

You can only delete from one table in a delete statement.

Use multiple delete statements within a transaction to perform the deletes

In case your not familiar with transactions in C# here is an example

using (var Conn = new SqlConnection(_ConnectionString))
{
    SqlTransaction trans = null;
    try
    {
        Conn.Open();
        trans = Conn.BeginTransaction();

        using (SqlCommand Com = new SqlCommand(ComText, Conn, trans))
        {
            /* DB work */
        }
        trans.Commit();
    }
    catch (Exception Ex)
    {
        if (trans != null) trans.Rollback();
        return -1;
    }
}

answered Sep 27 '13 at 12:03

Kevin Kunderman

2,036
1
19
30

1

You can also use triggers: http://stackoverflow.com/questions/9996643/sql-server-on-delete-trigger Or some form of cascading delete if it's applicable. – Eric Hotinger Sep 27 '13 at 12:06
Cascading delete makes the most sense here i'd say. – Yablargo Sep 27 '13 at 12:17
@Yablargo I agree only if the tables are truly relational, Since we don't know the table structure we can't really say that the id is set up as a foreign key in the other tables though. – Kevin Kunderman Sep 27 '13 at 12:19

score 1 · Accepted Answer · answered Sep 27 '13 at 12:11

If you want to do something similar to this, you can pass all tables as a list and still do what you want.

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ("user_data","user_item","user_ActiveSkill","user_blocklist","user_deleted","user_friend","user_henna","user_history","user_log","user_macro","user_macroinfo","user_newbie","user_nobless","user_punish","user_recipe","user_skill","user_sociality","user_subjob")  -- use these databases

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   


WHILE @@FETCH_STATUS = 0   
BEGIN   

       DELETE FROM @name WHERE char_id='whatever'

       FETCH NEXT FROM db_cursor INTO @name   
END

Edit: It seems like these are all related tables -- if you have a proper relationship with cascade: delete, you should only have to delete the main record and its children will all take care of themselves

score 0 · Answer 3 · answered Sep 27 '13 at 12:18

You cannot delete from multiple tables in one statement within SQL Server. You will need multiple statements, one for each table:

DELETE FROM user_data WHERE PersonID = '2';
DELETE FROM user_item WHERE PersonID = '2';

etc etc

And fire each one off individually, (making sure not to violate any FKs) or create a stored procedure and fire that off (to minimise open and closing of db connections).

My experience of using triggers tells me not to use triggers, at times they will not fire when they should!

Delete from multiple SQL tables with C# command

3 Answers3