RepositoryBase methods for SqlCommand initialization - lobodava/artisan-orm GitHub Wiki

The RepositoryBase constructor:

  • finds a connection string,
  • creates an SqlConnection leaving it closed,
  • assignes the SqlConnection to its public Connection property.

So if your repository is inherited from the RepositoryBase and when you create a repository method, it has an access to the existing but closed Connection.

The are three methods in RepositoryBase that allow to initiate a SqlCommand and pass it as a parameter to Func or Action:

Sync methods
T GetByCommand<T>(Func<SqlCommand, T>)
void RunCommand(Action<SqlCommand>)
int ExecuteCommand(Action<SqlCommand>)
Async methods
async Task<T> GetByCommandAsync<T>(Func<SqlCommand, T>)
async Task RunCommandAsync(Action<SqlCommand>)
async Task<int> ExecuteCommandAsync(Action<SqlCommand>)

GetByCommand

Creates SqlCommand, passes it to Func<SqlCommand, T> argument as cmd parameter, returns a result of type T.

This method is used when we intend to get a result from a command.

Example:

public User GetUserById(int id)
{
    return GetByCommand( cmd =>  // here is just created SqlCommand 
    {
        // SqlConnection is still closed here
       
        // Configuring command with stored procedure name and parameters 
        cmd.UseProcedure("dbo.GetUserById"); 
        cmd.AddIntParam("@Id", id);         

        // SqlCommand extension cmd.ReadTo opens SqlConnection, 
        // reads data, closes SqlConnection and returns the result.
        var user = cmd.ReadTo<User>();
        
        // Func parameter is returning result here
        return user;
    });
}

RunCommand

Creates SqlCommand, passes it to Action<SqlCommand> argument as cmd parameter, returns nothing.

This method is used when we intend to set variables that are out of the command execution block.

Example:

public User GetById(int id)
{
    User user;
    byte[] roleIds;

    RunCommand( cmd =>   // here is just created SqlCommand 
    {
        // Configuring command with stored procedure name and parameters 
        cmd.UseProcedure("dbo.GetUserById");
        cmd.AddIntParam("@Id", id);

        // SqlCommand extension cmd.ExecuteReader opens SqlConnection, 
        // and pass SqlDataReader as an argument 
        cmd.ExecuteReader(reader =>
        {
            // reader reads two Result Sets here
            user    = reader.ReadTo<User>();
            roleIds = reader.ReadToArray<byte>();
        }
        // cmd.RunCommand closes SqlConnection here
        );
    }
    // RunCommand Disposes SqlCommand here
    );

    // Do some work outside RunCommand block
    // having released valuable resources such as a Connection
    if (user != null)
        user.Roles = ResolveRoles(roleIds);

    return user;
}

ExecuteCommand

Creates SqlCommand, passes it to Action<SqlCommand> argument as cmd parameter, returns int ReturnValue.

-- ReturnValue  is integer value which is retuned by SQL statement:
return 0;

This method is used when we intend to execute command without data reading.

Example:

public Boolean DeleteUser(Int32 userId)
{
    var returnValue = ExecuteCommand(cmd =>  // here is just created SqlCommand 
    {
        // SqlConnection is closed here

        // Configuring command with stored procedure name and parameters  
        cmd.UseProcedure("dbo.DeleteUser"); 
        cmd.AddIntParam("@UserId", userId); 

        // Do not call any executing methods here    
    }
    // Right after command configuration,
    // ExecuteCommand method opens SqlConnection, 
    // executes ExecuteNonQueryCommand 
    // closes SqlConnection and disposes SqlCommand 
    ); 

    return (returnValue == 0);
}

See also:

⚠️ **GitHub.com Fallback** ⚠️