|
ASP.NET & Databases Part 3
|
by . .
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
22440/
25
|
|
|
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"
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.
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
|
|
|
|
Product Spotlight
|
|