ADO'NET Text Book Chapter 20 Most Material is from MSDN Overview of ADO'NET - PowerPoint PPT Presentation

1 / 31
About This Presentation
Title:

ADO'NET Text Book Chapter 20 Most Material is from MSDN Overview of ADO'NET

Description:

ADO.NET provides consistent access to data sources such as: Microsoft SQL Server ... The Command object enables access to database commands to return data, modify ... – PowerPoint PPT presentation

Number of Views:51
Avg rating:3.0/5.0
Slides: 32
Provided by: natheerk
Category:

less

Transcript and Presenter's Notes

Title: ADO'NET Text Book Chapter 20 Most Material is from MSDN Overview of ADO'NET


1
ADO.NETText Book (Chapter 20)Most Material is
from MSDN(Overview of ADO.NET )
2
Overview of ADO.NET
  • ADO.NET provides consistent access to data
    sources such as
  • Microsoft SQL Server
  • XML
  • Data sources exposed through OLE DB and ODBC.
  • ADO.NET separates data access from data
    manipulation
  • The ADO.NET classes are found in System.Data.dll,
    and are integrated with the XML classes found in
    System.Xml.dll.

3
Design Goals for ADO.NET
  • Disconnected data architecture
  • Tight integration with XML
  • Common data representation
  • The ability to combine data from multiple and
    varied data sources
  • Support the N-Tier programming model

4
ADO.NET Components
  • There are two components of ADO.NET that you can
    use to access and manipulate data
  • .NET Framework data providers
  • The DataSet

5
(No Transcript)
6
.NET Framework Data Providers
  • The .NET Framework Data Providers are components
    that have been explicitly designed for data
    manipulation and fast, forward-only, read-only
    access to data.
  • The Connection object provides connectivity to a
    data source.
  • The Command object enables access to database
    commands to return data, modify data, run stored
    procedures, and send or retrieve parameter
    information.
  • The DataReader provides a high-performance stream
    of data from the data source.
  • The DataAdapter provides the bridge between the
    DataSet object and the data source.
  • The DataAdapter uses Command objects to execute
    SQL commands at the data source to both load the
    DataSet with data, and reconcile changes made to
    the data in the DataSet back to the data source.

7
Connecting to DB using ADO .NET
DataReader
Command
username password
Command
DbDataAdapter
Data Source
Connection
Command
DB name DB driver
Update Fill
DataTable
DataSet
Consists of many Tables
DataRow
DataTable
Consists of many Rows
DataRow
Client Application
8
The DataSet
  • The ADO.NET DataSet is explicitly designed for
    data access independent of any data source.
  • As a result, it can be used with multiple and
    differing data sources.
  • The DataSet contains a collection of one or more
    DataTable objects made up of rows and columns of
    data, as well as primary key, foreign key,
    constraint, and relation information about the
    data in the DataTable objects.
  • As you may notice it is an Object-Oriented
    presentation of database.

9
.NET Framework Data Providers
  • Used for
  • Connecting to a database
  • Executing commands
  • Retrieving results
  • Those results are either processed directly
  • Or placed in an ADO.NET DataSet (OO presentation
    for the database)

10
.NET Framework Data Providers Core Objects
  • Connection
  • Establishes a connection to a specific data
    source. The base class for all Connection objects
    is the DbConnection class.
  • Command
  • Executes a command against a data source. Exposes
    Parameters and can execute within the scope of a
    Transaction from a Connection. The base class for
    all Command objects is the DbCommand class.
  • DataReader
  • Reads a forward-only, read-only stream of data
    from a data source. The base class for all
    DataReader objects is the DbDataReader class.
  • DataAdapter
  • Populates a DataSet and resolves updates with the
    data source. The base class for all DataAdapter
    objects is the DbDataAdapter class.

