This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
How do I truncate a table (removing all the data)? I prefer to truncate it instead of deleting all the records as it should be much faster. Thanks. Andrea |
|
|
// Fast way to delete all or if you really want to emit a TRUNCATE instead of a DELETE: cmd = _unitOfWork.Context.DataProviderObjectFactory.CreateCommand(); |
|
|
thanks for the reply. I think I'll use that syntax. Which syntax should I use if I need to delete many records using an input parameter (I need to delete old the records older than a certain date). Thanks. |
|
|
You should use IUnitOfWork.Remove(Query) for that because this will generate safe SQL in a portable way, and will cascade deletes if required. PrepareCommand is provided only for when you really need an escape hatch into raw SQL. |
|
|
Use the first syntax if you are performing a filtered delete. TRUNCATE wipes the entire table.
Jeremy |
|
|
if using Remove is the only option, I guess I'll need to use a stored procedure because using the Remove is very slow if compared to doing the same thing using a plain SQL statement. The code I'm sending is generated internally by my application so there's no risk of SQL injection. I just wonder if I can use parameters in the query (using the Parameters object) or I need to just compose the query and put the value of the parameters inside. Thanks. |
|
|
Remove by query is slow? Really? All it does is send a DELETE WHERE to the database, so it should be of comparable speed to plain SQL. Do you have a test case where Remove(Query) is slow compared to plain SQL? We'd be interested in investigating. Are you confusing Remove(Query) with Remove(Entity)? Remove(Entity) *would* be slow for removing lots of entities because it would (a) have to load each entity first and (b) send a separate DELETE statement for each entity. But Remove(Query) bypasses all that. Yes, you can use the Parameters collection. IUnitOfWork.PrepareCommand takes an IDbCommand so you have full access to whatever capabilities your database's command class exposes. |
|
|
Hi I'm having difficulty using the suggested approach to empty a table. My db schema looks like: Joint (1) --> (*) Joint Position (1) --> (*) Protocols (1) --> (*) Tests (1) --> (*) TestValues When I execute the below statement: Query allJoints = new Query(typeof(Joint), Entity.Attribute("Id") >= 0); uow.Remove(allJoints); uow.SaveChanges(); It generates the following SQL: DELETE FROM: TestValues WHERE EXISTS ( SELECT Tests.* FROM Tests WHERE Tests.Id = TestValues.TestId AND EXISTS ( SELECT Protocols.* FROM Protocols WHERE Protocols.Id = Tests.ProtocolId AND EXISTS ( SELECT JointPositions.* FROM JointPositions WHERE JointPositions.Id = Protocols.JointPositionId AND JointPositions.JointId >= 0 ) ) ); DELETE FROM Tests WHERE EXISTS ( SELECT Protocols.* FROM Protocols WHERE Protocols.Id = Tests.ProtocolId AND EXISTS ( SELECT JointPositions.* FROM JointPositions WHERE JointPositions.Id = Protocols.JointPositionId AND JointPositions.JointId >= 0 ) ); DELETE FROM Protocols WHERE EXISTS ( SELECT JointPositions.* FROM JointPositions WHERE JointPositions.Id = Protocols.JointPositionId AND JointPositions.JointId >= 0 ); DELETE FROM JointPositions WHERE JointPositions.JointId >= 0 This results in nothing being deleted. Any idea on what I am doing wrong? All I need is a DELETE FROM statement. Kind regards, |
|
|
UPDATE: the delete does seem to work. I guess I'm not refreshing the data correctly. However, wouldn't a DELETE FROM statement suffice instead of this big one? Cascaded Delte is turned on? Or is this not supported well by VistaDB? |
|
|
You're right, this is occurring because of cascade deletes. LightSpeed needs to delete any other entities that might be dependent on the rows you're deleting. |
|