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
 

Working with DataSets

By Exforsys | on July 14, 2005 |
VB.NET 2005

Access and Manipulate Data – Using DataSets

In this tutorial you will learn about Using DataSets, Populating a DataSet From a Database, Moving Around in DataSets and Retrieving Data, Using Strongly Typed DataSets, DataSets With Multiple Tables.

Populating a DataSet from a Database

As already discussed DataSets do not contain any data when they are created. The user must fill the data in to the DataSet separately. We have already seen that there are several methods of filling a DataSet with data. DataSets can be created using the Visual Studio Design in which case TableAdapters are also created.

Filling a DataSet using a TableAdapter

  1. Create a new project in Visual Basic IDE. 
  2. On the Database Explorer click the icon for creating new data connection and 
  3. Choose the SQL Server file.
  4. Establish the connection and you should be seeing the database objects on the window.
  5. In the solution Explorer click on the project name and
  6. Choose add an item option.
  7. In the dialog box that opens choose DataSet item and
  8. Name it as ds and click ok.
  9. You will see the DataSet item added to the solution and also
  10. A blank screen will be seen.
  11. From the Database explorer drag and drop the table ProductCategory.
  12. Choose ‘not’ in the message box that asks your permission to add the datafile as a project data.
  13. Now right click on the Form1 and
  14. Choose the option to see the code window.
  15. Type the following codes to fill the DataSet
  1. Dim ProductCategoryTableAdapter As New dsTableAdapters.ProductCategoryTableAdapter() 
  2. Dim ProductCategoryDataSet As New ds ProductCategoryTableAdapter.Fill(ProductCategoryDataSet.ProductCategory)

You can also populate a DataSet using a SqlDataAdapter or an OleDbDataAdapter. The method of doing this is same in both the cases. We shall now see how a DataSet is filled by using a SqlDataAdapter. You have to

  1. Create a SqlConnection object. SqlDatAdapter object.
  2. The SqlConnection object needs connection string as an argument and the SqlDataAdapter requires the SQL Statement and Connection Object as an argument.
  3. The ConnectString gives details about the Database Server, Initial Catalogue, connection type, userid and password.
  4. A typical connection string could look like this:
  1. data source=sql.domain.no; 
  2. initial catalog=xxxxx; 
  3. User ID=xxxxx;pwd=xxxxx; 
  4. Integrated Security=SSPI

Code for filling the DataSet is given below:

  1. Dim SQLStr As String 
  2. Dim ConStr As String 
  3. SQLStr = "SELECT Name FROM production.ProductCategory" 
  4. ConStr = "data source=sql.domain.no; initial catalog=xxxxx; User ID=xxxxx;pwd=xxxxx; Integrated Security=SSPI" 
  5. Dim sqlConn As New System.Data.SqlClient.SqlConnection(ConStr) 
  6. Dim ds As New DataSet 
  7. Dim SQLAdapter As New System.Data.SqlClient.SqlDataAdapter(SQLStr, ConStr) 
  8. SQLAdapter.Fill(ds)

New rows can be added manually to the data set as in the case of a data-entry. In this case the user has to first create a DataSet, a DataTabale, and a DataRow. Then he must populate the DataRow manually by supplying the value for each row and then add these rows to the DataSet. It should be remembered that there is no underlying data store that supplies data in this case. The code for the activity is given below:

  1. Dim dsNew As New DataSet 
  2. Dim t As New DataTable 
  3. Dim tr As DataRow = dsNew.Tables("T").NewRow 
  4. tr("Name") = "Aviation Gears" 
  5. dsNew.Tables("T").Rows.Add(tr)

You can also populate a DataSet by reading from an WML file. The code listing is given below:

  1. Dim dsXML As New DataSet() 
  2. dsXML.ReadXml("XmlFilePath and Name")

The user can also create a new DataSet and merge it with any existing DataSet. This opetaion is illustrated by the code given below:

  1. Dim dsXML As New DataSet() 
  2. Dim dsCopy As New DataSet 
  3. dsXML.ReadXml("XmlFilePath and Name") 
  4. dsCopy.Merge(dsXML, True, MissingSchemaAction.AddWithKey)

Moving Around in DataSet and Retrieving Data

In the above sections we have seen how to create a DataSet, how to populate it etc. In the following sections we shall see how to retrieve data from a DataSet. Remember, we stated that there is no current row in a DataSet? So any row can be accessed directly by just mentioning its position? Let us add to this the fact that a DataSet can contain as many tables as required and the user can also create objects that show the relationships that exist and the constraints that are imposed on them. Thus a DataSet can be a very complex data store. Let us understand the process of navigating the DataSet by the following example.

