Tutorials to .com

Tutorials to .com » Dotnet » Database » ADO programmers designed for ADO.NET

ADO programmers designed for ADO.NET

Print View , by: iSee ,Total views: 8 ,Word Count: 4604 ,Date: Tue, 9 Jun 2009 Time: 6:24 PM

Abstract: This article discusses how to achieve ADO.NET basic database operations, as well as when to use ADO.NET instead of ADO.

. NET data access in read data
DataSet, DataTable and Recordset
Conversion of existing code to update data
Sum up the expansion of support for xml

Since the introduction of a number of years before the Open database Connectivity (ODBC) application programming interface (API), there have been a variety of database access technology, and ADO.NET is the latest of which a. In the process, there have been many interesting things. For example, COM database into the field and began to cultivate the colonial process of OLE DB. Then, roughly equivalent to automation OLE DB version of the ActiveX ® Data Objects (ADO) to be elected to the rule of Windows ® database developers Visual Basic ® and asp communities.

Through. NET, Microsoft is providing a common framework (the Framework Class Library), which will include all the existing Windows API even more content. In particular, it included a large number of commonly used database, which the Treasury now requires all COM objects respectively. In these libraries, you will find that xml and ADO object model, they are integrated into the ADO.NET classes is called sub-tree.

ADO.NET to build data-aware as a matter of fact. NET applications based on. And ADO is different, ADO.NET follow more general principles, not so specifically for the database. ADO.NET allows the collection of all types of data processing. These type of database that has the typical features (such as indexing, sorting and view) the data container object. Although ADO.NET is. NET database application solutions for the authority, but from the design point of view, it does not model as ADO to database-centric as it is one of the major features of ADO.NET.

ADO.NET and ADO are quite different. ADO.NET data access is a new programming model, need to develop a comprehensive understanding of personnel, investment and new thinking. However, once started to master ADO.NET, you will be aware of: the original ADO techniques to help you is very different, even more clever and reliable way to create effective applications and solutions to old problems.

In the rest of this article, I will focus on how to achieve basic ADO.NET database operations. I wish to say that when ADO.NET is a better choice than ADO, but the best when you should give up the ADO. ADO to ADO.NET is not improved to comply with. NET infrastructure formed. As long as you look at ADO.NET syntax, code design and transplantation, will understand this.

. NET's data access in ADO.NET to access the data source in a manner determined by the hosting provider. Functional sense, managed provider and the OLE DB provider is very similar to, but there are two important differences. First of all, the management process. NET environment, through the DataReader and DataTable, etc.. NET to retrieve and open data type. Secondly, because of their architecture for. NET is optimized, it is relatively simple.

Currently ADO.NET managed provider provides two procedures: one for SQL Server ™ 7.0 or later, and the other for all the other you may have installed the OLE DB provider. In both cases you were using a different category, but follow a similar naming convention. In addition to the prefix, the name is the same. Prefix for the first case, SQL, the latter case is the ADO.

You should use SQL-type access to SQL Server table, because they go directly to the database server's internal API, skip by the OLE DB provider that the middle layer. ADO is the OLE DB provider on the. NET interface, COM Interop to use them to carry out the work of the bridge.

ADO.NET objects Omri Gazitt beginners can be found in the article introduces ADO +: for Microsoft. NET Framework data access services (English) and my ADO + to promote the evolution of data types (in English) of a text. The former technical nature of the procedures for ADO.NET model provides a high level overview of the commentary. The latter introduces the goals and ADO.NET with XML, scripting and other technical links.

Read data from data source to read data in ADO.NET application must first create a connection object. In accordance with the objectives of the different procedures, the SQLConnection object can be connected or ADOConnection. Remember, you can use the ADO.NET classes to connect to the SQL Server database, but we do not propose to do so. Its only disadvantage is that your code through an unnecessary layer of additional code. ADO it first transferred to the hosting provider, and then re-hosting provider to call SQL Server OLE DB provider. The SQL Server managed provider and the OLE DB provider as the data directly.

ADO and ADO.NET connection objects between the significant differences are: ADO.NET connection does not support attribute CursorLocation. Please note that this is not a documentation error, but a controversial design issues. To highlight to the principle of data-centric, ADO.NET does not achieve explicit cursor.

In ADO, you get used to using the cursor from the database or any other OLE DB-compliant data sources to extract records. You can choose the client or server cursors, each cursor has several pre-set the cursor type. ADO.NET is designed to be extracted from the data source data, and provide new programming interface to read and analyze data.

In ADO, you connect through the specified text and commands to create a Recordset object. For the location and type of cursor, Recordset have a certain strategy. You can read one of the following ways:

In memory to create a static copy of selected records, and then disconnect from the data sources need to be addressed in accordance with these records. ADO cursor called static.

