2

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

enter image description here

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?

maxiangelo
  • 125
  • 12

0 Answers0