Member Avatar for 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?

  • 2 Contributors
  • 2 Replies
  • 321 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by benkyma

Recommended Answers

All 2 Replies

Member Avatar for 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; } } }
Edited by sknake because: n/a
commented: V Helpful +0
Member Avatar for benkyma

Thats great, thanks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.