How to get object from database using ADO.NET?

Question

I want to check if user exists in database by username with SQL query using ADO.NET in ASP.NET WebAPI.

Like in Entity there is Any() call that does job.

private async Task<bool> UserExists(string username)
{
 return await context.Contacts.AnyAsync(x => x.UserId == username.ToLower());
}

if (await UserExists(createUserDto.Username)) 
     return BadRequest("Username is taken");

I want to do the same job but manually with SQL query and to store that object in var user and finally ask if there is a user in DB...(and to do other coding with that user).

I am passing CreateUserDtO which I want to create, to function with property string userId further in the body of getUserByUserName. UserId is string.

var user = await accountRepository.GetUserById(createUserDto.UserId);

I am opening connection, executing query, commit/rollback transaction and closing connection in GetUserById(string username). I just want to know how my query should be look like that returns object of that user.

public Task<bool> GetUserById(string username)
        {
            int result = 0;
            bool success = false;
            string usernameCheck = username.ToLower();

            try
            {
                OpenConnection();
                BeginTransaction();

                string query = "...";

                SqlCommand command = new SqlCommand(query, connection, transaction);
                result = "..."
            }
            catch (Exception ex)
            {
                _ = log.WriteLine("ERROR : " + ex.Message + "\nStackTrace : " + ex.StackTrace);
            }
            finally
            {
                if (result == 1)
                {
                    CommitTransaction();
                    success = true;
                }
                else
                {
                    RollbackTransaction();
                }
                CloseConnection();
            }

            return success;
        }

}

Is that possible?

You say SQL, but I see no SQL above. Are you sure you want an SQL language answer? — jarlh
– jarlh, Commented Sep 16, 2021 at 9:54
It sounds like you need an ADO.NET tutorial, not just a query. ADO.NET returns row results, not objects. This code has severe bugs already and it doesn't even execute any query — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 16, 2021 at 10:26
For example, connections are meant to be short-lived. Retrieving a single row doesn't need transactions at all. This code will leak connections and cause extensive blocking — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 16, 2021 at 10:27
Check Basics of ADO.NET to see how ADO.NET works. After you do that check Basics of Dapper to see how this can be simplified — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 16, 2021 at 10:29

Marc Gravell · Accepted Answer · 2021-09-16 10:26:26Z

Firstly, note that the SQL will vary by RDBMS; let's use SQL Server, for example; the query you want is probably:

select count(1)
from Users
where UserId = @userId

which will return the number of matching records, where @userId is the parameter we want. Now; the ADO.NET raw API is complex - honestly, your best bet may be to use either a raw SQL API on context.Something, or a tool like Dapper; for example, with Dapper:

using var connection = OpenConnection();

var count = await connection.QuerySingleAsync<int>(@"
select count(1)
from Users
where UserId = @userId", new { userId = username.ToLower() });

return count != 0;

Note that here I'm preserving the ToLower() semantics at the call site; everything in the new {...} is the parameters for Dapper pass to ADO.NET.

You can also do something similar with exists in SQL, if that is preferable; or you could select top 1 UserId and use QuerySingleOrDefaultAsync<string>(), and check it against null (the default if nothing is returned). This last approach has the advantage of also fetching the case-corrected version from the database, if you need it.

Collectives™ on Stack Overflow

How to get object from database using ADO.NET?

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