ASP.NET & Databases Part 3
 
Published: 23 Oct 2001
Unedited - Community Contributed
Abstract
In this part we are going to furher explore using a DataSet with a back-end database.
by . .
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19463/ 73

Introduction

ASP.NET & Databases : Part 3

Published 10/23/01

Introduction

In Part 1 we learnt about connecting, running queries and filling datasets. In Part 2 we talked about different parts of a dataset. In Part 3 we are actually going to use a dataset.
By now you should know all about how DataSet's work and how to fill them with information. All of the code here will be relating to the code we made in Part 1 (its at the bottom of the page). You should have a database with some sample data and used that in the code.

The Database Relationship

DataSet <-> Data Source

The connection between the DataSet and the data source is usually provided with a DataAdapter. Once a DataSet has got the data it is disconnected from the data source, whatever changes you make to the data are kept in the DataSet until you update the data source.

States:

  • Original This state is how the data was when it first came into the dataset. This cannot be modified, if you try to modify it, its put into one of the other two states.

  • Current This state is the data when you modify it, it holds the modified rows.

  • Proposed This state is the proposed data to be sent to the data source. The difference between this and the Current State is that this one is entered by using Edit Mode and imposes the rules of the data source immediately (the Current only enforces them when it updates the dataset).

You don't really need to worry about these, what we'll be concentrating on is the the current data (with changes).

Changing data and putting it back

Making some changes

We're going to do a simple example of where we take our data, change it and then update the data source.

Here is the table setup (designed for Microsoft Access)

Field Name Type
FName Text
LName Text
ID AutoNumber

 

 

FName LName ID
Philip Quinn 1
Joesph Payne 2
Douglas Adams 3
Michael Okuda 4

This is a pretty simple database, but we will be using it for all of our needs.

Example 1

Ok, I'll give you the code hunks, bit by bit and step you through it, then you can combine them to test it out.

<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<script language="VB" runat="server">
Sub Page_Load(sender as object, e as eventargs)

This is just your standard opening the language is VB and we need those two namespaces to do the data stuff.
 

Dim objConn as New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\sff\site\db\test.mdb")
objConn.Open()

Dim ds as Dataset = New DataSet()
Dim objAdapter as New OleDbDataAdapter("SELECT * FROM users", objConn)
Dim objCmdBld as New OleDbCommandBuilder(objAdapter)

objAdapter.Fill(ds, "users")
objConn.Close()

Ok, now we open the connection to the database (remember to specify your own database path when you do it).
We then open it (using the Open() method)
We declare a DataSet, a DataAdapter and a CommandBuilder. Then fill the DataSet and close the connection as we no longer need it.

For those of you who don't pay attention -

  • A DataAdapter is an interface between the database and the DataSet, allowing data to be transferred between both (2-way communication).
  • The CommandBuilder builds the SQL commands that you use to execute the SQL statements (we'll talk about this more soon).

The data is now in the DataSet table called "users"

Dim drow as DataRow

This declares a DataRow, we're going to use this one row to add two rows to the dataset.

drow = ds.Tables("users").NewRow()

drow(0) = "Gene"
drow(1) = "Rodenberry"
ds.Tables("users").Rows.Add(drow)

drow = ds.Tables("users").NewRow()

drow(0) = "Maxwell"
drow(1) = "Stewart"
ds.Tables("users").Rows.Add(drow)

From Part 2 you should be able to gather what this means. We make the DataRow a NewRow() from the DataTable "users", this means that it has the syntax and format of all the columns -

  • drow(0) is FName
  • drow(1) is LName
  • We don't need to specify drow(2) because it is an AutoNumber

We then add the row to the dataset and then go about creating another one.

objConn.Open()
objAdapter.Update(ds, "users")
objConn.Close()

This calls the Update() method of the DataAdapter which then uses the ds dataset's table "users" to update the database using the CommandBuilder's SQL Statements.

End Sub
</script>

Of course.

If you check the database you should have two new rows.

The CommandBuilder

OleDbCommandBuilder

I didn't cover this earlier because it wasn't relevant until now. The CommandBuilder builds SQL Statements for a specified DataAdapter. When dimensioning it (when will I use that word again?) you specify the name of the DataAdapter, when you call the Update() method of the DataAdapter, it checks the CommandBuilder for the SQL statements it needs to update the database.

The alternative is to set the individual commands in the DataAdapter (or use the OleDbCommand to do it), which is quite inflexible and I don't really see much of a point to it.

Other Stuff

There is other stuff that you can do with datasets, and you have already learnt it. I'll give you the code sample and you can decode it -

<%@ Page Language="VB" Debug="true" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<script language="VB" runat="server">
Sub Page_Load(sender as object, e as eventargs)

Dim objConn as New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\sff\site\db\test.mdb")
objConn.Open()

Dim ds as Dataset = New DataSet()
Dim objAdapter as New OleDbDataAdapter("SELECT * FROM users", objConn)
Dim objCmdBld as New OleDbCommandBuilder(objAdapter)

objAdapter.Fill(ds, "users")


Dim drow as DataRow

drow = ds.Tables("users").Rows(1)

drow(0) = "Joseph"

ds.Tables("users").Rows(0).AcceptChanges

objAdapter.Update(ds, "users")
objconn.Close()
End Sub
</script>
Summary

Summary

In this Part we looked at modifying data in a DataSet (and succeeded). However this isn't much use to you, in Part 4 we will look at displaying the Data in a nice format and then how to edit that data using TextBoxes.

Practice with using the DataSet to change data, also create your own DataSet and change that data and then merge that data with a database.

Happy Programming - wisemonk@aspalliance.com



User Comments

Title: ASP.NET & Databases : Part 3   
Name: ttt
Date: 2005-04-01 1:27:18 PM
Comment:
good

Product Spotlight
Product Spotlight 





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


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