Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Building the DatabaseBy Nannette Thacker
In most data-driven Web applications, users add data to the database by entering it through a web page. While this works well for most scenarios, there are times when the user has already collected the data to enter elsewhere, and having them tediously re-enter it through a web page is not only inefficient but prone to typos. In this multi-part series we will examine how to import data from a Microsoft Excel spreadsheet into a database from a web page. Specifically, we will build a tiered application architecture using Typed DataSets and TableAdapters. A ZIP file with the complete source code, Excel spreadsheet, and SQL Server Database is available for download at the end of this article. You may easily modify this tutorial to use an existing database as well.
This article and the subsequent installments have been written with step-by-step instructions and screen shots. If you are learning ASP.NET, please refrain from downloading the source code and plunking it into your project, as the best way to learn is to walk through the tutorial from scratch and do each step yourself. In this first installment we will discuss the purpose and architecture of the application and create the database tables. Read on to learn more!
An Overview of the Application: SQL Server Tables, Typed DataSets, and TableAdapters
In this tutorial series we will be creating an ASP.NET application that offers functionality to import data into the application's database by uploading a properly formatted Microsoft Excel spreadsheet. The application's database, whose tables we'll create later on in this article, is implemented as a SQL Server database. In the download, this database can be found in the application's
Rather than write the code for importing an Excel spreadsheet directly within our ASP.NET pages' code-behind classes, we will instead create a tiered application architecture using Visual Studio's Typed DataSets and TableAdapters. A Typed DataSet is a collection of strongly-typed objects that represent entities in the database (namely, database tables). TableAdapters are classes that populate data into the Typed DataSet from the underlying database, or take data from the Typed DataSet and insert, update, or delete the corresponding information from the database. In a future installment we will create this application architecture.
An Overview of the Application: The ASP.NET Page for Uploading and Importing an Excel Spreadsheet
The application consists of an ASP.NET page that enables a user to upload an Excel spreadsheet and import its data into the application's database. In building this page, we will use the ASP.NET Table control to layout the user interface, which includes Buttons, Panels, Labels, and a FileUpload control.
In particular, this ASP.NET page will consist of three buttons that, when clicked, activate the following functionality:
- Excel Spreadsheet File Upload.
- Retrieving and Viewing the Excel Spreadsheet Data.
- Importing the Excel Spreadsheet Data into a Database Table.
The following screenshot shows a glimpse of this page's user interface. Note the three Button Web controls along the top - Upload Excel Spreadsheet, View Excel Data, and Import Excel Data - along with the FileUpload control and Upload File button.
The View Excel Data button allows the user to view the Excel spreadsheet they just uploaded, displaying the results in a GridView control. We will learn
how to create a connection to the uploaded Excel Spreadsheet using the
OleDbConnection class and see how to run a
query against an Excel worksheet using the
The screenshot below shows what the output looks like when clicking the View Excel Data button. The GridView control renders the contents of the uploaded Excel spreadsheet, giving the visitor a chance to review the data before performing the import.
The Import Excel Data button imports the uploaded Excel spreadsheet into the application's database. To accomplish this we will need to
read the data from the Excel spreadsheet using the
OleDbDataReader class. We will see how to perform validation on the Excel spreadsheet's
data, specifically checking for
NULL values from the imported data, but you can easily add additional column-level validation logic if
needed. We will also check to ensure that the import does not add duplicate rows.
Getting Started: Creating the Project in Visual Studio and Examining the Excel Spreadsheet
To get started, fire up Visual Studio (or Visual Web Developer). Go to the File menu and choose to Create a New Website using the ASP.NET Web Site template. I named mine ShiningStarExcel.
Our database will consist of two tables:
Category. There is a one-to-many relationship between Categories and
Members, in that each Category may consist of many Members. To import data, an Excel spreadsheet of a specified format must be uploaded.
Specifically, this spreadsheet should have the following field names in the first row of a worksheet named
FIRSTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, PHONE, FAX, EMAIL, WEB SITE, and CATEGORY. We have named our worksheet Members.
(The download includes a sample Excel spreadsheet in this format.)
When importing a spreadsheet, we will check for duplicate Members, thus disallowing double imports. Moreover, if the Member's CATEGORY value does not
yet exist in the
Category table a new record will automatically be added to that table.
Getting Started: Creating the SQL Server Database
We are now ready to create the SQL Server database that will contain the
Categorytables. If you have an existing SQL Server database on a web or database server that you want to use, add a connection to it through the Server Explorer window. (If you are using Visual Web Developer, the Server Explorer is named Database Explorer.) Alternatively, you may create a new SQL Server 2005 Express Edition database in the application's
App_Datafolder. To do this, go to the Solution Explorer, right click the
App_Datadirectory, and select to "Add New Item..." (see the screenshot to the right). Select the "SQL Server Database" icon and enter a name; I named my database
SSMembers.mdf. The Server Explorer will now include our new
Adding the Tables
We now need to add the two tables to the database. Let's start with the
Memberstable. From the Server Explorer, expand the
SSMembersdatabase to list its assorted database object types (Database Diagrams, Tables, Views, and so forth). Right click the "Tables" folder and choose to Add a new table. Name the first column
member_idand set its data type to
int. Deselect the checkbox so as not to "Allow Nulls." Next, scroll down through the Column Properties section until you reach the "Identity Specification" property. Click the
+symbol to expand the Identity Specification values and change the "Is Identity" property from No to Yes.
The rest of our fields are fairly straightforward. Create columns named
varchar(50). Next, add a column named
State of type
Zip of type
Fax columns of type
varchar(12), and a column named
Website of type
Finally, add a
Category_id column of type
int. Allow Nulls for all of these columns.
At this point your screen should look similar to the screen shot below.
Lastly, we need to mark that the
member_id column is a primary key. To accomplish this, right click the member_id field, and select
the "Set Primary Key" option.
Save the table and name it
Add another table for the categories. Specifically, create the table with an identity primary key column of type
category_id and a
varchar(50) column named
CategoryName. Like with the
category_id column as a primary key. Notice the yellow key to the left of the
category_id column in the screen
shot below - this is what denotes that it is the table's primary key.
Name this table
At this point our application's data model has been defined. In the next installment we'll cover building the ASP.NET page that imports uploaded Excel spreadsheets.
May your dreams be in ASP.NET!
About the Author: