Shining Star Services LLC
C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import
Section 8: Using an OleDbDataReader to Retrieve Our Data
by Nannette Thacker

In our previous Sections, we learned how to create our DataSet and TableAdapters using Select and Insert queries. In this section, we are going to go back to our codebehind and add our ButtonImport_Click sub procedure which will handle our "ButtonImport.Click" event. Notice in our code below that we are setting our PanelUpload visibility to false in order to remove the file upload control and button from the screen. We are also setting the PanelView visibility to false to remove our Gridview from displaying to the screen. And lastly, we are setting our PanelImport visibility to true, in order to display our import results to the screen.

Protected Sub ButtonImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles ButtonImport.Click
    PanelUpload.Visible = False
    PanelView.Visible = False
    PanelImport.Visible = True
    LabelImport.Text = "" ' reset to blank
We will create a reader as an OleDbDataReader and use the ExecuteReader method, then we will use our select command to retrieve our worksheet data by using the same ExcelConnection() function used by our ButtonView_Click. The following example creates an OleDbCommand and executes it by passing our select query string and connection to our data source retrieved from our ExcelConnection function.

' retrieve the Select Command for the worksheet data
    Dim objCommand As New OleDbCommand()
    objCommand = ExcelConnection()

    ' create a DataReader
    Dim reader As OleDbDataReader
    reader = objCommand.ExecuteReader()
Next we will create and define our defaults for the variables we will retrieve from the spreadsheet columns.

'  create variables for the spreadsheet columns
        Dim member_id As Integer = 0
        Dim category_id As Integer = 0
        Dim lastname As String = String.empty
        Dim firstname As String = String.empty
        Dim address1 As String = String.empty
        Dim address2 As String = String.empty
        Dim city As String = String.empty
        Dim state As String = String.empty
        Dim zip As String = String.empty
        Dim phone As String = String.empty
        Dim fax As String = String.empty
        Dim email As String = String.empty
        Dim website As String = String.Empty
        Dim category As String = String.Empty
When importing spreadsheet data, I like to set up an early exit from my loop so that I can import a few records at a time in order to test the import functionality without cluttering up my table with a lot of garbage. So let's create a counter for our test loop.

Dim counter As Integer = 0 ' used for testing your import in smaller increments
Now let's use the reader we created earlier and do a While loop to Read() the data one row at a time. We'll increment our counter on each loop. For additional methods of Retrieving Values from a DataReader, please see my blog. I tried the various methods and ended up using a suggestion by Travis to use Convert.ToString. Our particular table does accept null values, but if you wish to not allow null values, you may easily use the Convert.ToString method, which handles nulls. I tested this theory by changing my columns to not allow nulls and importing null values without any errors.

While reader.Read()
        counter = counter + 1 ' counter to exit early for testing...

        ' set default values for loop
        member_id = 0
        category_id = 0

        lastname = Convert.ToString(reader("lastname"))
        firstname = Convert.ToString(reader("firstname"))
        address1 = Convert.ToString(reader("address1"))
        address2 = Convert.ToString(reader("address2"))

        city = Convert.ToString(reader("city"))
        state = Convert.ToString(reader("state"))
        zip = Convert.ToString(reader("zip"))
        phone = Convert.ToString(reader("phone"))

        fax = Convert.ToString(reader("fax"))
        email = Convert.ToString(reader("email"))
        ' Notice the space in the "web site" column from the Excel Spreadsheet
        website = Convert.ToString(reader("web site"))
        category = Convert.ToString(reader("category"))
        
        ' Insert any required validations here...
Validating the data is beyond the scope of this tutorial, but this is the place where you would want to add any validation code as needed.

Once we have retrieved our category by name, we will pass it to our GetCategoryID() helper function. This function checks to see if the category name exists, if so, it returns the category_id, if not, it inserts the category into the table and returns the new category_id. We'll create this new GetCategoryID() function in Section 9. We'll also pass all of our field values and our category_id to our ImportIntoMembers() function and return the member_id. We'll also create our ImportIntoMembers() function in Section 9.

category_id = GetCategoryID(category) 'retrieve the category_id
member_id = ImportIntoMembers(lastname, firstname, address1, address2, _
    city, state, zip, phone, fax, email, website, category_id)
Now we'll create our text for our LabelImport and keep adding to it on each loop. This will display the results of our import to the screen.

LabelImport.Text = LabelImport.Text & member_id & lastname & _
    " " & firstname & " " & address1 & " " & address2 & _
        " " & city & " " & state & " " & zip & " " & _
        phone & " " & fax & " " & email & " " & website & _
        " cat_id: " & category_id & " " & category & "<br>"
