Select between dataset and datareader

Select between dataset and datareader

The sqldatasource control contains data in the database in either of the following two ways based on the value of the performancemode attribute: datareader or dataset (default ). Datareader outbound information, while dataset retains the value in the memory and allows the data binding control to execute tasks such as sorting and paging. This difference applies to Web applications.ProgramNot important because end usersCodeNever access the underlying dataset or datareader. Dataset or datareader will be discarded at the end of the request, so it will not be kept in memory across multiple requests. The only way to decide whether to write data using the data source control is to define the update-, delete-, or insert-command. Chapter 11th and Chapter 12th will introduce the relevant content. This method is valid regardless of whether the data source returns dataset or datareader from selectmethod.

Table 3-2 summarizes the performance, representation, and Cache Usage considerations. There are many scenarios, but in the most common cases, datareader is faster than dataset and uses a very small amount of memory on the IIS server. According to general rules, when the data source control only fills in the list or data binding control, and this list or data binding control does not need to be cached, paged, or dynamically sorted, datareader should be used. If the data binding control provides sorting or paging of records, datareader should also be used. In this case, the cost of using dataset will be accepted (when performancemode is not explicitly set, is the default value ). For more information about paging and sorting, see Chapter 7th. Finally, for the cache, as described in chapter 17th, dataset must be used.

Table 3-2

value	advantages	meaning
datareader	relatively fast	cache, paging, and dynamic sorting are not supported
Dataset	Supports caching, sorting, and paging.	Slow

These differences will be seen in the following "try.

Try #4 -- set sqldatasource performancemode of dataset and datareader

In this short exercise, set the data source mode to datareader for the sqldatasource bound to the dropdownlist. Then, the gridview will be added and the pagination will fail when its data source control is changed from dataset (default value) to datareader mode. Paging and sorting are not discussed in depth here, but paging and sorting are involved in the first demonstration in Chapter 1st.

(1) create a page named TIO-0304-DataMode-1.aspx. In the design view, drag and drop a dropdownlist control to the page. Select data source, select new, and set it to use the mdfnorthwind connection created in "Save connection string in Web. config" before this chapter. This time, the shippers table and shipperid and companyName are used. Return to the dropdownlist wizard and set the fields that display the companyName field and the shipperid value.

Because this information is almost unchanged, you just need to put it into the page as soon as possible. Select the data source control and change the performancemode attribute to datareader in the Properties window (if the Properties window is invisible, press F4. This page is running, but you cannot feel the speed of datareader.

(2) Now we will demonstrate an error, which comes from the mismatch between the performancemode attribute and the paging function used in the girdview control. Drag and Drop a gridview control to the page, configure data source as a new database, and retain the default name of sqlperformance2. Use mdfnorthwind to connect and select the table named products and the productid and name fields in the table. Click next, and then click Finish. By default, the sqldatasource control is in dataset mode.

Select the gridview and enable paging in the general task panel. Save, run, and observe in the browser that there is no problem with page loading, the pagination in the gridview works correctly.

(3) Close the browser and return to the page in vWD design mode. Select the sqlperformance2 control and view the Properties window. Find performancemode (in the designer group), double-click it, and change the option to datareader. Note the error when saving the page as a TIO-0304-DataMode-2 and viewing the page in a browser.

Example #4 -- set sqldatasource performancemode of dataset and datareader

Datareader is faster. However, sqldatasource does not support paging or sorting when using datareader mode. For dropdownlist, it is ideal to quickly provide data to relatively static controls. In step (1), set the data source control in the list to datareader. In step (2), the default data mode is dataset. After switching to datareader, pagination fails. Note that you still need to use the datareader mode in many cases, such as filling in ListBox or bulletedlist quickly.