Sqldataadapter VS SQLdatareader

SqlDataAdapter vs SqlDataReader

DataReader:

• Needs the connection held open until you are finished (don't forget to close it!).
• Can typically only be iterated over once
• Is not as useful for updating back to the database

On the other hand, it:

• Only has one record in memory at a time rather than an entire result set (this can be HUGE)
• Is about as fast as you can get for that one iteration
• Allows you start processing results sooner (once the first record is available). For some query types this can also be a very big deal.

DataAdapter/DataSet

• Lets you close the connection as soon it's done loading data, and may even close it for you automatically
• All of the results are available in memory
• You can iterate over it as many times as you need, or even look up a specific record by index
• Has some built-in faculties for updating back to the database

At the cost of:

• Much higher memory use
• You wait until all the data is loaded before using any of it

So really it depends on what you're doing, but I tend to prefer a DataReader until I need something that's only supported by a dataset. SqlDataReader is perfect for the common data access case of binding to a read-only grid.

For more info, see the official Microsoft documentation.

SqlDataReader vs SqlDataAdapter: which one has the better performance for returning a DataTable?

The difference will be negligible, so it's probably better to use the more concise version: SqlDataAdapter.Fill.

SqlDataReader.Fill creates an internal class LoadAdapter (derived from DataAdapter) internally, and calls its Fill method: performance will be very similar to SqlDataAdapter.Fill(DataTable).

There will be some small differences in initialization / validation of arguments, but as the number of rows increases, this will become less and less significant.

Note also that your second sample should be modified to be comparable with the first:

public DataTable populateUsingDataAdapter(string myQuery)
{
    using (SqlConnection con = new SqlConnection(constring))
    {
        SqlDataAdapter dap = new SqlDataAdapter(myQuery,con);
        DataTable dt = new DataTable();
        dap.Fill(dt);
        return dt;
    }
}

Should i go for SqlDataReader or SqlDataAdapter class for returning a datatable

Also which one is better?

As I said in my comment, they are two different things. Apples and oranges..

SqlDataAdapter is using with the a DataTable. It can fill a DataTable with a table from your SQL. SqlDataReader reads database rows one-by-one.

In your case, I don't see any reason to use SqlDataReader since you want to return a DataTable. Go for SqlDataAdapter ;)

..also if u can add the code for SqlDataReader using using.

Sure. But as I said, you can't use DataTable with SqlDataReader. But hey.. Here is how you can get values with SqlDataReader;

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    int firstcell = reader.GetInt32(0);    // I assume your first column is int.
    string secondcell = reader.GetString(1);  // I assume your second column is string.
    string thirdcell = reader.GetString(2); // I assume your third column is string.
    Console.WriteLine("FirstCell = {0}, SecondCell = {1}, ThirdCell = {2}", firstcell, secondcell , thirdcell);
}

Compact-Framework SqlDataReader vs SqlDataAdapter

Using reader[string] in my experience is slow. When I developed for CE on HandHeld scanners I used something like the following: (don't use the DataAdapter it is slower)

private void QueryByStruct(SqlCeCommand cmd, CTypedClass1 curRec)
{
   if (cmd.Connection.State != ConnectionState.Open)
      cmd.Connection.Open();

   List<object> fields;

   using (var reader = cmd.ExecuteReader())
   {
      if (reader.Read())
      {
          var values = new Object[reader.FieldCount];
          reader.GetValues(values);
          fields = values.ToList();
      }
   }

   if (cmd.Connection.State == ConnectionState.Open)
      cmd.Connection.Close();

   curRec.Fld1 = (int)fields.ElementAt(1);
   curRec.Fld2 = (int)fields.ElementAt(2);
   // etc with rest of fields to 45
   // all explicitly (typecast) referenced

}

I changed the while to if because it appears you're just overwriting values for no really good reason. GetValues() in my experience is really really fast. The down side is that you only get values in column order with no column types nor column names.

Also a good - Microsoft SQL Server Compact Edition Ultimate Performance Tuning:

Video Channel

Microsoft SQL Server Compact Edition Ultimate Performance Tuning Powerpoint Presentation (direct link)

Using SqlDataAdapter or SqlDataReader, which one is more efficient and puts minimum load on server?

Neither sounds very good. Getting all of the data and cherry-picking what you need sounds inefficient on both sides (server and clients) while sending lots of tiny sql requests sounds inefficient as well.

Ideally you would get all of the data that you need (but no more) in as few requests as you can. Choosing between SqlDataAdapter and SQlDataReader doesn't solve that for you. You need to look at your design and determine if you can get all of the related data for a page without keeping an in-memory copy of your entire database.

BTW, SqlDataAdapter uses a SqlDataReader internally to get its data.

SqlCommand or SqlDataAdapter?

This is highly depend upon type of operation you want.

Following is my suggetion.

1. If you want to read data faster go for SQLDataReader but that comes as cost of operation you need to take
   during read after that also.
   Open Connection
   Read Data
   Close Connection. If you forgot to close than it will hit performance.

2. Go for SQLDataAdapter

   • If you want to read faster and use benefit of Disconnected Arch. of ADO.net
   • This will automatically close/open connection.
   • Also it will also allow you to automatically handle update in DataSet back to DataBase. ( SqlCommandBuilder)

3. Use SQLCommand ( This will also comes when you read SQLDataReader for read data) and for insert and update.

   • This will give you better performance for insert and update.

If you are using .NET Frame 3.5 sp1 or later i would suggest Linq to SQL or Entity Framework would also
solve your purpose.

Thanks.

what is the difference between data adapter and data reader?

Please see DataReader, DataAdapter & DataSet - When to use? :

ADO.NET provides two central Data
Access Components. The excellent thing
is that, they are common across all
Databases, be it SQL Server or other
competitive databases. Its only the
namespace to be used, that differs,
while using a Database other than SQL
Server.

SqlDataAdapter and SqlDataReader

if (reader.HasRows) is redundant. When you say while (reader.Read()), it will only loop if there are any rows. Also, this link will explain that HasRows requires a scrollable cursor. Read about the cursors here.

How to run SqlDataReader and SqlDataAdapter concurrently?

Actually you can have multiple operations on single connection by using MARS:

Multiple Active Result Sets (MARS) is a feature that works with SQL
Server to allow the execution of multiple batches on a single
connection. When MARS is enabled for use with SQL Server, each command
object used adds a session to the connection.

to enable it simply modify your connection string by adding MultipleActiveResultSets=True to it like so:

Data Source=MSSQL1;Initial Catalog=AdventureWorks;Integrated Security=SSPI;MultipleActiveResultSets=True

No other code changes are required yet remember it is a configuration change so if you have some environments setup like test, staging you need to propagate this change to those environments as well.

---

Related Topics