Read data from SQL Server table

Question

I'm trying to get data from a SQL Server database and return data (all data) based on a query as follows:

        public async Task<List<PersonEntity>> GetDataAsync()
        {
            var allUsers = new List<PersonEntity>();
            SqlConnection conn = new SqlConnection("");
            conn.Open();
            SqlCommand cmd = new SqlCommand("Select* from <TABLE> <WHERE>", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    var a = new PersonEntity { 
                        LocationAreaCode = reader.GetString(0),
                        PersonStatusCode = reader.GetString(1),
                        PersonStatusDesc = reader.GetString(2)                        
                    };
                    allUsers.Add(a);
                }
            }
            else
            {
                // no data found
            }
            reader.Close();
            conn.Close();            
            return allUsers;
        }

Is there a better way (in terms of performance) to read all data (values from all the columns) matching the query and return the data from this function?

Answer 1

You need to use ADO.net SqlDataAdapter Class

var ds = new DataSet();

using (SqlConnection connection = new SqlConnection(connectionString)) {
    connection.Open();

    var command = connection.CreateCommand();
   

   
    command.CommandText = "Select * from <table-name> where location = 'US'";

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(ds);

     }