Transcript ADO .Net Overview - University of South Alabama

ADO.Net
A Brief Overview
Introduction
• There probably isn’t an application of any
significance that doesn’t involve access to
a database.
• In 1992, Open Database Connectivity
ODBC was created to provide a common
interface for access to proprietary DBMS.
• DBMS providers developed ODBC drivers.
• This is a very low-level interface.
Introduction
• Classic ADO (ActiveX components) relied
on OLE DB providers to access data.
• It was an improvement over ODBC
because it was higher level and allowed
access to any tabular data (such as a
spreadsheet).
ADO.Net Providers
These implement a .Net
wrapper around existing
data providers.
SQL .Net
Data provider
Client
SQL
Server
OLE DB
.Net data
Provider
OLE DB
Provider
ODBC
.Net data
Provider
ODBC DB
Provider
Managed code
Other
DBMS
Other
DBMS
Figure 1.2. Data access stack
ADO.Net
• SQL provider
– Connects directly to SQL server with a native
communication method
– Optimized for SQL, faster than OLE DB
• OLE DB provider
– Connects to other DBMS such as Oracle and
older versions of SQL Server
ADO.Net
• All .Net data providers support the
following objects
– Connection
– Command
– DataReader
– DataAdapter
using (SqlConnection connection = new sqlConnection(connectionString))
{
//Create a SqlDataAdapter for the Suppliers table.
SqlDataAdapter adapter = new SqlDataAdapter();
//A table mapping names the DataTable.
adapter.TableMappings.Add("Table", "Suppliers");
//Open the connection.
connection.Open();
//Create a SqlCommand to retrieve Suppliers data.
SqlCommand command = new SqlCommand( "SELECT SupplierID, CompanyName
FROM dbo.Suppliers;“, connection);
command.CommandType = CommandType.Text;
//Set the SqlDataAdapter's SelectCommand.
adapter.SelectCommand = command;
//Fill the DataSet.
DataSet dataSet = new DataSet("Suppliers");
adapter.Fill(dataSet);
}
ADO.Net
• Connection Object
– Establishes a connection to a database.
– Parameters for the connection string vary by data
provider.
– These values will be stored in the web.config file
ADO.Net
• Command Object
– Using a connection object, the command
object can execute a SQL statement or a
stored procedure.
– Using a command object to execute a SQL
statement that returns a datareader
ADO.Net
• DataReader
– A datareader provides direct, sequential (one
row at a time), read-only access to data in a
database.
– Datareaders are fast
– Low memory usage
– Why?
ADO.Net
• DataReader
– Random access is not allowed.
– A connection is maintained until the last row is
retrieved.
– Only one row at a time is maintained in
memory.
– .Net provides a Read() method to retrieve the
next record.
– The Read() method returns False if there are
no more records to read.
ADO.Net
• DataReader
– ExecuteReader
• Returns a datareader
– ExecuteScalar
• Returns a single value. For example, the result of
the query SELECT COUNT(*) FROM table
– ExecuteNonQuery
• Returns the number of rows affected by a DML
query (INSERT, UPDATE, DELETE)
ADO.Net
• ExecuteNonQuery
– Returns the number of rows affected by a
DML query (INSERT, UPDATE, DELETE) or
DDL query (CREATE, ALTER)
– Or in SQL ExerciseSystem, to issue the
command:
– SET SHOWPLAN_ALL { ON | OFF }
– Before executing an SQL expression
ADO.Net
• DataAdapter
– A DataAdapter is used to fill a dataset and
make changes to data in a database.
– Four important properties
•
•
•
•
SelectCommand
InsertCommand
UpdateCommand
DeleteCommand
ADO.Net
• DataSet
– A DataAdapter is used to fill and manipulate
DataSets.
– A DataSet is an in-memory collection of data
from multiple sources.
– A DataSet can contain DataTables and
DataRelation objects that describe the
relationship between two tables (PK,FK).
ADO.Net
• DataSet
– A DataSet is a read-only, client-side cursor.
– A DataSet can contain data from different
databases using more than one DataAdapter.
– The DataTables are loaded into memory with
the DataAdapter Fill() method.
Figure 1.1. DataSet object model
ADO.Net
• DataSet
– With the switch from ADO to ADO.Net, the
concept of the Recordset and it’s navigation
methods went away.
• recordset.movenext, .moveprevious…
– Now we use:
• foreach (string item in collection)
– Or directly reference an item in the collection
using a subscript
– A DataTable is accessed via it’s:
• DataColumn collection
• DataRow collection