CODEWITHSUNDEEP
c#
Kampol Udomkacha
Kampol Udomkacha

Reputation: 1

C# - How to capture multi count from sql

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

Upvotes: 0

Views: 115

Answers (1)

gjhommersom
gjhommersom

Reputation: 179

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

Upvotes: 1

Related Questions