Transcript Advanced Language Concepts in C

Windows and Web Apps in C#
Session 4
Last Update: 4/11
David Figge
[email protected]
Page 1
Copyright (C) 2010 by David Figge. All Rights Reserved.
Checkbook Register

Let’s take a look at my solution for the
Checkbook Register program…
Last Update: 4/11
Page 2
Copyright (C) 2010 by David Figge. All Rights Reserved.
Windows Forms

We’re now going to work together to migrate
your program to a Windows Forms project



Together we will





The console app was getting pretty lame
And it will give you a very (very) basic introduction
to Windows forms.
Create a new WinForm project
Move the key class files into it
Add some Windows controls
Create some code and move some existing code
from the main program into the new program’s
form constructor.
Ready to begin?
Last Update: 4/11
Page 3
Copyright (C) 2010 by David Figge. All Rights Reserved.
Windows Forms

Work with the instructor to:


Create a new WinForm app WinCheckbook
Move the BankAccount, Trans, and Transactions classes to the
new project



Add some label controls to the form to display the owner, acct
number, and initial balance
Add a ListView to display the transactions



You’ll need to update the Namespace reference
Put the control into Detail view
Add the column headers Date, Type, Check#, Payee, Amount, and
Balance
In the Form1.cs file


Add a BankAccount object and initialize it
In the Form constructor





Last Update: 4/11
Set the text in the label’s text properties
Copy over the display loop from the original program, modify it
Create an array of string objects containing the fields to display
Create a ListViewItem object, initializing it to the string array
Add it to the ListView object.
Page 4
Copyright (C) 2010 by David Figge. All Rights Reserved.
Basic File I/O
Windows and Web Apps in C#
Last Update: 4/11
Page 5
Copyright (C) 2010 by David Figge. All Rights Reserved.
Components of Simple File I/O

Information-based Functions







I/O Basics
FileInfo
Using Stream I/O
Using Binary I/O
Serializing Data
DirectoryInfo / Directory
DriveInfo
Last Update: 4/11
Page 6
Copyright (C) 2010 by David Figge. All Rights Reserved.
C# I/O Overview

I/O in C# is based on the concept of a
Stream




Streams represent a flow of bytes to or
from an external source (file, pipe, serial
port, etc.)
Streams represent the lowest level of
unformatted data
To use the data, you must format it in
some way
This is done by adding one or more
“adapters” to translate the stream into
something useful…
Last Update: 4/11
Page 7
Copyright (C) 2010 by David Figge. All Rights Reserved.
Using Streams
Sometimes we want additional
C# uses its FileStream classes
functionality, such as buffering
To start with, we use
the low-level
Remember,
although
to
communicate
with
byteIf
the
file
contains
binary
(where data can gather before
it is
FileStream
object we’ve
to readbeen
fromtalking
the about
oriented
I/O
systems.
For
a athe
processed) C# allows you tofile.
addThis data,
takesinstead
thedata
dataof
from
filethis can
from
disk,
example,
let’s
say
we
want
StreamReader
wefrom
attach
additional adapters as you go,
andsuch
translates
it into
bytes.
be data
just about
information
from
a
disk
file.
a
BinaryReader.
We
can
as the BufferedStream object.
anywhere, including
then readmemory
the data(with
as a
ints, floats,
etc.
MemoryStream
object).
Int()R
The
Program
StreamReader
StreamWriter
BinaryReader
Buffered
37 1A Stream 37 1A
F3 D1
F3 D1
File Input
Stream
101010
Disk
For writing We
data,
C# has
could
use it as bytes alone, but it makes
“writer” versions
of each
more sense
to use a higher-level filter to
of the “readers”
to it into something useful. The
translate
translate from
program- class takes low-level byte
StreamReader
As you might expect, to instantiate a
oriented data
to
bits
and
streams and translates
them
to
streamalright
Doin’
“adapter”,
so Strings.
far?you pass in a reference
bytes.
to a lower-level stream object (or another
higher-level stream object).
Last Update: 4/11
Page 8
Copyright (C) 2010 by David Figge. All Rights Reserved.
Common Stream “Adapters”

Base Stream objects

FileStream – Initial access object




MemoryStream – Initial access object


Used to open files
Supports random access
Data is presented as a stream of bytes
Allows you to treat memory as a stream of bytes
Common “Adapters”



