I'm developing a C# WPF desktop application where I need to read/write to an SQL database (SQL server) regularly. Now I want to map the data from the database to objects in C#. I can't use Entity Framework so I'm doing all my data access through Dapper, slapper automapper and stored procedures.
As an example, I have modeled this sample database
The C# objects would look similar to this.
public class Manager {
public string Name { get; set; }
public string Phone { get; set; }
public List<Facility> Facilities {get; set;}
}
public class City {
public string Name { get; set; }
public string Description{ get; set; }
public List<Facility> Facilities {get; set;}
}
public class Facility {
public string Name { get; set; }
public string Description{ get; set; }
}
Now i query the Data from the Tables with Dapper in the underscore Notation from Slapper:
const string sql = @"SELECT
m.Name,
m.Phone,
m.ManagerId,
f.FacilityId As Facility_FacilityId,
f.WorkerCount As Facility_WorkerCount,
f.SquareFoot As Facility_SquareFoot,
f.Description As Facility_Description,
c.CityId AS Facility_City_CityId,
c.Name AS Facility_City_Name,
c.Description AS Facility_City_Description,
from Manager m
INNER JOIN dbo.Facility f ON m.ManagerId = f.ManagerId
INNER JOIN dbo.City c ON f.CityId = c.CityId";
using (System.Data.IDbConnection _connection = new System.Data.SqlClient.SqlConnection("Connstrint"))
{
var test = _connection.Query<dynamic>(sql);
Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(Facility), new List<string> { "FacilityId" });
Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(Manager), new List<string> { "ManagerId" });
Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(City), new List<string> { "CityId" });
var testNetwork = (Slapper.AutoMapper.MapDynamic<Manager>(test) as IEnumerable<Manager>).ToList();
System.Diagnostics.Debugger.Break();
}
I understand that the mapping cant fully work because facility cant go to City in the Objects. Should i map twice ? one time from Manager down then from City ? Or is there another design pattern that i should use?