Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Importing the Excel SpreadsheetBy Nannette Thacker
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!
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
Click event. The first order of business is to hide the
upload- and view-related Panels and show the import-related Panel (
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
The following code shows the start of the
Click event handler.
Next, we need to create an
OleDbCommand and call its
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
CommandText was set to
SELECT * FROM [Members$] (which returns
all columns and rows from the
To import the data we need to call the
ExcelConnection() method to get back a configured
OleDbCommand object and then
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:
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
Members table in the application database. The following code shows the general pattern with a few TODOs
that we will return to later.
Let's look at each TODO.
Because all the Excel spreadsheet columns are string values and because the
NULLvalues 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.
Getting (or Creating) the Corresponding
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_idvalue (1, 2, etc.). However, the
Membersdatabase links back to the
Categorydatabase via its
category_idcolumn. 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
This work is handled by the
GetCategoryID(categoryName) helper function.
checks to see if categoryName exists; if so, it returns the corresponding
If categoryName does not exist,
GetCategoryID(categoryName) inserts the category into the table and returns the
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:
Inserting the Excel Row Into the
Members Database Table
After determining the appropriate
category_idvalue 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 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
We will create this helper method in a moment. For now, let's examine how it is called from the Button's
Communicating with the Application Database Using the Data Access Layer (DAL)
At this point we still need to create the
ImportIntoMembershelper functions. These two functions communicate with the application database to determine the
category_idvalue (and create a new category, if needed) and to insert a record into the
Memberstable (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
using the following code:
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:
Because we need to work with categories here, use the
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
contains a single row; if the category was not found, then it contains zero rows.
If the passed in categoryName is found in the
Category table then its
is assigned to the local variable
category_id. If, however, no matching record is found then
will have its initial value, 0. In this case we need to insert a new record into the
and return the just-inserted
Importing Members Into the
GetCategoryID(categoryName)method complete, all that remains is to code the
First we need to define the function and its input parameters:
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:
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
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
LabelImport Label control.
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
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.
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
May your dreams be in ASP.NET!
About the Author: