Saturday, 10 September 2016

Chapter-5 New Conecpts in Datasets and Datatables



An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. The DataTableCollection contains all the DataTable objects in aDataSet. A DataTable is defined in the System.Data namespace and represents a single table of memory-resident data.
 
 Working in a disconnected environment
1)      In a disconnected environment, data is stored in datasets and manipulations are performed in the datasets.
2)      After the data has been manipulated in the dataset, the changes are updated to the database.
3)      A dataset is a disconnected, cached set of records that are retrieved from a database.
4)      The dataset acts like a virtual database containing tables, rows, and columns.
5)      The two main types of datasets are:
a)      Typed dataset
b)      Untyped dataset

Let us discuss each of these types in detail.

Typed Dataset:
i)        A typed DataSet is a class that derives from a DataSet. As such, it inherits all the methods, events, and properties of a DataSet.
ii)       A typed dataset is derived from the DataSet class and has an associated XML schema, which is created at the time of creation of the dataset.
iii)     The XML schema contains information about the dataset structure such as the tables, columns, and rows.
iv)     When a typed dataset is created, the data commands are generated automatically by using the column names from the data source.
v)      The XML Schema Definition (XSD) language is used to define the elements and attributes of XML documents.
vi)      Typed DataSet provides access to values as the correct type at compile time. With a strongly typed DataSet, type mismatch errors are caught when the code is compiled rather than at run time.
vii)   The structure of a typed dataset is decided at the time of its creation.

Untyped Dataset:
i)        An untyped dataset does not have any associated XML schema.
ii)       An UnTyped DataSet is an instance of class System.Data.DataSet. 
iii)     In an untyped dataset, the tables and columns are represented as collections.
iv)     It is binded with the tables at runtime and there no corresponding built-in schema. 
v)      Because an XML schema is not created for an untyped dataset, the structure of an untyped dataset is not known during compilation.
vi)     Untyped datasets find their use in cases where the structure of the schema is not decided during compilation or the data being used does not have a definite structure.
vii)   You are not aware of the schema of the dataset at design time and there is no error checking facility at the design time as they are filled at run time when the code executes.

A dataset has its own object model, as shown in the following figure. 


Demo Simple DataSet

//Create DataAdapter
SqlDataAdapter daEmp = new SqlDataAdapter("SELECT empno,empname,empaddress FROM EMPLOYEE",conn);
//Create a DataSet Object
DataSet dsEmp = new DataSet();
//Fill the DataSet
daEmp.Fill(dsEmp,"EMPLOYEE");
//Let us print first row and first column of the table
Console.Write(dsEmp.Tables["EMPLOYEE"].Rows[0][0].ToString());
//Assign a value to the first column
dsEmp.Tables["EMPLOYEE"].Rows[0][0] = "12345";//This will generate runtime error as empno column is integer

Demo Typed DataSet:

//Create DataAdapter
SqlDataAdapter daEmp = new SqlDataAdapter("SELECT empno,empname,empaddress FROM EMPLOYEE",conn);
//Create a DataSet Object
EmployeeDS dsEmp = new EmployeeDS ();
//Fill the DataSet
daEmp.Fill(dsEmp,"EMPLOYEE");
//Let us print first row and first column of the table
Console.Write(dsEmp.EMPLOYEE[0].empno.ToString());
//Assign a value to the first column
dsEmp.EMPLOYEE[0].empno = "12345";//This will generate compile time error.

6)      A dataset is created with the help of a DataSet object.
7)      The DataSet object is present in a DataSet class and is defined in the System.Data namespace. 
8)      The DataSet object contains a collection of DataTable objects, each containing one or more tables.
9)      A DataTable object contains one or more columns, each represented by a DataColumn object.
10)   To add a column to a DataTable, create a DataColumn object and call the Add() method on the Columns collection, which enables you to access the columns in a datatable.
11)   The following list describes some parameters that can be specified for a column:
a)      The name of the column
b)      The data type of the column
c)       Whether the column is read only
d)      Whether the column permits null values
e)      Whether the value of the column must be different in each row
f)       Whether the column is an auto-increment column
g)      Whether the column is an expression column
12)   Consider the following code snippet used for adding columns in a DataTable: 

DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add();
dt.Columns.Add(“Store Id”, typeof(string));
dt.Columns.Add(“Store Name”, typeof(string));
dt.Columns.Add(“Address”, typeof(string));



A DataSet contains relationships in its DataRelationCollection object. In ADO.NET, you can navigate through multiple tables to validate and summarize the data by using the DataRelation object.

