ado.net

Why ADO.NET?

ADO.NET is data access layer

What is full name of ADO.NET?

ActiveX Data Objects

Give Short History of ADO.Net.

Microsoft developed ActiveX Data Objects (ADO) as a COM( Component Object Model ) wrapper around OLE DB for Databases.

Why ADO.NET Is a Better Data Access Layer than others?

Write a sample example of ADO.Net?

using System;
using System.Data;
using System.Data.OleDb;

// Step 1.Open Database Connection
OleDbConnection conn = new
                      OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\inventory\\inventory\\db\\invent.mdb;");

// Step 2.Connection database opened 
conn.Open();

// Step 3.Create DataSet and Command objects
OleDbCommand cmd = new OleDbCommand(
                            "SELECT * FROM empr4r",
                            conn);

// Step 4.Execute the command
cmd.ExecuteScalar();

// Step 5.Closed this connection 
conn.Close();

Explain ADO.NET Namespaces?

The System.Data is main namespace of ADO.Net  and it has DataSet and classes (DataTable, DataColumn, DataRow, DataRelation, Constraint,etc ). System.Data has a namespace System.Data.Common.

What is ADO.NET Data Structures?

ADO.NET has three different ways of accessing database information directly which are:
 
1.Commands :-have classes  SqlCommand and OleDbCommand , used directly to retrieve results from database queries.Command classes always support the IDbCommand interface.Commands classesare used to get a scalar result (the first column of the first row of a result set) or out parameters of a stored procedure.sort of data is retrieved using IDbCommand.ExecuteScalar() and IDbCommand.ExecuteNonQuery()

2.DataReaders has  SqlDataReader and OleDbDataReader.These classes are provide something similar to ADO's Recordset using a forward-only cursor.
3.DataSets classes Microsoft uses DataReaders within the managed providers' DataAdapters to fill DataSet

Define ADO.NET provides data access services in the Microsoft .NET platform?

ADO.NET to access data by using the new .NET Framework data providers which are given below:
1.Data Provider for SQL Server (System.Data.SqlClient)
2.Data Provider for OLEDB (System.Data.OleDb)
3.Data Provider for ODBC (System.Data.Odbc).
4.Data Provider for Oracle (System.Data.OracleClient).

Tell me about ADO.NET Classes?

The ADO.NET classes are found in System.Data.dll and are integrated with the XML classes in System.Xml.dll. ADO.NET is a set of classes that expose data access services to the .NET developer. There are two central components of ADO.NET classes: the DataSet, and the .NET Framework Data Provider.

Define Data Provider in ADO.NET?

Data Provider is a set of components including:
1.The Connection object (SqlConnection, OleDbConnection, OdbcConnection, OracleConnection)
2.The Command object (SqlCommand, OleDbCommand, OdbcCommand, OracleCommand)
3.The DataReader object (SqlDataReader, OleDbDataReader, OdbcDataReader, OracleDataReader)
4.The DataAdapter object (SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter, OracleDataAdapter).
DataSet object represents a disconnected cache of data which is made up of DataTables and DataRelations that represent the result of the command.

Define Dataset in ADO.Net?

Dataset is:
1.Disconnected Recordset objects similar to an array.
2.Supports disconnected data access and operations.
3.Scalability, Provide greater scalability because users no longer have to be connected to the database all the time.
DataSet object is made up of two objects:
1.DataTableCollection object containing null or multiple DataTable objects
2.DataRelationCollection object containing null or multiple DataRelation objects which establish a parent/child relation between two DataTable objects.

Define types of Datasets in ADO.Net?

There are two type of dataset in Ado.net: 
1.Typed DataSet: Typed DataSet is derived from the base DataSet class and then uses information in an XML Schema file (.xsd file) in order to generate a new class. 
Create a typed DataSet without designer - manually
a.Call the command prompt (cmd) at the location of the XSD schema file. 
b.Use the XSD.EXE utility to create the class for the typed DataSet.

2.Untyped DataSet : Untyped DataSet is not defined by a schema, instead, you have to add tables, columns and other elements to it yourself, either by setting properties at design time or by adding them at run time. 

Define the ways to 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.

Define DataAdapter in ADO.Net?

DataAdapter object is links the database and a Connection object with the ADO.NET-managedDataSet object through its SELECT and action query Commands. Means it works like a bridge DataAdapter specified that which data is to move into and out of the DataSet. DataAdapter provide references to SQL statements or stored procedures that are invoked to read or write to a database.
The DataAdapter provides four properties that allow us to control how updates are made to the server:
SelectCommand 
UpdateCommand 
InsertCommand 
DeleteCommand 

