Teach Yourself Visual Studio® .NET 2003 in 21 Days [Electronic resources] نسخه متنی

Looking Inside ADO.NET

In the .NET Framework class library (FCL), the System.Data namespace has all the classes you need to access any type of database or data store. Before .NET, you used ADO to accomplish your data access tasks. ADO was a great technology for data access, but it wasn't designed for disconnected environments such as the Internet. ADO.NET was written from the ground up to include robust support for disconnected applications. The good news for developers is that ADO and ADO.NET have similar syntaxes, so learning how to code ADO.NET will be a snap if you've ever used ADO.

Every data access scenario includes several common operations when accessing databases:

Tip

The System.Data.SqlClient namespace speaks the same language that SQL Server speaks, and that's where the performance gains come from when using ADO.NET versus ADO when accessing SQL Server. SQL Server natively uses the tabular data stream (TDS) protocol to read and write data from SQL Server to a stream that's sent over the wire to your applications. In .NET, the SqlClient namespace uses the TDS to communicate directly with SQL Server, so there's no overhead in the conversion between the data access code you write and SQL Server consuming those commands. In the past, this created overhead by using unmanaged COM providers. This overhead no longer exists in .NET with the SqlClient namespace.

No matter what namespace you use, SqlClient or OleDb, you must still perform the same tasks to work with any database. You get a connection, execute a command, and either get or set data values. The classes in the System.Data.SqlClient namespace are similar in name and functionality to the classes in the System.Data.OleDb namespace. For example, to get a Connection object, you use the System.Data.SqlClient.SqlConnection class if you're accessing SQL Server. For OLE DB, you use the System.Data.OleDb.OleDbConnection class. The prefix of Sql or OleDb is the only difference in the syntax or functionality. For this reason, it's easy to switch between namespaces if the type of database you're using ever changes to or from SQL Server.

Note

When I refer to SQL Server throughout today's lesson, I'm talking about SQL Server 7.0 and 2000. For SQL Server 6.5 and earlier, you must use the OLE DB namespace to access data.

If you're familiar with the objects in ADO, Figure 10.1 gives you a better understanding of the mapping of ADO to ADO.NET objects.

Figure 10.1. Mapping ADO objects to ADO.NET objects.









As Figure 10.1 demonstrates, ADO.NET has a few more core objects than ADO. These additional objects give you greater flexibility in designing your applications.

The Connection object is still the same. You set properties on the object and call an Open method to connect to the data source.

The Command object is also practically the same as it was in ADO. You create a Command object to actually hold your SQL statements that select records, insert records, update records, and delete records in the database. The Command object in ADO.NET also supports a Parameters collection that makes it very easy to support parameterized ad hoc queries and stored procedures with input and output parameters.

The DataReader object is for read-only, forward-only data access. This class can be compared to a ForwardOnly cursor in ADO. If you need data as fast as possible with no overhead and that data can be read-only and doesn't need to support paging, a DataReader is the answer.

The DataAdapter is a new ADO.NET object and has no direct correlation to an ADO object. DataAdapters are the Swiss Army Knives of data access objects. The DataAdapter sits between the database and a DataSet object. The DataAdapter's purpose is to be the connected part of a disconnected environment. DataAdapters have InsertCommand, UpdateCommand, SelectCommand, and DeleteCommand properties that enable you to specify how to get or set values in the database. The DataAdapter is responsible for keeping track of the original data that you received from the database and updating any changes that were made since you last connected.

The DataSet is also a brand-new object that doesn't directly map to an ADO object. A DataSet is an in-memory representation of data. The DataSet doesn't directly connect to a database; it's either created dynamically in code or filled with information. DataSets can hold simple, nontyped data from a database or extensible markup language (XML) file, or they can hold complex strongly typed relational data from a database, XML file, or data generated dynamically. DataSets are used in conjunction with DataAdapters. The DataAdapter provides the Connection and Command objects, and the DataSet provides a place for the data to go.

You work with each of these objects in more detail throughout the day.

Note

In ADO.NET, there's no concept of a server-side cursor. A cursor is what a database uses to keep track of positioning in a set of records. In ADO, you can specify client-side cursors or server-side cursors. If a cursor is client side, the client side memory is used to keep positional track of records for operations such as moving to the first record, last record, previous record, or next record. If a cursor is server side, the database server keeps track of the positional information for a set of records. Each time a connection is made with the cursor option set to server side, the database server has to consume more resources. If you have many tens or hundreds of users, your database server has to work itself to death just to keep up with the demand of doing all the work server side. In ADO.NET, the server-side cursor doesn't exist, which prevents you from accidentally using cursors on the server.

What Does Disconnected Mean?




I mentioned earlier that ADO.NET is Day 12, "Accessing XML in .NET," you learn how to access XML using the XML classes on the FCL. On Day 13, you learn how to create and consume XML Web services in .NET. I don't want to minimize the importance and flexibility that XML gives you in the .NET Frameworkit's the underlying mechanism through which almost everything communicates. But I want to stress that you don't need to know anything about XML to be productive in .NET.

To give you an idea of how the disconnected model looks, Figure 10.2 shows you how the DataReader, DataAdapter, and DataSet objects fit into data access in ADO.NET.

Figure 10.2. Data access architecture of ADO.NET.









Databinding is mentioned for the first time in Figure 10.2. In Visual Basic 6 and ASP, databinding was a bad word. There was a lot of overhead and you always needed some sort of data connection to implement databinding. In .NET, databinding is encouraged. The databinding on Windows Forms applications and Web Forms applications is a robust and scalable architecture to use for your presentation tier. You should use databinding wherever possible.

Today, you'll use databinding on Windows Forms first, using DataAdapters with DataSets and DataViews, and then you'll see how to databind controls on Web Forms using DataReaders.

Note

You can't use databinding on Windows Forms using DataReaders. If you plan to write an application that uses both Windows Forms and Web Forms, you must implement DataSets, not DataReaders. With DataSets, you can share the code between Windows Forms and Web Forms.