ADO.Net Interview Questions

Q. What is DataTable in ADO.NET?

• DataTable represents a single table in a database.
• In this show row and column.
• DataSet is a collection of data tables.
• In this store data record.

DataTable representation in c# code,

1. protected void BinddataTable()  
2. {  
3.     SqlConnection con = new SqlConnection("your database connection string");  
4.     con.Open();  
5.     SqlCommand cmd = new SqlCommand("Write your query or procedure", con);  
6.     SqlDataAdapter da = new SqlDataAdapter(cmd);  
7.     DataTable dt = new DataTable();  
8.     da.Fill(dt);  
9.     grid.DataSource = dt;  
10.     grid.DataBind();  
11. }  

Q. What is DataAdapter and what are basic methods of a DataAdapter?

DataAdapter is important when you want to work on disconnected architecture and acts like a bridge between Database and Data in memory. DataAdapter populates DataTable or DataSet. You can perform Insert, Update, Delete when data is in memory and then reconnect to database to commit the changes.

These are the most commonly used methods of a DataAdapter:

• Fill - Executes the Select command to fill the DataSet object with data from the data source.
• Fillschema - Uses the SelectCommand to extract just the schema for a table from the data source, and creates an empty table in the DataSet.
• 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.

Q. What is ExecuteScalar method in ADO.NET?
ExecuteScalar Method

The ExecuteScalar method of the SqlCommand object is useful for retrieving a single value from the database. In our example, we need to retrieve the total number of records in the Titles table of the Pubs database. Since the total number of records is a single scalar value, the Execute Scalar method is used. The following is the code and its explanation:

1. private void frmSqlCommand_Load(object sender, EventArgs e)  
2. {  
3.     //Sample 03: Open Database Connection   
4.     String con_string = Properties.Settings.Default.ConStrPubs;  
5.     pubs_db_connection = new SqlConnection(con_string);  
6.     pubs_db_connection.Open();  
7.     //Sample 04: Form the Command Object   
8.     SqlCommand cmd = new SqlCommand();  
9.     cmd.CommandText = "Select Count(*) as Count from Titles";  
10.     cmd.Connection = pubs_db_connection;  
11.     //Sample 05: Execute the Command & retrive scalar value   
12.     lblTotal.Text = System.Convert.ToString(cmd.ExecuteScalar());  
13. } 

1. will work with Non-Action Queries that contain aggregate functions.
2. Return the first row and first column value of the query result.
3. Return type is object.
4. Return value is compulsory and should be assigned to a variable of required type.

Q. What is ExecuteReader method in ADO.NET?

ExecuteReader Method

1. will work with Action and Non-Action Queries (Select)
2. Returns the collection of rows selected by the Query.
3. Return type is DataReader.
4. Return value is compulsory and should be assigned to an another object DataReader.

Q. What is ExecuteNonQuery method in ADO.NET?

ExecuteNonQuery Method

1. will work with Action Queries only (Create,Alter,Drop,Insert,Update,Delete).
2. Returns the count of rows effected by the Query.
3. Return type is int
4. Return value is optional and can be assigned to an integer variable.

Q. What is the SqlCommandBuilder?

CommandBuilder helps you to generate update, delete, and insert commands on a single database table for a data adapter. Similar to other objects, each data provider has a command builder class. The OleDbCommandBuilder, SqlCommonBuilder, and OdbcCommandBuilder classes represent the CommonBuilder object in the OleDb, Sql, and ODBC data providers.

Creating a Command Builder Object:

Creating a CommonedBuider object is pretty simply. You pass a DataAdapter as an argument of the CommandBuilder constructor. For example,

1. // Create a command builder object  
2. SqlCommandBuilder builder = new SqlCommandBuilder(adapter);   

Q. What is the differences Between DataReader and DataSet?

No	Data Reader	DataSet
1	Used in a connected architecture	Used in a disconnected architecture.
2	Provides better performance	Provides lower performance.
3	DataReader object has read-only access	A DataSet object has read/write access
4	DataReader object supports a single table based on a single SQL query of one database	A DataSet object supports multiple tables from various databases.
5	A DataReader object is bound to a single control.	A DataSet object is bound to multiple controls.
6	A DataReader object has faster access to data.	A DataSet object has slower access to data.
7	A DataReader object must be manually coded.	A DataSet object is supported by Visual Studio tools.
8	We can't create a relation in a data reader.	We can create relations in a dataset.
9	Whereas a DataReader doesn't support data reader communicates with the command object.	A Dataset supports integration with XML Dataset communicates with the Data Adapter only.
10	DataReader cannot modify data.	A DataSet can modify data.