Define methods of DataAdapter in ADO.Net?

The DataAdapter includes three main methods:
1.Fill (populates a DataSet with data). 
2.FillSchema (queries the database for schema information that is necessary to update). 
3.Update (to change the database, DataAdapter calls the DeleteCommand, the InsertCommand and the UpdateCommand properties). 

How to DataBindings for TextBoxes in ADO.Net?

To bind some elements of a data source with some graphical elements of an application, this ability known as DataBinding. The data in Windows Forms is bound by calling DataBindings. Windows Forms allows you to bind easily to almost any structure that contains data.
Windows Forms Controls support two types of data binding:
a.Simple Data Binding: To display a single data element, means to display a column value from a DataSet table, in a control. It is possible to bind any property of a control to a given data value. Simple Data Binding can be performedby two ways:
   1.At design time using DataBindings property of a control 
   2.Dynamically at run time. This is the type of binding typical for controls such as a TextBox control or Label control that displays typically only a single value.
b.Complex Data Binding: To bind  more than one data element, typically more than one record in a database, or to more than one of any other type of bindable data element. DataGrid, ListBox and ErrorProvider controls support complex data binding. 

Define ADO.NET Objects in brief?

ADO.net includes many objects you can use to work with data. Some of the primary objects are:
1. The SqlConnection Object
2. The SqlCommand Object
3. The SqlDataReader Object
4. The DataSet Object
5. The SqlDataAdapter Object 

How to Create a SqlConnection Object in Ado.Net?

SqlConnection object is like a c# object.The object declare like that:
 SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI"); 
This argument is called a connection string. It is define as:
Data Source:  Identifies the server.  
Integrated Security:  Set to SSPI to make connection with user's Windows login
User ID:  Name of user configured in SQL Server. 
Password:  Password matching SQL Server User ID.
Using a SqlConnetion the connection creates as:
1.Instantiate the SqlConnection. 
2.Open the connection. 
3.Pass the connection to other ADO.NET objects. 
4.Perform database operations with the other ADO.NET objects. 
5.Close the connection.

How to create a SqlCommand Object in ADO.Net?

SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
Above syntax is written to instantiating a SqlCommand object.  It takes a string parameter that holds the command you want to execute and a reference to a SqlConnection object.

How to querying in ADO.Net, when using SqlCommand?

Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader(); 
Passing the command string and connection object to the constructor.  Then we obtain a SqlDataReader object by calling the ExecuteReader method of the SqlCommand object, cmd.

How to Inserting Data when using SqlCommand Object in ADO.Net?

To insert data into a database, use the ExecuteNonQuery method of the SqlCommand object. 
Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(insertString, conn);
 
Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();

How to Updating Data when using SqlCommand Object in ADO.Net?

The ExecuteNonQuery method is also used for updating data.
  The following code shows how to update data: 
1.Prepare command string
2.Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString)
3.Set the Connection property
cmd.Connection = conn;
4.Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery(); 

How to Deleting Data when using SqlCommand Object in ADO.Net?

To deleting data also using the ExecuteNonQuery method.  The following example shows how to delete a record from a database with the ExecuteNonQuery method: 
1.Prepare command string
2.Instantiate a new command
 SqlCommand cmd = new SqlCommand();
3.Set the CommandText property
 cmd.CommandText = deleteString;
4.Set the Connection property
 cmd.Connection = conn;
5.Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery(); 

How to create a SqlDataReader Object in ADO.Net?

There is a little different way to creating object of a SqlDataReader like other object creation in C#. Necessary call ExecuteReader on a command object.
SqlDataReader rdr = cmd.ExecuteReader();
The ExecuteReader method of the SqlCommand object, cmd , returns a SqlDataReader instance.

How to Read data with SqlDataReader Object in ADO.Net?

SqlDataReader rdr = cmd.ExecuteReader();
The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with a while loop.
while (rdr.Read())
{
string conta = (string)rdr["ContactName"];
string comp = (string)rdr["CompanyName"];
string cty    = (string)rdr["City"];
Console.Write("{0,-25}", conta);
Console.Write("{0,-20}", cty);
Console.Write("{0,-25}", comp);
Console.WriteLine();
}

How to Create a DataSet Object in ADO.Net?

Dataset object create as:
DataSet dsStudents = new DataSet(); 
DataSet constructor doesn't require parameters.  However there is one overload that accepts a string for the name of the DataSet, which is used if you were to serialize the data to XML.  Since that isn't a requirement for this example, I left it out.  Right now, the DataSet is empty and you need a SqlDataAdapter to load it.

