Build object from database without a discriminator column?

Question

I currently have a basic application that has a database backing it. I originally connected to the database with Entity Framework, but my tutor has requested from me to recreate the functionality with ADO.NET SQL. This has gone well so far using the SqlDataReader, however I have come across a problem.

My database stores a list of animals, the animals are Dog, Cat and Mouse, they all inherit from the Animal class. With entity framework when I wanted to get an animal from the database, it used the discriminator column automatically and I didn't have to worry about any logic to detect what type of animal the animal was. However so far the only way I can think of doing this with SQL is to manually check the discriminator column.

This works well for the moment but if I start adding say a hundred animals, it will become an impossible task to keep it working efficiently. My question is, is there another way to do this which is more elegant and will scale appropriately? The code for how I currently do the task is below:

if (reader.HasRows)
 {
 while (reader.Read())
 {
 if (reader.GetString(3) == "Dog")
 {
 list.Add(
 new Dog()
 {
 AnimalID = (int)reader.GetInt32(reader.GetOrdinal("AnimalID")),
 Name = (string)reader.GetString(reader.GetOrdinal("Name")),
 Age = (int)reader.GetInt32(reader.GetOrdinal("Age")),
 });
 }
 if (reader.GetString(3) == "Cat")
 {
 list.Add(
 new Cat()
 {
 AnimalID = (int)reader.GetInt32(reader.GetOrdinal("AnimalID")),
 Name = (string)reader.GetString(reader.GetOrdinal("Name")),
 Age = (int)reader.GetInt32(reader.GetOrdinal("Age")),
 });
 }
 if (reader.GetString(3) == "Mouse")
 {
 list.Add(
 new Mouse()
 {
 AnimalID = (int)reader.GetInt32(reader.GetOrdinal("AnimalID")),
 Name = (string)reader.GetString(reader.GetOrdinal("Name")),
 Age = (int)reader.GetInt32(reader.GetOrdinal("Age")),
 });
 }
 }
 }
 else
 {
 //no rows
 }

• Dennis: Could animals have properties, that extend base class?
• user2254372: No, the only real difference is the name.

Answer 1

Here is your code refactored - since all animals have id, name and age, you can remove duplication (reading and setting these properties for each type of animal):

if (reader.HasRows)
{
 while (reader.Read())
 {
 string animalType = reader.GetString(3);
 Animal animal = CreateAnimal(animalType);
 animal.AnimalID = (int)reader["AnimalID"];
 animal.Name = (string)reader["Name"];
 animal.Age = (int)reader["Age"]; 
 list.Add(animal); 
 }
}

Animal creation (if you are using type name as discriminator you can even use Activator.CreateInstance instead of this switch to create an instance of class):

private Animal CreateAnimal(string animalType)
{
 switch(animalType)
 {
 case "Dog": return new Dog();
 case "Cat": return new Cat(); 
 case "Mouse": return new Mouse(); 
 default:
 throw new ArgumentException();
 }
}