Contact Info

contact@activexperts.com 31625044454

Crumbtrail

ActiveXperts.com » Support » ActiveXperts Network Monitor » ADO

Introduction to ADO

Introduction

Microsoft ADO.NET represents a major step forward for Microsoft data access technologies. It gives developers an unprecedented level of control over how their code interacts with their data. This is a welcome advance for developers who have been frustrated by the lack of control offered by previous "black box" technologies such as the ADO cursor engine.

Why ADO.NET?

ADO has served many developers well for the past few years, but it lacks key features that developers need to build more powerful applications. For example, more and more developers want to work with XML data. While recent versions of ADO have added XML features, ADO was not built to work with XML data. For example, ADO does not allow you to separate the schema information from the actual data. Even if Microsoft added more XML features to future releases of ADO, it will never handle XML data as efficiently as ADO.NET does because ADO.NET was designed with XML in mind and ADO was not. The ADO cursor engine makes it possible to pass disconnected ADO Recordset objects between different tiers in your application, but you cannot combine the contents of multiple Recordset objects. ADO allows you to submit cached changes to databases, but it does not give you control over the logic used to submit updates. In addition, the ADO cursor engine does not, for example, provide a way to submit pending changes to your database via stored procedures. Because many database administrators allow users to modify the contents of the database only through stored procedures, many developers cannot submit updates through the ADO Recordset object.

What is ADO.NET?

ADO.NET is a set of libraries included in the Microsoft .NET Framework that help you to communicate with various data stores from .NET applications. The ADO.NET libraries include classes for connecting to a data source, submitting queries, and processing results. You can also use ADO.NET as a robust, hierarchical, disconnected data cache to work with data off line. The central disconnected object, the DataSet, allows you to sort, search, filter, store pending changes, and navigates through hierarchical data. The DataSet also includes a number of features that bridge the gap between traditional data access and XML development. Developers can now work with XML data through traditional data access interfaces and vice-versa.

.NET Data Providers

A .NET managed data provider is a collection of classes designed to allow you to communicate with a particular type of data store. The .NET Framework includes two such providers, the SQL Client .NET Data Provider and the OLE DB .NET Data Provider. In addition, an ODBC .NET Data Provider is available as a separate download (see below). The OLE DB .NET Data Provider lets you communicate with various data stores through OLE DB providers. The SQL Client .NET Data Provider is designed solely to communicate with SQL Server databases, version 7 and later. Each .NET data provider implements the same base classes - Connection, Command, DataReader, Parameter, and Transaction - although their actual names depend on the provider. For example, the SQL Client .NET Data Provider has a SqlConnection object, and the OLE DB .NET Data Provider includes an OleDbConnection object. Regardless of which .NET data provider you use, the provider's Connection object implements the same basic features through the same base interfaces. To open a connection to your data store, you create an instance of the provider's connection object, set the object's ConnectionString property, and then call its Open method. Each .NET data provider has its own namespace. The two providers included in the .NET Framework are subsets of the System.Data namespace, where the disconnected objects reside. The OLE DB .NET Data Provider resides in the System.Data.OleDb namespace, and the SQL Client .NET Data Provider resides in System.Data.SqlClient. The additional Open Database Connectivity (ODBC) .NET Data Provider mentioned above is available as a separate download at https://msdn.microsoft.com/en-us/subscriptions/downloads/. The namespace is Microsoft.Data.Odbc. The download includes documentation on the classes that make up the ODBC .NET Data Provider. The implementation has the same architecture as both the SQL Client .NET Data Provider and the OLE DB .NET Data Provider.

Connecting To A Data Source

A Connection object represents a connection to your data source. For SQL Server you use the namespace System.Data.SQLClient.SqlConnection and for OLE DB you use the System.Data.OleDb.OleDbConnection. You can specify the type of data source, its location, and other attributes through the various properties of the Connection object. A Connection object is roughly equivalent to an ADO Connection object; you use it to connect to and disconnect from your database. A Connection object acts as a conduit through which other objects, such as a DataAdapter and Command objects, communicate with your database to submit queries and retrieve results. The Connection object has a BeginTransaction method that you can use to create a Transaction object. You use a Transaction object to either commit (using the CommitTransaction method) or cancel (using the RollbackTransaction method) the changes that you make to your database during the lifetime of the Transaction object.

Commands

