ADO.NET interview questions for experienced

Q) Difference between Dataset and Data Reader?

The Dataset class in ADO.Net operates in an entirely disconnected nature, while Data Reader is a connection-oriented service.

DataReader is used to retrieve read-only (cannot update/manipulate data back to data source) and forward-only (cannot read backward/random) data from a database. It provides the ability to expose the data from the database while Dataset is a collection of in-memory tables.

DataReader fetches the records from the database and stores them in the network buffer and gives them whenever requests. It releases the records as the query executes and does not wait for the entire query to execute. Hence very fast compare to the Dataset which releases the data after loading all the data in memory.

DataReader is like a forward-only record set. It fetches one row at a time so very less network cost compare to Dataset which fetches all the rows at a time i.e. it fetches all data from the data source at a time to its memory area.

As one row at a time is stored in memory in DataReader it increases application performance and reduces system overheads while there is more system overheads in DataSet as it fetches all the data from the data source at a time in memory.

As DataReader is forward only, we can’t fetch random records as we can’t move back and forth . While in DataSet we can move back and forth and fetch records randomly as per requirement.

DataReader fetches data from a single table while DataSet can fetch data from multiple tables.

As DataReader can have data from a single table so no relationship can be maintained while relationships between multiple tables can be maintained in DataSet.

DataReader is read-only so no transaction like insert, update and delete is possible while these transactions are possible in DataSet.

DataSet is a bulky object that requires a lot of memory space as compared to DataReader.

DataReader is a connected architecture: The data is available as long as the connection with the database exists while DataSet is a disconnected architecture that automatically opens the connection, fetches the data into memory, and closes the connection when done.

DataReader requires the connection to be open and close manually in code while DataSet automatically handles it.

DataSet can be serialized and represented in XML so easily passed around to other tiers but DataReader can't be serialized.

DataReader will be the best choice where we need to show the data to the user which requires no manipulation while DataSet is best suited where there is the possibility of manipulation on the data.

Since DataSet can be serialized it, can be used in WCF services and web services that will return retrieved data. But DataReader can’t be serialized so can’t be used in WCF services and web services.

When you need to navigate through the data multiple times then DataSet is a better choice e.g. we can fill data in multiple controls But DataReader can only be read once so it can be bound to a single control and requires data to be retrieved for each control.

Data Reader Example:

string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constring))

{

    using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))

    {

        cmd.CommandType = CommandType.Text;

        con.Open();

        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())

        {

            string name = dr["Name"].ToString();

            string city = dr["City"].ToString();

            Response.Write("Name: " + name);

            Response.Write("City: " + city);

        }

        con.Close();

    }

}

Dataset:

string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constring))

{

    using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))

    {

        cmd.CommandType = CommandType.Text;

        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))

        {

            DataSet ds = new DataSet();

            sda.Fill(ds);

            foreach (DataRow row in ds.Tables[0].Rows)

            {

                string name = row["Name"].ToString();

                string city = row["City"].ToString();

                Response.Write("Name: " + name);

                Response.Write("City: " + city);

            }

        }

    }

}
-----------------------------------------------------------------------------------------------------------------------------

Q) How can you access the data from DataReader?

DataReader is a class that holds data as rows and columns to access data from the DataReader.

It provides the following methods:

GetName(int ColIndex)

The return type of this method is a string, it returns the name of the column for the given index position.

Read()

Moves the Record Pointer from the current location to the next row and returns a Boolean status that tells whether the row to which we have moved contains data in it or not, that will be true if present or false if not present.

GetValue(int Colindex)

Returns a column's value from the row to which the pointer was pointing by specifying the column index position.

NextResult ()

Moves the record pointer from the current table to the next table if a table exists and returns true

else returns false.

Features of Data Reader

Provides faster access to data from a Data Source, since it is connection-oriented.

It can hold multiple tables at a time. To load multiple tables into a DataReader pass multiple select statements as the argument to the command separated by a colon (;).

Ex:

Command cmd=new Command(“Select * From Student ; Select * From Mark ”, Con); 

Data Reader dr= cmd.ExecuteReader(); 

The DataReader properties

Property          Description

Depth  Indicates the depth of nesting for row

FieldCount      Returns the number of columns in a row

IsClosed          Indicates whether a data reader is closed

Item     Gets the value of a column in native format

RecordsAffected        Number of rows affected after a transaction

The DataReader methods

Property          Description

