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.Data | Consists 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.XML | Consists of the classes that provide standards-based support for processing XML. |
.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:
The .NET Framework includes 2 data providers:
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(); }
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>
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:
Example 4: Updating Records
Example 5: Deleting Records