ADO.Net,
Asp.net,
ASP.Net 2.0,
ASP.NET 3.5,
C#
The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data including tables, constraints, and relationships among the tables. Because the DataSet is independent of the data source, a DataSet can include data local to the application, as well as data from multiple data sources. Interaction with existing data sources is controlled through the DataAdapter.
Each .NET Framework data provider included with the .NET Framework has a DataAdapter object: the .NET Framework Data Provider for OLE DB includes an OleDbDataAdapter object, the .NET Framework Data Provider for SQL Server includes a SqlDataAdapter object, and the .NET Framework Data Provider for ODBC includes an OdbcDataAdapter object. A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source and Command objects to retrieve data from and resolve changes to the data source.
The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the DataSet. These properties are covered in more detail in Updating the Database with a DataAdapter and the DataSet.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Page_Load(object sender, System.EventArgs e) {
if (!Page.IsPostBack) {
SqlConnection MySqlConnection;
SqlCommand MySqlCommand;
SqlDataAdapter MySqlDataAdapter;
DataTable MyDataTable;
MySqlConnection = new SqlConnection();
MySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["MyAppConnectionString"].ConnectionString;
MySqlCommand = new SqlCommand();
MySqlCommand.CommandText = "SELECT TOP 50 * FROM < Tbl Employee >";
MySqlCommand.CommandType = CommandType.Text;
MySqlCommand.Connection = MySqlConnection;
MyDataTable = new DataTable();
MySqlDataAdapter = new SqlDataAdapter();
MySqlDataAdapter.SelectCommand = MySqlCommand;
MySqlDataAdapter.Fill(MyDataTable);
GridViewData.DataSource = MyDataTable.DefaultView;
GridViewData.DataBind();
MySqlDataAdapter.Dispose();
MySqlCommand.Dispose();
MySqlConnection.Dispose();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>DataAdapter Example: How to use DataAdapter in ASP.Net C#</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridViewData" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>
ADO.NET includes many objects you can use to work with data. This section introduces some of the primary objects you will use. Over the course of this tutorial, you'll be exposed to many more ADO.NET objects from the perspective of how they are used in a particular lesson. The objects below are the ones you must know. Learning about them will give you an idea of the types of things you can do with data when using ADO.NET.
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApplicationExample
{
public partial class FormExample : Form
{
public FormExample()
{
InitializeComponent();
FillData();
}
void FillData()
{
// 1.Open connection
using (SqlConnection conn = new SqlConnection(
Properties.Settings.Default.DataConnectionString))
{
conn.Open();
// 2.Create new DataAdapter
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM EmployeeTable", conn))
{
// 3.Use DataAdapter to fill DataTable
DataTable table = new DataTable();
adapter.Fill(table);
// 4.Render data onto the screen
// dataGridViewExample.DataSource = table;
}
}
}
}
}
The purpose of the DataAdapter is embedded in its name: It performs the activities necessary to get the data from the data source on the server into the database that's held in the DataSet. To do that, the DataAdapter lets us specify the commands that should be carried out to retrieve and update data.
In ADO, if we used a client-side Recordset, our data was disconnected from the data source. This had lots of advantages, not the least of which was that we could close our application's connection to the data source and still work with the data. This would free up the connection so that it could be used by other applications, improving our application's scalability.
Each .NET Framework data provider included with the .NET Framework has a DataAdapter object: the .NET Framework Data Provider for OLE DB includes an OleDbDataAdapter object, the .NET Framework Data Provider for SQL Server includes a SqlDataAdapter object, and the .NET Framework Data Provider for ODBC includes an OdbcDataAdapter object. A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source and Command objects to retrieve data from and resolve changes to the data source.
The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the DataSet. These properties are covered in more detail in Updating the Database with a DataAdapter and the DataSet.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Page_Load(object sender, System.EventArgs e) {
if (!Page.IsPostBack) {
SqlConnection MySqlConnection;
SqlCommand MySqlCommand;
SqlDataAdapter MySqlDataAdapter;
DataTable MyDataTable;
MySqlConnection = new SqlConnection();
MySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["MyAppConnectionString"].ConnectionString;
MySqlCommand = new SqlCommand();
MySqlCommand.CommandText = "SELECT TOP 50 * FROM < Tbl Employee >";
MySqlCommand.CommandType = CommandType.Text;
MySqlCommand.Connection = MySqlConnection;
MyDataTable = new DataTable();
MySqlDataAdapter = new SqlDataAdapter();
MySqlDataAdapter.SelectCommand = MySqlCommand;
MySqlDataAdapter.Fill(MyDataTable);
GridViewData.DataSource = MyDataTable.DefaultView;
GridViewData.DataBind();
MySqlDataAdapter.Dispose();
MySqlCommand.Dispose();
MySqlConnection.Dispose();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>DataAdapter Example: How to use DataAdapter in ASP.Net C#</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridViewData" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>
ADO.NET includes many objects you can use to work with data. This section introduces some of the primary objects you will use. Over the course of this tutorial, you'll be exposed to many more ADO.NET objects from the perspective of how they are used in a particular lesson. The objects below are the ones you must know. Learning about them will give you an idea of the types of things you can do with data when using ADO.NET.
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApplicationExample
{
public partial class FormExample : Form
{
public FormExample()
{
InitializeComponent();
FillData();
}
void FillData()
{
// 1.Open connection
using (SqlConnection conn = new SqlConnection(
Properties.Settings.Default.DataConnectionString))
{
conn.Open();
// 2.Create new DataAdapter
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM EmployeeTable", conn))
{
// 3.Use DataAdapter to fill DataTable
DataTable table = new DataTable();
adapter.Fill(table);
// 4.Render data onto the screen
// dataGridViewExample.DataSource = table;
}
}
}
}
}
The purpose of the DataAdapter is embedded in its name: It performs the activities necessary to get the data from the data source on the server into the database that's held in the DataSet. To do that, the DataAdapter lets us specify the commands that should be carried out to retrieve and update data.
In ADO, if we used a client-side Recordset, our data was disconnected from the data source. This had lots of advantages, not the least of which was that we could close our application's connection to the data source and still work with the data. This would free up the connection so that it could be used by other applications, improving our application's scalability.