Close   Closes a DataReader object.

Read    Reads next record in the data reader.

NextResult      Advances the data reader to the next result during batch transactions.

Getxxx            There is dozens of Getxxx methods. These methods read a specific data type value from a column. For example. GetChar will return a column value as a character and GetString as a string. 

-----------------------------------------------------------------------------------------------------------------------------Difference between ADO and ADO.NET?

ADO	ADO.NET
It is based on COM (Component Object Modelling).	It is a CLR (Common Language Runtime) based library.
It works only when the datastore is connected.	It does not need an active connection to access data from the data store.
It has the feature of locking.	It does not have the feature of locking
It accesses and stores data from the data sources by recordset object.	It accesses and stores data from the data source by dataset object
XML integration is not feasible in ADO.	XML integration is feasible in ADO.NET
In ADO, data is stored in binary form.	While in this, data is stored in XML.
It allows us to create client-side cursors only.	It gives us the choice of using weather client-side and server-side cursors.
It requires SQL JOINs and UNIONs to combine data from multiple tables in a single result table.	It uses Data Relational objects, for combining data from multiple tables without requiring JOINs and UNIONs.
It supports sequential access of rows in a Recordset.	It allows completely non-sequential data access in DataSet through the collection-based hierarchy.
You cannot send multiple transactions using a single connection instance	You can send multiple transactions using a single connection instance
The firewall might prevent the execution of Classic ADO	ADO.Net has firewall proof and its execution will never be interrupted
Using classic ADO, you can obtain information from one table or set of tables through the join. You cannot fetch records from multiple tables independently	Dataset object of ADO.Net includes the collection of DataTable wherein each DataTable will contain records fetched from a particular table. Hence multiple table records are maintained independently

-----------------------------------------------------------------------------------------------------------------------------Q) Difference between typed dataset and untyped dataset in c#

·         A typed DataSet is a class that derives from a DataSet. As such, it inherits all the methods, events, and properties of a DataSet. Additionally, a typed DataSet provides strongly typed methods, events, and properties.

·         The typed dataset is derived from the Dataset class and has an associated XML schema, which is created at the time of the creation of the dataset. The XML schema contains information about the dataset structure such as tables, columns, and rows. The data is transferred from a database into a dataset and from the dataset to another component in the XML format.

·         The typed dataset is binded with the database tables(s) at design time and you have all the schema information at design time in your code. It is added as a .xsd file in your application.

•  This means you can access tables and columns by name, instead of using collection-based methods. 
•  Aside from the improved readability of the code, a typed DataSet also allows the Visual Studio .NET code editor to automatically complete lines as you type.

·         Additionally, the strongly typed DataSet provides access to values as the correct type at compile time. With a strongly typed DataSet, type mismatch errors are caught when the code is compiled rather than at the run time.

·         Access to tables and columns in a typed dataset is also slightly faster at run time because access is determined at compile-time, not through collections at run time.

UnTyped DataSet :

·          Untyped dataset is an object of class System.Data.DataSet.

·         It is binded with the tables at runtime.

·         The Untyped dataset doesn’t have an XML schema associated with it. Untyped Dataset, the tables, and columns are represented as a collection.

·         You can access the tables and columns using index no.

·         More generally, there are many times when you might create a dataset dynamically without having a schema available. In that case, the dataset is simply a convenient structure in which you can keep the information, as long as the data can be represented in a relational way.

·         At the same time, you can take advantage of the dataset’s capabilities, such as the ability to serialize the information to pass to another processor to write out an XML file

·         You are not aware of the schema of the dataset at design time and there is no error checking facility at the design time as they are filled at run time when the code executes.

// This accesses the Name column in the first row of the Employee table.

string s = dsEmployee.Employee[0].Name;

The same call using the UnTyped Dataset as follows

string s = (string) dsEmployee.Tables[“Employee “].Rows[0][“Name”];

Strongly Typed DataSet	Untyped DataSet
It provides additional methods, properties, and events, and thus it makes it easier to use.	It is not as easy to use as a strongly typed dataset.
You will get type mismatches and other errors at compile time.	You will get type mismatches and other errors at runtime.
You will get the advantage of IntelliSense in VS. NET	You can't get an advantage of IntelliSense.
Performance is slower in the case of the strongly typed dataset.	Performance is faster in the case of the Untyped dataset.
In a complex environment, strongly typed datasets are difficult to administer.	Untyped datasets are easy to administer.

---------------------------------------------------------------------------------------------------------------------------------Q) What are all the different authentication techniques used to connect to MS SQL Server?

SQL Server should authenticate before performing any activity in the database. There are two types of authentication:

Windows Authentication – Use authentication using Windows domain accounts only. 

SQL Server and Windows Authentication Mode – Authentication provided with the combination of both Windows and SQL Server Authentication.
-----------------------------------------------------------------------------------------------------------------------------Q)What is an ADO.Net?

ADO.Net is commonly termed as ActiveX Data Objects which is a part of .Net Framework. ADO.Net framework has a set of classes that are used to handle data access by connecting with different databases like SQL, Access, Oracle, etc.

There are two important objects of ADO.Net:

DataReader and DataSet

Ex:

DataReader with ExecuteReader() Method:

//Open connection  

Conn.Open(); 

sdr = sc.ExecuteReader(CommandBehavior.CloseConnection); 

//Get all records  

while(sdr.Read()) 

{ 

    textBox1.AppendText(sdr.GetValue(0) + "\t" + sdr.GetValue(1)); 

    textBox1.AppendText("\n");

}

Dataset ex:

private void Form1_Load(object sender, EventArgs e) 

{ 

    //Connection String  

    string conString = "Data Source=localhost;Database=AdventureWorksLT2008;Integrated Security=SSPI"; 

    // Add Connection string to SqlConnection  

    SqlConnection Conn = new SqlConnection(conString); 

    string query = "select * from SalesLT.Customer"; 

    //Command Class definition  

    SqlCommand sc = new SqlCommand(query, Conn); 

    // Data Adapter definition  

    SqlDataAdapter sda = new SqlDataAdapter(); 

    sda.SelectCommand = sc; 

    //data Set definition  

    DataSet ds = new DataSet(); 

    // filling the result set in data table  

    sda.Fill(ds, "SalesLT.Customer"); 

    //output in data grid  

    dataGridView1.DataSource = ds.Tables["SalesLT.Customer"]; 

}
-----------------------------------------------------------------------------------------------------------------------------Q) What are the advantages of ADO.NET?

 Advantages of ADO.NET:

 ADO.NET offers several advantages over previous Microsoft data access technologies, including ADO. Few advantages are listed below:

 Single Object-oriented API

ADO.NET provides a single object-oriented set of classes. There are different data providers to work with different data sources but the programming model for all these data providers work in the same way.

Managed Code:

The ADO.NET classes are managed, classes. CLR takes care of language independency and automatic resource management.

 Deployment:

 Microsoft uses MDAC (Microsoft Data Access Component), which is used as an ActiveX component in .NET Framework (X is an extensible component when X is written after a term means extensible). .NET components take care of deployment which was difficult than the previous technologies used in deployment.

XML Support:

ADO.NET data is cached and transferred in XML (EXtensible Markup Language) format. XML provides fast access to data for desktop and distributed applications.

 Performance and scalability:

Performance and scalability are two major factors while developing web-based applications and services. Disconnected cached data in XML help in performance and scalability.
-----------------------------------------------------------------------------------------------------------------------------Q)What are the namespaces used in ADO.Net to connect to a database or data providers in the ADO.net framework?

Following namespaces are used to connect to Database.

The System.Data namespace.

The System.Data.OleDb namespace  –  A data provider used to access databases such as Access, Oracle, or SQL. The System.Data.dll assembly implements the OLEDB .NET framework data provider.
This assembly is not part of the Visual Studio .NET installation.

The System.Data.SQLClient namespace – Used to access SQL as the data provider.

System.Data.OracleClient - This namespace can communicate with an “Oracle” database only using OracleClient Providers. The System.Data.OracleClient.dll assembly implements the Oracle .NET framework data provider in the System.Data.OracleClient namespace. The Oracle client software must be installed on the system before you can use the provider to connect to an Oracle data source

System.Data.ODBC - This namespace contains the same set of classes as the following:

Connection

Command

DataReader

DataAdapter

CommandBuilder

Parameter
-----------------------------------------------------------------------------------------------------------------------------Q)Which provider is used to connect MS Access, Oracle, etc…?

OLEDB Provider and ODBC Provider are used to connecting to MS Access and Oracle. Oracle Data Provider is also used to connect exclusively for the oracle database.
-----------------------------------------------------------------------------------------------------------------------------Q)What are all components of the ADO.Net data provider?

