Ado Net Interview Questions and Answers

Answer: The following namespaces are available in .NET. System.Data This namespace is the base of ADO.NET. It provides all the classes that are used by data providers. The most import class that it supports is DataSet. It also contains classes to represent tables, columns, rows, relation and the constraint class. System.Data.Common This namespace defines common classes that are used as base classes for data providers. These classes are used by all data providers. Examples are DbConnection and DbDataAdapter System.Data.OleDb This namespace provides classes that work with OLE-DB data sources using the .NET OleDb data provider.Example of these classes a...

Answer: The .NET framework data provider includes the following components for data manipulation: - Connection: Used for connectivity to the data source - Command: This executes the SQL statements needed to retrieve data, modify data or execute stored procedures. It works with connection object. - DataReader: This class is used to retrieve data. DataReader is forward only and read-only object. We cannot modify the data using DataReader. - DataAdapter: It works as bridge between dataset and data source. Datadapter uses fill method to load the dataset.

Answer: ADO.NET provides the connection object to connect with datasource. Always remember that a connection object does not fetch or update data, it does not execute sql queries, and it does not contain the results of sql queries. Connection object contains only the information about connection string.

Answer: The DataSet is the most important object in ADO.NET. DataSet object works as a mini database. It provides the disconnected environment. It persists data in memory which is separate from the database. A DataSet contains a collection of DataTable objects means it can store more than one table simultaneously. Each DataTable object contains collections of DataRow, DataColumn and Constraint objects. A DataSet also contains a collection of DataRelation objects that define the relationship between the DataTable objects. It belongs to “System.Data” namespace.

