Do you know that? 77% of freelancers are OPTIMISTIC about their business prospects next

See right interview answers on 30 common job interview questions


Collapse | Expand

1. Access database at runtime using ADO.NET

SqlConnection sqlCon = new SqlConnection(connectionString)
sqlCon.Open();
string strQuery = "select CategoryName from abcd";
SqlCommand cmd = new SqlCommand(strQuery, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader [0]);
}
reader.Close(); 
con.Close();

2. ADO.NET Code showing Dataset storing multiple tables.

DataSet ds = new DataSet();
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
ds.Tables.Add(dtn);

3. Can you explain how to enable and disable connection pooling?

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.

4. Command objects uses, purposes and their methods.

Command objects are used to execute the queries, procedures. Sql statements etc. It can execute stored procedures or queries that use parameters as well. 

It works on the basis of certain properties like ActiveConnection, CommandText, CommandType, Name etc. 

Command object has three methods:

a) Execute: executes the queries, stored procedures etc. 
b) Cancel: stops the method execution
c) CreateParameter: to create a parameter object

5. Define connected and disconnected data access in ADO.NET

Data reader is based on the connected architecture for data access. Does not allow data manipulation

Dataset supports disconnected data access architecture. This gives better performance results.

6. Define the data provider classes that is supported by ADO.NET.

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.

7. Describe CommandType property of a SQLCommand in ADO.NET.

CommandType is a property of Command object which can be set to Text, Storedprocedure. If it is Text, the command executes the database query. When it is StoredProcedure, the command runs the stored procedure. A SqlCommand is an object that allows specifying what is to be performed in the database. 

8. Describe Connection object in ADO.NET

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.

9. Describe DataReader object of ADO.NET with example.

- 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, EventArgs e)
{
if (!Page.IsPostBack) 
{
SqlDataReader reader; 
SqlConnection MyConnection = new SqlConnection("Data Source=name of your datasource;Initial Catalog=Employee;Integrated Security=True");
SqlCommand cmd = new SqlCommand(); 
cmd.CommandText = "SELECT * FROM emp"; 
cmd.CommandType = CommandType.Text; 
cmd.Connection = MyConnection; MyConnection.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
GridView1.DataSource = reader; 
GridView1.DataBind(); 
cmd.Dispose();
MyConnection.Dispose();
}
} 
} 

10. Describe the command object and its method.

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 
- ExecuteNonQuery: If you are using Insert, Update or Delete SQL statement then use this method. Its return type is Integer (The number of affected records).

using System;
using System.Data;
using System.Data.SqlClient; 
public partial class Default2 : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 
SqlConnection con = new SqlConnection("Data Source=Your data source name;Initial Catalog=Demo;Integrated Security=True"); 
SqlCommand cmd = new SqlCommand(); 
cmd.Connection = con; 
cmd.CommandText = "select * from employee"; 
cmd.CommandType = CommandType.Text; 
con.Open(); 
SqlDataReader reader = cmd.ExecuteReader(); 
if (reader.HasRows)
{ 
while (reader.Read())
{ 
DropDownList1.Items.Add(reader[1].ToString()); 
} 
} 
else 
{ 
Label1.Text="No rows found."; 
} 
reader.Close(); 
con.Close(); 
} 
} 

In the above given example a DropdownList has been taken that is filled by second column of employee table

11. Describe the DataSet object in ADO.NET.

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. 

12. Difference between dataset and datareader.

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

13. Differences between úDataSetĚ and úDataReaderĚ.

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.

14. Explain how to load multiple tables in a DataSet.

MyDataSet myds = new MyDataSet();

SqlDataAdapter myda = new SqlDataAdapter ("procId", this.Connection);
myda.SelectCommand.CommandType = CommandType.StoredProcedure;
myda.SelectCommand.Parameters.AddWithValue ("@pId", pId);
myda.TableMappings.Add ("Table", myds.xval.TableName);
myda.Fill (myds);

15. Explain the basic use of úDataViewĚ and explain its methods.

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 row
FindRow : Parameter: An array of values; Value Returned: Collection of DataRow
AddNew : Adds a new row to the DataView object.
Delete : Deletes the specified row from DataView object

16. Explain the namespaces in which .NET has the data functionality class.

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:

DataSet
DataTable
DataRelation.

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 required by an OLE-DB connection are removed in this.
This provides better performance.

System.XML contains the basic objects required to create, read, store, write, and manipulate XML documents according to W3C recommendations.

