CODEWITHSUNDEEP
c#sqlado.net
NikolaCSI
NikolaCSI

Reputation: 31

How to get object from database using ADO.NET?

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?

Upvotes: 0

Views: 895

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063198

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.

Upvotes: 1

Related Questions