C# question

Question

Normal.dotm 0 0 1 31 178 UNR 1 1 218 12.0 0 false 18 pt 18 pt 0 0 false false false /* Style Definitions */ table.MsoNormalTable {mso-style-name:Table Normal; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Times New Roman; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;} The DataReader provides a forward only reader that reads records one at a time while the DataSet is an in-memory editable table-based collection. Describe and explain when and why you would use one over the other.

Riddhi Valecha · Answer

Datareader - Example of Connected Architecture. It will fetch the values directly from the database. The SQLConnection is open when we use Datareader. ------------------------- Dataset - Example of Disconnected Architecture. All the changes/updated will not be reflected in the database.Changes will be done in database only with the connection is open. All changes will be made in local copy. ----------------------------------- 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 Code snippet using 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 Code sample 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; } ------------ Hope this helps