ADO.NET

Data Grid and Repeater :-

Datagrid is,
1. One which has advanced features and lets you do lot many things like paging and sorting your data without much effort.
2. DataGrid can hold text data, but not linked or embedded objects.

Whereas a DataRepeater is,
1. Which does not have the paging feature but we can do it by coding.
2. One which can hold other controls and can embed objects.
3. It can embed a Datagrid within it but not viceversa.

Apart from this a Data Repeater
-- is used in places where you need more control over the rendering of your data
-- have very flexible templates that give you total control over the formatting of your data

Purpose of Connection Polling :-

Connection pooling enables an application to use a connection from a pool of connections that do not need to be re-established for each use. Once a connection has been created and placed in a connection pool, an application can reuse that connection without performing the complete connection creation process.

execution time out in web.config

connection time out in connection string

Create a DataView from DataTable :-

DataView dView = new DataView(dTable);

Relationship of XML and ADO.NET :-

ADO.NET utilizes the power of XML by providing disconnected access to data. This is designed with the help of XML classes in .NET Framework which form the components of single architecture.

Data access objects or DAO :-

DAO is used for database access on windows platform. It creates a work space object in which applications or operations are performed. There are two types of database engines they are Jet database engine and ODBC direct database engine.

Advantage of ADO.Net :-

• ADO.NET Does Not Depend On Continuously Live Connections
• Database Interactions Are Performed Using Data Commands
• Data Can Be Cached in Datasets
• Datasets Are Independent of Data Sources
• Data Is Persisted as XML, As in ADO.NET transmission is via XML format, therefore it can pass through firewalls.

OLEDB Provider vs SqlClient :-

SQLClient is faster than the Oracle provider, and faster than accessing database via the OleDb layer. It's faster because it accesses the native library (which automatically gives you better performance), and it was written with lots of help from the SQL Server team.

Add a new row in DataTable :-

To add a new row in DataTable, we can use NewRow() method of the DataTable object.

DataTable dTable = new DataTable();

DataRow row = null;

for (int i = 0; i < 5; i++)

{

       row = dTable.NewRow();

       row["Name"] = i + " - Raja";

       row["Address"] = "USA";
       dTable.Rows.Add(row);

}

Classes are found in System.Data.Common NameSpace :-

1)DataColumnMapping

2)DataTableMapping

Classes are found in System.Data NameSpace :-

DataSet

DataTable

DataColumn

DataRow

DataRealation

Constraint

Stored procedure return more than one result set and datareader used to fetch record, how you fetch second result set using datareader :-

Use DataReader.NextResult()

Dataview in ADO.Net :-

We can create DataView in two ways. Either we can use the DataView constructor, or we can create a reference to the DefaultView property of the DataTable.

DataView dView = new DataView(dTbl);

dView = dataSet.Tables[0].DefaultView;

Can you update the records in database using datareader :- Well, you cannot update.

Components of .NET DataProvider :-

Connection Object:- It is used to connect to the data source. Data source can be any database file. The connection object contains information like the provider name, server name, datasource name, user name and password.

Command Object:- It is used for connect the connection object to a DataReader or DataAdapter object. The command object allow us to execute SQL statement or a stored procedure in a data source.

DataReader Object:- It is used to read the data in a fast and efficient manner from the database. It is generally used to extract one or a few records or specific field values or to execute simple SQL statement.

DataAdapter Object:- It is used to fill data from the database into the DataSet object. it is use din the disconnected approach.

Fill DataSet with data :-

To fill DataSet with data we have to use Fill() method of DataAdapter object.

Fill() has several overloads. But the simple one is

Fill(DataSet,  DataTable)

Convert a DataSet to a DataReader :-

DataTableReader rd = ds.Tables[0].CreateDataReader();

Connected and disconnected data access in ADO.NET :-

In connected data access you can connect through the DataReader objects of data provider. This object requires exclusive use of the connection object. It can provide fast and forward-only data access. It doesn't allow editing.

Disconnected data access is achieved through the DataAdapter object. This object establishes connection, executes the command, load data in the DataSet. The Dataset works independent of database. It contains data in the memory and can edit the data. The changes in the data can be transmitted to the database using Update method of DataAdapter object.

DataViewManager :-

DataViewManager is used to manage view settings of the tables in a DataSet. A DataViewManager is best suited for views that consist of a combination of multiple tables.

