Chapter 3 Introduction to ADO.NET

1
Chapter 3 Introduction to ADO.NET

• In this chapter, you will
•  Learn the basic classes in ADO.NET and its
• architecture
• Learn the different ADO.NET Data Providers
• Learn about the Connection and Command
  components
• Later
• Learn about the Parameters collection component
• Learn about the DataReader components.

2
3.2    Overview of the ADO.NET

• ADO.NET is a set of classes provides a rich set
  of components for creating distributed,
  data-sharing applications.
• ADO.NET is an integral part of the Microsoft .NET
  Framework.
• All ADO.NET classes are located at the
  System.Data namespace .
• When compiling code that uses the System.Data
  namespace, reference both System.Data.dll and
  System.Xml.dll.
• Basically speaking, ADO.NET provides a set of
  classes to support you to develop database
  applications and enable you to connect to a data
  source to retrieve, manipulate and update data
  with your database.

3
Chapter 3 Introduction to ADO.NET

• The classes provided by ADO.NET are core to
  develop a professional data-driven application
  and they can be divided into the following three
  major components
• Data Provider
• DataSet
• DataTable
• These three components are located at the
  different namespaces. The DataSet and the
  DataTable classes are located at the System.Data
  namespace. The classes of the Data Provider are
  located at the different namespaces based on the
  types of the Data Providers.

4
Chapter 3 Introduction to ADO.NET

• Data Provider contains four classes Connection,
  Command, DataAdapter and DataReader. These four
  classes can be used to perform the different
  functionalities to help you to
• Set a connection between your project and the
  data source using the Connection object
• Execute data queries to retrieve, manipulate and
  update data using the Command object
• Move the data between your DataSet and your
  database using the DataAdapter object
• Perform data queries from the database
  (read-only) using the DataReader object

5
Chapter 3 Introduction to ADO.NET

• The DataSet class can be considered as a table
  container and it can contain multiple data
  tables. These data tables are only a mapping to
  those real data tables in your database. But
  these data tables can also be used separately
  without connecting to the DataSet. In this case,
  each data table can be considered as a DataTable
  object.
• The DataSet and DataTable classes have no direct
  relationship with the Data Provider class
  therefore they are often called Data
  Provider-independent components.
• Four classes such as Connection, Command,
  DataAdapter and DataReader that belong to Data
  Provider is often called Data Provider-dependent
  components.

6
3.3  The Architecture of ADO.NET

• The ADO.NET architecture can be divided into two
  logical pieces
• command execution and caching.
• Command execution requires features like
  connectivity, execution, and reading of results.
  These features are enabled with ADO.NET Data
  Providers.
• Caching of results is handled by the DataSet.

7
 The Architecture of the ADO.NET
8
 The Architecture of the ADO.NET - 2
9
3.4 The Components of ADO.NET

• 3.4.1 The Data Provider
• The Data Provider can also be called a data
  driver and it can be used as a major component
  for your data-driven applications. The
  functionalities of the Data Provider, as its name
  means, are to
• Connect your data source with your applications
• Execute different methods to perform the
  associated data query and data accessing
  operations between your data source and your
  applications
• Disconnect the data source when the data
  operations are done

10
3.4.1 The Data Provider

• The Data Provider is physically composed of a
  binary library file and this library is in the
  DLL file format. Sometimes this DLL file depends
  on other DLL files, so in fact a Data Provider
  can be made up of several DLL files. Based on the
  different kinds of databases, Data Provider can
  have several versions and each version is matched
  to each kind of database. The popular versions of
  the Data Provider are
• Open DataBase Connectivity (Odbc) Data Provider
  (ODBC.NET)
• Object Linking and Embeding DataBase (OleDb) Data
  Provider (OLEDB.NET)
• SQL Server (Sql) Data Provider (SQL Server.NET)
• Oracle (Oracle) Data Provider (Oracle.NET)

11
3.4.1 The Data Provider

