[ADO.NET] Disconnected Classes

When you are working with “DbDataReader”, you are connected with a database and working directly with it. In some cases, it might not be a good idea. Another approach can be (1) load data from DB, (2) working with data for a while, (3) commit all changes back to DB at one time. ADO.NET provides another set of classes for the disconnected approach.  

 

1. Disconnected Classes

Why disconnected classes?

  • Do not need to keep the connection open while you’re doing time-consuming tasks
  • It is easy to bind in-memory data with ASP.NET controls such as a GridView
  • You can navigate freely back and forth through your data
  • You can write the data to a file (xml) easily
  • You can cache the data.

 

2.  DataTable

The “DataTable” object contains “DataColumn” objects, which define the schema, and “DataRow” objects, which contain the data.

– Properties –

  • Columns: a collection of  “DataColumn” objects
  • Rows: a collection of  “DataRow” objects
  • PrimaryKey: an array of columns(DataColumn[]) that function as primary keys

– Methods –

  • Select(string condition): returns an array of matched “DataRow” objects
  • ReadXml(): reads XML schema and data from the “DataTable”
  • ReadXmlSchema(): reads an XML schema from the “DataTable”
  • WriteXml(): writes the current contents of the “DataTable” as XML
  • WriteXmlSchema(Stream): writes the current data structure of the DataTable as an XML schema

 

3. DataColumn

The “DataColumn” specifies the column constraints.

– Properties –

  • ColumnName: the name of the column
  • Caption: a descriptive or friendly name
  • DataType: represents the column data type
  • MaxLength: the maximum length of a text column
  • Unique: indicates whether the values in each row of the column must be unique
  • AllowDBNull: indicates whether null values are allowed

The Primary Key Columns are defined by the “PrimaryKey” property of the “DataTable” object.

 

4. DataRow

The “DataRow” represents a row of data.

– Properties –

  • RowState: indicates whether the DataRow should be inserted, updated, or deleted from the data source

The “RowState” is quite important when you save the data back to the data storage. The value of the “RowState” depends on two factors: (1) the kind of operation has been performed on the row, and (2) whether “AcceptChanges()” has been called on the DataRow.

The possible “DataRowState” enum values are:

  • Detached: created but not added to the DataTable
  • Added: added to the DataTable
  • Unchanged: has not been changed since the “AcceptChanges()” was last called
  • Modified: has been modified since the “AcceptChanges()” was last called
  • Deleted: deleted using the “Delete()” method

– Methods –

  • AcceptChanges(): commits all the changes made to this row since the last time “AcceptChanges” was called
  • BeginEdit(): starts an edit operation
  • CancelEdit(): cancels the current edit on the row
  • EndEdit(): ends the edit occurring on the row

 

5. Accessing Items in the DataRow

You can use the indexer property to access an item. There are 3 ways you can acsess the item:

  • row[DataColumn]: using the “DataColumn” object
  • row[int]: using the index
  • row[string]: using the column name

In addition to these,  you can pass one of “DataRowVersion” enum values as an additional arguement. In fact, the “DataRow” object can contain up to 3 copies(Original, Current, and Proposed) of its data for rollback, or conflict resolution.

  • Original: the row contains its original values
  • Current: the row contains current values
  • Proposed: the row contains a proposed value
  • Default: For a “DataRowState” value of “Added”, “Modified” or “Deleted”, the default version is “Current”. For a DataRowState value of “Detached”, the version is “Proposed”.

Here is an example. Suppose you only have a “Current” version. When you call the “BeginEdit()” method and start to edit some values, the updated verison is saved to “Proposed” version. (2 version exist: Current + Proposed) When you call the “EndEdit()” the “Current” version becomes the “Original” version and the “Proposed” version is now the “Current” version.

 

6. DataSet

The “DataSet” is the in-memory relational-data representation. It is a collection of “DataTable” objects and their relationships.

– Properties –

  • Tables: a collection of “DataTable” objects
  • Relations: a collection of “DataRelation” objects

