Commands

Commands

Command creation

• CreateSqlCommand
• CreateStoredProcedureCommand
• CreateCommand

DbCommand extensions methods (allow to chain registration)

• AddInParameter
• AddOutParameter
• AddParameter

Execution

• SelectAllAsync returns a list of Models
• SelectOneAsync returns a Model
• InsertAsync returns the new Id (for model with primary key auto incremented for example) or the number of rows affected
• UpdateAsync returns the number of rows affected
• DeleteAsync returns the number of rows affected
• CountAsync returns a number
• ExecuteScalarAsync returns an object
• ExecuteNonQueryAsync returns the number of rows affected

Transactions

• ExecutePendingOperationsAsync
• ExecuteTransactionFactoryAsync

Best practice use Factories

• ISelectionAllCommandFactory
• ISelectionOneCommandFactory
• IInsertCommandFactory
• IUpdateCommandFactory
• IDeleteCommandFactory
• TransactionFactory
• IModelFactory

Plus

• DeepClone allows to clone object and preserve original values for example

Select All

var db = new EasyDb();
db.SetConnectionStringSettings(Constants.ConnectionString, Constants.ProviderName);

db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;

// arguments:
// - limit
// - condition
// - sorts
var result = await db.SelectAllAsync<User>(10, Check.Op("Age", ">", 18), new string[] { "UserName DESC" });

Or with a command

var db = new EasyDb();
db.SetConnectionStringSettings(Constants.ConnectionString, Constants.ProviderName);

db.SetTable<User>("users")
    .SetPrimaryKeyColumn("id", p => p.Id);

using (var command = db.CreateSqlCommand("select * from [users]"))
{
    var users = await db.SelectAllAsync<User>(command);
}

Or with factories

• Command factory

public class UserSelectionAllCommandFactory : ISelectionAllCommandFactory<User, NullCriteria>
{
    public DbCommand CreateCommand(EasyDb db, NullCriteria criteria)
    {
        return db.CreateSqlCommand("select * from [User]");
    }
}

Note: Criteria allows to pass a value (a filter, a key, etc.) or a class

• Model factory (data reader => model)

public class UserModelFactory : IModelFactory<User>
{
    public User CreateModel(IDataReader reader, EasyDb db)
    {
        return new User
        {
            UserName = ((string)reader["UserName"]).Trim(),
            Email = reader["Email"] == DBNull.Value ? default(string) : ((string)reader["Email"]).Trim(),
            Age = reader["Age"] == DBNull.Value ? default(int?) : (int)reader["Age"]
        };
    }
}

Or with CheckDBNullAndConvertTo function

public class UserModelFactory : IModelFactory<User>
{
    public User CreateModel(IDataReader reader, EasyDb db)
    {
        return new User
        {
            UserName = ((string)reader["UserName"]).Trim(),
            Email = db.CheckDBNullAndConvertTo<string>(reader["Email"])?.Trim(),
            Age = db.CheckDBNullAndConvertTo<int?>(reader["Age"])
        };
    }
}

Execution

var db = new EasyDb();
db.SetConnectionStringSettings(Constants.ConnectionString, Constants.ProviderName);

db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;

var result = await db.SelectAllAsync<User>(new UserSelectionAllFactory(), new UserModelFactory());

Select One

Example:

var db = new EasyDb();
db.SetConnectionStringSettings(Constants.ConnectionString, Constants.ProviderName);

db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;

Post result = null;

using (var command = db.CreateSqlCommand("select a.Title, a.Content, a.UserId, b.UserName from [Post] as a,[User] as b where a.UserId=b.Id and a.Id=@id").AddInParameter("@id", 2))
{
    result = await db.SelectOneAsync<Post>(command, (reader, idb) =>
    {
        return new Post
        {
            Title = ((string)reader["Title"]).Trim(),
            Content = ((string)reader["Content"]).Trim(),
            UserId = (int)reader["UserId"],
            // populate relation object
            User = new User
            {
                UserName = ((string)reader["UserName"]).Trim()
            }
        };
    });
}

Relations, 2 possibilities:

• Create one sql query with aliases on table and columns and populate relation objects with data reader
• ... Or use EasyDb to get each relation (multiple queries/ server requests)

Examples:

1-1 relation

var db = new EasyDb();

// connection + mapping
            
// 1 post => 1 user (author)
var post = await db.SelectOneAsync<Post>(Check.Op("Id", 1));
post.User = await db.SelectOneAsync<User>(Check.Op("Id", post.UserId));

Note: for 0-1 relation, use a nullable with the "foreign key property" (example "UserId")

