none
convert sqlDatareader to list of objects RRS feed

  • Question

  • Hi

    I have data from stored procedure I get it like this

    sqlDatareader reader = cmd.ExecuteReader()

    What is the best way to convert these data to list of objects ?

    Sunday, December 22, 2019 10:09 PM

Answers

  • Hello,

    Here is a simple example not from a stored procedure yet the same applies other than setting the command type to stored procedure on the command object.

    public class ContactType
    {
        public int ContactTypeIdentifier { get; set; }
        public string ContactTitle { get; set; }
        public override string ToString()
        {
            return ContactTitle;
        }
    }

    Ignore the mHasException and mLastException, they come from a NuGet package in my signature.

    public async Task<List<ContactType>> LoadContactTypes()
    {
        mHasException = false;
    
        var contactList = new List<ContactType>();
    
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand() { Connection = cn })
            {
                cmd.CommandText = "SELECT ContactTypeIdentifier,ContactTitle  FROM dbo.ContactType";
                try
                {
    
                    /*
                     * If there is an issue with the connection using
                     * Open will freeze the user interface
                     */
                    await cn.OpenAsync();
    
                    var reader = await cmd.ExecuteReaderAsync();
    
                    while (await reader.ReadAsync())
                    {
                        contactList.Add(new ContactType()
                        {
                            ContactTypeIdentifier = await reader.GetFieldValueAsync<int>(0),
                            ContactTitle = await reader.GetFieldValueAsync<string>(1)
                        });
    
                    }
                }
                catch (Exception ex)
                {
                    mHasException = true;
                    mLastException = ex;
                }
            }
        }
    
    
        return contactList;
    
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Valkyrie.7 Tuesday, December 24, 2019 2:32 AM
    Sunday, December 22, 2019 11:12 PM
    Moderator