11
  • using System
  • using System.Data
  • using System.Data.OleDb
  • public class ADODemo01
  • public static void Main()
  • //Create the connection
  • OleDbConnection connection new
    OleDbConnection("ProviderMicrosoft.Jet.OLEDB.4.0
    Data SourceDataDirectory\\Students.mdb")
  • // The insertSQL string contains a SQL
    statement that
  • // inserts a new row in the source table.
  • OleDbCommand command new
    OleDbCommand("INSERT INTO Students(StudentId,
    Name, Age) VALUES ('01','2F','1G') ")
  • // Set the Connection to the new
    OleDbConnection.
  • command.Connection connection
  • // Open the connection and execute the
    insert command.
  • try
  • connection.Open()
  • command.ExecuteNonQuery()

12
  • //same as before
  • //beginning of the main method
  • //Create the connection
  • OleDbConnection connection new
    OleDbConnection("ProviderMicrosoft.Jet.OLEDB.4.0
    Data SourceDataDirectory\\Students.mdb")
  • // The insertSQL string contains a SQL
    statement that
  • // inserts a new row in the source table.
  • OleDbCommand command new
    OleDbCommand("SELECT StudentId, Name, Age FROM
    Students")
  • // Set the Connection to the new
    OleDbConnection.
  • command.Connection connection
  • // Open the connection and execute the
    insert command.
  • try
  • connection.Open()
  • OleDbDataReader reader
    command.ExecuteReader()
  • while (reader.Read())
  • Console.WriteLine(reader0.ToStri
    ng() " " reader1.ToString()

  • reader2.ToString())

13
Connection Class
  • An object of class OleDbConnection or
    SqlConnection (namespace System.Data.OleDb or
    System.Data.SqlClient) represents a connection to
    a data source
  • Connection object keeps track of the location of
    the data source and any settings that specify how
    the data source is to be accessed. A connection
    is either active (i.e., open and permitting data
    to be sent to and retrieved from the data source)
    or closed

14
OleDbConnection Class
15
OleDbCommand Class
  • A command object represents a SQL command that a
    DBMS can execute on a database.
  • A program can use the Command objects to
    manipulate a data source through the Connection
    object.
  • The program must open the connection to the data
    source before executing one or more Commands and
    close the connection once no further access to
    the data source is required.
  • A connection that remains active for some length
    of time to permit multiple data operations is
    known as a persistent connection.

16
OleDbCommand Class
17
OleDbDataReader Class
  • Provides a way of reading a forward-only stream
    of data rows from a data source. This class
    cannot be inherited.
  • To create an OleDbDataReader, you must call the
    ExecuteReader method of the OleDbCommand object,
    instead of directly using a constructor.
  • Changes made to a result set by another process
    or thread while data is being read may be visible
    to the user of the OleDbDataReader

18
OleDbDataReader Class
19
OleDbDataAdapter Class
  • Object DataAdapter connects to a data source
    (databaes) and executes SQL statements to both
    populate a DataSet and update the data source
    based on the current contents of a DataSet.
  • DataAdapter maintains a Connection (inside the
    Command object) object that it opens and closes
    as needed to perform these operations using
    SqlCommands.

20
OleDbDataAdapter Class
21
DataSet Class
  • An object of class System.Data.DataSet, which
    consists of a set of DataTables and the
    relationships among them, represents a cache of
    data that a program stores temporarily in local
    memory.
  • The structure of a DataSet mimics the structure
    of a relational database.

22
DataSet Class
23
DataTable Class
  • A DataTable contains a collection of DataRows
    that represent the table's data.
  • A DataTable also has a collection of DataColumns
    that describe the columns in a table.

24
DataTable Class
25
Connecting to DB using ADO .NET
OleDbCommand
username password
OleDbCommand
OleDbDataAdapter
Data Source
OleDbConnection
OleDbCommand
DB name DB driver
Update Fill
dataTable
DataSet
Consists of many Tables
DataRow
dataTable
Consists of many Rows
DataRow
Client Application
26
DataSet Object Model
27
DataSet
  • The ADO.NET DataSet is a memory-resident
    representation of data
  • The DataSet represents a complete set of data
    including tables, constraints, and relationships
    among the tables.
  • DataSet is independent of the data source
  • DataSet can include from multiple data sources.

28
  • //same as before
  • //Create and adapter
  • OleDbDataAdapter oleDbDataAdapter new
    OleDbDataAdapter()
  • //connect to the command
  • oleDbDataAdapter.SelectCommand command
  • DataSet dataSet new DataSet()
  • // Open the connection
  • try
  • connection.Open()
  • oleDbDataAdapter.Fill(dataSet,
    "Students")
  • catch (Exception ex)
  • Console.WriteLine(ex.Message)
  • finally
  • // Make sure the connection is closed
    when the

29
  • using System
  • using System.Data
  • using System.Data.OleDb
  • public class ADODemo04
  • public static void Main()
  • OleDbConnection connection new
    OleDbConnection("ProviderMicrosoft.Jet.OLEDB.4.0
    Data SourceDataDirectory\\Students.mdb")
  • string queryString "SELECT StudentId,
    Name, Age FROM Students"
  • OleDbDataAdapter adapter new
    OleDbDataAdapter(queryString, connection)
  • DataSet students new DataSet()
  • adapter.Fill(students, "Students")
  • for (int i 0 i lt students.Tables0.Row
    s.Count i)
  • Console.WriteLine(
  • students.Tables0.Rowsi0.ToSt
    ring() ","
  • students.Tables0.Rowsi1.ToSt
    ring() ","

Opens and Closes the connection
30
  • //Demo05.cs
  • //The following will not compile it is just a
    demo for filling dataset from multiple resources
  • // Assumes that customerConnection is a valid
    SqlConnection object.
  • // Assumes that orderConnection is a valid
    OleDbConnection object.
  • SqlDataAdapter custAdapter new SqlDataAdapter(
  • "SELECT FROM dbo.Customers",
    customerConnection)
  • OleDbDataAdapter ordAdapter new
    OleDbDataAdapter(
  • "SELECT FROM Orders", orderConnection)
  • DataSet customerOrders new DataSet()
  • custAdapter.Fill(customerOrders, "Customers")
  • ordAdapter.Fill(customerOrders, "Orders")
  • DataRelation relation customerOrders.Relations.A
    dd("CustOrders",
  • customerOrders.Tables"Customers".Columns"Cust
    omerID",
  • customerOrders.Tables"Orders".Columns"Custome
    rID")

31
Quiz
Given the above DB diagram Write a windows
application that inserts grade a student grade
Write a Comment
User Comments (0)
About PowerShow.com