StreamReader/Writer – Used for text
BinaryReader/Writer – Used for binary
BufferedStream – Adds buffering support
Last Update: 4/11
Page 9
Copyright (C) 2010 by David Figge. All Rights Reserved.
Text, Unicode, and UTF


As we’ve discussed, C# uses Unicode
for all characters
C# uses its Reader and Writer classes to
convert between Unicode and the
target device

For example ,storing characters in ASCII in
text files
Last Update: 4/11
Page 10
Copyright (C) 2010 by David Figge. All Rights Reserved.
File I/O Example

Watch as I create a very simple console app that
writes the numbers 1-25 to the disk

Steps:

Create a FileStream object






Attach a StreamWriter object
Write the data to the StreamWriter object
Close the StreamWriter object (flushes data to disk)
Close the file
When using Readers, Writers, you can open file
through them (the FileStream is automatic)


Use FileMode.Create, opens file automatically
Remove the FileStream object
The Using statement can close streams for us

Last Update: 4/11
Add the Using statement for FileStream, StreamWriter
(closes them automatically)
Page 11
Copyright (C) 2010 by David Figge. All Rights Reserved.
Your Turn!

Add an Export button to our Checkbook
program


Add a button to the window, which will export
all the transactions onto the disk, to the file
Transactions.csv, one line per transaction
Steps:



Add the Export button, and in the handler
Use a StreamWriter to write the file
Write a line representing the column titles


“Date,Type,Check#,Payee,Amount”
For each of the transactions, write a line containing
all five of the elements separated by commas


This makes a Comma-Separated Variables, or CSV file
This is a common way of exchanging data, and creates a
file that can be read in and understood by Excel (Try it!)
30 Minutes
Last Update: 4/11
Page 12
Copyright (C) 2010 by David Figge. All Rights Reserved.
The File Class

The FileInfo class describes a file or directory
on the disk




It does not directly open the file, rather it
contains the directory-based information about
the file
Generally, you create a File object for each file or
directory you wish to work with on the disk
Note that constructing a File object does not
involve any interaction with the disk. It simply
encapsulates an existing (or soon to exist)
filename into an object
The File class provides several methods to help
you work with and manipulate the file or
directory…
Last Update: 4/11
Page 13
Copyright (C) 2010 by David Figge. All Rights Reserved.
FileInfo Class Details

Constructor:


Properties (typically no interaction with the disk):








boolean Exists – checks to see if File (i.e. name in obj) exists
String FullPath– returns full path to File
String Name – returns name portion of File
String Extension – returns the extension of the file
Long Length – returns the size of the file
Bool IsReadOnly – True if file is read only
String DirectoryName – returns full path of parent directory
Methods (may interact with disk)


File(String pathname)
CopyTo, MoveTo, Delete
Can also use to open the file


FileStream: Open, OpenRead, OpenWrite, Create
StreamReader/Writer: OpenText, CreateText
Last Update: 4/11
Page 14
Copyright (C) 2010 by David Figge. All Rights Reserved.
The DirectoryInfo Class

What FileInfo does for files,
DirectoryInfo does for directories


Allows you to get information on
directories, get lists of files and directories
within it, etc.
Properties:


Exists, Name, FullName, Parent
Methods

Last Update: 4/11
Create, Delete, MoveTo,
DirectoryInfo[] GetDirectories,
FileInfo[] GetFiles
Page 15
Copyright (C) 2010 by David Figge. All Rights Reserved.
File Example
static void Main(string[] args)
{
DirectoryInfo dir = new DirectoryInfo(args[0]);
FileInfo[] files = dir.GetFiles();
DirectoryInfo[] dirs = dir.GetDirectories();
foreach (DirectoryInfo di in dirs)
Console.WriteLine("[" + di.Name + "]");
foreach (FileInfo fi in files)
Console.WriteLine(fi.Name);
Console.WriteLine("\nNumber of directories: {0}", dirs.Length);
Console.WriteLine("Number of Files: {0}", files.Length);
}
This example uses the File object to list and count subdirectories
and files. It uses the GetFiles() and GetDirectories(), and is a
good example of what can be done with the FileInfo and
DirectoryInfo objects.
Last Update: 4/11
Page 16
Copyright (C) 2010 by David Figge. All Rights Reserved.
Recursion


In preparation for the next exercise, I
want to discuss Recursion
A recursive function is a function that –
somewhere in its code – calls itself



