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

Question

I have written the below code. I want to know if i can improve it any further.

public static DataTable GetDepartments()
{
    DataTable dt = new DataTable();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand())
        {
            command.CommandText = "proc_GetDepartments";
            command.CommandType = CommandType.StoredProcedure;

            connection.Open();
            using (SqlDataAdapter da = new SqlDataAdapter(command))
            {
                command.Connection = connection;
                da.Fill(dt);
            }
        }
    }
    return dt;

}

Here i have used SqlDataAdapter. What would be the SqlDataReader way to write it. Also which one is better. Any help/guidance is appreciated.

use the one you have since you are filling directly in a data table :) you can also use SqlDataReader but seems pointless to me since you have no modification on the value from the resultset. — John Woo
– John Woo, Commented Jun 2, 2013 at 15:22
which one is better They are two different things. What is your real question? — Soner Gönül
– Soner Gönül, Commented Jun 2, 2013 at 15:22
@SonerGönül they both can be used to return datatable...wanted to which one is faster/better...also how can i implement SqlDataReader above. — user2322507
– user2322507, Commented Jun 2, 2013 at 15:24
@JW웃 so if want some modification on the result set...that when i need to use SqlDataReader? — user2322507
– user2322507, Commented Jun 2, 2013 at 15:26
@user2322507 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. — Soner Gönül
– Soner Gönül, Commented Jun 2, 2013 at 15:28

Community · Accepted Answer · 2017-05-23 12:11:20Z

2

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

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Jun 2, 2013 at 15:42

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

2 Comments

user2322507 Over a year ago

seems logical now....thanks for ur input...also if u can add the code for SqlDataReader using using...would have been of great help.

Ashigore Over a year ago

You should be very careful when using SqlDataReader to act upon each record, until youre finish looping through the rows the table will be locked and other users will get applications hanging waiting for table locks. Disabling the lock can be even worse, since its possible to read duplicate rows, parts of uncommitted transactions and all sorts of horrible things can happen

Jack RW · Accepted Answer · 2013-06-02 15:36:43Z

1

Use SqlDataReader if you want to loop each record and do some manipulation data inside the looping, but if you want to put the data on the dataset or datatable and then want to bind it to asp.net controls (for e.g: GridView, ComboBox, etc.) then use SqlDataAdapter.

Here is how to use SqlDataReader anyway.

using System;
    using System.Data.SqlClient;

  class ConnectToSqlConnection {
    static void Main(string[] args)  {
      String sConn = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";

        String sSQL = "select id, firstname, lastname from Employee";

      SqlConnection oConn = new SqlConnection(sConn);
      oConn.Open();

      SqlCommand oCmd = new SqlCommand(sSQL, oConn);
      SqlDataReader oReader = oCmd.ExecuteReader();

      int idxID = oReader.GetOrdinal("id");
      int idxFirstName = oReader.GetOrdinal("firstname");
      int idxLastName = oReader.GetOrdinal("lastname");

      while(oReader.Read()) {
        Console.WriteLine("{0} {1} {2}",
          oReader.GetValue(idxID),
          oReader.GetValue(idxFirstName),
          oReader.GetValue(idxLastName));
      }
    }

answered Jun 2, 2013 at 15:36

Jack RW

776 bronze badges

1 Comment

Ashigore Over a year ago

You should be very careful when using SqlDataReader to act upon each record, until youre finish looping through the rows the table will be locked and other users will get applications hanging waiting for table locks. Disabling the lock can be even worse, since its possible to read duplicate rows, parts of uncommitted transactions and all sorts of horrible things can happen.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related