Use of SqlParameter.Direction Property :-

This Property is used to initialize the Sql Parameter type and sets some of its properties such as input-only , output-only , bidirectional , or a stored procedure which will return value parameter. The default property is Input.

AcceptChanges(), GetChanges() , HasChanges(), RejectChanges()  :-

When AcceptChanges() call on the DataSet, then the edit mode of DataRow objects will end successfully with editing.

The GetChanges() method - Returns the DataSet object, which is changed since it was loaded or since the AcceptChanges() method was executed.

The HasChanges() method - Indicates if any changes occurred since the DataSet object was loaded or after a call to the AcceptChanges() method was made.

If you want to revert all changes since the DataSet object was loaded, use the RejectChanges() method.

Access database using ADO.Net :-

1. Create a connection to the database using a connection object
2. Invoke a command to create a DataSet object using an adapter object
3. Use the DataSet object in code to display or to change items in the database
4. Invoke a command to update the database from the DataSet object using an adapter object
5. Close the database connection (if you explicitly opened it)

Which ADO.NET classes is an abstract class :-

DbConnection, DbConnection is the abstract class.We cannot create instances of DbConnection class, but SqlConnection and DataView are instantiated.

Which event of the SqlConnection class can be used to retreive error information from a database :-

InfoMessage

Use of SqlCommandBuilder :-

The CommandBuilder class is used to automatically update a database according to the changes made in a DataSet. It is a class defined by System.Data.SqlClient namespace and is used to generate commands(insert,delete,update) that can be used to reflect DataSet changes back to a sql server database. Update method of SqlDataAdapter uses the SqlCommandBuilder's commands to reflect DataSet changes back to a sql server database.

Example:
//save a record in a database using the Update method of SqlDataAdapter

using System.Data;

using System.Data.SqlClient;

class dd

{

    static void Main()

    {

        SqlConnection cn = new SqlConnection("server=.;uid=sa;pwd=1234;database=employee");

        SqlDataAdapter da = new SqlDataAdapter("select * from emp", cn);

        DataTable dt = new DataTable();

        da.Fill(dt);

        //emp has 2 columns known as eno and ename.

        //Initialize the SqlCommandBuilder.

        SqlCommandBuilder cd = new SqlCommandBuilder(da);

        //create a DataRow

        DataRow dr = dt.NewRow();

        dr["eno"] = 100;

        dr["ename"] = "king";

        //the row is temporarily saved

        dt.Rows.Add(dr);

        //save the Row permanently

        da.Update(dt);

    }

}

SqlCommand vs SqlDataAdapter :-

SqlCommand:
1) Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.
2)It can be initalized by any T-SQL satement
3)we have to explicitly initalize SqlDataReader when we use SqlCommand
4)database connections have to be manually opened and closed.

SqlDataAdapter:
1)Represents a set of data commands and a database connection that are used to fill the DataSet/DataTable and update a SQL Server database.
2)It can be initialized using Select command either directly in its constructor or by assigning an object of SqlCommand containing a select statement to its SelectCommand property.
3)SqlDataReader is internally used in SqlDataAdapter.
4)database connections are automatically opened and closed.

Typed and UnTyped DataSets :-

Typed Datasets,
1) Typed DataSets use explicit names and DataTypes for their members.
example:
northwindDataSet.Products.ProductNameColumn.Caption = "pnames";
2) They have .xsd file (Xml Schema definition) file associated with them and do error checking regarding their schema at design time using the .xsd definitions.

UnTyped DataSets,
1) UnTyped DataSets use table and column collections for their members
ex:
ds.Tables["emp"].Columns["eno"].ReadOnly=true;
2) They do not do error checking at the design time as they are filled at run time when the code executes.

Sort a DataSet :-

DataView dv = new DataView(ds.Tables["demo"]);

dv.Sort = "productname";

dataGridView1.DataSource = dv;

Name of the data source when we use SQL Server Express Edition :-

It is .\SQLEXPRESS
It is specified in the connection string. The complete connection is like this:

Data Source=.\SQLEXPRESS;AttachDbFileName=c:\abc.mdf;Integrated Security=True;
User Instance=True

It is presumed that abc.mdf file exists in c drive. abc.ldf must also be there. Integrated Security is used to authenticate SQL Server Express Edition.
User Instance=true. It starts SQL Server Express Edition using the current user's account.

