SQL pains..

So I was working on some code to read and write data to SQL ( not using LINQ or any fancy stuff.. heck I just started doing managed code. )

I was specifically interested in the count of times , it should look something like this ( from SQL  )

select COUNT(DateandTime),DateandTime from MyData group by DateandTime,Server order by COUNT(DateandTime)

8    2008-07-21 10:43:03.000
8    2008-07-21 10:40:26.000
9    2008-07-21 10:41:15.000
9    2008-07-21 10:45:13.000
9    2008-07-21 10:45:31.000
10    2008-07-21 10:40:58.000
10    2008-07-21 10:49:11.000
11    2008-07-21 10:45:08.000
12    2008-07-21 10:43:04.000

So for example - 2008-07-21 10:43:04.000  had 12 occurrences in the data.

But for the life of me , I couldn't figure out how to return the count in the code.. lotsa references on how to return how many rows were returned total  using ExecuteScalar() but that's not what I wanted..

Anyway. In the end I did something like this - not being a SQL guy, not sure if this is the right way to go about it, will I take a hit on performance? Was there a better way?

DataTable dt = new DataTable();
     SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
     adapter.SelectCommand = myCommand;
     adapter.Fill(dt);

     if (dt.Rows.Count > 0)
     {
         int rowcount = dt.Rows.Count;
         for (int i = 0; i < rowcount; i++)
         {
             int cnt = (int)dt.Rows[i].ItemArray.GetValue(0);   // this gives me the count info...
             string str = dt.Rows[i]["somestring"].ToString();
             Console.Write("data = {0} : {1}\n", cnt,str);
         }
     }

thx

spat

Comments

• Anonymous
  August 18, 2008
  The comment has been removed

• Anonymous
  August 18, 2008
  Ah I tried the reader  method but could not get the right format you shared: var cnt = reader.GetInt32(0); thanks! spat

• Anonymous
  September 08, 2008
  The caveat to those samples is that they need the latest and greatest .Net 3 or 3.5 or something like that. You would want to strongly type the variables (so int instead of var, etc) to make it compile on downlevel versions. You can also call reader.read("column name") or something to that tune if you don't want to hard code the indexes of the columns. The downside is you have to manage the type casting yourself.