Following are the components of ADO.Net Data provider:

Connection object – Represents a connection to the Database

Command object – Used to execute stored procedure and command on Database

ExecuteNonQuery – The ExecuteNonQuery method is used to execute the command and return the number of rows affected. The ExecuteNonQuery method cannot be used to return the result set.

ExecuteScalar – Executes and returns a single value from a dataset

ExecuteReader – Forwardonly resultset

DataReader – Forward and read-only recordset

DataAdapter – This acts as a bridge between a database and a dataset.

ExecuteXMLReader – Build XMLReader object from a SQL Query 

Properties:

Connection - Read/Write - The SqlConnection object that is used by the command object to execute SQL queries or Stored Procedures.

CommandText - Read/Write - Represents the T-SQL Statement or the name of the Stored Procedure.

CommandType - Read/Write - This property indicates how the CommandText property should be interpreted.

The possible values are:

1. Text (T-SQL Statement)

2. StoredProcedure (Stored Procedure Name)

3. TableDirect

CommandTimeout - Read/Write - This property indicates the time to wait when executing a particular command.

Default Time for Execution of Command is 30 Seconds.

The Command is aborted after it times out and an exception is thrown.
Syntax for Execute Non-Query:

public void CallExecuteNonQuery() 

{ 

    SqlConnection conn = new SqlConnection(); 

    conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString; 

    try 

    { 

        SqlCommand cmd = new SqlCommand(); 

        cmd.Connection = conn; 

        cmd.CommandText = "DELETE FROM EMP WHERE DEPTNO = 40"; 

        cmd.CommandType = CommandType.Text; 

        conn.Open(); 

        Int32 RowsAffected = cmd.ExecuteNonQuery(); 

        MessageBox.Show(RowsAffected + " rows affected", "Message"); 

        cmd.Dispose(); 

        conn.Dispose(); 

    } 

    catch(Exception ex) 

    { 

        MessageBox.Show(ex.Message); 

    } 

}  
-----------------------------------------------------------------------------------------------------------------------------Q) What are the Connection object properties and Connection class members?

The Connection class has a connection string that opens a connection to the database. The connection string will vary depending upon the provider used. The connection strings typically contain a group of property-value pairs to describe how to connect to a database. For an OleDbConnection, you have properties such as Provider and Datasource.

Property          Description

ConnectionString - Represent the connection string.

ConnectionTimeOut - Waiting time while establishing a connection.

DataBase - Name of the current database.

DataSource - Location of the file name of the data source.

Provider - Name of the OLE DB provider. This property is not available for SQL and ODBC data providers.

State - Current state of the connection of type ConnectionState.

PacketSize - Size of network packets. Available to only SQL data providers.

server version - SQL server version. Available to only SQL data providers.

WorkStationId - Database client ID. Available to only SQL data providers.

The connection Class Members:

Method           Description

BeginTransaction - Begins database transaction.

ChangeDatabase - Changes databases for an open connection.

Close - Closes an opened connection.

CreateCommand - Creates and returns a Command object depends on the data providers. For example, OleDb Connection returns OleDbCommand, and SqlConnection returns SqlCommand.

Open - Open a new connection.

ReleaseObjectPool - Represents that the connection pooling can be cleared when the provider is

released. Available only for Ole Db data providers.

Use of Connection object:

ADO.Net Connection object is used to establish a connection between the application and the data source. SQL Commands can be executed once this connection has been established. It is mandatory to close the connection object once database activities are completed.
-----------------------------------------------------------------------------------------------------------------------------Q)What are all the classes that are available in System. Data Namespace?

Following are the classes that are available in System. Data Namespace:

Dataset.

DataTable.

DataColumn.

DataRow.

DataRelation.

Constraint.
-----------------------------------------------------------------------------------------------------------------------------Q)What are the classes in System.Data.Common Namespace?

There are two classes involved in System.Data.Common Namespace:.

DataColumnMapping.

DataTableMapping.
-----------------------------------------------------------------------------------------------------------------------------Q) What is the use of the SqlCommand object?

SQLCommand object that allows users to interact with the database. This object is mainly used to query the database and it can be of different types – Select, Insert, Modify and Delete. The default timeout of Sqlcommand. CommandTimeout property is 30 Seconds.

There are different methods under SqlCommand and they are:

Cancel – Cancel the query

CreateParameter – returns SQL Parameter