Difference between Execute Reader, Execute Scalar and Execute Non Query methods :-

Execute Reader
Execute Reader will be used to return the set of rows, on execution of SQL Query or Stored procedure using command object.

Execute Scalar
Execute Scalar will be used to return the single value, on execution of SQL Query or Stored procedure using command object.

Execute Non Query
If the command or stored procedure performs INSERT, DELETE or UPDATE operations, then we use Execute Non Query method. Execute Non Query method returns an integer specifying the number of rows inserted, deleted or updated.

Methods provided by the dataset object to generate XML in ADO.Net :-

1.ReadXML: Read’s a XML document in to Dataset.

2.GetXML: This is a function, which returns the string containing XML document.

3.Writexml: This writes a XML data to disk.

Property of dataadapter :-

DataAdapter provides four properties,

SelectCommand 

UpdateCommand 

InsertCommand 

DeleteCommand 

Populate a Dataset in ADO.Net :-

There are following way to populate Dataset: 

a.By using DataAdapter objects and Fill method.

b.By creating DataTable, DataColumn and DataRow objects programmatically.

c.Read an XML document or stream into the DataSet.

d.Merge (copy) the contents of another DataSet, with the Merge method.

Two fundamental objects in ADO.NET :-

DataReader and DataSet are the two fundamental objects in ADO.NET.

Object pooling :-

Object pooling is a concept of storing a pool (group) of objects in memory that can be reused later as needed. Whenever, a new object is required to create, an object from the pool can be allocated for this request; thereby, minimizing the object creation.

Check whether a DataReader is closed or opened :-

IsClosed property is used to check whether a DataReader is closed or opened.

Pre-requisites for connection pooling :-

The prerequisites for connection pooling are as follows:

·         There must be multiple processes to share the same connection describing the same parameters and security settings.

·         The connection string must be identical.

Object is used to add a relationship between two DataTable objects :-

The DataRelation object is used to add relationship between two DataTable objects.

Clone() vs Copy() methods of the DataSet class :-

The Clone() method copies only the structure of a DataSet. The copied structure includes all the relation, constraint, and DataTable schemas used by the DataSet. The Clone() method does not copy the data, which is stored in the DataSet.

The Copy() method copies the structure as well as the data stored in the DataSet.

Perform asynchronous data access, what must be added to the connection string :-

Asynchronous=true

GetSchemaTable() in ADO.NET :-

The GetSchemaTable method returns a DataTable property that contains the column schema for a DataReader. The DataTable contains one row for each field in the resultset.

Default TimeOut for a SqlCommand.CommandTimeout property :-

The default timeOut is 30 seconds.

If you want to increase the TimeOut see the below example code
For example:-

SqlCommand cmd = new SqlCommand("select * from tablename", conn);

cmd.CommandTimeout = 50;

SqlDataReader dr = cmd.ExecuteReader();

Ensure that the database connections are always closed :-

To ensure that the database connections are always closed, open the connection inside of a using block, as shown in the following code fragment.

using (SqlConnection ConnectionObject = new SqlConnection())

{

ConnectionObject.Open();

       //The database connection will be closed when the control exits the using code block

}

Methods that can ensure asynchronous execution of the Transact-SQL statement or stored procedure :-

BeginExecuteNonQuery

BeginExecuteReader

Benefit of Stored procedure :-
1) Improved Performance
2) Security
3) Easy to maintain
4) Reduce network traffice


Difference between Response.Expires and Response.ExpiresAbsolute? 
Response.Expires,
This property specifies the number of minutes before a page cached in the browser expires ie. if the user returns to the same page before the specified number of minutes the cached version of the page is displayed. 
Response.ExpiresAbsolute,
Using this property we can set the date and/or time at which page cached in the browser expires.


Clear data from all the tables in a daataset,
Dataset.Clear()


Best method to retrieve two values from database :-
ExecuteNonQuery using Output parameter


Add Autoincrement column in Datatable :-
DataTable dTable = new DataTable();
DataColumn auto = new DataColumn("AutoID", typeof(System.Int32));
dTable.Columns.Add(auto);
// specify it as auto increment field
auto.AutoIncrement = true;
auto.AutoIncrementSeed = 1;
auto.ReadOnly = true;