Use one DataContext.ExecuteCommand to execute query for multiple tables

Question

I need to delete all data from my tables in database. So I use DataContext.ExecuteCommand.

db.ExecuteCommand("DELETE From Products");

This method causes a new request to the database for each table. I want to clear data from my tables in one query. But all my attempts so far were unsuccessful.

Is it possible to use DataContext.ExecuteCommand to delete data from multiple tables (multiple SQL queries)?

        try
        {
            using (var context = new DataContext())
            {
                context.DeleteAll();
            }
        }
        catch (Exception exception)
        {
            LogHelper.Log(exception);
        }

private static void DeleteAll(this DataContext context)
        {
            var ctx = ((IObjectContextAdapter)context).ObjectContext;

            ctx.ExecuteStoreCommand("DELETE FROM [RegionProducts]; DELETE FROM [ProductTypes]; DELETE FROM [Measurements]; DELETE FROM [Sizes]; " +
                                    "DELETE FROM [Regions]; DELETE FROM [Types]; DELETE FROM [Categories]; DELETE FROM [Images] where Product_Id is not null; DELETE FROM [Products]; " +
                                    "DELETE FROM [Brands]; DELETE FROM [Images];");
        }

marc_s · Accepted Answer · 2015-12-19T21:53:19.863

1

That will heavily depend on the actual database system you're using - but in the case of e.g. SQL Server, you should be able to have multiple DELETE statements - separated by a semicolon - like this:

string deleteQuery = "DELETE FROM dbo.Products; DELETE FROM dbo.OtherTable; DELETE FROM dbo.YetAnotherTable;";

db.ExecuteCommand(deleteQuery);

Of course, you need to pay attention to any possible FK relationships that might cause one delete to fail, if other tables still reference its data - but it should be possible to do this in a single db.ExecuteCommand call

Update: unfortunately, in SQL Server Compact Edition v4 that you're using, you cannot have multiple SQL statements in a single command - it is just not supported. This approach works just fine in the real SQL Server - but it will not work in SQL Server CE ...

edited Dec 19 '15 at 21:53

answered Dec 19 '15 at 20:34

marc_s

732,580
175
1,330
1,459

about FK - I've found correct way of deleting data from tables when I make `ExecuteCommand` for separate server. I hope it will work in query as in your example? – demo Dec 19 '15 at 20:45
Don't know why, but I get error : `There was an error parsing the query. [ Token line number = 1,Token line offset = 31,Token in error = DELETE ]` – demo Dec 19 '15 at 21:12
Would be better to use `context.Database.ExecuteSqlCommandAsync` if `context is DataContext` ? – demo Dec 19 '15 at 21:26
What **version** and **edition** of SQL Server are you using? Is this SQL Server **CE** (Compact Edition) by any chance?? – marc_s Dec 19 '15 at 21:44
**AHHHH!!!!** That explains it (and you didn't ever mention that before - please **do tell us** what system you're using - always - and just "SQL" isn't a product - it's a language - so that doesn't usually help....). Unfortunately, SQL Server **COMPACT** doesn't allow multiple SQL statements in a single command - you cannot delete from all tables in a single command in SQL Server CE (the *real* SQL Server can do this, no problem) – marc_s Dec 19 '15 at 21:50

Use one DataContext.ExecuteCommand to execute query for multiple tables

1 Answers1