ADO.NET Interview Questions

1. Advantage of ADO.Net?

Ans
· ADO.NET Does Not Depend On Continuously Live Connections
· Database Interactions Are Performed Using Data Commands
· Data Can Be Cached in Datasets
· Datasets Are Independent of Data Sources
· Data Is Persisted as XML
· Schemas Define Data Structures

2. How would u connect to database using .NET?

Ans. SqlConnection nwindConn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI;" +
"Initial Catalog=northwind");
nwindConn.Open();

3. What are relation objects in dataset and how & where to use them?

Ans. In a DataSet that contains multiple DataTable objects, you can use DataRelation objects to relate one table to another, to navigate through the tables, and to return child or parent rows from a related table. Adding a DataRelation to a DataSet adds, by default, a UniqueConstraint to the parent table and a ForeignKeyConstraint to the child table.
The following code example creates a DataRelation using two DataTable objects in a DataSet. Each DataTable contains a column named CustID, which serves as a link between the two DataTable objects. The example adds a single DataRelation to the Relations collection of the DataSet. The first argument in the example specifies the name of the DataRelation being created. The second argument sets the parent DataColumn and the third argument sets the child DataColumn.
custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustID"],
custDS.Tables["Orders"].Columns["CustID"]);

OR

private void CreateRelation()
{
// Get the DataColumn objects from two DataTable objects in a DataSet.
DataColumn parentCol;
DataColumn childCol;
// Code to get the DataSet not shown here.
parentCol = DataSet1.Tables["Customers"].Columns["CustID"];
childCol = DataSet1.Tables["Orders"].Columns["CustID"];
// Create DataRelation.
DataRelation relCustOrder;
relCustOrder = new DataRelation("CustomersOrders", parentCol, childCol);
// Add the relation to the DataSet.
DataSet1.Relations.Add(relCustOrder);
}

4. Difference between OLEDB Provider and SqlClient ?

Ans: 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.

5. What are the different namespaces used in the project to connect the database? What data providers available in .net to connect to database?

Ans
· System.Data.OleDb – classes that make up the .NET Framework Data Provider for OLE DB-compatible data sources. These classes allow you to connect to an OLE DB data source, execute commands against the source, and read the results.
· System.Data.SqlClient – classes that make up the .NET Framework Data Provider for SQL Server, which allows you to connect to SQL Server 7.0, execute commands, and read results. The System.Data.SqlClient namespace is similar to the System.Data.OleDb namespace, but is optimized for access to SQL Server 7.0 and later.
· System.Data.Odbc - classes that make up the .NET Framework Data Provider for ODBC. These classes allow you to access ODBC data source in the managed space.
· System.Data.OracleClient - classes that make up the .NET Framework Data Provider for Oracle. These classes allow you to access an Oracle data source in the managed space.

6. Difference between DataReader and DataAdapter / DataSet and DataAdapter?

Ans. You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory.
After creating an instance of the Command object, you create a DataReader by calling Command.ExecuteReader to retrieve rows from a data source, as shown in the following example.
SqlDataReader myReader = myCommand.ExecuteReader();
You use the Read method of the DataReader object to obtain a row from the results of the query.
while (myReader.Read())
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
myReader.Close();
The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables. The methods and objects in a DataSet are consistent with those in the relational database model. The DataSet can also persist and reload its contents as XML and its schema as XML Schema definition language (XSD) schema.
The DataAdapter serves as a bridge between a DataSet and a data source for retrieving and saving data. The DataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet. If you are connecting to a Microsoft SQL Server database, you can increase overall performance by using the SqlDataAdapter along with its associated SqlCommand and SqlConnection. For other OLE DB-supported databases, use the DataAdapter with its associated OleDbCommand and OleDbConnection objects.

7. Which method do you invoke on the DataAdapter control to load your generated dataset with data?

Ans. Fill()

8. Explain different methods and Properties of DataReader which you have used in your project?

Ans. Read
GetString
GetInt32
while (myReader.Read())
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
myReader.Close();

9. What happens when we issue Dataset.ReadXml command?

Ans. Reads XML schema and data into the DataSet.

10. In how many ways we can retrieve table records count? How to find the count of records in a dataset?

Ans. foreach(DataTable thisTable in myDataSet.Tables){
// For each row, print the values of each column.
foreach(DataRow myRow in thisTable.Rows){

11. How to check if a datareader is closed or opened?

Ans. IsClosed()

12. What happens when u try to update data in a dataset in .NET while the record is already deleted in SQL SERVER as backend?

OR

What is concurrency? How will you avoid concurrency when dealing with dataset? (One user deleted one row after that another user through his dataset was trying to update same row. What will happen? How will you avoid the problem?)

13. How do you merge 2 datasets into the third dataset in a simple manner? OR If you are executing these statements in commandObject. "Select * from Table1;Select * from Table2” how you will deal result set?

14. How do you sort a dataset?

15. If a dataset contains 100 rows, how to fetch rows between 5 and 15 only?

16. Differences between dataset.clone and dataset.copy?

Ans. Clone - Copies the structure of the DataSet, including all DataTable schemas, relations, and constraints. Does not copy any data.
Copy - Copies both the structure and data for this DataSet.

17. What is the use of parameter object?

18. How to generate XML from a dataset and vice versa?

19. What is method to get XML and schema from Dataset?

Ans: getXML () and get Schema ()

20. How do u implement locking concept for dataset?