How to use DataAdapter in asp.net c#

DataAdapter
DataAdapter represents a set of SQL commands and a Database connection that are used to fill the DataSet and update the data source. .NET developers can initialize a new instance of DataAdapter class using this two constructors DataAdapter() and DataAdapter(DataAdapter). DataAdapter(DataAdapter) constructor initialize a new instance of a DataAdapter class from an existing same type object.

DataAdapter class have many methods such as CloneInternals, CreateTableMappings, Dispose, Dispose(Boolean), Equals(Object), Fill(DataSet), Fill(DataTable, IDataReader), Fill(DataTable[], IDataReader, Int32, Int32), FillSchema(DetaSet, SchemaType), Finalize, GetFillParameters, GetHashCode, GetService, GetType, HasTableMappings, OnFillError, ResetFillLoadOption, ToString, Update etc.

DataAdapter Fill method add or refresh rows in the DataSet to match those in the data source. Fill(DataTable, IDataReader) uses DataTable name and specified IDataReader, Fill(DataTable[], IDataReader, Int32, Int32) overloaded method add or refresh rows in a specified range in the collection of DataTable objects to match those in the data source. Fill(DataSet, String, IDataReader, Int32, Int32) overloaded method uses DataSet and DataTable names.

SqlDataAdapter
If we want to connect a SQL Server Database, we can increase overall performance by using the SqlDataAdapter along with its associated SqlCommand and SqlConnection objects.

SqlDataAdapter represent a set of data commands and a Database connection that are used to fill the DataSet and update a SQL Server Database. We can initialize a new instance of SqlDataAdapter class using those constructors SqlDataAdapter(), SqlDataAdapter(SqlCommand), SqlDataAdapter(String, SqlConnection), and SqlDataAdapter(String, String). SqlDataAdapter(SqlCommand) overloaded method require specified SqlCommand as the SelectCommand property. SqlDataAdapter(String, SqlConnection) overloaded method require a SelectCommand and a SqlConnection object. SqlDataAdapter(String, String) method require a SelectCommand and a connection string.

SqlDataAdapter class main properties are AcceptChangesDuringFill, ContinueUpdateOnError, DeleteCommand, FillLoadOption, InsertCommand, SelectCommand, TableMappings, UpdateCommand etc.

SqlDataAdapter SelectCommand property get or set a Transact SQL Statement or stored procedure used to select records. UpdateCommand property used to update records in the data source. InsertCommand used to insert new records in the data source. DeleteCommand delete records from the DataSet.

Following methods are important in SqlDataAdapter class Dispose(), Fill(DataSet), Fill(DataTable), Fill(DataSet, String), Fill(Int32, Int32, DataTable[]), FillSchema(DataSet, SchemaType), Update(DataRow[]), Update(DataSet), Update(DataTable), Update(DataSet, String) etc.

SqlDataAdapter Fill(DataSet) method add or refresh rows in the DataSet. Fill(DataTable) overloaded method add or refresh rows in a specified range in the DataSet to match those in the data source using DataTable name. Update(DataTable) method update the values in the database by executing INSERT, UPDATE or DELETE statement in the specified DataTable.

The following c# ADO.NET example source code describe you more about SqlDataAdapter.
DataAdapterExample.aspx

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>

<!DOCTYPE html>

<script runat="server">
    protected void Page_Load(object sender, System.EventArgs e) {
        if (!Page.IsPostBack) {
            SqlConnection MyConnection;
            SqlCommand MyCommand;
            SqlDataAdapter MyAdapter;
            DataTable MyTable;

            MyConnection = new SqlConnection();
            MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString;

            MyCommand = new SqlCommand();
            MyCommand.CommandText = "SELECT TOP 8 * FROM PRODUCTS";
            MyCommand.CommandType = CommandType.Text;
            MyCommand.Connection = MyConnection;

            MyTable = new DataTable();
            MyAdapter = new SqlDataAdapter();
            MyAdapter.SelectCommand = MyCommand;
            MyAdapter.Fill(MyTable);

            GridView1.DataSource = MyTable.DefaultView;
            GridView1.DataBind();

            MyAdapter.Dispose();
            MyCommand.Dispose();
            MyConnection.Dispose();
            
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>DataAdapter example: how to use DataAdapter in asp.net</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    </form>
</body>
</html>
More c# examples