1)      By using primary key and foreign key constraints that use a DataRelation object, you can create the relationship between multiple tables.
2)      A relationship, represented by the DataRelation object, associates rows in oneDataTable with rows in another DataTable.
3)      The primary key is a unique index and ensures the uniqueness of data stored in that table for that particular row.
4)      A relationship is analogous to a join path that might exist between primary and foreign key columns in a relational database. A DataRelation identifies matching columns in two tables of a DataSet.
5)      The foreign key is a constraint on a table that can reference one or more columns in that table.
6)      The table that has a primary key constraint is known as the Parent table, and the table that has a foreign key constraint is known as the Child table.
7)      A DataSet.Relations property gets the collection of relations that link tables and allow navigation from Parent tables to Child tables.

Ado.Net Data Rule
1) The Rule enumeration indicates the action that occurs when a foreign key constraint is enforced.
2) The various Rule enumeration values are:   
     a) Cascade : Deletes or updates the child DataRow object when the parent DataRow object is   deleted or its unique key is changed
     b) None : Throws an exception if the parent DataRow object is deleted or its unique key is changed
     c)  SetDefault : Sets the foreign key column(s) value to the default value of the DataColumn object(s), if the parent DataRow object is deleted or its unique key is changed
     d) SetNull : Sets the foreign key column(s) value to DbNull, if the parent DataRow object is deleted or its unique key is changed
3) Sometimes, the data available in one DataSet can be merged with another DataSet. Or, a copy of the DataTable objects can be created so that the user can edit or modify data, which can then be merged back to the original dataset.
4) The Merge() method is used to combine data from multiple DataSet, DataTable, and DataRow objects.
5) During merging of data within datasets, the MissingSchemaAction enumeration specifies the action to be taken when the data added to the dataset and the required DataTable or DataColumn is missing.
6) The various values of MissingSchemaAction enumeration are:
     a) Add : Adds the DataTable and DataColumn objects to complete the schema
     b) AddWithPrimaryKey : Adds the DataTable, DataColumn, and PrimaryKey objects to complete the schema
     c) Error : Throws an exception if the DataColumn does not exist in the DataSet that is being updated
    d) Ignore : Ignores data that resides in DataColumns that are not in the DataSet being updated

Working with Dataviews
 1) A dataview provides a dynamic view of data stored in a datatable.
2) If any data is modified in the datatable, the dataview associated with the datatable will also show the modified data.
3) DataView has capability that is often used in data-binding applications. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression.
4) A DataView object creates a fixed customized view of a given DataTable object.
5) You can create a DataView object to display the data based on a criterion and another DataView object to display the data based on a different criterion.
6) A DataView enables you to create different views of the data stored in a DataTable.
7) A dataview provides a sorted or filtered view of data in a datatable.
8) Sorting in a DataTable by using a DataView object is done by using the Sort property.
9) Filtering a DataTable by using the DataView object is done by using the RowFilter and RowStateFilter properties.

The following figure shows the object model of a dataview.

The following figure shows how customized data is displayed through dataviews.


Demo Using Parameterized Disconnected architecture using DataSet

using System;
using System.Data;
using System.Configuration;
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 _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string name = TextBox1.Text;
        SqlConnection con = new SqlConnection("server=localhost;database=employee;integrated security=true;");

        SqlCommand cmd = new SqlCommand("select roll ,name from student where name='" + TextBox1.Text + "'", con);

        SqlDataAdapter apt = new SqlDataAdapter();

        apt.SelectCommand = cmd;
        DataSet ds = new DataSet("t1");
        apt.Fill(ds);

        SqlParameter sql = new SqlParameter("@name", SqlDbType.Char, 90);
        sql.Value = name;
       
        //or
        //same
        //cmd.Parameters.Add(new SqlParameter("@name",name ));

        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();

    }
}

Summary:

i)        A dataset, which is a part of disconnected environment, is a disconnected, cached set of records that are retrieved from a database.
ii)       The two main types of datasets are:
iii)     Typed datasets
iv)     Untyped datasets
v)      A DataTable object contains one or more columns, each represented by a DataColumn object.
vi)     A DataSet contains relationships in its DataRelationCollection object. In ADO.NET, you can navigate through multiple tables to validate and summarize the data by using the DataRelation object.
vii)   A typed dataset is derived from the DataSet class and has an associated XML schema, which is created at the time of creation of a dataset.
viii)  An untyped dataset does not have any associated XML schema. As a result, the structure of an untyped dataset is not known during compilation.
ix)     The DataSet object contains a collection of DataTable objects, each containing one or more tables.
x)      A DataTable object also has a Rows collection, which allows rows to be accessed in a dataset. A DataTable object contains one or more rows, each represented by a DataRow object.
xi)     The DataRelation object is used to navigate through multiple tables to validate and summarize the data.
xii)   The primary key and foreign key constraints in a DataRelation object create the relationship between the tables in a schema.

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