An ADO.NET Data Provider is a suite of components designed to facilitate data access. Every Data Provider minimally includes a Connection object that provides the actual connection to the data source, a Command object that represents a direct command to the data source, a DataReader object that provides connected, forward-only, read-only access to a database, and a DataAdapter that facilitates disconnected data access.
 

In ADO.NET, connected data access is available through the DataReader, which is a lightweight class designed to provide very fast and efficient data access. It is severely limited, however, in that it can only provide forward-only data access, it does not allow editing, and it requires the exclusive use of a Connection object. In contrast, disconnected data access is facilitated by a DataAdapter, which manages the commands required for selecting and updating data. The DataAdapter executes a SELECT command against a database, opening a data connection just long enough to retrieve the data, and loads the data into a DataSet, which is an in-memory copy of the data. When the data is ready to be updated, the Data Provider manages the updates in the same way, generating the appropriate commands to update the database and keeping the connection open just long enough to execute those commands.
 

A Command object can have a CommandType property setting of Text, StoredProcedure, or TableDirect. When set to Text, the command executes the SQL string that is stored in the Command object’s CommandText property. When set to StoredProcedure, the command accesses a procedure stored on the database and returns the results. A CommandText setting of TableDirect indicates that the command should return the entire contents of the table indicated by the CommandText property.
 

You must use a Command object to execute DDL commands. You can set the CommandType property to Text and enter the appropriate DDL command in the CommandText property. Then call Command.ExecuteNonQuery to execute the command.
 

Typed DataSet objects allow you to work with data that is represented as members of the .NET common type system. This allows your applications to be aware of the types of data returned in a DataSet and serves to eliminate errors resulting from invalid casts, as any type mismatches are caught at compile time. Untyped DataSet objects, however, are useful if you do not know the structure of your data, and can be used with any data source.
 

Every data source on a form has an associated CurrencyManager object that keeps that of the “current” record with respect to bound controls. For convenience, all of the CurrencyManager objects represented on a form are exposed through the form’s BindingContext property. The Position of the CurrencyManager can be changed, allowing navigation through the records.
 

You can apply sort criteria to a DataView by setting the Sort property to the name of a column or columns to be sorted by. The data represented in a DataView object can be filtered by setting the RowFilter property to a valid filter expression.
 

An XmlDataDocument is an in-memory representation of data in a hierarchical XML format. Each XmlDataDocument is synchronized with a DataSet. Whenever changes are made to one object, the other is instantly updated. Thus, you can use the XmlDataDocument to perform XML manipulations on a DataSet.
 

The four major parts of a SELECT statement are SELECT, FROM, WHERE, and ORDER BY. SELECT specifies the fields to be retrieved. FROM specifies the table from which the records are to be retrieved. WHERE allows you to specify filter criteria for the records to be retrieved, and ORDER BY allows you to specify a sort order for the records.
 

ADO.NET handles most of the database communication for you behind-the-scenes. You would only use SQL statements when generating ad-hoc queries for the database. You execute SQL statements by using a DataCommand object. Statements the return records, such as SELECT statements, are executed using the ExecuteQuery method. You can also return a single value with a SELECT statement by using the ExecuteScalar method. To execute non-value returning statements, such as DELETE, INSERT INTO, or UPDATE statements, use the ExecuteNonQuery method.
 

SQL injection attacks occur when a malicious user attempts to execute SQL code by passing a SQL string to the application through user input. You can guard against SQL injection attacks by validating the format of all strings derived from user input that are used to form ad hoc SQL statements.
 

To read data from an XML file into a dataset, you can use the ReadXML method of the dataset, specifying the stream or file that contains the XML data. To write data to a file, you can use the WriteXML method of the dataset, again specifying either the file or the stream that represents the file. The GetXML method of the dataset can be used to retrieve a string representation of the XML data contained by a dataset.
 

Data access in ADO.NET relies on two entities: the DataSet, which stores data on the local machine, and the Data Provider, a set of components that mediates interaction between the program and the database.
 

The DataSet is a disconnected, in-memory representation of data. It can be thought of as a local copy of the relevant portions of a database. Data can be loaded into a DataSet from any valid data source, such as a SQL Server database, a Microsoft Access database, or an XML file. The DataSet persists in memory, and the data therein can be manipulated and updated independent of the database.
 

ADO.NET The data access architecture for the Microsoft .NET Framework. ADO.NET is built around a disconnected data access model that uses a set of classes called a Data Provider to retrieve data from a data source.
 

transaction A group of commands (treated as a single unit) that change the data stored in a database. The transaction ensures that the commands are handled in an all-or-nothing fashion—if one of the commands fails, all of the commands fail, and any data that was written to the database by the commands is backed out. In this way, transactions maintain the integrity of data in a database.
 

XML Schema A description of the data elements contained in an XML file. The XML Schema provides the names of the elements, their types, whether or not they are key fields, and other information.