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];");
}
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 ...