ExecuteNonQuery – Executes and returns the number of rows affected

ExecuteReader – executes and returns data in DataReader

ExecuteScalar – Executes and returns a single value

ExecuteXmlReader – Executes and return data in XMLDataReader object

ResetCommandTimeout – Reset Timeout property
-----------------------------------------------------------------------------------------------------------------------------Q) What is DataReader Object?

DataReader is an object of ADO.Net which provides access to data from a specified data source. It consists of classes that sequentially read data from a data source like Oracle, SQL, or Access.
-----------------------------------------------------------------------------------------------------------------------------Q) What is a Dataset object?

A Dataset is set to be a collection of data with a tabular column representation. Each column in the table represents a variable and the row represents to value of a variable. This Dataset object can be obtained from the database values. It is used in disconnected architecture. It represents records in the form of Database table(Row and Column) format. It stores records of one or more tables.

 SqlDataAdapter da; 

DataSet ds; 

string strconn = "Data Source=YourServerName;Initial Catalog=EMP;Integrated Security=True"; 

private void Form1_Load(object sender, EventArgs e) 

{ 

    da = new SqlDataAdapter("select * from userdet", strconn); 

    ds = new System.Data.DataSet(); 

    da.Fill(ds); 

    dataGridView1.DataSource = ds.Tables[0]; 

} 

Methods of DataSet:

AcceptChanges(): This method saves changes that are made with records in a DataSet.

Clear(): This method clears (removes) all rows from DataSet.Ex:   ds.Clear(); 

Clone(): The clone method copy the structure of DataSet. This means it copies only schema not full records of DataSet.

DataSet daset = ds.Clone(); 

   dataGridView2.DataSource = daset.Tables[0];  

Copy(): It copies the whole records with the structure of DataSet.

  DataSet daset = ds.Copy(); 

  dataGridView2.DataSource = daset.Tables[0];  

RejectChanges(): This method discard changes which is made with DataSet and sets the DataSet to the previous stage (which was at first).

HasChanges(): This method returns a boolean value to show whether the record of DataSet has changed or not. It returns true if any changes have been made and false if no other changes are made.

GetChanges(): This method keeps a copy of those record, which is changed or modified. 
Difference between Dataset. clone and Dataset. copy:

Dataset. clone object copies the structure of the dataset including schemas, relations, and constraints. This will not copy data in the table.

Dataset. copy – Copies both structure and data from the table.

-----------------------------------------------------------------------------------------------------------------------------Q)What is GetChanges() method in ADO.NET and also How can we check that some changes have been made to dataset since it was loaded?
The GetChanges method of DataSet can be used to retrieve the rows that have been modified since the last time DataSet was filled, saved, or updated. The GetChanges method returns a DataSet object with modified rows.
The GetChanges method can take either no argument or one argument of type DataRowState. The DataRowState enumeration defiles the DataRow state, which can be used to filter a DataSet based on the types of rows.

Added - Add added rows to a DataRowCollection of a DataSet and AcceptChanges has not been called.
Deleted           - All the deleted rows.
Detached - Rows were created but not added to the row collection. Either waiting for the addition or have removed from the collection.
ModifiedModified -  rows and AcceptChanges have not been called.
Unchanged - Unchanged rows since last AcceptChanges was called.
-----------------------------------------------------------------------------------------------------------------------------Q) What is the HasChanges() method of DataSet?

This method returns a boolean value to show whether the record of DataSet has changed or not. It returns true if any changes are made and false if no changes are performed.

Ex:

private void btnHasChanges_Click(object sender, EventArgs e) 

{ 

    if(ds.HasChanges()) 

    { 

        MessageBox.Show("Changes Has Made"); 

    } 

    if(!ds.HasChanges()) 

    { 

        MessageBox.Show("No Change"); 

    } 

} 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------Q) What is Data Adapter?

Data Adapter is a part of the ADO.NET data provider which acts as a communicator between Dataset and the Data source. This Data adapter can perform Select, Insert, Update and Delete operations in the requested data source.DataAdapter is used to retrieve data from a data source.

 A Data Adapter represents a set of data commands and a database connection to fill the dataset and update a SQL Server database.

 A Data Adapter contains a set of data commands and a database connection to fill the dataset and update a SQL Server database. Data Adapters form the bridge between a data source and a dataset. Data Adapters are designed depending on the specific data source. The following table shows the Data Adapter classes with their data source.

