Important ADO.NET Interview Questions

In this post we are going to share the interview questions or the information which you must know as a programmer or a developer, especially if you are a Dot Net developer. I hope you will like this article.

Background

I am a dot net developer. As a dot net developer, you will always use ADO.NET to handle your data. So it is important that you must know some information. We will read those basic information here.

So shall we now discuss about ADO.NET interview questions?

ADO.NET Interview Questions

Question: What is ADO.NET?

ADO.NET is basically a set of components which can be used by a programmer to access and manipulate the data in a disconnected architecture. It gives access to the data source such as SQL Server and XML. We use DataSet for the data operations in ADO.NET. It is a part of Microsoft .NET Framework.

Question: Difference between ExecuteReader, ExecuteScalar and ExecuteNonQuery ?

Click here for source code.

ExecuteNonQuery

ExecuteNonQuery is basically used for operations where there is nothing returned from the SQL Query or Stored Procedure. Preferred use will be for INSERT, UPDATE and DELETE Operations.
1. using(SqlConnection con = new SqlConnection(constring))
2. {
3. using(SqlCommand cmd = new SqlCommand("DELETE FROM Persons WHERE Name = @Name", con))
4. {
5. cmd.CommandType = CommandType.Text;
6. cmd.Parameters.AddWithValue("@Name", name);
7. con.Open();
8. int rowsAffected = cmd.ExecuteNonQuery();
9. con.Close();
10. }
11. }
Execute Scalar

ExecuteScalar is a handy function when you want to just need one Cell value i.e. one column and one row.

For example in a case where I need to get the City of a person based on its Name.
1. using(SqlConnection con = new SqlConnection(constring))
2. {
3. using(SqlCommand cmd = new SqlCommand("SELECT City FROM Persons WHERE Name=@Name", con))
4. {
5. cmd.CommandType = CommandType.Text;
6. cmd.Parameters.AddWithValue("@Name", name);
7. con.Open();
8. object o = cmd.ExecuteScalar();
9. if (o != null)
10. {
11. string city = o.ToString();
12. }
13. con.Close();
14. }
15. }
Execute Reader

ExecuteReader is strictly used for fetching records from the SQL Query or Stored Procedure i.e. SELECT Operation.

Example would be fetching Name and City for all the records in the Person Table.
1. using(SqlConnection con = new SqlConnection(constring))
2. {
3. using(SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))
4. {
5. cmd.CommandType = CommandType.Text;
6. con.Open();
7. SqlDataReader dr = cmd.ExecuteReader();
8. while (dr.Read())
9. {
10. string name = dr["Name"].ToString();
11. string city = dr["City"].ToString();
12. Response.Write("Name: " + name);
13. Response.Write("City: " + city);
14. }
15. con.Close();
16. }
17. }

Question: What is Data Reader, Dataset & Data Adapter ? Explain the differences?

Data Reader

Data Reader is used to read the data from database and it is a read and forward only connection oriented architecture during fetch the data from database. Data Reader is used to iterate through result set that came from server and it will read one record at a time because of that memory consumption will be less and it will fetch the data very fast when compared with dataset. Generally we will use Execute Reader object to bind data to data reader.
1. using(SqlConnection con = new SqlConnection("Connectionstring"))
2. {
3. con.Open();
4. SqlCommand cmd = new SqlCommand("Select UserName,LastName,Location FROM UserInformation", con);
5. SqlDataReader dr = cmd.ExecuteReader();
6. }
DataSet

DataSet is a disconnected oriented architecture that means there is no need of active connections during work with datasets and it is a collection of Data Tables and relations between tables. It is used to hold multiple tables with data. You can select data form tables, create views based on table and ask child rows over relations. Also Dataset provides you with rich features like saving data as XML and loading XML data.
1. SqlCommand cmd = new SqlCommand("select UserName,LastName,Location from UserInformation", con);
2. SqlDataAdapter da = new SqlDataAdapter(cmd);
3. DataSet ds = new DataSet();
4. da.Fill(ds);
DataAdapter

DataAdapter acts as a bridge between DataSet and database. This DataAdapter object is used to read the data from database and bind that data to DataSet. DataAdapter is a disconnected oriented architecture.
1. SqlCommand cmd = new SqlCommand("select UserName,LastName,Location from UserInformation", con);
2. SqlDataAdapter da = new SqlDataAdapter(cmd);
3. DataSet ds = new DataSet();
4. da.Fill(ds);

Question: ADO stands for?

ActiveX Data Object

Question: What are the main objects used in ADO.NET?

Dataset

DataSet is a disconnected oriented architecture that means there is no need of active connections during work with datasets and it is a collection of Data Tables and relations between tables. It is used to hold multiple tables with data. You can select data from tables, create views based on table and ask child rows over relations. Also, Dataset provides you with rich features like saving data as XML and loading XML data.

DataReader

DataReader is used to read the data from database and it is a read and forward only connection oriented architecture during fetching the data from database. DataReader is used to iterate through result set that came from server and it will read one record at a time because of that memory consumption will be less and it will fetch the data very fast when compared with dataset. Generally, we will use Execute Reader object to bind data to data reader.

That’s all. Have a great day.

Conclusion

Did I miss anything that you may think which is needed? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.

Your turn. What do you think?

If you have any suggestions, then please mention it in the comments section.

Read this article in my blog here.