Through the fast, only to move the cursor to scroll the read-only data, which record the cursor in a static snapshot. ADO called read-only cursor.

Through two types of server-side cursor to access the data, the cursor needs to maintain a good connection, but you can at all different levels of testing at any time the user is connected to other changes. ADO called dynamic sets and cursor keys.
The first two methods are disconnected record set and cache from the client to read information, it is their similarities. In addition, Web-oriented environment and the new n-tier systems, these two methods proved to be the highest frequency of use.

In ADO, the way all these different types of cursor and the corresponding. You will be back in this article found that although ADO.NET is very different, but it you use ADO to achieve any function can be realized. Only your source code from the actual data and physical storage media and format of data collected.

ADO.NET provides two objects to deal with data source from the data collected. They are the DataSet and DataReader objects. The former is recorded in the memory cache, you can access from any direction and modify freely. The latter is the object of highly optimized specifically for the way forward to rolling only designed to read-only records. Please note that looks like a static cursor DataSet, but in fact, in the. NET and ADO in the corresponding read-only cursor is the DataReader object.

In ADO.NET does not support server-side cursor. However, this does not mean you can not use the cursor. You need to do is. NET import ADO type library. References in the project window, right-click the node on the list. After import, you can start using the application in a local ADO objects.

Although I recognize the determined shift. NET is a very difficult thing, but I personally recommend that you consider. NET rewrite of existing applications. ADO can serve as a fully imported. NET the first step, it need not invest too much time and resources. However, please remember this is only the first step in long road. This is not your move. NET only step. . NET has the value of the real value due to the uniform and consistent programming interface, as well as the extensive use of the local category. You can import a COM type library, but to import COM type library only as a temporary solution or intermediate step, we are not encouraged to do so.

The use of ADO.NET should be taken fully into account its unified data programming interface type containers that fact. No matter what you intend to prepare an application, Windows Forms, Web Forms or Web services, to pass through the same set of data type to deal with. Whether in the back-end data source is SQL Server database, OLE DB, xml file or an array, you can through the same methods and properties to the rolling and processing of their content.

Figure 1: Solution Explorer menu

If you insist on. NET using ADO, please ready to face some side effects. For example, you need extra code to control data binding from the use of recordsets.

DataSet, DataTable and Recordset
In ADO.NET, there is no direct correspondence with the Recordset object of the object. Closest to the DataTable object. Although the functions of these two objects are almost identical, but they are the framework in their respective play different roles.

Is a large Recordset object, ADO has many functions, but are lacking in something. Recordset performance in many ways, for example, can create and still work when disconnected, feature-rich and so on. However, in some respects still needs to be improved. For example, the COM due to the inherent characteristics of Recordset through the sequence of networks will be very heavy. Also it is a binary object, so in different modules running on a platform to share it very difficult, but it can not pass through the firewall. In addition, Recordset that recorded more than a single table. If the table is from one or more JOIN generated, update the original data source may be very difficult. If you disconnect the records to set and maintain the coordination of the original data sources, data must be able to identify the source of SQL. However, your record collection is likely to be provided through non-SQL procedures created.

In ADO.NET in, ADO Recordset removed all the features into several relatively simple objects, DataReader is one of them. DataReader simulated fast, only to move the operation of the read-only cursor.

DataTable as a data source that the simple object. You can manually constructed DataTable, can also be ordered through the DataSet to fill it automatically. It contains DataTable does not differentiate between the sources of data. The object allows you to process data in memory, as well as browse, sort, edit, filter applications, such as the creation of view the operation.

ADO does not correspond with the DataSet object. DataSet object is a container class, ADO.NET data extraction is to achieve the key targets. DataSet will be one or more DataTable objects grouping. DataTable through the rows and columns such as the general public the content of the collection. When you try to read from the data table data, you may object through two different layers: DataTableMapping and DataView.

DataTableMapping object describes the data source DataTable columns of data and the mapping relations between objects. When the DataSet when filled, DataSetCommand To use this type of object. It focused on the maintenance of data abstraction and data sources are listed in the link between the physical column.

View table through the DataView object. It expressed the view custom DataTable can be bound to a specific control (such as Windows Forms and Web form data in the grid) in the. The object equivalent of SQL CREATE VIEW statement to achieve in memory.

All of the DataSet tables are accessible through a common domain Add relations. The relationship between the management by the DataRelation object. It looks very much like the formation of ADO data, but the distinction is important. You do not need to use the data to form the language, you will eventually have a very flexible structure. Navigation model of ADO.NET allows you to easily a table from a main line into all of its sub-line.

