Can you give an overview of ADO.NET architecture?
The most important section in ADO.NET architecture is “Data Provider”. Data Provider provides access to data source (SQL SERVER, ACCESS, ORACLE).In short it provides object to achieve functionalities like opening and closing connection, retrieve data, and update data. In the below figure, you can see the four main sections of a data provider:-
• Connection
• Command object (This is the responsible object to use stored procedures)
• Data Adapter (This object acts as a bridge between data store and dataset)
• Data reader (This object reads data from data store in forward only mode).
• Dataset object represents disconnected and cached data. If you see the diagram, it is not in direct connection with the data store (SQL SERVER, ORACLE etc) rather it talks with Data adapter, who is responsible for filling the dataset. Dataset can have one or more Data table and relations.
What is the use of connection object?
They are used to connect a data to a Command object.
• An OleDbConnection object is used with an OLE-DB provider.
• A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server.
Difference between OLEDB Provider and SqlClient ?
SQLClient .NET classes are highly optimized for the .net / sqlserver combination and achieve optimal results. The SqlClient data provider is fast. It's faster than the Oracle provider, and faster than accessing database via the OleDb layer. It's faster because it accesses the native library (which automatically gives you better performance), and it was written with lots of help from the SQL Server team.
Which is the default Provider Name of the Providers used to access the DataBase?
System.Data.SqlClient
What extra features does ADO.Net 2.0 have?
Bulk Copy Operation
Bulk copying of data from a data source to another data source is a newly added feature in ADO.NET 2.0. ADO.NET inrtoduces bulk copy classes which provide fastest way to transfer\ data from once source to the other. Each ADO.NET data provider has bulk copy classes. For example, in SQL .NET data provider, the bulk copy operation is handled by SqlBulkCopy class, which can read a DataSet, DataTable, DataReader, or XML objects.
Data Paging
A new method is introduced ExecutePageReader which takes three parameters - CommandBehavior, startIndex, and pageSize. So if you want to get rows ony from 10 - 20, you can simply call this method with start index as 10 and page size as 10.
Batch Update
If you want to update large number of data on set ADO.NET 2.0 provides UpdateBatchSize property, which allows you to set number of rows to be updated in a batch. This increases the performance dramatically as round trip to the server is minimized.
Load and Save Methods
In previous version of ADO.NET, only DataSet had Load and Save methods. The Load method can load data from objects such as XML into a DataSet object and Save method saves the data to a persistent media. Now DataTable also supports these two methods. You can also load a DataReader object into a DataTable by using the Load method.
New Data Controls
In toolbox you can see three new controls - DataGridView, DataConnector, and DataNavigator.
DataReader's New Execute Methods
Some new execute methods introduced are ExecutePageReader, ExecuteResultSet, and ExecuteRow.
Name the classes that are contained in System.Data NameSpace?
DataSet
DataTable
DataColumn
DataRow
DataRealation
Constraint
Name the classes are found in System.Data.Common NameSpace?
1)DataColumnMapping
2)DataTableMapping
DataReader
How do you create an instance of SqlDataReader class?To create an instance of SqlDataReader class, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor. You Cannot use SqlDataReader() constructor to create an instance of SqlDataReader class
//Error!
SqlDataReader ReaderObject = new SqlDataReader();
//Call the ExecuteReader method of the SqlCommand object
SqlCommand CommandObject = new SqlCommand();
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
Creating an instance of SqlDataReader class using SqlDataReader() constructor generates a compile time error - The type 'System.Data.SqlClient.SqlDataReader' has no constructors defined.
How do you programatically check if a specified SqlDataReader instance has been closed?Use the IsClosed property of SqlDataReader to check if a specified SqlDataReader instance has been closed. If IsClosed property returns true, the SqlDataReader instance has been closed else not closed.
How do you get the total number of columns in the current row of a SqlDataReader instance?
FieldCount property can be used to get the total number of columns in the current row of a SqlDataReader instance.
How do you retrieve two tables of data at the same time by using data reader?
Include 2 select statements either in a stored procedure or in a select command and call the ExecuteReader() method on the command object. This will automatically fill the DataReader with 2 Tables of data.
The datareader will always return the data from first table only. If you want to get the second table then you need to use ReaderObject.NextResult() method. The NextResult() method will return true if there is another table. The following code shows you how do it.
//Create the SQL Query with 2 Select statements
string SQLQuery = "Select * from Customers;Select * from Employees;";
//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand(SQLQuery, ConnectionObject);
//Open the connection
ConnectionObject.Open();
//Execute the command. Now reader object will have 2 tables of data.
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
//Loop thru the tables in the DataReader object
while (ReaderObject.NextResult())
{
while (ReaderObject.Read())
{
//Do Something
}
}
//Close the Reader
ReaderObject.Close();
//Close the Connection
ConnectionObject.Close();
Using ADO.NET Datareader a user extracted data from a database table having 5 records.What happens if another user adda 5 more records to the table same time.Can the first user extracted records become 10 instead of 5 or will it remain same 5? what about same case when ADO ? pls explain in detail.
It will remain 5, the DataReader object is a forward-only, read-only object, it can't be updated to read newly added records. DataReader object isn't available in ADO.
Which method is used to Gets the name of the specified column using DataReader?
GetName
How can you update the records in database using datareader?You cannot update. DataReader is just used for reading the data in forward only mode. You can achieve this using Dataset but not by DataReader.
Which ADO.NET object is very fast in getting data from database?
SqlDataReader object. (Even datasets also use SqlDataReader objects internally for retriving data from database.)
What is the difference between DataReader and DataAdapter?
1. Data Reader is read only forward only and much faster than DataAdapter.
2. If you use DataReader you have to open and close connection explicitly where as if you use DataAdapter the connection is automatically opened and closed.
3. DataReader is connection oriented where as Data Adapter is disconnected
Using ADO.NET Datareader a user extracts data from a database table having 1000 rows.He closed his browser in between. that is after fetching only 50 records.
What happens to the Datareader?will it remain connected? and will fetch 1000 records and what after? will garbage collector collect and dispose it soon?
DataReader isn't connected, so the rows will be lost and the object will be destroyed.
why datareader is forward only?
A DataReader is a stream of data that is returned from a database query. When the query is executed, the first row is returned to the DataReader via the stream. The stream then remains connected to the database, poised to retrieve the next record. The DataReader reads one row at a time from the database and can only move forward, one record at a time. As the DataReader reads the rows from the database, the values of the columns in each row can be read and evaluated, but they cannot be edited.
Generally we use datareader ado.net control where we don't need go to previous row like binding a dropdown or data repeater control. To keep it light weighted microsoft support forward only functionality in datareader control.
Typed and Untyped Datasets
What are typed datasets?
A typed dataset is a dataset that is first derived from the base DataSet class and then uses information in an XML Schema file (an .xsd file) to generate a new class. Information from the schema (tables, columns, and so on) is generated and compiled into this new dataset class as a set of first-class objects and properties.
A strongly typed DataSet is actually a class that inherits from the System.Data.DataSet class and adds a few extra features of its own. The class file is generated from the XSD file. You can regenerate the class file by right-clicking in the XSD's designer view and selecting Generate DataSet (alternatively, you can use the xsd.exe command-line utility). The class file actually contains a series of classes that inherit from and extend the DataSet, DataTable, DataRow, and EventArgs classes. Because of this inheritance, developers do not lose any functionality by using a strongly typed DataSet. For example, even though you can refer to a DataTable via a property with the same name as the table, you can still refer to the table through the Tables collection. The following two lines evaluate to the same DataTable object:
oDs.Tables["Orders"]
oDs.Orders
Why would you use typed datasets or Advantages of Typed Datasets?
Typed data sets provide three features that aren't part of the base DataSet class.
1)They provide a designer surface for laying out relationships and constraints.
2)They provide type-checked operations, i.e. retrieval, inserts and updates.
3)They provide a special constructor to add serialization support to your typed data set. All of the rest of the functionality, e.g. finding rows, comes from the DataSet and related classes themselves
What are the differences between typed and Untyped Datasets ?
Strongly Typed DataSet
1)It provides additional methods, properties and events and thus it makes it easier to use
2)You will get type mismatch and other errors at compile time.
3)You will get advantage of intelliSense in VS. NET
4)Performance is slower in case of strongly typed dataset
5)In complex environment, strongly typed dataset's are difficult to administer.
Untyped DataSet
1)It is not as easy to use as strongly typed dataset.
2)You will get type mismatch and other errors at runtime
3)You can't get an advantage of intelliSense.
4)Performance is faster in case of Untyped dataset.
5)Untyped datasets are easy to administer.
