Connections, Data Adapters, and Datasets

This chapter begins our database work, a very big topic in C#. In fact, Microsoft says that most Visual Studio work involves databases. Database handling is an extensive subject in C# programming, so we'll spend this and the next chapter on it. As with the rest of the book, this will be a programmer-to-programmer discussion, so we're going to omit some of the basics; for example, we're going to assume you're familiar with what a database is and at least introductory SQL here.

There are two ways to work with databases in C#using the IDE's visual tools, and in code. In this chapter, we're going to work with the visual tools, and in the next chapter, we'll handle databases exclusively in code.

When you work with databases in C#, you work with three objects: connection objects, data adapters, and dataset objects. To work with databases, you have to master all three of these objects.

Connection objects, data adapters, and dataset objects are the foundation of ADO.NET (ActiveX Data Objects .NET), the primary data access protocol in C#. We'll start this chapter with an overview of these objects, working through how to connect to a data source step by step. Say, for example, that you have a database you want to gain access to in your code. You start by getting a connection object for the database, and that's where we'll start in this chapter.

Using Connection Objects

To work with data, you first connect to a data source (such as a database made accessible by a data server, like Microsoft's SQL Server). To do that, you use a connection object . The type of connection object you use depends on which database provider you're working with. There are four types of connection objects in C#:

• SqlConnection objects are supported by the .NET Framework Data Provider for SQL Server and are recommended for applications using Microsoft SQL Server 7.0 or later.

• OleDbConnection objects are supported by the .NET Framework Data Provider for Object Linking and Embedding Database protocol (OLE DB) and are recommended for applications using Microsoft SQL Server 6.5 or earlier, or any OLE DB provider that supports the OLE DB interfaces.

• OdbcConnection objects are supported by the .NET Framework Data Provider for Open Database Connectivity (ODBC, which is a data protocol supported in earlier versions of Visual Studio). These objects are recommended for applications using ODBC data sources.

• OracleConnection objects are supported by the .NET Framework Data Provider for Oracle and are recommended for applications using Oracle data sources.

By default, the data provider that Visual Studio works with is Microsoft's SQL Server, version 7.0 or later. We'll use SQL Server 2000 in this book, but C# can also work with any data provider that can support ODBC, OLE DB, or Oracle protocols. (Note that even if you don't have a high-end database system to work with, you can still connect to databases created with MS Access, which comes with MS Office.)

EARLIER VERSIONS OF .NET AND C#

Note that the Oracle classes like OracleConnection and the ODBC classes like OdbcConnection are built into .NET Framework 1.1 and Visual Studio .NET 2003. They're also available via free download from Microsoft for .NET Framework 1.0 and Visual Studio .NET 2002. In this book, we're not going to be using the Oracle or ODBC classes in any case.

Using Data Adapters

When you have a connection object for a data source, you next create a data adapter to work with that data. You need a data adapter because datasets do not maintain an active connection to the database. They are disconnected from the database and connect only as needed (which enables you, for example, to work with databases on the Internet). The data adapter is in charge of managing your connection to the data sourceyou're not connected to the data source by default, and the data adapter connects you to the data source when you need to be connected.

The data adapter is where you store the SQL statements that will be executed on the data in the database; the data adapter applies your SQL to the database when you need to connect to that database, and it fills your datasets with data. To access the data in a database's table, you first create a connection to the database the table was stored in, and then create a data adapter with the appropriate SQL statement to retrieve that table. For example, if the table was named prices , that SQL might be SELECT * FROM prices , which retrieves all the records from the students table.

The type of data adapter you use depends on the type of your data source; the possibilities are SqlDataAdapter , OleDbDataAdapter , OdbcDataAdapter , and OracleDataAdapter .

Using Datasets

Now that you have a data adapter, you can generate a dataset using that data adapter. Datasets are local repositories for the data you get from the data source through the data adapter. They're what you actually work with in your code when you want to handle data from a database.

You can use a data adapter to fill a DataSet object. Then you can use the methods of the DataSet object to read the data from individual records. If you change the data in a dataset, you can call the data adapter's Update method to send those changes back to the data provider.

There are three essential objects you need to understand in order to work with databases using ADO.NET: connections to connect to the database, data adapters to connect when needed and execute SQL, and datasets to store the actual data which your code will actually work on. That's how the process works in overview. There are plenty of other objects available in ADO.NET, but these are the fundamental ones.