DataSet DataTable DataReader DataAdapter details

Source: Internet
Author: User
Tags sql server query xslt

Public JsonResult Test () {JsonResult json = new JsonResult {Data = new {Name = "zzl", Sex = "male" ,}}; return Json (json );} public JsonResult TestList () {List <User> userList = new List <User> {new User {Name = "zzl", Email = "bfyxzls@sina.com "}, new User {Name = "zhz", Email = "zhanghangzheng@sina.com" },}; JsonResult json = new JsonResult {Data = userList}; return Json (json ); // return Json (json, JsonRequestBehavior. allowGet); // If AJAX is used for GET, // when will the GET request be used? When we enter the URL directly in the browser, in fact, it is a GET request // if we directly enter the URL/Home/TestList, it will prompt us to download this JSON format document} front-end call:

<Script> $. ajax ({url: "/Home/Test", dataType: "json", cache: false, data: null, type: "POST", success: function (data) {alert (data. data. name) ;}}); $. ajax ({url: "/Home/TestList", dataType: "json", cache: false, data: null, type: "POST", success: function (data) {var msg = ""; for (var I = 0, length = data. data. length; I <length; I ++) {msg + = "<DiV> Name:" + data. data [I]. name + ", Email:" + data. data [I]. email + "</div>" ;}$ ("# msg2" ).html (msg) ;}}); </script> method of List 2, you can also achieve this in a simpler way:

 

Public JsonResult TestList () {List <User> userList = new List <User> {new User {Name = "zzl", Email = "bfyxzls@sina.com "}, new User {Name = "zhz", Email = "zhanghangzheng@sina.com"}, new User {Name = "zql", Email = "zql1980.happy@sina.com" },}; return Json (userList ); // return Json (userList, JsonRequestBehavior. allowGet); // use this sentence for front-end AJAX if it is GET} $. ajax ({url: "/Home/TestList", dataType: "json", cache: false, data: null, ty Pe: "POST", success: function (data) {var msg = "<table border = 1> <thead> <tr> <td> Name </td> <td> Email </td> </tr> </thead> <tbody> "; for (var I = 0, length = data. length; I <length; I ++) {msg + = "<tr> <td>" + data [I]. name + "</td> <td>" + data [I]. email + "</td> </tr>";} msg + = "</body> </table> "; $ ("# msg2" ).html (msg) ;}}); check whether the red part is simpler! Haha pure JS operations:

Var arr = [{"name": "zzl", "sex": "male" },{ "name": "lr", "sex ": "female"}]; for (var I = 0; I <arr. length; I ++) {document. write (arr [I]. name);} 1. their respective introductions

DataSet is a method used to connect to SQL. It means that a copy of the database tutorial is stored in an application, which is equivalent to a database in memory. When the application starts to run, save database-related data to DataSet.

DataTable indicates a table with data in memory. You can use view to obtain a custom view of a table that may include a filtering view or a cursor position.

The DataReader object is the simplest way to read the database. It can only read, cannot write, and is read from the beginning to the end. It cannot read only a piece of data, but it occupies a small memory and is fast.

The DataAdapter object is used to read data from the database. It can read and write data. A piece of data is too strong, but it occupies a larger memory than dataReader and is slow. It is generally used with DataSet.


Dataset indicates a Dataset, which is cached in memory. Can contain multiple tables

DataSet is non-connection-oriented when connecting to the database. Read all tables to the SQL Buffer Pool and disconnect from the database.

DataReader is connected to the database. When reading a table, you can only read the table forward. After reading the data, the user determines whether to disconnect the table.

The data in the distributed system may use DataSet as the data carrier, because DataSet is the data structure that stores the data, and DataReader is not responsible for saving the data, it is only responsible for reading data from the data source to the local. It is not a data structure, but a high-level encapsulation of network communication components. DataAdapter only uses DataReader to read data from the data source and Add it to AataSet for storage. If we use DataReader separately, we can also write data to the business class or dataset. That's just to select different data carriers based on business needs. In fact, all the data we get from the database will be obtained through DataReader, but DataAdapter encapsulates all this.

 

2. What is the difference between DataSet and DataReader objects?

DataSet can be processed offline and rolled back and forth. dataReader cannot be processed offline and is read-only forward. However, the DataSet can store various database objects, such as table triggers, quickly. DataReader can only store cursor records.

DataSet can be updated back to the original database, but DataReader cannot;

DataSet can forword previus, while DataReader can only be FW;

DataReader is similar to a forward cursor record set.

DataSet is called a DataSet! It is the main embodiment of the disconnected database connection between ADO.net and ADO! DateReader is a client that only forwards the cursor. The two fields are different! After reading data! If you want to make frequent changes, you can use DataSet, and DataSet also supports serialization, can be combined with xslt! Web development!

DataReader is more inclined to read data quickly! Data with large data volumes may be used more frequently!

 

ADO. NET provides the following two objects for retrieving relational data and storing it in memory: DataSet and DataReader. DataSet provides a relational representation of In-memory data, a set of data including some tables (these tables contain data, Sort data, and constrain data), and relationships between tables. DataReader provides a fast, forward-only, read-only data stream from the database.

When DataSet is used, DataAdapter (or CommandBuilder) is often used to interact with the data source. When using DataSet, you can also use DataView to sort and filter data in DataSet. You can also inherit from DataSet to create a strong-type DataSet, which is used to expose tables, rows, and columns as strong-type object attributes.

The following topics involve the best time to use DataSet or DataReader, how to optimize access to the data they contain, and how to optimize the techniques for using DataAdapter (including CommandBuilder) and DataView.

3. DataSet and DataReader

When designing an application, consider the level of functionality required by the application to determine whether to use DataSet or DataReader.

DataSet is required to perform the following operations through the application:

Navigate between multiple discrete tables in the result.

Operate on data from multiple data sources (for example, mixed data from multiple databases, an XML file, and a workbook.

Exchange data between layers or use XML Web Services. Unlike DataReader, DataSet can be passed to a remote client.

Reuse the same set of records to improve performance (such as sorting, searching, or filtering data) through caching ).

Each record requires a large amount of processing. Extended processing of each row returned by DataReader will prolong the necessary time to serve the DataReader connection, which affects the performance.

Use XML operations to operate data, such as extensible style sheet language conversion (XSLT conversion) or XPath query.


Use DataReader in applications in the following cases:

Data does not need to be cached.

The result set to be processed is too large to be stored in the memory.

Once you need to quickly access data in the forward and read-only mode.

Note: When filling in DataSet, DataAdapter uses DataReader. Therefore, replacing DataSet with DataAdapter improves performance by saving DataSet memory usage and filling the cycles required by DataSet. Generally, this performance improvement is symbolic. Therefore, design decisions should be based on the required functions.

I often hear this question: "should I use the DataReader class or the DataSet class in ASP. NET Web applications ?" I often see this misunderstanding in many articles and newsgroups, that is, the DataReader (abbreviation of SqlDataReader or OleDbDataReader) is better than DataSet. Sometimes I see the opposite. In fact, Microsoft has created these two data access classes because they are all what we need. Each class has its own advantages and disadvantages. You can choose which one to use based on the application environment.

This article gives a clear description of the selection of the two, so that you can get some guidance on selecting the DataReader class or DataSet class when using ASP. NET. In a client-based Windows Form application environment, these rules may change. When talking about these things, let's assume that you have used the DataReader and DataSet classes and are familiar with them.

Use DataReader class

The following is an ideal condition for using the DataReader class: the data you read must be new, so you must read the data from the database every time you need it. Creating a DataReader class does not consume a lot of memory, but as the load increases, the performance on the DataSet will also increase quickly (refer to the article in Visual Studio Magazine in the resource ).

Your requirements for each row of data are simple. The best example is to simply bind a DataReader to a Web control, such as a DataGrid or DropDownList.

You only need to access XML data from the database in the form of forward-only and read-only. In this case, you can use the ExcecuteXmlReader () method of the SQLCommand object to obtain an XmlReader class (equivalent to the XML version of DataReader ). This requires an SQL Server query that uses the FOR XML clause, or an ntext field that contains valid XML.

You plan to make several repeated calls to the database to read a small piece of information. In this case, the performance data we mentioned above will be greatly improved.

Indeed, many of the more powerful functions of the DataSet class are only applicable to client-based Windows Form applications, such as the ability to establish relationships between multiple tables. In many cases, the DataSet class is more advantageous than the DataReader class, and in some cases, you simply cannot use the DataReader class.

Use DataSet class

In the following situations, you should consider using the DataSet class: you have built a Web service, which uses the data you read as the return value. Because DataReader classes must be connected to databases, they cannot be serialized into XML or sent to a Web service caller.

You need to sort or filter data. Before using a DataView object (presented as the DefaultView attribute of the able class, which contains a DataSet class) to sort or filter data, we first try to use SQL queries (such as WHERE and ORDER BY statements) to implement these functions, and use a more lightweight and faster DataReader class. However, sometimes this method cannot be used, or you cannot use DataReader when you need to sort or filter data multiple times.

For the same request, you need to traverse data multiple times. You can only loop in DataReader once. If you want to bind multiple ServerControl classes to the same DataSet, it is better to select DataSet. The DataReader class cannot be bound to multiple ServerControl classes because it only reads forward data. In this case, if you want to use DataReader, you must read the data twice from the database.

You need to store data, which may be used by subsequent page requests. If the data is only used by the person who requests it, you can save the DataSet class in a Session variable. If the data can be accessed by anyone, you can save it in an Application variable or in a Cache (I suggest using the latter method, because it supports time period and callback )). Because the DataReader class must always open a connection to the database, and it can only save one row of data at a time, they cannot be saved in cross-page requests.

You need to implement special time-consuming functions for each element of a result set. For example, if you read a column of zip code from a database and want to call a Web service to obtain detailed weather information for each region, it would be better to select DataSet. This is because when you use the DataReader class, the connection to the database will not be released back to the connection pool before closing the DataReader class. A small latency between thousands of page requests may result in a high access volume of Web applications, consuming available connections. On the contrary, DataSet can read all the data at the front end, close the connection with the database immediately, and return it to the connection pool. Therefore, other page requests can use this connection.

You need to load and process XML data in a two-dimensional example. The DataSet class is useful for XML, because you can use DataView for XML and sort and filter the basic data, just like processing a database result set. However, you must note that there are many classes in the System. Xml namespace. You can use them for more complex XML operations.

Your data source is not a database. Although OleDbDataReader can be used by any OLEDB data provider (which may point to a database or not to a database), the DataSet object can directly load data from an XML file, and dynamically explain its schema. The DataSet class can also write XML data back to a data stream or a file.

From the above, we can see that the DataSet class has more functions than the DataReader class, which allows you to use them in more cases. However, this does not mean that you are always using the DataSet class. A considerable part of the tasks you need to complete in ASP. NET belong to the category of DataReader.

Even so, there is no doubt that the DataSet class plays an important role in many ASP. NET Web applications in terms of importance or complexity. You can minimize database round-trips through wise caching to reduce the "performance damage" of the DataSet class ". DataReader and DataSet are both important parts of a successful ASP. NET Web application. What's important is that we need to know when and where to best use them.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.