For loop using in conjunction with an sqlcommand

Question

i have an SProc which returns the avg of all columns between two times, ie 04:00 to 04:14. I want to have an option to return results for 24hr at that same interval (in this case the interval is 15mins. 04:14 - 04:00) So the results would look like the following:

00:00 - 00:14 = x.xxx

00:15 - 00:29 = x.xxx

....

im assuming i cant change the sqlcommand (specifically the parameters for the SProc) inside the using statement, and would need to put the for loop before, thus creating a new SqlCommand object each time?

    for(int i = x; .....)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
        }
    }

Thanks

score 2 · Accepted Answer · answered Jun 24 '12 at 21:39

2

This is an okay way to do this, as long as you don't open a new connection each time. I'm not sure about the performance implications though.

answered Jun 24 '12 at 21:39

Kyle Trauberman

25,414
13
85
121

loopedcode · Answer 2 · 2012-06-25T03:26:17.727

0

If you really want to go with a for loop then you should do something like following:

using(SqlCommand cmd = new SqlCommand())
{
  //Setup the command object and connection.
  for(int i=0; i<....)
  {
    cmd.CommandText = "set your dynamic sql here based on current iteration";
    var reader = cmd.ExecuteReader();
    //Now read you rows
  }
}

I would do this within SQL in a stored proc and return all the rows for your time range data.

edited Jun 25 '12 at 03:26

answered Jun 24 '12 at 21:40

loopedcode

4,863
1
21
21

Thanks for correcting. For some reason I always thought disposer on SqlCommand closes the connection. – loopedcode Jun 25 '12 at 03:27
The `SqlConnection.Dispose` method closes the connection, so you're partly right. – Kyle Trauberman Jun 25 '12 at 03:49

For loop using in conjunction with an sqlcommand

2 Answers2