Working with Data
ADO.NET Fundamentals
Core Concepts
1
Introduction
• .NET is a multilayered technology
– ASP.NET is one component
– ADO.NET is another component
• ADO.NET
– is a technology that .NET applications use to communicate
with a database (data source in general)
– is a family of objects that provides the above functionality
• There are alternatives (such as Data Binding FW)
– “But to build scalable, high performance web applications
custom database code is always preferred”.
2
ADO.NET and Data Management
• Sophisticated ASP.NET application use a database behind the
scenes to retrieve information which is then processed and
displayed in appropriate format and location in browser.
• Using a database is an excellent way
– to start dividing the user interface logic from the content
– to create a site that can work with dynamic data.
3
Role of Database
• Business software example
• Hierarchical set of related information
– List of customers
– List of products
– List of sales
– Relational model representation
• Some Basic Examples of Data Driven
Applications
– E-Commerce sites
• Manage sales, customers,
inventory information, record
transactions
– Online knowledge bases / Customized
search engines
• Vast quantities of information or
links to documents and resources
– Information based sites
• Web portals
4
Database Access in Internet World
• Problems of Scale
– Hundreds of simultaneous users
– Limited resources
• Server memory
• Database connections
• Locked records
• Conflicting updates
• Problems of State
– Stateless protocol
– Continuously running application –Just an illusion
– Modify retrieved information: identification of record ?
5
Introducing ADO.NET
• DataSet
– A cache of information that has been queried from db.
– Disconnected
– Can store more than one table
– Can store relationships
• Answer questions easily: “What product did Joe Smith
order”
• Disconnected Access
– Previous tech – live connections
– Problem: Limited number of db connections
– Copy of information is made
– Connection is closed
– Reconnect to commit changes
• XML Integration
– As an XML document
– XML manipulation
6
SQL Server Express Edition
• Free data engine
• Scaled down version of SQL Server
• Free to distribute
• Limitations
– 1 CPU
– 1 GB RAM
– Db 4GB
– Graphical tools missing
7
Pubs Database
• By default no databases are
installed in SQL Server Express
edition.
• The scripts of Pubs database
can be executed to create the
Pubs database.
8
Browsing and Modifying Databases in Visual Studio
1. SQL Server Management Studio
2. Enterprise Manager
3. Visual Studio: Server Explorer
– Data Connections Node
• Existing dbs
• New db
9
Show Edit Table Data
10
SQL Basics
• To design an efficient database application you need to
understand the basic concepts of SQL.
1. A Select statement retrieves records
2. An Update statement modifies existing records
3. An Insert statement adds a new record
4. A Delete statement deletes existing records
11
Running Queries in Visual Studio
12
Sample Select Statement
• SELECT * FROM Authors
– * --- Slowdown
– FROM clause - Put some limit
– WHERE Clause - Put some limit
–
13
Improving Select Statement
• SELECT au_lname, au_fname
FROM Authors WHERE
State=‘MI’ ORDER BY au_lname
• TOP Clause
• International Date Format
14
String Matching with Like Operator
SELECT stor_id, stor_name,
stor_address, city, state, zip
FROM stores
WHERE (stor_name LIKE 'B%')
By default SQL is not CASE
Sensitive (Check validity of this
statement, Page 465)
WHERE (stor_name LIKE
'[BCDE]%')
15
Aggregate Queries
• Avg
• Sum
• Min
• Max
• Count
SELECT SUM(qty) AS EXPR1 FROM sales
RESULT: 493
16
SQL Update Statement
• UPDATE [table] SET [update_expression] WHERE
[search_condition]
• UPDATE Authors SET phone=‘408 496-2222’ WHERE
au_id=‘172-32-1176’
• It is possible to update an entire range of matching records
17
SQL Insert Statement
• INSERT INTO [table]
([column_list]) VALUES
([value_list])
• Database tables often have
requirements that prevent from
adding a record unless all fields
are with valid information
• Default values – used if left
blank
• Required fields
• Specified format for authorID
and ZIP code
18
Auto-Increment Fields
• Automatically incrementing identity column
– Assigns a unique value to a specified column when insert
operation is performed.
• Alternate: Create and maintain your own unique field
• Social Security Number as unique field
– If a persons doesn’t hold SSN
19
SQL Delete Statement
• DELETE FROM [table] WHERE [search_condition]
• It specifies criteria for one or more rows to be removed
• Delete operation may fail due to primary key – foreign key
relationships.
• Cascaded Delete
20
ADO.NET Basics
• Set of core objects divided into two groups
– Objects used to contain and maintain data
– Objects used to connect to a specific data source
• Data container objects are generic (irrespective of the data
source)
• ADO.NET Providers
– Each set of data interaction objects is called an ADO.NET
provider
– Customized for best performance
• SQL Server TDS protocol
– Provider objects derive from same base classes, implement
same interfaces, provide same basic set of methods and
properties (+ additional )
21
Providers
• SQL Server provider
– Provides optimized access to
SQL Server 7 or later
• OLEDB Provider
– Access to any data source that
has OLE DB driver
• Oracle Provider
– Optimized access to Oracle 8i
or later
• ODBC Provider
– Access to data source with
ODBC driver
• 3rd Party Providers
• Which provider to use?
22
Data Namespaces
23
Data Provider Objects
• It is possible to add tables and data by hand in a data object.
• But usually the information to be retrieved and updated lies in a
relational database.
• To access this information, extract it, and insert it into
appropriate data objects, data provider objects are required.
• Data provider objects are db specific.
• Code similarity
– Externally equivalent
– Code translation
• Differences:
– Namespace
– ADO.NET data access objects
– Internally different
24
Direct Data Access
• Easiest way to access data is to perform db operations directly
• Traditional ADO programming
• To retrieve information
• Create Connection, Command and DataReader objects.
• Use DataReader to retrieve information from db, and display it
on a web form
• Close connection
• Send the page to user.
– No live connection between the information seen by user and
the db
– ADO.NET objects are destroyed
25
Direct Data Access
• To add or update information
– Create Connection and Command objects
– Execute the Command (with SQL statement)
26
Importing Namespaces
• For using SQL Server provider
– Imports System.Data
– Imports System.Data.SqlClient
• For using Oracle Server provider
– Imports System.Data
– Imports System.Data.OracleClient
– Imports Oracle.DataAccess.Client
27
Creating Connection
• Limited in number
• Hold a connection open for as short a time as possible
• Try/Catch error handling structure
28
Connection String
Dim myConn As New SqlConnection( )
myConn.ConnectionString=“Data Source=localhost; Initial
Catalog=Pubs; Integrated Security=SSPI”
Default instance
Named instance
Using OLE DB Provider
Dim myConn As New OleDbConnection( )
myConn.ConnectionString=“Provider=SQLOLEDB.1; Data
Source=localhost; Initial Catalog=Pubs; Integrated
Security=SSPI”
29
Windows Authentication
• Can be configured using Enterprise Manager or Management
Studio
• Integrated Windows Authentication
– Default in SQL Server
• SQL Server Authentication
– User ID and password in connection string
Dim oradb As String = "Data Source=XE;User
Id=pinevalley;Password=pinevalley;"
Dim conn As New OracleConnection(oradb)
30
Connection String Tips
• Shared by all application code
• Where to store it?
• Easy to maintain if there is only a single Connection String
31
Making the Connection
32
Defining a Select Command
Dim cmd As New OracleCommand
cmd .Connection=myConn
cmd.CommandText=“SELECT * FROM Authors”
Alternate syntax
33
Using a Command with DataReader
• DataReader uses live connection and should be closed quickly.
• Fast-forward-only read-only access
• Better performance than DS
• No way to move back
myConn.Open( )
Dim myReader As OracleDataReader
myReader=cmd.ExecuteReader( )
myReader.Read( )
myReader.Close( )
Conn.Close( )
34
Author Browser
35
Filling List Box
Partial Class AuthorBrowser
Inherits System.Web.UI.Page
Private connectionString As String = _
WebConfigurationManager.ConnectionStrings("Pubs").Connection
String
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
FillAuthorList()
End If
End Sub
36
Conn is opened inside error
handling block.
Code for reading data uses loop.
Listbox is set to use ViewState
37
Retrieving Record
The code dynamically creates
SQL statement based on
selected item in drop down list
box. It uses value property of
selected item.
Only one record is read.
Assumption: one author has
matching record.
AutoPostBack
38
Updating Data
• To execute Update, Insert or Delete statement create a
Command object
• Execute the command with ExecuteNonQuery( ) method
• Returns number of rows effected
• If during an attempt to update or delete a record no records
are affected then probably there is an error in Where clause
that is preventing any records from being selected.
• If SQL command has a syntax error or attempts to retrieve
information from a nonexistent table, an exception will occur.
39
Enhancing the Author Page
• Enhancing the previous example
• Information from each field is added to a separate textbox
• Following buttons are added
– Update Button
– Delete Button
– Create New Button
– Insert New Button
• FillAuthorList( ) is the same as before
40
Author Manager
41
Selecting a List Item
42
Fields of Selected Author’s Record
43
lstAuthor_SelectedIndexChanged
44
Create New
What the code behind
does in this case?
Why Create New is
clicked before Insert
New?
45
Insert New
46
Error Inserting Record!
Figure out the reason
behind the failure?
47
Successful Insertion
48
Dynamically Generated Insert Statement
• Insert New button triggers the
ADO.NET code that inserts
finished record using a
dynamically generated Insert
statement.
• If insert succeeds then refresh
author list
• Using Validation controls
Pay special attention when creating
dynamic SQL.
Error prone
49
Creating More Robust Commands
• Using dynamically pasted-together SQL string has potentially
serious drawbacks
– Users may accidentally enter characters that will effect
SQL statement
– Users might deliberately enter characters that will effect
SQL statement (SQL Injection Attack)
• Orders returned by other customers
• Deleting records in other tables
• xp_cmdshell
• Protecting against SQL Injection
– Sanitizing input
• Doubling apostrophes
– Parameterized command
• Replace hard coded values with placeholders
• SELECT * FROM Customers WHERE CustomerID = @CustID
50
Parameterization Variations
• Syntax for para cmd differs from provider to provider
• OLEDB requires that each hard coded value is replaced with a
question mark.
• In this case, parameters aren’t identified by name but by their
position in SQL string.
• OleDB uses a sequential approach, in this case, the parameters
order really matters. A parameter is identified by a character
"?" in the SQL query.
string sql = "SELECT * FROM Customers WHERE CustomerId = ?";
OleDbCommand command = new OleDbCommand(sql);
command.Parameters.Add(new OleDbParameter("CustomerId",
OleDbType.Integer));
command.Parameters["CustomerId"].Value = 1;
51
Parameterization Variations
• SQL server uses a named parameter approach, it does not
matter the parameters order, but each parameter name requires
a "@" prefix.
string sql = "SELECT * FROM Customers WHERE CustomerId =
@CustomerId";
SqlCommand command = new SqlCommand(sql);
command.Parameters.Add(new SqlParameter("@CustomerId",
System.Data.SqlDbType.Int));
command.Parameters["@CustomerId"].Value = 1;
52
Parameterization Variations
• Oracle uses a similar approach, but a different prefix, since it
expects a ":" prefix.
string sql = "SELECT * FROM Customers WHERE CustomerId
= :CustomerId";
OracleCommand command = new OracleCommand(sql);
command.Parameters.Add(new OracleParameter(":CustomerId",
OracleType.Int32));
command.Parameters[":CustomerId"].Value = 1;
53
Rewriting Insert Code with Para Command
insertSQL = "INSERT INTO Authors ("
insertSQL &= "au_id, au_fname, au_lname, "
insertSQL &= "phone, address, city, state, zip, contract) "
insertSQL &= "VALUES ("
insertSQL &= "@au_id, @au_fname, @au_lname, "
insertSQL &= "@phone, @address, @city, @state, @zip, @contract)"
cmd.Parameters.AddWithValue("@au_id", txtID.Text)
cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text)
cmd.Parameters.AddWithValue("@au_Lname", txtLastName.Text)
cmd.Parameters.AddWithValue("@phone", txtPhone.Text)
cmd.Parameters.AddWithValue("@address", txtAddress.Text)
cmd.Parameters.AddWithValue("@city", txtCity.Text)
cmd.Parameters.AddWithValue("@state", txtState.Text)
cmd.Parameters.AddWithValue("@zip", txtZip.Text)
cmd.Parameters.AddWithValue("@contract", Val(chkContract.Checked))
54
Updating a Record
55
Update – Bug
A user successfully inserts a new
record.
He then click Update button. The
record of author selected in the
drop down list gets changed (with
values of newly inserted record).
Also a mismatch is created
between first name and last name
displayed in text boxes and the
drop down list (until the list is
repopulated).
Effectively we have two records
with different ids but same data.
How to avoid this behavior?
56
Deleting a Record
Dim deleteSQL As String
deleteSQL = "DELETE FROM Authors WHERE au_id=@au_id"
“Destructive tasks
are comparatively
easy”
57
References
Textbook: Beginning ASP.NET 4.0
Chapter 14
Parameterized Queries
http://weblogs.asp.net/cibrax/archive/2006/09/28/Parameterized-
Queries-_2800_Oracle_2C00_-SQLServer_2C00_-OleDb_2900_.aspx
Overview of ADO.NET
http://msdn.microsoft.com/en-us/library/h43ks021(v=VS.71).aspx
ADO.NET Architecture
http://msdn.microsoft.com/en-us/library/27y4ybxw(v=VS.71).aspx
58
