Delete records from datatable

Hello guys i have this datatable, and i want remote ALL the records i have been created. Have some option to clear the datatable?



there are multiple ways to do that.

1. using SQL server to truncate the table (when you've access to)
2. by building functionality to do a for each and deleteRecord
3. perhaps by copying the table, delete the original one. and after that, use dbcleaner to remove the old entity from the database.

Regards, Hans

Hi Francisco,


You need to  do an advanced query with Delete {Candidaturas}. You can select the output anything you want (in my case I put the User table) since no output will come from there.


Although you can do it with a for each loop, I disadvise that because it has a high performance cost. If for instance, you would be deleting 1000 records, it would be 1000 calls to the database whereas like this it is just one and thus much more performant.


Cheers,

João

Hi Francisco Brito 

use a check box option to delete all record from table widget, but next time you can add new record id will not generate 1.

Hi Francisco,

I'd go for Hans's option 3: Simply cut the Entity with the data you want to clear & paste it back. Works like a charm. Like Hans mentions, there's going to be a "trash" table left behind in the Database, but there are tools the system administrator can use to clean it up.

If you don't want to touch the Entity definitions, I'd advise against Hans's option 2, and prefer João's approach. But I suggest you use the SQL Sandbox instead of writing one-time-use functionality. You can use the sandbox to execute SQL instructions directly via a web interface (like the DELETE João suggests). Be careful though, like the story goes, "with great power comes great responsibility".

Hope this helps!

Hello Francisco,

Please check this forum thread where several options were given to address the same issue.


Hope that this helps you!


Kind regards,

Rui Barradas

Francisco,

You've asked a VERY similar question a few days ago, and already got some of the same answers you are receiving here.

Is there a reason to ask it again now? Were the other answers not what you needed?

Jorge Martins wrote:

Francisco,

You've asked a VERY similar question a few days ago, and already got some of the same answers you are receiving here.

Is there a reason to ask it again now? Were the other answers not what you needed?

 ok, sory my memory is not good, and i dont remember is ask it question. Sorry

 

Hi Francisco,

As others said, you can always cut and paste the entities in service studio, what will reset them and leave an old table in your database.

However beware that if this code is used in QA or Production, if you do it in your dev environment and deploy via Lifetime or via Solution, it will also reset the data in these environments. 

So just use this solution if this code is not in other environments!!!


Another solution that is really practical and don't need SQL Sandbox component is to test an advanced query commiting it.

Just try to use in the advanced query:


BEGIN

DELETE FROM [Table];

COMMIT;
END


Then hit Test. It will probably throw an error, but will delete all from this table.

It only works in SQL Server IaaS if I'm not mistaken.

If you want to use it in a PaaS if I remember well, you have to use a transaction clause to be able to use this hint.

Something like:


BEGIN Transaction;

DELETE FROM [Table];

COMMIT;
END


However, have in mind this is a dangerous way to do it if you not aware of what you are doing!!

Raphael Ranieri wrote:


Another solution that is really practical and don't need SQL Sandbox component is to test an advanced query commiting it.

Just try to use in the advanced query:


BEGIN

DELETE FROM [Table];

COMMIT;
END


Then hit Test. It will probably throw an error, but will delete all from this table.

It only works in SQL Server IaaS if I'm not mistaken.

If you want to use it in a PaaS if I remember well, you have to use a transaction clause to be able to use this hint.

Something like:


BEGIN Transaction;

DELETE FROM [Table];

COMMIT;
END


However, have in mind this is a dangerous way to do it if you not aware of what you are doing!!

Raphael and Francisco,

COMMIT is no longer accepted by Service Studio as a valid SQL command used like that.

Also, and more importantly, even when this type of solution worked, it also is a lot more error-prone, often leading to SQL tools left behind in code that delete all data and cause all sorts of problems later on if we’re not careful. That’s why I suggested the SQL Sandbox application.

Raphael’s remarks around cut & paste of entities are spot on, but when you’re still fine-tuning the data model, and haven’t deployed any application to other environments it’s one of easiest and safest ways.

Hi,

Crate a server action and add SQL widget. Write your delete SQL.

What I prefer to do in development environment is to create a server action to clear entity using SQL widget and create one timer with has no schedule. When I need I run this timer manually from service center and clear the tables.

Jorge Martins wrote:

Raphael Ranieri wrote:


Another solution that is really practical and don't need SQL Sandbox component is to test an advanced query commiting it.

Just try to use in the advanced query:


BEGIN

DELETE FROM [Table];

COMMIT;
END


Then hit Test. It will probably throw an error, but will delete all from this table.

It only works in SQL Server IaaS if I'm not mistaken.

If you want to use it in a PaaS if I remember well, you have to use a transaction clause to be able to use this hint.

Something like:


BEGIN Transaction;

DELETE FROM [Table];

COMMIT;
END


However, have in mind this is a dangerous way to do it if you not aware of what you are doing!!

Raphael and Francisco,

COMMIT is no longer accepted by Service Studio as a valid SQL command used like that.

Also, and more importantly, even when this type of solution worked, it also is a lot more error-prone, often leading to SQL tools left behind in code that delete all data and cause all sorts of problems later on if we’re not careful. That’s why I suggested the SQL Sandbox application.

Raphael’s remarks around cut & paste of entities are spot on, but when you’re still fine-tuning the data model, and haven’t deployed any application to other environments it’s one of easiest and safest ways.

 

Hi Jorge,

Since what version the commit can't be used in advanced? I still can use it for now!

But I agree with you, that is error prone this is the why I pointed the red warning, and have to be used very carefully :)

In any case, when you know what you are doing, this is a very fast way to solve the problem if you don't have SQL Sandbox or Access to DB directly.