How to read data from a data reader into a data table?

Question

I have a base class for ado.net operations, that looks like this:

    public abstract class BaseDataAccess
    {
        private string _connectionString = ......

        protected string ConnectionString
        {
            get { return _connectionString; }
        }

        protected object ExecuteScalar(string spName, SqlParameter[] parameters=null)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                using (SqlCommand command = new SqlCommand(spName, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    connection.Open();
                    return command.ExecuteScalar();
                }
            }            
        }

        protected DataTable ExecuteReader(string spName, SqlParameter[] parameters=null)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                using (SqlCommand command = new SqlCommand(spName, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    command.CommandType = CommandType.StoredProcedure;
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        DataTable table = new DataTable();                        
                        table.Load(reader);
                        return table;
                    }
                }
            }
        }
    }

And a class that queries the users table

    public class UsersAccess : BaseDataAccess
    {
        private const string SP_COUNT_USERS = "spCountUsers";
        private const string SP_GET_USERS = "spGetUsers";

        public int CountUsers()
        {
            return (int)base.ExecuteScalar(SP_COUNT_USERS);
        }

        public IList<User> GetUsers()
        {
            DataTable usersTable = base.ExecuteReader(SP_GET_USERS);
            return GetUsersFromDataTable(usersTable);
        }

        private IList<User> GetUsersFromDataTable(DataTable usersTable)
        {
            IList<User> users = new List<User>();
            foreach (DataRow row in usersTable.Rows)
            {
                User user = new User();
                user.UserID = (int)row["UserID"];
                user.Password = (string)row["Password"];
                user.Username = (string)row["Username"];
            }
            return users;
        }
    }

The data reader comes with 2 rows of data but when no data gets into the data table. How to accomplish that?

PS: Any tips for a better base class for ado.net would be great. I only have few tables and only few procedures/table.

You could try the DataAdapter.Fill method, although I'm not seeing a good reason as to why your current code doesn't work. (msdn.microsoft.com/en-us/library/497b1k4t.aspx) — Jon Senchyna
– Jon Senchyna, Commented Aug 9, 2012 at 20:52

Steve · Accepted Answer · 2012-08-09 20:53:36Z

2

Missing the users.Add(user); at the end of each loop

    private IList<User> GetUsersFromDataTable(DataTable usersTable) 
    { 
        IList<User> users = new List<User>(); 
        foreach (DataRow row in usersTable.Rows) 
        { 
            User user = new User(); 
            user.UserID = (int)row["UserID"]; 
            user.Password = (string)row["Password"]; 
            user.Username = (string)row["Username"]; 
            users.Add(user);
        } 
        return users; 
    }

answered Aug 9, 2012 at 20:53

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to read data from a data reader into a data table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related