DataReader (connected architecture. Connected architecture was built on the classes connection, command, datareader and transaction. )
DataReader is like a forward only recordset. It fetches one row at a time so very less network cost compare to DataSet(Fethces all the rows at a time). DataReader is readonly so we can’t do any transaction on them. DataReader will be the best choice where we need to show the data to the user which requires no transaction. As DataReader is forward only so we can’t fetch data randomly. .NET Data Providers optimizes the datareader to handle huge amount of data. To access one by one record from the DataReader, call Read() method of the DataReader whose return type is bool. When the next record was successfully read, the Read() method will return true and otherwise returns false.
DataAdapter : DataAdapter is used to transfer the data between database and dataset. It has commands like select, insert, update and delete. Select command is used to retrieve data from database and insert, update and delete commands are used to send changes to the data in dataset to database. It needs a connection to transfer the data.
DataSet is an in memory representation of a collection of Database objects including tables of a relational database schemas.
DataSet is always a bulky object that requires a lot of memory space compare to DataReader. We can say that the DataSet is a small database because it stores the schema and data in the application memory area. DataSet fetches all data from the datasource at a time to its memory area. So we can traverse through the object to get the required data like querying database.
- A datatable is an in-memory representation of a single database table. You can think of it as having columns and rows in the same way.
What is the use of DataView?
User-defined view of a table is contained in a DataView. A complete table or a small section of table depending on some criteria can be presented by an object of the DataView class. You can use this class to sort and find data within DataTable.
The DataView class has the following methods:
- Find() – Finds a row in a DataView by using sort key value.
- FindRows() – Uses the sort key value to match it with the columns of DataRowView objects. It returns an array of all the corresponding objects of DataRowView whose columns match with the sort key value.
- AddNew() – Adds a new row to the DataView object.
- Delete() – Deletes the specified row from the DataView object according to the specified index.
- In order to create a DataView from a DataTable, use instantiate the DataView object by passing DataTable as parameter in the constructor.
- DataView dView = new DataView(dTable);
The architecture of ADO.net in which data retrieved from database can be accessed even when connection to database was closed is called as disconnected architecture. Disconnected architecture of ADO.net was built on classes connection, dataadapter, commandbuilder and dataset and dataview.
Command object is used to execute a single query against a database.
Command object has the following important methods.
- ExecuteNonQuery() : Used to execute an SQL statement that doesn’t return any value like insert, update and delete. Return type of this method is int and it returns the no. of rows effected by the given statement.
- ExecuteScalar() : Used to execute an SQL statement and return a single value. When the select statement executed by executescalar() method returns a row and multiple rows, then the method will return the value of first column of first row returned by the query. Return type of this method is object.
- ExecuteReader() : Used to execute a select a statement and return the rows returned by the select statement as a DataReader. Return type of this method is DataReader.
What are basic methods of a DataAdapter?
These are the most commonly used methods of a DataAdapter:
Executes the Select command to fill the DataSet object with data from the data source. It an also be used to update (refresh) an existing table in a DataSet with changes made to the data in the original data source if there is a primary key in the table in the DataSet.
Uses the SelectCommand to extract just the schema for a table from the data source, and creates an empty table in the DataSet object with all the corresponding constraints.
Calls the respective InsertCommand, UpdateCommand, or DeleteCommand for each inserted, updated, or deleted row in the DataSet so as to update the original data source with the changes made to the content of the DataSet.
ADO.NET is a very important feature of .NET Framework, which is used to work with data that is stored in structured data sources, such as databases and XML files. The following are some of the important features of ADO.NET:
- Contains a number of classes that provide you with various methods and attributes to manage the communication between your application and data source.
- Enables you to access different data sources, such as Microsoft SQL Server, and XML, as per your requirements.
- What are the two fundamental objects in ADO.NET?
- DataReader and DataSet are the two fundamental objects in ADO.NET.
- Explain the DataAdapter.Update() and DataSet.AcceptChanges() methods.
- The DataAdapter.Update() method calls any of the DML statements, such as the UPDATE, INSERT, or DELETEstatements, as the case may be to update, insert, or delete a row in a DataSet. TheDataSet.Acceptchanges() method reflects all the changes made to the row since the last time theAcceptChanges() method was called.
What is the role of the DataSet object in ADO.NET?
One of the major component of ADO.NET is the DataSet object, which always remains disconnected from the database and reduces the load on the database.
How can you identify whether or not any changes are made to the DataSet object since it was last loaded?
The DataSet object provides the following two methods to track down the changes:
- The GetChanges() method – Returns the DataSet object, which is changed since it was loaded or since the AcceptChanges() method was executed.
- The HasChanges() method – Indicates if any changes occurred since the DataSet object was loaded or after a call to the AcceptChanges() method was made.
If you want to revert all changes since the DataSet object was loaded, use the RejectChanges() method.
Which property is used to check whether a DataReader is closed or opened?
The IsClosed property is used to check whether a DataReader is closed or opened. This property returns atrue value if a Data Reader is closed, otherwise a false value is returned
CommandBuilder: Easing the Work of the Programmer
Sometimes creating SQL statements could be lengthy job when dealing with many columns in a table. A CommandBuilder object reduces the burden of creating SQL statements for you. In other words, the CommandBuilder helps you to generate update, delete., and insert commands on a single database table for a data adapter.
construct the CommandBuilder by passing the DataAdapter into its constructor. The act of creating the CommandBuilder automatically cause the UPDATE, INSERT, and DELETE commands to be generated for the adapter:
// Create a command builder object
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
ext, fill the DataSet using the adapter and create an instance of the Employee DataTable from the DataSet:
// Create a dataset object
DataSet ds = new DataSet(“Employee Set”);
Now insert a new DataRow into the DataTable in memory and popular a row with your desired value using DataTable’sAddNew method. After that you call the DataRowCollection.Add method to add the row to the DataTable:
// Create a data table object and add a new row
DataTable EmployeeTable = ds.Tables[“Employees”];
DataRow row = EmployeeTable.NewRow();
row[“firstName”] = “Rodney”;
row[“LastName”] = “Dangerfield”;
row[“Title”] = “comedian”;
Finally you call DataAdapter’s Update method to update the DataTable change to the data source:
// update data adapter
you didn’t have to figure out how to create the InsertCommand for Employee table because the CommandBuilder did it for you.
What is connection pooling?
Connection pooling refers to the task of grouping database connections in cache to make them reusable because opening new connections every time to a database is a time-consuming process. Therefore, connection pooling enables you to reuse already existing and active database connections, whenever required, and increasing the performance of your application.
You can enable or disable connection pooling in your application by setting the pooling property to either true or false in connection string. By default, it is enabled in an application.
What is the difference between the Clone() and Copy() methods of the DataSet class?
The Clone() method copies only the structure of a DataSet. The copied structure includes all the relation, constraint, and DataTable schemas used by the DataSet. The Clone() method does not copy the data, which is stored in the DataSet.
The Copy() method copies the structure as well as the data stored in the DataSet.
Creating Row in Datatable
DataTable dTable = new DataTable();
DataRow row = dTable.NewRow ();
row[“Name”] = ” – Raja”;
row[“Address”] = “USA”;
// create column
DataColumn name = new DataColumn(“Name”, typeof(string));