C# allows this
The only requirement is that (somewhere)
the function must know when to stop
calling itself and returning
Let’s look at an example…
Last Update: 4/11
Page 17
Copyright (C) 2010 by David Figge. All Rights Reserved.
Recursive Function Example
static void Main(string[] args)
{
int fac = factorial(5); // 1*2*3*4*5, or 120
}
int factorial(int n)
{
if (n == 1) return 1;
else return n * factorial(n-1);
}
Last Update: 4/11
Page 18
Copyright (C) 2010 by David Figge. All Rights Reserved.
Recursion

You do have to be careful with recursion



Due to repeated function calls, recursive calls
that get “out of control” usually result in a
stack error
However, there are times when it is just the
“perfect solution”
You should avoid using this for items where
you may make recursive calls more than 100
or so levels deep


There are usually other non-recursive ways to deal
with situations that are deeper or whose depth is
unknown…
Now for that exercise I promised…
Last Update: 4/11
Page 19
Copyright (C) 2010 by David Figge. All Rights Reserved.
FindFile Lab





We’re going to create a console app file find utility called
FindFile. It will allow you to experiment with FileInfo and
DirectoryInfo a bit and to learn how useful they are
Its syntax will be simple: findfile [filespec] [/r]
FindFile *.txt will display all the files with .txt extensions in the
current directory
FindFile *.txt /r
will recurse subdirectories as well,
displaying all .txt
files located ‘below’
the current directory
as well.
You’ll make this work with the FileInfo and DirectoryInfo
classes…
Last Update: 4/11
Page 20
Copyright (C) 2010 by David Figge. All Rights Reserved.
Supporting Recursion


To support recursion, you might design your program thus:
 In the main function, parse out the command line parameters,
saving the filespec and set a flag indicating if you are recursing or
not
Have the main function call a FindFiles function
 This function uses dirInfo.GetFiles() to find and print all filenames
matching the spec (you can pass in the filespec)
 Then (if recursing directories) use dirInfo.GetDirectories() go
through all the directories
 For each directory
 Use Directory.SetCurrentDirectory to move into the directory
 Call FindFiles recursively to display the files in that directory
and recurse it’s subdirectories
 use Directory.SetCurrentDirectory(“..”) to move back to the
current directory and move to the next in line



Remember to count the files, and to print the relative
path (from the current directory) with the filename.
After you return to the main function, print the number of files found.
To test, in Project Properties/Debug, set the command line arguments
to “*.jpg /r” and the starting directory to the Pictures directory.
60 Minutes
Last Update: 4/11
Page 21
Copyright (C) 2010 by David Figge. All Rights Reserved.
An Introduction to ADO.Net
Windows and Web Apps in C#
Last Update: 4/11
Page 22
Copyright (C) 2010 by David Figge. All Rights Reserved.
Introduction to ADO.Net


ADO.Net is the .Net framework that
supports data access, typically to a
database
ADO isolates the program from the
actual datastore, allowing you to
change back-end database systems
with minimal changes to the code.
Last Update: 4/11
Page 23
Copyright (C) 2010 by David Figge. All Rights Reserved.
ADO Components


ADO has 4 primary objects
Object Type
Base class
Purpose
Connection
DbConnection
Provides connection to a data store, and provide support for
transactions
Command
DbCommand
Programmatically represents a SQL query or stored
procedure.
DataReader
DbDataReader
Provides a forward-only, read-only access to data using a
server-side cursor.
DataAdapter
DbDataAdapter
Transfers DataSets from the database, providing an inmemory version of the table.
Process:
1.
2.
3.
Open connect to DB using Connection object
Create a (SQL) command to execute against the data
Use either


DataReader to read one-line at a time, or
Use a DataAdapter to get an in-memory table of results
Doing okay so far?
Last Update: 4/11
Page 24
Copyright (C) 2010 by David Figge. All Rights Reserved.
ADO Data Providers


Microsoft supplies drivers to support all
(compatible) systems, with a couple of
specialized versions.
Data Provider
Description
ODBC
Provides legacy support for early databases (pre 2000). Most databases
support ODBC, but it is not as efficient as OleDb
OleDb
Microsoft’s generic driver that supports all relatively recent database systems
SQL
Microsoft’s driver to support SQL Server (7.0+). Provides optimizations
specifically for SQL Server
Oracle
Microsoft’s driver to support Oracle, providing optimizations for that platform
This Data Provider is currently marked Obsolete, as Oracle has provided one of
it’s own for .Net 4.0+ (available from the Oracle web site)
Although we will use SQL in our
exercises, you could just as easily use
OleDb.
Last Update: 4/11
Page 25
Copyright (C) 2010 by David Figge. All Rights Reserved.
Simple Data Access