If you want to turn on the testing feature, simply uncomment the following 3 lines. You may change the numeric value to exit when the counter is greater than 1 or any other value. Once your import looks like it is working fine, you may comment these lines so that the entire import runs. Then we end our while loop and close our reader.

    'If counter > 1 Then ' exit early for testing, comment later...
    '    Exit While
    'End If

    End While
    reader.Close()

End Sub
Below is this same function in C#:

C#:

protected void ButtonImport_Click(object sender, System.EventArgs e)
    {
        PanelUpload.Visible = false;
        PanelView.Visible = false;
        PanelImport.Visible = true;
        LabelImport.Text = "";
        // reset to blank

        // retrieve the Select Command for the worksheet data
        OleDbCommand objCommand = new OleDbCommand();
        objCommand = ExcelConnection();

        //  create a DataReader
        OleDbDataReader reader;
        reader = objCommand.ExecuteReader();


        //  create variables for the spreadsheet columns
        int member_id = 0;
        int category_id = 0;
        string lastname = "";
        string firstname = "";
        string address1 = "";
        string address2 = "";
        string city = "";
        string state = "";
        string zip = "";
        string phone = "";
        string fax = "";
        string email = "";
        string website = "";
        string category = "";


        int counter = 0;
        // used for testing your import in smaller increments

        while (reader.Read())
        {
            counter = counter + 1;
            // counter to exit early for testing...

            // set default values for loop
            member_id = 0;
            category_id = 0;


            lastname = GetValueFromReader(reader,"lastname");
            firstname = GetValueFromReader(reader, "firstname");

            address1 = GetValueFromReader(reader, "address1");
            address2 = GetValueFromReader(reader, "address2");

            city = GetValueFromReader(reader, "city");
            state = GetValueFromReader(reader, "state");
            zip = GetValueFromReader(reader, "zip");
            phone = GetValueFromReader(reader, "phone");

            fax = GetValueFromReader(reader, "fax");
            email = GetValueFromReader(reader, "email");
            // Notice the space in the "web site" column from 
            // the Excel Spreadsheet
            website = GetValueFromReader(reader, "web site");
            category = GetValueFromReader(reader, "category");

            // Insert any required validations here...

            category_id = GetCategoryID(category);
            //retrieve the category_id
            member_id = ImportIntoMembers(lastname, firstname, address1, 
            address2, city, state, zip, phone, fax, email,
            website, category_id);

            LabelImport.Text = LabelImport.Text + member_id + lastname + 
            " " + firstname + " " + address1 + " " + address2 + " " + 
            city + " " + state + " " + zip + " " + phone + " " + fax + 
            " " + email + " " + website + " cat_id: " + category_id + 
            " " + category + "<br>";
            //If counter > 2 Then ' exit early for testing, comment later...
            //    Exit While
            //End If

        }
        reader.Close();

    }
In our next section, we'll learn how to setup our GetCategoryID and ImportIntoMembers functions. These functions will call our TableAdapters. I also want to discuss Intellisense when setting up your TableAdapter code. Section 9: Using our TableAdapters, DataTables and Intellisense .

May your dreams be in ASP.NET!

Nannette Thacker

C# and VB Project: Importing an Excel Spreadsheet to a Database Using Data Sets and Table Adapters:

Introduction: C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import
Section 1: Creating Our Project, Database and Tables
Section 2: WebForm: Table, Label, and Panel Controls
Section 3: FileUpload Control and Functionality
Section 4: Auto Formatting a Web Form GridView
Section 5: Bind the Excel Data to a GridView using an OleDbDataAdapter
Section 6: Data Access Layer DataSet TableAdapters
Section 7: TableAdapter Select and Insert Queries with Parameters
Section 8: Using an OleDbDataReader to Retrieve Our Data
Section 9: Using our TableAdapters, DataTables and Intellisense

Download the ZIP files:

C#: ShiningStarCExcel.zip
VB: ShiningStarVBExcel.zip

About the Author

Nannette Thacker is an ASP.NET web application developer and SQL Server developer. She is owner of the ASP.NET consulting firm, Shining Star Services, LLC in Kansas City. Nannette specializes in ASP Classic to ASP.NET conversions and custom Membership Provider solutions as well as existing or new ASP.NET development. Nannette's many articles on ASP.NET, ASP Classic, Javascript and more may be read at http://www.shiningstar.net. You may also view her http://weblogs.asp.net/nannettethacker/ web blog.

© Copyright 1997-2017 Shining Star Services LLC, Nannette Thacker. All Rights Reserved.