To delete all records in a database table up to yesterday's date, leaving only today's records, you can modify your SQL query to include a date condition. Here's a step-by-step guide to achieve this:
1. Identify the Date Column
Assuming your table has a date or datetime column that indicates when each record was created or last updated (e.g., `CreatedDate` or `LastUpdatedDate`), you will use this column to filter out the records.
2. Modify the Delete Query
Here’s the modified query that will delete records where the date is before today's date. This example assumes the column is named `CreatedDate`:
DELETE FROM YourTableName
WHERE CreatedDate < CAST(GETDATE() AS DATE);
- `CAST(GETDATE() AS DATE)` converts the current date and time to just the date portion (i.e., it removes the time).
- The `WHERE` clause ensures that only records with a `CreatedDate` before today are deleted.
3. Implement in C# Console Application
Here’s how you can incorporate this SQL query into a C# console application:
using System;
using System.Data.SqlClient;
using System.Configuration;
namespace ScheduledDatabaseCleaner
{
class Program
{
static void Main(string[] args)
{
DeleteRecordsUpToYesterday();
}
private static void DeleteRecordsUpToYesterday()
{
string connectionString = ConfigurationManager.ConnectionStrings["BajajTMSResultMasterEntities"].ConnectionString;
string deleteQuery = "DELETE FROM YourTableName WHERE CreatedDate < CAST(GETDATE() AS DATE)"; // Replace 'YourTableName' with the actual table name.
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(deleteQuery, connection))
{
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"{rowsAffected} rows deleted successfully.");
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
}
4. Compile and Schedule the Application
1. Compile: Build your console application to create an executable (`.exe`) file.
2. Schedule: Use the Windows Task Scheduler to schedule the execution of your application at 12:35 AM or any other desired time.
Scheduling with Windows Task Scheduler:
1. Open Task Scheduler: Go to the Start menu, search for "Task Scheduler," and open it.
2. Create a New Task: Click on "Create Task" on the right pane of the Task Scheduler.
3. General Tab: Enter a name for your task, like "Delete Old Database Records."
4. Triggers Tab:
- Click on "New..."
- Set the start time to 12:35 AM.
- Set the frequency to "Daily."
- Click "OK."
5. Actions Tab:
- Click on "New..."
- In the "Action" dropdown, select "Start a program."
- In the "Program/script" box, click "Browse" and select the compiled `.exe` file of your console application.
- Click "OK."
6. Conditions and Settings (Optional): Adjust any conditions or settings according to your needs.
7. Save the Task: Click "OK" to save the task.
5. Test the Application
Test your application by running it manually from the Task Scheduler to ensure it deletes the records correctly.
Conclusion:
By following these steps, you've successfully set up a system to delete all records up to yesterday, leaving only today's records in the table. This automated approach ensures that your table contains only the most recent data, which can help with database maintenance and performance.
If you like comment and share. 🚀
0 Comments