0

Is this the fastest and efficient way to retrieve data from the database to the business logic layer?

public static DataTable Getdata(Guid companyId)
{
    DbCommand command = db.GetStoredProcCommand("dbo.P_GetData");
    db.AddInParameter(command, "@company_id", DbType.Guid, companyId);
    IDataReader reader = db.ExecuteReader(command);
    DataTable data = new DataTable();
    data.Load(reader, LoadOption.OverwriteChanges);
    reader.Close();
    return data;
}
Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
Naor
  • 23,465
  • 48
  • 152
  • 268
  • 6
    I would put the `db.ExecuteReader()` statement in a `using` block, just in case an exception happens during `data.Load()` which would cause `reader.Close()` not to be called. – Cameron Mar 14 '11 at 01:46

3 Answers3

1

According to nawfal's benchmarks:

For some reason Fill():

dataAdapter.Fill(dataSet);

Is faster than Load():

dataTable.Load(dataReader);

For example, something like this might be 4-5x faster than what you have:

using (var da = new MySqlDataAdapter())
{
    using (da.SelectCommand = conn.CreateCommand())
    {
        da.SelectCommand.CommandText = query;
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds.Tables[0];
    }
}

See his answer for more details and benchmark times.

Community
  • 1
  • 1
JohnB
  • 18,046
  • 16
  • 98
  • 110
1

It Depends on your Requirement..

There are many ways to retrieve data from database.

In Ado.net datareader and data adapter can be use. And they both have its advantage.

you can also use Linq to sql

and check this Performance Comparison: Data Access Techniques

http://msdn.microsoft.com/en-us/library/ms978388.aspx

Regards.

Crimsonland
  • 2,194
  • 3
  • 24
  • 42
0

Assuming that your logic layer requires a DataTable, and ignoring other coding issues and requirements, this is probably plenty fast.

Do you have reason to believe that it's too slow for your needs? If so, experiment with different approaches, and don't neglect:

  • the logic used by the stored procedure running your query, and the plan it generates
  • the amount of data that stored procedure is returning (do you need all of those columns?)
  • the bandwidth of the intervening network, if any

Edit: If I were doing something like this, if I was working with a reasonably-sized data model, and if I had influence over the business layer, I'd use a persistence framework (like the Entity Framework) rather than straight ADO.NET. I'd do this not for performance reasons, though - a persistence layer is more maintainable in the long run.

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
  • I just wonder if this is the right and the best way to do this. – Naor Mar 14 '11 at 01:49
  • 1
    @Naor, if I were doing something like this, and I had influence over the BLL, I'd use a persistence layer (like the Entity Framework) rather than ADO.NET. More maintainable in the long run. – Michael Petrotta Mar 14 '11 at 01:50
  • Right and best way can vary depending on your requirements and computational environment. – GRGodoi Mar 14 '11 at 01:53