ASP.NET Performance Tips
page 17 of 19
by Adiseshu Dasari
Feedback
Average Rating: 
Views (Total / Last 10 Days): 76765/ 277

Use SqlDataReader to visit the Read-Only Data instead of DataSet

A DataReader is a lean, mean access method that returns results as soon as they are available, rather than waiting for the whole of the query to be populated into a DataSet. This can boost your application performance quite dramatically and, once you get used to the methodology, can be quite elegant in and of itself.

To get first employee information we are waiting until the whole data is filled in the dataset.

Listing 15

SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter dta = new SqlDataAdapter
("select * from EmpTable;", conn);
DataSet ds = new DataSet();
dta.Fill(ds);
string strFirstEmpInfo = ds.Tables[0].Rows[0][0].ToString();

Now using a datareader once the required record is fetched, collecting other results is no way required. This can boost your application performance.

Listing 16

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand comm = new SqlCommand("select * from EmpTable", conn); 
comm.Connection.Open();
SqlDataReader dr =  comm.ExecuteReader(CommandBehavior.CloseConnection); 
string strFirstEmpInfo; 
while(dr.Read())  
{ 
strFirstEmpInfo = dr.GetString(0);
break;
} 
dr.Close(); 
conn.Close();

Not only can we inspect as we go, but the DataReader only stores one result at a time on the client. This results in a significant reduction in memory usage and system resources when compared to the DataSet, where the whole query is stored.


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2020 ASPAlliance.com  |  Page Processed at 2020-08-03 10:53:16 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search