How to create a SqlDataAdapter in ADO.Net?

SqlDataAdapter holds the SQL commands and connection object for reading and writing data.  It initializes with a SQL select statement and connection object:
SqlDataAdapter daStudent = new SqlDataAdapter(
    "select StudentID, CollegeName from Students", conn);
The SQL select statement specifies what data will be read into a DataSet.   
The connection object, conn, should have already been instantiated, but not opened. 
SqlCommandBuilder is instantiated with a single constructor parameter of the SqlDataAdapter, daStudent, instance.
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daStudent);
SqlCommandBuilder will read the SQL select statement (specified when the SqlDataAdapter was instantiated), infer the insert, update, and delete commands.

How to Filling the DataSet in Disconnected mode in ADO.Net?

Once you have a DataSet and SqlDataAdapter instances, you need to fill the DataSet.  Here's how to do it, by using the Fill method of the SqlDataAdapter:
SqlDataAdapter daStudent = new SqlDataAdapter
       ("select StudentID, CollegeName from Students", conn);
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daStudent);
daStudent.Fill(dsStudent, "Students"); 
Fill method, takes two parameters: a DataSet and a table name.

What are the basic Namespaces in ADO.Net?

Some basic namespaces are: 
1.System.Data (basics types)
2.System.Data.OleDb (OLEDB Provider)
3.System.Data.SqlClient (Microsoft Sql Server provider)
4.System.Data.Common
5.System.Data.SqlTypes
6.System.Data.Odbc (ODBC Provider)
7.System.Data.Odbc.OracleClient (Oracle Provider)
8.System.Data.SqlServerCe (Compact framework)

Compare the Connection-Oriented and Disconnected Scenario in ADO.Net?

Connection-Oriented Scenario:  
1.Always accessing current data
2.Low number of concurrent data accesses
3.Many write accesses situation
4.IDbConnection, IDbCommand, IDataReader

Disconnected Scenario:
1.Modification in Dataset is not equal to modification in data source
2.Many concurrent read accesses situation
3.Dataset, Data table, DbDataAdapter

What is the difference between “Dataset” and “DataReader”?

1.To cache data and pass to a different tier Dataset forms the best choice and it has decent XML support
2.Dataset is a disconnected architecture; DataReader has live connection while reading data.
3.To access data from more than one table Dataset forms the best choice.
4.If we need to move back while reading records, data reader does not support this functionality.
5.A biggest drawback of Dataset is speed. As Dataset carry considerable overhead because of relations, multiple table etc speed is slower than DataReader. Always try to use DataReader wherever possible, as it is meant especially for speed performance.
 

Define, how can we load multiple tables in a Dataset?

objCommand.CommandText = "Tab1"
objDataAdapter.Fill(objDataSet, "Tab1")
objCommand.CommandText = "Tab2"
objDataAdapter.Fill(objDataSet, "Tab2")

Above code shows how to load multiple Data Table objects in one Dataset object. Sample code shows two tables Tab1 and Tab2 in object ObjDataSet.
lstdata.DataSource = objDataSet.Tables("Tab1").DefaultView
In order to refer Tab1 Data Table, use Tables collection of Datasets and the Default view object will give you the necessary output.

Explain the difference between an ADO.NET Dataset and an ADO Recordset?

1.Using dataset retrieve data from two databases like Oracle and SQL server and merge them in one dataset, with recordset this is not possible.
2.Recordset uses COM. But all representation of Dataset is using XML.
3.Dataset can be transmitted on HTTP while Recordset cannot be transmitted.

Difference between classic ADO and ADO.NET?

1.We have recordset in ADO and in ADO.NET we have dataset.
2.In recordset we can only have one table. If we want to accommodate more than one table we need to do inner join and fill the recordset. Dataset can have multiple tables.
3.All data persist in XML as compared to classic ADO where data persisted in Binary format also. 

Define Connection Pooling in brief?

Connection Pooling make a single connection instance, which allows that instance to connect to all the databases. It does not open and close the connection object multiple times.

Define 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.

What is ADO.NET?

ADO stands for ActiveX  Data Object .ADO.NET is an object-oriented set of libraries that allows  to interact with data sources.  The data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file.ADO .NET consists of classes that allow a .NET application to connect to the data source, executes commands and manage disconnected data. One of the key Differences between ADO.NET and other database technologies is how it deals with Challenge with different data sources, that means, the code you use to connect to an SQL Database will not differ that much to the one connecting to an Oracle Database.