DataAdapter Object in ADO.net:

Octomber 11, 2011

DataAdapter is an integral part of the ADO.NET Data Provider. DataAdapter provides the means of communication between the Dataset and the Datasource. We can use the DataAdapter in combination with the DataSet Object. DataAdapter object is used in dis-connected architecture as it automatically open yhe connection and closes after data operation is completed.
DataAdapter provides this combination by mapping Fill method, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet.
The DataAdapter can be used to perform insert, update and Delete operations in the datasource.

SqlDataAdapter da = new SqlDataAdapter(); da.Fill(ds);
The SqlDataAdapter Object and DataSet objects are combine to perform both data access and data manipulation operations in the SQL Server Database.

Different DataAdapter in ADO.net:
  • System.Data.SqlClient.SqlDataAdapter
  • System.Data.OleDb.OleDbDataAdapter
  • Oracle.OracleClient.OracleDataAdapter

DataAdapter Properties:

DeleteCommand:

Represents a DELETE statement or stored procedure for deleting records from the data source da.DeleteCommand.CommandText ="Delete from table_name where Condition";

InsertCommand:

Represents a INSERT statement or stored procedure for inserting records from the data source. da.InsertCommand.CommandText ="Insert into table_name values(values1,Values2);

SelectCommand:

Represents a SELECT statement or stored procedure for selecting records from the data source da.SelectCommand.CommandText ="select * from table_name";

Update Command:

Represents a UPDATE statement or stored procedure for updating records from the data source da.UpdateCommand.CommandText ="update table_name set column_name= value where Condition";

TableMappings:

Represents a collection of mappings between actual data source table and a DataTable object


DataAdapter Methods:

Fill:

Fill method is used to fill data into Dataset or Datatable using DataAdapter.

Update:

Update method updates data to the datasource.

FillSchema:

Update method adds a DataTable to a DataSet.

Sample Code (SQL):

SqlConnection conn = new SqlConnection(""); SqlCommand com = new SqlCommand("select * from ", conn); SqlDataAdapter da = new SqlDataAdapter(com); DataSet ds=new DataSet(); da.Fill(ds);