DataRelation object JOIN statement is equivalent to the realization in memory that can be used to establish the same type of data out of the parent / child relationship. Once established a relationship does not allow any damage to this relationship will change, if there is abnormal can lead to run-time. View and the relationship is to achieve the main table / schedule of the structure in two ways. To remember that the view is just the mask on the record, while the relationship is set up in the two tables out of one or more dynamic link between. If you are using the relationship, you can not change the order or set conditions.

If your code needs one-on-one foreign key relationship, and does not change the data, then you should not use the JOIN command format. If you require additional filtering, you should use a custom view of ADO.NET.

Conversion of existing code there are many ASP pages using ADO to extract data objects. Let us discuss several typical case, you in the near future transplantation and adaptation of the code may have to deal with such situations.

If you have a recordset from a single ASP page to generate statements, DataReader object will be your best partner.
DataReader object your browser, it will be the result of output to the page.

String strConn, strCmd;
strConn = "DATABASE = MyAgenda; SERVER = localhost; UID = sa; PWD =;";
strCmd = "Select * From Names where ID =" + contactID.Text;
SQLConnection oCN = new SQLConnection (strConn);
SQLCommand oCMD = new SQLCommand (strCmd, oCN);
oCN.Open ();
SQLDataReader dr;
oCMD.Execute (out dr);
while (dr.Read ()) (
/ / Use dr.GetString (index) or
/ / Dr [ "field name"] way to output data Response.Write

You can also quickly check attributes with HasMoreRows whether DataReader is empty. If you only need a quick series of records, there is no better and faster than the DataReader objects. It is equally applicable to a single record of inquiries. You can not edit the contents of the DataReader, but you can move more easily manage the content of the object, for example, one or more DataTable or DataRow object.

When you need to address the table and record the complex relations between, DataReader is no longer suitable tool. In ADO, the final recordset you need to be addressed. Your link to more data model, SQL command more complicated. Navigation model is still the order, and finally Add cache data is often more than you need. DataSet and DataRelation object relations model is the basis of table.

To the management of parent / child relationship, ADO data also form the engine package. Functional sense, the formation and ADO.NET data relations are the same. However, from the design point of view, they have little in common. Set will form a record of all the information embedded in a single list of objects. ADO.NET is that you can at any time the relationship between two data tables in the dynamic link between. ADO to a single command in the implementation of the process of creating a hierarchy of recordsets, ADO relies on Shaping OLE DB service provider, and the use of specific types of SQL language.

In ADO.NET, the relations are always involved in each object to be seen as separate individuals. Relations as object is open, and have certain rules of conduct. For example, DataRelation object line from father to son line change layers. You can add to the DataTable object ForeignKeyConstraint the Constraints collection for this operation. When the object ForeignKeyConstraint delete or update values and when, through the foreign key relationships associated with a group of out of bound. As previously mentioned, once set up the relationship, in its default termination in accordance with the procedure, you can not disrupt the relationship likely to change.

In addition, the relationship can not be transmitted. You can create a different relationship between the two groups, such as customers and orders, orders and product relationships. However, when the navigation in order to find a customer, you can not jump from one order line related products. In addition you must open orders / product relationship, you need to navigate to the orders, and then to get the relevant line. That is why it is sometimes best not to pass the original format of the SQL JOIN statement no one-to-one relationship between the reasons for the achievement.

ASP Session object needs to store records? The use of ADO.NET and the DataSet object, you can operate a very safe and would not lead to GIT store in ADO recordset visit could lead to conflict (in English) in the issues discussed, there will not be a similar thread of trouble.

Update data to update data, Web applications typically use plain SQL statements, or the use of better parameters of stored procedures. However, when the need to use the data is not connected, you may want to use the built-services to update all records need to be revised. ADO provides a batch update mechanism to achieve this function.

UpdateBatch methods used to preserve a copy of the buffer in the Recordset changes sent to the server to update the data source. Lock it open, to allow all pending changes to the local. It is also in a single operation, all changes to the data source. Only when the change after the submission of the data source to change the record lock when the lock appears open. Lock open the two users can simultaneously access the same record, but a user input changes will soon be covered by another user. Of course, this approach requires the data source of data to detect and prevent conflicts. Also requires relatively stable throughout the data source, the change does not occur frequently. Otherwise, it is not difficult to imagine the co-ordination will soon exceed the cost of alternatives brought about by strict conservation lock. In fact, the use of UpdateBatch method, any changes in the failure will return an error. Then, you can set through the Errors and Error object to access the error.

To understand why ADO.NET is to update the data model more powerful tool in understanding open ADO locking the working principle is very crucial. In ADO code, you can not control what happens after the call UpdateBatch all. In other words, the update is on the server has been changed through the rolling line, and then compare the original value and the corresponding data source records to the current value of. When all the values are consistent, and should form the implementation of appropriate SQL statement (INSERT, UPDATE or DELETE).

The problem is that you can not control the actual SQL statement used to change. Server-side code does not update the code than you write a good, if you provide the procedures for the use of non-SQL, it can not even run. In the beginning of this section, I have talked about the adoption of Web applications is usually parameterized stored procedure to update the data. However, if you use batch update is different.

In ADO.NET, this model has been extended. It is now more generic framework that allows you to specify the basic operation of their order, such as insert, delete, update and selection. The intention is clear: No matter what kind of data source can be extracted from the data and provide the same support. ADO.NET approved in the update, you'll need to create objects DataSetCommand that SQLDataSetCommand or ADODataSetCommand.

Note: In Beta 2 in, DataSetCommand object will be called the DataAdapter object.

Have DataSetCommand object, you can call its Update method. DataSetCommand provide InsertCommand, DeleteCommand, UpdateCommand, and attributes such as SelectCommand. They are the Command object. However, unless the default behavior can not satisfy the need, otherwise you do not have to set them. This is like in the ADO. In the Update process, xxxCommand If you do not set any attributes, but the existence of primary key information will be automatically generated Command object. Please note that the right to carry out the process, must be involved in setting the primary key data tables.

The following code shows how to set the DataSet's primary key EmployeesList table:

DataColumn [] keys = new DataColumn [1];
keys [0] = m_oDS.Tables [ "EmployeesList"]. Columns [ "EmployeeID"];
m_oDS.Tables [ "EmployeesList"]. PrimaryKey = keys;

Primary key is an array of DataColumn objects.

If you want to use stored procedures to update the table, or to use a dedicated non-SQL data provider, you will be frequently used properties of these commands.

XML support in ADO in the expansion, XML is only the input and output format. However, in ADO.NET in, XML is a data format, providing the operation, organization, sharing and transmission of data means. Any data into the DataSet, regardless of their source, can be adopted to deal with double-sided programming model. You can turn the order of access to information, or visit in accordance with and in accordance with the XML Document Object Model-driven non-sequential, hierarchical structure of the path for a visit.

DataSet data and structure as read and write XML documents. Data and structure can be transmitted through the HTTP and XML support in all of the platform. The same data at different times can be a different structure, which is achieved through the XSLT. You can use the method of preparation of the framework ReadXmlSchema. XML data structure, including description of the table, as well as the relationship between tables and constraints. ReadXmlData call methods in the DataSet filled before the completion of this step should be.

The following is a sample code to update the data tables show the most simple ASP.NET page.

<% @ Import Namespace = "System.Data"%>
<% @ Import Namespace = "System.IO"%>

<script runat="server" language="C#">
void Page_Load (Object source, EventArgs e)
DataSet data = new DataSet ();

/ / Load the XML data and structure
StreamReader sr;
sr = new StreamReader (Server.MapPath ( "data.xml"));
data.ReadXml (sr);
sr.Close ();

/ / Add the URL pass through the new record
if (Request.QueryString.Count> 0)
DataTable dt = data.Tables [0];
DataRow dr = dt.NewRow ();
dr [ "FirstName"] = Request.QueryString [ "First"];
dr [ "LastName"] = Request.QueryString [ "Last"];
dt.Rows.Add (dr);
dt.AcceptChanges ();

StreamWriter sw;
sw = new StreamWriter (Server.MapPath ( "data.xml"));
data.WriteXml (sw);
sw.Close ();

/ / Refresh the UI (from the grid) in
grid.DataSource = data.Tables [0]. DefaultView;
grid.DataBind ();
</ script>

As shown in Figure 2, you can add new rows to the table. However, it does not involve the SQL Server or Access table. It is a XML document to deal with it in the code, do not use the XML node or XMLDOM method. You can use the same intuitive interface to data sheets to read and update XML records. You work with in virtually the same as in ADO, but here the model of a more in-depth, but also huge potential for more for you to explore.

Figure 2: Examples of renewable Table

The success of Web applications has changed the face of a typical distributed system. Distributed systems are now the majority of n layer system, this type of system scalability and interoperability have become increasingly demanding. Therefore, the non-connected data processing and XML to be the best practice and widely accepted for the industry.

ADO.NET try to practice some of the best of today's unified. NET under. Which used the data access programming model of a comprehensive and very powerful. However, this model may still not meet everyone's requirements, the model design in the future also need to take a big step. However, keep in mind is only Beta version of ADO.NET, only limited support for the document.

Beta version of ADO programmers benefit from the most because they are familiar with many aspects of ADO.NET, including the highest level of abstraction that is instructive model. ADO.NET code with the existing ADO code is not compatible, but the function is similar. To take full advantage of ADO.NET, you should spend some effort to understand the concept, not just to find the quickest way to transplant the code. No matter what you choose. NET programming model, Windows Forms, Web Forms or Web services, ADO.NET will help you deal with the issue of data access.

.Net Database Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.