To execute a SQL command:
1.
Create and open a Connection object
establishing a virtual connection to the
database

2.
3.
4.
5.

This requires a connection string
Create a Command object that contains
the SQL statement to execute
Execute the command
If the command returns data, consume it
Close the connection
Let’s talk about each of these in turn…
Last Update: 4/11
Page 26
Copyright (C) 2010 by David Figge. All Rights Reserved.
Creating the Connection
This connection
is defined
through a
To establish
a connection
to a database,
“Connection
There
are several
we use the String”.
Connection
object
(in thisways to
get thiscase
(which
we’ll discuss object).
in a few minutes…).
a SqlConnection
void ReadData()
You can
alsois pass this string in to a constructor.
Once the connection
string
{
SqlConnection
conn =Tonew
SqlConnection();
execute
a SQLthe
command, we create a
established,
we
can
open
And
SQL command
conn.ConnectionString
= @"Data
Source=.\SQLEXPRESS;”+
Command
object.
Thethe
command
object you wish to
connection.
TheCatalog=PersonnelDB;Integrated
connection object (soneeds
it knows
execute.
again, both these
”Initial
Security=True";
to
know…Once
conn.Open();
where to execute the command)…
properties
set can
viathe
ajust
constructor
As
you
readready
thecan
data,
you
use the
Now
we’re
tobe
execute
command.
takes as
them
as parameters.
column
an
index
into
the a
The .Read() method returnsThe
true
ifthat
it name
ExecuteReader
command
returns
SqlCommand cmd = new SqlCommand();
DataReader
object,(the
which
returns to you
was able to=read
the next row.
Queryingobject
DataReader
forward-only,
readcmd.Connection
conn;
thegets
contents
of the column
(pretty
cool!).
the DataReader
object First,Last,Title,Phone
now
the method
cmd.CommandText
= "SELECT
FROM
Employees";
only
of reading
data)
newly read data. You must cast the data because Reader
SqlDataReader reader = cmd.ExecuteReader();
sees everything as an Object
while (reader.Read())
{
Console.WriteLine("Name: {0}",(string)reader["Last“]);
Console.WriteLine("
Title: {0}",(string)reader["Title"]);
Console.WriteLine("
Phone: {0}",(string)reader["Phone"]);
Console.WriteLine("-----------------------------------------");
}
conn.Close();
}
Last Update: 4/11
When you’re all done, you close the
connection.
Page 27
Copyright (C) 2010 by David Figge. All Rights Reserved.
Creating the Connection
void ReadData()
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = @"Data Source=.\SQLEXPRESS;”+
”Initial Catalog=PersonnelDB;Integrated Security=True";
Note that, like most Open/Close system
conn.Open();
components, making use of the USING statement is
SqlCommand cmd = new SqlCommand();
better than manually closing the connection (in this
cmd.Connection = conn;
case the conn.Close() function is ALWAYS called,
cmd.CommandText = "SELECT First,Last,Title,Phone FROM Employees";
even if an exception occurs). Using the USING
is preferred, and will now the be norm in
SqlDataReader reader = command
cmd.ExecuteReader();
while (reader.Read())
the examples I show.
{
Console.WriteLine("Name: {0}",(string)reader["Last“]);
Console.WriteLine("
Title: {0}",(string)reader["Title"]);
Console.WriteLine("
Phone: {0}",(string)reader["Phone"]);
Console.WriteLine("-----------------------------------------");
}
}
}
Last Update: 4/11
Is this making sense?
Page 28
Copyright (C) 2010 by David Figge. All Rights Reserved.
Checkbook Database


In a moment, you’ll use what we just
discussed to move the our checking
transactions to a new database.
But first, let’s work together to


Create the CheckbookRegister database
Create a BankAccount table and fill it with the
(non-transaction) data from BankAccount


Insert into BankAccount(InitBal,Name,Acct)
values (“+bal+”,”+Name+”,”+Acct+”)”
Create the Transactions table

Include AcctNum and establish it as a foreign key
relationship to BankAccount.

Last Update: 4/11
This allows you to support multiple bank accounts, and is
a more flexible (and proper) design.
Page 29
Copyright (C) 2010 by David Figge. All Rights Reserved.
Checkbook Database: Your Turn!

Modify the checkbook program by

Modify your LoadTransactions function to move
the existing transaction data into the
Transactions table


Once that worked (once)



Call this method only once, or you will duplicate your
transactions!
Modify the main program to call the BankAccount
default constructor (which doesn’t exist yet)
Create the default (no parameter) BankAccount
constructor that reads the transaction data from the
database instead of from the AcctTransactions object
Good Luck!
45 Minutes
Last Update: 4/11
Page 30
Copyright (C) 2010 by David Figge. All Rights Reserved.
Parameters

Up to now, we’ve been building our SQL
statements by combining text


We did this because it was intuitive and easy
However, it’s not a good practice, as it can be
a security hole


Parameters are a better solution


Especially if you have statements based on user
input
These are very similar to the parameters we use in
function calls, although the syntax is different.
Let’s see an example of a Select statement
without and with parameters…
Last Update: 4/11
Page 31
Copyright (C) 2010 by David Figge. All Rights Reserved.
Parameters
// Without Parameters
cmd.CommandText = “SELECT First,Last,Title,Phone “
“FROM Employees WHERE Title = “ + tbSearch.Text;
+
// With Parameters
This is
called
SQL Injection,
However,First,Last,Title,Phone
what
the
developer
may notand“is a+real
cmd.CommandText = “SELECT
concern.
Parameters
are the best defense
This is how
oneconsidered
might
put together
have
is that, bya simply
“FROM Employees WHERE
Title
=
@Title”;
to
SQL
Injection,
as it
limits
the parameter
select statement
without
parameters.
Itthe
appending
the
text
they typed,
user
cmd.Parameters.AddWithValue(“@Title”,tbSearch.Text);
text
to the
place
innot
the query (in this case
looks reasonable,
and
would
has the
ability
toprobably
add
additional
the
text
for
Where clause).
cause information
any problems
most
toin
that
textcases.
andthe
potentially
access (or damage) sensitive data.
A good rule-of-thumb is to use
This is the same query parameters
using a parameter
whenever using data input by
(you can have as many parameters
as you when inserting data
users (security),
wish). It’s not significantly
more work, and to avoid “quoting
(performance),
and also produces faster execution
within quotes” (readability).
(because the SQL engine doesn’t have to
work as hard to parse the statement).
Do parameters
make sense?
Last Update: 4/11
Page 32
Copyright (C) 2010 by David Figge. All Rights Reserved.
Stored Procedures

Stored Procedures are “functions” (a
series of SQL statements) that are precompiled and stored within the database


Because they’re pre-compiled, they are faster
than ad-hoc SQL statements
Let’s work together to


Create a stored procedure that returns the
BankAccount customer’s name, account
number, and initial balance
Modify our BankAccount constructor to use
that function…
Last Update: 4/11
Page 33
Copyright (C) 2010 by David Figge. All Rights Reserved.
The Disconnected Layer
Windows and Web Apps in C#
Last Update: 4/11
Page 34
Copyright (C) 2010 by David Figge. All Rights Reserved.
The Disconnected Layer

Up to now, we’ve been working in
ADO’s “Connected” layer



Everything we’ve been doing interacts
through a direct connection to the
database
For example, the Reader object uses a
(server side) cursor to retrieve the next
row
ADO also provides a “Disconnected”
layer, allowing you to simulate the
database design in memory
Last Update: 4/11
Page 35
Copyright (C) 2010 by David Figge. All Rights Reserved.
Disconnected Layer Components
DataSet
DataColumn
D
a
t
a
R
o
w
DataTable
DataRelation
The
DataColumn
represents
a database
column
field).have
The something
DataColumn
Of
course,
a table with
columns
is not very
useful(record
if you don’t
to
The
Of
The
Not
As
course,
The
DataSet
DataTable
surprisingly,
the
final
Disconnected
databases
missing
also
element,
has
you
element
many
often
put
Layer
representing
multiple
properties
consist
is
is
the
intended
of
DataColumns
DataRelation
multiple
and
the
to
methods.
database
simulate
tables.
element.
together
table,
We’ll
a
When
database
hit
by
It
has
you
also
putting
those
several
do,
in
resides
memory,
a
you
them
little
properties
can
in
later
into
the
place
an
aas
has many
useful
The
key ones
DataType,
represent
the
rowsproperties.
of data. This
is done
withare
theColumnName,
DataRows element.
We’lland
take
DataSet,
them
designed
and
all into
istoused
asupport
obvious
DataSet
to define
common
that.
place
element,
foreign
Key
to DataTable
ones
start
well.
which
key
is
are
the
relationships
represents
PrimaryKey
object.
DataColumn.
the
between
and
Database
TableName.
DataTables.
level.
Default
Value.
more about
DataRows
in a minute.
Last Update: 4/11
Page 36
Copyright (C) 2010 by David Figge. All Rights Reserved.
Component Summary
Element
Represents
Comments
DataSet
The Database
Holds the high-level design aspects
simulating a database
DataTable
Database Table
Contains the relationships and
settings associated with tables in
database systems
DataRelation
Table Relationships
Represents key relationships
between tables
DataColumn
Table column
Represents a record field
DataRow
A row of data in a table
Contains the row data
Last Update: 4/11
Page 37
Copyright (C) 2010 by David Figge. All Rights Reserved.
Manually Defining a DataSet



Sometimes it’s useful to create a
DataSet, along with all the tables and
relationships, manually
It would also give us an opportunity to
discuss the key elements of these
elements
We can look at our CheckRegister
database as an example…
Last Update: 4/11
Page 38
Copyright (C) 2010 by David Figge. All Rights Reserved.
The DataSet
DataSet bads = new DataSet("Bank Account");
The Account table has 3
columns, so we ‘ll create 3
DataColumn objects to
Info");
represent them.
DataTable AcctTable = new DataTable("Account
DataTable TransTable = new DataTable("Transaction Info");
by creating the DataSet typeof(string));
DataColumn BAAcctNum We’ll
= newstart
DataColumn("AccountNum",
BAAcctNum.AllowDBNull
= false;
element.
We’ll call it “Bank Account”. This
BAAcctNum.Unique = true;
We then
theitDataTable
is just a descriptive
namecreate
we give
in
elements,
oneitsfor
Account
case anything wants
to display
name.
DataColumn BAAcctName = new DataColumn("AccountName", typeof(string));
information
one for
Transaction
The
Accountand
Number
doesn’t
allow
BAAcctName.AllowDBNull = false;
information
Nulls, and must
be unique (it’s the
I mentioned
that the=AcctNum
is the table’s primary key). typeof(decimal));
DataColumn
BAInitBal
new DataColumn("InitialBalance",
BAInitBal.AllowDBNull
primary key for the table.
= false;
A DataTable
allows a primary key that is composed ofFinally, add the tables to the DataSet, and
After
we define
the
AcctTable.Columns.Add(BAAcctNum);
one or more columns. In this case, there’s we can
Thethen
Account
Name
andcolumns,
Initial we
define
the
relationship
AcctTable.Columns.Add(BAAcctName);
then
add them
to the
Columns
only
one,
the
0
entry
in
the
collection.
Balance
columns
aren’t
allowed
between
the
two
tables
like
this,
stating
AcctTable.Columns.Add(BAInitBal);
collection
of
the table like this.
to
be
Null.
...
that
the
AccountNum
columns
in both
You would see very similar logic for
AcctTable.PrimaryKey = new DataColumn[] { AcctTable.Columns[0]
creating the columns and addingtables
them are
to related. };
bads.Tables.Add(AcctTable);
the TransTable.
bads.Relations.Add(new DataRelation("AcctTrans",
AcctTable.Columns[0],
How are we doing?
TransTable.Columns[1]));
Last Update: 4/11
Page 39
Copyright (C) 2010 by David Figge. All Rights Reserved.
Adding a New Row
DataRow acctinfo = AcctTable.NewRow();
acctinfo["AccountNum"] = "93844934";
acctinfo["AccountName"] = "John Doe";You don’t just call the default
DataRow constructor, because
acctinfo["InitialBalance"] = 800;
the row would have no idea of
the columns within the table.
Once you have a row
AcctTable.Rows.Add(acctinfo);
youryou
data
in place,
SoOnce
instead
askisthe
table to
(patterned after the
youyou
then
add row
it tobased
the on
create
a new
columns), you can then add
existing
rowThis
collection
its
columns.
is donelike
with
data similar to how you
this. method.
the NewRow()
read data before.
Now let’s look at how to
add a row of data to an
existing DataTable.
Still making sense?
Last Update: 4/11
Page 40
Copyright (C) 2010 by David Figge. All Rights Reserved.
DataTableReader dtr = TransTable.CreateDataReader();
while (dtr.Read())
{
Console.WriteLine("Payee: {0}", (string)dtr["Payee”]);
...
Reading table data was made similar to
}
Tables also
support simple
queries
like this one. It returns to you
standard
DataReaders.
Startto
bythe
creating
Also, because
references
actual rows are returned, you can
an array of DataRows,
which
youusing
can then navigate through .
a DataTableReader
from
the
table
also make changes
to the
result
set that
willjust
change
in the tables.
Then
go
through
the
its method CreateDataReader.
...
rows like you would a
normal DataReader.
// Select all purchases from QFC
DataRow[] qfcs = TransTable.Select("Payee = 'QFC'");
Decimal total = 0;
foreach(DataRow d in qfcs)
total += d["Amount"];
Still holding on?
Last Update: 4/11
Page 41
Copyright (C) 2010 by David Figge. All Rights Reserved.
DataAdapters



Now that you understand the basics of
manipulating DataSets, it’s time to talk
DataAdapters
Remember I mentioned this section is
on the Disconnected Layer
That all works because of the
DataAdapter object, working with the
other elements we’ve been discussing
Last Update: 4/11
Page 42
Copyright (C) 2010 by David Figge. All Rights Reserved.
DataAdapters





The DataAdapter is analogous to the
Connection object you’re used to
working with
It works differently, however, because
when you request data it connects, gets
the data, then disconnects.
This allows you to work with your data
without requiring (or being slowed down
by) a connection to the database
You can make changes to the data, and
when the DataAdapter reconnects, your
changes will be applied to the database
It’s all very cool and very magical .
Last Update: 4/11
Page 43
Copyright (C) 2010 by David Figge. All Rights Reserved.
Using DataAdapters

Here are the steps to using DataAdapters. Say
we want to work with all our (fictional)
customer records.

Create a DataSet to hold the result set


Create a new DataAdapter


DataAdapter da = new DataAdapter(
“Select * from Customers”, connectionString);
Fill the DataSet with a new table containing the
results of the query


DataSet ds = new DataSet(“Marketing”);
da.Fill(ds,”Customers”);
The DataAdapter connects, gets the data, puts
it into your DataSet, and disconnects.

You are now free to read and manipulate the
data offline (even though it looks online!)
Last Update: 4/11
Page 44
Copyright (C) 2010 by David Figge. All Rights Reserved.
Time To Play


Let’s quit talking and start doing!
Together let’s


Add a few rows to the BankAccount table
Create a simple console application that




Last Update: 4/11
Creates a DataSet
Creates a DataAdapter
Fills the DataSet with “SELECT * from Accounts”
Iterates through the result set and prints the
record information.
Page 45
Copyright (C) 2010 by David Figge. All Rights Reserved.
Your Turn!

Modify your BankAccount constructor so
it uses DataAdapter(s) and DataSet(s) to


Select the Account record with the correct
account number…now that we’ve added
more records to the Accounts table (then
read in the account number, name, and
initial balance)
Use a DataSet to read in and initialize the
array of transactions.
40 Minutes
Last Update: 4/11
Page 46
Copyright (C) 2010 by David Figge. All Rights Reserved.
Updating the Database


The one aspect of DataAdapters that
we haven’t discussed is the process of
updating the database with changes
you made while in your disconnected
state.
This is done through the da.Update()
method…
Last Update: 4/11
Page 47
Copyright (C) 2010 by David Figge. All Rights Reserved.
The Update Method

When the Update call is made

The DataAdapter examines the RowState
of each row


row.RowState values of Added, Deleted, or
Modified are automatically updated based on
what you do within the row
For each row added, deleted, or modified,
that change is propagated to the
database


Errors updating will trigger an exception
You can also monitor (without exceptions) on
a row-by-row update basis for fine tuned
Questions on
updating.
Disconnected States?
Last Update: 4/11
Page 48
Copyright (C) 2010 by David Figge. All Rights Reserved.
End of Session 4
Windows and Web Apps in C#
Last Update: 4/11
Page 49
Copyright (C) 2010 by David Figge. All Rights Reserved.