Csla plus ADO.NET and stored procs

[Chicagoan2016]

Hi,
Fellow members who use ADO.NET and stored procs for data access, what do you use for mapping stored proc's parameters to data transfer objects?
Code like this could be tedious

  cmobj.Parameters.AddWithValue("@sampleID", data.sampleID);

We have an old(er) project (at least 10 years old) that uses Enterprise library data access block and I could see mapping of data transfer object to stored procs parameters there.

Regards

[saulyt]

I'm using Dapper in a couple of projects. Pretty satisfied.

[Chicagoan2016]

@saulyt would love to see some sample code, how could we not write every line like I mentioned in my question?

[saulyt]

I'm using reflection to set the parameters.

public static class ParameterExtensions
{
    public enum CommandType
    {
        Insert,
        Update,
        Delete
    }

    public static DynamicParameters AddInsertParameters<T,TKey>(this T incoming) where T : BaseEditableEntity<TKey>
    {
        DynamicParameters dynamicParameters;
        dynamicParameters = new DynamicParameters(new
        {
            ConcurrencyToken = new byte[8],
            DateCreated = DateTime.Now,
            ID = default(TKey),
            UserCreated = incoming.UserCreated
        });
        AddeDerivedClassProperties(incoming, ref dynamicParameters, CommandType.Insert);
        dynamicParameters.Output<T>(incoming, i => i.ConcurrencyToken);
        dynamicParameters.Output<T>(incoming, i => i.ID);
        dynamicParameters.Output<T>(incoming, i => i.DateCreated, DbType.DateTime2);
        return dynamicParameters;
    }

    public static DynamicParameters AddUpdateParameters<T, TKey>(this T incoming) where T : BaseEditableEntity<TKey>
    {
        DynamicParameters dynamicParameters;
        dynamicParameters = new DynamicParameters(new
        {
            ConcurrencyToken = incoming.ConcurrencyToken,
            UserModified = incoming.UserModified,
            ID = incoming.ID,
            DateModified = DateTime.Now,
            Void = incoming.Void

        });
        AddeDerivedClassProperties(incoming, ref dynamicParameters, CommandType.Update);
        dynamicParameters.Output(incoming, i => i.DateModified, DbType.DateTime2);
        dynamicParameters.Output<T>(incoming, i => i.ConcurrencyToken);
        return dynamicParameters;
    }

    private static void AddeDerivedClassProperties<TKey>(BaseEditableEntity<TKey> entity, ref DynamicParameters dynamicParameters, CommandType commandType)
    {
        SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTime2);
        SqlMapper.AddTypeMap(typeof(DateTime?), System.Data.DbType.DateTime2);
        var baseProps = typeof(BaseEditableEntity<TKey>).GetProperties();
        var props = entity.GetType().GetProperties().Where(p => !baseProps.Any(b => b.Name == p.Name)
                                                               && p.Name != entity.EntityIDName
                                                               && p.PropertyType.FullName.StartsWith("System.") //hacky to avoid user defined
                                                               && !(
                                                                     (commandType == CommandType.Insert && (p.GetCustomAttribute(typeof(IgnoreOnInsertAttribute)) != null || p.GetCustomAttribute(typeof(IgnoreOnInsertAndUpdateAttribute)) != null))
                                                                     || (commandType == CommandType.Update && (p.GetCustomAttribute(typeof(IgnoreOnUpdateAttribute)) != null || p.GetCustomAttribute(typeof(IgnoreOnInsertAndUpdateAttribute)) != null))
                                                                    )
                                                               ).ToList(); 
        foreach (PropertyInfo property in props)
        {
            object value = GetPropValue(entity, property.Name);
            var name = property.Name;
            dynamicParameters.Add(property.Name, value);
        }

    }

    private static object GetPropValue(object src, string propName)
    {
        return src.GetType().GetProperty(propName)?.GetValue(src, null);
    }
}

[TheCakeMonster]

I largely code generate mine in new repositories, and then maintain it manually after that. Maintaining it is occasionally problematic, as it is possible to forget when you add a field. It's not perfect, but generally it works OK.

I would want to be slightly cautious of anything too automated, as that's the boundary to the database. For example, while I could use nameof() in the generated code, I'm not sure I would. I wouldn't want anyone to break my code by renaming a DTO property, as they may choose not to make the same - potentially breaking - change in the stored procedures or the objects beneath the SPs.

[GillesBer]

code generate mine

We also created a (initially dummy, but no more now) T4 file to generate an .NET method wrapper for each SQL procedure.
It does the job pretty well, generating all the parameters, then calling a shared "ExecuteStoredProc" coming from Enterprise Library, and managing output parameters.

We started picking them one by one in bugfix, then after some time noticed it was saving so much time / fixing so many complex bug (such as nvarchar size mismatch, ...) that after some time we migrated all our code.

When something is not implemented, we still have a clean starting point, to evolve our T4.
This is the only 'generated' code that I always commit, to ease identifying possible regressions!

[Chicagoan2016]

@GillesBer you folks use Enterprise Library?
I am/was a huge fan of patterns and practices group.

[saulyt]

I'll be offline until Sunday. I'll try to send you something then. If I remember correctly I am using reflection. I have an ignore attribute for the few times I don't want a property to be added as a parameter.

…

On Fri, Jun 17, 2022, 6:10 PM Chicagoan2016 ***@***.***> wrote: @saulyt <https://github.com/saulyt> would love to see some sample code, how could we not write every line like I mentioned in my question? — Reply to this email directly, view it on GitHub <#2992 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJLAXO3AGNSH2WIE35TWAA3VPTZULANCNFSM5ZDGW7WQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>