CODEWITHSUNDEEP
c#sqlinsert
user3544501
user3544501

Reputation: 45

insert into database in a range time

I'm using Visual Studio 2012, SQL Server, C#.

I have two DatePicker objects, I want to insert 1 row into the database for each day between the selected dates.

For example, I select

Datepicker 1: 09/08/2014 (mm/dd/yyyy)

Datepicker 2: 09/10/2014 (mm/dd/yyyy)

And when I clicked insert button with same content "a" , it will insert 3 times with date between 09/08/2014 to 09/10/2014 , see picture

http://www.mediafire.com/convkey/2b4c/13l6kpevdk113026g.jpg

The result in the database would look like this:

http://www.mediafire.com/convkey/e25c/842wgeaq4z107y36g.jpg

I am thinking of using a for loop to accomplish this but I don't know how to write it.

Thank you very much

Upvotes: 1

Views: 685

Answers (1)

Jim
Jim

Reputation: 701

If you're using C# you can use the functions in DateTime to construct the SQL string to execute. E.g.

  DateTime start = new DateTime(2000, 2, 25);
  DateTime end = new DateTime(2000, 3, 2);
  String sql = "";
  for (int i = 0; i < end.Subtract(start).Days; i++)
  {
      if (sql.Length > 0)
          sql += ",";
      sql += "\n('" + start.AddDays(i).ToString("yyyy-MM-dd") + "')";
  }
  sql = "INSERT INTO tableX VALUES" + sql;

Produces 

INSERT INTO tableX VALUES
('2000-02-25'),
('2000-02-26'),
('2000-02-27'),
('2000-02-28'),
('2000-02-29'),
('2000-03-01')

Upvotes: 1

Related Questions