Answer: using System; using System.Data; public partial class Default : System.Web.UI.Page {            protected void Page_Load(object sender, EventArgs e)            {                      DataTable dt = new DataTable("Employee"); // Create the table object                      DataColumn dc = new DataColumn();       &n...

Answer: The DataReader object is a forward-only and read only object - It is simple and fast compare to dataset. - It provides connection oriented environment. - It needs explicit open and close the connection. - DataReader object provides the read() method for reading the records. read() method returns Boolean type. - DataReader object cannot initialize directly, you must use ExecuteReader() method to initialize this object. Example: using System; using System.Web.UI; using System.Data; using System.Data.SqlClient; public partial class CareerRide : System.Web.UI.Page {         protected void Page_Load(object sender...

Answer: Serialization is the process of converting an object into stream of bytes that can be stored and transmitted over network. We can store this data into a file, database or Cache object. De-Serialization is the reverse process of serialization. By using de-serialization we can get the original object that is previously serialized. Following are the important namespaces for Serialization in .NET. - System.Runtime.Serialization namespace. - System.Runtime.Serialization.Formatters.Binary - System.Xml.Serialization The main advantage of serialization is that we can transmit data across the network in a cross-platform environment and we can save in ...

Answer: After successful connection with database you must execute some sql query for manipulation of data or selecting the data. This job is done by command object. If you are using SQL Server as database then SqlCommand class will be used. It executes SQL statements and Stored Procedures against the data source specified in the Connection Object. It requires an instance of a Connection Object for executing the SQL statements. - ExecuteReader: This method works on select SQL query. It returns the DataReader object. Use DataReader read () method to retrieve the rows. - ExecuteScalar: This method returns single value. Its return type is Object - Execu...

Answer: System.data contains basic objects. These objects are used for accessing and storing relational data. Each of these is independent of the type of data source and the way we connect to it. These objects are: DataSetDataTableDataRelation. System.Data.OleDB objects are used to connect to a data source via an OLE-DB provider. These objects have the same properties, methods, and events as the SqlClient equivalents. A few of the object providers are: OleDbConnection OleDbCommand System.Data.SqlClient objects are used to connect to a data source via the Tabular Data Stream (TDS) interface of only Microsoft SQL Server. The intermediate layers require...

Answer: Data Provider provides objects through which functionalities like opening and closing connection, retrieving and updating data can be availed. It also provides access to data source like SQL Server, Access, and Oracle). Some of the data provider objects are: Command object which is used to store procedures. Data Adapter which is a bridge between datastore and dataset. Datareader which reads data from data store in forward only mode. A dataset object is not in directly connected to any data store. It represents disconnected and cached data. The dataset communicates with Data adapter that fills up the dataset. Dataset can have one or more Datat...

Answer: Dataset DataSet object can contain multiple rowsets from the same data source as well as from the relationships between them Dataset is a disconnected architecture Dataset can persist data. Datareader DataReader provides forward-only and read-only access to data. Datareader is connected architecture Datareader can not persist data. Dataset and datareader in ADO.NET - June 06, 2009 at 10:00 AM by Shuchi Gauri Difference between dataset and datareader. Dataset a. Disconnectedb. Can traverse data in any order front, back. c. Data can be manipulated within the dataset.d. More expensive than datareader as it stores multiple row...

Answer: The command objects are used to connect to the Datareader or dataset objects with the help of the following methods: ExecuteNonQuery: This method executes the command defined in the CommandText property.The connection used is defined in the Connection property for a query.It returns an Integer indicating the number of rows affected by the query. ExecuteReader: This method executes the command defined in the CommandText property.The connection used is defined in the Connection property. It returns a reader object that is connected to the resulting rowset within the database, allowing the rows to be retrieved. ExecuteScalar: This method execute...

Answer: The data adapter objects connect a command objects to a Dataset object. They provide the means for the exchange of data between the data store and the tables in the DataSet. An OleDbDataAdapter object is used with an OLE-DB providerA SqlDataAdapter object uses Tabular Data Services with MS SQL Server. Use of data adapter in ADO.NET - June 06, 2009 at 10:00 AM by Shuchi Gauri Explain the use of data adapter. Data adapters are the medium of communication between datasource like database and dataset. It allows activities like reading data, updating data.

Answer: The most commonly used methods of the DataAdapter are: Fill: This method executes the SelectCommand to fill the DataSet object with data from the data source. Depending on whether there is a primary key in the DataSet, the ‘fill’ can also be used to update an existing table in a DataSet with changes made to the data in the original datasource. FillSchema This method executes the SelectCommand to extract the schema of a table from the data source.It creates an empty table in the DataSet object with all the corresponding constraints. Update This method executes the InsertCommand, UpdateCommand, or DeleteCommand to update the origina...

Answer: The DataSet object is a disconnected storage.It is used for manipulation of relational data. The DataSet is filled with data from the storeWe fill it with data fetched from the data store. Once the work is done with the dataset, connection is reestablished and the changes are reflected back into the store. Dataset has a collection of Tables which has DataTable collection which further has DataRow DataColumn objects collections. It also has collections for the primary keys, constraints, and default values called as constraint collection. A DefaultView object for each table is used to create a DataView object based on the table, so that the dat...

Answer: The DataSet object is a disconnected storage.It is used for manipulation of relational data. The DataSet is filled with data from the storeWe fill it with data fetched from the data store. Once the work is done with the dataset, connection is reestablished and the changes are reflected back into the store.

Answer: The changes made to the dataset can be tracked using the GetChanges and HasChanges methods. The GetChanges returns dataset which are changed since it was loaded or since Acceptchanges was executed. The HasChanges property indicates if any changes were made to the dataset since it was loaded or if acceptchanges method was executed.

Answer: NewRow’ method is provided by the ‘Datatable’ to add new row to it. ‘DataTable’ has “DataRowCollection” object which has all rows in a “DataTable” object. Add method of the DataRowCollection is used to add a new row in DataTable.We fill it with data fetched from the data store. Once the work is done with the dataset, connection is reestablishedRemove method of the DataRowCollection is used to remove a ‘DataRow’ object from ‘DataTable’. RemoveAt method of the DataRowCollection is used to remove a ‘DataRow’ object from ‘DataTable’ per the index...

Answer: A DataView is a representation of a full table or a small section of rows.It is used to sort and find data within Datatable. Following are the methods of a DataView: Find : Parameter: An array of values; Value Returned: Index of the rowFindRow : Parameter: An array of values; Value Returned: Collection of DataRowAddNew : Adds a new row to the DataView object.Delete : Deletes the specified row from DataView object

Answer: Dataset DataSet object can contain multiple rowsets from the same data source as well as from the relationships between them Dataset is a disconnected architecture Dataset can persist data. A DataSet is well suited for data that needs to be retrieved from multiple tables. Due to overhead DatsSet is slower than DataReader. Datareader DataReader provides forward-only and read-only access to data. Datareader is connected architecture. It has live connection while reading data Datareader can not persist data. Speed performance is better in DataReader.

Answer: CommandBuilder builds “Parameter” objects automatically.  Example: Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)pobjCommandBuilder.DeriveParameters(pobjCommand) If DeriveParameters method is used, an extra trip to the Datastore is madewhich can highly affect the efficiency.

Answer: In pessimistic locking, when a user opens a data to update it, a lock is granted. Other users can only view the data until the whole transaction of the data update is completed. In optimistic locking, a data is opened for updating by multiple users. A lock is granted only during the update transaction and not for the entire session. Due to this concurrency is increased and is a practical approach of updating the data.

Answer: Following are the general steps that are followed during a transaction: Open connection Begin Transaction: the begin transaction method provides with a connection object this can be used to commit or rollback. Execute the SQL commands Commit or roll back Close the database connection

Answer: A connection pool is created when a connection is opened the first time. The next time a connection is opened, the connection string is matched and if found exactly equal, the connection pooling would work. Otherwise, a new connection is opened, and connection pooling won't be used. Maximum pool size is the maximum number of connection objects to be pooled.  If the maximum pool size is reached, then the requests are queued until some connections are released back to the pool. It is therefore advisable to close the connection once done with it.

Answer: Set Pooling=true. However, it is enabled by default in .NET. To disable connection pooling set Pooling=false in connection string if it is an ADO.NET Connection.  If it is an OLEDBConnection object set OLEDB Services=-4 in the connection string.

Answer: Language Integrated Query or LINQ provides programmers and testers to query data and ituses strongly type’s queries and results.

Answer: There are two classes involved in System.Data.Common Nameapce:.DataColumnMapping.DataTableMapping.

Answer: The default timeout of Sqlcommand. CommandTimeout property is 30 Seconds

Answer: Following are uses of Stored Procedure:Improved Performance.Easy to use and maintain.Security.Less time and effort taken to execute.Less Network traffic.

Answer: Following are the classes that are available in System.Data Namespace:Dataset.DataTable.DataColumn.DataRow.DataRelation.Constraint.

Answer: ExecuteNonQuery is the best method to get two values from the database

Answer: DataRelation object is used to add relationship between two or more datatable objects

Answer: Sort() method of GridViewControl is used to sort the data in a datatable.

Answer: Thread.Abort() function stops the thread execution at any time.

Answer: Typed datasets use explicit names and data types for their members but untyped dataset usestable and columns for their members.

Answer: There are three different layers of ADO.Net:Presentation LayerBusiness Logic LayerDatabase Access Layer

Answer: OLEDBReader and OLDDBConnection object need to be closed. This will stay in memory if it isnot properly closed.

Answer: Fill Method is used to populate dataset with records

Answer: ReadXML() method is used to read XML file.

Answer: Params keyword is used to accept variable number of parameters

Answer: Execute Scalar method is used by command class to execute SQL statement which can returnsingle values.

Answer: Following are the Data Providers used in ADO.Net:.MS SQL Server.OLEDB.ODBC.

Answer: Dataview is used to represent a whole table or a part of table. It is best view for sorting andsearching data in the data table.

Answer: SQL Server should authenticate before performing any activity in the database. There are twotypes of authentication:Windows Authentication – Use authentication using Windows domain accounts only.SQL Server and Windows Authentication Mode – Authentication provided with thecombination of both Windows and SQL Server Authentication.

Answer: There are various methods of XML dataset object:GetXml() – Get XML data in a Dataset as a single string.GetXmlSchema() – Get XSD Schema in a Dataset as a single string.ReadXml() – Reads XML data from a file.ReadXmlSchema() – Reads XML schema from a file.WriteXml() – Writes the contents of Dataset to a file.WriteXmlSchema() – Writes XSD Schema into a file.

Answer: Yes, stored procedures are used in ADO.Net and it can be used for common repetitivefunctions.

Answer: Yes, it is possible to load multiple tables in a single dataset.29. Which provider is used toconnect MS Access, Oracle, etc…?OLEDB Provider and ODBC Provider are used to connect to MS Access and Oracle. OracleData Provider is also used to connect exclusively for oracle database.

Answer: Command is used to execute all kind of queries like DML and DDL. DML is nothing but Insert,Update and Delete. DDL are like Create and drop tables.Command Builder object is used to build and execute DML queries like Create and DropTables.

Answer: Dataset.clone object copies structure of the dataset including schemas, relations andconstraints. This will not copy data in the table.Dataset.copy – Copies both structure and data from the table.

Answer: There are different methods under SqlCommand and they are:Cancel – Cancel the queryCreateParameter – returns SQL ParameterExecuteNonQuery – Executes and does not return result setExecuteReader – executes and returns data in DataReaderExecuteScalar – Executes and returns single valueExecuteXmlReader – Executes and return data in XMLDataReader objectResetCommandTimeout – Reset Timeout property

Answer: DataAdapter is used to retrieve data from a data source .Insertcommand, UpdateCommand andDeleteCommand are the commands object used in DataAdapter to manage update on thedatabase.

Answer: Following are different execute methods of ADO.Net command object:ExecuteScalar – Returns single value from the datasetExecutenonQuery – Returns resultset from dataset and it has multiple valuesExecuteReader – Forwardonly resultsetExecuteXMLReader – Build XMLReader object from a SQL Query

Answer: OLEDB provider is used to access any database and provides flexibility of changing thedatabase at any time. SQLClient provider is used to access only SQL Server database but itprovides excellent performance than OLEDB provider while connecting with SQL Serverdatabase.

Answer: Following are the components of ADO.Net Data provider:Connection object – Represents connection to the DatabaseCommand object – Used to execute stored procedure and command on DatabaseExecuteNonQuery – Executes command but doesn’t return any valueExecuteScalar – Executes and returns single valueExecuteReader – Executes and returns result setDataReader – Forward and read only recordsetDataAdapter – This acts as a bridge between database and a dataset.

Answer: No, it is not possible to edit data in the Repeater control.

Answer: Following table gives difference between Datareader and Dataset: Datareader:- Forward only Connected Recordset Single table involved No relationship required No XML storage Occupies Less Memory Read only Dataset:- Loop through Dataset Disconnected Recordset Multiple tables involved Relationship between tables maintained Can be stored as XML Occupies More memory Can do addition / Updation and Deletion  

Answer: ADO.NET refers to the various classes in the .NET Framework that provide data access.   ADO.NET is typically used to access relational databases can also be used to access other external data such as XML documents data such as XML documents. .

Answer:  We know that ADO.NET allows us to interact with different types of data sources and different types of databases. However, there isn't a single set of classes that allow you to accomplish this universally. Since different data sources expose different protocols, we need a way to communicate with the right data source using the right protocol Some older data sources use the ODBC protocol, many newer data sources use the OleDb protocol, and there are more data sources every day that allow you to communicate with them directly through .NET ADO.NET class libraries. ADO.NET provides a relatively common way to interact with data sources, but ...

Answer: ADO.NET Objects ADO.NET includes many objects you can use to work with data. This section introduces some of the primary objects you will use. Over the course of this tutorial, you'll be exposed to many more ADO.NET objects from the perspective of how they are used in a particular lesson. The objects below are the ones you must know. Learning about them will give you an idea of the types of things you can do with data when using ADO.NET. The SqlConnection Object To interact with a database, you must have a connection to it. The connection helps identify the database server, the database name, user name, password, and other parameters that are...

Answer: The first thing you will need to do when interacting with a data base is to create a connection. The connection tells the rest of the ADO.NET code which database it is talking to. It manages all of the low level logic associated with the specific database protocols. This makes it easy for you because the most work you will have to do in code is instantiate the connection object, open the connection, and then close the connection when you are done. Because of the way that other classes in ADO.NET are built, sometimes you don't even have to do that much work. Although working with connections is very easy in ADO.NET, you need to understand conn...

Answer: Creating a SqlDataReader Object Getting an instance of a SqlDataReader is a little different than the way you instantiate other ADO.NET objects. You must call ExecuteReader on a command object, like this: SqlDataReader rdr = cmd.ExecuteReader(); The ExecuteReader method of the SqlCommand object, cmd , returns a SqlDataReader instance. Creating a SqlDataReader with the new operator doesn't do anything for you. As you learned in previous lessons, the SqlCommand object references the connection and the SQL statement necessary for the SqlDataReader to obtain data. Reading Data previous lessons contained code that used a SqlDataReader, but the dis...

Answer: A DataSet is an in-memory data store that can hold numerous tables. DataSets only hold data and do not interact with a data source. It is the SqlDataAdapter that manages connections with the data source and gives us disconnected behavior. The SqlDataAdapter opens a connection only when required and closes it as soon as it has performed its task. For example, the SqlDataAdapter performs the following tasks when filling a DataSet with data: Open connection Retrieve data into DataSet Close connection and performs the following actions when updating data source with DataSet changes: Open connection Write changes from DataSet to data source Close ...

Answer: Adding new data rows to a table is a three-step process: 1.    Create a new row object. 2.    Store the actual data values in the row object. 3.    Add the row object to the table. Creating New RowsThe DataColumn objects you add to a DataTable let you define an unlimited number of column combinations. One table might manage information on individuals, with textual name fields and dates for birthdays and driver-license expirations. Another table might exist to track the score in a baseball game, and contain no names or dates at all. The type of  information you store in a table depends on the c...

Answer: The features shown previously for adding, modifying, and removing data records within a DataTable all take immediate action on the content of the table. When you use the Add method to add a new row, it’s included immediately. Any field-level changes made within rows are stored and considered part of the record—assuming that no data-specific exceptions get thrown during the updates. After you remove a row from a table, the table acts as if it never existed. Although this type of instant data gratification is nice when using a DataTable as a simple data store, sometimes it is preferable to postpone data changes or make several chang...

Answer: While making your row-level edits, ADO.NET keeps track of the original and proposed versions of all fields. It also monitors which rows have been added to or deleted from the table, and can revert to the original row values if necessary. The Framework accomplishes this by managing various state fields for each row. The main tracking field is the DataRow.RowState property, which uses the following enumerated values: ■■ DataRowState.Detached The default state for any row that has not yet been added to a DataTable. ■■ DataRowState.Added This is the state for rows added to a table when changes to the table have not yet bee...

Answer: The DataSet includes some properties and methods that replicate the functionality of the contained tables. These features share identical names with their table counterparts. When used, these properties and methods work as if those same features had been used at the table level in all contained tables. Some of these members that you’ve seen before include the following:■■ Clear ■■ CaseSensitive ■■ AcceptChanges ■■ RejectChanges ■■ EnforceConstraints ■■ HasErrors

Answer: In relational database modeling, the term cardinality describes the type of relationship that two tables have. There are three main types of database model cardinality:■■ One-to-One A record in one table matches exactly one record in another table. This is commonly used to break a table with a large number of columns into two distinct tables for processing convenience.Table1 Record 1 Record 2 Record 3Table2 Record 1 Record 2 Record 3 ■■ One-to-Many One record in a “parent” table has zero or more “child” records in another table. A typical use for the one-to-many relationship is in an ordering sy...

Answer: The DataRelation class, found within the System.Data namespace, makes table joins within a DataSet possible. Each relationship includes a parent and a child. The DataRelation class even uses the terms “parent” and “child” in its defining members. To create a relationship between two DataSet tables, first add the parent and child table to the data set. Then create a new DataRelation instance, passing its constructor the name of the new relationship, plus a reference to the linking columns in each table. The following code joins a Customer table with an Order table, linking the Customer.ID column as the parent with the r...

Answer: An aggregation function returns a single calculated value from a set of related values. Averages are one type of data aggregation; they calculate a single averaged value from an input of multiple source values. ADO.NET includes seven aggregation functions for use in expression columns and other DataTable features.  ■■ Sum Calculates the total of a set of column values. The column being summed must be numeric, either integral or decimal. ■■ Avg Returns the average for a set of numbers in a column. This function also requires a numeric column. ■■ Min Indicates the minimum value found within a set of colu...

Answer: To calculate the aggregate of a single table column, use the DataTable object’s Compute method. Pass it an expression string that contains an aggregate function with a columnname argument   C# DataTable employees = new DataTable("Employee"); employees.Columns.Add("ID", typeof(int)); employees.Columns.Add("Gender", typeof(string)); employees.Columns.Add("FullName", typeof(string)); employees.Columns.Add("Salary", typeof(decimal));   // ----- Add employee data to table, then... decimal averageSalary=(decimal)employees.Compute("Avg(Salary)", "");   In the preceding code, the Compute method calculates the average of the value...

Answer: Expression columns typically compute a value based on other columns in the same row. You can also add an expression column to a table that generates an aggregate value. In the absence of a filtering expression, aggregates always compute their totals using all rows in a table. This is also true of aggregate expression columns. When you add such a column to a table, that column will contain the same value in every row, and that value will reflect the aggregation of all rows in the table.   C# DataTable sports = new DataTable("Sports"); sports.Columns.Add("SportName", typeof(string)); sports.Columns.Add("TeamPlayers", typeof(decimal)); spor...

Answer: Adding aggregate functions to an expression column certainly gives you more data options, but as a calculation method it doesn’t provide any benefit beyond the DataTable.Compute method. The real power of aggregate expression columns appears when working with related tables. By adding an aggregate function to a parent table that references the child table, you can generate summaries that are grouped by each parent row. This functionality is similar in purpose to the GROUP BY clause found in the SQL language.   C# // ----- Build the parent table and add some data. DataTable customers = new DataTable("Customer"); customers.Columns.Add...

Answer: ADO.NET is Active Data Object. It is commonly a type of managed library used by the .NET Framework. There are mainly two types of architecture:1. Connected2. disconnected It is used for accessing data, application data, and retrieving data.