Frequently asked ADO.NET Interview Questions


How do you create an instance of SqlDataReader class?
To create an instance of SqlDataReader class, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor.
//Error! Cannot use SqlDataReader() constructor
//to create an instance of SqlDataReader class
SqlDataReader ReaderObject = new SqlDataReader();

//Call the ExecuteReader method of the SqlCommand object
SqlCommand CommandObject = new SqlCommand();
SqlDataReader ReaderObject = CommandObject.ExecuteReader();

Creating an instance of SqlDataReader class using SqlDataReader() constructor generates a compile time error - The type 'System.Data.SqlClient.SqlDataReader' has no constructors defined.

How do you programatically check if a specified SqlDataReader instance has been closed?
Use the IsClosed property of SqlDataReader to check if a specified SqlDataReader instance has been closed. If IsClosed property returns true, the SqlDataReader instance has been closed else not closed.

How do you get the total number of columns in the current row of a SqlDataReader instance?
FieldCount property can be used to get the total number of columns in the current row of a SqlDataReader instance.

Give an example for executing a stored procedure with parameters?
//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand("StoredProcedureName", ConnectionObject);
//Specify to CommandObject that you intend to execute a Stored Procedure
CommandObject.CommandType = CommandType.StoredProcedure;
//Create an SQL Parameter object
SqlParameter ParameterObject = new SqlParameter();
//Specify the name of the SQL Parameter
ParameterObject.ParameterName = "Parameter1";
//Assign the Parameter value
ParameterObject.Value = "Some Value";
//Specify the Database DataType of the Parameter
ParameterObject.DbType = DbType.String;
//Specify the type of parameter - input-only, output-only, bidirectional
ParameterObject.Direction = ParameterDirection.Input;
//Associate the Parameter to the Command Object
CommandObject.Parameters.Add(ParameterObject);
//Open the connection
ConnectionObject.Open();
//Execute the command
int Records_Affected = CommandObject.ExecuteNonQuery();
//Close the Connection
ConnectionObject.Close();



What is the use of SqlParameter.Direction Property?
SqlParameter.Direction Property is used to specify the Sql Parameter type - input-only, output-only, bidirectional, or a stored procedure return value parameter. The default is Input.

How do you retrieve two tables of data at the same time by using data reader?
Include 2 select statements either in a stored procedure or in a select command and call the ExecuteReader() method on the command object. This will automatically fill the DataReader with 2 Tables of data.

The datareader will always return the data from first table only. If you want to get the second table then you need to use ReaderObject.NextResult() method. The NextResult() method will return true if there is another table. The following code shows you how do it.
//Create the SQL Query with 2 Select statements
string SQLQuery = "Select * from Customers;Select * from Employees;";
//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand(SQLQuery, ConnectionObject);
//Open the connection
ConnectionObject.Open();
//Execute the command. Now reader object will have 2 tables of data.
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
//Loop thru the tables in the DataReader object
while (ReaderObject.NextResult())
{
while (ReaderObject.Read())
{
//Do Something
}
}
//Close the Reader
ReaderObject.Close();
//Close the Connection
ConnectionObject.Close();

What are the advantages of using SQL stored procedures instead of adhoc SQL queries in an ASP.NET web application?
Better Performance : As stored procedures are precompiled objects they execute faster than SQL queries. Every time we run a SQL query, the query has to be first compiled and then executed where as a stored procedure is already compiled. Hence executing stored procedures is much faster than executing SQL queries.
Better Security : For a given stored procedure you can specify who has the rights to execute. You cannot do the same for an SQL query. Writing the SQL statements inside our code is usually not a good idea. In this way you expose your database schema (design) in the code which may be changed. Hence most of the time programmers use stored procedures instead of plain SQL statements.
Reduced Network Traffic : Stored Procedures reside on the database server. If you have to execute a Stored Procedure from your ASP.NET web application, you just specify the name of the Stored Procedure. So over the network you just send the name of the Stored Procedure. With an SQL query you have to send all the SQL statements over the network to the database server which could lead to increased network traffic.

Can you update the database using DataReader object?
No, You cannot update the database using DataReader object. DataReader is read-only, foward only. It reads one record at atime. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record.

What is the difference between a DataReader and a DataSet?
DataReader
1.
DatReader works on a Connection oriented architecture.
2. DataReader is read only, forward only. It reads one record at atime. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record. So using a DataReader you read in forward direction only.
3. Updations are not possible with DataReader.
4. As DataReader is read only, forward only it is much faster than a DataSet.
DataSet
1.
DataSet works on disconnected architecture.
2. Using a DataSet you can move in both directions. DataSet is bi directional.
3. Database can be updated from a DataSet.
4. DataSet is slower than DataReader.

