CODEWITHSUNDEEP
c#asp.netsql-serverstored-proceduresdapper
PixelPaul
PixelPaul

Reputation: 2777

C# remove SQL from method and use stored procedure & Dapper

I'm trying to convert a method to use a stored procedure with Dapper. But I'm quite confused with what's going on with var result =...

public Task<TUser> FindByIdAsync(Guid userId)
{
    var sql = @"SELECT *
                FROM IdentityUser
                WHERE UserId = @USERID";

    using (var connection = new SqlConnection(_connection))
    {
        var result = connection.Query<TUser, IdentityProfile, TUser>(sql, (user, profile) => { user.Profile = profile;
                             return user; }, 
                          new { userId }, splitOn: "UserId").SingleOrDefault();

        return Task.FromResult(result);
     }
}

Here is what I have:

public Task<TUser> FindByIdAsync(Guid userId)
{
    using (var connection = new SqlConnection(_connection))
    {
        var param = new DynamicParameters();
        param.Add("@UserId", userId);

        return Task.FromResult(connection.Query("IdentityGetUserById", param, commandType: CommandType.StoredProcedure).SingleOrDefault());
    }
}

Upvotes: 0

Views: 302

Answers (1)

Mrinal Kamboj
Mrinal Kamboj

Reputation: 11478

But I'm quite confused with what's going on with var result =...

Needn't be confused this is simple Dapper functionality

Let me explain what the following code does: 

var result = connection.Query<TUser, IdentityProfile, TUser>
                        (sql, (user, profile) => 
                        { 
                             user.Profile = profile;
                             return user; 
                        }, new { userId }, 
                       splitOn: "UserId").SingleOrDefault();
  1. User is a complex type with Profile type part of it
  2. Result on executing the Sql query is a combined tabulated data (with columns for both, User and Profile), which is automatically split at the column UserId and then it represent the Profile type, which is filled in the User type, as per logic user.Profile = profile
  3. Since query results in IEnumerable<TUser>, therefore SingleOrDefault is called, which returns data if Single record is returned or returns null
  4. This is a standard mechanism to automatically bind complex types

Now same kind of code is expected when you run the stored procedure, IdentityGetUserById, only changes are replace real Sql by stored procedure name (done), tell Dapper that you are executing stored procedure (done), you have already bind the parameters using DynamicParameter, which can also be anonymous type. Result will be in a similar format and you can do exactly same binding, check the following code, with few modifications:

using (var connection = new SqlConnection(_connection))
{
        var result = connection.Query<TUser, IdentityProfile, TUser>
        ("IdentityGetUserById", 
         commandType: CommandType.StoredProcedure,
         (user, profile) => 
                        { 
                           user.Profile = profile;
                           return user; 
                        }, new { userId }, 
                       splitOn: "UserId").SingleOrDefault();

          return Task.FromResult(result);
         );
}

In fact, I have removed Dynamic Parameters and used the Anonymous type to ensure that it is consistent with the Sql code

Upvotes: 2

Related Questions