We have to research when our application
consumes lot of time in executing and also when its scalability becomes a
question. Before doing so I always used DataSet to get the data from the
database. Whether it is for large application or too small, but then I found
something was doing wrong.
DataSet is a collection of in memory tables
and datareader provides the ability to expose the data from database.
Both are very widely used in asp.net
applications to get/fetch the data from the database. But for a scalable, fast,
and reliable application one has to know the best practices in developing application.
DataSet
We should use when the application is:
· Windows application
· Not too large data
· Returning multiple tables
· If, to be serialized
· Disconnected architecture
· To return from a WCF service
· To send across layers
· Caching the data
· Do not need to open or close connection
Example for dataset:
public DataSet GetRecord(Guid id, string procedureName)
{
DataSet resultSet = new DataSet();
SqlConnection connection = new SqlConnection(System.Configuration.
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand(procedureName,
connection);
command.CommandType =
CommandType.StoredProcedure;
command.Parameters["pID"].Value = id.ToString();
IDataAdapter adapter = new SqlDataAdapter(command);
try
{
adapter.Fill(resultSet);
}
catch (Exception ex)
{
throw new PerformanceException(ex.Message,
ex.InnerException);
}
return resultSet;
}
DataReader
DataReader is a stream which is readonly and
forward only. It fetches the record from databse and stores in the network
buffer and gives whenever requests. DataReader releasese the records as query
executes and do not wait for the entire query to execute. Therefore it is very
fast as compare to the dataset. It releases only when read method is called.
Its usages:
· Web application
· Large data
· Returning multiple tables
· For Fast data access
· Needs explicitly closed
· Output parameter value will only available
after close
· returns only a row after read
Example for DataReader:
public SqlDataReader GetRecord(Guid id, string procedureName)
{
SqlDataReader resultReader
= null;
SqlConnection connection = new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand(procedureName,
connection);
command.CommandType =
CommandType.StoredProcedure;
command.Parameters["pID"].Value = id.ToString();
try
{
connection.Open();
resultReader =
command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
if (resultReader != null || connection.State ==
ConnectionState.Open)
{
resultReader.Close();
connection.Close();
}
throw new PerformanceException(ex.Message,
ex.InnerException);
}
return resultReader;
}
No comments:
Post a Comment