Thursday, March 22, 2012

30 Day Old Delete

I have a database of posts which i want to delete after 30days of being on the site, my code so far gets all the data out of the table and then if their is more than 0 rows it loops within the tblTable and finds all the posts made within the last 30days and deletes them, my problem is how do I get it to work out 30days ago, for 30days time, its just " DateTime.Now.AddDays(30);" but i cant seem to do something as simple for back in time, heres my code :


private void DeleteOldStuff()
{
string strSQL = "SELECT * FROM ForumPosts";
SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BlinkConnectionString"].ConnectionString);
Connection.Open();
SqlCommand comm = new SqlCommand(strSQL, Connection);
SqlDataAdapter da = new SqlDataAdapter(comm);
tblData = new DataTable();
da.Fill(tblData);
Connection.Close();
if (tblData.Rows.Count > 0)
{
for (int i = 0; i < tblData.Rows.Count; i++)
{
DataRow dr = tblData.Rows[i];
if ("30DAYSAGO" <= dr["DateCreated"])
{
Connection.Open();
SqlCommand Delete = new SqlCommand("DELETE FROM ForumPosts WHERE PostID = '" + dr["PostID"].ToString() +"'", Connection);
Delete.ExecuteNonQuery();
Connection.Close();
}
}
}
}

Any help / advice is appriciated! Thanks John

Meaby it is better if u do it in Sql code to select all data if is older than 30 days.. example

; //Pseudokod

Select * from ForumPosts where dataPost < DateTime.Now.AddDays(-30)//

and delete u can do it like this

delete from ForumPosts where dataPost < DateTime.Now.AddDays(-30)// Pseudokod

Sorry for my bad english

|||

No worrys that works much better thanks a million John

sql

No comments:

Post a Comment