Chapter 1
After completing this chapter, you will be able to:
■ Identify what ADO.NET is
■ Explain ADO.NET’s role in an application
■ Identify the major components that make up ADO.NET
■ Create an ADO.NET link between a database and a .NET application
This chapter introduces you to ADO.NET and its purpose in the world of Microsoft .NET application development. ADO.NET has been included with the .NET Framework since its initial release in 2002, playing a central role in the development of both desktop and Internet-targeted applications for programmers using C#, Visual Basic, and other Framework languages.
first learn of ADO.NET’s multiple object layers, its dozens of general and platform-specific classes, and its myriad options for interacting with actual data. Applications written using the .NET Framework depend on .NET class libraries, which exist in special DLL files that encapsulate common programming functionality in an easy-to-access format. Most of the libraries supplied with the .NET Framework appear within the System
namespace. System.IO, for instance, includes classes that let you interact with standard disk files and related data streams. The System.Security library provides access to, among other things, data encryption features. ADO.NET, expressed through the System.Data namespace, implements a small set of libraries that makes consuming and manipulating large amounts of data simple and straightforward.
ADO.NET manages both internal data—data created in memory and used solely within an application—and external data—data housed in a storage area apart from the application, such as in a relational database or text file. Regardless of the source, ADO.NET generalizes the relevant data and presents it to your code in spreadsheet–style rows and columns.
Note:- Although ADO.NET manipulates data in tabular form, you can also use ADO.NET to access non-tabular data. For instance, an ADO.NET provider (discussed later in the chapter, on page 7) could supply access to hierarchical data such as that found in the Windows Registry, as long as that provider expressed the data in a tabular structure for ADO.NET’s use. Accessing such non-tabular data is beyond the scope of this book.
If you are already familiar with relational databases such as Microsoft SQL Server, you will encounter many familiar terms in ADO.NET. Tables, rows, columns, relations, views; these ADO.NET concepts are based loosely on their relational database counterparts. Despite these similarities, ADO.NET is not a relational database because it doesn’t include key “relational algebra” features typically found in robust database systems. It also lacks many of the common support features of such databases, including indexes, stored procedures, and triggers. Still, if you limit yourself to basic create, read, update, and delete (CRUD) operations, ADO.NET can act like a miniature yet powerful in-memory database.
As an acronym, “ADO.NET” stands for—nothing. Just like the words “scuba,” “laser,” and “NT” in Windows NT, the capital letters in ADO.NET used to mean something, but now it is just a standalone term. Before Microsoft released the .NET Framework, one of the primary data access tools Windows developers used in their programs was known as ADO, which did stand for something: ActiveX Data Objects. After .NET arrived on the scene, ADO.NET became the natural successor to ADO. Although conceptual parallels exist between ADO.NET and ADO, the technologies are distinct and incompatible.
Note:- ADO is based on Microsoft’s older COM technology. The .NET Framework provides support for COM components, and therefore enables .NET programs to use ADO. This is especially useful for development teams transitioning legacy applications to .NET. Although ADO and ADO.NET components can appear in the same application, they can interact only indirectly because their object libraries are unrelated.
When communicating with external data stores, ADO.NET presents a disconnected data experience. In earlier data platforms, including ADO, software developers would typically establish a persistent connection with a database and use various forms of record locking to manage safe and accurate data updates. But then along came the Internet and its browser-centric view of information. Maintaining a long-standing data connection through bursts of HTTP text content was no longer a realistic expectation. ADO.NET’s preference toward on-again, off-again database connections reflects this reality. Although this paradigm change brought with it difficulties for traditional client-server application developers, it also helped usher in the era of massive scalability and n-tier development that is now common to both desktop and Web-based systems.
Introducing ADO.NET 4
After completing this chapter, you will be able to:
■ Identify what ADO.NET is
■ Explain ADO.NET’s role in an application
■ Identify the major components that make up ADO.NET
■ Create an ADO.NET link between a database and a .NET application
This chapter introduces you to ADO.NET and its purpose in the world of Microsoft .NET application development. ADO.NET has been included with the .NET Framework since its initial release in 2002, playing a central role in the development of both desktop and Internet-targeted applications for programmers using C#, Visual Basic, and other Framework languages.
What Is ADO.NET?
ADO.NET is a family of technologies that allows .NET developers to interact with data in standard, structured, and primarily disconnected ways. If that sounds confusing, don’t worry. This blog exists to remove the confusion and anxiety that many developers experience when theyfirst learn of ADO.NET’s multiple object layers, its dozens of general and platform-specific classes, and its myriad options for interacting with actual data. Applications written using the .NET Framework depend on .NET class libraries, which exist in special DLL files that encapsulate common programming functionality in an easy-to-access format. Most of the libraries supplied with the .NET Framework appear within the System
namespace. System.IO, for instance, includes classes that let you interact with standard disk files and related data streams. The System.Security library provides access to, among other things, data encryption features. ADO.NET, expressed through the System.Data namespace, implements a small set of libraries that makes consuming and manipulating large amounts of data simple and straightforward.
ADO.NET manages both internal data—data created in memory and used solely within an application—and external data—data housed in a storage area apart from the application, such as in a relational database or text file. Regardless of the source, ADO.NET generalizes the relevant data and presents it to your code in spreadsheet–style rows and columns.
Note:- Although ADO.NET manipulates data in tabular form, you can also use ADO.NET to access non-tabular data. For instance, an ADO.NET provider (discussed later in the chapter, on page 7) could supply access to hierarchical data such as that found in the Windows Registry, as long as that provider expressed the data in a tabular structure for ADO.NET’s use. Accessing such non-tabular data is beyond the scope of this book.
If you are already familiar with relational databases such as Microsoft SQL Server, you will encounter many familiar terms in ADO.NET. Tables, rows, columns, relations, views; these ADO.NET concepts are based loosely on their relational database counterparts. Despite these similarities, ADO.NET is not a relational database because it doesn’t include key “relational algebra” features typically found in robust database systems. It also lacks many of the common support features of such databases, including indexes, stored procedures, and triggers. Still, if you limit yourself to basic create, read, update, and delete (CRUD) operations, ADO.NET can act like a miniature yet powerful in-memory database.
As an acronym, “ADO.NET” stands for—nothing. Just like the words “scuba,” “laser,” and “NT” in Windows NT, the capital letters in ADO.NET used to mean something, but now it is just a standalone term. Before Microsoft released the .NET Framework, one of the primary data access tools Windows developers used in their programs was known as ADO, which did stand for something: ActiveX Data Objects. After .NET arrived on the scene, ADO.NET became the natural successor to ADO. Although conceptual parallels exist between ADO.NET and ADO, the technologies are distinct and incompatible.
Note:- ADO is based on Microsoft’s older COM technology. The .NET Framework provides support for COM components, and therefore enables .NET programs to use ADO. This is especially useful for development teams transitioning legacy applications to .NET. Although ADO and ADO.NET components can appear in the same application, they can interact only indirectly because their object libraries are unrelated.
When communicating with external data stores, ADO.NET presents a disconnected data experience. In earlier data platforms, including ADO, software developers would typically establish a persistent connection with a database and use various forms of record locking to manage safe and accurate data updates. But then along came the Internet and its browser-centric view of information. Maintaining a long-standing data connection through bursts of HTTP text content was no longer a realistic expectation. ADO.NET’s preference toward on-again, off-again database connections reflects this reality. Although this paradigm change brought with it difficulties for traditional client-server application developers, it also helped usher in the era of massive scalability and n-tier development that is now common to both desktop and Web-based systems.
Why ADO.NET?
In the early days of computer programming, the need for a data library like ADO.NET didn’t exist. Programmers had only a single method of accessing data: direct interaction with the values in memory. Permanently stored data existed on tape reels in fire-resistant, climate-controlled, raised-floor rooms. Data queries could take hours, especially if someone with more clout had a higher-priority processing need.
Over time, computers increased in complexity, and (as if to fill some eternal maxim) data processing needs also expanded to consume all available computing resources. Businesses sought easier ways to manage entire records of numeric, text, and date-time values on their
mainframe systems. Flat-file and relational database systems sprang up to establish proprietary management of millions of data values. As personal computers arrived and matured, developers soon had several database systems at their disposal.
This was great news for data consumers. Businesses and individuals now had powerful tools to transform data bits into usable information, to endow seemingly unrelated values with meaning and purpose. But it was bad news for developers. As technology marched on, companies
purchased one proprietary system after another. Programming against such systems meant a reinvention of the proverbial wheel each time a middle manager asked for yet another one-time report. Even the standard SQL language brought little relief because each database vendor provided its own spin on the meaning of “standard.”
What programmers needed was a way to generalize different data systems in a standard, consistent, and powerful way. In the world of .NET application development, Microsoft ADO.NET meets that need. Instead of worrying about the minutiae associated with the different database systems, programmers using ADO.NET focus on the data content itself.
Major Components of ADO.NET
The System.Data namespace includes many distinct ADO.NET classes that work together to provide access to tabular data. The library includes two major groups of classes: those that manage the actual data within the software and those that communicate with external data systems. Figure 1-1 shows the major parts that make up an ADO.NET instance.
At the data-shaped heart of the library is the DataTable. Similar in purpose to tables in a database, the DataTable manages all the actual data values that you and your source code ultimately care about. Each DataTable contains zero or more rows of data, with the individual data values of each row identified by the table’s column definitions.
■ Each table defines DataColumn items, each representing the individual data values that appear in the table’s records. DataColumn definitions include a data type declaration based on the kind of data destined for each column. For instance, a CustomerLastName column might be defined to use data of type System.String, whereas an OrderSalesTax column could be crafted for use with System.Decimal content.
■ One DataRow entry exists for each record of data stored within a table, providing access to the distinct columnar data values. ADO.NET includes methods that let you add to, delete from, modify, and query each DataTable object’s rows. For tables connected to an external data storage area, any changes made can be propagated back to the source.
■ You can optionally establish links between the tables of data using DataRelation entries.
Programmatic limitations can be placed on tables and their data values using Constraint instances.
■ DataView instances provide a limited or modified view of the rows in a DataTable.
■ Tables can be grouped together into a DataSet. Some tools that interact with ADO.NET data require that any tables be bound within a DataSet, but if you plan to do some limited work with only a single table, it’s fine to work with just the DataTable instance.
DataTable instances and their associated objects are sufficient for working with internal data. To connect with external data from a database, ADO.NET features multiple data providers, including a custom provider for Microsoft SQL Server. Database platforms without a specific
provider use the more generic ODBC and OLE DB providers, both included with ADO.NET. Several third-party providers can be purchased or obtained free of charge, which target specific platforms, including Oracle.
■ All communication with the external data source occurs through a Connection object. ADO.NET supports connection pooling for increased efficiency between queries.
■ SQL queries and data management statements get wrapped in a Command object before being sent to the data source. Commands can include optional Parameter instances that let you call stored procedures or create fill-in-the-blank queries.
■ The DataAdapter object stores standard query definitions for interacting with a database, removing the tedium of constantly needing to build SQL statements for each record you want to read or write, and helping to automate some ADO.NET-related tasks.
■ The DataReader object provides fast, read-only access to the results of a query for those times when you just need to get your data quickly.
ADO.NET also includes features that let you save an entire DataSet as an XML file and load it back in later. And that’s just the start. You’ll learn how to use all these elements—and more—throughout the upcoming chapters.
15. For this test, include the Customer table in the DataSet by expanding the Tables section and marking the Customer table with a check mark.
You can optionally modify the DataSet Name field to something that will be easier to repeatedly type in your source code. Click Finish to exit the wizard and create the data source. The data source is now available for use in your application.
16. Select Data | Show Data Sources from the Visual Studio menu to see the data source. The wizard also added a new .xsd file to your project; it appears in the Solution Explorer with your other project files. This XML file contains the actual definition of the data source. Removing this file from the project removes the Wizard-created data source.
Visual Studio also lets you preview the data records within the data source.
17. Select Data | Preview Data from the Visual Studio menu to open the Preview Data dialog box.
The menu choice might be hidden depending on what is currently active in the Visual Studio IDE. If that menu choice does not appear, click the form in the design window and then try to select the menu item again.
direct ties to specific ADO.NET classes and class members. As a .NET developer, you already have a core understanding of how ADO.NET can be used in an application because everything in the library is expressed as standard .NET objects. The only things you still need to learn are some of the details that are specific to ADO.NET—the very subjects covered in the rest of this book.
Extensions to ADO.NET
Generalizing access to data is a key benefit of using ADO.NET. But an even greater advantage for .NET developers is that all values managed through ADO.NET appear as objects, first-class members of the .NET data world. Each data field in a table is a strongly typed data member,
fully compliant with .NET’s Common Type System. Individual fields can be used just like any other local variable. Data rows and other sets of objects are standard .NET collections and can be processed using standard iteration methods.
Because ADO.NET values exist as true .NET objects and collections, Microsoft has enhanced the core ADO.NET feature set with new tools. Two of these technologies, the Entity Framework and LINQ, are not formally part of ADO.NET. But their capability to interact with and enhance the ADO.NET experience makes them essential topics for study.
The Entity Framework, the focus of Part III of this book, emphasizes the conceptual view of your data. Although the data classes in ADO.NET are programmer-friendly, you still need to keep track of primary keys and relationships between tables and fields. The Entity Framework
attempts to hide that messiness, and restores the promise of what object-oriented programming was supposed to be all about. In the Entity Framework, a customer object includes its orders; each order includes line item details. Instead of working with the raw table data, you
interact with logically designed entities that mimic their real-world counterparts, and let the Framework worry about translating it all into SQL statements.
LINQ, introduced in Part IV, brings the joy of English-like queries to your favorite programming language. Microsoft enhanced both Visual Basic and C# with new LINQ-specific language features. Now, instead of building string-based SQL statements to query data, the syntax of each programming language becomes the query language. LINQ is a generic data tool, enabling you to easily mix ADO.NET data and other content sources together into a single set of results.
Connecting to External Data
Chapter 8, “Establishing External Connections,” introduces the code elements that support communications between ADO.NET and external sources of data. Although using only code to establish these connections is quite common, Visual Studio also includes the Data Source Connection Wizard, a mouse-friendly tool that guides you through the creation of a ready-to-use DataSet. Here’s an example of using the Connection Wizard.
Creating a Data Source Using the Connection Wizard
1. Start Visual Studio 2010. Select File | New | Project from the main menu.
ADO.NET is supported in most common project types. To keep things simple for now, create a Windows Forms application using either C# or Visual Basic as the language.
The following figures show the process using a Visual Basic Windows Forms application, although the steps are identical in C#.
2. In the New Project dialog box, provide a name for the project.
3. Click OK.
Visual Studio will create a project.
4. Select Data | Add New Data Source from the menu.
Visual Studio displays the Data Source Configuration Wizard.
The Database choice should already be selected in the Choose A Data Source Type panel.
5. Click Next.
6. In the Choose a Database Model panel, choose Dataset.
7 . Click Next
7. Click Next.
The Wizard displays the Choose Your Data Connection panel. If you previously configured data sources, they will appear in the Which Data Connection Should Your Application Use To Connect To The Database? list.
8. Because you are setting up a connection to the test database for the first time, click the New Connection button.
9. When the Choose Data Source dialog box appears, select Microsoft SQL Server from the Data Source list.
The Data Provider field will automatically choose the SQL Server data provider. For maximum flexibility, clear the Always Use This Selection field.
Note:- Choosing Microsoft SQL Server will access a database that has previously been attached to a SQL Server database instance. To create a data source that directly references a database file not necessarily attached to the engine instance, select Microsoft SQL Server Database File from the Data Source list instead. The wizard will then prompt you for the disk location of the file.
10. Click Continue to accept the data source.
11. In the Add Connection dialog box, select the server from the Server Name field.
For SQL Server 2008 Express Edition instances, this is typically the name of the local computer with \SQLEXPRESS appended to the name. If you are using the full SQL Server product, leave off the \SQLEXPRESS suffix. For SQL Server instances hosted on
the same system as your Visual Studio installation, you can use (local) as the server name.
For SQL Server instances configured with database-managed authentication, select Use SQL Server Authentication and supply the appropriate user name and password. For databases managed with Windows authentication (the likely choice for the test database),
select Use Windows Authentication instead.
The Select Or Enter a Database Name field should now include the available databases within the test database file. (If not, confirm that you have supplied the right server name and authentication values and that SQL Server is running on your system.)
12. Select StepSample (or the name of your primary test database) from the list.
Then click OK to complete the connection configuration.
Control returns to the wizard with the new data connection selected in the list on the Choose Your Data Connection panel.
Note:- ADO.NET uses connection strings, short, semicolon-delimited definition strings, to identify the data source. As you develop new applications, you will probably forgo the Data Source Configuration Wizard as a means of building connection strings. If you are curious about what appears in a connection string, expand the Connection String field in the Choose Your Data Connection panel.
13. Click the Next button to continue.
The next wizard panel asks if the connection string should be stored in the application’s configuration file. The field should already be selected, which is good, although you might want to give it a more programmer-friendly name.
Note:- .NET applications use two types of configuration files (although it varies by project type): application configuration files and user configuration files. Although your application has access to the settings in both files, if you plan to include a feature in your program that modifies these saved settings, make sure that you place such settings in the user configuration file. Application configuration files can’t be modified from within the associated application.
14. Click the Next button once more to continue.
SQL Server will perform a quick analysis of your database, compiling a list of all available data-exposing items, including tables, views, stored procedures, and functions. The Choose Your Database Objects panel displays all items found during this discovery process.
15. For this test, include the Customer table in the DataSet by expanding the Tables section and marking the Customer table with a check mark.
You can optionally modify the DataSet Name field to something that will be easier to repeatedly type in your source code. Click Finish to exit the wizard and create the data source. The data source is now available for use in your application.
16. Select Data | Show Data Sources from the Visual Studio menu to see the data source. The wizard also added a new .xsd file to your project; it appears in the Solution Explorer with your other project files. This XML file contains the actual definition of the data source. Removing this file from the project removes the Wizard-created data source.
Visual Studio also lets you preview the data records within the data source.
17. Select Data | Preview Data from the Visual Studio menu to open the Preview Data dialog box.
The menu choice might be hidden depending on what is currently active in the Visual Studio IDE. If that menu choice does not appear, click the form in the design window and then try to select the menu item again.
Summary
This chapter provided an overview of Microsoft’s ADO.NET technology and its major data management components. At its heart, computer programming is all about data manipulation, whether the data values represent customer records, characters and objects in a 3D interactive video game, or the bits in a compressed audio file. With this inherent focus on data, it makes sense that Microsoft would provide a great tool for interacting with tabular data, one of the most useful ways of organizing data, especially in a business setting. As you will see in upcoming chapters, the concepts included in this opening chapter havedirect ties to specific ADO.NET classes and class members. As a .NET developer, you already have a core understanding of how ADO.NET can be used in an application because everything in the library is expressed as standard .NET objects. The only things you still need to learn are some of the details that are specific to ADO.NET—the very subjects covered in the rest of this book.