Friday, 9 September 2016

Chapter-4 Working with Data Adapters





Data Adapters

1)      Data adapters are an integral part of ADO.NET managed providers, which are the set of objects used to communicate between a data source and a DataSet
2)      Data adapters forms an interface between the application and the data source from retrieving and saving data.
3)      A data adapter handles data transfer between the database and the DataSet through its properties and methods, and displays data through the process of table mapping.
4)      A data adapter is integral to the working of ADO.NET because data is transferred to and from a database through a data adapter.
5)      A data adapter retrieves data from a database into a DataSet. 



6)      The data adapter first compares the data in the dataset with that in the database and then updates the database.
7)      Data from a database can be accessed by configuring a data adapter.
8)      Following are the data adapters that can be configured to connect to a database:
a)      SqlDataAdapter : Accesses data specifically from Microsoft SQL Server
b)      OleDbDataAdapter : Accesses data from a database that is supported by an OLE DB data provider. OleDbDataAdapter object represents a connection and a set of command objects that helps populat a Dataset object.
c)       OdbcDataAdapter : Accesses data from a database that is supported by an ODBC data provider
d)      OracleDataAdapter : Accesses data from a database that is supported by an Oracle data provider
9)      The following properties and methods of a data adapter can be used to perform various operations on a database:
a)      SelectCommand : Refers to a DML statement or a stored procedure to retrieve data from a database .You set the CommandText property of default Command object is created when you SelectCommand object. It doesnot return any rows , no table to Dataset and no exception is thrown.
b)      InsertCommand : Refers to a data command to insert data into a database .You set the CommandText property of default Command object is created when you insert command object. InsertCommand Property return an integer value that specifies the number of rows affected by corresponding  insert operation.
c)       UpdateCommand : Refers to a data command to update a database .You set the CommandText property of default Command object is created when you update command object.When you execute the UpdateCommand object, it returns the number of rows affected by update operation.
d)      DeleteCommand : Refers to a data command to delete data from a database.You set the CommandText property of default Command object is created when you delete command object.When you execute the DeleteCommand object, it returns the number of rows affected by update operation.
e)      Fill() : Fills the dataset with the records from a database
f)       Update() : Executes the corresponding Insert, Update, or Delete commands for each inserted, modified, or deleted row to reflect the changes in a database .Update method to update the database with changes from Dataset object, Update method is usually called from the Page_PreRender event procedure.

10)   After a dataset has been created, the data adapter uses the process of table mapping to map the columns in the database table with the dataset columns.
11)   A data adapter uses the TableMappings property, a collection of DatatableMapping objects that is used for mapping between the database table and the DataTable object in the dataset. 

Demo How to use Data Adapter in Asp.net

Steps: 1) Open website in Visual studio .

2) Drag Gridview , Button on web page.

3) Create a store procedure in Sql Server.

create proc pp1
as begin
select * from std
end

4) Write the below code on the button click event in Asp.net

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("server=abc;database=xyz2;Integrated Security=true;");

        SqlCommand cmd = new SqlCommand("pp1", con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter adt = new SqlDataAdapter();
        adt.SelectCommand = cmd;

        DataSet ds = new DataSet("std");
        adt.Fill(ds);

        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
   
    }
    protected void TextBox2_TextChanged(object sender, EventArgs e)
    {

    }
}

Understanding ADO.NET Datasets

1)      ADO.NET supports in memory , disconnected data architecture. You established a connection to data source , retrieve the records from DataSet and close it. We have discussthe advantage in previous section.
2)      An ADO.NET dataset is a collection of tables and information about the relationship between tables. Each table contain rows, column and constraint.
3)      Data Table Class : includes the Rows and Columns collection that represent rows and columns . DataTable contains the ChildRelations and ParentRelation collections that represent relationship between tables.
4)      DataRows Class: includes the RowState property .The RowState propert indicate whether a row has changed after loading the data table from database.
5)      A DataTable also contains the Constraints collection . The constraints collection allows dataset to implement all constraint at the database level.

UnKnown Points related to DataAdapter

1)      A major challenge related to data access is that more than one user might need to simultaneously access data in a database.
2)      Another challenge is more than one user might need to access the data anytime, anywhere. This challenge can be overcome by implementing database locking while a transaction is executing.
3)      However, if database locking is not implemented, it can lead to data concurrency conflicts that arise from multiple updates being performed on the database.

4)         Resolving data concurrency conflicts is a business decision, with the following choices:
i)        Prioritized on time; first update wins
ii)       Prioritized on time; last update wins
iii)     Prioritized on role
iv)     Prioritized on location
v)      User resolves the conflict

5)      A significant way to increase the performance of data updates is to update and send the changes to the database in batches. This is known as batch updates.
6)      Batch updates are performed by using the UpdateBatchSize property of the SqlDataAdapter object.
7)      By default, the UpdateBatchSize property is set to 1.
8)      One way to confirm that the changes are being sent to the database server in batches is to add a RowUpdated event to the SqlDataAdapter object. This event will show the number of rows affected in the last batch.
9)      ADO.NET provides support for classes that can create any provider-specific objects. These classes are known as the DbProviderFactories classes. 

static DataTable GetProviderFactoryClasses()
{
    // Retrieve the installed providers and factories.
    DataTable table = DbProviderFactories.GetFactoryClasses();

    // Display each row and column value.
    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn column in table.Columns)
        {
            Console.WriteLine(row[column]);
        }
    }
    return table;
}

10)   The DbProviderFactories class contains a method called GetFactoryClasses that returns a data table, which is populated with data from various providers, as shown in the following figure.






Summary:-


i)        A data adapter, which is a part of the connected environment, retrieves data from a database into a dataset. 

ii)       The data adapters that can be configured to connect to a database in Visual Studio .NET are:
(1)    SqlDataAdapter
(2)    OleDbDataAdapter
(3)    OdbcDataAdapter
(4)    OracleDataAdapter

iii)     Resolving data concurrency conflicts is a business decision, with the following choices:
(1)    Prioritized on time; first update wins
(2)    Prioritized on time; last update wins
(3)    Prioritized on role
(4)    Prioritized on location
(5)    User resolves the conflict

iv)     A significant way to increase the performance of data updates is to update and send the changes to the database in batches. This is known as batch updates.

v)      Data Table Class : includes the Rows and Columns collection that represent rows and columns . DataTable contains the ChildRelations and ParentRelation collections that represent relationship between tables.

vi)     DataRows Class: includes the RowState property .The RowState propert indicate whether a row has changed after loading the data table from database.

vii)   A DataTable also contains the Constraints collection . The constraints collection allows dataset to implement all constraint at the database level.

viii)  ADO.NET provides support for classes that can create any provider-specific objects, such as SqlClient, Odbc, OracleClient, and OleDb. These classes are known as DbProviderFactories classes.


I hope that the functionality of the above attribute is clear to you now. Now you can use this code in your application. If you have further question, just drop a line below and I will try to answer you as soon as possible.

Last but not least, connect with me on Twitter , Facebook , LinkedIn and Google+ for technical updates and articles news. We won’t spam or share your email address as we respect your privacy.




No comments:

Post a Comment