Command Objects are similar in structure to ADO Command objects. They can represent a query against your database, a call to a stored procedure, or a direct request to return the contents of a specific table. For SQL Server you use the namespace System.Data.SQLClient.SqlCommand. For OLE DB you use the namespace System.Data. OleDb.OleDbCommand. Databases support many different types of queries. Some queries retrieve rows of data by referencing one or more tables or views or by calling a stored procedure. Other queries modify rows of data. Still others manipulate the structure of the database by creating or modifying objects such as tables, views, or store procedures. You can use a Command object to execute any of these types of queries against your database. To query your database, you would set the Connection property to a Connection object which connects to your database. You would then specify the text for your query in the CommandText property. You can also supply just the name of a table, view, or store procedure and use the Command object's CommandType property for the type of query that you want to execute. The Command object offers different ways in which to execute your query. If the query does not return rows, simply call the ExecuteNonQuery method, which usually includes stored procedures that have output parameters and/or return values. The Command object has an ExecuteReader method, which returns a DataReader object that you can use to examine the rows returned by your query. The SqlCommand includes a third execution method, ExecuteScalar, which is used to return a singleton value.

Reading Data

The DataReader object is designed to help you retrieve and examine the rows returned by your query as quickly as possible. You can use the DataReader object to examine the results of a query one row at a time. When you move forward to the next row, the contents of the previous row are discarded. The DataReader doesn't support updating. The data returned by the DataReader is read-only. Because the DataReader object supports such a minimal set of features, it's extremely fast and lightweight. The disadvantage of using a DataReader object is that it requires an open database connection and increases network activity. The DataReader provides a non-buffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data; only one row of data will be cached in memory at a time. You should always call the Close method when you are through; using the DataReader object, as well as closing the DataReader object's database connection. Otherwise the connection won't be closed until the Garbage Collector gets around to collecting the object. One trick is to use the CloseConnection enumeration on the ExecuteReader method. This tells the Command object to automatically close the database connection when the DataReader's Close method is called.

Executing Stored Procedures

If you pass and select query to SQL Server using the SQLCommand object, SQL Server has to compile the code before it can run it, in much the same way that VFP applications have to be compiled before they can be executed. Thus compilation takes SQL Server time. That's one of the benefits of using stored procedures: you create a procedure, store it in the database and because the procedure is known of and understood ahead of time, it can be compiled ahead of time ready for use in your application. In the same way as you use a Command object to execute SQL statements against a database, you can use the Command object to execute stored procedures on the database. To do this, use the Command object's CommandType property. Set this property to any value in the CommandType enumeration: Text, TableDirect, or StoreProcedure. The property is set to Text by default. Setting CommandType to StoredProcedure tells the Command that you're calling a stored procedure. The Command object will combine the value stored in its CommandText property with the information in its Parameters collection to generate the syntax for calling your stored procedure. You can create parameterized queries by adding one or more parameters to the query statement, and then adding the same parameters to the Command object's Parameters collection. By default the Parameter objects are created as input parameters, but setting the Direction property of the Parameter object can also set them as output parameters. The two possible enumerators for the Direction property are ParameterDirection.Output or ParameterDirection.Input. The SQL Server .NET data provider doesn't support the generic parameter marker "?"; instead it requires named parameters that use the "@" prefix.

DataAdapter - Working With Disconnected Data

The DataAdapter object represents a new concept for Microsoft data access models. It acts as a bridge between your database and the disconnected objects in the ADO.NET object model. The DataAdapter object's Fill method provides an efficient mechanism to fetch the results of a query into a DataSet or a DataTable so that you can work with your data off-line. You can also use DataAdapter objects to submit the pending changes stored in your DataSet objects to your database. The ADO.NET DataAdapter object exposes a number of properties that are actually Command objects. For instance, the SelectCommand property contains a Command object that represents the query that you'll use to populate your DataSet object. The DataAdapter object also has UpdateCommand, InsertCommand and DeleteCommand properties that correspond to Command objects used when you submit modified, new, or deleted rows to your database, respectively. You can set the UpdateCommand, InsertCommand, and DeleteCommand properties to call stored procedures or a SQL statement. Then you can simply call the Update method on the DataAdapter object and ADO.NET will use the Command objects which you've created to submit the cached changes in your DataSet to your database. As stated earlier, the DataAdapter object populates tables in the DataSet object and also reads cached changes and submits them to your database. To keep track of what-goes-where, a DataAdapter has some supporting properties. The TableMappings collection is a property used to track which table in your database corresponds to which table in your DataSet object. Each table mapping has a similar property for mapping columns, called a ColumnMappings collection.

Command Builder

The ADO.NET object model not only allows you to define your own updating logic, but it also provides a dynamic updating logic generation similar to that of the ADO cursor engine, using the CommandBuilder object. If you instantiate a CommandBuilder object and associate it with a DataAdapter object, the CommandBuilder will attempt to generate updating logic based on the query contained in the DataAdapter object's SelectCommand. The CommandBuilder can generate updating logic if all the following are true:

The disadvantages with using the CommandBuilder are:

DataSet Object

A DataSet object, as its name indicates, contains a set of data. It is a container for a number of DataTable objects (stored in the DataSet object's Tables collection). ADO.NET was created to help developers build large multi-tiered database applications. At times, you might want to access a component running on a middle-tier server to retrieve the contents of many tables. Rather than having to repeatedly call the server in order to fetch that data one table at a time, you can package all of the data into a DataSet object and return it in a single call. However, a DataSet object does a great deal more than act as a container for multiple DataTable objects. The data stored in a DataSet object is disconnected from your database. Any changes you make to the data are simply cached in each DataRow. When it's time to send these changes to your database, it might not be efficient to send the entire DataSet back to your middle-tier server. You can use the GetChanges method to extract just the modified rows from your DataSet. In this way, you pass less data between the processes or servers. The DataSet also exposes a Merge method, which can act as a complement to the GetChanges method. The middle-tier server that you use to submit changes to your database - using the smaller DataSet returned by the Merge method- might return a DataSet that contains newly retrieved data. You can use the DataSet class's Merge method to combine the contents of two DataSet objects into a single DataSet. You can create a DataSet object and populate its Tables collection with information without having to communicate with a database. With ADO.NET, it is not necessary to communicate until you're ready to submit the new rows. The DataSet object has features that allow you to write it to and read it from a file or and area of memory. You can save just the contents of the DataSet object, just the structure of the DataSet object, or both. ADO.NET stores this data as an XML document. Because ADO.NET and XML are so tightly coupled, moving data back and forth between ADO.NET DataSet objects and XML documents is easy.

Strongly-Typed DataSets

Visual Studio .NET helps you simplify the process of building data-access applications by generating strongly typed DataSets. Strongly-typed DataSets can provide a more intuitive mechanism for the manipulation of data. A typed DataSet is bound to an XML Schema Definition (XSD) file. Schemas provide very rigorous definitions for the types of particular objects. In conjunction with the typed DataSet, they can allow access to the tables and columns of a DataSet using meaningful names. This not only improves the readability of your code, but also enables Visual Studio .NET's IntelliSense feature to make context-sensitive suggestions as you type code. As far as the performance when using typed DataSets, the answer is somewhat vague. Exception throwing incurs a slight overhead from the Runtime, as does typecasting. All of the properties and functions in a typed DataSet are wrapped in exception handling calls,and a great many are wrapped with typecasting code. This leads some developers to believe that they are slightly less efficient than standard DataSets. However, consider the case of schema loading or schema inference. When a regular DataSet loads XML directly from an XML source, that XML is parsed and traversed twice in order to: First, obtain an inferred schema; and second to actually populate the data. A typed DataSet knows its schema ahead of time. Therefore, no matter where it loads its data from, the DataSet will only have to traverse that data once to populate its internal data.

DataTable Object

A DataSet object is made up of a collection of tables, relationships, and constraints. In ADO.NET, DataTable objects are used to represent the tables in a DataSet object. A DataTable object represents one table of in-memory relational data. The data is local to the .NET application in which it resides, however, can be populated from a data source such as SQL Server or VFP using a DataAdapter. You can create and use a DataTable independently or as a member of a DataSet object. DataTable objects can then be used by other .NET Framework objects, including the DataView object. Access the collection of tables in a DataSet object through the DataSet object's Tables property. The schema, or structure, of a table is represented by columns and constraints. Define the schema of a DataTable object using DataColumn objects, as well as ForeignKeyConstraint and UniqueConstraint objects. The columns in a table can map to columns in a data source, contain calculated values from expressions, automatically increment their values, or contain primary key values. If you populate a DataTable object from a database, it will inherit the constraints from the database so you do not have to do all of the work manually. A DataTable object must also have rows in which to contain and order the data. The DataRow class represents the actual data contained in the table. As you access and change the data within a row, the DataRow object maintains both its current and original state. You can create parent/child relationships between tables within a database, like SQL Server and VFP, using one or more related columns in the tables. You can create a relationship between DataTable objects using a DataRelation object, which may then be used to return a row's related child or parent rows.

DataColumn Object

Each DataTable object has a Columns collection, which is a container for DataColumn objects. A DataColumn object corresponds to a column in a table. However, a DataColumn object does not actually contain the data stored in a DataTable. Instead, it stores information about the structure of the column. This type of information, data about data, is called metadata. The DataColumn exposes a Type property which describes the data type (such as string or integer) that is stored in the column. DataColumn has other properties such as ReadOnly, AllowDBNull, Unique, Default, and AutoIncrement which allow you to control whether the data in the column can be updated, restricted as to what can be stored in the column, or can dictate how values should be generated for new rows of data.

DataRow Object

To access the actual values stored in a DataTable object, use the object's Rows collection, which contains a series of DataRow objects. To examine the data stored in a specific column of a particular row, use the Item property of the appropriate DataRow object to read the value for any column in that row. Rather than returning the data for just the current row, the DataTable object makes all rows available through a collection of DataRows. The DataRow object is also the starting point for your updates. You can call the BeginEdit method of the DataRow object, change the value of some columns in that row through the Item property, and then call the EndEdit method to save the changes to that row. A DataRow object's CancelEdit method allows you to cancel the changes made in the current editing session. A DataRow object also exposes methods to delete or remove an item from the DataTable object's collection of DataRows. When changing the contents of a row, the DataRow object caches those changes so that you can submit them to your database at a later time. Thus, when you change the value of a column in a row, the DataRow object maintains that column's original value as well as its current value in order to successfully update the database. The Item property of a DataRow object also allows you to examine the original value of a column when the row has a pending change

DataView Object

Once you've retrieved the results of a query into a DataTable object, you can use a DataView object to view the data in different ways. If you want to sort the contents of a DataTable object based on a column, simply set the DataView object's Sort property to the name of that column. You can also use the Filter property of the DataView so that only the rows that match certain criteria are visible. You can use multiple DataView objects to examine the same DataTable at the same time.

Using DataRelations

The tables in your database are usually related in some fashion. The ADO.NET DataSet object is designed to handle this through the DataRelation object. The DataSet class defines a Relations property, which is a collection of DataRelation objects. You can use a DataRelation object to indicate a relationship between different DataTable objects in your DataSet. DataRelation objects also expose properties that allow you to enforce referential integrity. For example, you can set a DataRelation object so that if you modify the value of the primary key field in the parent row, the change cascades down to the child rows automatically. You can set your DataRelation object such that if you delete a row in one DataTable, the corresponding rows in any child DataTable objects - as defined by the relation - are automatically deleted as well. Relationships may only be created between matching columns in the parent and child tables, therefore, the column in each table must contain identical data types - if the parent column is an integer, then the child column must also be an integer. Locating a row's related child rows in another DataTable is rather straight-forward. Simply call the GetChildRows method of your DataRow and supply the name of the DataRelation object that defines the relationship between your DataTable objects. You can supply the actual DataRelation object instead of the object's name. The GetChildRows method returns the related data as an array of DataRow objects.

Manipulating Data

The DataAdapter object acts as a bridge between the DataSet object and the database. The DataAdaper object's Update method submits changes stored in the DataSet into the database. Each DataAdapter object corresponds to one of the DataTable object in your DataSet. In order to submit changes stored in DataTable objects, you must call the Update method on the DataAdapter object. When you call the Update method on a DataAdapter object, specify what data you want to submit to the database. The DataAdapter object is flexible and can accept a number of different structures in its Update method. You can submit a DataSet object, DataTable object, or an array of DataRow objects to the Update method. The DataAdapter object examines the contents of the data structure to determine which rows it can handle. The DataAdapter object knows which table to examine because of its TableMappings collection. When the DataAdapter object detects a modified row, it determines the type of change - insert, update, or delete - and submits it to the database, based on this type. If the row has been modified, the DataAdapter object executes the DataCommand stored in its UpdateCommand property using the current contents of the row. Similarly, the DataAdapter object uses its InsertCommand to submit new rows and its DeleteCommand to delete rows. Once the rows have successfully updated to the database, the next step is to call the DataSet object's AcceptChanges method. This method removes from the local cached DataSet any rows that were flagged as deleted and sets any rows where the RowState is Modified or Added to UnChanged. The RejectChanges method is used to cancel any pending changes. Any row that has a RowState of Modified or Deleted will be changed to UnChanged. If a row has a RowState of Added then the row will be removed.

Working With XML

The DataSet is capable of reading and writing its data and schema as XML. This is important if you consider that XML is an open, industry standard that is popular among most software solutions providers. At this time, XML is pervasive - found both in Internet solutions and in traditional applications. The fact that it is designed to both contain and describe data (data that describes itself) makes it the perfect choice for a universal data container such as the DataSet. Furthermore, the ability of a DataSet to both read and write data and schema as XML makes it possible for you to both create and modify data in a DataSet using XML or XML enabled solution, such as SQL Server 2000 and VFP. The methods for reading XML into a DataSet have, of course, complimentary means of writing XML from a DataSet: WriteXml and WriteXmlSchema. Two additional methods provided are: GetXml and GetXmlSchema. These methods return the data or schema as a string.