by Nannette Thacker

You may recognize much of this from my article series on 4guysfromrolla.com, Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Displaying the Uploaded Excel Spreadsheet. This is somewhat different as my goal for this series is less about importing an Excel spreadsheet, and more about newbies building a project to learn how to create the project, create a DataSet and TableAdapter, how to upload a file, how to create a WebForm and add controls, how to use intellisense, and how to work with retrieving and inserting into a database.

This series includes more screen shots and less technical discussion, but more step by step instruction. It also contains source code for both C# and VB. If you need additional explanation on a given area of this article, please refer to the 4guys version, as it contains more indepth information on other specific areas.

In this tutorial by Nannette Thacker, we will create a VB or C# project from scratch. We will create DataSets, TableAdapters, a WebForm and Controls, a File Upload, and import and view an Excel Spreadsheet, and more! We'll also learn about intellisense and other features of Visual Studio or Visual Web Developer. This project may be used in both VS or VWD 2005 or 2008. Zip files with the complete source code in either C# or VB, Excel Spreadsheet, and .mdf SQL Server Database is available for download. You may easily modify this tutorial to use an existing database as well.

I have written this tutorial with step by step instructions and screen shots on how to do each point. If you are learning ASP.NET, please don't simply download the source code and plunk it into your project, the best way to learn is to walk through the tutorial from scratch and do each step yourself.

SQL Server Tables, DataSets and TableAdapters

We will learn how to create a project and our SQL Server Database Tables using Visual Web Developer. We will look at the design of our Excel Spreadsheet and create a new database and tables for our imported data. When we create our tables, we will learn how to use the Identity Specifications property when creating a table column. We will then learn how to create a DataSet and TableAdapters, as well as set up a ConnectionString in our web.config file. With our TableAdapters, we will learn how to create Select Queries which utilize parameters. We will create an Insert Query in our Table Adapter that retrieves the Identity field using Scope_Identity().

WebForm and Controls

Next, we will learn how to create a webform and define the properties of an ASP.NET Table control to layout our buttons. We will learn how to add controls for Buttons, Panels and Labels. We will discuss how to set a Label text value and set Panel visibility from Codebehind. We will also learn how to use the "Handles" key word at the end of our procedure declarations to handle our Button Click events.

Our WebForm will consist of three buttons which will activate the following functionality:

1. Excel Spreadsheet File Upload.
2. Retrieving and Viewing the Excel Spreadsheet Data.
3. Importing the Excel Spreadsheet Data into a Database Table.

Excel Spreadsheet File Upload

Our "Excel Spreadsheet File Upload" button will activate our FileUpload control. The FileUpload control allows the user to select a "Browse" button to find the file on their hard drive. The user may then select the "Upload File" button in order to upload the file to the web server. We will learn how to create a function to save an uploaded file in our Codebehind.



Retrieving and Viewing the Excel Spreadsheet Data

Our second button will allow the user to Retrieve and View the Excel Spreadsheet Data. In this section, we will learn how to add and Auto Format a GridView in our WebForm. We will learn how to create a connection to our Excel Spreadsheet using an OleDbConnection with a Microsoft.Jet.OLEDB Connection String. We will learn how to create a SELECT query to retrieve data from a given Excel worksheet using an OleDbCommand. We will populate and bind our data to our GridView using an OleDbDataAdapter and DataSet.

Below we see a portion of our finished screen displaying our uploaded Excel Spreadsheet within our GridView. Our tutorial uses a default selection when Auto Formatting, but you may easily customize your GridView for a tighter layout.



Importing the Excel Spreadsheet Data into a Database Table

Our final button will perform the operation of Importing the Excel Spreadsheet Data into a Database Table. In this section, we will learn how to read the data from the Excel Spreadsheet using an OleDbDataReader. We will perform minimal validation, in that we will only check for null values from the imported data, but you may easily add column validation upon import. Our tutorial will show how to detect errors using Try/Catch and check for Exceptions. We will check for duplicate rows and learn how to check for and insert new categories. We will take a brief look at how to use Intellisense when using DataSets and TableAdapters. And finally, we will learn how to pass parameters to our TableAdapters.

The below screenshot shows the feedback from the Excel Spreadsheet Import.



With this tutorial, you may use Visual Studio or Visual Web Developer 2008 or 2005. Coding is in VB.

Let's get started with Section 1: Creating Our Project, Database and Tables.

May your dreams be in ASP.NET!

Nannette Thacker

C# and VB Project: Importing an Excel Spreadsheet to a Database Using Data Sets and Table Adapters:

Introduction: C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import
Section 1: Creating Our Project, Database and Tables
Section 2: WebForm: Table, Label, and Panel Controls
Section 3: FileUpload Control and Functionality
Section 4: Auto Formatting a Web Form GridView
Section 5: Bind the Excel Data to a GridView using an OleDbDataAdapter
Section 6: Data Access Layer DataSet TableAdapters
Section 7: TableAdapter Select and Insert Queries with Parameters
Section 8: Using an OleDbDataReader to Retrieve Our Data
Section 9: Using our TableAdapters, DataTables and Intellisense

Download the ZIP files:

C#: ShiningStarCExcel.zip
VB: ShiningStarVBExcel.zip

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. You may also view her http://weblogs.asp.net/nannettethacker/ web blog.