Syntax:
SqlDataAdapter sda = new SqlDataAdapter(sc); 

Property          Description

SelectCommand          This command is executed to fill in a Data Table with the result set.

InsertCommand          Executed to insert a new row to the SQL database.

UpdateCommand       Executed to update an existing record on the SQL database.

DeleteCommand         Executed to delete an existing record on the SQL database.

Methods:

Fill

FillSchema

GetFillParameters

Update

A Data Adapter supports mainly the following two methods:

Fill (): The Fill method populates a dataset or a data table object with data from the database. It

retrieves rows from the data source using the SELECT statement specified by an associated select command property. The Fill method leaves the connection in the same state as it encountered before populating the data.

Update (): The Update method commits the changes back to the database. It also analyzes the RowState of each record in the DataSet and calls the appropriate INSERT, UPDATE, and DELETE statements.
Example:

SqlDataAdapter da=new SqlDataAdapter("Select * from 

Employee", con); 

da.Fill(ds,"Emp"); 

bldr =new SqlCommandBuilder(da); 

dataGridView1.DataSource = ds.Tables["Emp"];   

-----------------------------------------------------------------------------------------------------------------------------Q) What is DataTable in ADO.NET?

DataTable represents a single table in a database.DataSet is a collection of data tables.

protected void BinddataTable() 

{ 

    SqlConnection con = new SqlConnection("your database connection string"); 

    con.Open(); 

    SqlCommand cmd = new SqlCommand("Write your query or procedure", con); 

    SqlDataAdapter da = new SqlDataAdapter(cmd); 

    DataTable dt = new DataTable(); 

    da.Fill(dt); 

    grid.DataSource = dt; 

    grid.DataBind(); 

} 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Q) ExecuteReader method in ADO.net?

The DataReader object is a forward-only and read-only cursor. It requires a live connection to the data source. The DataReader object cannot be directly instantiated. Instead, we must call the ExecuteReader() method of the command object to obtain a valid DataReader object.

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
---------------------------------------------------------------------------------------------------------------------------Q)ExecuteScalar method in ADO.NET?

The ExecuteScalar Method in SqlCommandObject returns the first column of the first row after executing the query against the Data Source. If the result set contain more than one column or row, it takes only the first column of the first row. All other values are ignored. If the result set is empty it will return null. The ExecuteScalar method of the SqlCommand object is useful for retrieving a single value from the database. Since the total number of records is a single scalar value, the Execute Scalar method is used.

Int32 TotalSalary = Convert.ToInt32(cmd.ExecuteScalar()); 

private void frmSqlCommand_Load(object sender, EventArgs e) 

{ 

    //Sample 03: Open Database Connection  

    String con_string = Properties.Settings.Default.ConStrPubs; 

    pubs_db_connection = new SqlConnection(con_string); 

    pubs_db_connection.Open(); 

    //Sample 04: Form the Command Object  

    SqlCommand cmd = new SqlCommand(); 

    cmd.CommandText = "Select Count(*) as Count from Titles"; 

    cmd.Connection = pubs_db_connection; 

    //Sample 05: Execute the Command & retrive scalar value  

    lblTotal.Text = System.Convert.ToString(cmd.ExecuteScalar()); 

}  
-----------------------------------------------------------------------------------------------------------------------------Q) Explain the ExecuteXmlReader?

 The execute reader method is flexible when we need the result set in the form of an XML document. The ExecuteXmlReader methods return an instance of XmlReader class.

 Example: XmlReader xmlreader = cmd.ExecuteXmlReader();

XmlDocument xdoc = new XmlDocument();

Using the XmlDocument class we load the XmlReader object and save it to the File System using the Save method.
-----------------------------------------------------------------------------------------------------------------------------Q)What are the methods of XML dataset objects?

There are various methods of XML dataset object:

GetXml() – Get XML data in a Dataset as a single string.

GetXmlSchema() – Get XSD Schema in a Dataset as a single string.

ReadXml() – Reads XML data from a file.

ReadXmlSchema() – Reads XML schema from a file.

WriteXml() – Writes the contents of Dataset to a file.

WriteXmlSchema() – Writes XSD Schema into a file.
-----------------------------------------------------------------------------------------------------------------------------Q)What is the difference between Command and CommandBuilder object?

a) SQLCommand is used to execute all kinds of SQL queries like DML(Insert, Update, Delete) & DDL like(Create table, drop table, etc)
b)SQLCommandBuilder object is used to build & execute SQL (DML) queries like select, insert, update & delete.