You can write code to go to the first record of the table, go one record forward or backward and also go to the last record. We shall use the BindingContext to do this. Look at the following code:

Click here to view sample code

Retrieving data from DataSets is easy. The data in the DataSet can be displayed in a grid and a script can be written to enable the user browse through the data. The sample code illustrates this.

  1. Imports system.Data 
  2. Public Class Form1 
  3. Dim ProductDataSet As ds 
  4. Private Sub DataLoad() 
  5. Dim ProductTableAdapter As New dsTableAdapters.ProductTableAdapter() 
  6. ProductDataSet = New ds ProductTableAdapter.Fill(ProductDataSet.Product) 
  7. End Sub

The code that is given below will make navigation within the data set.

Click here to view sample code

Using Strongly Typed DataSets

A strongly typed DataSet can ensure that the CLR and .NET framework know at design time the type of data that is to be stored in the location specified by the DataSet. Unless a strongly typed data classes are set up, the compiler needs to take extra time at runtime to determine where to place the incoming data and how to convert it into the destination data type. The performance overhead is increased substantially relative to the strongly typed DataSets

Strongly typed DataSets can be created in two ways. In the menu click the Data MainMenu and choose the submenu Add New Data Source …. And click on this. The Data Source Configuration Wizard opens. In the Wizard choose Database and click next.

In the second screen choose the data file (*.mdf) by clicking new connection and click next.

Click next to continue.

Choose the tables that you want to add and click on Finish. This will create a typed data set.

The screenshot shown below shows the new Strongly typed DataSet that was created.

In the next method, click the project in the solution explorer and choose the add new item from the context sensitive menu and in the dialog box choose the data set and click add.

The DataSet is created. The screenshot shows the toolbar with dataset tools. You can drag and drop any of the tools and configure. This also will create a typed dataset.

DataSets With Multiple Tables

In most situations a typical DataSet will be populated with multiple tables. By default these tables will not have any relationship defined. The user will also not have any constraints defined. Tables can be referenced by table name or serial number. For example a Customer table which was added as a third table can be referenced as under:

  1. CustomerDataTable = MultiTableDataSet.Tables(3)

or by the following statement:

  1. CustomerDataTable = MultiTableDataSet.Tables("Customer")

The following screenshot shows the structure of a DataSet with multiple tables:

« « ADO.NET Object Model
Using XML Data » »

Author Description

Avatar

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

Free Training

RSSSubscribe 0 Followers
  • Popular
  • Recent
  • Deploying Windows Applications In Visual Studio.NET 2005

    August 3, 2005 - 0 Comment
  • Application Class and Message Class

    June 8, 2005 - 0 Comment
  • .NET Complex Data Binding

    July 7, 2005 - 0 Comment
  • VB.NET MDI Applications

    July 3, 2005 - 0 Comment
  • Instantiating – Invoking Web Services, Creating Proxy Classes with WSDL

    July 16, 2005 - 0 Comment
  • VB.NET 2005 Free Training

    May 20, 2005 - 0 Comment
  • The Registry Editor in Visual Studio.NET 2005

    August 4, 2005 - 0 Comment
  • Setting and Adding Properties to Windows Form

    June 8, 2005 - 0 Comment
  • .NET Data Form Wizard

    July 9, 2005 - 0 Comment
  • .NET Exceptions

    July 5, 2005 - 0 Comment
  • Microsoft .NET Creating Installation Components

    August 10, 2005 - 0 Comment
  • Shared Assembly

    August 9, 2005 - 0 Comment
  • The File Types Editor

    August 9, 2005 - 0 Comment
  • Tracing VB.NET Windows Application

    August 9, 2005 - 0 Comment
  • VB.NET Windows Application Testing

    August 9, 2005 - 0 Comment
  • The Registry Editor in Visual Studio.NET 2005

    August 4, 2005 - 0 Comment
  • Customizing Setup Project in Visual Studio.NET 2005

    August 4, 2005 - 0 Comment
  • Deploying Windows Applications In Visual Studio.NET 2005

    August 3, 2005 - 0 Comment
  • Debugging Windows Applications In Visual Studio.NET 2005

    August 3, 2005 - 0 Comment
  • Working with Legacy Code and COM Components

    July 30, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Microsoft .NET Creating Installation Components
  • Shared Assembly
  • The File Types Editor
  • Tracing VB.NET Windows Application
  • VB.NET Windows Application Testing

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