Scribd Logo
Upload

Welcome to Scribd!

  • 74 views

    Vijaya Lakshmi Byri Support Engineer Developer Support, VB Webdata Microsoft Corporation

    Uploaded by

    Suganya Devi

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd

    Vijaya Lakshmi Byri Support Engineer Developer Support, VB Webdata Microsoft Corporation

    Uploaded by

    Suganya Devi
    74 views27 pages

    Original Title

    Ppt

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PPT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    74 views27 pages

    Vijaya Lakshmi Byri Support Engineer Developer Support, VB Webdata Microsoft Corporation

    Uploaded by

    Suganya Devi

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    You are on page 1of 27

    Microsoft ADO.

    NET

    Vijaya Lakshmi Byri


    Support Engineer
    Developer Support, VB WebData
    Microsoft Corporation
    Overview
     Microsoft® ADO.NET components
     Differences between ADO.NET and classic
    ADO
     When and how to use classic ADO in
    Microsoft .NET applications

    2
    Introduction to ADO.NET
     ADO.NET is a natural evolution of ADO, built
    around n-tier development and architecture,
    with XML at its core.
     ADO.NET is the new set of classes that
    exposes data access services to the .NET
    programmer.
     It is an integral part of the class Framework,
    which contains the entire library of classes
    that Microsoft provides with .NET, including
    the base classes for the primitive system
    types, I/O, network, data, and XML.
    3
    ADO.NET Components
     Managed providers
     DataSets

    4
    Managed Provider
     The .NET data provider is a set of
    components including the Connection,
    Command, DataReader, and DataAdapter
    objects.
     The .NET data provider is designed to be
    lightweight, creating a minimal layer between
    the data source and your code, increasing
    performance while not sacrificing
    functionality.

    5
    Managed Provider Components

    6
    Types of Managed Providers
     OLEDB managed provider
     SQL managed provider
     ODBC managed provider

    7
    OLEDB Managed Provider
     Uses native OLE DB through COM
    interoperability to enable data access.
     To use the OLE DB .NET data provider, you
    must also use an OLE DB provider. The
    following providers are compatible with
    ADO.NET:
     Microsoft OLE DB Provider for SQL Server
     Microsoft OLE DB Provider for Oracle
     Microsoft.Jet.OLEDB.4.0 OLE DB Provider for Jet
     These classes are located in the
    System.Data.OleDb namespace. 8
    Sample OLEDB Connection
    Dim OLEDBCn as OLEDBConnection
    Dim strConn as string

    StrConn = "Provider=MSDAORA.1;Data
    Source=dseoracle8; user
    id=demo;password=demo;"

    OLEDBCn = New OLEDBCOnnection


    OLEDBCn.Connectionstring = strconn
    OLEDBCn.Open()
    9
    SQL Managed Provider
     It uses its own protocol to communicate with
    Microsoft SQL Server™
     These classes are located in the
    System.Data.SqlClient namespace

    10
    Sample SQL Connection
    Dim SQLCn as SQLConnection
    Dim strConn as String

    StrConn = "Data Source=Vijayab1; user


    id=sa;password=Password1;Initial
    Catalog=Northwind;"

    SQLCn = New SQLConnection


    SQLCn.Connectionstring = strconn
    SQLCn.Open()
    11
    ODBC Managed Provider
     The ODBC .NET data provider is an add-on
    component to the .NET Framework SDK B2.
     It provides access to native ODBC drivers the
    same way the OLE DB .NET data provider
    provides access to native OLE DB providers.
     The ODBC .NET data provider is intended to
    work with all compliant ODBC drivers.

    12
    ODBC Managed Provider (2)
     Only the following drivers have been tested
    with the ODBC .NET data provider:
     Microsoft SQL ODBC Driver
     Microsoft ODBC Driver for Oracle
     Microsoft Jet ODBC Driver
     These classes are located in the
    System.Data.Odbc namespace
     The ODBC .NET data provider also requires
    the installation of MDAC 2.6 or later

    13
    Sample ODBC Connection
    Dim odbccn As Odbc.OdbcConnection = New
    Odbc.OdbcConnection("Driver={SQL
    SERVER};SERVER=Vijayab1;UID=sa;PWD=Password
    1;DATABASE=Northwind;")
    Dim odbcda As Odbc.OdbcDataAdapter = New
    Odbc.OdbcDataAdapter("Select * from employees",
    odbccn)
    Dim odbcds As New DataSet()

    odbcda.Fill(odbcds, "Employees")

    Me.DataGrid1.DataSource =
    odbcds.Tables("Employees") 14
    Managed Provider Components
    Command Object

     Represents a SQL statement or stored


    procedure to execute at a data source.

    15
    Managed Provider Components
    DataReader

     DataReader to retrieve a read-only, forward-


    only stream of data from a database.

    16
    DataReader Example
    Dim SQLcn As SqlClient.SqlConnection
    Dim cmd As SqlClient.SqlCommand
    Dim dr As SqlClient.SqlDataReader
    Dim strconn As String

    SQLcn = New SqlClient.SqlConnection()


    strconn = "Data Source=byri;integrated security=SSPI;Initial
    catalog=Northwind"
    SQLcn.ConnectionString = strconn
    SQLcn.Open()

    cmd = New SqlClient.SqlCommand("Select * from Products")


    cmd.Connection = SQLcn
    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    While dr.Read
    Me.ListBox1.Items.Add(dr.GetValue(1))
    End While

    dr.Close() 17
    Managed Provider Components
    DataAdapter
     Represents a set of data commands and a
    database connection which are used to fill
    the DataSet and update the data source.

    18
    DataAdapter Properties
     SelectCommand
     InsertCommand
     DeleteCommand
     UpdateCommand
     TableMappings

    19
    DataSet
    DataSet

    Tables

    Table

    Columns

    Column

    Constraints

    Constraint

    Rows

    Row

    Relations

    Relation
    20
    DataSet Example
    Dim strconn As String

    strconn = "Provider=SQLOLEDB.1;Data Source=byri;Integrated


    Security=SSPI;Persist Security Info=False;Initial
    Catalog=Northwind;"

    Dim OLEDBCn As OleDb.OleDbConnection = New


    OleDb.OleDbConnection(strconn)
    'OLEDBCn.ConnectionString = strconn

    Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select *


    from Products", OLEDBCn)

    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()

    da.SelectCommand = cmd
    OLEDBCn.Open()

    Dim ds As New DataSet()


    da.Fill(ds, "Products")
    21
    Differences Between ADO and
    ADO.NET
    ADO ADO.NET
    Uses Recordset Uses DataSet
    Requires JOIN query Supports DataRelation
    Provided by the RecordSet Communicates to a
    but typically supports database with standardized
    connected access, calls to the DataAdapter
    represented by the object
    Connection object

    22
    Differences Between ADO and
    ADO.NET (2)
    ADO ADO.NET
    Uses server-side and client- The architecture is
    side cursors disconnected so cursors
    are not applicable
    Database locks and active Does not retain database
    database connections locks

    23
    When to Use ADO in .NET
    Applications
     Client/server applications
     Server-side cursors
     Pessimistic locking

    24
    How to Use ADO
     Set a reference to Microsoft ActiveX® Data
    Object 2.x library by selecting COM tab (click
    Project and then References)
     The ADODB object should be used

    25
    Using ADO in .NET
    Example:
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strconn As String

    strconn = "Provider=SQLOLEDB.1;Data Source=Byri;Integrated


    Security=SSPI;Persist security info=false;Initial Catalog=Northwind;"
    cn.Open(strconn)
    cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient

    rs.Open("select * from products", cn,


    ADODB.CursorTypeEnum.adOpenStatic,
    ADODB.LockTypeEnum.adLockReadOnly)

    Dim DA As OleDb.OleDbDataAdapter
    DA = New OleDb.OleDbDataAdapter()

    Dim DS As New DataSet()


    DA.Fill(DS, rs, "RECORDSET")
    26

    You might also like