SQLCommand is used to retrieve or update the data from the database.
You can use the SELECT / INSERT, UPDATE, DELETE command with SQLCommand. SQLCommand will execute these commands in the database.
SQLBUILDER is used to build SQL commands like SELECT/ INSERT, UPDATE, etc.

A CommandBuilder object is used to automatically create/generate Update, Delete, and Insert SQL statements according to the changes made in the dataset. 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.

Syntax:

//code tomodify data in dataset
SqlCommand builder=new SqlCommandbuilder(adapter);

Builder.GetUpdateCommand();
//Without the sqlcommandbuilder()this line would fail.

Adapter.Update(ds,tableName);

Return dataset;
-----------------------------------------------------------------------------------------------------------------------------Q) What is Data view?

A Data View enables you to create different views of the data stored in a Data Table, a capability that is often used in data-binding applications. Using a Data View, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression. The data view is used to represent a whole table or a part of a table. It is the best view for sorting and Searching data in the data table.

A Data View provides you with a dynamic view of a single set of data, much like a database view, to which you can apply different sorting and filtering criteria. Unlike a database view, however, a Data View cannot be treated as a table and cannot provide a view of joined tables. You also cannot exclude columns that exist in the source table or append columns that do not exist in the source table, such as computational columns. You can use a  DataviewManager to manage view settings for all the tables in a DataSet. The DataViewManager provides you with a convenient way to manage default view settings for each table. When binding a control to more than one table of a DataSet, binding to a DataViewManager is the ideal choice.

To create a Data View:

 There are two ways to create a Data View. You can use the Data View constructor, or you can create a reference to the Default View property of the Data Table. The Data View constructor can be empty, or will also take either a Data Table as a single argument, or a Data Table along with filter criteria, sort criteria, and a row state filter.

DataView custDV = new DataView(customerDS.Tables["Customers"],  

"Country = 'USA'",  

"ContactName",  

DataViewRowState.CurrentRows); 

DataView custDV = customerDS.Tables["Customers"].DefaultView;  
-----------------------------------------------------------------------------------------------------------------------------Q)What is object pooling?

Object pooling is nothing but a repository of the objects in memory that can be used later. This object pooling reduces a load of object creation when it is needed. Whenever there is a need for an object, the object pool manager will take the request and serve accordingly.
----------------------------------------------------------------------------------------------------------------------------Q) What is connection pooling?

Connection pooling consists of a database connection so that the connection can be used or reused whenever there is a request to the database. This pooling technique enhances the performance of executing the database commands. This pooling definitely reduces our time and effort.

 Connection pooling is the ability of reusing your connection to the database. This means if you enable Connection pooling in the connection object, actually you enable the re-use of the connection to more than one user.

 ADO.NET uses a technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling reuses existing active connections with the same connection string instead of creating new connections when a request is made to the database. It involves the use of a connection manager that is responsible for maintaining a list, or pool, of available connections for a given connection string. Several pools exist if different connection strings ask for connection pooling.

 Example of Pooling:

connection.ConnectionString = sqlConnectString + "Connection Timeout=30;Connection Lifetime=0;Min Pool

Size=0;Max Pool Size=100;Pooling=true;"; 

//Open connection 

A Connection String in the Web.Config file with connection pooling option:

<connectionStrings> 

   <clear /> 

   <add name="sqlConnectionString" connectionString="Data Source=mySQLServer;Initial

Catalog=myDatabase;Integrated Security=True;Connection Timeout=15;Connection Lifetime=0;Min Pool

Size=0;Max Pool Size=100;Pooling=true;" /> 

</connectionStrings> 

SQL Server connection string pooling attributes:

Connection Lifetime: Length of time in seconds after creation after which a connection is destroyed.

The default is 0, indicating that the connection will have the maximum timeout.

Connection Reset: Specifies whether the connection is reset when removed from the pool. The default is true.

Enlist: Specifies whether the connection is automatically enlisted in the current transaction context of the creation thread if that transaction context exists. The default is true.

Load Balance Timeout: Length of time in seconds that a connection can remain idle in a connection pool before being removed.

Max Pool Size: Maximum number of connections allowed in the pool. The default is 100.

Min Pool Size: Minimum number of connections maintained in the pool. The default is 0.

Pooling: When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool. The default is true. 

