Transcript CPGM21X1 - Sheridan College

PROG 11044
Advanced Web Applications
With .NET
Notes on ADO.NET (1)
Review So Far
Software we used:
Microsoft SQL Server Express
Microsoft SQL Server Management
Studio
Web Developer or Visual Studio
We created a Database called
KaluhaBooks
Authors, Categories, and Books tables
See the database diagrams for an
overview
7/17/2015
Wendi Jollymore, ACES
2
Review So Far
Database Accounts:
sa
the system admin account we use in the
Server Management software to work with
out database
Your guest account
The account you’ll use for visitors to your
data pages
This account was given permission to run
select, delete, update, and insert queries
on the data in the database
7/17/2015
Wendi Jollymore, ACES
3
Review So Far
Simple data page
SqlDataSource control
“quick and dirty” way to create a
connection to a database
GridView control
Shows data in rows and columns
Paging, sorting
AutoGenerateColumns: turn this off to
add the columns yourself
7/17/2015
Wendi Jollymore, ACES
4
Web-Based Database Apps
Recall that the web is stateless
This changes how database
applications function on the web
Typical Database App:
User accesses the database
They make changes to the data
They close the database or database
connection
7/17/2015
Wendi Jollymore, ACES
5
Web-Based Database Apps
Web Database Apps:
Connection to the database is made by client
machine
A copy of the desired data is retrieved and
downloaded to client machine
Connection to database is closed
Client user makes changes to data.
Client connects to database and applies the
changes
Connection to database is closed and a new
connection created.
A new and refreshed copy of the data is retrieved
and downloaded to the client machine.
The connection is closed.
7/17/2015
Wendi Jollymore, ACES
6
Web-Based Database Apps
Thankfully, many web pages will
only be allowing a user to view
records, not update/delete/insert
records
We will still learn to handle data
manipulation
But we’ll start by learning the
anatomy of the various
components of ADO.NET and datadriven web applications
7/17/2015
Wendi Jollymore, ACES
7
Database Connections
Open (or recreate) the first example
from the last class
Examine the markup for this page.
Look for the markup for the data
source control:
<asp:SqlDataSource ID="SqlDataSource1"
runat="server" ConnectionString="<%$
ConnectionStrings:KaluhaBooksConnectionString %>“
SelectCommand="SELECT * FROM [Authors]">
</asp:SqlDataSource>
7/17/2015
Wendi Jollymore, ACES
8
Database Connections
Look at the markup for the
GridView control:
DataSourceID property points to the
name of the data source control
See the <Columns> </Columns> tags
So far, we haven’t written any code.
We will do this today!
7/17/2015
Wendi Jollymore, ACES
9
Database Connections
Connection Strings
Recall in the markup for the Sql data
source control you saw:
ConnectionString="<%$
ConnectionStrings:KaluhaBooksConnectionString %>“
This is referring to a variable in the
Web.Config file
Open the Web.Config file for the
project
7/17/2015
Wendi Jollymore, ACES
10
Database Connections
<connectionStrings>
<add name="KaluhaBooksConnectionString"
connectionString="Data Source=l-userdude\sqlexpress;
Initial Catalog=KaluhaBooks; Persist Security Info=True;
User ID=userGuest; Password=ungabunga"
providerName="System.Data.SqlClient"/>
</connectionStrings>
The connectionStrings element contains
one or more connection strings that
define the connection to the database
The add element defines a single
connection string
7/17/2015
Wendi Jollymore, ACES
11
Connection String Properties
Provider
This is the database software, or data provider, that
you'd like to use.
we're using System.Data.SqlClient.
If you were using a different kind of database, such as
an .MDB file or MySql database, your provider name
would be different.
Data Source/Server/Address
the name of the server/instance, network address, or
location of the data source.
Initial Catalog/Database
the name of a database to use when the connection is
made.
7/17/2015
Wendi Jollymore, ACES
12
Connection String Properties
User ID/UID
the user name used for logging into the database, if
applicable.
Password/PWD
the password used to log into the database, if
applicable.
Connection Timeout
Number of seconds until timeout if there’s a problem
connecting
default is 15 seconds; never change this to 0!
having a timeout of 0 will cause the connection to
hang indefinitely!
Persist Security Info
true if security information is returned with the
connection; false if not.
In most cases, this should always be false!!
7/17/2015
Wendi Jollymore, ACES
13
Connection String in Web.Config
In the Web.Config file’s
connectionStrings element:
providerName attribute was used
This was automatically generated
when we added the Sql Data Source
control
Normally you would not include this
You will be coding the connections by hand
You’ll include the statement:
using System.Data.SqlClient;
7/17/2015
Wendi Jollymore, ACES
14
Exercise
Start up a new web project.
Add a new Web.Config file to the
project:
Right-click on the top node of your
project
From the dialog select Web
Configuration File and click the Add
button
In your project, open the
Web.Config file
7/17/2015
Wendi Jollymore, ACES
15
Exercise
Locate the <configuration> section
You should see the
<connectionStrings /> tag
Replace it with:
<connectionStrings>
<add name="SqlConnectionString“ connectionString=
"Data Source=L-USERDUDE\SQLEXPRESS;
Initial Catalog=KaluhaBooks; User ID=yourguest;
Password=yourguestpw" />
</connectionStrings>
Then go to your source code and add:
using System.Data.SqlClient;
7/17/2015
Wendi Jollymore, ACES
16
Exercise
In the Page_Load event, get the
connection string:
string connString =
ConfigurationManager.ConnectionStrings["SqlConne
ctionString"].ConnectionString;
ConfigurationManager
Contains config information in the Web.Config file
ConnectionStrings property contains the array of
elements added to the <connectionStrings> element
Access one of the elements by the name you gave it in
the web.config file
The last ConnectionString property corresponds to the
same item in the <add> element.
7/17/2015
Wendi Jollymore, ACES
17
Connection Pooling
Why put the connection string in the
web.config file?
When you connect to a database, many
things happen
Parsing of connection string and its
properties
Authentication
Locate data source
..and lots of other stuff!
Takes up a lot of processing time and
bandwidth!
7/17/2015
Wendi Jollymore, ACES
18
Connection Pooling
Some providers support connection
pooling:
When a connection is “closed”, it’s put aside
into a connection pool
When a new connection is needed, the
provider checks the pool.
If one matches, it uses that instead of
creating a new one.
This is much faster, and is a lot less
work.
SqlClient supports connection pooling.
7/17/2015
Wendi Jollymore, ACES
19
Connection Pooling
For connection pooling to work, connections
need to match exactly.
E.g.
Data Source=mycrapbox\sqlexpress; Initial
Catalog=KaluhaBooks; User ID=ungabunga;
Password=whatevah
Server=mycrapbox\sqlexpress;
Database=KaluhaBooks; User ID=ungabunga;
Password=whatevah
These connections are the same, in our eyes!
The syntax is different!
According to the provider, they do not match!
7/17/2015
Wendi Jollymore, ACES
20
Connection Pooling
By putting your connection string info in
the web.config file:
You will always use that exact connection
string each and every time.
When an unused connection goes into the
connection pool, it will be a perfect match for
all other connections you will need
This will make your program faster!
7/17/2015
Wendi Jollymore, ACES
21
Classes Used in ADO.NET
SqlConnection
Models the actual connection to a
database.
Like an input/output stream in Java.
Must be opened and closed to be used.
Has Open() and Close() methods.
Has a ConnectionString property that
contains the connection string
information
7/17/2015
Wendi Jollymore, ACES
22
Classes Used in ADO.NET
SqlCommand
Models a command that can be executed
over a specific connection.
Connection property
References the connection object that this
command should use.
CommandText property
Contains the SQL command to execute.
Can be a query, or insert/update/delete statement.
Can be a stored procedure or table name.
See also CommandType property
7/17/2015
Wendi Jollymore, ACES
23
Classes Used in ADO.NET
SqlCommand continued
ExecuteQuery() method
Used to execute an SQL Select query
Returns a DataReader object
DataReaders can be used as a data source
for controls like the GridView and ListBox
ExecuteScalar() method
Used to execute an SQL command that
returns a single value
E.g. using a Max() or Count() function
7/17/2015
Wendi Jollymore, ACES
24
Classes Used in ADO.NET
SqlCommand continued
ExecuteNonQuery() method
Used to execute an SQL statement that is
not a Select query
E.g. update, insert, or delete statement
Returns an integer  number of rows
affected
7/17/2015
Wendi Jollymore, ACES
25
Classes Used in ADO.NET
SqlDataReader
Models set of records returned by a query.
Forward-only
You can only move forward; you can’t move
backward
Read() method moves to the next available record
Returns true if successful, false otherwise.
Read-only
You can’t change/update any of the data
A connection used by a data reader is not
available until the data reader is closed.
Data readers are very fast and efficient!
7/17/2015
Wendi Jollymore, ACES
26
Exercise
Set up your web page
Add a GridView control
In the Page_Load event, create a
string variable cmdText with the
following SQL statement:
"SELECT * FROM Authors ORDER BY
LastName, FirstName"
Create a new SqlConnection object
The constructor takes a connection
string
7/17/2015
Wendi Jollymore, ACES
27
Exercise
Create a new SqlCommand object
The constructor takes a command string and
a connection object.
Open the connection:
connection.Open();
Assign the return value of the command
object’s ExecuteReader() method to the
grid’s DataSource property:
GridView1.DataSource =
command.ExecuteReader();
7/17/2015
Wendi Jollymore, ACES
28
Exercise
Invoke the grid’s DataBind()
method
Close the connection:
connection.Close();
Load the page into your browser.
7/17/2015
Wendi Jollymore, ACES
29
Data Binding
GridView.DataSource property
References a data source used to
populate the grid
Can be a hash table, data set, or data
reader (e.g. SqlDataReader)
SqlCommand.ExecuteReader() returns
an SqlDataReader object.
We assigned this to the DataSource
property of the grid.
7/17/2015
Wendi Jollymore, ACES
30
Data Binding
GridView.DataBind() method
Once you assign a data source, invoke
DataBind()
This method retrieves the data and
populates the grid
The whole process is called data
binding
We say that “the grid is bound to
the database”.
7/17/2015
Wendi Jollymore, ACES
31
Parameters
Sometimes you will want to view only
certain records
Records that match a certain criteria
E.g. only titles by a certain author, or only
books in a certain category
In older web programs, you could allow
the user to select a value, and then
concatenate the value to a WHERE
clause:
Select * from Authors Where LastName Like “
+ txtSearchAuthor.Text
7/17/2015
Wendi Jollymore, ACES
32
Parameters
This kind of practice can open you up to
SQL Injection Attacks.
E.g. a hacker who knows SQL and your
database structure could enter into the text
field:
*; Delete * From Authors;
When concatenated to your Sql statement,
you get:
Select * from Authors Where LastName Like *;
Delete * From Authors;
This will delete the records in your Authors
table!
7/17/2015
Wendi Jollymore, ACES
33
Parameters
ADO.NET uses Parameter objects to keep
this from happening
SqlParameter class
Models a parameter used as a variable value
in an Sql statement
ParameterName property:
The name of the parameter/variable used in the Sql
query
SqlDbType property
The data type of the value this parameter will have
Value property
The parameter’s value that will be inserted into the
Sql statement
7/17/2015
Wendi Jollymore, ACES
34
Parameters
A command object has a
Parameters property
This references a collection of
Parameter objects
command.Parameters.Add(paramObj);
Adds a parameter object to the collection
7/17/2015
Wendi Jollymore, ACES
35
Exercise
Do the tutorial in the notes:
Ado.Net: Coding Data Pages
“Using Parameter Objects”
7/17/2015
Wendi Jollymore, ACES
36