how to create windows applications in VB.Net or ASP.Net

Question

which method is more efficient to use: DataAdapter and DataSet Or DataReader when importing data from a certain database table???

Answer 1

I would just pick whichever one you like best, and decide to optimize once you find a bottleneck in using one or the other. If you're at the place of just planning a new app, it makes more sense to get it working with Okay performance, than to get bogged down trying to pick the 'best' approach.

Answer 2

Under the hood, the DataAdapter uses the DataReader to actually query the data and populate the datatable/dataset. So end of the day, it doesn't really matter which one you use, but I would suggest the DataAdapter as it's easier to code against, and less error prone.

Answer 3

A DataReader is more 'efficient' than a DataAdapter but it has limitations. It's probably the best for your problem, but without further details it is hard to say.

And as John says, get a working solution might be your first priority.

Answer 4

A Data reader will read the data in a progressive manner allowing you to deal with one row at a time, its fast and light weight but is mainly forward only.

A Data adapter and Dataset, acctually use the DataReader in order to get the data out and stores the result in memory filling the table takes longer but accessing and updating after this is much quicker, until you persist the data back to the DB.

Use a datareader if speed is your thing, otherwise use a dataset since its far more readable and managable.

Answer 5

Use case is important here. How are you going to use the data once it's marshaled into your application?

If you need only iterate through it, a DataReader has the least memory overhead of your listed technologies.

DataAdapter provides more capabilities in exchange for more memory, and is specificaly going to involve the overhead of DataTable. This is in exchange for a more convenient API.

DataSet is heaviest (before you get to typed datasets, at least), but provides the abilitiy to get in-memory relationships between DataRows.

There is a range of capabilities versus cost in terms of memory and programming efficiency - memory is relatively cheap in many use cases, and programmer time and maintainability of code may be more important in the long run.

Answer 6

Since it sounds like you're looking for program efficiency. I would lean toward a business object class using a data reader as the access method. That would give you the ability to create maintainable code and give you speed.

You could create an object with a method that iterates through a DataReader like the example below. This gives you all the speed and in my opinion it's quite maintainable. This is probably pretty much what a type DataSet provides for you, just without more lines of code to generalize the functionality needed in retrieving data.

something like this:

public class Table1
{
    public int Col1 { get; set; }
    public String Col2 { get; set; }

    public List<Table1> GetTable1()
    {
        List<Table1> tableContents = new List<Table1>();
        SqlCommand cmd = new SqlCommand("SELECT * FROM Table1");
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            tableContents.Add(new Table1
            {
                Col1 = (int)rdr["Col1"],
                Col2 = (string)rdr["Col2"]
            });
        }

        return tableContents;
    }
}

There's lots of ways to do it, hope this helps