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.
|
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!
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
|
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
|
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
|
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
|
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
|
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)
|
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
|
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
|
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
|
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, _
|
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
|
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
|
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
|
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!
Attachments
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.
|
