Connected Environment
A connected environment is one in which a user or an application is constantly connected to a data source.
Advantages of connected environment:
· Data concurrency issues are easily control (At a time only one user can update the data in database)
· Data is always updated.
Disadvantages of connected environment:
· A constant network connection is required that may lead to network traffic logging.
· Scalability and performance issues in application. (If more than one user connected to database query processing may be slow.)
Disconnected Environment
A disconnected environment is one in which a user is not necessarily connected with a database. Connection is required only at the time of retrieval after that connection is close and if data is need to be updated again connection will be re established.
Advantages of disconnected environment:
· Multiple applications can simultaneously interact with the database.
· Improve scalability and performance of applications.
Disadvantages of disconnected environment:
· Data is not always updated.
· Data concurrency issues can occur when multiple users are updating the data to the data source.
Working in Connected Environment
In a connected environment, an application is connected to a data source dedicately. DataReader object is used for this, which is a component of the data provider. DataReader uses the connection object to connect to the database. It then uses the Command object to retrieve data and provides data to the application in a read only and forward only mode.
Command Object
It is a specific DML statement or a stored procedure that is used to retrieve, insert, delete or manipulate data in a data source. A data command is an instance of the OleDbCommand or SqlCommand class.
DbCommand Object
Used to execute command in a data source.It can be a DML command that is used to retrieve, insert, update or delete data in the database. It can also be DDL command that is used to create and modify the schema in the database.
Properties of DbCommand object
Property | Description |
CommandText | Gets or Sets the command text |
CommandTimeout | Gets or Sets the wait time before terminating the attempt to execute a command and generate an error. |
CommandType | Indicates or specifies how the CommandText property is interpreted |
Connection | Gets or Sets the connection used by the DbCommand object |
Container | Gets the IContainer interface |
DesignTimeVisible | Gets or sets a value indicating whether the command object should be visible in a customized interface control |
Parameters | Gets the collection of DbParameter object |
Site | Gets or Sets the ISite interface |
Transaction | Gets or Sets the DbTransaction class |
UpdatedRowSource | Gets or Sets the command results that are applied to the datarow when used by the update( ) method of a data adapter |
DataAdapter
It is a part of connected environment. It is an integral part of ADO.NET because data is transferred to and from a database through DataAdapter. DataAdapter retrieves data in a DataSet. A user actually modifies data in a DataSet and then changes are performed in database through DataAdapter.
In .NET Framework following DataAdapters are used:
· SqlDataAdapter
· OleDbDataAdapter
· OracleDataAdapter
· OdbcDataAdapter
A DataAdapter uses the connection object OleDbConnection, SqlConnection, OdbcConnectioon and OracleConnection provided by Data providers.
DataAdapter can use following properties and methods for various operations:
· SelectCommand: DataCommand used to retrieve data from a data source
· InsertCommand: DataCommand used to insert date into a DataSet
· UpdateCommand: DataCommand used to update a DataSet
· DeleteCommand: DataCommand used to delete data from a DataSet
· Fill( ): Fills the DataSet with the records from a database
· Update( ): It performs changes in a database
Following code snippet shows the use of DataAdapter:
// Create a connection object SqlConnection myconnection=new SqlConnection( ); // set a connection string myconnection.ConnectionString=”Data Source=SQLSERVER01;Initial Catalog=EMPLOYEE; User ID=sa; Password=abc#123456”; // create an object of DataSet class DataSet DataSet1=new DataSet( ); // create an object of SqlDataAdapter class SqlDataAdapter ad=new SqlDataAdapter( ); // pass the SQL query to the command object SqlCommand cmd=new SqlCommand( “select * from empdetails”, myconnection); // Fill the records into the dataset ad.SelectCommand=cmd; ad.Fill(DataSet1);
Table Mapping in Data Adapters
A data adapter handles data transfer between the database and the dataset through its properties and methods, and display data through the process of table mapping.
A Data Adapter uses the TableMapping property, a collection of DatatableMapping objects that is used for mapping between the database tables and the DataTable object in the dataset. There is one DataTableMapping object defined for each set of mapped tables. When the dataset is filled with records, the data adapter looks up each source column name in the TableMapping object, gets the matching column in the dataset table, and then writes the data to the DataTable in the dataset.
Working in Disconnected Environment
A disconnected environment is one in which an application is not directly connected to a data source. Data is stored in datasets and manipulations are performed there. After the data has been modified in the dataset, the changes are updated to the database.
DataSet and DataTables
A dataset is a memory based relational representation of data. It is a part of disconnected environment. A dataset is a disconnected, cached set of records that are retrieved from a database. The dataset acts like a virtual database containing tables, rows, and columns.
Types of DataSet
· Typed
· Untyped
Typed Dataset
A typed dataset is derived from DataSet class and has an associated XML schema, which is created at the time of creation of the dataset. XML schema contains information about the dataset structure such as the tables, columns and rows. Data is transferred from a database into a dataset and from the dataset to another component in the XML format.
The XML schema definition language is used to define the elements and attributes of XML documents.
Untyped DataSet
An untyped dataset does not have any associated XML Schema. In an untyped dataset the tables and columns are represented as collections. The structure of an untyped dataset is not known during compilation.
DataSet Object Model
Working with DataViews
A dataview is a part of disconnected environment. The System.Data namespace defines the DataView class that enables to create various views of the data stored in a datatable. A dataview can be used to sort or filter data in a datatable. It can also be used to add, modify and delete rows in a datatable.
A dataview provides a dynamic view of data stored in a datatable. These dynamic capabilities of the dataview make it ideal for data binding applications.
DataView object Model