This is a more detailed example question that I asked for: Best Practice with classes and the database
I'm using c# with Sql Server and using Dapper as my ORM.
Here is my class that will load the Component table into the application:
class DBComponent { public int ID { get; set; } public string Component { get; set; } public int Material_ID { get; set; } public int Color_ID { get; set; } }
Then I need my other class that will have the actual values:
class Component { public int ID { get; set; } public string Component { get; set; } public string Material { get; set; } public string Color { get; set; } public Component(DBComponent C) { ID = C.ID; Component = C.Component; Material = //queries the Material Table passing in C.Material_ID and returning the Material Value Color = //queries the Color Table passing in C.Color_ID and returning the Color Value } }
The reason I'm doing this is so that I can use the values for a WinForm application with controls(combobox) and other needs. Also the "DBComponent" Class would have a method that would take a "Component" object and create a "DBComponent" object which would be sent back to the database as a new record.
Is this the best way to handle this situation? Or is there a better method? In my other post someone mentioned that dapper can do this by itself to where I won't need to create 2 classes, only need 1 class. How is that so?
You could use just one class and just one query to load your data. It is just a matter to build the correct sql query and let Dapper do its magic in mapping the data retrieved to your Component class.
Suppose you change your component class like this
public class Component { public int ID { get; set; } public string ComponentX { get; set; } public int Color_ID { get; set; } public int Material_ID { get; set; } public string Material { get; set; } public string Color {get;set;} }
now you can retrieve your data using a proper join between the tables
IEnumerable<Component> SelectComponents() { using (IDbConnection connection = OpenConnection()) { const string query = @"SELECT p.ID, p.Component as ComponentX, p.Color_ID, p.Material_ID, c.Color, m.Material FROM Component p JOIN Color c on p.Color_ID = c.ID JOIN Material m on p.Material_ID = m.ID"; return connection.Query<Component>(query, null); } }
Notice that I have renamed the member Component to ComponentX because you can't have a member name with the same name of the enclosing type