Basics Of ADO.NET Components

What Is ADO.NET

ADO.Net is a data access technology that enables connection to the database for data processing (insert, update, delete, and select) operations in a disconnected mode. As data processing increasingly uses a multi-tier development approach, the .Net application needs to be developed in disconnected mode to provide better scalability, as the traditional way of data processing is connection-based where clients remain connected to the database until data not processed and create more traffic to the database. The scalability and Interoperability of ADO.Net on ADO are that ADO transfer data in the binary format while ADO.NET does this in XML format.

How Does ADO.NET Work?

Connected Vs Disconnected

A) Connected mode is faster in performance because it works in forward only-read only manner whereas disconnected get low in speed and performance because it can insert, update, delete, and select data.

B) Connected mode can hold the data of a single table whereas disconnected can hold multiple tables of data.

C) Connected mode is forward-only-read-only objects whereas Disconnected can process data in any dimension.

Example:

1) DataReader is working in connected architecture as it keeps the connection open until all records are fetched
2) DataSet is working in disconnected architecture as it gets all records at once and closes the connection and are available even after the connection is closed

Understanding ADO.NET Components

ADO.Net Components

1. ADO.Net Data Provider

Connection (connection string): Connection object is used to establish a connection between the database and .Net application, all the database processing is handled once the connection between DB and application created successfully.

Example:

string connectionString = “Data Source=(local);Initial Catalog=Northwind;
Integrated Security=true”;
SqlConnection connection = new SqlConnection(connectionString);
connectionObj.Open();

Command (Transection): Command object contains queries like select, update, insert and delete commands which need to be executed on database server. To execute command we need to provide the connection to it and set the command type as below.

Example:

CommandObj.CommandType=CommandType.Text; // could be text or stored procedure, text, table)
CommandObj.Connection = connectionObj;
commandObj.CommandText =”Select * from EmpTable”;
CommandObj.ExecuteNonQuery();

DataReader: Data reader is an alternative to the Dataset and DataAdapter combination. Data reader is working on connected mode. We can use it if we need to retrieve data only. It reads data in forward only-read only manner. It’s faster than the DataSet. So these objects are suitable for read-only data access.

Data Adapter(Select, Insert, Update, Delete): Data Adapter is a bridge between database and dataset to transfer data, receive data. It uses select, update, delete command. The select command is used to retrieve data from the database. Insert, update, delete commands are used to update the changes at the database using the dataset. Data Adapter uses the connection object to process all these requests.

2. DataSet

There are two types of DataSets

1) Typed DataSet
2) Untyped DataSet

Typed DataSet: is derived from the base DataSet class and then uses information in .xsd file to generate a new class. Information from the schema (tables, columns, and so on) is generated and compiled into this new DataSet class as a set of first-class objects and properties. Typed dataset is easier to read. It’s also supported by IntelliSense in the Visual Studio Code Editor. At compile time, it has type checking so that there are fewer errors in assigning values to DataSet members.

Therefore, using Typed DataSet has many advantages.

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. Typical scenario: if you don’t know in advance what the structure of your program is that is interacting with a component that returns a DataSet.

Data Set can be a collection of data table objects which is used to store the data retrieved from Database so that we can process the retrieved data in disconnected mode into our application.

Example:

dataAdapterObj.Fill(DataSetObj)

So when we call Fill method the data adapter object opens a connection and executes the select query, stores the result into the dataset, and closes the connection back.

DataSet can also be used in XML data processing as DataSet contents can be converted to XML data and vice versa. So the design of dataset enables you to transport data to the client over the web using XML Web services.

DataTable Collection

Data Table is a collection of DataRow and DataColumn. We can have multiple DataTable in a DataSet.

Data Relation

Data relation can be implemented on different tables on the basis of keys as Parent-Child relation.

Example:

DataSet ds_order = new DataSet(“Order”);
ds_order.Tables.Add(orders);
ds_order.Tables.Add(orderdetail);
ds_order.Relations.Add(“Order_OrderDetail”,orders.Columns[“OrderID”], orderDetail.Columns[“OrderID”]);

Required Namespaces

• using System;
• using System.Data;
• using System.Data.SqlClient; (If DB server is SqlServer)
• using System.Data.OracleClient; (If DB server is Oracle)
• using System.Data.Oledb; (If DB server is any Other)

Sample Code:

using System;
using System.Data;
using System.Data.SqlClient;

public partial class Default : System.Web.UI.Page
{
SqlConnection connection; // declaring connection object
SqlCommand command; // declaring command object
SqlDataAdapter adp ; // declaring data adapter object

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetEmpName();
GridView1.DataSource = GetData();
GridView1.DataBind();
}

}
private DataTable GetData()
{
connection = new SqlConnection(“Data Source=(local);
Initial Catalog=Northwind; Integrated Security=true”);

command = new SqlCommand();
adp = new SqlDataAdapter();

// We are using inline queries in this article
// We can also call Stored Procedures also with Parameters
// For Inline queries CommandType = CommandType.Text;
// For Database objects CommandType = CommandType.StoredProcedure;
command.CommandType = CommandType.Text;
DataTable dt = new DataTable();

// Query string with a parameter.
string queryString = “SELECT ProductID, UnitPrice, ProductName from
dbo.products ORDER BY UnitPrice DESC;”;

command.Connection = connection;
command.CommandText = queryString;

try
{
connection.Open();
adp.SelectCommand = command;
adp.Fill(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return dt;
}

protected void btnSave_Click(object sender, EventArgs e)
{
string queryString = “Insert into dbo.products(UnitPrice, ProductName) values(“+
txtUnitPrice.Text +”,'”+ txtProductName.Text + “‘)”;

command.Connection = connection;
command.CommandText = queryString;

try
{
connection.Open();
return command.ExecuteNonQuery().ToString();

}
catch (Exception ex)
{
return ex.Message;
}
}
}