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