Count distinct values

[benkyma]

I need to query a database of application titles and count the instances of each title. I've already got a list<string> of distinct values from a previous query, and now I need to count how many times each value occurs. I'm not sure if the query is correct, or how to handle the output. So far I have:

(cands is my list of distinct titles)
 for (int i = 0; i < cands.Count; i++)
            {
                SqlCommand appCommand = new SqlCommand("select count(title) from poss_titles where title = @thisTitle", appConnection);
                appCommand.Parameters.AddWithValue("@thisTitle", cands[i]);

                
                SqlDataReader reader = appCommand.ExecuteReader();

I have created an 'AppTitle' class with getters and setters for 'name' and 'instances' so that I can return a List<AppTitle> from this method. Maybe there is a better way than this?

[sknake]

You can get all that information in one pass:

Select Title, Count(*) As Cnt
From poss_titles
Group By Title
Order By Title

Here is one way you can store it. This is really only beneficial if you are looking up based on the _exact_ title match:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace daniweb
{
  public class Title
  {
    public string TitleName { get; set; }
    public int Count { get; set; }
    public Title()
    {
    }
    public Title(string TitleName, int Count)
      : this()
    {
      this.TitleName = TitleName;
      this.Count = Count;
    }
  }

  public static class TitleLoader
  {
    public static Dictionary<string, Title> LoadTitles()
    {
      Dictionary<string, Title> result = new Dictionary<string, Title>();
      DataTable dt= null;
      try
      {
        using (SqlConnection conn = new SqlConnection("conn string"))
        {
          conn.Open();
          using (SqlCommand cmd = new SqlCommand("query", conn))
          {
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
              dt = new DataTable();
              dt.Load(dr);
            }
          }
        }
        foreach (DataRow row in dt.Rows)
        {
          Title t = new Title(Convert.ToString(row["Title"]), row["Cnt"] is DBNull ? 0 : Convert.ToInt32(row["Cnt"]));
          result.Add(t.TitleName, t);
        }
      }
      finally
      {
        if (dt != null)
        {
          dt.Dispose();
          dt = null;
        }
      }
      return result;
    }
  }
}

[benkyma]

Thats great, thanks.