Online Web Quiz II: Using the AccessDataSource to Query Microsoft Access in ASP.NET 2.0

Introduction

Did you ever think that technology is changing so quickly, that everything you write will only contain half-truths in 10 years? This certainly seems to be the path that .NET is taking. The .NET library keeps changing and improving at such an accelerated rate, I sometimes wonder if I am wasting time learning one aspect of the technology, when I should be concentrating on the latest and greatest feature in the library. Let's take ADO.NET for example. Remember when this library seemed the way to go when accessing databases? Then along came datasources, which wraps a lot of the complicated functionality. And now LINQ is coming down the pike... Do I really need ADO.NET anymore? Hmmm........

This is a rewrite of the Dynamic Web Quiz Project in ASP.NET 2.0. In this project we chose to replace all of our OleDb objects inside of ADO.NET with AccessDataSources in order to access our Quiz.mdb database on the web. Once you start using these data sources, you'll probably want to say good-bye to OleDb classes. First of all the data source classes work well with Access. Second of all they are less cumbersome and require much less code. Third of all you can declare them inside your web page. Fourth of all, you don't have to worry about all these small details such as the MissingSchemaAction or how parameters are passed: It just works.

Declaring an Access Data Source

The AccessDataSource object is declared like any other server side object, it's just added as an xml node inside of the web form. Listing 1 shows the declaration of the AccessDataSource. This declaration names and instantiates the AccessDataSource1 object. Note that all we need to do to connect to the MSAccess database is to declare the relative location of the database in the DataFile property. We also have a choice of what mode we can run our AccessDataSource. There are two modes for an AccessDataSource: DataSet and DataReader. When you have the DataSourceMode set to DataSet, you can access the data from memory through a collection of DataRowViews. If you are in DataReader mode, you can access each row through the IDataReader interface a row at a time.

Listing 1 - Declaring the Access Data Source

<body> <form id="Form1" method="post" runat="server"> <asp:table id="Table1" style="Z-INDEX: 101; LEFT: 26px; POSITION: absolute; TOP: 27px" runat="server" Width="550px" Height="310px" CellSpacing="1" CellPadding="1"></asp:table> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Quiz.mdb" DataSourceMode="DataSet" SelectCommand="SELECT * FROM [Choices]"></asp:AccessDataSource> </form> </body>

With this declaration, there is no need for connection strings, connection string objects, adapters, or any of that other drivel. You can just change the query string and call the Select method on the AccessDataSource and wallah, you have a list of desired rows!

Using the AccessDataSource

For our project, we've created 2 methods to help us use the AccessDataSource SelectRows and GetColumnValue. SelectRows takes a SELECT query statement and returns a list of enumerable DataRowViews. GetColumnValue takes a SELECT query and a column name and returns the data of the column in the first row determined by the query. The Select method of the AccessDataSource takes an arguement of type DataSourceSelectArguements (seems like a pretty relevant name for the arguement of a DataSource Select statement :-) ). This arguement allows us to limit the number of rows returned. It also allows us to sort the rows returned from the select statement.

Listing 2 - Methods to Help us Perform Queries on the Database

private IEnumerable SelectRows(string queryString) { // this arguement allows us to sort or filter on the DataView DataSourceSelectArguments args = new DataSourceSelectArguments(""); // assign a query string to the SelectCommand for our Access Data Source AccessDataSource1.SelectCommand = queryString; // Select the DataRowViews and return them return AccessDataSource1.Select(args); } object GetColumnValue(string queryString, string columnName) { DataSourceSelectArguments args = new DataSourceSelectArguments(""); AccessDataSource1.SelectCommand = queryString; // get the first row returned from the select statement IEnumerator enumerator = AccessDataSource1.Select(args).GetEnumerator(); enumerator.MoveNext(); // return the value in the first row return ((DataRowView) enumerator.Current)[columnName]; }

Now let's take a look at how we use this data source functionality with the web quiz. Rather than rehash the previous article, we'll just pick one example to illustrate how the AccessDataSource helps us dynamically build our quiz into a ASP.NET table. In listing 3, we call upon the SelectRows method (from listing 2) to use the AccessDataSource to get us all of the questions. Then we loop through the collection of DataRowViews returned from the query and plop the questions into our Table. In order to put the question inside the table, we need to create a TableRow and then create a Cell to place inside the TableRow. The Cell is assigned the question Text so it will appear on our web page. After we place the question on the page, we want to get all the corresponding choices for that question. The Rows in the Choice table in our Access Database contain a QuestionID. The QuestionID in the Choices table allows us to match the proper choices against the question id in the Questions table. Using our AccessDataSource again, we can assign it a query to choose the choices for our particular QuestionID. Once we have selected the proper choices from the database table, we loop through each of the choices and create a radio button containing the choice information. The radio button is placed inside a Cell in the Table object. We can use the question number and the letter of the choice as a makeshift key for our radio button and assigned to the radio button's id. When we later go to score the test, we will pull the key out of the selected radio and compare it against the answer to the question in the database.