1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Working with disconnected environment in ADO.NET

Discussion in 'ASP.NET' started by MinalS, Feb 3, 2015.

  1. MinalS

    MinalS New Member

    Jul 8, 2014
    Likes Received:
    Trophy Points:
    To overcome this problem ADO.NET contains a new component as DataSet. The DataSet is the used for retrieving data in read only, forward only format. Once the data is manipulated in the database the changes are updated to the database.

    DataSet in ADO.NET

    A dataset can be defined as memory – based relational representation of data. It is defined as the part of the disconnected environment. A dataset caches the set of records retrieved from the database. It looks like a virtual database that contains tables, rows and columns.

    Dataset Object Model

    The dataset object model is as shown below:


    In the above shown figure, the dataset contains collection of DataTable and DataRelation objects. The DataTable contains collection of rows for representing the data. The DataRelation object contains the relationship between rows and navigation. The DataTable object contains unique or foreign key constraints for data integrity.

    Some of the properties of the DataSet class are as mentioned below:
    1. CaseSensitive: The string comparisons within the data tables are case sensitive.
    2. DataSetName: It gets or sets the current name of the data set.
    3. DesignMode: It shows that the component is in the design mode.
    4. Events: It provides the list of event handlers that are associated with the component.
    5. Locale: It provides the locale information for comparing strings in the table
    6. Namespace: It gets or sets the namespace of the DataSet
    Some of the methods supported by the DataSet class are as mentioned below:
    1. AcceptChanges: It accepts all the changes done to the DataSet when it was loaded.
    2. Clear: It clears all the data in the dataset
    3. Clone: It copies the structure of the Dataset but not the data
    4. EndInit: It ends the initialization of the dataset
    5. GetChanges: It returns the copy of the Dataset with the changes done since it was loaded.
    6. GetXML: It returns the XML representation of the data.
    There are two types of dataset in ADO.NET as mentioned below:
    1. Typed Dataset
    2. UnTyped Dataset
    1. Typed Dataset

    The DataSet class contains the typed dataset. The XML schema is associated with the typed dataset. The schema contains information about data structures as tables, columns, and rows. The data is transferred to and from the dataset in XML format.

    The XML Schema Definition ( XSD ) is used for defining the elements and attributes of the XML documents. The dataset is stored in XSD file. The structure of the typed dataset is defined at the time of creation. The data commands are generated through the use of the column names from the data source.

    Consider the example to demonstrate the data tables as OrderData and ProductData. The dataset class contains the data tables as OrderDataTable and ProductDataTable.

    It contains the columns as DataColumn fields. The different columns are represented in the datatable. A typed dataset class contains nested classes for representing rows in the datatables.

    Consider the following code for accessing the column value of the StudInfo table in the dataset.
    string studid;
    studid = DSStud.StudInfo[0].StudID;
    In the above code, the first row of the table is the typed dataset. The value in the StudID column is saved in the studid. The intellisense and auto complete support is provided for the typed dataset.

    2. UnTyped Dataset

    The UnTyped dataset is not associated with any XML schema. The tables and columns are represented as collections. The XML schema is not created on the untyped dataset, the structure is unknown for compilation.

    Consider the following code for accessing the rows of the StudInfo table in the dataset.
    string studid;
    OleDbDAStud.Fill ( DSStud );
    studid = ( string ) ( DSStud.tables[ "StudInfo" ].Rows[0].Item["StudID"] );
    In the above code, it is difficult to read the code as compared to the typed dataset. The intellisense and auto complete support is not provided for the untyped dataset.

    Consider the following code to demonstrate the use of dataset for managing data.
    1. Add the DataGridView control on the windows form application.
    2. Add the following code in the load method of the form.
      private void Form1_Load ( object sender, EventArgs e )
          SqlConnection con = new SqlConnection( "Data Source=.\SQLEXPRESS; Intergrated Security=true; Initial Catalog = Student");
          SqlDataAdapter da = new SqlDataAdapter ("select * from student", con);
          DataSet ds = new DataSet();
          dataGridView1.DataSource = ds.Tables["Student"];
    3. The output generated from the above code is as shown below:

    DataSet Implementation

    The DataSet object is used for the creation of the Dataset. The in-memory cache of data is provided. The DataSet class contains the object. It is defined in the System.Data namespace. The object contains a collection of DataTable objects that contain one or more tables.

    The DataTable object consists of one or more columns. The DataColumn object is created for adding the DataColumns to the table. The Columns collection helps user to access the columns in the datatable.

    Some of the properties of the DataTable object are as mentioned below:
    1. Columns: It returns the collection of the columns
    2. DataSet: It returns the DataSet to the user.
    3. DefaultView: It returns the view of the table
    4. Rows: It returns the rows collection
    5. ParentRelations: It returns the ParentRelations collection
    The methods associated with the DataTable are as mentioned below:
    1. AcceptChanges: It commits the changes since the last AcceptChanges was executed.
    2. GetErrors: It returns the array of rows with errors.
    3. ImportRows: It copies the new row into the table.
    4. Merge: It merges the table with another DataTable
    5. Reset: It resets the table to the original state
    6. Select: It returns the array of the DataRow objects
    The DataTable contains the Rows collection that helps the rows to be accesses in the dataset. One or more rows in the DataTable object are represented using the DataRow object.

    The properties associated with the DataRow object are as mentioned below:
    1. HasErrors: It indicates if there are any errors present.
    2. Items: It gets or sets the data from the specific column
    3. Table: It returns the parent table to the user.
    The methods associated with the DataRow object are as mentioned below:
    1. Add: It appends the rows at the end of the table
    2. Find: It accesses the row in a table through the primary key
    3. Select: It finds the rows that satisfy the specific condition
    4. Remove: It removes the DataRow object
    5. Delete: The rows are provisionally removed from the table
    Consider an example of student table. User adds the columns in the DataTable object.
    1. Add the GridView control to the Windows Appcation.
    2. In the load event of the form, add the following code.
    private void Form1_Load( object sender, EventArgs e )
        DataTable dt = new DataTable();
        DataColumn dc1 = new DataColumn ( "studid", typeof(Int32) );
        DataColumn dc2 = new DataColumn ("studname", typeof(string) );
        DataColumn dc3 = new DataColumn ( "marks", typeof(Int32) );
        dt.Rows.Add(111, "Sandesh", 40);
        dt.Rows.Add(112, "Mark", 76);
        dt.Rows.Add(113, "Kelvin", 90);
        DataSet ds = new DataSet ();
        dataGridView1.DataSource = ds.Tables[0];
    The output for the above code is as shown below:


    Relationship in a DataTable

    User can migrate through multiple tables for validating and summarizing the data. The DataRelation object is used for managing data. The primary and foreign key constraints can be used for creating the relationship. The primary key is defined as unique index and provides uniqueness of data stored in the data table. The foreign key provided reference to the one or more columns.

    The DataSet.Relations property gets the collection of relations linking the tables.

    The following code snippet demonstrates the addition of two tables, OrderData and ProductData into a single dataset.
    DataSet ds = new DataSet();
    DataTable dt = ds.Tables.Add( "OrderData" );
    dt.Columns.Add ( "OrderID", typeof(string) );
    dt.Columns.Add( "Quantity", typeof(string) );
    dt.Columns.Add( "Location", typeof(string) );
    DataTable dt1 = ds.Tables.Add( "ProductData" );
    dt1.Columns.Add("OrderID", typeof(string) );
    dt1.Columns.Add( "ProductName", typeof(string) );
    dt1.Columns.Add( "Cost", typeof(string));
    dt.PrimaryKey = new DataColumn [] { dt.Columns [ "OrderID" ] };
    ds.Relations.Add("OrderDetails", dt1.Columns[ "OrderID" ],
    dt.Columns["OrderID"] );
    In the above code, the OrderDetails is the name of the relation used to establish relationship in two tables in the dataset. The OrderID is the common attribute in the two tables.

    The Add() method contains the relation among the two tables in the dataset. One or more DataRelation objects can be used for retrieving the data.

    Consider the following code snippet to demonstrate the constraint and relationship among them.
    ForeignKeyConstraint fkc ; 
    DataColumns dcEmpID, dcInternalEmpID;
    dcEmpID = ds1.Tables["Employee"].Columns["cEmpID"];
    fkc = new ForeignKeyConstraint("Studentfk", dcStudID, dcInternalEmpID);
    fkc.DeleteRule = Rule.SetNull;
    fkc.UpdateRule = Rule.Cascade;
    In the above code, the Rule enumeration states that action is enforced when the foreign key constraint is enforced.

    There are several enumeration values that can be assigned. They are as listed below:
    1. None: It throws an exception if the Parent DataRow object is deleted or the unique key is changed.
    2. Cascade: It deletes or updates the child DataRow object when the Parent DataRow object is deleted.
    3. SetDefault: It sets the foreign key column value to the default value of the DataColumn object.
    4. SetNull: It sets the foreign key column value to DbNull if the parent DataRow object is deleted.

    Merging data with DataSets

    The data present in a DataSet can be merged with another dataset. The Merge() method is used for combining the data from different DataSet, DataTable or DataRow objects.

    Consider the following code snippet to demonstrate the merging of data from the datasets.
    SqlConnection con = new SqlConnection ("DataSource =SQLServer01; InitialCatalog=Stud;Userid=sa;Password=abcd1234");
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandText = "Select * from Student";
    SqlDataAdapter da= new SqlDataAdapter ();
    da.SelectCommand cmd;
    DataSet ds = new DataSet();
    cmd.CommandText = "Select * from Result");
    DataSet ds1 = new DataSet();
    da.Fill ( ds1, "Result" );
    ds.Merge(ds1, true, MissingSchemaAction.Add );
    foreach ( DataTable dt in ds.Tables )
        Console.WriteLine("\n The value in the table are " +dt + "DataTable" );
        foreach ( DataRow dr in dt.Rows )
            Console.WriteLine ( dc + " = " + dr [dc] );
    In the above code, the Merge() method accepts three parameters. The first parameter is the dataset whose data and schema will be merged. The second parameter contains the Boolean value which is true. It is useful for saving the changes. The third parameter is the MissingSchemaAction enumeration which states that the data is added to the dataset.

    The following list specifies the value of the MissingSchemaAction enumeration.
    1. Add: It adds the DataTable and DataColumn objects to complete the schema.
    2. AddWithPrimaryKey: It adds the DataTable, DataColumn, and PrimaryKey objects to the schema.
    3. Error: It throws the exception if the DataColumn does not exist in the DataSet.
    4. Ignore: It ignores the data that is present in the DataColumn not updated in the DataSet.

Share This Page