CODEWITHSUNDEEP
c#asp.netasp.net-4.0usingsqlconnection
user2322507
user2322507

Reputation: 141

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

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.

Upvotes: 2

Views: 1993

Answers (2)

Soner Gönül
Soner Gönül

Reputation: 98750

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

Upvotes: 2

Jack RW
Jack RW

Reputation: 77

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

Upvotes: 1

Related Questions