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`? https://stackoverflow.com/a/42419101/1188197 — EspressoBeans, Aug 20 '21 at 16:31
All the info you need right here: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ — mxmissile, Aug 20 '21 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, Aug 20 '21 at 17:04

score 1 · Answer 1 · answered Aug 20 '21 at 17:05

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"]
        };
    }
}

C# - How to capture multi count from sql

1 Answers1