The Sample

Let's say we have database tables Users and UserRoles (TSQL):

create table dbo.Users
(
    Id       int           not null	  identity primary key clustered,
    [Login]  varchar(20)   not null	  ,
    Name     nvarchar(50)  not null	  ,
    Email    varchar(50)   not null
);

create table dbo.UserRoles
(
    UserId   in       not null   foreign key (UserId) references dbo.Users (Id),	
    RoleId   tinyint  not null   foreign key (RoleId) references dbo.Roles (Id),

    primary key clustered (UserId, RoleId)
);

// The Roles table is just a dictionary or lookup table and users don't edit it.

And we have a User class (C#):

public class User
{
    public Int32   Id       { get; set; }
    public String  Login    { get; set; }
    public String  Name     { get; set; }
    public String  Email    { get; set; }
    public Byte[]  RoleIds  { get; set; }
}

In order to read and save the User data in Artisan.Orm way it is required to create:

• mapper static vlass (C#)
• user-defined table type (TSQL)
• stored procedures (TSQL)

Mapper static class (C#) is decorated with MapperFor attribute and consists of four static methods with reserved names:

• CreateObject
• CreateObjectRow
• CreateDataTable
• CreateDataRow

[MapperFor(typeof(User)]
public static class UserMapper 
{
    public static User CreateObject(SqlDataReader dr)
    {
        var i = 0;

        return new User 
        {
            Id     =  dr.GetInt32   (i)   ,
            Login  =  dr.GetString  (++i) ,
            Name   =  dr.GetString  (++i) ,
            Email  =  dr.GetString  (++i)
        };
    }

    public static ObjectRow CreateObjectRow(SqlDataReader dr)
    {
        var i = 0;

        return new ObjectRow(4)
        {
            /* 0 - Id      =  */  dr.GetInt32   (i)   ,
            /* 1 - Login   =  */  dr.GetString  (++i) ,
            /* 2 - Name    =  */  dr.GetString  (++i) ,
            /* 3 - Email   =  */  dr.GetString  (++i)
        };
    }

    public static DataTable CreateDataTable()
    {
        return new DataTable("UserTableType")

            .AddColumn< Int32  >( "Id"    )
            .AddColumn< String >( "Login" )
            .AddColumn< String >( "Name"  )
            .AddColumn< String >( "Email" );
    }

    public static Object[] CreateDataRow(User obj)
    {
        if (obj.Id == 0) 
            obj.Id = Int32NegativeIdentity.Next;

        return new object[]
        {
            obj.Id     ,
            obj.Login  ,
            obj.Name   ,
            obj.Email
        };
    }
}

User-defined table type (TSQL):

create type dbo.UserTableType as table
(
    Id       int           not null   primary key clustered,
    [Login]  varchar(20)   not null   ,
    Name     nvarchar(50)  not null   ,
    Email    varchar(50)   not null
);

Stored procedures (TSQL):

create procedure dbo.GetUserById
    @Id    int
as
begin
    set nocount on;

    -- read User

    select
        Id       ,
        [Login]  ,
        Name     ,
        Email
    from
        dbo.Users
    where
        Id = @Id;
        
    -- read User RoleIds
        
    select
        RoleId
    from
        dbo.UserRoles
    where
        UserId = @Id;
        
end;

create procedure dbo.SaveUser
    @User     dbo.UserTableType       readonly,
    @RoleIds  dbo.TinyIntIdTableType  readonly
as
begin
    set nocount on;
    
    declare @UserIds table ( InsertedId int primary key, ParamId int unique);

    begin -- save User

        merge into dbo.Users as target
            using 
            (
                select
                    Id       ,
                    [Login]  ,
                    Name     ,
                    Email
                from
                    @User
            ) 
            as source on source.Id = target.Id

        when matched then
            update set
                [Login]  =  source.[Login]  ,
                Name     =  source.Name     ,
                Email    =  source.Email    

        when not matched by target then                                                         
            insert (    
                [Login]  , 
                Name     ,
                Email    )
            values (
                source.[Login]  , 
                source.Name     , 
                source.Email    )
    
        output          inserted.Id , source.Id
        into @UserIds ( InsertedId  , ParamId   );

    end; 

    begin -- save UserRoles
            
        merge into dbo.UserRoles as target
            using 
            (
                select
                    UserId  =  ids.InsertedId,
                    RoleId  =  r.Id
                from 
                    @User u
                    inner join @UserIds ids on ids.ParamId = u.Id
                    cross join @RoleIds r
            )
            as source on source.UserId = target.UserId and source.RoleId = target.RoleId

        when not matched by target then                                                         
            insert (    
                UserId  ,
                RoleId  )
            values (
                source.UserId  ,
                source.RoleId  )

        when not matched by source and target.UserId in (select InsertedId from @UserIds) then    
            delete;
    end;

end;

Having prepared mapper, user-defined table type and stored procedures we can write the UserRepository (C#):

public class UserRepository: Artisan.Orm.RepositoryBase
{
    public User GetById(int id)
    {
        return GetByCommand(cmd =>
        {
            cmd.UseProcedure("dbo.GetUserById");

            cmd.AddIntParam("@Id", id);

            return cmd.GetByReader(reader =>
            {
                var user     = reader.ReadTo<User>();
                user.RoleIds = reader.ReadToArray<byte>();            
                
                return user;
            });

        });
    }

    public void Save(User user)
    {
        ExecuteCommand(cmd =>
        {
            cmd.UseProcedure("dbo.SaveUser");

            cmd.AddTableParam( "@User"    , user         );
            cmd.AddTableParam( "@RoleIds" , user.RoleIds );
        });
    }
}

More examples of the Artisan.Orm usage are available in the Tests and Database projects.