All replies

  • You're being shown how to address the results from the stored procedure by column name with a data reader.

    https://www.akadia.com/services/dotnet_data_reader.html

    while(rdr.Read())
    {
           lbFound.Items.Add(rdr["FirstName"].ToString() +
            " " + rdr["LastName"].ToString());
    }

    However you make the adjustment, and you're going to make a new DTO in the while loop, populate the DTO from the reader and add the DTO to the dtos collection.

    Loop

    read

    make new DTO

    populate DTO properties from results read by datareader

    add DTO to collection

    end loop

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    It's a similar DTO usage in using the DTO in the code example below,  but it's using EF  and Linq AddRange (), which is just a loop on the authors collection of author objects making a new DTO and adding the DTO to the dtos collection. 

    The reader while loop is being use internally and you just don't see it, but it's doing the same thing that you're going to do manually.

    public async Task<List<DtoAuthor>> GetAll()
            {
                var dtos = new List<DtoAuthor>();
    
                var authors =  await pc.Author.ToListAsync();
    
                dtos.AddRange(authors.Select(author => new DtoAuthor()
                {
                    AuthorId = author.AuthorId,
                    FirstName = author.FirstName,
                    LastName = author.LastName
                }).ToList());
    
                return dtos;
            }

    namespace Entities
    {
        public class DtoAuthor
        {
            public int AuthorId { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
        }
    }



    • Edited by DA924x Sunday, December 22, 2019 11:00 PM
    Sunday, December 22, 2019 10:59 PM
  • Hello,

    Here is a simple example not from a stored procedure yet the same applies other than setting the command type to stored procedure on the command object.

    public class ContactType
    {
        public int ContactTypeIdentifier { get; set; }
        public string ContactTitle { get; set; }
        public override string ToString()
        {
            return ContactTitle;
        }
    }

    Ignore the mHasException and mLastException, they come from a NuGet package in my signature.

    public async Task<List<ContactType>> LoadContactTypes()
    {
        mHasException = false;
    
        var contactList = new List<ContactType>();
    
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand() { Connection = cn })
            {
                cmd.CommandText = "SELECT ContactTypeIdentifier,ContactTitle  FROM dbo.ContactType";
                try
                {
    
                    /*
                     * If there is an issue with the connection using
                     * Open will freeze the user interface
                     */
                    await cn.OpenAsync();
    
                    var reader = await cmd.ExecuteReaderAsync();
    
                    while (await reader.ReadAsync())
                    {
                        contactList.Add(new ContactType()
                        {
                            ContactTypeIdentifier = await reader.GetFieldValueAsync<int>(0),
                            ContactTitle = await reader.GetFieldValueAsync<string>(1)
                        });
    
                    }
                }
                catch (Exception ex)
                {
                    mHasException = true;
                    mLastException = ex;
                }
            }
        }
    
    
        return contactList;
    
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Valkyrie.7 Tuesday, December 24, 2019 2:32 AM
    Sunday, December 22, 2019 11:12 PM
    Moderator
  • Hi Valkyrie.7,

    Thank you for posting here.

    If the properties of your class correspond exactly to the table in the database, you can also use the following way.

    I used a NuGet package: Newtonsoft.Json, so the efficiency may be reduced, but this way has a small amount of code.

    Here is the code.

      SqlDataReader reader = command.ExecuteReader();
      var dataTable = new DataTable();
      dataTable.Load(reader);
      List<MyObject> myObjects = new List<MyObject>();
      if (dataTable.Rows.Count > 0)
      {
          var serializedMyObjects = JsonConvert.SerializeObject(dataTable);
          // Here you get the object
          myObjects = (List<MyObject>)JsonConvert.DeserializeObject(serializedMyObjects, typeof(List<MyObject>));
       }

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 23, 2019 3:25 AM
  • Thanks alot. I found also a solution but I dont know how it work so please tell me how it work and your opinion.

    using (var reader = cmd.ExecuteReader()) {
    
    var indices = Enumerable.Range(0, reader.FieldCount).ToList();
            foreach (IDataRecord record in reader as IEnumerable)
            yield return indices.Select(i => record[i]).ToList();
    
    
    }
    Can I test this with VS 2017 community edition ?


    • Edited by Valkyrie.7 Monday, December 23, 2019 3:47 AM
    Monday, December 23, 2019 3:45 AM
  • Thanks alot. I found also a solution but I dont know how it work so please tell me how it work and your opinion.

    using (var reader = cmd.ExecuteReader()) {
    
    var indices = Enumerable.Range(0, reader.FieldCount).ToList();
            foreach (IDataRecord record in reader as IEnumerable)
            yield return indices.Select(i => record[i]).ToList();
    
    
    }
    Can I test this with VS 2017 community edition ?


    You couldn't send the results out of a method to somewhere else like you can with a a known type,  a class. No I don't think that's an optimal choice. And it doesn't matter about it being any version of VS, becuase it's about the .NET Framework,  object oriented programming and encapsulation.

    https://en.wikipedia.org/wiki/Encapsulation_(computer_programming)



    • Edited by DA924x Monday, December 23, 2019 4:19 AM
    Monday, December 23, 2019 4:18 AM
  • what is
    pc.Author
    ?
    Tuesday, December 24, 2019 2:13 AM
  • what is
    pc.Author
    ?

    It is the Entity Framework dbcontext of the PublishingCompany database that is being dependency injected into the DAO that is being used in the Data Access Layer, which is using the Author table.

    https://www.entityframeworktutorial.net/what-is-entityframework.aspx

    https://www.entityframeworktutorial.net/efcore/entity-framework-core-dbcontext.aspx

    https://en.wikipedia.org/wiki/Data_access_object

    https://www.tutorialspoint.com/design_pattern/data_access_object_pattern.htm

    https://en.wikipedia.org/wiki/Data_access_layer

    https://en.wikipedia.org/wiki/Separation_of_concerns

    https://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET-2

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Threading.Tasks;
    using DAL.Models;
    using Entities;
    using Microsoft.EntityFrameworkCore;
    
    namespace DAL
    {
        public class DaoAuthor : IDaoAuthor
        {
            private PublishingCompanyContext pc;
    
            public DaoAuthor(PublishingCompanyContext dbcontext)
            {
                pc = dbcontext;
            }
    
            public async Task<List<DtoAuthor>> GetAll()
            {
                var dtos = new List<DtoAuthor>();
    
                var authors =  await pc.Author.ToListAsync();
    
                dtos.AddRange(authors.Select(author => new DtoAuthor()
                {
                    AuthorId = author.AuthorId,
                    FirstName = author.FirstName,
                    LastName = author.LastName
                }).ToList());
    
                return dtos;
            }
    
            public async Task<DtoAuthor> Find(int id)
            {
                var dto = new DtoAuthor();
    
                var author =  await pc.Author.FindAsync(id);
    
                if (author != null)
                {
                    dto.AuthorId = author.AuthorId;
                    dto.FirstName = author.FirstName;
                    dto.LastName = author.LastName;
                }
                else
                {
                    throw new Exception($"Author with ID = {id} was not found.");
                }
    
                return dto;
    
            }
    
            public async Task <List<DtoAuthorType>> GetAuthorTypes()
            {
                var dtos = new List<DtoAuthorType>();
    
                var authors = await pc.Author.ToListAsync();
    
                foreach (var author in authors)
                {
                    DtoAuthorType dto = new DtoAuthorType
                    {
                        AuthorId = author.AuthorId,
                        Value = author.AuthorId.ToString(),
                        Text = author.LastName + ", " + author.FirstName
                    };
    
                    dtos.Add(dto);
                }
    
                return dtos;
            }
            public async Task Add(DtoAuthor dto)
            {
                var author = new Author
                {
                    FirstName = dto.FirstName,
                    LastName = dto.LastName
                };
    
                pc.Author.Add(author);
                await pc.SaveChangesAsync();
    
            }
            public async Task Update(DtoAuthor dto)
            {
                var author = new Author
                {
                    AuthorId = dto.AuthorId,
                    FirstName = dto.FirstName,
                    LastName = dto.LastName
                };
    
                pc.Entry(author).State = EntityState.Modified;
                await pc.SaveChangesAsync();
    
            }
    
            public async Task Delete(int id)
            {
                
                var author = pc.Author.Find(id);
    
                if (author != null)
                {
                    var articles = await pc.Article.Where(a => a.AuthorId.ToString().Contains(id.ToString())).ToListAsync();
              
                    foreach (var article in articles)
                    {
                        author.Articles.Remove(article);
                    }
    
                    var payrolls = await pc.Payroll.Where(a => a.AuthorId.ToString().Contains(id.ToString())).ToListAsync();
    
                    foreach (var payroll in payrolls)
                    {
                        author.Payrolls.Remove(payroll);
                    }
    
                    pc.Author.Remove(author);
                    await pc.SaveChangesAsync();
                }
    
            }
    
        }
    }


    • Edited by DA924x Tuesday, December 24, 2019 7:22 AM
    Tuesday, December 24, 2019 7:08 AM