17. Explain the use of data adapter.

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 provider
A SqlDataAdapter object uses Tabular Data Services with MS SQL Server.

18. How can we add/remove row's in úDataTableĚ object of úDataSetĚ?

'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 reestablished
Remove 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 specified in the DataTable.

19. How can we check that some changes have been made to dataset since it was loaded?

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.

The RejectChanges can be used to revert thee changes made to the dataset since it was loaded.

20. How can we perform transactions in .NET?

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

21. How can you serialize the DataSet ? Explain with example.

using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Xml.Serialization; 
using System.IO; 
public partial class Default2 : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 
SqlConnection con = new SqlConnection("Data Source=Your data source name;Initial Catalog=Demo;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter("select * from employee", con); 
DataSet ds = new DataSet(); da.Fill(ds); 
FileStream fileObj = new FileStream("C:\\abc.xml", FileMode.Create); 
XmlSerializer serObj = new XmlSerializer(typeof(DataSet)); 
serObj.Serialize(fileObj, ds); fileObj.Close(); 
} 
} 

In the above given example the database name is Demo and table name is employee. The data will be serialized in abc.xml file..

22. How do we use stored procedure in ADO.NET and how do we provide parameters to the stored procedures?

CREATE PROCEDURE RUPDATE (@RID INTEGER, @RDESC NCHAR(50))
AS
SET NOCOUNT OFF
UPDATE Region
SET RDESC = @RDESC

SqlCommand command = new SqlCommand("RUPDATE",con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@RID",SqlDbType.Int,0,"RID"));
command.Parameters.Add(new SqlParameter("@RDESC",SqlDbType.NChar,50,"RDESC"));
command.Parameters[0].Value=4;
command.Parameters[1].Value="SouthEast";
int i=command.ExecuteNonQuery();

23. How will you fill the GridView by using DataTable object at runtime?

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();
DataRow dr;
dc.ColumnName = "ID"; //Heading of the coloumn 
dc.DataType = Type.GetType("System.Int32"); //Set the type of ID as Integer 
dt.Columns.Add(dc); 
dc = new DataColumn(); 
dc.ColumnName = "ItemName"; 
dc.DataType = Type.GetType("System.String"); //Set the type of ItemName as String 
dt.Columns.Add(dc);
for (int i = 0; i <= 4; i++) //This code will create 5 new rows 
{
dr = dt.NewRow();
dr["id"] = i; 
dr["ItemName"] = "Item " + i;
dt.Rows.Add(dr); 
}
GridView1.DataSource = dt; 
GridView1.DataBind();
}
}

24. Overview of ADO.NET architecture.

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 Datatable and   relations. 
  • DataView object is used to sort and filter data in   Datatable.

25. What are basic methods of Dataadapter?

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 original data source with the changes made to the content of the DataSet. 

26. What are the different ADO.NET namespaces are available in .NET.

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 are as follows:

- OleDbConnection. 
- OleDbCommand.

System.Data.Odbc

It defines the data provider for the SQL Server database. It contains classes such as 

- SqlConnection. 
- SqlCommand.
- SqlDataReader

System.Data.SqlTypes

This namespace provides classes for specific data types for the SQL Server database. 

27. What are the steps involved to fill a dataset?

The DataSet object is a disconnected storage.
It is used for manipulation of relational data. 
The DataSet is filled with data from the store
We 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.

28. What is connection pooling and what is the maximum Pool Size in ADO.NET Connection String?

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.

29. What is Dataset object? Explain the various objects in Dataset.

The DataSet object is a disconnected storage.
It is used for manipulation of relational data. 
The DataSet is filled with data from the store
We 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 data can be searched, filtered or otherwise manipulated while displaying the data.

30. What is Serialization and De-Serialization in .Net? How can we serialize the DataSet object?

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 a persistent or non-persistent storage medium. Serialization can be the following types: 

- Binary Serialization 
- SOAP Serialization 
- XML Serialization 
- Custom Serialization 

31. What is the difference between DataSet and DataReader?

The following are the main difference between DataSet and DataReader. 

DataSet 

- Consumer Object 
- Provides Disconnected mode 
- Forward and backward scanning of data 
- Slower access to data 
- Can store multiple table simultaneously 
- Read/Write access 

DataReader 

- Provider Object 
- Provides Connected mode 
- Forward-only cursor 
- Faster access to data 
- Supports a single table based on a single SQL query 
- Read Only access 

32. What is the use of CommandBuilder?

 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.

33. What's difference between úOptimisticĚ and úPessimisticĚ locking?

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.