delete fields from sql database where the date is a previous date to today in c#

Question

SqlConnection sqlcon = new SqlConnection(GlobalClass.DBLocate);
sqlcon.Open();

string query = "Delete from [Plans] Where Date < '" + 
GlobalClass.Date.ToShortDateString() + "'";

SqlDataAdapter sda = new SqlDataAdapter(query, sqlcon);

sda.SelectCommand.ExecuteNonQuery();

I have tried many techniques but everything in this database is deleted or nothing at all. Many thanks

Answer 1

Instead of passing in the date as a string, why don't you write the SQL using it's native date functions?

As this question is tagged as C# and not with any specific flavor of SQL, I am writing this connecting to SQL Server. You also did not provide the DDL for the table that is being deleted from. The code is based on using a proper column type for the [Date] column, which would be some variant of DateTime.

The following statement should delete everything up the current DateTime

string query = "DELETE from [Plans] WHERE Date < GetDate()";

If you need to fine tune this to everything before today, then you can use CAST/CONVERT and just get the Date portion:

string query = "DELETE from [Plans] WHERE Date < Cast(GetDate() as Date)";

You also don't need to use a DataAdapter for a NonQuery; it's return type is an int, and can be assinged directly

int RowsDeleted = cmd.ExecuteNonQuery();

So putting it all together, we get this code block. I also wrapped within a using block so that the resources are properly cleaned up afterwards.

int RowsDeleted = -1;

using (SqlConnection sqlcon = new SqlConnection(GlobalClass.DBLocate)) {
    string query = "DELETE from [Plans] WHERE Date < GetDate()";
    SqlCommand cmd = new SqlCommand(query, sqlcon);

    sqlcon.Open();
    RowsDeleted = cmd.ExecuteNonQuery();
    sqlcon.Close();
}