Section 9: Using our TableAdapters, DataTables and Intellisense - Importing an Excel Spreadsheet using Data Sets and Table Adapters

C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import

Section 9: Using our TableAdapters, DataTables and Intellisense

by Nannette Thacker

In this tutorial, we've learned how to create our Database and Tables, create our Data Access Layer and TableAdapters, how to work with our WebForm and codebehind, how to create functions, upload a file, and more. In our final section, we will now learn how to use our TableAdapters within our code. I also want to show how Intellisense makes it easy to set up your TableAdapter code.

Working with our Table Adapters and Intellisense

Now that we're at the fun part, let's create our GetCategoryID function and utilize our Table Adapters. Microsoft Visual Web Developer has a feature called Intellisense. This means that as you type, a drop down list of possibilities will display on the screen. You may select from these possibilities to complete your coding. Notice in our screenshot example below, we want to setup a new adapter and have begun typing in our SSSMembersDataSetTableAdapters by name, and the intellisense kicks in and displays the possibilities beginning with "S." We can select the one we desire and save ourselves some typing (as well memorizing the exact name).

Once we have the proper TableAdapter selected, we type the "." and now our two possible classes for this adapter display in our intellisense. In our screenshot below, we may select from our "CategoryTableAdapter" class or our "MembersTableAdapter" class. We're going to select our "CategoryTableAdapter."

Next we want to setup a new DataTable so we start typing and intellisense offers us the possibility of our "SSSmembersDataSet."

We select that, type in a "." and now have numerous other options. We want to select our "CategoryDataTable" class.

Now we're going to use the SSAdapter.GetCategoyByName function to populate our SSDataTable. Recall how we created the GetCategoryByName query within our TableAdapter in Section 7: TableAdapter Select and Insert Queries with Parameters. As we type, notice how the intellisense lets us know that we need to pass the categoryname as a string to this function.

We check if our SSDataTable Is Nothing and check if any rows have been returned. Then we check if we have a valid category_id in our SSDataTable, and if so, we store it to our local variable.

Protected Function GetCategoryID(ByVal categoryname As String) As Integer

Dim category_id As Integer = 0
Try
    Dim SSAdapter As New SSSMembersDataSetTableAdapters.CategoryTableAdapter
    Dim SSDataTable As SSSMembersDataSet.CategoryDataTable = Nothing
    SSDataTable = SSAdapter.GetCategoryByName(categoryname)
    ' see if the category already exists in the table, if not insert it
    If Not SSDataTable Is Nothing Then
        If SSDataTable.Rows.Count > 0 Then
            If Not SSDataTable(0).category_id = Nothing Then
                category_id = SSDataTable(0).category_id
            End If
        End If
    End If

If our category_id is 0, then we use our InsertCategoryQuery to insert a new category, retrieve the identity key, and return the new category_id. The category_id is then returned to the calling function. If there is an exception error, the function returns 0 and the LabelImport text is set to display the error message. Notice the comment in the code below that if the category_id fails to return the proper category_id, make sure to set the InsertCategoryQuery ExecuteMode Property to Scalar. We learned how to do this in Section 7: TableAdapter Select and Insert Queries with Parameters. If the property is not set to Scalar, the returned value will be 1, the number of rows affected.

If category_id = 0 Then ' if it is still 0, then insert it into the table
        ' retrieve the identity key category_id from the insert
        category_id = _
            Convert.ToInt32(SSAdapter.InsertCategoryQuery(categoryname))
        ' if this fails to return the proper category_id, make sure to 
        ' set the InsertCategoryQuery ExecuteMode Property to Scalar
    End If
    Return category_id
Catch ex As Exception
    LabelImport.Text = LabelImport.Text & ex.ToString
    Return 0
End Try

End Function

