Spat's WebLog (Steve Patrick)

When things go wrong...

SQL pains..

SQL pains..

  • Comments 3

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

Leave a Comment
  • Please add 2 and 7 and type the answer here:
  • Post
  • Well, LINQ is much easier than dealing with the regular SQL APIs, so you may consider looking at that.

    If you want to use a datatable, you can enumerate over it to make the code bit easier:

    foreach (DataRow row in dt.Rows) {

       var cnt = (int)row[0];

       var str = row["columnname"].ToString();

       Console.Write("data = {0} : {1}\n", cnt, str);

    }

    Second, since you aren't doing anything with the data as a whole (just using it line-by-line), you may consider using ExecuteReader. This gives you a forward, read-only way of reading the data (versus datatable, which loads it all into memory at once). More performance can be obtained:

    using (var reader = myCommand.ExecuteReader()) {

       while (reader.Read()) {

           var cnt = reader.GetInt32(0);

           var str = reader.GetString(reader.GetOrdinal("somestring"));

           Console.Write("data = {0} : {1}\n", cnt, str);

       }

    }

    If you aren't going to use LINQ, I wrote a bit about consolidating these types of operations: http://www.atrevido.net/blog/2007/08/16/Practical+Functional+C+Part+III+Loops+Are+Evil.aspx

  • Ah I tried the reader  method but could not get the right format you shared:

    var cnt = reader.GetInt32(0);

    thanks!

    spat

  • 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.

Page 1 of 1 (3 items)