CODEWITHSUNDEEP
c#ado.net
gigi
gigi

Reputation: 3936

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

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.

Upvotes: 0

Views: 10601

Answers (1)

Steve
Steve

Reputation: 216273

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; 
    }

Upvotes: 2

Related Questions