Introduction 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 App_Data folder.
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 FileUpload control renders a textbox and a button labeled Browse. If the visitor selects the Browse button, their web browser displays a dialog box allowing them to choose a file on their hard drive. After picking a file and clicking the page's Upload File, the Excel spreadsheet is uploaded to the web server. Once the Excel spreadsheet has been uploaded, the user can view the data or import it into the database.
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 SELECT query against an Excel worksheet using the OleDbCommand class.
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.
From the Internet.com eBook Library: Navigating Your IT Career
A career in information technology usually has its share of ups and downs. Download this Internet.com eBook to learn where the jobs are in IT, how to negotiate a salary, and helpful advice on job security and how to deal with a layoff. Join Internet.com now to download! http://www.devx.com/ebook/Link/34938
Interested in placing your TEXT AD HERE? Click Here
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: Members and 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 Members: LASTNAME, 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 Member and Category tables. 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_Data folder. To do this, go to the Solution Explorer, right click the App_Data directory, 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 SSMembers database.
Adding the Tables We now need to add the two tables to the database. Let's start with the Members table. From the Server Explorer, expand the SSMembers database 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_id and 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 Lastname, Firstname, Address1, Address2, City, and Email and give each a data type of varchar(50). Next, add a column named State of type varchar(2) and Zip of type varchar(10). Add Phone and Fax columns of type varchar(12), and a column named Website of type varchar(200). 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 Members.
Add another table for the categories. Specifically, create the table with an identity primary key column of type int named category_id and a varchar(50) column named CategoryName. Like with the Members table, mark 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 Category.
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.
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/.
All newsletters are sent from the domain "internet.com." Please use this domain name (not the entire "from" address, which varies) when configuring e-mail or spam filter rules, if you use them.
Getting Started: Creating the SQL Server Database
No comments:
Post a Comment