Getting a count(1) value to a list in C#

Question

I'm trying to get the count of a column in a table using a SQL query to a C# list

my sample code is below

"Select count(1) as Values, SM.Name from SM INNER JOIN Bill on SM.ID = Bill.AL INNER JOIN Details on Bill.ID = Details.ID"

I need to to add the count to a list<> Can some one tell me how to do that?

Since this count value doesn't have a column name I have given it the name "Values" I have tried to get the value as following code

 public IHttpActionResult Post(Brands brand)
        {
            DataTable dt = new DataTable();
            List<Brands> list = new List<Brands>();
            try
            {
                SqlConnection con =
                    new SqlConnection(
                        "MyConnectionString");
                con.Open();
                    var query = "Select count(1) as Values, SM.Name from SM INNER JOIN Bill on SM.ID = Bill.AL INNER JOIN Details on Bill.ID = Details.ID";

                SqlCommand com = new SqlCommand(query, con);   

                com.ExecuteNonQuery();
                con.Close();
                SqlDataAdapter adptr = new SqlDataAdapter(com);
                adptr.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    Brands GetAll = new Brands();
                   GetAll.Count = Convert.ToInt32(dt.Rows[i]["Values"]);
                    GetAll.Name = dt.Rows[i]["Name"].ToString();

                    list.Add(GetAll);
                }
            }
            catch (Exception e)
            {

            }
            return Ok(list);
        }
    }
}

COUNT takes a column name as the parameter. Not sure what you mean by Count(1) — Dmitry K., Apr 29 '16 at 04:04
Can you also update with a screen shot or something of the resulting Data in the DataTable? I'm assuming you are trying to loop over the rows and get the count value for each row. — Dmitry K., Apr 29 '16 at 04:11
I'm a beginner in C#. I dont know how to get the output of the data table. Sorry — Tharindu, Apr 29 '16 at 04:13
@Kason. I need the count to be passed in to the list. But it wont happen. Thats my problem — Tharindu, Apr 29 '16 at 04:14
Are you sure your SQL statement result is what you expected? If true, could you show more C# code? — Kason, Apr 29 '16 at 04:22
I have Edited the question with full code. Is this helpful @Kason — Tharindu, Apr 29 '16 at 04:27
Since the colum name is given in like 'Count(*) as Values' , by adding the name Value to the input in 'GetAll.Count = Convert.ToInt32(dt.Rows[i]["Values"]);' gives the resulting table? — Tharindu, Apr 29 '16 at 04:33
There is no error in this code. I need to push the count in to the list. I wont to know how to do that. At the moment the list returns 0 — Tharindu, Apr 29 '16 at 04:33
you can get the rows using this : dt.Rows[i][0]; which 0 is columns index — Kason, Apr 29 '16 at 04:34
I believe this line `Convert.ToInt32(dt.Rows[i]["Values"])` would not return 0. Does your SQL return no rows so that you never run the looping? — Kason, Apr 29 '16 at 04:36
When I use this code with Select Name or Select ID it works fine. I wont to add the count(1) value just like the above to the resulting table. — Tharindu, Apr 29 '16 at 04:38
Error CS0021 Cannot apply indexing with [] to an expression of type 'object' Is the error im getting when I add Convert.ToInt32(dt.Rows[i]["Values"]) — Tharindu, Apr 29 '16 at 04:39
@Kason Added it but nothing changed. Yes the loop runs but only once — Tharindu, Apr 29 '16 at 04:42
No no. Im just putting some brake points and getting the value by moving the cursor — Tharindu, Apr 29 '16 at 04:46
Now thats the problem. When I run the same query in SQL Server it gives a table as output. But here I get nothing — Tharindu, Apr 29 '16 at 04:48

score 0 · Answer 1 · answered Apr 29 '16 at 05:44

0

Thanks Every one for contributing to help me out. Special Thanks to @Kason. What I did was add Group by to SQL Query. That Solved the Issue. Hope this would be helpful to others.

answered Apr 29 '16 at 05:44

Tharindu

13
7

score -1 · Answer 2 · answered Apr 29 '16 at 06:07

-1

I appriciate the efforsts and suggestion for all helpers,but some comments are showing they have less knowledge of sql.

1)

you used column(1), please never ever use this syntax for query,this is bad practice to get data,always use name of column.

2)

Someone posted that count(1) is equals to count() than it's myth,because count() will provide count of all the rows where count(1) will provide only count of that which are not null. (Not believing try this in your system.)

keep this points in mind also suggest to others,hope you like this post.

answered Apr 29 '16 at 06:07

Sagar R

595
3
14

count(*) and count(1) also include null. – Kason Apr 29 '16 at 06:28
it may be your primary key field that why. – Sagar R Apr 29 '16 at 06:35
http://stackoverflow.com/questions/5179969/what-is-better-in-mysql-count-or-count1 if your sql knowledge is not enough, please do more research. – Kason Apr 29 '16 at 06:42
@Kason the issue was not because of count(1) or count(*). It was just because of not having group by key word at the end of the sql query and Thanks for the support. I will look into this as well. Cheers – Tharindu Apr 29 '16 at 07:57

Getting a count(1) value to a list in C#

2 Answers2

1)

2)