Define ADO.NET

ADO.NET is Microsoft's latest data access technology. ADO.NET provides a rich set of .NET classes that facilitate for accessing data stored in any kind of database (relational or non-relational) and manipulating that data in console, window and web applications.

.NET Framework Data Access Namespaces
The .NET Framework provides a number of classes for database access. These are contained in different namespaces. The important namespaces are listed below:

S.No.Namespace Description
System.DataConsists of the classes that constitute the ADO.NET architecture.
System.Data.OleDb Consists of the classes that make up the .NET data provider for OLE DB-compatible data sources. These classes allow you to connect to an OLE DB data source, execute commands against the source, and read the results.
System.Data.SqlClient Consists of the classes that make up the .NET data provider for SQL Server, which allows you to connect to SQL Server 7.0 (or later), execute commands, and read results. It is similar to the System.Data.OleDb namespace, but is optimized for access to SQL Server 7.0 and later.
System.Data.XMLConsists of the classes that provide standards-based support for processing XML.

.NET Data Providers

.NET data provider is used for connecting to a database, executing commands and retrieving results. The .NET data provider is designed to be lightweight, creating a minimal layer between the data source and your code, increasing performance. It includes the following three core objects:

  1. Connection: Establishes a connection to a specific data source. The class for connection is SqlConnection.
  2. Command: Executes a command (a SQL statement, or a stored procedure). The class for command is SqlCommand.
  3. DataReader: Reads a forward-only, read-only stream of data from a data source. There are 2 classes for Data reader. i.e. SqlDataReader and SqlDataAdapter.

The .NET Framework includes 2 data providers:

  1. SQL Server .NET Data Provider (for Microsoft SQL Server version 7.0 or later): It uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer.
  2. OLE DB .NET Data Provider: It communicates to an OLE DB data source through both the OLE DB Service component, which provides connection pooling and transaction services, and the OLE DB Provider for the data source. The providers that have been tested with ADO.NET are:
    • SQLOLEDB: Microsoft OLE DB Provider for SQL Server. Used for earlier versions of SQL Server.
    • MSDAORA: Microsoft OLE DB Provider for Oracle.
    • Microsoft.Jet.OLEDB.4.0: OLE DB Provider for Microsoft Jet.

If you wish to use another database such as Access or Oracle, you must use the OLEDB .NET Data Provider. Its classes are located in the System.Data.OleDb namespace. They are similar to the classes in the System.Data.SqlClient namespace, except that they start with OleDb instead of SQL.

Creating, Opening a connection and Retrieving Records from a database.

Example 1: Retrieving Records

using System.Data.SqlClient;
protected void Page_Load(object sender, EventArgs e)
{
    SqlConnection con;
    SqlCommand cmd;
    SqlDataReader dr;
    con = new SqlConnection(@"Data Source=pc-2net\sqlexpress; 
    AttachDbFilename=E:\student data\RAJAN.mdf; Integrated Security=True");
    con.Open();
    cmd = new SqlCommand("select name from customer", con);
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {	
        Response.Write("<li>");
        Response.Write( dr["name"]);
    }
    dr.Close();
    con.Close();
}
  • ramesh
  • aakriti
  • marry
  • candy
  • harry
  • ramesh
  • mukesh
  • komal
  • aakash
  • sammy

If you are looking for a specific record such as an employee's grade, or executing SQL functions such as Count(*), which returns the number of records, you can use the ExecuteScalar() method instead of ExecuteReader().

Example 2: Using a Parameterized Query

using System.Data.SqlClient;
protected void btnSubmit_Click(Object sender, EventArgs e)
{
    SqlConnection con;
    SqlCommand cmd;
    con = new SqlConnection(@"Data Source=pc-2net\sqlexpress;AttachDbFilename=E:\student data\RAJAN.mdf;Integrated Security=True");
    con.Open();
    cmd = new SqlCommand("select name from customer where id = @id", con);
    cmd.Parameters.Add("@id",txtId.Text);
    lblOutput.Text = cmd.ExecuteScalar().ToString();
    con.Close();
}
<div>
    Enter Id: <asp:TextBox ID="txtId" runat="server"></asp:TextBox> <br>
    <asp:Button ID="btnSubmit" runat="server" Text="Button" onclick="btnSubmit_Click" /> <br>
    <asp:Label ID="lblOutput" runat="server" Text="Label"></asp:Label>
</div>
ado select

Inserting new records, Updating existing records, Deleting records

You can insert a new record in a database table by using the SQL Insert statement, update existing records by using the SQL Update statement, and delete a record by using the SQL Delete statement. Wrap the statement in a command and execute it with the ExecuteNonQuery() method.

Example 3: Inserting Records

using System.Data.SqlClient;
protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con;
    SqlCommand cmd;
    con = new SqlConnection(@"Data Source=pc-2net\sqlexpress; AttachDbFilename=E:\student data\RAJAN.mdf; Integrated Security=True");
    con.Open();
    cmd = new SqlCommand("Insert into customer values(@id, @name, @address, @city, @post_code, @salary)", con );
    cmd.Parameters.Add("@id",Convert.ToInt32(txtId.Text));
    cmd.Parameters.Add("@name",txtName.Text);
    cmd.Parameters.Add("@address",txtAddress.Text);
    cmd.Parameters.Add("@city",txtCity.Text);
    cmd.Parameters.Add("@post_code",txtPost_code.Text);
    cmd.Parameters.Add("@salary",Convert.ToSingle(txtSalary.Text));
    cmd.ExecuteNonQuery();
    lblMsg.Text = "Record Inserted!";
    con.Close();
}
<html>
    <head>
        <style>
            .format {
                float:left;
                padding: 20px;
                line-height:22px;
            }
        </style>
    </head>
    <body>
        <h2>Enter New Record in Customer Table </h2>
        <form id="Form1" runat="server">
        <div>
            <div class="format">
                <p><b>Customer ID: </b></p>
                <p><b>Name: </b></p>
                <p><b>Address: </b></p>
                <p><b>City: </b></p>
                <p><b>Postal Code: </b></p>
                <p><b>Salary: </b></p>
            </div>
            <div class="format">
                <p><asp:TextBox id="txtId" columns="30" runat="server"/></p>
                <p><asp:TextBox id="txtName" columns="30" runat="server"/> </p>
                <p><asp:TextBox id="txtAddress" columns="30" runat="server"/></p>
                <p><asp:TextBox id="txtCity" columns="30" runat="server"/></p>
                <p><asp:TextBox id="txtPost_code" columns="30" runat="server"/></p>
                <p><asp:TextBox id="txtSalary" columns="30" runat="server"/></p>
                <p><asp:Button ID="btnSubmit" text="Insert Record" onclick="Button1_Click" runat="server"/></p>
                <p><b><asp:Label id="lblMsg" runat="server"/></b></p>
            </div>
        </div>
        </form>
    </body>
</html>
Output:
Ouput

Example 4: Updating Records


Output:
Ouput

Example 5: Deleting Records


Output:
Ouput