Course: ASP.NET 3.

5
Topic 15: Working with Data

1
 ADO.NET Fundamentals
• ADO.NET is the technology that .NET
applications use to interact with a
database.
• It is a collection of classes, interfaces,
structures, and enumerated types that
manage data access from relational data
stores within the .NET Framework
• These collections are organized into
namespaces:
– System.Data,
– System.Data.OleDb,
– System.Data.SqlClient, etc.
 Understanding Data
Management
• The Role of the Database
• The most common way to manage
data is to use a database.
• This type of information is best
described using a relational model,
which is the philosophy that
underlies all modern database
products, including SQL Server,
Relational Model
 Database Access in
the Web World
• Accessing a database in a web application is a
completely different scenario than accessing a
database in a typical client-server desktop
application.
• Problems of scale: are the problems that can result
from the massively multiuser nature of the Web
• Problems of state: are problems that can result
from the disconnected nature of the Internet.
 Configuring Your
Database
• Before you can run any data access code, you
need a database server to take your command.

• A significant majority of ASP.NET applications use

Microsoft SQL Server
 Browsing and Modifying
Databases in Visual
Studio
• To create a connection to db carry-out the following
these steps:
– Right-click the Data Connections node, and
choose Add Connection, then select Microsoft SQL
Server and then click Continue.
– Enter or select your server Name and
authentication mode
– Click Test Connection to verify that this is the
location of your database.
– Select or Enter a Database Name list and click Ok
– Right-click a table and choose Show Table Data
 Running Queries in
Visual Studio
• Right-click your connection, and choose New
Query.
• Choose the table
• Create your query by adding check marks next to
the fields you want to include.
• select Query Designer ➤ Execute SQL
 ADO.NET Basics

Classes that are used to connect to a specific

data source (such as Connection, Command,
DataReader, and DataAdapter).

• Exists in several different flavors

• Each set is called an ADO.NET data provider.
 .NET Data Providers

• SQL Server provider: Provides optimized access to a

SQL Server

• OLE DB provider: Provides access to any data source

that has an OLE DB driver

• Oracle provider: Provides optimized access to an

Oracle database (version 8i or later)

• ODBC provider: Provides access to any data source

that has an ODBC (Open Database Connectivity)
driver
.NET Data Providers
Data Namespaces
 The Data Provider
Classes
• The information you need is located in a data
source such as a relational database.

• To access this information, extract it, and insert it

into the appropriate data objects, you need the
data provider classes described in this section.
 Direct Data Access
• When you use direct data access, you’re in charge
of building a SQL command and executing it.

• You use commands to query, insert, update, and

delete information.

• You work with it for a brief period of time while the

database connection is open, and then close the
connection as soon as possible.

• This is different than disconnected data access,

where you keep a copy of the data in the DataSet
object
Direct Data Access
 ADO.NET Namespaces
System.data Core namespace, defines types that
represent data

System.Data.Common Types shared between managed providers

System.Data.OleDb Types that allow connection to OLE DB

compliant data sources
System.Data.SqlClien Types that are optimized to connect to
t Microsoft® SQL Server

System.Data.SqlType Native data types in Microsoft® SQL

s Server
 To Query Information
1. Create Connection, Command, and DataReader
objects.
2. Use the DataReader to retrieve information from
the database, and display it in a control on a web
form.
3. Close your connection.
4. Send the page to the user.

To add or update information

1. Create new Connection and Command objects.

2. Execute the Command (with the appropriate SQL

statement).
 Importing Namespaces
• If You Are using SQL Server
– Imports System.Data
– Imports System.Data.SqlClient

• If You are using Ms Access

– Imports System.Data
– Imports System.Data.Oledb
 Creating a Connection
• Before you can retrieve or update
data, you need to make a connection
to the data source.
• When creating a Connection object,
you need to specify a value for its
ConnectionString property.
 The Connection String
• The connection string is actually a series of
distinct pieces of information separated by
semicolons (;). Each separate piece of
information is known as a connection string
property.
– Data source
– Initial catalog
– Integrated security
– ConnectionTimeout
• Windows Authentication
• With SQL Server authentication,
 Connection String