Give an example scenario of using a DataSet and a DataReader?
If you want to just read and display the data(No updates, deletes, or inserts) then use a DataReader.
If you want to do a batch inserts, updates and deletes then use a DataSet.

24 comments:

  1. why dont you add some more...
    advance Thanks.

    ReplyDelete
  2. give the definition of stored procedure

    ReplyDelete
  3. A stored procedure is set of compiled SQL statements that performs a specific task. Stored procedures have the following advantages over using adhoc queries:

    1. Stored Procedures are much faster than adhoc queries. This is because when we create a stored procedure, it is already compiled and an execution stratergy is also in place and are reused every time we execute the stored procedure.

    2. Stored Procedures can also reduce traffic between the client and the server. If it is an adhoc query, you have to send all the statements in the query to the database server, where as if it is a stored procedure, you only will send the name of the SP. Hence using stored procedures can reduce the network traffic.

    3. Stored procedures promote reusability. Stored procedures can be re used on different web pages and even by different clients.

    4. Stored procedures provide enhanced security. You can grant users permission to execute a stored procedure independently of underlying table permissions. SQL injection attacks that are possible with adhoc queries can be prevented using stored procedures.

    ReplyDelete
  4. very clear explanation.Iam a fresher.I find it very useful for preparation.

    great job..!!

    ReplyDelete
  5. Why would use a datareader over a dataset? You can sort, go back and forth between rows, and update. There are many things you can do with a dataset that you cannot do with a datareader.

    ReplyDelete
  6. you can use a user define function in storedprocedure, UDF are bnot pre compiled but stored procedures are cmpiled, whenyou use udf are they precomplied are not.

    ReplyDelete
  7. can u explain the differences between the stored procedure and user defined function

    ReplyDelete
  8. by using stored procedures, we can return many values at a time. but with udf, we can return only one value at a time.

    venkat.

    ReplyDelete
  9. you said that SP are precompiled, then what if, i changed value in SP let say SP is deleting the table and i delete the table first and then execute the same SP, as SP is precompiled so why it will give error message.

    ReplyDelete
  10. GOOD EXPLANATION! I'M A FRESH, NOW I GOT CLEAR IDEA REGARDING "STORED PROCEDURES". THANK YOU VERY MUCH.[V]ENKY

    ReplyDelete
  11. what is difference between compiled and non-compiled stored procedures

    ReplyDelete
  12. It's worth noting that the execution plans of all queries can be cached for reuse, not just stored procs. Execution plans of stored procs may remain cached longer though, they'll generally have a higher priority in the cache.

    Stored procs can be a great layer of abstraction though, I think they're a good idea if you're working in a team.

    ReplyDelete
  13. superb explonations..

    ReplyDelete
  14. what is the default port no. when connection is made through connection string?

    ReplyDelete
  15. Thank you for your answers..They are so straight to the point.
    You really simplify the answers. Keep the good work going for
    freshers like me.
    Greatly appreciated

    ReplyDelete
  16. sir you are doing great job, your explanation in details makes us easy to understand and build confidence in fresher, sir please keep it on and don't stop, thanks
    please make video on store procedure in great detail,
    once again thanks and good job...

    ReplyDelete
  17. sir where is your remaining videos on .NET basic please confirm me...

    ReplyDelete
  18. why dont i get video tutorial on asp.net from you.because your explanation is high standards that any one can understood.iam waiting for asp .net tutorials from you. if you make asp.net vids tutorial keep intrest that on gridview please i think we know how important this to understand gridview.
    thank you

    ReplyDelete
  19. I want you to upload more videos on youtube. they are fantastic.

    ReplyDelete
  20. Kindly provide me some ASP. Net Questions for 1 to 2 yrs. experienced person.

    ReplyDelete
  21. How the sql queries are pre-compiled?? can u please explain

    ReplyDelete
  22. Sir
    A very Good Morning u r doing a very good job which is realy helpfull for all, I just want your help please give me some more impotant interview related questions and answers for 3years of experience with realtime example
    please send in my mail id sahoo.sasmita48@gmail.com

    ReplyDelete
  23. Really good example of retrieving two tables using reader.

    ReplyDelete

If you are aware of any other asp.net questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us