How to delete 1 or n year old data from current date from database(SQL server) in C#?

Question

0.00/5 (No votes)

See more:

I want to delete data from database which are older than 1 or n year(user will enter year),
how to do this plz help me.
thanks in advance.
sushil

What I have tried:

I search on google but i dint get any solution, I dont know how to do this,

Posted 3-Jul-16 6:17am

Member 11859517

Updated 3-Jul-16 6:24am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-07-03T06:24:00

Solution 1

Try:

C#

int year;
if (!int.TryParse(userInputTextBox.Text, out year))
   {
   // Report problem to user
   ...
   return;
   }
DateTime olderThan = DateTime.Now.Date.AddYears(-year);
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("DELETE FROM myTable WHERE dateColumn < @LIM", con))
        {
        cmd.Parameters.AddWithValue("@LIM", olderThan);
        cmd.ExecuteNonQuery();
        }
    }

Posted 3-Jul-16 6:24am

OriginalGriff

Comments

Member 11859517 3-Jul-16 12:53pm

thanx,

actualy i want to delete the images.

Member 11859517 3-Jul-16 12:56pm

DateTime olderThan = DateTime.Now.Date.AddYears(-year);
what it means (-year) i dint get.

OriginalGriff 3-Jul-16 13:21pm

What happens if you add a negative number?
int x =42;
x += -3;
Console.WriteLine(x);

Try it and see...

Member 11859517 3-Jul-16 23:50pm

thanks i got it.

OriginalGriff 4-Jul-16 5:03am

You're welcome!

Member 11859517 3-Jul-16 23:53pm

plz, can u tell me about @LIM parameter.

OriginalGriff 4-Jul-16 5:03am

What do you want to know?

Member 11859517 4-Jul-16 5:54am

why we use parameter there? sorry i think its silly que. bt fracky i m not understood.

can we do without using parameters?

OriginalGriff 4-Jul-16 6:52am

You use a parameter for two reasons:
1) So that you can send the DateTime value directly. The alternative is to convert it to a string, but then you leave room for SQL to misinterpret the value: what date is 01/02/03? It could be US: 2nd Jan 2003, Europe: 1st Feb 2013, Japan / ISO 3rd Feb 2001 ... getting it wrong can really screw up your database!
2) Because it's good practice: you should *NEVER* concatenate strings to form an SQL command, because it can leave you wide open to something called SQL Injection which lets users damage or destroy your database just by typing in text boxes. And it's really, really easy for them to do:
https://xkcd.com/327/
Read it, and don't assume it's just a joke.
Get in the habit of always using parameters and you won't have a problem - it's too easy to get into bad habits and they are hard to break.

Member 11859517 4-Jul-16 23:50pm

thank u so much sir...:)

OriginalGriff 5-Jul-16 2:54am

You're welcome!