0-n relation

var db = new EasyDb();

// connection + mapping

// 1 user => 0-n posts
var user = await db.SelectOneAsync<User>(Check.Op("Id", 1));
user.Posts = await db.SelectAllAsync<Post>(null, Check.Op("UserId", user.Id));

Insert

var db = new EasyDb();
db.SetConnectionStringSettings(Constants.ConnectionString, Constants.ProviderName);

db.DefaultMappingBehavior = DefaultMappingBehavior.CreateEmptyTable;

var user = new User
{
    UserName = "Marie"
};
var newId = await db.InsertAsync<User>(user);

With command factory

public class UserInsertFactory : IInsertCommandFactory<User>
{
    public DbCommand CreateCommand(EasyDb db, User model)
    {
        return db.CreateSqlCommand("insert into [User](UserName) output inserted.id values(@username)")
                .AddInParameter("@username", model.UserName);
    }

    public void SetNewId(DbCommand command, User model, object result)
    {
        model.Id = (int)result;
    }
}

var db = new EasyDb();
db.SetConnectionStringSettings(Constants.ConnectionString, Constants.ProviderName);

var user = new User
{
    UserName = "Marie"
};
var result = await db.InsertAsync<User>(new UserInsertFactory(), user);

Update

var db = new EasyDb();
db.SetConnectionStringSettings(Constants.ConnectionString, Constants.ProviderName);

var user = new User
{
    Id = 2,
    UserName = "UserName updated"
};
var rowsAffected = await db.UpdateAsync<User>(user, Check.Op("Id", user.Id));

Delete

var db = new EasyDb();
db.SetConnectionStringSettings(Constants.ConnectionString, Constants.ProviderName);

var rowsAffected =  await db.DeleteAsync<User>(Check.Op("Id", 3));

Transactions

Transaction Factory

Allows to create quickly transaction and not have to define "BeginTransaction" or "transaction scope", open connection, etc.

Example

public class MyTransactionOperations : TransactionFactory
{
    public override async Task ExecuteAsync(EasyDb db)
    {
        try
        {
            await db.InsertAsync<User>(new User { UserName = "New user 1" });
            await db.InsertAsync<User>(new User { UserName = "New user 2" });
            await db.InsertAsync<User>(new User { UserName = "New user 3" });
        }
        catch (System.Exception ex)
        {
            // throw exception to rollback 
            throw ex;
        }
    }
}

Execution

var db = new EasyDb();

// connection + mapping ...

var succcess = await db.ExecuteTransactionFactoryAsync(new MyTransactionOperations()); // true or false

Pending transaction

Allows to register actions, and execute all actions on demand

Example:

var db = new EasyDb();

// connection + mapping ...

// 3 operations
db.AddPendingOperation(() => db.InsertAsync<User>(new User { UserName = "New user 1" }));
db.AddPendingOperation(() => db.InsertAsync<User>(new User { UserName = "New user 2" }));
db.AddPendingOperation(() => db.InsertAsync<User>(new User { UserName = "New user 3" }));

// execution
var success = await db.ExecutePendingOperationsAsync(); // if success, operations are clear

Stored procedure

Example:

var db = new EasyDb();
db.SetConnectionStringSettings(Constants.ConnectionString, Constants.ProviderName);

using (var command = db
            .CreateStoredProcedureCommand<User>("GetUser")
            .AddInParameter("@id", 1))
        {
            var user = await db.SelectOneAsync<User>(command);
        }

Output Parameters

For example with MySQL:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_output_age` (IN `p_id` INT, OUT `p_age` INT)  NO SQL
select age into p_age from users where id=p_id$$
DELIMITER ;

var db = this.GetDb();

// connection + mapping ...

using (var command = db.CreateStoredProcedureCommand<User>("get_output_age")
            .AddInParameter("p_id", 2)
            .AddOutParameter("p_age"))
        {
            await db.SelectOneAsync<User>(command);
            var result = command.Parameters["p_age"];
        }

Functions

For example with MySQL:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_username_function` (`p_id` INT) RETURNS VARCHAR(255) CHARSET latin1 NO SQL
begin
DECLARE result varchar(255);
select username into result from users where id=p_id;
return result;
end$$
DELIMITER ;

using (var command = db.CreateSqlCommand("SELECT `get_username_function`(@p0) AS `get_username_function`;")
            .AddInParameter("@p0", 2)
            .AddParameter("get_username_function", null, ParameterDirection.ReturnValue))
        {
            // Return value
            var result = await db.ExecuteScalarAsync(command);
        }