ADO.NET Objects

ADO.NET includes many objects we can use to work with data.
A) The SqlConnection Object – To interact with a database, we must have a connection to it. The connection helps identify the database server, the database name, user name, password, and other parameters that are required for connecting to the data base. A connection object is used by command objects so they will know which database to execute the command on.
B) The SqlCommand Object – The process of interacting with a database means that we must specify the actions we want to occur. This is done with a command object. We use a command object to send SQL statements to the database. A command object uses a connection object to figure out which database to communicate with. We can use a command object alone, to execute a command directly, or assign a reference to a command object to an SqlDataAdapter, which holds a set of commands that work in a group of data.
C) SqlDataReader Object - Many data operations require that we only get a stream of data for reading. The data reader object allows us to obtain the results of a SELECT statement from a command object. For performance reasons, the data returned from a data reader is a fast forward – only stream of data. This means that we can only pull the data from the stream in a sequential manner. This is good for speed, but if we need to manipulate data, then a DataSet is a better object to work with.
D) The DataSet Object – DataSet objects are in – memory representations of data. They contain multiple Datatable objects, which contain columns and rows, just like normal database tables. We can even define relations between tables to create parent –child relationships. The DataSet is specifically designed to help manage data in memory and to support disconnected operations on data, when such a scenario make sense. The DataSet is an object that is used by all of the Data Providers, which is why it does not have a Data Provider specific prefix.
E) The SqlDataAdapter Object – Sometimes the data we work with is primarily read – only and you rarely need to make changes to the underlying data source. Some situations also call for caching data in memory to minimize the number of database calls for data that does not change. The data adapter makes it easy for us to accomplish these things by helping to manage data in a disconnected mode. The data adapter fills a DataSet object when reading the data and writes in a single batch when persisting changes back to the database. A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading form or writing to the database. Additionally, the data adapter contains command object references for SELECT, INSERT, UPDATE, and DELETE operations on the data. We will have a data adapter defined for each table in a DataSet and it will take care of all communication with the database for us. All we need to do is tell the data adapter when to load form or write to the database.