Q1. What is the namespace in which .NET has the data functionality class?
Following are the namespaces provided by .NET for data management:-System. Data
This contains the basic objects used for accessing and storing relational data, such as DataSet, DataTable, and Data Relation. Each of these is independent of the type of data source and the way we connect to it.
System.Data.OleDB
It contains the objects that we use to connect to a data source via an OLE-DB provider, such as OleDbConnection, OleDbCommand, etc. These objects inherit from the common base classes, and so have the same properties, methods, and events as the SqlClient equivalents.
System.Data.SqlClient:
This contains the objects that we use to connect to a data source via the Tabular Data Stream (TDS) interface of Microsoft SQL Server (only). This can generally provide better performance as it removes some of the intermediate layers required by an OLE-DB connection.
System.XML
This Contains the basic objects required to create, read, store, write, and manipulate XML documents according to W3C recommendations.
Q2. 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.
Figure: - 9.1 ADO.NET Architecture
. Data View" object is used to sort and filter data in Data table.
Note:- This is one of the favorite questions in .NET. Just paste the picture in your mind and during interview try to refer that image.
Q3. What are the two fundamental objects in ADO.NET?
Data reader and Dataset are the two fundamental objects in ADO.NET.Q4 What is difference between dataset and data reader?
Following are some major differences between dataset and data reader:-. Data Reader provides forward-only and read-only access to data, while the Dataset object can hold more than one table (in other words more than one row set) from the same data source as well as the relationships between them.
. Dataset is a disconnected architecture while data reader is connected architecture.
. Dataset can persist contents while data reader cannot persist contents, they are forward only.
Q5 What are major difference between classic ADO and ADO.NET?
Following are some major differences between both :-. In ADO we have recordset and in ADO.NET we have dataset.. In recordset we can only have one table. If we want to accommodate more than one tables we need to do inner join and fill the recordset. Dataset can have multiple tables.
. All data persist in XML as compared to classic ADO where data persisted in Binary format also.
Q6 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
Q7 What is the use of command objects?
They are used to connect connection object to Data reader or dataset. Following are the methods provided by command object:-. ExecuteNonQuery: -
Executes the command defined in the Command Text property against the connection defined in the Connection property for a query that does not return any row (an UPDATE, DELETE, or INSERT). Returns an Integer indicating the number of rows affected by the query.
. ExecuteReader: -
Executes the command defined in the Command Text property against the connection defined in the Connection property. Returns a "reader" object that is connected to the resulting row set within the database, allowing the rows to be retrieved.
. ExecuteScalar: -
Executes the command defined in the Command Text property against the connection defined in the Connection property. Returns only single value (effectively the first column of the first row of the resulting row set any other returned columns and rows are discarded. It is fast and efficient when only a "singleton" value is required
Q8 What is the use of data adapter?
These objects connect one or more Command objects to a Dataset object. They provide logic that would get data from the data store and populates the tables in the Dataset, or pushes the changes in the Dataset back into the data store.. An OleDbDataAdapter object is used with an OLE-DB provider
. A SqlDataAdapter object uses Tabular Data Services with MS SQL Server.
Q9 What are basic methods of Data adapter?
There are three most commonly used methods of Data adapter:-Fill: -
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.
FillSchema :-
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.Update:- 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. This is a little like the UpdateBatch method provided by the ADO Recordset object, but in the DataSet it can be used to update more than one table.
Q10 What is Dataset object?
The Dataset provides the basis for disconnected storage and manipulation of relational data. We fill it from a data store, work with it while disconnected from that data store, then reconnect and flush changes back to the data store if required.Q11 What are the various objects in Dataset?
Dataset has a collection of Data Table object within the Tables collection. Each Data Table object contains a collection of Data Row objects and a collection of Data Column objects. There are also collections for the primary keys, constraints, and default values used in this table, which is called as constraint collection, and the parent and child relationships between the tables. Finally, there is a Default View object for each table. This is used to create a Data View object based on the table, so that the data can be searched, filtered, or otherwise manipulated while displaying the data.Note: - Look back again to the main diagram for ADO.NET architecture for visualizing this answer in pictorial form.
Q12 How can we connect to Microsoft Access, FoxPro, and Oracle etc?
Microsoft provides System.Data.OleDb namespace to communicate with databases like success , Oracle etc. In short, any OLE DB-Compliant database can be connected using System.Data.OldDb namespace.Note :- Small sample of OLEDB is provided in "WindowsAppOleDb" which uses "Nwind.mdb" in bin directory to display data in Listbox.
Private Sub loadData()
Dim strPath As String
strPath = AppDomain.CurrentDomain.BaseDirectory
Dim objOLEDBCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & strPath & "Nwind.mdb")
Dim objOLEDBCommand As OleDbCommand
Dim objOLEDBReader As OleDbDataReader
Try
objOLEDBCommand = New OleDbCommand("Select FirstName from Employees")
objOLEDBCon.Open()
objOLEDBCommand.Connection = objOLEDBCon
objOLEDBReader = objOLEDBCommand.ExecuteReader()
Do While objOLEDBReader.Read()
lstNorthwinds.Items.Add(objOLEDBReader.GetString(0))
Loop
Catch ex As Exception
Throw ex
Finally
objOLEDBCon.Close()
End Try
End Sub
The main heart is the "Load data ()" method which actually loads the data in list box.
Note:- This source code has the connectionstring hard coded in the program itself which is not a good programming practice. For windows application the best place to store connectionstring is "App.config". Also note that "AppDomain.CurrentDomain.BaseDirectory" function gives the current path of the running exe which is "BIN" and the MDB file is in that directory. Also note that the final block which executes irrespective that there is error or not. Thus ensuring that all the connection to the datastore is freed. Its best practice to put all clean up statements in finally block thus ensuring that the resources are deallocated properly.
ADO.NET provides the SqlCommand object, which provides the functionality of executing stored procedures.
Note :- Sample code is provided in folder "WindowsSqlClientCommand". There are two stored procedures created in same database "Employees" which was created for the previous question.
CREATE PROCEDURE SelectByEmployee @FirstName nvarchar(200) AS
Select FirstName from Employees where FirstName like @FirstName + '%'
CREATE PROCEDURE SelectEmployee AS
Select FirstName from Employees
If txtEmployeeName.Text.Length = 0 Then
objCommand = New SqlCommand("SelectEmployee")
Else
objCommand = New SqlCommand("SelectByEmployee")
objCommand.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar, 200)
objCommand.Parameters.Item("@FirstName").Value = txtEmployeeName.Text.Trim()
End If
In the above sample, not much has been changed only that the SQL is moved to the stored procedures. There are two stored procedures one is "Select Employee" which selects all the employees and the other is "SelectByEmployee" which returns employee name starting with a specific character. As you can see to provide parameters to the stored procedures, we are using the parameter object of the command object. In such question interviewer expects two simple answers one is that we use command object to execute stored procedures and the parameter object to provide parameter to the stored procedure. Above sample is provided only for getting the actual feel of it. Be short be nice and get a job.
Command method Execute reader takes a parameter called as Command Behavior where in we can specify saying close connection automatically after the Data reader is close.
PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.CloseConnection)
PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.SchemaOnly)
Again, CommandBehaviour enumeration provides two values Single Result and Single Row. If you are expecting a single value then pass "CommandBehaviour.SingleResult" and the query is optimized accordingly, if you are expecting single row then pass "CommandBehaviour.SingleRow" and query is optimized according to single row.
Config files are the best places to store connection strings. If it is a web-based application "Web.config" file will be used and if it is a windows application "App.config" files will be used.
Note:- XML is one of the most important leap between classic ADO and ADO.NET. So this question is normally asked more generally how can we convert any data to XML format. Best answer is convert in to dataset and use the below methods.
. ReadXML
Read's a XML document in to Dataset.
. GetXML
This is a function, which returns the string containing XML document.
. Writexml
This writes a XML data to disk.
Read's a XML document in to Dataset.
. GetXML
This is a function, which returns the string containing XML document.
. Writexml
This writes a XML data to disk.
Dataset has "Accept Changes" method, which commits all the changes since last time "Accept changes" has been executed.
Note :- This book does not have any sample of Acceptchanges. We leave that to readers as homework sample. But yes from interview aspect that will be enough.
Twist: - How can we cancel all changes done in dataset?
Twist: - How do we get values, which are changed, in a dataset?
For tracking down changes, Dataset has two methods, which comes as rescue "Get Changes "and "Has Changes".
Get Changes
Get Changes
Returns dataset, which are changed since it, was loaded, or since Accept changes was executed.
Has Changes
Or abandon all changes since the dataset was loaded use "Reject Changes This property indicates that has any changes been made since the dataset was loaded or accept changes method was executed.
Note:- One of the most misunderstood things about these properties is that it tracks the changes of actual database. That is a fundamental mistake; actually the changes are related to only changes with dataset and have nothing to with changes happening in actual database. As dataset are disconnected and do not know anything about the changes happening in actual database.
Note:- One of the most misunderstood things about these properties is that it tracks the changes of actual database. That is a fundamental mistake; actually the changes are related to only changes with dataset and have nothing to with changes happening in actual database. As dataset are disconnected and do not know anything about the changes happening in actual database.
"Data table" provides "NewRow" method to add new row to "Data Table". "Data Table" has "DataRowCollection" object that has all rows in a "Data Table" object. Following are the methods provided by "DataRowCollection" object:-
Add
Adds a new row in Data Table
Remove
It removes a "Data Row" object from "Data Table"
Remove At
It removes a "Data Row" object from "Data Table" depending on index position of the "Data Table".
Add
Adds a new row in Data Table
Remove
It removes a "Data Row" object from "Data Table"
Remove At
It removes a "Data Row" object from "Data Table" depending on index position of the "Data Table".
"Data View" represents a complete table or can be small section of rows depending on some criteria. It is best used for sorting and finding data with in "data table".
Data view has the following methods:-
Find
It takes an array of values and returns the index of the row.
Find Row
This also takes array of values but returns a collection of "Data Row".
If we want to manipulate data of "Data Table" object create "Data View" (Using the "Default View" we can create "Data View" object) of the "Data Table" object and use the following functionalities:-
Add New
Adds a new row to the "Data View" object.
Delete
Deletes the specified row from "Data View" object.
Data view has the following methods:-
Find
It takes an array of values and returns the index of the row.
Find Row
This also takes array of values but returns a collection of "Data Row".
If we want to manipulate data of "Data Table" object create "Data View" (Using the "Default View" we can create "Data View" object) of the "Data Table" object and use the following functionalities:-
Add New
Adds a new row to the "Data View" object.
Delete
Deletes the specified row from "Data View" object.
Twist: - Why is Dataset slower than Data Reader is?Fourth point is the answer to the twist.
Note:- This is my best question and we expect everyone to answer it. It is asked almost 99% in all companies....Basic very Basic cram it.
Following are the major differences between "Dataset" and "Data Reader":-
. "Dataset" is a disconnected architecture, while "Data Reader" has live connection while reading data. If we want to cache data and pass to a different tier "Dataset" forms the best choice and it has decent XML support.
. When application needs to access data from more than one table "Dataset" forms the best choice.
. If we need to move back while reading records, "data reader" does not support this functionality.
. However, one of the biggest drawbacks of Dataset is speed. As "Dataset" carry considerable overhead because of relations, multiple table's etc speed is slower than "Data Reader". Always try to use "Data Reader" wherever possible, as it is meant especially for speed performance.
Following are the major differences between "Dataset" and "Data Reader":-
. "Dataset" is a disconnected architecture, while "Data Reader" has live connection while reading data. If we want to cache data and pass to a different tier "Dataset" forms the best choice and it has decent XML support.
. When application needs to access data from more than one table "Dataset" forms the best choice.
. If we need to move back while reading records, "data reader" does not support this functionality.
. However, one of the biggest drawbacks of Dataset is speed. As "Dataset" carry considerable overhead because of relations, multiple table's etc speed is slower than "Data Reader". Always try to use "Data Reader" wherever possible, as it is meant especially for speed performance.
objCommand.CommandText = "Table1"
objDataAdapter.Fill(objDataSet, "Table1")
objCommand.CommandText = "Table2"
objDataAdapter.Fill(objDataSet, "Table2")
Above is a sample code, which shows how to load multiple "Data Table" objects in one "Dataset" object. Sample code shows two tables "Table1" and "Table2" in object ObjDataSet.
lstdata.DataSource = objDataSet.Tables("Table1").DefaultView
In order to refer "Table1" Data Table, use Tables collection of Datasets and the Default view object will give you the necessary output.
objDataAdapter.Fill(objDataSet, "Table1")
objCommand.CommandText = "Table2"
objDataAdapter.Fill(objDataSet, "Table2")
Above is a sample code, which shows how to load multiple "Data Table" objects in one "Dataset" object. Sample code shows two tables "Table1" and "Table2" in object ObjDataSet.
lstdata.DataSource = objDataSet.Tables("Table1").DefaultView
In order to refer "Table1" Data Table, use Tables collection of Datasets and the Default view object will give you the necessary output.
Dim objRelation As DataRelation
objRelation=New
DataRelation("CustomerAddresses",objDataSet.Tables("Customer").Columns("Custid")
,objDataSet.Tables("Addresses").Columns("Custid_fk"))
objDataSet.Relations.Add(objRelation)
Relations can be added between "Data Table" objects using the "Data Relation" object. Above sample, code is trying to build a relationship between "Customer" and "Addresses" "Data table" using "Customer Addresses" "Data Relation" object.
objRelation=New
DataRelation("CustomerAddresses",objDataSet.Tables("Customer").Columns("Custid")
,objDataSet.Tables("Addresses").Columns("Custid_fk"))
objDataSet.Relations.Add(objRelation)
Relations can be added between "Data Table" objects using the "Data Relation" object. Above sample, code is trying to build a relationship between "Customer" and "Addresses" "Data table" using "Customer Addresses" "Data Relation" object.
Q26 What is the use of Command Builder?
Command Builder builds "Parameter" objects automatically. Below is a simple code, which uses command builder to load its parameter objects.
Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)
pobjCommandBuilder.DeriveParameters(pobjCommand)
Be careful while using "Derive Parameters" method as it needs an extra trip to the Data store, which can be very inefficient
Command Builder builds "Parameter" objects automatically. Below is a simple code, which uses command builder to load its parameter objects.
Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)
pobjCommandBuilder.DeriveParameters(pobjCommand)
Be careful while using "Derive Parameters" method as it needs an extra trip to the Data store, which can be very inefficient
In pessimistic locking when user wants to update data it locks the record and till then no one can update data. Other user's can only view the data when there is pessimistic locking.
In optimistic locking multiple users can open the same record for updating, thus increase maximum concurrency. Record is only locked when updating the record. This is the most preferred way of locking practically. Now a days in browser based application it is very common and having pessimistic locking is not a practical solution.
In optimistic locking multiple users can open the same record for updating, thus increase maximum concurrency. Record is only locked when updating the record. This is the most preferred way of locking practically. Now a days in browser based application it is very common and having pessimistic locking is not a practical solution.
Following are the ways to implement locking using ADO.NET:-
. When we call "Update" method of Data Adapter it handles locking internally. If the Dataset values are not matching with current data in Database, it raises concurrency exception error. We can easily trap this error using Try. Catch block and raise appropriate error message to the user.
. Define a Date time stamp field in the table. When actually you are firing the UPDATE SQL statements, compare the current timestamp with one existing in the database. Below is a sample SQL which checks for timestamp before updating and any mismatch in timestamp it will not update the records. This I the best practice used by industries for locking.
Update table1 set field1=@test where Last Timestamp=@Current Timestamp
. Check for original values stored in SQL SERVER
. When we call "Update" method of Data Adapter it handles locking internally. If the Dataset values are not matching with current data in Database, it raises concurrency exception error. We can easily trap this error using Try. Catch block and raise appropriate error message to the user.
. Define a Date time stamp field in the table. When actually you are firing the UPDATE SQL statements, compare the current timestamp with one existing in the database. Below is a sample SQL which checks for timestamp before updating and any mismatch in timestamp it will not update the records. This I the best practice used by industries for locking.
Update table1 set field1=@test where Last Timestamp=@Current Timestamp
. Check for original values stored in SQL SERVER
and actual changed values. In stored procedure check before updating that the old data is same as the current Example in the below shown SQL before updating field1 we check that is the old field1 value same. If not then some one else has updated and necessary action has to be taken.
Update table1 set field1=@test where field1 = @oldfield1value
Locking can be handled at ADO.NET side or at SQL SERVER side i.e. in stored procedures. For more details of how to implementing locking in SQL SERVER read "What are different locks in SQL SERVER?" in SQL SERVER chapter.
Update table1 set field1=@test where field1 = @oldfield1value
Locking can be handled at ADO.NET side or at SQL SERVER side i.e. in stored procedures. For more details of how to implementing locking in SQL SERVER read "What are different locks in SQL SERVER?" in SQL SERVER chapter.
The most common sequence of steps that would be performed while developing a transactional application is as follows:
. Open a database connection using the Open method of the connection object.
. Begin a transaction using the Begin Transaction method of the connection object. This method provides us with a transaction object that we will use later to commit or rollback the transaction. Note that changes caused by any queries executed before calling the Begin Transaction method will be committed to the database immediately after they execute. Set the Transaction property of the command object to the above mentioned transaction object.
. Execute the SQL commands using the command object. We may use oneormorecommand objects for this purpose, as long as the Transaction property of all the objects is set to a valid transaction object.
. Commit or roll back the transaction using the Commit or Rollback method of the transaction object.
. Close the database connection.
. Begin a transaction using the Begin Transaction method of the connection object. This method provides us with a transaction object that we will use later to commit or rollback the transaction. Note that changes caused by any queries executed before calling the Begin Transaction method will be committed to the database immediately after they execute. Set the Transaction property of the command object to the above mentioned transaction object.
. Execute the SQL commands using the command object. We may use oneormorecommand objects for this purpose, as long as the Transaction property of all the objects is set to a valid transaction object.
. Commit or roll back the transaction using the Commit or Rollback method of the transaction object.
. Close the database connection.
Clone: - It only copies structure, does not copy data.
Copy: - Copies both structure and data.
Copy: - Copies both structure and data.
There two main basic differences between record set and dataset:-
. With dataset you an retrieve data from two databases like oracle and sql server and merge them in one dataset , with record set this is not possible
. All representation of Dataset is using XML while record set uses COM.
. Record set cannot be transmitted on HTTP while Dataset can be.
. With dataset you an retrieve data from two databases like oracle and sql server and merge them in one dataset , with record set this is not possible
. All representation of Dataset is using XML while record set uses COM.
. Record set cannot be transmitted on HTTP while Dataset can be.
When a connection is opened first time, a connection pool is created and is based on the exact match of the connection string given to create the connection object. Connection pooling only works if the connection string is the same. If the connection string is different, then a new connection will be opened, and connection pooling will not be used.
Figure 9.5: - Connection Pooling action.
Let us try to explain the same pictorially. In the above figure, you can see there are three requests "Request1", "Request2", and "Request3". "Request1" and "Request3" have same connection string so no new connection object is created for "Request3" as the connection string is same. They share the same object "ConObject1". However, new object "ConObject2" is created for "Request2" as the connection string is different.
Note: - The difference between the connection string is that one has "User id=sa" and other has "User id=Testing".
Note: - The difference between the connection string is that one has "User id=sa" and other has "User id=Testing".
Maximum pool size decides the maximum number of connection objects to be pooled. If the maximum pool size is reached and there is no usable connection available the request is queued until connections are released back in to pool. So it's always a good habit to call the close or dispose method of the connection as soon as you have finished work with the connection object.
For .NET it is enabled by default but if you want to just make sure set Pooling=true in the connection string. To disable connection pooling set Pooling=false in connection string if it is an ADO.NET Connection. If it is an OLEDBConnection object set OLE DB Services=-4 in the connection string.
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.