-----------------------------------------------------------------------------------------------------------------------------Q) What is Data view?

The data view is the representation of data in various formats and it can be requested by the users. Data can be exposed in different sort orders or filter on the user condition with the help of Data view. Data Customization is also possible through Data View.
----------------------------------------------------------------------------------------------------------------------------Q)What is LINQ?

LINQ is a native query language for the .NET framework and it is specially designed to support queries with the .net applications. LINQ can be connected to SQL and MS Access.
----------------------------------------------------------------------------------------------------------------------------Q) What is the difference between Response. Expires and Response.ExpiresAbsolute?

Response. expires property specify the minutes of the page in cache from the time, the request has been served from the server.

But Response.ExpiresAbsolute property provides the exact time at which the page in cache expires.

Example:

Response. expires – Set to 10 mins and it will stay in the cache for 10 mins from the time it has been requested.

Response.ExpiresAbsolute – Oct 30 12:20:15. Till this specified time, Page will be in cache.
-----------------------------------------------------------------------------------------------------------------------------Q)Is it possible to edit data in Repeater control?

No, it is not possible to edit data in the Repeater control.
-----------------------------------------------------------------------------------------------------------------------------Q)What are the differences between OLEDB and SQLClient Providers?

OLEDB provider is used to access any database and provides the flexibility of changing the database at any time. SQLClient provider is used to accessing only SQL Server database but it provides excellent performance than OLEDB provider while connecting with SQL Server database.
-----------------------------------------------------------------------------------------------------------------------------Q)Which object is used to add a relationship between two Datatables?

DataRelation object is used to add a relationship between two or more datatable objects. The DataRelation is a class of disconnected architecture in the .NET framework. It is found in the System. Data namespace. It represents a relationship between database tables and correlates tables on the basis of matching columns.

Syntax:

DataRelation drel; 

drel = new DataRelation("All", ds.Tables[0].Columns[0], ds.Tables[1].Columns[0]);
-----------------------------------------------------------------------------------------------------------------------------Q) What is Binding Source class in ADO.NET?

The Binding Source class is used to simplify data binding as well as various operations on records. It has different methods like AddNew( ), MoveFirst( ), MovePrevious( ), MoveNext( ), etc which provide an easier way for adding a new row, moving to the first record, moving to the previous record, moving to the next record, and many other operations without writing code for them.
-----------------------------------------------------------------------------------------------------------------------------Q)Explain the DataTable and Relationship between the DataTable, the DataRow, and the DataColumn.

 A DataTable object represents a database table. A data table is a collection of columns and rows. The DataRow object represents a table row, and the DataColumn object represents a column of the table.

 The Columns property of the DataTable object represents the DataColumnCollection, which is a collection of DataColumn objects in a DataTable. You use a DataRow object to add data to a data table.

TheDataRowCollection object represents a collection of rows of a DataTable object, which can be accessed by its Rows property.
-----------------------------------------------------------------------------------------------------------------------------Q) What is Transactions and Concurrency in ADO.NET?

Transactions: ADO.NET providers a transaction class that represents a transaction. All data providers provide their own version of the transaction class. The IDbTransaction interface implements the basic functionality of the transaction class. All data provider-specific classes implement this namespace.

IDBTransaction->OleDBTransaction/SQLTransaction/ODBCTransaction

Methods:
Commit - Commits the transaction to the database. It saves changes made in the Database during the transaction. In simple terms, we can also say that it shows the end of transaction at that time.

Rollback - Rollbacks a transaction to the previous database state. It set the database in the previous stage which was, before the beginning of the transaction.

Begin(IsolationLevel) - Begins a nested database transaction passing the isolation level

Concurrency in ADO.NET

The ADO.NET model assumes that the optimistic concurrency is the default concurrency because of its disconnected nature of data. A user reads data in a data through a data adapter, and data is available to the user as a local copy of the data. The server database is available to all other users.

Another way of handling optimistic concurrency that you may be familiar with is by checking to see if a timestamp on the data source row has changed or the row version number has changed on the row being updated.

 Pessimistic locking on the database isn't really supported by the data providers because the connection to the database is not kept open, so you must perform all locking with business logic on the DataSet.
-----------------------------------------------------------------------------------------------------------------------------Q)What is a linked server?

A linked server is used to enable SQL Server to execute commands against OLE DB data sources on remote servers.
-----------------------------------------------------------------------------------------------------------------------------