Here is this function in C#:

    protected int GetCategoryID(string categoryname)
    {

        int category_id = 0;
        try
        {
            SSSMembersDataSetTableAdapters.CategoryTableAdapter SSAdapter = 
            new SSSMembersDataSetTableAdapters.CategoryTableAdapter();
            SSSMembersDataSet.CategoryDataTable SSDataTable = null;
            SSDataTable = SSAdapter.GetCategoryByName(categoryname);
            // see if the category already exists in the table, if not insert it
            if (SSDataTable != null)
            {
                if (SSDataTable.Rows.Count > 0)
                {
                    if (SSDataTable[0].category_id > 0)
                    {
                        category_id = SSDataTable[0].category_id;
                    }
                }
            }
            if (category_id == 0)
            {
                // if it is still 0, then insert it into the table
                // retrieve the identity key category_id from the insert
                category_id = (int)SSAdapter.InsertCategoryQuery(categoryname);
                // if this fails to return the proper category_id, make sure to 
                // set the InsertCategoryQuery ExecuteMode Property to Scalar
            }
            return category_id;
        }
        catch (System.NullReferenceException ex) 
        {
            LabelImport.Text = LabelImport.Text + ex.Message;
            return 0;
        }

    }

Now let's look at our ImportIntoMembers function. First we want to define our function and our variables passed in By Value.

Protected Function ImportIntoMembers(ByVal lastname As String, _
    ByVal firstname As String, ByVal address1 As String, _
    ByVal address2 As String, ByVal city As String, ByVal state As String, _
    ByVal zip As String, ByVal phone As String, ByVal fax As String, _
    ByVal email As String, ByVal website As String, _
    ByVal category_id As Integer) As Integer

We're going to truncate each of our values to the maximum column width defined in the tables.

' make sure values don't exceed column limits
lastname = Left(lastname, 50)
firstname = Left(firstname, 50)
address1 = Left(address1, 50)
address2 = Left(address2, 50)
city = Left(city, 50)
state = Left(state, 2)
zip = Left(zip, 10)
phone = Left(phone, 12)
fax = Left(fax, 12)
email = Left(email, 50)
website = Left(website, 200)

We're going to Dim our member_id as an Integer and assign it a value of 0. Then we will setup a new Adapter using our MembersTableAdapter. We'll define a new DataTable using our MembersDataTable, then populate our SSDataTable with our row retreived from the GetMemberByNameAddress class. Notice how we pass in the member's firstname, lastname, and address1. This will allow us to check for duplicates. If a row is returned, we will retrieve the member_id of the current row and add a message to our LabelImport text.

Dim member_id As Integer = 0
Try
Dim SSAdapter As New SSSMembersDataSetTableAdapters.MembersTableAdapter
Dim SSDataTable As SSSMembersDataSet.MembersDataTable = Nothing
SSDataTable = SSAdapter.GetMemberByNameAddress(firstname, lastname, address1)
' see if the category already exists in the table, if not insert it
If Not SSDataTable Is Nothing Then
    If SSDataTable.Rows.Count > 0 Then
        If Not SSDataTable(0).member_id = Nothing Then
            member_id = SSDataTable(0).member_id
            LabelImport.Text = LabelImport.Text & _
                "<font color=blue>Member Found, Not Imported: " & _
                " ID: " & member_id & " " & lastname & " " & firstname & _
                " address: " & address1 & ".</font><br>"
        End If
    End If
End If

Next we will check if our member_id is 0, and if so, we will insert a record in our Members table using our InsertMemberQuery function.

If member_id = 0 Then ' if it is still 0, then insert it into the table
        ' retrieve the identity key member_id from the insert
        member_id = _
            Convert.ToInt32(SSAdapter.InsertMemberQuery(lastname, firstname, _
                address1, address2, city, state, zip, phone, fax, _
                email, website, category_id))
        LabelImport.Text = LabelImport.Text & _
            "<font color=green>Member Imported: " & _
            " ID: " & member_id & " " & lastname & " " & _
            firstname & " address: " & address1 & ".</font><br>"
    End If
    Return member_id
Catch ex As Exception
    LabelImport.Text = LabelImport.Text & "<font color=red>" & _
    ex.ToString & "</font><br>"
    Return 0
End Try

End Function

Notice how when we type our call to our InsertMemberQuery function, intellisense informs us of all the required variables. Notice as we are on the zipcode variable, intellisense has highlighted the zip as string variable in bold to inform us that it is ready for this variable.

