This chapter is from the book
This chapter is from the book
This chapter is from the book
Connecting to the Data Source
For this lesson, you will be using a database that is installed by default when you install Internet Information Services (IIS) in Windows 2000 and Windows XP. If you do not have IIS installed, you should either install it now or use the database contained within the code for this hour, which can be downloaded from the Sams Web site. The database itself contains only a single table. Within this table are records corresponding to several author names and the years they were born. There is also an associated ID for each of the authors. If you have IIS installed, you can open the database in Microsoft Access, which is a part of Microsoft Office. By default, the database is located in the IIS folder in the iissamples\sdk\asp\database subdirectory.
The first step in using a database within your application is obviously to connect to it. Create a member function within your form class called OpenDataSource. Because this is just a test application, you can forgo any error checking by just returning void and not specifying any parameters for the function.
The first step in connecting to a data source is to create the main DataSet object. This object is your main access point to all the tables contained within as well as the specific records within those tables. Create a private DataSet member variable within your form class and create an instance of this object within the OpenDataSource function, as shown in Listing 20.4. There are two possible constructors for this object. The one used here accepts a string parameter. This is purely optional and allows you to specify a specific name for the DataSet.
Listing 20.4 Connecting to the Data Source and Filling the DataSet Object
1: void AuthorDBForm::OpenDataSource()
2: {
3: // create data set
4: m_pDataSet = new DataSet( "Author Database" );
5:
6: // create data source connection
7: m_pAuthorsDB = new OleDbConnection(
8: S"Provider=Microsoft.Jet.OLEDB.4.0;
9: Data Source=
10: C:\\Inetpub\\iissamples\\sdk\\asp\\database\\Authors.mdb;");
11:
12: // create selection command
13: OleDbCommand* pSelectCMD = new OleDbCommand(
14: S"SELECT * FROM Authors ORDER BY AU_ID", m_pAuthorsDB );
15: pSelectCMD->CommandTimeout = 30;
16:
17: // create the ole data adapter
18: m_pDA = new OleDbDataAdapter();
19: m_pDA->SelectCommand = pSelectCMD;
20:
21: // fill the dataset with the data source
22: m_pDA->Fill( m_pDataSet, "Authors" );
23:
24: // save the author table pointer
25: m_pAuthorTable = m_pDataSet->get_Tables()->get_Item("Authors");
26:
27: // set primary key to author id
28: DataColumn *myColArray[] =
29: {m_pAuthorTable->get_Columns()->get_Item("AU_ID")};
30: m_pAuthorTable->set_PrimaryKey( myColArray );
31: }
As mentioned earlier, DataSet is the main object within ADO.NET. In fact, with the DataSet object that you just created, you can manually add new tables and insert new data without having to use any type of database. This is useful because it can give you a way to internally organize data within an application without having to create your own data-persistence scheme. Once you are finished, you can save the data to an XML file and then read the persisted data back in when your application is restarted. For this lesson, however, you will be working with data that is already contained within a database.
The next step is to connect to the data source and specify the command you want to run on the data source to retrieve the data. Connecting to a data source involves the use of the OleDBConnection object. This object is used regardless of the format of the database. In other words, if you plan on connecting to a Structured Query Language (SQL) database, you would use the same object (OleDBConnection) as you use for an Access database. In fact, you will soon realize that most of the data objects within the .NET Framework are used for differing types of databases. The difference lies within an object known as the data adapter. A data adapter is specialized to work with the format of a specific database. The .NET Framework currently ships with two data adapters: OleDbDataAdapter and SqlDataAdapter.
For this lesson, you will be using OleDbDataAdapter. Each data adapter works with two objects to access a database: the OleDBConnection object, responsible for opening the database, and the OleDbCommand object. The OleDbCommand object is used to specify the type of command you want to execute on the data source to retrieve data. This lesson will use a SQL statement to retrieve the database contents. The database you are working with contains a single table named Authors. Because you are interested in all the data, you can use a SQL command that selects all rows and columns within the Authors table, as shown on line 13 of Listing 20.4. Also, in order to present the data in an ordered manner, you should order it by the ID assigned to each individual author.
Even though the connection and command objects have been associated with the data adapter, a connection to the database still has not been made. The goal of a disconnected client scenario is to eliminate as much connection to the database as is possible. Even though you have specified which type of connection to use and which command to run, you haven't written the code to fill the DataSet with that data. Therefore, there is no need to connect to the data source. To fill a DataSet with the data within the database, you can use the data adapter's Fill method, as shown on line 22 of Listing 12.4. The Fill method accepts two parameters: a pointer to a DataSet, which is the DataSet you created earlier, and the name of the source table you wish to retrieve data from, which in this case is the Authors table.
The last portion of the OpenDataSource function does two things. The first is a shortcut to the table contained within the DataSet. Create a pointer to a DataTable type within the private section of your class and retrieve the Authors table from the DataSet, as shown on line 25 of Listing 12.4. This isn't a necessary step, but it will save you from having to access the Author table from the DataSet each time you need to retrieve data. The last portion of the OpenDataSource function is used to set a primary key. A primary key within a table is used to specify the column whose values are unique across each of the rows. If you were to add a row later that contained the same ID as another row, an exception would be thrown.
The last step before you can check to see whether your application compiles correctly is to place the OpenDataSource function call within the constructor of your form. Place this call before your call to the InitForm function call. Once you finish that, compile your application. If you run your application, one of two things may happen: It may run the Windows Form just fine, or an exception may be thrown. If an exception is thrown, as shown in Figure 20.3, it may be the fact that the data source could not be found. If this happens, navigate to the directory you specified for the OleDbConnection object and ensure that the database exists at that location.
){kind=link}
Figure 20.3 Specifying an invalid location for your data source will throw an exception.