Transcript Session 2 - Faculty Washington

Advanced Language Concepts in C#
Session 2
Last Update: 3/09
David Figge
[email protected]
Page 1
Copyright (C) 2009 by David Figge. All Rights Reserved.
Where We’re at, Where We’re Going…
Advanced Language Concepts in C#
Last Update: 3/09
Page 2
Copyright (C) 2009 by David Figge. All Rights Reserved.
Session 2 Topics






More Linq
Creating tables in a Database
Relationships and Referential Integrity
Nullable Types
Displaying XML data with DataGrid
SQL Security Concepts
Last Update: 3/09
Page 3
Copyright (C) 2009 by David Figge. All Rights Reserved.
Language INtegrated Query
Advanced Language Concepts in C#
Last Update: 3/09
Page 4
Copyright (C) 2009 by David Figge. All Rights Reserved.
Data from a Database



Up to now, we’ve been working with data from
within the program
Another obvious place from which to get data is a
Database
Let’s see what that would look like. To do that, we
will

Create a table in the database





We’ll use our Addresses information
Although we’ll start with a ‘test’ app, we’ll migrate the
Addresses app, too
Write code to initialize the database
Write code to retrieve data from the database
Let’s Add a New Database Table

We’ll add a table using Visual Studio’s interface

Last Update: 3/09
VS comes with SQL Express, too
Page 5
Copyright (C) 2009 by David Figge. All Rights Reserved.
Adding a New Table


Add an
Add an


Name the table Entry
ID: int, no nulls, Primary Key








The ID field provides a unique identifier to each
record, and is almost always useful. Especially in
situations like address entries, where two people
Addresses
database
SQL
could
easily have theto
same
name.Server
It’s also
essential to
for table
(which will review
Entry table
therelationships
database
in a few minutes…)
Set Identity Specification to Yes (1,1 is okay)
This makes ID be a database
maintained
entrylength.
that Itis
nchar(14)
is not variable
unique for each row in the table. This can be acct etc,
always
takes
14 characters.
Theto
nchar
but is primary key
and
used
for
unique
identifier
is aless
variable
lengthand
field– since phone
has
overhead,
relationships innvarchar(50)
other tables
(max 50). Use
it when
numbers
arelengths
prettymay
much all the same
Name: nvarchar(50),
nodatabase
nulls
(i.e.
atake
required
field)
vary, so the
doesn’t
length
– there’s
noupbenefit in
50 characters
every time.
Address: nvarchar(50)
supporting
variable lengths.
CSZ: nvarchar(50)
Phone: nchar(14) (not much variation in length)
Email: nvarchar(50)
ContactType: int, no nulls
Last Update: 3/09
Page 6
Copyright (C) 2009 by David Figge. All Rights Reserved.
Initializing the Database


Now that we have a data table, the
next step is to create a method of
reading and writing that table data
There are several ways to do this



SQL, for example
In C#, one of the easiest ways is to
create a class that ‘represents’ a data
record (a table row)
Let’s do that together…
Last Update: 3/09
Page 7
Copyright (C) 2009 by David Figge. All Rights Reserved.
Simple Linq 2 SQL

Create a Console app called “Simple
Linq 2 SQL”


Add a reference to System.Data.Linq
If needed, add Addresses as a new Data
Connection


Test it and make sure the connection works
Next step is to create a class that
represents a row in the Entry table of
Addresses…
Last Update: 3/09
Page 8
Copyright (C) 2009 by David Figge. All Rights Reserved.
Addresses.dbo.Entry
Now, for each data member that represents a table column
(or “field”), we indicate that with a Column attribute. In this
case, the Column attribute has some additional attributes of
[Table(Name = "Addresses.dbo.Entry")]
IsPrimaryKey and IsDbGenerated.
public class Entry
Now we continue for the reset of the
{
data member/columns.
[Column(IsPrimaryKey=true, IsDbGenerated=true)]
public int ID { get; set; }
[Column]
This portion in the [ ] is called an
public string Name { get; set; }
Attribute. It is used to describe a
[Column]
class, function, or data member,
public string Address { get; set; }
and is stored inside the assembly
[Column]
public string CSZ { This
get;
set; }for convenience.
is mostly
at compile
It allows
time.us
It is also available
[Column]
to “print” an object, which
at runtime
would display
through an API called
public string Phone { get; set; }
the object’s name andReflection.
City, State,InZip.
this case, the system
[Column]
(We’llset;
use this
canminutes…)
look at this attribute and see
public string Email { get;
} in a few
that we’re trying to associate this
[Column]
public int ContactType { get; set; }
class with the Entry table in
public override string ToString() // Easily
display
Addresses.
PrettyEntry
cool (and VERY
{
handy)!
return Name + " (" + CSZ + ")";
}
}
Last Update: 3/09
Page 9
As usual, type first, then we’ll talk…
Copyright (C) 2009 by David Figge. All Rights Reserved.
Connecting to the Database


The next step is to connect to the database
itself.
This is done by creating a DataContext object


The easiest way to create a DataContext is
using the constructor that takes a connection
string


Once created, this DataContext ‘represents’ the
database to our program
And the easiest way to get a connection string is
from Server Explorer’s Data Connections
properties
Let’s create a member DataContext variable
for our program…
Last Update: 3/09
Page 10
Copyright (C) 2009 by David Figge. All Rights Reserved.
Create the Database
Connection
Get this string from
the Server
Explorer/Data Connection/Connection
String property.
class Program
{
DataContext ADBC = new DataContext(@"Data Source=...");
public void Main(string[] args)
{
}
}
public class Entry
{
...
}
Last Update: 3/09
Page 11
Copyright (C) 2009 by David Figge. All Rights Reserved.
Initializing the Data Table

We could start by reading the table
contents from the database



But there’s no data in it!
Let’s start by initializing the table
This will also give us an idea of how
the Entry class (that represents the
table) will be used…
Last Update: 3/09
Page 12
Copyright (C) 2009 by David Figge. All Rights Reserved.
Initializing the Table
1.
2.
Create a new function: void
InitializeEntryData()
From the Addresses program


Copy over the enum Ctypes
Copy over the initialization string arrays

3.
names, addresses, types, etc.
Add this code to initialize the table
data…

Last Update: 3/09
Note that we only want to execute this
once!
Page 13
Copyright (C) 2009 by David Figge. All Rights Reserved.
Initializing the Table
This function reads the data in from
void InitializeEntryData()
the database table and puts it into a
{
variable
we’ve called entries.
string[] names = {
"Bob Anderson",
...
...
We then create a new Entry (a new
Table<Entry> entries = ADBC.GetTable<Entry>();
record or row, if you will) and
for (int x = 0; x < 10; x++)
initialize it.
{
Entry e = new Entry();
// Create entry
e.Name = names[x];
// Initialize
e.ContactType = (int)types[x];
e.Address = addresses[x]; This says, “When I say so, add the
data from this object as a new row in
e.CSZ = cszs[x];
database table”
After we’ve initialized all 10the
new
e.Phone = phones[x];
entries, we tell the data context to
e.Email = emails[x];
commit the changes to the database.
ADBC.Entries.InsertOnSubmit(e);//
Add to table
}
ADBC.SubmitChanges(); // Submit changes
Everyone doing okay?
}
Last Update: 3/09
Page 14
Type it in, then we’ll look at it…
Copyright (C) 2009 by David Figge. All Rights Reserved.
Initializing the Table

Once you have the program
compiling…

Insert a call to InitializeData into the Main
function

Remember – only call this function one time,
then comment it out

Otherwise you’ll get several copies of the data!
public static void Main(string[] args)
{
Program prg = new Program();
prg.InitializeEntryData();
}
Last Update: 3/09
Page 15
Copyright (C) 2009 by David Figge. All Rights Reserved.
Comment out InitializeEntryData
public static void Main(string[] args)
{
Program prg = new Program();
// prg.InitializeEntryData(); // Only Call ONCE!
}
Now that we have the data into the database,
let’s see what it takes to read that data in and
display it…
Last Update: 3/09
Page 16
Copyright (C) 2009 by David Figge. All Rights Reserved.
Displaying Existing Data
public static void Main(string[] args)
{
Program prg = new Program();
As we discussed, this reads in (all the)
// prg.InitializeEntryData();
data from an existing table into (in this
case) a Table of Entry objects (so each
prg.ListEntries();
Entry object in Table represents one
}
row in the data table).
void ListEntries()
Using Linq, we can select the records
{
we want
to display.
As a side note
– Normally
staticObviously, we
Table<Entry> entriesfunctions
= ADBC.GetTable<Entry>();
a where
clause, too.
(like could
Main)add
can’t
call nonstatic member functions (like
And
then
display
theWe
data.
This
is awe
way
you
var names = from e ListEntries).
in entries
select
e;can.
create an instance of Program (prg),
use it to invoke the instance
foreach (Entry ent and
in then
names)
member functions. This avoids having
Console.WriteLine(ent);
lots of static functions and allows
}
easier use of member variables.
Everyone have it working?
Making sense?
Last Update: 3/09
Page 17
Type it in, then we’ll look at it. It should work…
Copyright (C) 2009 by David Figge. All Rights Reserved.
Linq to SQL the Easy Way
Advanced Language Concepts in C#
Last Update: 3/09
Page 18
Copyright (C) 2009 by David Figge. All Rights Reserved.
Link to SQL

While this process works fairly well, there
must be an easier way than creating classes
like Entry





Not to mention the headaches of keeping it in
sync with the database table definition
Fortunately, there is an easier way.
The Linq to SQL tool allows you to graphically
define a set of tables you wish to use, and the
dependent classes will be built (and
maintained) for you automatically
Let’s see how this works.
Let’s go back to our Addresses program and
modify it to use the data from the database…
Last Update: 3/09
Page 19
Copyright (C) 2009 by David Figge. All Rights Reserved.
Linq to SQL

Add New Item (Right-click on the project)

Add the template "Linq to SQL Classes”


Call it AddressDB.dbml
For every table that you want to use

Simply drag it onto the .DBML design window


Do this now for the Entry table
A class of that name will be made for you
automatically


Similar to the class we created by hand
This also creates AddressDB.designer.cs


Last Update: 3/09
This contains the class info
Do not modify this directly, it will get rebuilt by VS
when needed (like when you modify the DB structure)
Page 20
Copyright (C) 2009 by David Figge. All Rights Reserved.
Linq to SQL

Linq to SQL automatically gives you

Get and Set properties for all fields



The DataContext handled for you
Linq to SQL takes the hassle out of
working with databases


However, be careful!
Databases are often bottlenecks, and must is
done to optimize them


The properties will update the database (on
request) only if data actually changes (very nice!)
Don’t thwart the DBA's work by your app! Work
with the DBA to maximize throughput
Let's make Addresses work with the
database…
Last Update: 3/09
Page 21
Copyright (C) 2009 by David Figge. All Rights Reserved.
Making Addresses work with the Database

Comment out the Entry and Entries classes


We don’t need those, since the Linq to SQL handles
that for us now
Leave the Ctypes enum



Next we define the DataContext

Add the variable to the top of the Form1 class:


AddressDBDataContext ADBC = new AddressDBDataContext();
Then we’ll tie in the Entry class


Comment on the AddressList declaration
Anywhere you refer to AddressList, replace it with


Although the database uses ints, it's more descriptive
We will need to convert from CType  int.
ADBC.Entries
Note that much of this works because we were
consistent in naming in our class and data table
It should work at
this point!
Last Update: 3/09
Page 22
Copyright (C) 2009 by David Figge. All Rights Reserved.
Linq to SQL



Pretty cool and easy, huh?
With relatively easy steps, we were
able to use data from the database
Linq to SQL provided



A definition for the Entry class
A collection of Entry objects (entries) that
represents the data within the database
It also supports multiple tables and the
relationships between them

Let’s see how that works…
Last Update: 3/09
Page 23
Copyright (C) 2009 by David Figge. All Rights Reserved.
Relational Database Review
Foreign Key
Customer
Phones
CustID
LName
Fname
CustSince
CustID
Phone
Type
13557
Smith
Joe
1983
13558
243-223-9384
Home
13558
Jones
John
1984
13558
243-349-3392
Mobile
13559
Baker
Paula
1984
13731
532-394-2246
Home
13560
Miller
Mike
1985
13777
223-221-3947
Business
13561
Stevens
Eleanor
1985
13777
669-394-3394
Mobile
Records
Relational
fromdatabases
two tablesare
arepopular
associated
because
with of
each
their
other
flexibility.
by having
You acan
common
decidefield.
at
There
Relational
When
Most
are
two
databases
several
databases
tablestypes
share
are
store
comprised
ofa databases,
common
their data
offield,
multiple
in
but
Tables.
Relational
this tables.
isEach
called
databases
row
The
a Foreign
in
layout
a table
are
of
Key
by
represents
the
relationship
far
tables
the most
isone
In
runtime
this example,
how youboth
want
tables
to combine
have a CustID
tables in
field.
order
CustID
to produce
is a unique
the results
identifier
youofwant
one
common.
“record”,
(because
designed
All
and
of
the
toeach
the
CustID
provide
big
column
names
inorganization
Phones
represents
in databases
is from
and
one
aefficiency,
–foreign
“field”
SQL Server,
of
table,
and
thatOracle,
to
Customer).
record.
ensure
Sybase,
Sothat
Usually
a database
as
MySql
little
this
–of
are
customer.
(this is where
So we
theknow
join that
and group
customer
keywords
13558 are
is John
used).
Jones,
The with
usualalanguage
home phone
for
definesduplication
a 30000
1-to-many
customers
of relationship
data asmight
possible
relational
(one
haveexists
customer
a Customer
databases.
(called
may
table
Normalizing
have
with
many
3000
aphone
database)
rows.numbers).
creating these results
number
is Structured
of 242-223-9384.
Query Language, or SQL.
Last Update: 3/09
Page 24
Copyright (C) 2009 by David Figge. All Rights Reserved.
Supporting Multiple Emails
Foreign Key
Entry
Email
ID
Name
Address
RecID
ID
EmailAddress
1
Bob Anderson
101 Main St.
1
1
[email protected]
2
Bill Henderson
528 W 32nd
2
2
[email protected]
3
Charlie Jones
2938 N 39th St
3
3
[email protected]
4
David King
2349 E 83rd St
4
3
[email protected]
5
Bob Lincoln
200 Belmont Pl
5
3
[email protected]
We’ll add a new table, Email, which will have three columns: RecID (a unique ID for
So,the
with
email
thattable),
review,IDlet’s
(theextend
sharedour
column
Addresses
from Entry
database
– thebyForeign
adding Key),
support
andfor
We’ll do this by starting with our Entry table.
EmailAddress. If someone
people
has
with
3 email
multiple
addresses,
email addresses.
there will be three rows in this
table all with the same ID.
Make sense?
Ready to give it a try?
Last Update: 3/09
Page 25
Copyright (C) 2009 by David Figge. All Rights Reserved.
Supporting Multiple Emails

Start by adding a new Email table



Name = Email
RecID: int, no nulls, primary key, identity
ID: int, no nulls


Use the Relationships settings to define the
foreign key relationship with Entry
EmailAddress: nvarchar(50), no nulls
Last Update: 3/09
Page 26
Copyright (C) 2009 by David Figge. All Rights Reserved.
Supporting Multiple Emails



Next we open both tables and copy
the original 10 email entries into the
new table (ensuring the ID matches)
Then we add a few more email
addresses for one or two people
(giving some people multiple email
accounts)
Finally, we remove the email column
from the Entry class
Questions so far?
Last Update: 3/09
Page 27
Copyright (C) 2009 by David Figge. All Rights Reserved.
Updating the DBML file

So now we’ve got an issue.

The Entry class we originally brought over has an
Email column/field, and that’s now changed


Here’s another area where Linq to SQL shines.
Simply



Open the dbml file
Clear the current contents, and
Add both tables again



Along with another table being added!
Linq to SQL will rebuild the Entry (and now Email)
classes for us!
Note that Linq to SQL also recognizes the foreign
key/ 1 to many relationship between the tables
So now that the table classes are updated in
our project, let's display the first email entry…
Last Update: 3/09
Page 28
Copyright (C) 2009 by David Figge. All Rights Reserved.
Supporting Multiple Emails
public partial class AddressFrm : Form
{
...
And this code selects all email
Email[] SelectedEmails;
addresses
associated
with the
current
Next, since
our current
method
of
...
Entry (e.ID),
putswork,
it intocomment
displaying
emailsand
won’t
void Display()
Start by adding
this
new class
member
SelectedEmails.
out
or
remove
this
line.
{
to hold the emails associated with the
...
‘current’ entry
// tbEmail.Text = e.Email;
SelectedEmails = (from ems
in ADBC.Emails
where ems.ID == e.ID
select ems).ToArray<Email>();
tbEmail.Text = SelectedEmails[0].EmailAddress;
}
...
We’ll start by displaying the first email address. When that’s working,
}
we’ll come back and add support for more…
Last Update: 3/09
Go ahead and make your code look like this. It should work.
Page 29
Copyright (C) 2009 by David Figge. All Rights Reserved.
Viewing Multiple Emails


The next step is to add support for viewing all
the email addresses associated with and Entry.
Add a button next to the Email field


Create an integer class variable CurEmail


This will hold the currently displayed index of the
SelectedEmails array
Create another integer origRecord and
initialize it to -1


Call it btnNextEmail, with Text of “>”
This indicates the record we last displayed with
the Display function. If we’re redisplaying the
same record, it’s because the email changed.
Here’s the code changes for the Display
function…
Last Update: 3/09
Page 30
Copyright (C) 2009 by David Figge. All Rights Reserved.
Viewing Multiple Emails
void Display()
{
if (current != origRecord)
{
origRecord = current;
Entry e = SelectedEntries[current];
...
SelectedEmails = (from ems
in ADBC.Emails
where ems.ID == e.ID
select ems).ToArray<Email>();
curEmail = 0;
}
tbEmail.Text = SelectedEmails[curEmail].EmailAddress;
}
Last Update: 3/09
Page 31
Copyright (C) 2009 by David Figge. All Rights Reserved.
Adding Email Addresses


The final step is to allow the user to add
another email address to an existing entry.
Start by creating a simple form asking for
email address

Add a New Form, EmailAddressForm, with a
label, text box, OK and Cancel buttons



Add an EmailAddress property to the class


This will just set and Get textbox’s Text property
Add a button to the main for to add an email


Set the label to “Email Address”
Set the form’s AcceptButton to the OK button, and
CancelButton to the cancel button
Call it btnAddEmail, with the text of “+”
Here’s the code that adds the email address
Last Update: 3/09
Page 32
Copyright (C) 2009 by David Figge. All Rights Reserved.
Adding Email Addresses
We then display it. If the user exited
private void btnAddEmail_Click(object sender,
EventArgs e)
with OK…
{
Associate it with the current
Create
a new
emailEntry’s
recordID (it belongs to the
EmailAddressForm eaf
= new
EmailAddressForm();
if (eaf.ShowDialog() == DialogResult.OK)
currently displayed entry)
{
Store the address
This
creates a new instance of the form
Email rec = new Email();
And add itthat
to the
database
asks
for the email address
rec.ID = SelectedEntries[current].ID;
This re-queries the SelectedEmails
rec.EmailAddress = eaf.EmailAddress;
so it includes the new email
ADBC.Emails.InsertOnSubmit(rec);
address
ADBC.SubmitChanges();
SelectedEmails = (from em
And this sets the current
Finally, we
the
inredisplay
ADBC.Emails
display to the most recently
record (with
theem.ID
new == SelectedEntries[current].ID
added email address.
where
email).
select em).ToArray<Email>();
curEmail = SelectedEmails.Length - 1;
Display();
}
Questions on what we’ve
done here?
}
Last Update: 3/09
Page 33
Type it in, then we’ll look at it. It should work…
Copyright (C) 2009 by David Figge. All Rights Reserved.
Your Turn!


Using a logic similar to our support for
multiple email addresses…
Add support for multiple phone numbers

Include both the phone number and the
type of phone



Use at least Home, Mobile, and Business
Include the ability to display all phone
numbers (and types), and the ability to add
additional phone numbers to the current
entry.
Good luck!
60 Minutes
Last Update: 3/09
Page 34
Copyright (C) 2009 by David Figge. All Rights Reserved.
Using XML Data
Advanced Language Concepts in C#
Last Update: 3/09
Page 35
Copyright (C) 2009 by David Figge. All Rights Reserved.
XML Refresher


XML is a common way to store and transfer
data, so it makes sense to delve into that a
little
What are some characteristics of XML files?



They are text files
They contain data
They contain information about the data




This is what makes XML so powerful
Rather than just data, it contains a context for that data
as well
As long as the sender/receiver agree on the context
information, data can be transferred and put into the
right place
Let's look at an example…
Last Update: 3/09
Page 36
Copyright (C) 2009 by David Figge. All Rights Reserved.
XML File Example
In XML, the tags are made up by the
writer. This is what makes it so
Each XML document
hasas
one
When used
input to a
<?xml version="1.0" encoding=“UTF-8" flexible.
?>
(and only
one) root
<!-- My CD Collection -->
program,
theelement,
programinmust
<CATALOG>
this case CATALOG.
This
first
line (so
is optional, but it
also
specifies
that
understand
whatItthe
tags
mean
<CD Number="1">
tells
the
‘version’
we’re
using
UTF-8 of XML that is
<TITLE>Empire Burlesque</TITLE>
coordination
is needed).
Comments
in
XML
start
<ARTIST>Bob Dylan</ARTIST>
Version 1.0
encodingbeing
(8 bitused.
Unicode
with <!– and end with -->.
<COUNTRY>USA</COUNTRY>
corresponds to the W3C
characters)
<COMPANY>Columbia</COMPANY>
The
other
way
is
to
specify
an
There are two ways tospecification
define
<PRICE>10.90</PRICE>
for XML 1.0.
Attribute, information
which is within
the
<YEAR>1985</YEAR>
for an element. The
</CD>
Element tag
like
this.
tag defines
a sub-a
<CD Number="2">
This ARTIST
XML document
contains
<TITLE>Hide your heart</TITLE> element of CD, and (in this case)
CDs
XML documents
have no
<ARTIST>Bonnie Tyler</ARTIST> collection of CDs. Multiple
Bob Dylan.
<COUNTRY>UK</COUNTRY>
are contains
stored inthe
thetext
CATALOG.
Each
required
layout format, but this
<COMPANY>CBS Records</COMPANY>
CD block starts with
<CD> and
example
is typical of how most
<PRICE>9.90</PRICE>
ends with </CD>
<YEAR>1988</YEAR>
XML documents are made and
You
can
specify
all
elements as Attributes instead
</CD>
displayed. Like C#, carriage
<CD Number="3">
(they are seen as the same). For example
<TITLE>Greatest Hits</TITLE>
returns and tabs are optional
<ARTIST>Dolly Parton</ARTIST>
(but you should use them).
<COUNTRY>USA</COUNTRY>
<CD Number=“2”
Title=“Hide
your heart”
… />
For every element/tag,
there is
<COMPANY>RCA</COMPANY>
a closing element/tag (the name
<PRICE>9.90</PRICE>
<YEAR>1982</YEAR>
The /> can close a tag
there it)
are no subwithwhen
a / before
Questions on XML
</CD>
elements.
</CATALOG>
layout?
Last Update: 3/09
Page 37
Copyright (C) 2009 by David Figge. All Rights Reserved.
Generating an XML File



Let’s take a look at how we can
generate an XML file of our address
book contact information
Then we’ll use that file as a source of
information for a web page
To generate the XML data we will

Add an XML Export button to our form



Call it btnXmlExport
Use that to create Entries.XML
Here’s the code
Last Update: 3/09
Page 38
Copyright (C) 2009 by David Figge. All Rights Reserved.
XML Export
The XElement data type has
This, Here
of course, calls
several useful functions.
An
describes
CreateAddressXML,
which them
private void btnXmlExport_Click(objectwe’re
sender,
EventArgs
Ine)
this
case,
we’reone
writing
using
theXElement
Save
function,
{
branch
of
anas
XML
document.
In of
Within
returns
the
anto
CreateAddressXML,
XElement
object
Attributes
instead
which writes
the
element
a
XElement xe = CreateAddressXML();
this case,
because
webase
start
with
we
create
the
XElement
xe.Save("Entries.xml");
Elements.
Remember
file. Pretty
easy,
huh?
This
constructor
takes
a variable
}
the rootattributes
element,
xe
contains
variable are
byarguments,
calling
the thus
like ID=“3”
inside
number
of
private XElement CreateAddressXML()
thethe
whole
data
constructor.
{
start
tag.set.
We’re
doing
allowing
you
to
specify
all this
the
XElement xDoc = new XElement("Addresses",
So
the
Addresses
node
contains
because
the GridView
we’ll use
sub-nodes
in Addresses.
from c in ADBC.Entries
the
root
<Addresses>
tag, and
select new XElement("Entry",
in the Web page likes attributes
new XAttribute("ID", c.ID),
allbetter
the Entry
tags
within it
than
elements.
new XAttribute("Name", c.Name),
new XAttribute("Address", c.Address),
Each Entry tag contains the ID,
new XAttribute("CSZ", c.CSZ),
from e in ADBC.Emails
Name, Address,
andwe
CSZ
In addition
to emails,
also
where e.ID == c.ID
elements
have multiple
Phone records, so
select new XElement("Email",
new XAttribute("RecID", e.RecID),
them)
Now,we
stillextract
within(and
eachadd
Entry,
we here.
new XAttribute("ID",e.ID),
new XAttribute("EmailAddress",
e.EmailAddress)
),
So, in the end, xDoc
is one
havebig
a collection
of 0 or more
from p in ADBC.Phones
long XElement that contains
Email records for each Entry. We
where p.ID == c.ID
select new XElement("Phone",
one Addresses element,
withthe base Email Element,
create
new XAttribute("PhoneID",p.PhoneID),
lots of sub-elements Entries.
with Attributes for the data.
new XAttribute("ID",p.ID),
new XAttribute("PhoneNum",
Each entry p.PhoneNum),
has 0 or more subnew XAttribute("Type", p.Type) ) ) );
Questions on how this
elements of Email and Phone.
return xDoc;
works?
}
Last Update: 3/09
Page 39
Typical stuff. Type then we’ll talk…
Copyright (C) 2009 by David Figge. All Rights Reserved.
XML Export

Okay. Let’s go ahead and run the
program, selecting XML Export

It should produce an XML file that looks
something like this

The file is in the bin\debug directory
<?xml version="1.0" encoding="utf-8"?>
<Addresses>
<Entry ID="1" Name="Bob Anderson" Address="101 Main St" CSZ="Belmont, NC 93849">
<Email RecID="1" ID="1" EmailAddress="[email protected]" />
<Email RecID="11" ID="1" EmailAddress="[email protected]" />
<Phone PhoneID="1" ID="1" PhoneNum="234-304-3290 " Type="1" />
</Entry>
<Entry ID="2" Name="Bill Henderson" Address="528 W 32nd" CSZ="San Francisco, CA 28348">
<Email RecID="2" ID="2" EmailAddress="[email protected]" />
….
Last Update: 3/09
Page 40
Copyright (C) 2009 by David Figge. All Rights Reserved.
Displaying XML Data on the Web



Now let’s see how to take that data and display it
on the Web with a GridView…
Start by creating a new ASP.Net Web Application
In the Design view (select at the bottom of the
window), add a new GridView to the display





A GridView is used to display tabular data on a web
page
This data can be from a database, Linq data, or from
an XML file (which is what we'll use)
We're going to make this display our Addresses data
Let’s start by copying the XML data file into the
App_Data directory of our project…
Next we define a DataSource…
Last Update: 3/09
Page 41
Copyright (C) 2009 by David Figge. All Rights Reserved.
Defining a Data Source




Highlight the table and select the small right
arrow to select GridView Tasks (if it’s not
already displayed).
From the “Choose Data Source” drop-down,
select “<New Data Source…>”
Click on XML File
For the Data file entry, specify

App_Data\Entries.xml




The Browse button may not see the file
You should see the basic Entry data displaying
You can also run the app (Ctrl-F5) and view it in a
browser window
You can hide columns if you wish

Last Update: 3/09
Select the ID column, and (from the Tasks) select
Remove Column
Page 42
Copyright (C) 2009 by David Figge. All Rights Reserved.
But Where’s the Rest?

That works fine, but we don’t have the phones and emails
(since they may have multiple items per entry)


But we can fix this!
Let’s get the Emails up and running…


Add a new GridView below the first
Specify a new XML Data Source called Emails





Data File: App_Data\Entries.xml
Xpath Expression: /Addresses/Entry/Email
Remove the RecID and ID columns from the display
In the Properties display for gridView2, set the Visible property to
FALSE (it won't show up until we say so)
Now we tell the top GridView that we want the ability to
select records

Select the Tasks menu from the top GridView and turn on “Enable
Selection”


This adds a column on the left saying “Select”, and allows us to select a
record from the grid
Select the top GridView, and in the properties window set
DataKeyNames to ID (this means that it's the primary key)
Last Update: 3/09
Page 43
Copyright (C) 2009 by David Figge. All Rights Reserved.
Displaying Emails

Now we have to tell the lower grid
which entries to display



Double-click on one of the “Select” lines
This takes you to the
SelectedIndexChanged handler, where
we’ll tell the lower GridView what to
display
Here’s the code. Type it in, then we’ll
talk about it…
Last Update: 3/09
Page 44
Copyright (C) 2009 by David Figge. All Rights Reserved.
Displaying Email Addresses
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
String ID = (String)GridView1.DataKeys[GridView1.SelectedIndex].Value;
Emails.XPath = "/Addresses/Entry[@ID=" + ID + "]/Email";
GridView2.Visible = true;
}
To begin
with, we take the
So now theThen
Emails
Source
has
weData
set the
XPath
entry. XPath allows you
(the row selected), and
been refined
to just SelectIndex
Email
entries
to indicate
a portion
of the XML entries you’re
use that
to
the ID field of the
within the selected
entry.
Nowsays
weget“Grab
interested
in. This
all the emails
record.
Note
this
uses
that DataKeys
display the where
grid. We
theinitially
Entry’sset
ID this
attribute matches the ID
property
we set to ID earler.
property to false
because
specified.
Thethe
‘@’XPath
indicates to look for an
wasn’t set toattribute,
properlyrather
select than
the an element (all our
records (as no record entries
was selected).
are as attributes).
Try it out.
It should work!
Last Update: 3/09
Page 45
Copyright (C) 2009 by David Figge. All Rights Reserved.
Your Turn!

Using what you learned in the previous
exercise,


add a grid to display the phone numbers of the
selected entry.
Note that you’ll simply add code to the existing
SelectedIndexChanged method of the top
GridView



Just add code to initialize and display the additional
GridView
Display only the phone numbers (no IDs or
types)
Good Luck!
30 Minutes
Last Update: 3/09
Page 46
Copyright (C) 2009 by David Figge. All Rights Reserved.
Where We’re At
Advanced Language Concepts in C#
Last Update: 3/09
Page 47
Copyright (C) 2009 by David Figge. All Rights Reserved.
Summary

Just to recap what we’ve done…






We started by creating a collection of “Entry” class objects
and displayed them
We then moved those to a database and created classes to
hold that data
Then we used Linq-to-SQL to have these 'container' classes
built automatically
We added additional tables to the SQL database, and had
Linq-to-SQL adapt
Finally, we exported our data to XML, and displayed that
data into a data grid
I want to close the loop with a couple words on a couple
more items




Nullable Types
Referential Integrity
SQL Security
.Net Security
Last Update: 3/09
Page 48
Copyright (C) 2009 by David Figge. All Rights Reserved.
Nullable Types

We saw that one great way to
integrate database data into an app is
to create a table that represents that
data, then read each row in as an
object of that class


Linq to SQL does this for us, but we could
(and have) also done it by hand
Just to refresh your memory…
Last Update: 3/09
Page 49
Copyright (C) 2009 by David Figge. All Rights Reserved.
Nullable Types
That’s a fine solution, but now we’ve got a
[Table(Name = "PhotoData")]
problem. The boolean value in the database
it isn’t track
known
the person
One of the items What
we’re ifkeeping
ofwhether
is if
public class PhotoData
has one of 3 states, TRUE, FALSE, or
N/A
submitting
the photo
the owner or not?
the person submitting
the photo
is the was
owner
{
(null). Because IsOwner
is a .Net
boolWell,
type,the
it database
Enter Nullable
Data
Types.
Since
.Net 2.0,
you
way
to solve this one is to
of
the
photo
(a
pretty
good
idea if you
might
[Column(IsPrimaryKey=true, IsDbGenerated=true)]
has only TRUE and
(Recall athat
Valuedata
canFALSE.
now declare
nullable
which
make
thetype,
‘can be
NULL’ option is on. That
want
to publish
it). }Itsure
seems
pretty
straight
public
int
ID
{
get;
set;
types can’t
be
set
tohave
NULL
likevalues
reference
types
can
the
defined
for that
data
type,
So
to
declare
IsOwner
a
nullable
way,
boolean
if
you
don’t
type,
know,
you simply leave the
forward
(you
either
are
or
you
aren’t
the
[Column]
A
couple
of useful
can). So we
have athe
dilemma
(that
applies
tonull
plus
value
of
null.
Note
that
this
only
include
?the
after
the
type
itself.
field
That’s
(sort
it.
of
a
‘not
applicable
to this
owner),
butcan
there’s
a}
problem.
public
string
Filename
{
get;
set;
The
??
operator
be
used
to
give
a
default
value
if it
properties/operators
all value
typeapplies
data huh?
types):
how
cantypes,
you
to value
data
as reference
types
Pretty
easy,
Remember
this only
works
photo’ statement).
is null.
Thisalready.
statementwith
essentially
[Column]
nullablesays:
types…
represent NULL as a on
legitimate
can
bevalue?
null
value
types.
.HasValue can be used to
public string SubmittedBy { get; set; }
see if the variable has
CanUsePhoto = photo.IsOwner.HasValue;
[Column]
been assigned a value
if (!CanUsePhoto.HasValue)
public bool? IsOwner
{ get; set; } CanUsePhoto=false;
(i.e. if it’s not
null).
Remember
this is what
In thisthat
example,
we’reit
}
lookskeeping
like when
youofdefine
a
track
photos
public void PhotoInfo(Photo photo)
class submitted
to represent
row in a
for,a perhaps,
{
table. at a later
publication
if (photo.IsOwner.HasValue) ...
date.
bool? CanUsePhoto = photo.IsOwner ?? false;
...
Questions on
}
Nullable Types?
Last Update: 3/09
Page 50
Copyright (C) 2009 by David Figge. All Rights Reserved.
Referential Integrity

Recall that when we set up the Entry, Email, and Phone
database tables, we used a couple of Foreign Keys



These connected the 3 tables via a shared column
Referential Integrity addresses issues having to do with this
foreign key relationship
The questions it addresses are:

What happens if Phone is given an ID that is not in the Entry
table?


What happens to Email and Phone if an Entry row is deleted?



Typically this returns an error
Typically, foreign key rows are deleted or an error is returned
So Referential Integrity is the database's responsibility to
ensure that the database isn't corrupted by modifications in
related tables.
What happens when referential integrity is violated is defined
in the database definition, and is the responsibility of the
database

You should, of course, check for errors if changing things that
could threaten referential integrity, like changing an Entry's ID.
Last Update: 3/09
Page 51
Copyright (C) 2009 by David Figge. All Rights Reserved.
SQL Security

SQL-based databases incorporated security into their
infrastructure


This is similar to the security in the operating system.
This security defines (among other things) who can








SQL databases have registered users, and permissions associated
with those users
Permissions are given or taken away with the SQL commands
GRANT and REVOKE
Typically in a production environment you will find





Create tables
Read table data (i.e. select)
Modify table data (i.e. update, insert)
Delete table data (i.e. delete)
Delete tables
Many users have read rights
Some users have modify rights
Fewer users have delete data rights
Only DBAs have create table/delete table rights
Sometimes applications will have password/logons to restrict
users appropriately. In that case, the app may have a powerful ID
into the database system
Last Update: 3/09
Page 52
Copyright (C) 2009 by David Figge. All Rights Reserved.
.Net Security

What code can execute (and how it
executes) on your machine all boils down
to trust

We’ve been running in full trust mode


Other situations would require less trust




Machine administrators, executing from the local
machine
Executing a file from a network share
Executing a file from an Internet site
Executing a file on a remote machine
In these circumstances, .Net security becomes
involved in executing the program
Last Update: 3/09
Page 53
Copyright (C) 2009 by David Figge. All Rights Reserved.
.Net Security

.Net security has a benefit because it controls
managed Code. With that it can:

Validate File format and metadata information



Verify code


MSIL verified for type safety at JIT compile time
Verify the code's Integrity


CLR determines that only functions within the
executable are accessed ("Assembly Isolation")
CLR validates metadata information
Strong named assemblies are verified using a digital
signature to ensure no alteration has happened
Further control via Code Access Security (later…)

Last Update: 3/09
CLR controls access at runtime (Web apps can't access
file system, for example
Page 54
Copyright (C) 2009 by David Figge. All Rights Reserved.
.Net Security

.Net Security is implemented in two
branches

Role-based security

Windows Authentication


Program Control


Microsoft's Internet based authentication
Code Access Security


Program authenticates an controls access
Passport Authentication


Supports local machine and Active Directory
Controls code's access to machine resources (disk,
network, registry, database, etc.)
Both of these come into play, combining into
a set of permissions either allowing or (more
often) disallowing access to a resource
Last Update: 3/09
Page 55
Copyright (C) 2009 by David Figge. All Rights Reserved.
Role-based Security

Role-based Authentication:

Windows-based



Forms-based



uses local- or network-based IDs, passwords, and
permissions
good for windows-based apps, or corprorate-based
apps with Active Directory
You add code to authenticate user and retrieve
role membership from a user store (database, etc)
If you don't, users given "guest" or "anonymous"
user access
Passport

Last Update: 3/09
Uses Microsoft's Passport SDK to authenticate user
and retrieving user roles
Page 56
Copyright (C) 2009 by David Figge. All Rights Reserved.
Code Access Security

Determines level of trust based on evidence




Installation directory of application
Site where assembly was loaded (if Web)
Assembly’s certificate (if any)
URL


Zone


Was it HTTP, FTP, etc
The name and trust of the zone it was loaded from
It takes this evidence, and classifies executable
into a Code Group, which defines access

So unsigned apps from a public Web site are in
one group, where apps installed onto a local
machine are in another
Last Update: 3/09
Page 57
Copyright (C) 2009 by David Figge. All Rights Reserved.
.Net Security

So, based on the group permissions assigned to it,
.Net will decide if the app can access a resource


If it can’t, a SecurityException is triggered
The restricted environment that not-fully-trusted
apps run in is called a Sandbox

Some resources are given to the sandbox



Like temporary (isolated) local storage
The “size” of the sandbox depends on the group
permissions
You can affect the sandbox by

Assigning a digital signature and either


Declaring that signature trusted (works only on local
machine) or
Having the signature verified by an outside agency, like
Verisign
Does this make sense?
Last Update: 3/09
Page 58
Copyright (C) 2009 by David Figge. All Rights Reserved.
End of Session 2
Advanced Language Concepts in C#
Last Update: 3/09
Page 59
Copyright (C) 2009 by David Figge. All Rights Reserved.