How to delete only Top 100 rows which are older than 30 days from current date in C#?

Question

When I execute following query , it is executed properly in SSMS but same query in code does not delete anything and 0 rows are affected from thousands of rows.

Code is excuted without any error , have tried Query parameters as well but not worked.

Logs is table and DateCreated is column of DateTime. Sql Connection settings and other things are correct.

SSMS query execution below :

Can someone please correct this query for code ?

                connection.Open();                                       
                SqlCommand cmd1 = new SqlCommand("delete TOP (100) from [Logs] WHERE DateCreated < GETDATE() - 30", connection);
          
                var number_of_rows_deleted = cmd1.ExecuteNonQuery();
                result = number_of_rows_deleted.ToString() + " records deleted";

Also: you you get an error? Is there a try/catch here that might be hiding the error? Is the connection string for the same user/login as the SSMS connection? Are there any schemas other than `dbo`, since that's missing from the query that doesn't run? — Joel Coehoorn, Sep 13 '21 at 22:01
Other than the way you refer to the table, the SSMS and dot net code queries are the same so you (pick one): are not using the same connection; are getting an error that you are not sharing; are not actually getting to that code because of some program flow logic (put a breakpoint on that line and debug); I think that's it.... — Jonathan, Sep 13 '21 at 22:05
Silly but I have to ask. After you delete 100 rows with SSMS did you restore the table? Or do you have another set of 100 rows to delete? — Steve, Sep 13 '21 at 22:06
@JoelCoehoorn , have updated question . Code is executed without error , thousands of record are there , as I said in question only the query needs to be corrected. — Pratik Wasnik, Sep 13 '21 at 22:11
Are you trying to delete *all* qualifying records in batches of 100, ie, repeatedly executing until rows deleted <100, in which case it wouldn't matter *which* 100 rows were deleted; otherwise, essentially 100 random rows are deleted. — Stu, Sep 13 '21 at 22:15
@Stu any random 100 rows deletion is fine , suggestion on why this query is not working is appreciated. — Pratik Wasnik, Sep 13 '21 at 22:21
If ExecuteNonQuery returns 0 without any error then the database table has no rows to delete that match the condition. The query text is correct and it is the same if executed by code or by SSMS. Are you using DataDirectory in the connectionstring? — Steve, Sep 13 '21 at 22:25
@Steve , table has thousands of rows which match the condition, not DataDirectory is used. — Pratik Wasnik, Sep 13 '21 at 22:27
only a detail but use `DATEADD` like `DATEADD(DAY, - 30,GETDATE())` — musium, Sep 13 '21 at 22:29
@Jonathan , everything is same , no problem with connection string , try it yourself , its very strange problem in execution in C# code — Pratik Wasnik, Sep 13 '21 at 22:29
Does this answer your question? [Get affected rows on ExecuteNonQuery](https://stackoverflow.com/questions/10059158/get-affected-rows-on-executenonquery) — Stu, Sep 13 '21 at 22:32

Serge · Accepted Answer · 2021-09-13T22:42:47.400

2

When TOP is used with DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statement. If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement

your sql script should be like this

DELETE from [Logs] 
WHERE Id IN  
 (SELECT TOP(100) Id
   FROM Logs
   WHERE  DATEDIFF(day, DateCreated, GETDATE()) < 30
  Order By DateCreated )

edited Sep 13 '21 at 22:42

answered Sep 13 '21 at 22:31

Serge

40,935
4
18
45

1

Serge can you please edit the question so it matches the answer? It does look like there is nothing related to C# and @PratikWasnik did not have working SQL statement to start with (despite cool pictures). Converting images to text would be nice too... – Alexei Levenkov Sep 14 '21 at 01:56

How to delete only Top 100 rows which are older than 30 days from current date in C#?

1 Answers1