A better way to populate a class object from a database query?

Question

Currently I am using SqlCommands to run stored procedures, I will ExecuteReader and run a while .read, assigning each field to an object then adding that object to a list. This has worked fine so far when stored procedures return a few fields, but now I need to return a full database row of 20+ fields. Is there a better way I should be doing this?

Here is an example of how I am currently using the SqlCommand object and populating a class.

private IEnumerable<DigitalQueue> GetUncheckedQueues()
    {
        var cmd = new SqlCommand
        {
            CommandTimeout = 30,
            CommandText = "sp_StoredProcedureName",
            Connection = Main.Con,
            CommandType = CommandType.StoredProcedure
        };

        var sdrQueues = cmd.ExecuteReader();
        var uncheckedQueues = new List<DigitalQueue>();
        while (sdrQueues.Read())
        {
            var workingQueue = new DigitalQueue
            {
                queueId = sdrQueues.GetValue(0).ToString(),
                queueStatus = sdrQueues.GetValue(1).ToString()
            };
            if (!sdrQueues.IsDBNull(2))
            {
                workingQueue.queueDate = sdrQueues.GetDateTime(2);
            }
            uncheckedQueues.Add(workingQueue);
        }
        return uncheckedQueues;
    }

I suggest you look into frameworks like Entity Framework. https://learn.microsoft.com/en-us/ef/ — Joe C, Jul 23 '19 at 16:37
Or return json if possible and then deserialize the object... — Trevor, Jul 23 '19 at 16:39
What you're looking for is called an [Object-Relational mapping](https://stackoverflow.com/questions/1279613/). — Dour High Arch, Jul 23 '19 at 16:58

score -1 · Accepted Answer · answered Jul 23 '19 at 16:39

-1

Get started with Dapper, it's the best kind of micro-orm that will help you to map queries to object in most efficient way possible.

Getting Started with Dapper Dapper Tutorials

Sample Code

var uncheckedQueues = connection.Query<DigitalQueue>("sp_StoredProcedureName", commandType: CommandType.StoredProcedure).ToList();

answered Jul 23 '19 at 16:39

Parimal Raj

20,189
9
73
110

Thanks for the suggestion, will look into it and let you know if I am able to get it working properly. – G_Man Jul 23 '19 at 16:48

A better way to populate a class object from a database query?

1 Answers1