ASP.NET & Databases Part 2
 
Published: 17 Oct 2001
Unedited - Community Contributed
Abstract
In this part we're going to talk about the dataset object, getting you into the basics of how it works and how to use it.
by . .
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24741/ 63

Introduction to the DataSet

ASP.NET & Databases : Part 2

Published 10/17/01

Introduction

Welcome to Part 2 of our series into ASP.NET & Databases (that should really be data sources). In this part we're going to talk about the dataset object. The dataset object is huge and we are not going to cover all of it. We'll be talking about DataTables, DataRows and all that.

We're going to be using the page that we created in Part 1, that page opened a database and filled a dataset with data.

What is a DataSet?

Looking back on Classic ASP, we see that the no. 1 thing to store data in from a database is a recordset. A recordset could hold one table of data and was fairly flexible.
The dataset combines VB.NET's language and an updated version of the recordset to provide you with much more functionality and flexibility and it can hold many tables of data.

A DataSet is broken down to things like DataRows and DataTables, you can use these to create a dataset without a connection to an external data source. Also, a dataset is disconnected data, that means that its not connected to the database, all the data can be used when its offline and the dataset only needs the connection to update the data. Let's take a closer look at it.

For this Part I'll be taking you through the different objects that make up the DataSet.

DataSet in Action

In action

We'll create a simple dataset that we can use throughout this part.

Dim ds1 As New DataSet()
Dim dtable As new DataTable("people")
With dtable.Columns
     .Add("FName", System.Type.GetType("System.String"))
     .Add("LName", System.Type.GetType("System.String"))
     .Add("UID", System.Type.GetType("System.Int32"))
End With

dtable.Columns("UID").AutoIncrement = True

ds1.Tables.Add(dtable)

 

dim pkey() as DataColumn = {ds1.Tables("people").Columns("UID")}
ds1.Tables("people").PrimaryKey = pkey

This is a bit complicated so I separated it into two boxes.

Box 1: Ok, we declare a new dataset and a datatable which we call - "people" as you can see in the parameters.
The dTable.Columns collection has a function - add, that lets you add columns with the syntax - (column name, type). After adding three columns, we define the "UID" column as AutoIncrement (these properties are part of the DataColumn object). You can also set things like the Seed, caption, etc.
We then add the DataTable to the DataSet.

Box 2: This creates a array of DataColumn objects (in this case, only the UID column) and then tell the dataset that this array has a datacolumn that we want to be a primary key

This is a bit complicated so you don't really need to worry that much about box 2. We'll be going over the stuff to help you understand this next.

DataTables

DataTables

A DataTable is simply one table of data (much like the recordset) and you can do things like get data, modify data and all that kind of stuff. You can either create a DataTable like that or we can create a DataTable from a dataset.

Dim dtable As DataTable = ds1.Tables("people")

This fills dtable with the table at ds1.Tables("people"). The table structure and data is copied into a DataTable object. However it's disconnected but it still remains a link to the dataset, so if we add a row and then apply the changes we can update the dataset.

Dim row as DataRow = dtable.NewRow()
row(0) = "Philip"
row(1) = "Quinn"
dtable.Rows.Add(row)

dtable.AcceptChanges

This creates a DataRow. The dtable.NewRow() returns a datarow with the same syntax as the columns, so row(0) is FName. Then we add the row to the DataTable.
When we use dtable.AcceptChanges, because we got the table from the dataset, it then updates the dataset to include those changes.

<%@ Import Namespace="System.Data" %>
<script language="VB" runat="server">
Sub Page_Load(sender as object, e as eventargs)
Dim ds1 As New DataSet()
Dim dtable As new DataTable("people")
With dtable.Columns
.Add("FName", System.Type.GetType("System.String"))
.Add("LName", System.Type.GetType("System.String"))
.Add("UID", System.Type.GetType("System.Int32"))
End With

dtable.Columns("UID").AutoIncrement = True
ds1.Tables.Add(dtable)

Dim dtable2 As DataTable = ds1.Tables("people")

Dim row as DataRow = dtable2.NewRow()
row(0) = "Philip"
row(1) = "Quinn"
dtable2.Rows.Add(row)
dtable2.AcceptChanges

Response.Write(ds1.Tables("people").Rows(0)("FName").ToString)
End Sub
</script>

This should print "Philip" to the screen. A quick breakdown of this is (incase you haven't been reading):

1.      We create a new DataSet and a DataTable called "people"

2.      Added three columns and set their properties

3.      Added them to the DataSet

4.      Created another DataTable. This table was a copy of the one we had just put into the dataset.

5.      We created a DataRow using the syntax of the DataTable and put some data into it.

6.      We then accepted the changes which then updated the dataset.

7.      And printed the First column (which happens to be "FName") of the first row to the screen

There are also other methods that you may want to use with a DataTable.
We'll be covering these as we use them. So far we have seen what a DataTable is and how you can create them and then use them to edit a dataset (with the help of a DataRow). Next we'll take a look at the DataRow

DataRows

Summary

Anything Else

I've decided not to cover the DataColumn as there isn't really anything in there, we've seen it adding columns and setting their properties, that's about it.

Dim dcol as DataColumnCollection = ds1.Tables("people").Columns
Dim dcol2 as DataColumn = dcol.Item(2)

dcol2.AutoIncrementStep = 3

The AutoIncrementStep obviously increases the step from 1 (default) to 3.

I know that this article has been a bit messy, but DataSets are a messy. Luckily, it's not too hard to see what we're doing without much of an explanation. However you should remember that Columns and Rows are made from Collections and like arrays the collection indexes start at 0. Remember that you must AcceptChanges (in most cases) to have the data sent back to the dataset.

Exercise

Now using what we've learnt in this part and part 1, fill a dataset with information from a database and then make some changes to the data using the DataTable, DataRow and DataColumn objects.

Summary

You've probably seen that the DataSet is a very complex object and the DataTable, DataRow etc. are just part of that. In Part 3, we'll look at Database data in the DataSet, modifying and then sending back the data to the database.



User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-11-29 9:58:02 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search