• The different data providers are located at the
  different namespaces, and these namespaces hold
  the various data classes that you must import
  into your code in order to use those classes in
  your project.
• Table 3-1 lists the most popular namespaces used
  by the different data providers and used by the
  DataSet and the DataTable.

12
 3.4.1.1   The ODBC Data Provider

• The ODBC.NET supports the following Data
  Providers
• SQL Server
• Microsoft ODBC for Oracle
• Microsoft Access Driver (.mdb)

13
 3.4.1.2   The OLEDB Data Provider

• The OLE DB.NET data access technique supports the
  following Data Providers
• Microsoft Access
• SQL Server (7.0 or later)
• Oracle (9i or later)

14
 3.4.1.3   The SQL Server Data Provider

• This Data Provider provides access to a SQL
  Server version 7.0 or later database using its
  own internal protocol. The functionality of the
  data provider is designed to be similar to that
  of the .NET Framework data providers for OLE DB,
  ODBC, and Oracle. All classes related to this
  Data Provider are defined in a DLL file and is
  located at the System.Data.SqlClient namespace.
  Although Microsoft provides different Data
  Providers to access the data in SQL Server
  database, such as the ODBC and OLE DB, for the
  sake of optimal data operations, it is highly
  recommended to use this Data Provider to access
  the data in an SQL Server data source.

15
3.4.1.4   The Oracle Data Provider

• This Data Provider is an add-on component to the
  .NET Framework that provides access to the Oracle
  database. All classes related to this Data
  Provider are located in the System.Data.OracleClie
  nt namespace. This provider relies upon Oracle
  Client Interfaces provided by the Oracle Client
  Software. You need to install the Oracle Client
  software on your computer to use this Data
  Provider.
• Microsoft provides multiple ways to access the
  data stored in an Oracle database, such as
  Microsoft ODBC for Oracle and OLE DB, you should
  use this Data Provider to access the data in an
  Oracle data source since this one provides the
  most efficient way to access the Oracle database.

16
3.4.2   The Connection Class

• Data Provider contains four sub-classes and the
  Connection component is one of them. This class
  provides a connection between your applications
  and the database you selected to connect to your
  project. To use this class to setup a connection
  between your application and the desired
  database, you need first to create an instance or
  an object based on this class.
• The Connection object you want to use depends on
  the type of the data source you selected. Data
  Provider provides four different Connection
  classes and each one is matched to one different
  database.

17
3.4.2   The Connection Class

• Table 3-3 lists these popular Connection classes
  used for the different data sources

18
  3.4.2   The Connection Class

• The connection string is a property of the
  Connection class and it provides all necessary
  information to connect to your data source.
  Regularly this connection string contains a quite
  few parameters to define a connection, but only
  five of them are popularly utilized for most
  data-driven applications
• Provider
• Data Source
• Database
• User ID
• Password

19
The Provider parameter

• Specifies the driver that the connection class
  uses to communicate with the database.
• The most common drivers are
• Microsoft.Jet.OLEDB.4.0 for Access
• SQLOLEDB for SQL Server
• MSDAORA for Oracle

20
The Data Source parameter

• is used to specify the server name of the
  computer on which the database is running.
• When connecting to an Access database, this
  specifies the path and database name.

21
The Database parameter

• Is self-explanatory and specifies the database
  name where your data resides

22
User ID and Password parameters

• They are used to specify your database login
  credentials
• The User ID and Password defined in the database,
  not your Windows login credentials.

23
3.4.2   The Connection Class

• A typical data connection instance with a
  general connection string can be expressed by the
  following codes
•  
• Connection New xxxConnection(Provider
  MyProvider _
• Data Source MyServer _
• Database MyDatabase _
• User ID MyUserID _
• Password MyPassWord)
• where xxx should be replaced by the selected
  Data Provider in your real application, such as
  OleDb, Sql or Oracle. You need to use the real
  parameter values implemented in your applications
  to replace those nominal values such as MyServer,
  MyDatabase, MyUserID and MyPassWord in your
  application.

