Overview of ADO.net
- Components of ADO.net and their functions
ADO.net provides disconnected database access to make minimum resource (RAM,Page Table,Heap) use. Database connections are open as long as the connection is required. Afterwards, the connection is just closed. If connections are kept active and if new connections are used for new operations, the system resource will easily used up. Further, it can consume database licenses quickly.
ADO.net has two major entities. DataSet and Data Provider
Data set is in-memory representation of data. After querying a database, you may keep your data in a DataSet. DataSet may contain 0, one or more tables. The tables in DataSet are termed as DataTables. DataTables have two concepts/collections DataColumns, and DataRows. DataRows contain the data. DataSet also contains DataRelations collections that may be used to create relations among the DataTables in the DataSet. Additionally, DataSet contains ExtendedProperties collections to contain custom information about the DataSet
It is actually a collection of several components. Some data provider objects are:
- SQL Server Data Provider: To interact with MSSQL Server Databases
- OLE DB.Net data provider: May be used to interact with other databases.
- ODBC (.Net) Data provider
- Oracle Data Provider: For efficient interaction with Oracle Databases
Data Provider Components
- Connection Object: To connect to the database. Objects: SqlConnection(for MS Sql Server), OleDbConnection(for wide range of databases), ODBC Connection(to connect through ODBC), OracleConnection(for oracle). The main property of Connection objects are the ConnectionString.
- Command Object: like SqlCommand, OleDbCommand. After creating a connection a command object may be used to query the database or to run a stored procedure. It has methods like ExecuteNonQuery(For Insert,Update,Delete), ExecuteScalar(to access a single value),ExecuteReader(returns ResultSet, may be kept in a DataReader object)
- DataReader Object: Like, SqlDataReader, OleDbDataReader, [ODBCDataReader,OracleDataReader]. It is a read-only, forward only, connected data stream. It uses the connection exclusively and tries to use minimum system resources[contain only one row in the RAM]
- DataAdapter Object: It is the object that provides disconnected data access in .Net. DataAdapter fills a DataSet or DataTable by using it's Fill method. Unlike DataReader DataAdaptor can propagate the changes in DataSet/DataTable to the database using it's Update method. DataAdapter contains queries(select,update,insert,delete) in four properties like SelectCommand, InsertCommand,DeleteCommand,UpdateCommand. When fill method is called the data returned by SelectCommand query are saved in DataSets/DataTables. Remember Update is another method, that copies DataSet data to the Database.