Property
For SQL server:
Dim Conn As New SqlConnection ()
Conn.ConnectionString =
"server=UNISO;Database=Register;User
Id=cs12d;Password=12345“
For MS access:
• Dim ConnectionString As String = "Provider=
Microsoft.Jet.OleDb.4.0; Data source =C:\Users\
Safraawi\Desktop\ASP\Sample\Project\Inventory\
Application\Inventory.mdb;Database Password="
 Making the Connection

• Before you can perform any database

operations, you need to explicitly open your
connection:
• myConnection.Open()
• Closing a connection is just as easy, as
shown here:
• myConnection.Close()
 The Select Command
To actually retrieve data, you need a few more
ingredients:
– A SQL statement that selects the information you
want
– A Command object that executes the SQL statement
– A DataReader or DataSet object to access the
retrieved records
 The DataReader
• Use a DataReader to quickly retrieve all your
results.
• The DataReader uses a live connection and should
be used quickly and then closed.
• Because of the DataReader’s optimized nature, it
provides better performance than the DataSet.
• It should always be your first choice for direct data
access.
• Before you can use a DataReader, make sure
you’ve opened the connection:
• To create a DataReader, you use the
ExecuteReader() method of the command object
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
 Retrieving the
Record

• The record is retrieved as soon as the

user changes the selection in the list box.
To make this possible, the AutoPostBack
property of the list box is set to True so
that its change events are detected
automatically.
 Updating Data
• Once again, you use the Command object,
but this time you
• don’t need a DataReader because no results
will be retrieved.
• You also don’t use a SQL Select command.
• Instead, you use: Update, Insert, or Delete.
• To execute an Update, an Insert, or a Delete
statement, you need to create a Command
object.
• You can then execute the command with the
ExecuteNonQuery() method
Displaying Values in Text Boxes
Adding a Record
Deleting a Record
 Disconnected Data Access
• When you use disconnected data access, you
keep a copy of your data in memory using the
DataSet. You connect to the database just long
enough to fetch your data and dump it into the
DataSet, and then you disconnect immediately.
• Do something time-consuming with the data.
• Fill a web control (like a grid) with your data.
• Navigate backward and forward through your
data while you’re processing it
• Save the data to a file for later use.
• The majority of ASP.NET web applications use the
DataSet to store data but not to make updates.
 Selecting
Disconnected Data
• With disconnected data access, a copy of
the data is retained in memory while
your code is running.
• You fill the DataSet in much the same
way that you connect a DataReader.
• DataReader holds a live connection,
information in the DataSet is always
disconnected.
Summar
y
 Query from Table
myConnection.Open()
Dim myCommand As New OleDbCommand()
myCommand.Connection = myConnection

myCommand.CommandText = "Select * from students"

Dim myReader As OleDbDataReader
myReader = myCommand.ExecuteReader()

Do While myReader.Read()

TextBox1.Text = myReader(0)
TextBox2.Text = myReader(1)
TextBox3.Text = myReader(2)
Loop

myConnection.Close()

11/15/24 33
 Query from Table
myConnection.Open()
Dim myCommand As New OleDbCommand()
myCommand.Connection = myConnection

myCommand.CommandText = "Select * from students"

Dim myAdapter As OleDbDataAdapter

myAdapter = New OleDbDataAdapter(myCommand)
Dim ds As New DataSet
myAdapter.Fill(ds, "students")

GridView1.DataSource = ds.Tables("students")
GridView1.DataBind()

myConnection.Close()

11/15/24 34
 Insert
myConnection.Open()
Dim myCommand As New OleDbCommand()
myCommand.Connection = myConnection

myCommand.CommandText = "insert into students Values ('" +

TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "')"

myCommand.ExecuteNonQuery()

myConnection.Close()

11/15/24 35
 Delete
myConnection.Open()
Dim myCommand As New OleDbCommand()
myCommand.Connection = myConnection

myCommand.CommandText = "DELETE from students WHERE std_id= " +

TextBox1.Text

myCommand.ExecuteNonQuery()

myConnection.Close()

11/15/24 36
 Update
myConnection.Open()
Dim myCommand As New OleDbCommand()
myCommand.Connection = myConnection

myCommand.CommandText = "update students set std_name = '" +

TextBox2.Text + "'" + "where std_id= " + TextBox1.Text

myCommand.ExecuteNonQuery()

myConnection.Close()

11/15/24 37
Q&A
38