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

Mike Gold
17y
51.2k
0
3

Article

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

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.

Listing 3 - Dynamically Building the Web Quiz in C#

private void ReadQuestionsIntoTable()
{

NumberOfQuestions = 0; // counts the # of questions

// select all the questions in the quiz
IEnumerable questions = SelectRows("Select * From Questions");

// go through every row in the questions table
// and place each question in the Table Web Control

            foreach (DataRowView question in questions)
             {
               // create a row for the question and read it from the database
               TableRow tr = new TableRow();
               Table1.Rows.Add(tr);
               TableCell aCell = new TableCell();

                // get the text for the question and stick it in the cell
                aCell.Text = question["QuestionText"].ToString();
                tr.Cells.Add(aCell);
                string questionNumber = String.Format("Answer{0}", question["QuestionID"].ToString());                       Session[questionNumber] = question["Answer"].ToString();

// add a blank row to pad between question and choices

                TableRow blankRow = new TableRow();
                TableCell cellPad = new TableCell();
                cellPad.BorderWidth = 5;
                blankRow.Cells.Add(cellPad);
                Table1.Rows.Add(blankRow);

int count = 0; // tracks the number of choices

                // select all the choices for this question and
                // create radio buttons for each choice
                IEnumerable choices = SelectRows(String.Format("Select * From Choices where QuestionID={0}",
                                                                   question["QuestionID"]));

                foreach (DataRowView choiceRow in choices)
                    {
                              TableRow tr2 = new TableRow();
                              Table1.Rows.Add(tr2);

                              // create a cell for the choice
                              TableCell aCell3 = new TableCell();
                              aCell3.Width = 1000;

                              // align the choices on the left
                              aCell3.HorizontalAlign = HorizontalAlign.Left;
                              tr2.Cells.Add(aCell3);

// create a radio button in the cell
RadioButton rb = new RadioButton();

                              // assign the radio button to Group + QuestionID
                              rb.GroupName = "Group" +
                                        choiceRow["QuestionID"].ToString();

                              // Assign the choice to the radio button
                              rb.Text = choiceRow["ChoiceLetter"].ToString() +
                                       ". " + choiceRow["ChoiceText"].ToString();

// the radio button id corresponding to the choice and question #

rb.ID = "Radio" + NumberOfQuestions.ToString() + Convert.ToChar(count + 65);
rb.Visible = true;

                              // add the radio button to the cell
                              aCell3.Controls.Add(rb);
                              count++;
                        }

// add a table row between each question
// as a spacer

                        TableRow spacer = new TableRow();
                        TableCell spacerCell = new TableCell();
                        spacerCell.Height = 30;
                        spacer.Cells.Add(spacerCell);
                        Table1.Rows.Add(spacer); // add a spacer

                        NumberOfQuestions++;   // count the # of questions
                  }
            }

Conclusion

With the help of the AccessDataSource, manipulating the Access Database for our Web Quiz becomes a no-brainer. You can also use the AccessDataSource to bind to controls such as the GridView, the DropDownList, or the Repeater control in ASP.NET. In the future, we will look at some of these cool visual features. In the meantime, don't hold me bound to it, but you can really improve your access to the database with the help of the AccessDataSource and ASP.NET.

Up Next

Ebook Download

View all

Coding Principles

Read by 0 people

Download Now!

Learn

View all