24
3.4.2   The Connection Class

• The Provider parameter indicates the database
  driver you selected. If you installed a local SQL
  server and client such as the SQL Server 2005
  Express on your computer, the Provider should be
  localhost. If you are using a remote SQL Server
  instance, you need to use that remote servers
  network name. If you are using the default named
  instance of SQLX on your computer, you need to
  use .\SQLEXPRESS as the value for your Provider
  parameter. For the Oracle server database, you do
  not need to use this parameter.
• The Data Source parameter indicates the name of
  the network computer on which your SQL server or
  Oracle server is installed and running.
• The Database parameter indicates your database
  name.
• The User ID and Password parameters are used for
  the security issue for your database. In most
  cases, the default Windows NT Security
  Authentication is utilized.

25
3.4.2   The Connection Class

• Some typical Connection instances are listed
  below
•  
• OLE DB Data Provider for Microsoft Access
  Database 2003
•  
• Connection New OleDbConnection("ProviderMicroso
  ft.Jet.OLEDB.4.0" _
• "Data SourceC\database\CSE_DEPT.mdb" _
• "User IDMyUserID" _
• "PasswordMyPassWord")
• OLE DB Data Provider for SQL SERVER
•  
• Connection New OleDbConnection("ProviderSQLOLED
  B.1" _
• "Data SourceMyComputerName\SQLEXPRESS" _
• "Database MyDatabseName" _
• "User IDMyUserID" _
• "PasswordMyPassWord")
•  

26
3.4.2   The Connection Class

• Some typical Connection instances are listed
  below
•  
• SQL Server Data Provider for SQL Server Database
•  
• Connection New SqlConnection("Serverlocalhost"
  _
• "Data SourceSusan\SQLEXPRESS" _
• "DatabaseCSE_DEPT" _
• "Integrated SecuritySSPI")
•  
• Oracle Data Provider for Oracle Database
• Connection New OracleConnection("Data
  SourceXE" _
• "User
  IDsystem" _
• 
  "Passwordreback")

27
3.4.2.1   The Open() Method of the Connection
Class

• To create a real connection between your
  database and your applications, the Open() method
  of the Connection class is called and it is used
  to open a connection to a data source with the
  property settings specified by the connection
  string.
• An example of opening an OLEDB connection

28
3.4.2.2   The Close() Method of the Connection
Class

• The Close() method is a partner of the Open()
  method and it is used to close a connection
  between your database and your applications when
  you finished your data operations to the data
  source. You should close any connection object
  you connected to your data source after you
  finished the data access to that data source,
  otherwise a possible error may be encountered
  when you try re-open that connection in the next
  time as you run your project.
• Unlike the Open() method, which is a key to your
  data access and operation to your data source,
  the Close() method does not throw any exceptions
  when you try to close a connection that has
  already been closed. So you do not need to use a
  Try.Catch block to catch any error for this
  method.

29
3.4.2.3   The Dispose() Method of the Connection
Class

• The Dispose() method of the Connection class is
  an overloaded method and it is used to releases
  the resources used by the Connection object. You
  need to call this method after the Close() method
  is executed to perform a cleanup job to release
  all resources used by the Connection object
  during your data access and operations to your
  data source.
• After the Close() and Dispose() methods
  executed, you can release your reference to the
  Connection instance by setting it to Nothing. A
  piece of example code is shown in Figure 3-4.

30
3.4.3 The Command and the Parameter Classes

• Command objects are used to execute commands
  against your database such as a data query, an
  action query, and even a stored procedure. In
  fact, all data accesses and data operations
  between your data source and your applications
  are achieved by executing the Command object with
  a set of parameters.
• Command class can be divided into the different
  categories and these categories are based on the
  different Data Providers. For the popular Data
  Providers, such as OLE DB, ODBC, SQL Server and
  Oracle, each one has its own Command class. Each
  Command class is identified by the different
  prefix such as OleDbCommand, OdbcCommand,
  SqlCommand and OracleCommand. Although these
  different Command objects belong to the different
  Data Providers, they have the similar properties
  and methods, and they are equivalent in
  functionalities.

31
  3.4.3.1   The Properties of the Command Class

• The Command class contains more than 10
  properties, but only four of them are used
  popularly in most applications
• Connection property
• CommandType property
• CommandText property
• Parameters property

32
  3.4.3.1   The Properties of the Command Class

• Connection property holds a valid Connection
  object, and the Command object can be executed to
  access the connected database based on this
  Connection object.
• CommandType property indicates what kind of
  command that is stored in the CommandText
  property should be executed.
• CommandText property contains a complete SQL
  statement if the value of the CommandType
  property is Text.
• Parameters property holds a collection of the
  Parameter objects. You must first create and
  initialize a Parameter object before you can add
  that object to the Parameters collection for a
  Command object.

33
  3.4.3.5   The Constructor of the Command Class

• The constructor of the Command class is an
  overloaded method and it has multiple protocols.
  Four popular protocols are listed in Figure 3-8
  (an SQL Server Data Provider is used as an
  example).

34
  3.4.3.6   The Methods of the Command Class

• The actual execution of a Command object is to
  run one of methods of the Command class to
  perform the associated data queries or data
  actions. Four popular methods are widely utilized
  for most data-driven applications and Table 3-6
  lists these methods.

35
  3.4.3.2   The Constructors and Properties of
the Parameter Class

• The Parameter class has four popular
  constructors, which are shown in Figure 3-5 (an
  SQL Server Data Provider is used as an example).

36
  3.4.3.3   Parameter Mapping

• When you add a Parameter object to the
  Parameters collection of a Command object, the
  Command object needs to know the relationship
  between that added parameter and the parameters
  you used in your SQL query string. Different
  parameter mappings are used for different Data
  Providers. Table 3-5 lists these mappings.

37
  3.4.3.3   Parameter Mapping

• Both OLE DB and ODBC Data Providers used a
  so-called Positional Parameter Mapping, which
  means that the relationship between the
  parameters defined in an SQL statement and the
  added parameters into a Parameters collection is
  one-to-one in the order. In other words, the
  order in which the parameters appear in an SQL
  statement and the order in which the parameters
  are added into the Parameters collection should
  be exactly identical. The Positional Parameter
  Mapping is indicated with a question mark ?.
• Both SQL Server and Oracle Data Provider
  used the Named Parameter Mapping, which means
  that each parameter, either defined in an SQL
  statement or added into a Parameters collection,
  is identified by the name. In other words, the
  name of the parameter appeared in an SQL
  statement or a stored procedure must be identical
  with the name of the parameter you added into a
  Parameters collection.

38
  3.4.3.4   The Methods of the
ParameterCollection Class

• To add Parameter objects to an Parameters
  collection of a Command object, two popular ways
  are generally adopted, Add() method and
  AddWithValue() method.
• The Add() method is an overloaded method and it
  has five different protocols, but only two of
  them are widely used. The protocols of these two
  methods are shown below.
•  
• ParameterCollection.Add( value As SqlParameter )
  As SqlParameter
• ParameterCollection.Add( paramName As String,
  Value As Object )
• The AddWithValue() method is similar to the
  second Add() method with the following protocol
•  
• ParameterCollection.AddWithValue( paramName As
  String, Value As Object )

39
 Questions and Answers

40

• for creating distributed ????? ??????
• Integral ?? ?????
• are core to develop a professional data-driven
  application ?? ?????? ???? ???????? ???????
  ?????? ?????
• Data Provider-dependent components. ???? ??????
  ?????
• ??? ????????
• Functionalities ?????
• a table container ????? ??????
• sake of optimal ???? ??? ????? ??????
• highly recommended ????
• Quite ?????
• self-explanatory ?????
• Credentials ????? ??????

41
(No Transcript)