What is a dataset?
A dataset is the local repository of the data used to store the tables and disconnected record set. When using disconnected architecture, all the updates are made locally to dataset and then the updates are performed to the database as a batch.
What is a data adapter?
A data adapter is the component that exists between the local repository (dataset) and the physical database. It contains the four different commands (SELECT, INSERT, UPDATE and DELETE). It uses these commands to fetch the data from the DB and fill into the dataset and to perform updates done in the dataset to the physical database. It is the data adapter that is responsible for opening and closing the database connection and communicates with the dataset.
What is a database connection?
A database connection represents a communication channel between you application and database management system (DBMS). The application uses this connection to pass the commands and queries to the database and obtain the results of the operations from the database.
What is a database command?
A database command specifies which particular action you want to perform to the database. The commands are in the form of SQL (Structured Query Language). There are four basic SQL statements that can be passed to the database.
SQL SELECT Statement
This query is used to select certain columns of certain records from a database table.
A dataset is the local repository of the data used to store the tables and disconnected record set. When using disconnected architecture, all the updates are made locally to dataset and then the updates are performed to the database as a batch.
What is a data adapter?
A data adapter is the component that exists between the local repository (dataset) and the physical database. It contains the four different commands (SELECT, INSERT, UPDATE and DELETE). It uses these commands to fetch the data from the DB and fill into the dataset and to perform updates done in the dataset to the physical database. It is the data adapter that is responsible for opening and closing the database connection and communicates with the dataset.
What is a database connection?
A database connection represents a communication channel between you application and database management system (DBMS). The application uses this connection to pass the commands and queries to the database and obtain the results of the operations from the database.
What is a database command?
A database command specifies which particular action you want to perform to the database. The commands are in the form of SQL (Structured Query Language). There are four basic SQL statements that can be passed to the database.
SQL SELECT Statement
This query is used to select certain columns of certain records from a database table.
SELECT * from emp// sql steaments
selects all the fields of all the records from the table name ‘emp’
SELECT empno, ename from emp
selects the fields empno and ename of all records from the table name ‘emp’
selects the fields empno and ename of all records from the table name ‘emp’
SELECT * from emp where empno <>
selects all those records from the table name ‘emp’ that have the value of the field empno less than 100
selects all those records from the table name ‘emp’ that have the value of the field empno less than 100
SELECT * from article, author where article.authorId = author.authorId
selects all those records from the table name ‘article’ and ‘author’ that have same value of the field authorIdSQL INSERT StatementThis query is used to insert a record to a database table.
selects all those records from the table name ‘article’ and ‘author’ that have same value of the field authorIdSQL INSERT StatementThis query is used to insert a record to a database table.
INSERT INTO emp(empno, ename) values(101, ‘John Guttag’)
inserts a record to emp table and set its empno field to 101 and its ename field to ‘John Guttag’
SQL UPDATE Statement
This query is used to edit an already existing record in a database table.
inserts a record to emp table and set its empno field to 101 and its ename field to ‘John Guttag’
SQL UPDATE Statement
This query is used to edit an already existing record in a database table.
UPDATE emp SET ename =‘Eric Gamma’ WHERE empno = 101
updates the record whose empno field is 101 by setting its ename field to ‘Eric Gamma’
SQL DELETE Statement
This query is used to delete the existing record(s) from the database table
updates the record whose empno field is 101 by setting its ename field to ‘Eric Gamma’
SQL DELETE Statement
This query is used to delete the existing record(s) from the database table
DELETE FROM emp WHERE empno = 101deletes the record whose empno field is 101 from the emp table
What is a data reader?
The data reader is a component that reads the data from the database management system and provides it to the application. The data reader works in the connected manner; it reads a record from the DB, pass it to the application, then reads another and so on.How do
different components of ADO.Net interact with each other in disconnected architecture?The Data Adapter contains in it the Command and Connection object. It uses the connection object to connect to the database, execute the containing command, fetch the result and update the DataSet.
What is a data reader?
The data reader is a component that reads the data from the database management system and provides it to the application. The data reader works in the connected manner; it reads a record from the DB, pass it to the application, then reads another and so on.How do
different components of ADO.Net interact with each other in disconnected architecture?The Data Adapter contains in it the Command and Connection object. It uses the connection object to connect to the database, execute the containing command, fetch the result and update the DataSet.
What does it mean by Dot Net Framework Data Provider?
Dot Net Framework Data Provider is a set of classes that establishes the database communication between an application and the database management system based on the standards of ADO.Net framework. Different data providers provide specialized and optimized connectivity to particular database management system or to a particular class of DBMS. For example, the MS SQL Server data provider provides the optimized connectivity between dot net application and MS SQL Server DBMS while the OLEDB data provider provides the uniform connectivity between dot net application and the OLEDB databases.
What is ADO.Net?
Most of the today’s applications need to interact with database systems to persist, edit or view data. In .Net data access service is provided through ADO.Net (ActiveX Data Object in Dot Net) components. ADO.Net is an object oriented framework that allows you to interact with database systems. We usually interact with database systems through SQL queries or stored procedures. ADO.Net encapsulates our queries and commands to provide a uniform access to various database management systems.
ADO.Net is a successor of ADO (ActiveX Data Object). The prime features of ADO.Net are its disconnected data access architecture and XML integration.
What does it mean by disconnected data access architecture of ADO.Net?
ADO.Net introduces the concept of disconnected data architecture. In traditional data access components, you make a connection to the database system and then interact with it through SQL queries using the connection. The application stays connected to the DB system even when it is not using DB services. This commonly wastes the valuable and expensive database resource as most of the time applications only query and view the persistent data. ADO.Net solves this problem by managing a local buffer of persistent data called data set. Your application automatically connects to the database server when it needs to pass some query and then disconnects immediately after getting the result back and storing it in dataset. This design of ADO.Net is called disconnected data architecture and is very much similar to the connection less services of http over the internet. It should be noted that ADO.Net also provides the connection oriented traditional data access services.
ADO.Net introduces the concept of disconnected data architecture. In traditional data access components, you make a connection to the database system and then interact with it through SQL queries using the connection. The application stays connected to the DB system even when it is not using DB services. This commonly wastes the valuable and expensive database resource as most of the time applications only query and view the persistent data. ADO.Net solves this problem by managing a local buffer of persistent data called data set. Your application automatically connects to the database server when it needs to pass some query and then disconnects immediately after getting the result back and storing it in dataset. This design of ADO.Net is called disconnected data architecture and is very much similar to the connection less services of http over the internet. It should be noted that ADO.Net also provides the connection oriented traditional data access services.
What does it mean by disconnected data access architecture of ADO.Net?ADO.Net introduces the concept of disconnected data architecture. In traditional data access components, you make a connection to the database system and then interact with it through SQL queries using the connection. The application stays connected to the DB system even when it is not using DB services. This commonly wastes the valuable and expensive database resource as most of the time applications only query and view the persistent data. ADO.Net solves this problem by managing a local buffer of persistent data called data set. Your application automatically connects to the database server when it needs to pass some query and then disconnects immediately after getting the result back and storing it in dataset. This design of ADO.Net is called disconnected data architecture and is very much similar to the connection less services of http over the internet. It should be noted that ADO.Net also provides the connection oriented traditional data access services.
What does it mean by connected data access architecture of ADO.Net?
In the connected environment, it is your responsibility to open and close the database connection. You first establish the database connection, perform the interested operations to the database and when you are done, close the database connection. All the changes are done directly to the database and no local (memory) buffer is maintained.
In the connected environment, it is your responsibility to open and close the database connection. You first establish the database connection, perform the interested operations to the database and when you are done, close the database connection. All the changes are done directly to the database and no local (memory) buffer is maintained.
What's the difference between accessing data with dataset or data reader?
The dataset is generally used when you like to employ the disconnected architecture of the ADO.Net. It reads the data into the local memory buffer and perform the data operations (update, insert, delete) locally to this buffer.
The data reader, on the other hand, is directly connected to the database management system. It passes all the queries to the database management system, which executes them and returns the result back to the application.
Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests.
The dataset is generally used when you like to employ the disconnected architecture of the ADO.Net. It reads the data into the local memory buffer and perform the data operations (update, insert, delete) locally to this buffer.
The data reader, on the other hand, is directly connected to the database management system. It passes all the queries to the database management system, which executes them and returns the result back to the application.
Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests.
What are the performance considerations when using dataset?
Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests.
However, since the dataset stores the records in the local buffer in the hierarchical form, it does take up more resources and may affect the overall performance of the application.
Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests.
However, since the dataset stores the records in the local buffer in the hierarchical form, it does take up more resources and may affect the overall performance of the application.
How to select dataset or data reader?
The data reader is more useful when you need to work with large number of tables, database in non-uniform pattern and you need not execute the large no. of queries on few particular table.
When you need to work on fewer no. of tables and most of the time you need to execute queries on these fewer tables, you should go for the dataset.
It also depends on the nature of application. If multiple users are using the database and the database needs to be updated every time, you must not use the dataset. For this, .Net provides the connection oriented architecture. But in the scenarios where instant update of database is not required, dataset provides optimal performance by making the changes locally and connecting to database later to update a whole batch of data. This also reduces the network bandwidth if the database is accessed through network.
Disconnected data access is suited most to read only services. On the down side, disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time (e.g., Airline Reservation System).
The data reader is more useful when you need to work with large number of tables, database in non-uniform pattern and you need not execute the large no. of queries on few particular table.
When you need to work on fewer no. of tables and most of the time you need to execute queries on these fewer tables, you should go for the dataset.
It also depends on the nature of application. If multiple users are using the database and the database needs to be updated every time, you must not use the dataset. For this, .Net provides the connection oriented architecture. But in the scenarios where instant update of database is not required, dataset provides optimal performance by making the changes locally and connecting to database later to update a whole batch of data. This also reduces the network bandwidth if the database is accessed through network.
Disconnected data access is suited most to read only services. On the down side, disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time (e.g., Airline Reservation System).
How XML is supported in ADO.Net?
The dataset is represented in the memory as an XML document. You can fill the dataset by XML and can also get the result in the form of XML. Since XML is an international and widely accepted standard, you can read the data using the ADO.Net in the XML form and pass it to other applications using Web Service. These data consuming application need not be the essentially Dot Net based. They may be written with Java, C++ or any other programming language and running on any platform.
Hey, thanks so much for posting this article.
ReplyDeleteThanks! if you want, you can check out my blog for ADO.NET Interview Questions and Answers
ADO.NET Interview Questions and Answers
Thanks
Joya