Let's look at this same functionality in C#. Notice we create our own Left() function to reproduce the Left() function used in VB:

    protected int ImportIntoMembers(string lastname, 
    string firstname, string address1, string address2, string city, 
    string state, string zip, string phone, string fax, string email,
    string website, int category_id)
    {


        // make sure values don't exceed column limits
        lastname = Left(lastname, 50);
        firstname = Left(firstname, 50);
        address1 = Left(address1, 50);
        address2 = Left(address2, 50);
        city = Left(city, 50);
        state = Left(state, 2);
        zip = Left(zip, 10);
        phone = Left(phone, 12);
        fax = Left(fax, 12);
        email = Left(email, 50);
        website = Left(website, 200);

        int member_id = 0;
        try
        {
            SSSMembersDataSetTableAdapters.MembersTableAdapter 
            SSAdapter = new SSSMembersDataSetTableAdapters.MembersTableAdapter();
            SSSMembersDataSet.MembersDataTable SSDataTable = null;
            SSDataTable = SSAdapter.GetMemberByNameAddress(firstname, 
            lastname, address1);
            // see if the category already exists in the table, if not insert it
            if (SSDataTable != null)
            {
                if (SSDataTable.Rows.Count > 0)
                {
                    if (SSDataTable[0].member_id > 0)
                    {
                        member_id = SSDataTable[0].member_id;
                        LabelImport.Text = LabelImport.Text + 
                        "<font color=blue>Member Found, Not Imported: " + 
                        " ID: " + member_id + " " + lastname + " " + firstname 
                        + " address: " + address1 + ".</font><br>";
                    }
                }
            }
            if (member_id == 0)
            {
                // if it is still 0, then insert it into the table
                // retrieve the identity key member_id from the insert
                member_id = _
                    Convert.ToInt32(SSAdapter.InsertMemberQuery(lastname, firstname, _
                        address1, address2, city, state, zip, phone, fax, _
                        email, website, category_id))
                LabelImport.Text = LabelImport.Text + 
                "<font color=green>Member Imported: " + 
                " ID: " + member_id + " " + lastname + " " + firstname + 
                " address: " + address1 + ".</font><br>";
            }
            return member_id;
        }
        catch (System.NullReferenceException ex)
        {
            LabelImport.Text = LabelImport.Text + "<font color=red>" 
            + ex.Message + "</font><br>";
            return 0;
        }

    }

    // Thanks to Martin Brown! http://www.mgbrown.com/PermaLink68.aspx
    public static string Left(string text, int length)
    {
        if (length < 0)
            throw new ArgumentOutOfRangeException("length", length, "length must be > 0");
        else if (length == 0 || text.Length == 0)
            return "";
        else if (text.Length <= length)
            return text;
        else
            return text.Substring(0, length);
    }
}

To complete our project, our page class also requires that we import the following classes:

VB:

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System;

partial class _Default : System.Web.UI.Page
{

Now when we use the Import Excel Data button, our function will check for duplicates and insert the data to our database tables and write the results to the screen.

If we click the button a second time, our function will check for the duplicates, and, finding duplicates, will instead write the data to the screen without adding the additional record to the table.

One final thing I want to point out in regard to the joys of using Data Sets and Table Adapters, notice our last line for our member "L'Toya O'Conner" who lives on "Don't Drive" street in "O'Paine, Texas?" With a dataset you don't have to worry about converting apostrophes so as not to choke the database on inserts and updates. You may pass your data into your TableAdapter classes without having to worry about apostrophes.

In this tutorial, we've learned how to setup a project, create a SQL Server Database with two tables, use the Identity Specification property, and create a data access layer with TableAdapters. We've learned how to use various WebForm controls including FileUpload, Panel, GridView, Button and Labels.

We've learned how to populate a GridView using a TableAdapter, how to read data from an Excel Spreadsheet and use an OleDbDataReader. We've learned how to save an uploaded file, how to detect errors with try/catch and check for exceptions, and how to create a Microsoft.Jet.OLEDB connection string.

We've learned how to use intellisense, how to pass parameters into our Table Adapter classes, and how to retrieve the identity field using Scope_Identity(). These are just a few of the things we've learned in this series. I hope this helps you along your way in learning and using ASP.NET.

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.


	Web Application Development ASP.NET, VB, C#, SQL Server, Cascading Style Sheets	Web Design Web Sites, Logos, Adobe PhotoShop, Adobe Illustrator	Conversions ASP Classic to ASP.NET Conversions