Unable to execute sql query in async

Question

I'm trying to execute the SQL query in async and want to return the result but it's not returning the data, not sure what I'm missing.

public async Task<string> AsyncSBSystemApps()
    {
        using (var scope = _scopeFactory.CreateScope())
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(@"SELECT 
                        a.app_guid as AppGuid,
                        a.name as AppName,
                        a.state as AppState,
                        a.created_at as AppCreatedAt,
                        a.updated_at as AppUpdatedAt,
                        a.foundation as AppFoundation,

                        s.name as SpaceName,
                        o.name as OrgName
                    FROM
                        apps as a
                    INNER JOIN
                        spaces as s ON a.space_guid = s.space_guid
                    INNER JOIN
                        organizations as o ON s.org_guid = o.org_guid
                    where s.name = 'system' and o.name = 'system' and a.foundation = 2", connection);

                await connection.OpenAsync();


                string result = (string)command.ExecuteScalar() ;

                return result;
            }
        }

Did you try this query with SSMS? Does it returns any data? Also, ExecuteScalar returns the first column from the first row. What do you expect this code to return? — Steve
– Steve, Commented Sep 4, 2019 at 21:47
Yes I tried in SSMS it does return data, I need everything that query executes — Sam
– Sam, Commented Sep 4, 2019 at 21:50
First, you need a model class that represent the data returned by this query. Then, instead of ExecuteScalar you need ExecuteReader. Using the reader you read row by row the returned data and create an instance of the model class. Each instance should be added to a List of that class. Finally you can serialize the list into as json string with the appropriate library. — Steve
– Steve, Commented Sep 4, 2019 at 21:57
There is no straight away mode to transform the rows returned by a query in a single string. Perhaps you could start to use an ORM tool like Dapper that will simplify a lot the transformation of the raw data in object instances. Still you need to serialize in json the result — Steve
– Steve, Commented Sep 4, 2019 at 22:03

Xueli Chen · Accepted Answer · 2019-09-06 09:47:57Z

If you want to use FOR JSON PATH to maintain full control over the format of the JSON output , you could add it behind the sql query and deserialize json , the following is a example I tested

public class ResponseBody
    {
        public string StudioName { get; set; }
        public string CityName { get; set; }
        public string Street { get; set; }
    }

    public async Task<IActionResult> AsyncSBSystemApps()
    {
        using (var scope = _scopeFactory.CreateScope())
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(@"SELECT 
                    s.Name as StudioName,

                    c.Name as CityName,
                    a.Street as Street
                FROM
                    Studios as s
                INNER JOIN
                    Cities as c ON s.Id = c.StudioId
                INNER JOIN
                    Addresses as a ON c.Id = a.CityId
                where s.Name = 'Studio1' and c.Name = 'Wuxi' and a.Number = '101'
                For JSON PATH  ", connection);

                await connection.OpenAsync();

                var result = command.ExecuteScalar().ToString();

                 // Deserializing json data to object 
                var getModel = JsonConvert.DeserializeObject<List<ResponseBody>>(result);

                //return ...
            }
        }

    }

As mason suggested , you could also use Dapper(Install Dapper package ) to get the query easily like below :

using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                var queryResult = connection.Query<ResponseBody>(@"SELECT 
                    s.Name as StudioName,

                    c.Name as CityName,
                    a.Street as Street
                FROM
                    Studios as s
                INNER JOIN
                    Cities as c ON s.Id = c.StudioId
                INNER JOIN
                    Addresses as a ON c.Id = a.CityId
                where s.Name = 'Studio1' and c.Name = 'Wuxi' and a.Number = '101'");

                return queryResult ;
            }

Collectives™ on Stack Overflow

Unable to execute sql query in async

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related