C# - How to capture multi count from sql

Question

I get 5 results from this sql code

SELECT count(Location) as total, 
    sum(case when Location= 'Province_A' then 1 else 0 end) AS count_A,
    sum(case when Location= 'Province_A' then 1 else 0 end) AS count_B,
    sum(case when Location= 'Province_A' then 1 else 0 end) AS count_C,
    sum(case when Location= 'Province_A' then 1 else 0 end) AS count_D
FROM dataBase

How can I capture these 5 results in to int array

Depending on how you're interfacing with the database, maybe using List<dynamic>? stackoverflow.com/a/42419101/1188197 — EspressoBeans
– EspressoBeans, Commented Aug 20, 2021 at 16:31
All the info you need right here: learn.microsoft.com/en-us/dotnet/framework/data/adonet — mxmissile
– mxmissile, Commented Aug 20, 2021 at 16:33
That's a single result set with 1 row and 5 columns. It's like any other query. You can use raw ADO.NET, Dapper, Entity Framework or any other ORM mechanism — Flydog57
– Flydog57, Commented Aug 20, 2021 at 17:04

gjhommersom · Accepted Answer · 2021-08-20 17:05:20Z

1

const string queryString = @"
SELECT count(Location) as total, 
    sum(case when Location= 'Province_A' then 1 else 0 end) AS count_A,
    sum(case when Location= 'Province_B' then 1 else 0 end) AS count_B,
    sum(case when Location= 'Province_C' then 1 else 0 end) AS count_C,
    sum(case when Location= 'Province_D' then 1 else 0 end) AS count_D
FROM dataBase";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand(queryString, connection);
    using(SqlDataReader reader = command.ExecuteReader())
    {
        reader.Read();
        return new int[] {
            (int)reader["total"],
            (int)reader["count_A"],
            (int)reader["count_B"],
            (int)reader["count_C"],
            (int)reader["count_D"]
        };
    }
}

answered Aug 20, 2021 at 17:05

gjhommersom

1791 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# - How to capture multi count from sql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related