When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, March 26, 2008

Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Importing the Excel Spreadsheet

By Nannette Thacker


Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters
This article is one in a series of articles on importing a Microsoft Excel spreadsheet into a database table.

  • Building the Database - provides an overview of the tutorial series and the first steps, including creating the website project and database tables.
  • Building the Importer Web Page and Uploading the Excel Spreadsheet - adds the interface for uploading an Excel spreadsheet from the user's computer and saving it on the web server's file system.
  • Displaying the Uploaded Excel Spreadsheet - shows how to query and display the contents of an Excel spreadsheet in a GridView control.
  • Creating a Data Access Layer (DAL) - creates the Data Access Layer (DAL) used to communicate with the application database during the import process.
  • Importing the Excel Spreadsheet - looks at importing the uploaded Excel spreadsheet into the application database tables using the DAL.
  • Introduction


    Over the past four installments in this article series we have: created a SQL Server database for our web application; created an ASP.NET web page that enabled the visitor to upload an Excel spreadsheet and view its contents in a GridView; and created a Data Access Layer (DAL) using Typed DataSets for accessing our application database. All that remains is to import the uploaded Excel spreadsheet's data into the application database.

    In a perfect world, a user would carefully check the data in the Excel spreadsheet and cross-reference it with the data already existing in the application database. But our users are humans and bound to err. Therefore, it behooves us to design the import functionality so that it can gracefully handle common types of mistakes. We'll examine two such cases: preventing duplicate entries and ensuring that the imported data is in the appropriate format.

    Read on to learn more!

    - continued -

    Importing the Uploaded Excel Spreadsheet


    In the Building the Importer Web Page and Uploading the Excel Spreadsheet article we created a web page with three Button Web controls for uploading, viewing, and importing the spreadsheet. Past installments have looked at handling the uploading and viewing buttons; we're now ready for the importing Button.

    Start by creating an event handler for the ButtonImport Button's Click event. The first order of business is to hide the upload- and view-related Panels and show the import-related Panel (PanelImport). The PanelImport Panel includes a Label Web control named LabelImport that displays information about the import progress, such as whether certain Excel spreadsheet rows were not imported because they already exists in the database. In addition to showing PanelImport we also need to clear out this Label's Text property.

    The following code shows the start of the ButtonImport's Click event handler.

    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

    Next, we need to create an OleDbCommand and call its ExecuteReader method to retrieve the contents of the Excel spreadsheet. In Displaying the Uploaded Excel Spreadsheet we created an ExcelConnection() method that included code that connected to the uploaded Excel spreadsheet and returned an OleDbCommand whose CommandText was set to SELECT * FROM [Members$] (which returns all columns and rows from the Members worksheet).

    To import the data we need to call the ExcelConnection() method to get back a configured OleDbCommand object and then utilize the OleDbCommand's ExecuteReader method to get an OleDbReader object so that we can step through the results one record at a time. The following code accomplishes this:

       ' 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()

    Now that we have an OleDbReader object we can step through the Excel spreadsheet's rows one at a time. For each row we read the values in for the various Excel spreadsheet columns using the syntax reader("columnName"). (For more information on retrieving data from a DataReader, see Retrieving Values from a DataReader.) This information is then used to add a new record to the Members table in the application database. The following code shows the general pattern with a few TODOs that we will return to later.

       While reader.Read()       ' set default values for loop
          Dim member_id As Integer = 0
          Dim category_id As Integer = 0

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

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

          Dim fax As String = Convert.ToString(reader("fax"))
          Dim email As String = Convert.ToString(reader("email"))

          ' Notice the space in the "web site" column from the Excel Spreadsheet
          Dim website As String = Convert.ToString(reader("web site"))
          Dim category As String = Convert.ToString(reader("category"))

          'TODO: Insert any required validations here...

          'TODO: Get (or create) the corresponding category_ID

          'TODO: Insert a record into the Members table (if it's not a duplicate)

          'Display information in LabelImport
          LabelImport.Text &= member_id & lastname & _
             " " & firstname & " " & address1 & " " & address2 & _
             " " & city & " " & state & " " & zip & " " & _
             phone & " " & fax & " " & email & " " & website & _
             " cat_id: " & category_id & " " & category & "<br>"
       End While
       reader.Close()
    End Sub

    Let's look at each TODO.

    Validating Data


    Because all the Excel spreadsheet columns are string values and because the Members table allows NULL values for all its columns, we don't need to perform any sort of validation. If, however, the Excel spreadsheet prompted users for numeric, Boolean, or date information (such as the member's birthdate), we would want to ensure that the value entered by the user in the Excel spreadsheet was in the appropriate format.

    Furthermore, our application's business rules may indicate that certain fields are required or must be one of a certain set of legal values. For example, our application might require that a member always have an associated category or email address. Or it may be the case that all members must have a unique email address (that is, we cannot have two or more members with the same email address).

    Such validation is a bit beyond the scope of this article, so I leave this as a topic for the reader to explore on her own. In a nutshell, you can add any required checks at the TODO line in the code above. For example, the following code snippet shows how you could require that a value was entered for the last name.

    'Ensure the import data includes a last name for each member
    If lastname.Length = 0 Then
       LabelImport.Text &= "You must provide a last name!"
    Else
       'TODO: Get (or create) the corresponding category_ID value

       'TODO: Insert a record into the Members table (if it's not a duplicate)
    End If

    Getting (or Creating) the Corresponding category_ID Value


    Presumably the Excel spreadsheet will be filled out by a human (not auto-generated by a computer program). Consequently, when prompted to specify the category for a member, a user will enter the name of the category (Development, Digital Imaging, etc.) rather than the corresponding category_id value (1, 2, etc.). However, the Members database links back to the Category database via its category_id column. Therefore, we need to take the category name entered by the user and determine its category_id. Moreover, if the category cannot be found then we need to create a new record in the Category table.

    This work is handled by the GetCategoryID(categoryName) helper function. GetCategoryID(categoryName) checks to see if categoryName exists; if so, it returns the corresponding category_id value. If categoryName does not exist, GetCategoryID(categoryName) inserts the category into the table and returns the new category_id value. We'll create this GetCategoryID() function later in this article. For now, here is what the code will look like from the Button Click event handler:

    'Get (or create) the corresponding category_ID
    category_id = GetCategoryID(category)

    Inserting the Excel Row Into the Members Database Table


    After determining the appropriate category_id value we need to take the values from the current Excel row and use them in inserting a new record into Members. However, we don't want to perform the insert if the row already exists in the table. This duplicate check, along with the actual insert into the database table, is handled by a helper function named ImportIntoMembers.

    ImportIntoMembers takes as input the various fields that describe a member (last name, first name, address, etc.). It then checks to see if a member with these field values already exists. If so, it displays an appropriate message in LabelImport; otherwise, it inserts the record and returns the value of the just-inserted record's member_id column.

    We will create this helper method in a moment. For now, let's examine how it is called from the Button's Click event handler:

    'Insert a record into the Members table (if it's not a duplicate)
    member_id = ImportIntoMembers(lastname, firstname, address1, address2, city, state, zip, phone, fax, email, website, category_id)

    Communicating with the Application Database Using the Data Access Layer (DAL)


    At this point we still need to create the GetCategoryID(categoryName) and ImportIntoMembers helper functions. These two functions communicate with the application database to determine the category_id value (and create a new category, if needed) and to insert a record into the Members table (if it is not a duplicate). While we could interface with the application database directly through our ASP.NET page's base class, a better alternative is to use a Data Access Layer (DAL). We built our DAL in the preceding article, Creating a Data Access Layer. We are now ready to use it to perform the remaining logic.

    Let's start by coding the GetCategoryID(categoryName) method. Create a method named GetCategoryID using the following code:

    Protected Function GetCategoryID(ByVal categoryname As String) As Integer
       Dim category_id As Integer = 0

       Try

       Catch ex As Exception
          LabelImport.Text &= ex.ToString
          Return 0
       End Try
    End Function

    We need to now add code within the Try block to connect to the database via the DAL. Recall that the DAL's methods are accessible through its TableAdapter classes, which are available in the SSSMembersDataSetTableAdapters namespace. Therefore, type in Dim SSAdapter As New SSSMembersDataSetTableAdapters and then hit the period (.). As the following screen shot shows, Visual Web Developer's IntelliSense lists the available classes in the namespace. There are two: CategoryTableAdapter and MembersTableAdapter. Because we need to work with categories here, use the CategoryTableAdapter.

    The CategoryTableAdapter's GetCategoryByName method returns information about a category by a specified name. This method, like all TableAdapter methods that return a set of rows, return data in the form of their corresponding strongly-typed DataTable. In other words, the GetCategoryByName method returns a CategoryDataTable object. If the category is found, then the returned CategoryDataTable contains a single row; if the category was not found, then it contains zero rows.

    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 the passed in categoryName is found in the Category table then its category_id value is assigned to the local variable category_id. If, however, no matching record is found then category_id will have its initial value, 0. In this case we need to insert a new record into the Category table and return the just-inserted category_id value.

          If category_id = 0 Then
             ' retrieve the identity key category_id from the insert
             category_id = Convert.ToInt32(SSAdapter.InsertCategoryQuery(categoryname))
          End If

          Return category_id

       Catch ex As Exception
          LabelImport.Text &= ex.ToString
          Return 0
       End Try
    End Function

    Importing Members Into the Members Table


    With the GetCategoryID(categoryName) method complete, all that remains is to code the ImportIntoMembers function.

    First we need to define the function and its input parameters:

    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

    The columns in the Members database table have fixed widths. For example, lastname may not exceed 50 characters, while website may be between 0 and 200 characters. However, the Excel spreadsheet imposes no length limits. Therefore we need to truncate lengthy inputs to their maximum size. If you omit this truncation, SQL Server will throw an error if you attempt to import a column value whose length exceeds its defined bounds.

    First we need to define the function and its input parameters:

       ' 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)

    Much like how we determined if a match category existed given a category name, we need to perform similar logic to determine whether there is a duplicate member already in the database. What constitutes a duplicate may differ by application. By that I mean that some applications might consider two records as duplicates if the members share the same last and first names; for others, two member records may only be considered duplicated if every single field has matching values. For our application, I've made it so that two members are considered duplicates if they have the same first and last names and the same address.

    The following code calls the MembersTableAdapter's GetMemberByNameAddress method, passing in the first name, last name, and address values for the current Excel row. If there is a matching record, a message is displayed in the LabelImport Label control.

       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 check if member_id is 0. If so, then no duplicate was found so we insert a record into the Members table using the MembersTableAdapter's InsertMemberQuery method.

          If member_id=0 Then
             ' 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 &= "<font color=red>" & ex.ToString & "</font><br />"
          Return 0
       End Try
    End Function

    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.

    Conclusion


    This article concludes this multi-part series on importing an Excel spreadsheet into a SQL Server database using Typed DataSets and TableAdapters. In this tutorial we've learned how to setup an ASP.NET website, created a SQL Server Database with two tables, use the Identity Specification property, and created a data access layer with TableAdapters. We've learned how to use various Web controls including the FileUpload, Panel, GridView, Button and Label. We've seen how to populate a GridView using a TableAdapter, how to read data from an Excel Spreadsheet and use an OleDbDataReader.

    May your dreams be in ASP.NET!

    By Nannette Thacker


    Attachments


  • Download the Application (in ZIP format)
  • 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. Her blog is online at http://weblogs.asp.net/nannettethacker/.

    Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters
    This article is one in a series of articles on importing a Microsoft Excel spreadsheet into a database table.

  • Building the Database - provides an overview of the tutorial series and the first steps, including creating the website project and database tables.
  • Building the Importer Web Page and Uploading the Excel Spreadsheet - adds the interface for uploading an Excel spreadsheet from the user's computer and saving it on the web server's file system.
  • Displaying the Uploaded Excel Spreadsheet - shows how to query and display the contents of an Excel spreadsheet in a GridView control.
  • Creating a Data Access Layer (DAL) - creates the Data Access Layer (DAL) used to communicate with the application database during the import process.
  • Importing the Excel Spreadsheet - looks at importing the uploaded Excel spreadsheet into the application database tables using the DAL.


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article