– Methods –

  • ReadXml()
  • WriteXml()
  • Clear()
  • Clone(): copies the structure of the “DataSet“, including all DataTable schemas, relations, and constraints. Does not copy any data
  • Copy(): copies both the structure and data
  • Merge()

 

7. DataRelation

The “DataRelation” represents a parent/child relationship between two “DataTable” objects.

– Properties –

  • RelationName: the name used to retrieve a DataRelation from a collection
  • ChildTable: Gets the child “DataTable” object
  • ParentTable: Gets the parent “DataTable” object

 

8. DataView

The “DataView” represents a customized view of a “DataTable” for sorting, filtering, searching, and navigation.

– Properties –

  • Sort: sort column or columns, and sort order
  • RowFilter: the expression used to filter which rows are viewed

 

9. DataAdapter

The “DbDataAdapter” reads data from DB and fill the table in a DataSet.

– Methods –

  • Fill(): SelectCommand
  • Update(): InsertCommand, UpdateCommand, or DeleteCommand

 

10. Example

– Web.config –

<connectionStrings>
<add name="AdventureWorks"
  connectionString="Data Source=localhostSQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True"
  providerName="System.Data.SqlClient" />
</connectionStrings>
<div>
 Product Category:
 <asp:DropDownList ID="categoryDDL" runat="server" AutoPostBack="True"
 onselectedindexchanged="categoryDDL_SelectedIndexChanged">
 </asp:DropDownList>
 <br /><br />
 Product Sub Categories:
 <asp:BulletedList ID="subCategiryList" runat="server"
 EnableViewState="False">
 </asp:BulletedList>
</div>

 

private string conStr =
    WebConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;

private const string CATEGORY_DB = "ProductCategory";
private const string SUBCATEGORY_DB = "ProductSubCategory";

private DataSet productDBSet = new DataSet();

protected void Page_Load(object sender, EventArgs e)
{
    FillDataSet();
    if (!IsPostBack)
    {
        FillCategories();
        PopulateSubCategories();
    }
}

protected void categoryDDL_SelectedIndexChanged(object sender, EventArgs e)
{
    PopulateSubCategories();
}

private void FillDataSet()
{
    using (SqlConnection con = new SqlConnection(conStr))
    {
        string sqlCmd1 = "SELECT ProductCategoryID, Name FROM Production.ProductCategory";
        string sqlCmd2 = "SELECT Name, ProductCategoryID FROM Production.ProductSubCategory";

        using (SqlCommand cmd = new SqlCommand(sqlCmd1, con))
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                adapter.Fill(productDBSet, CATEGORY_DB);

                cmd.CommandText = sqlCmd2;
                adapter.Fill(productDBSet, SUBCATEGORY_DB);
            }
        }
    }

    // Defining Relations
    DataRelation categorySubCategory
        = new DataRelation("Cat_SubCat",
          productDBSet.Tables[CATEGORY_DB].Columns["ProductCategoryID"],
          productDBSet.Tables[SUBCATEGORY_DB].Columns["ProductCategoryID"]);
          productDBSet.Relations.Add(categorySubCategory);
}

private void FillCategories()
{
    categoryDDL.Items.Clear();

    foreach (DataRow row in productDBSet.Tables[CATEGORY_DB].Rows)
    {
        ListItem item = new ListItem();
        item.Text = row["Name"].ToString();
        item.Value = row["ProductCategoryID"].ToString();
        categoryDDL.Items.Add(item);
    }

    categoryDDL.SelectedIndex = 0;
}

private void PopulateSubCategories()
{
    subCategiryList.Items.Clear();

    int selectedCategoryID = Int32.Parse(categoryDDL.SelectedValue);
    foreach (DataRow row in productDBSet.Tables[CATEGORY_DB].Rows)
    {
        int categoryId = Int32.Parse(row["ProductCategoryID"].ToString());
        if (categoryId == selectedCategoryID)
        {
            foreach (DataRow chileRow in row.GetChildRows("Cat_SubCat"))
            {
                ListItem item = new ListItem();
                item.Text = chileRow["Name"].ToString();
                item.Value = chileRow["Name"].ToString();
                subCategiryList.Items.Add(item);
            }
        }
    }
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s