ADO.NET

ADO.NET is a set of class libraries that are part of the .NET Framework. The ADO.NET classes are generally divided into two types: connected classes and disconnected classes. The connected classes are those that are part of a namespace specific to a data source type. For example, the ADO.NET connected classes associated with SQL Server are part of the System.Data.SqlClient namespace. You use the connected classes to manage your connections to the SQL Server database and to access data in that database. The disconnected classes are part of the System.Data namespace and are independent from any data source. You use the disconnected classes to work with the data after it has been retrieved by the connected classes.
The disconnected classes never communicate directly with a data source. Figure 1 shows the more commonly used classes available in the System.Data.SqlClient and System.Data namespaces. The System.Data.SqlClient namespace includes the following connected classes specific to SQL Server:

• SqlConnection—Connects to the SQL Server .NET data provider in order to establish and manage the connection to the target database.
• SqlCommand—Contains the details necessary to issue a T-SQL command against a SQL Server database.
• SqlParameterCollection—Contains the collection of SqlParameter objects associated with a specific SqlCommand object. You access the collection through the SqlCommand object’s Parameters property.
• SqlParameter—Contains parameter-related information specific to a SqlCommand object.
• SqlDataReader—Provides efficient read-only access to the data retrieved through the SqlConnection and SqlCommand objects. The SqlDataReader is similar to a forward-only cursor.
• SqlDataAdapter—Provides a bridge between the connected classes and disconnected classes. This class includes the Fill and Update methods. Use the Fill method to populate a DataSet or DataTable object. Use the Update method to propagate updated data in a DataSet or DataTable object to the database.

The System.Data namespace includes the following disconnected classes:

• DataSet—Contains all the data retrieved through your connected objects. The DataSet object acts as a container for all DataTable objects and provides functionality that lets you work with the data in all the tables as single operations (such as saving data to a file).
• DataTableCollection—Contains the collection of DataTable objects associated with a specific DataSet object. You access the collection through the DataSet object’s Tables property.
• DataTable—Stores the data returned by your query. The data is stored in rows and columns, similar to how data is stored in a database table.
• DataColumnCollection—Contains the collection of DataColumn objects associated with a specific DataTable object. You access the collection through the DataTable object’s Columns property.
• DataColumn—Contains the metadata that describes the columns associated with a specific table. A DataColumn object doesn’t contain the stored data itself, only information about the column structure. The stored data is saved to DataRow objects.
• DataRowCollection—Contains the collection of DataRow objects associated with a specific DataTable object. You access the collection through the DataTable object’s Rows property.
• DataRow—Contains the actual data that is retrieved through your connected objects. Each DataRow object contains the data from one row of your query results.

In general, the disconnected objects act as an offline data cache for the data you retrieve through your connected objects. As a result, you can view and modify the data in a dataset without being connected to the data source.

 http://www.sqlmag.com/article/scripting/accessing-sql-server-data-from-powershell-part-1

 

The Basics of ADO.NET

You will spend the most time working with five objects. They are the Connection object, the Command object, the DataReader object, the DataSet object, and the DataAdapter object.

ADO.NET provides you with predefined objects and methods. These objects and methods insulate you from the disparate data providers with which your application must interact.

ADO.NET implements four main data provider objects. They are Connection, Command, DataReader, and DataAdapter. Microsoft provides an implementation of these objects for SQL Server. They are called the SqlConnection, SqlCommand, SqlDataReader, and SqlDataAdapter objects. They ship as part of the .NET Common Language Runtime (CLR). The Connection object represents a single persistent connection to a data source. The Command object represents a string that ADO.NET can execute via a connection. The DataReader object provides you with a very efficient set of results that are based on a Command object. Finally, the DataAdapter object provides a link between the data provider objects and the DataSet object.

Working with a SQL Connection Object

The ADO.NET Connection object provides you with a persistent connection to data. After you have that connection, you can return rowsets and update data. When working with the Connection object, you can pass all or some of the following information to the provider:

• Server name
• Provider name
• UserID and password
• Default database
• Other provider-specific information
•