Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Managing Data with ADO.NET DataSets and C#

By Exforsys | on April 8, 2005 |
ASP.NET

This tutorial explains about The Role of DataSets in ADO.NET , Using DataSets in ASP.NET , Saving DataSets in Session State, Using DataTable Constraints, DataSet, DataRelations Using DataSets with DataAdapters to Modify Data and the Transactional Model in DataSets.

 

ASP.NET: Managing Data with ADO.NET DataSets and C#

 

Introduction

In this article we will explore the DataSet, DataTable and DataRelations classes. Those who have worked with classic asp will be familiar with record sets. Record set was the individual rows that were retrieved from the database and than binded to the screen using html table or any other html tags.

DataSet on the other hand is a new technology in Asp.net and this maps the entire database maps in its self. What I mean is it can have all the fields of the tables which we specify. In other words its a disconnected database. By disconnected I mean that if you make changes in Dataset those changes will not be reflected in the actual database.

The Role of DataSets in ADO.NET

The important of datasets can never be denied nor it can be questioned. DataSet is a mirror image of the table or the query which you run. DataSets makes it easier to edit and update the information. Another good thing is that datasets are disconnected in nature so, if you make any changes in the dataset it will not reflect in the database unless use special methods to perform the change and confirm it.

Using DataSets in ASP.NET

Let’s see how we can use datasets in Asp.net. The most common way of using the DataSet control is with the dataadapter. The DataAdapter fills the dataset control with data coming from the database. Let’s see how we can perform this simple action:

SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROm Categories",myConnection);

DataSet ds = new DataSet();

ad.Fill(ds,"Categories");

DataGrid1.DataSource = ds;

DataGrid1.DataBind();

Explanation of the code:

1. First we declare a simple dataadapter instance which fetches all the rows from the Categories table. ( Categories table is present in the Northwind database which is shipped with Sql Server 2000 ).

2. New we created an instance of the DataSet control.

3. Next we fill the dataset with the data coming from the dataadapter control. As you can see that I have written ad.Fill(ds,"Categories"); you can also write ad.Fill(ds); and this will work fine too. In the former case I was telling the dataadapter which table I am using in my query and later the dataadapter was finding the table itself.

4. Finally I used DataGrid to bind the data on the screen.

Iterating through the DataSet:

Here is a simple code to iterate through the dataset and select individual items instead of selecting all the data in the DataSet.

SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROm Categories",myConnection);

DataSet ds = new DataSet();

ad.Fill(ds,"Categories");

DataTable dt = ds.Tables["Categories"];


Response.Write(dt.Rows[0][1].ToString()) ;

1) First few lines are identical which we have done before.

2) In the forth line we declared the DataTable object. DataTable object is used to hold a single table. As I already told you that dataset can hold multiple tables depending upon the query so we can assign a single table from the dataset to the datatable object. I have done that using the line.

DataTable dt = ds.Tables["Categories"];

3) Next we are printing the first row of the second column. Always remember that the number of the rows and columns in the database does not start with 1 but it starts at 0. So in this line of code:

Response.Write(dt.Rows[0][1].ToString()) ;

This line of code means that get Row ‘0’ and Column 1 of the from the datatable. And so we get the value and it prints out "Beverages".

Saving dataSets in Session State:

DataSets can also be saved in the Session State so that it can be available in other areas of the application and in new pages. The way of storing the DataSet in Session State is very easy.

Session["MyDataSet"] = DataSet;

Later if you want to retrieve the DataSet from the Session State you can easily do this by using this line of code:

DataSet ds = (DataSet) Session["MyDataSet"]

As you can see that when I am retrieving the values from the Session object I am casting it into the dataset object. This is because this is explicit conversion and requires casting. I am unboxing in this case.


Using DataTable:

DataTable is also a collection which you can use like dataset. The difference is that DataTable represents only one table. Usually its used when you don’t have a database and want to save something in the collection.

Here is few lines of code that you can use to make a populate the datatable:

// Create a new DataTable.
System.Data.DataTable myDataTable = new DataTable("ParentTable");
// Declare variables for DataColumn and DataRow objects.
DataColumn myDataColumn;
DataRow myDataRow;

// Create new DataColumn, set DataType, ColumnName and add to DataTable.
myDataColumn = new DataColumn();
myDataColumn.DataType = System.Type.GetType("System.Int32");
myDataColumn.ColumnName = "id";
myDataColumn.ReadOnly = true;
myDataColumn.Unique = true;
// Add the Column to the DataColumnCollection.
myDataTable.Columns.Add(myDataColumn);

DataSet Relations:

In a relational representation of data, individual tables contain rows that are related to one another using a column or set of columns. In the ADO.NET DataSet, the relationship between tables is implemented using a DataRelation. When you create a DataRelation, the parent-child relationships of the columns are managed only through the relation. The tables and columns are separate entities. In the hierarchical representation of data that XML provides, the parent-child relationships are represented by parent elements that contain nested child elements. To facilitate the nesting of child objects when a DataSet is synchronized with an XmlDataDocument or written as XML data using WriteXml, the DataRelation exposes a Nested property. Setting the Nested property of a DataRelation to true causes the child rows of the relation to be nested within the parent column when written as XML data or synchronized with an XmlDataDocument. The Nested property of the DataRelation is false, by default. For example, consider the following DataSet:


SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
SqlDataAdapter orderDA = new SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate FROM Orders", nwindConn);

nwindConn.Open();

DataSet custDS = new DataSet("CustomerOrders");
custDA.Fill(custDS, "Customers");
orderDA.Fill(custDS, "Orders");

nwindConn.Close();

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustomerID"],
custDS.Tables["Orders"].Columns["CustomerID"]);
Because the Nested property of the DataRelation object is not set to true for this DataSet, the child objects will not be nested within the parent elements when this DataSet is represented as XML data.

The following code example shows the output that will result from calling WriteXml on the DataSet.



ALFKI
Alfreds Futterkiste


ANATR
Ana Trujillo Emparedados y helados


10643
ALFKI
1997-08-25T00:00:00


10692
ALFKI
1997-10-03T00:00:00


10308
ANATR
1996-09-18T00:00:00


Note that the Customers element and the Orders elements are shown as sibling elements. If you wanted to have the Orders elements show up as children of their respective parent elements, the Nested property of the DataRelation would need to be set to true and you would add the following:

The following code shows what the resulting output would look like, with the Orders elements nested within their respective parent elements.



ALFKI

10643
ALFKI
1997-08-25T00:00:00


10692
ALFKI
1997-10-03T00:00:00

Alfreds Futterkiste


ANATR

10308
ANATR
1996-09-18T00:00:00

Ana Trujillo Emparedados y helados

(www.msdn.microsoft.com)

Using DataSet with DataAdapters to Modify Data

We can also use DataAdapters to modify data in the database. There are several properties that are available to perform these operations.

Let’s see some of the properties:

ad.SelectCommand = new SqlCommand("SELECT * FROM Person",myConnection);

ad.UpdateCommand = new SqlCommand("UPDATE TABLE Person SET Name=’john’ WHERE PersonID = 1");

ad.DeleteCommand = new SqlCommand("DELETE TABLE Person WHERE PersonID = 1");

ad.InsertCommand = new SqlCommand("INSERT INTO PERSON VALUES(‘NewName’) ");

All the properties are self explanatory. SelectCommand property is used to select, Update command property is used to update and so forth.

When we issue the command we can just later fill the Dataset with the DataAdatper and later we can bind the dataset to datagrid to view the result on the user interface.

Download Project Files

« « Oracle 9i Packages
MSAS: Introduction and Working with Cubes » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 391 Followers
  • Popular
  • Recent
  • Securing ASP.NET Applications with C#

    May 14, 2005 - 0 Comment
  • ASP.NET Web Forms Controls

    February 26, 2005 - 0 Comment
  • ASP .NET: Validating User Input with C#

    March 4, 2005 - 0 Comment
  • Using Rich Server Controls with C#

    March 12, 2005 - 0 Comment
  • Accessing Data with C#

    March 19, 2005 - 0 Comment
  • ASP.NET Using the DataList and Repeater, Datagrid Controls

    March 26, 2005 - 0 Comment
  • Creating and consuming XML Web Services with C#

    April 14, 2005 - 0 Comment
  • ASP .NET Migration and Interoperability

    April 24, 2005 - 0 Comment
  • Managing State with ASP.NET and C#

    May 3, 2005 - 0 Comment
  • ASP.NET with C# Training Launch

    February 19, 2005 - 0 Comment
  • Securing ASP.NET Applications with C#

    May 14, 2005 - 0 Comment
  • Configuring and Deploying ASP.NET Applications

    May 14, 2005 - 0 Comment
  • Caching in ASP.NET

    May 9, 2005 - 0 Comment
  • Managing State with ASP.NET and C#

    May 3, 2005 - 0 Comment
  • ASP .NET Migration and Interoperability

    April 24, 2005 - 0 Comment
  • Creating and consuming XML Web Services with C#

    April 14, 2005 - 0 Comment
  • ASP.NET Using the DataList and Repeater, Datagrid Controls

    March 26, 2005 - 0 Comment
  • Accessing Data with C#

    March 19, 2005 - 0 Comment
  • Using Rich Server Controls with C#

    March 12, 2005 - 0 Comment
  • ASP .NET: Validating User Input with C#

    March 4, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Securing ASP.NET Applications with C#
  • Configuring and Deploying ASP.NET Applications
  • Caching in ASP.NET
  • Managing State with ASP.NET and C#
  • ASP .NET Migration and Interoperability

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT