DAL technique - which is better

[SpaceMarine]

hello,

in my DAL here is how i typically fetch a single DataTable (for biz
layer consumption):

SqlConnection conn = new SqlConnection(connStr);
SqlCommand command = new SqlCommand("GetFoo", conn);
command.CommandType = CommandType.StoredProcedure;

//do it - dataset & adapter
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);

return ds.Tables[0];

but recently i came across something that suggested using a
DataReader:

SqlConnection conn = new SqlConnection(connStr);
SqlCommand command = new SqlCommand("GetFoo", conn);
command.CommandType = CommandType.StoredProcedure;

//do it - datareader
conn.Open();
SqlDataReader dr =
command.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dt = new DataTable();
dt.Load(dr);

//closing DataReader auto-closes the Connection due to above
dr.Close();

return dt;

....the idea being that since DataReaders are faster, this would fetch
& fill a DataTable faster than using a SqlDataAdapter to fill a
DataSet.

any thoughts?


thanks!
sm

 

[Miha Markic]

Hi there,

No, it shouldn't be faster as internally it is always used.
Using reader gives you more flexibility if you need it.

 

[SpaceMarine]

No, it shouldn't be faster as internally it is always used.
Using reader gives you more flexibility if you need it.

internally what is always used?


sm

 

[Miha Markic]

DataReader that is.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

No, it shouldn't be faster as internally it is always used.
Using reader gives you more flexibility if you need it.

internally what is always used?


sm

 

[Cor Ligthert [MVP]]

I see my message did not arive.

I wrote earlier.

Some people tell that you can better go from New York to Amsterdam in a
rowing-boat, one of the reasons, it does not use fuel.

I prefer more simple ways.

A DataAdapter is nothing more than a kind of wrapper around the proces of
handling resultsets (it uses all kind of command methods.)

I prefer that then every time to tell that it should be done row by row.

Cor