'How do I add data from a string field to the model as a property?

I am separating a string field I have below by splitting it.

String : D837D323-40A0-1EDD-F365-A55C43725DE0|SPECODE,10A4F529-CDFB-7553-0D05-7C9A36A721F8|SPECODE2,BCAA7E77-DF1D-3135-AD4C-823CEC02ED56|SPECODE3,24CC7FBE-5F43-67FC-7998-73869E2B6D5B|SPECODE4,DF15B062-3072-E3A3-A268-F2DEC8CF947B|SPECODE5,F56355C9-CCF6-095E-F697-DE36BAF464A7|CYPHCODE

Split & UnitOfWork :

using (var dbFactory = new DbFactory()){

    var specialCodeList = new List<SpecialCodeModel>();
    string[] specialCode = _settings.SpecialCode.Split(',');

    for (int i = 0; i < specialCode.Length; i++)
    {
        int position = specialCode[i].IndexOf('|');
        specialCodeList.Add(new SpecialCodeModel
        {
            EkGuid = specialCode[i].Substring(0, position),
            ErpProperty = specialCode[i].Substring(position + 1),
            SystemTypeName = "System.String"
        });
    }

    var factory = new DynamicTypeFactory();
    var extendedType = factory.CreateNewTypeWithDynamicProperties(typeof(CustomerDto),
        specialCodeList);

    // Get all read/write properties for the extended Type.
    var properties = extendedType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                                 .Where(p => p.CanRead && p.CanWrite);



    var connection = dbFactory.GetConnection(_settings);
    _settings = GetLogoFirmInfo(parameters, _settings, connection);

    string query = SetLogoDbSettings(LogoSqlCommands.CustomerListCountCmd(), parameters, _settings);
    int dataCount = connection.QueryFirstOrDefault<int>(query);

    query = SetLogoDbSettings(LogoSqlCommands.CustomerListCmd(_settings), parameters, _settings);
    var data = connection.Query<CustomerDto>(query);

    return QueryResult<IEnumerable<CustomerDto>>.CreateResult(data, dataCount, parameters.PagingLimit, parameters.PagingOffset);
}

Then I assign SpecialCodeModel to this model. What I want to do is to automatically add the EkGuid fields to the CustomerDto model as a System.String property. Then I add a query using dapper with ErpProperty.The query formed as EKGuid AS ErpProperty should bring me a full CustomerDto.Of course, with the fields, I added specifically. I tried something, it added to the model, but the dapper did not fill the fields :( I need help with this.

c++sharpsql.netdapperunit-of-work


Solution 1:[1]

Idea: Construct a SQL command by using a fixed set of aliases that can be mapped by Dapper:

SELECT
    [D837D323-40A0-1EDD-F365-A55C43725DE0] AS Column1,
    [10A4F529-CDFB-7553-0D05-7C9A36A721F8] AS Column2
    ...
FROM mytable

Now, you can create a class having a fixed set of properties

public class Data
{
    public string Column1 { get; set; }
    public string Column2 { get; set; }
    ...
}

The SQL command can be constructed like this:

var specialCodes = _settings.SpecialCode.Split(',')
    .Select(s => {
        string[] parts = s.Split('|');
        return new SpecialCodeModel {
            EkGuid = parts[0],
            ErpProperty = parts[1],
            SystemTypeName = "System.String"
        };
    });


var selectList = specialCodes
    .Select((s, i) => $"[{s.EkGuid}] AS Column{i + 1}");

string sql = $"SELECT\r\n  {String.Join(",\r\n  ", selectList)}\r\nFROM myTable";

Console.WriteLine(sql);

With the string from above it creates this output:

SELECT
  [D837D323-40A0-1EDD-F365-A55C43725DE0] AS Column1,
  [10A4F529-CDFB-7553-0D05-7C9A36A721F8] AS Column2,
  [BCAA7E77-DF1D-3135-AD4C-823CEC02ED56] AS Column3,
  [24CC7FBE-5F43-67FC-7998-73869E2B6D5B] AS Column4,
  [DF15B062-3072-E3A3-A268-F2DEC8CF947B] AS Column5,
  [F56355C9-CCF6-095E-F697-DE36BAF464A7] AS Column6
FROM myTable

Make sure to escape the column names the appropriate way for the DB type ([] for SQL-Server, "" for Oracle, etc.).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions