Chapter 5: Database Access with ADO

[Pages:17]Chapter 5: Database Access with

Difference between ADO and

ADO

1.It

is

a

COM

based

library.

2.Classic ADO requires active connection with the data store.

3.Locking

feature

is

available.

4.Data

is

stored

in

binary

format.

5.XML

integration

is

not

possible.

6.It uses the object named Recordset to reference data from the data store.

7.Using Classic ADO, we can obtain information from one table or set of tables through

join. We cannot fetch records from multiple tables independently.

8.Firewall

might

prevent

execution

of

Classic

ADO.

9.Classic ADO architecture includes client side cursor and server side cursor.

10.We cannot send multiple transactions using a single connection instance.



1.It

is

a

CLR

based

library.

2. architecture works while the data store is disconnected.

3.Locking

feature

is

not

available.

4.Data

is

stored

in

XML.

5.XML

integration

is

possible.

6.It uses Dataset Object for data access and representation.

7.Dataset object of includes collection of DataTables wherein each DataTable

will contain records fetched from a particular table. Hence multiple table records are

maintained

independently.

8. has firewall proof and its execution will never be interrupted.

9.

architecture

doesn't

include

such

cursors.

10.We can send multiple transactions using a single connection instance.

Definition of

? ActiveX Data () is a software library in the .NET framework consisting of software components providing data access services.

? is designed to enable developers to write managed code for obtaining disconnected access to data sources, which can be relational or non-relational (such as XML or application data).

? This feature of helps to create data-sharing, distributed applications

1

The Two Faces of

? The libraries can be used in two conceptually unique manners: connected or disconnected.

? Connected architecture: the application remains connected with the database

throughout

the

processing.

? Disconnected architecture: the application automatically connects/disconnects during the processing. The application uses temporary data on the application side called a DataSet.

? When you are making use of the connected layer, your code base will explicitly connect to and disconnect from the underlying data store.

? When you are using in this manner, you typically interact with the data store using connection objects, command objects, and data reader objects.

? As you will see later in this chapter, data readers provide a way to pull records from a data store using a forward-only, read-only approach (much like a fire-hose cursor).

? The disconnected layer, on the other hand, allows you to obtain a set of DataTable objects (contained within a DataSet) that functions as a client-side copy of the external data.

? When you obtain a DataSet using a related data adapter object, the connection is automatically opened and closed on your behalf.

Understanding Data Providers

? does not provide a single set of types that communicate with multiple database management systems (DBMSs).

? Rather, supports multiple data providers, each of which is optimized to interact with a specific DBMS.

? The first benefit of this approach is that a specific data provider can be programmed to access any unique features of the DBMS.

? Another benefit is that a specific data provider is able to directly connect to the underlying engine of the DBMS without an intermediate mapping layer standing between the tiers.

? SimplySimply put, a data provider is a set of types defined in a given namespace that understand how to communicate with a specific data source.

? Regardless of which data provider you make use of, each defines a set of class types that provide core functionality.

2

Figure 22-1 illustrates the big picture behind data providers. Note that in the diagram, the "Client Assembly" can literally be any type of .NET application: console program, Windows Forms application, web page, XML web service, .NET code library, and so on.

3

Microsoft-Supplied Data Providers

? As of version 2.0, Microsoft's .NET distribution ships with numerous data providers, including a provider for Oracle, SQL Server, and ODBC-style connectivity.

? Table 22-2 documents the namespace and containing assembly for each Microsoft data provider.

? The OLE DB data provider, which is composed of the types defined in the System.Data.OleDb namespace, allows you to access data located in any data store that supports the classic COMbased OLE DB protocol.

? Using this provider, you may communicate with any OLE DB?compliant database simply by tweaking the "Provider" segment of your connection string.

? Be aware, however, that the OLE DB provider interacts with various COM objects behind the scenes, which can affect the performance of your application.

? By and large, the OLE DB data provider is only useful if you are interacting with a DBMS that does not define a specific .NET data provider.

? The Microsoft SQL Server data provider offers direct access to Microsoft SQL Server data stores, and only SQL Server data stores (version 7.0 and greater).

? The System.Data.SqlClient namespace contains the types used by the SQL Server provider and offers the same basic functionality as the OLE DB provider.

? The key difference is that the SQL Server provider bypasses the OLE DB layer and thus gives numerous performance benefits. As well, the Microsoft SQL Server data provider allows you to gain access to the unique features of this particular DBMS. 4

Third-Party Data Providers

? In addition to the data providers that ship from Microsoft, numerous third-party data providers exist for various open source and commercial databases.

? Table 22-3 documents where to obtain managed providers for several databases that do not directly ship with Microsoft .NET 2.0

Understanding the Connected Layer of

? the connected layer of allows you to interact with a database using the connection, command, and data reader objects of your data provider.

? When you wish to connect to a database and read the records using a data reader object, you need to perform the following steps:

1. Allocate, configure, and open your connection object. 2. Allocate and configure a command object, specifying the connection object as a constructor argument or via the Connection property. 3. Call ExecuteReader() on the configured command object. 4. Process each record using the Read() method of the data reader.

? In the example program, The goal is to open a connection (via the SqlConnection object) and submit a SQL query (via the SqlCommandobject) to obtain all records within the Inventory table of the Cars database.

? At this point, you will use a SqlDataReader to print out the results using the type indexer. ? Here is the complete code within Main(), with analysis to follow: class Program { static void Main(string[] args) {

Console.WriteLine("***** Fun with Data Readers *****\n"); // Create an open a connection. SqlConnection cn = new SqlConnection(); cn.ConnectionString =

"uid=sa;pwd=;Initial Catalog=Cars; Data Source=(local)";

5

cn.Open(); // Create a SQL command object. string strSQL = "Select * From Inventory"; SqlCommand myCommand = new SqlCommand(strSQL, cn); // Obtain a data reader a la ExecuteReader(). SqlDataReader myDataReader; myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Loop over the results. while (myDataReader.Read()) {

Console.WriteLine("-> Make: {0}, PetName: {1}, Color: {2}.", myDataReader["Make"].ToString().Trim(), myDataReader["PetName"].ToString().Trim(), myDataReader["Color"].ToString().Trim()); } // Because we specified CommandBehavior.CloseConnection, we // don't need to explicitly call Close() on the connection. myDataReader.Close(); } } Working with Connection Objects

? The first step to take when working with a data provider is to establish a session with the data source using the connection object (which, as you recall, derives from DbConnection).

? .NET connection types are provided with a formatted connection string, which contains a number of name/value pairs separated by semicolons.

? This information is used to identify the name of the machine you wish to connect to, required security settings, the name of the database on that machine, and other data provider?specific information.

? As you can infer from the preceding code, the Initial Catalog name refers to the database you are attempting to establish a session with (Pubs, Northwind, Cars, etc.).

? The Data Source name identifies the name of the machine that maintains the database (for simplicity, I have assumed no specific password is required for local system administrators).

? Once your construction string has been established, a call to Open() establishes your connection with the DBMS.

? In addition to the ConnectionString, Open(), and Close() members, a connection object provides a number of members that let you configure attritional settings regarding your connection, such as timeout settings and transactional information.

? Table 22-6 lists some (but not all) members of the DbConnection base class.

6

? As you can see, the properties of the DbConnection type are typically read-only in nature and are only useful when you wish to obtain the characteristics of a connection at runtime.

? When you wish to override default settings, you must alter the construction string itself. ? For example, the connection string sets the connection timeout setting from 15 seconds to 30

seconds (via the Connect Timeout segment of the connection string):

static void Main(string[] args) {

SqlConnection cn = new SqlConnection(); cn.ConnectionString =

"uid=sa;pwd=;Initial Catalog=Cars;" + "Data Source=(local);Connect Timeout=30"; cn.Open(); // New helper function (see below). ShowConnectionStatus(cn); ... } ? In the preceding code, notice you have now passed your connection object as a parameter to a

new static helper method in the Program class named ShowConnectionStatus(), implemented as so:

static void ShowConnectionStatus(DbConnection cn) {

// Show various stats about current connection object. Console.WriteLine("***** Info about your connection *****"); Console.WriteLine("Database location: {0}", cn.DataSource); Console.WriteLine("Database name: {0}", cn.Database); Console.WriteLine("Timeout: {0}", cn.ConnectionTimeout); Console.WriteLine("Connection state: {0}\n", cn.State.ToString());

7

} ? While most of these properties are self-explanatory, the State property is worth special mention. ? Although this property may be assigned any value of the ConnectionState enumeration

public enum System.Data.ConnectionState {

Broken, Closed, Connecting, Executing, Fetching, Open } ? the only valid ConnectionState values are ConnectionState.Open and ConnectionState.Closed

(the remaining members of this enum are reserved for future use). Also, understand that it is always safe to close a connection whose connection state is currently ConnectionState.Closed.

Working with .NET 2.0 ConnectionStringBuilders

? the Microsoft-supplied data providers now support connection string builder objects, which allow you to establish the name/value pairs using strongly typed properties.

? Consider the following Main() method:

static void Main(string[] args) {

// Create a connection string via the builder object. SqlConnectionStringBuilder cnStrBuilder =

new SqlConnectionStringBuilder(); cnStrBuilder.UserID = "sa"; cnStrBuilder.Password = ""; cnStrBuilder.InitialCatalog = "Cars"; cnStrBuilder.DataSource = "(local)"; cnStrBuilder.ConnectTimeout = 30; SqlConnection cn = new SqlConnection(); cn.ConnectionString = cnStrBuilder.ConnectionString; cn.Open(); ShowConnectionStatus(cn); ... } ? In this iteration, you create an instance of SqlConnectionStringBuilder, set the properties

accordingly, and obtain the internal string via the ConnectionString property. ? Also note that you make use of the default constructor of the type. ? If you so choose, you can also create an instance of your data provider's connection string builder

object by passing in an existing connection string as a starting point (which can be helpful when you are reading these values dynamically from an app.config file). ? Once you have hydrated the object with the initial string data, you can change specific name/value pairs using the related properties, for example:

static void Main(string[] args)

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download