Shining Star Services LLC
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
by Nannette Thacker

In this section of our Tutorial on Importing an Excel Spreadsheet to a Database, 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.

Creating the Project

Create a new project using the ASP.NET Web Site template. I named mine ShiningStarExcel.



Excel Spreadsheet

Let's take a look at our Microsoft Excel spreadsheet. Our spreadsheet has the field names in the first row. They are named LASTNAME, FIRSTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, PHONE, FAX, EMAIL, WEB SITE, and CATEGORY. We have named our worksheet Members. The worksheet name is the name of the "table" in our select query. We'll cover that more in a bit.

Below is simply a sample spreadsheet. You may alter this project to include any kind of data. Perhaps you have a member website and wish to import your list of members with contact information. Your spreadsheet may contain your DVD collection, or your home inventory, or anything. It doesn't matter what data you have in the spreadsheet, if it is something you want to put on the web, it may be easily imported into a database for web use. Be sure your Excel Spreadsheet is saved as an .xls file.

Our database will consist of two tables: Members and Category. This tutorial will show how to import a spreadsheet, check for duplicate Members -- thus disallowing double imports, check for existing categories, insert categories in the Category table as needed, retrieve the category_id and store it in the Member table.



Creating our SQL Server Database

Now we are going to create the SQL Server database that will hold the Member and Category tables. In the Solution Explorer, right click the App_Data directory and select to "Add New Item..."



Select the "SQL Server Database" icon. I named my database "SSMembers.mdf." If you have an existing SQL Server database on a web or database server, you may easily skip this section and continue on. Select the "Add" button to add this new database.



The Database Explorer will now display our new "SSMembers" database.



Adding Tables

Right click the "Tables" directory and select to Add a new table. Our first column will be the member_id. Since we don't expect to have too many members, we are going to give this an "int" data type. Deselect the checkbox to not "Allow Nulls." Scroll down to the "Identity Specification" property and press the + symbol to open up the Identity Specification values. Select the "Is Identity" property and change it from "No" to "Yes." In our example, our member_id will start with the number 1 and will increment by 1. These are the defaults.



The rest of our fields are fairly straightforward. We will leave the box checked to "Allow Nulls" in all other columns. Lastname, firstname, address1, address2, city, and email will all be a Data Type of  varchar(50). State is a varchar(2), zip is a varchar(10), phone and fax are varchar(12), and website is a varchar(200). Category_id is an int. For your own uses, you may alter the table to any columns, data types, and character lengths as needed.



To set a Primary key, right click the member_id field, and select the "Set Primary Key" option.



Save the table and you will be asked for a table name. I have named this table "Members."



Add another table for the categories. You may name this second table anything you desire based on the data you intend to import, such as  "Department" or "MediaType" or whatever. We are creating a "Category" table and adding an identity key for the category_id and a categoryname. We are also setting the category_id as a Primary Key. Notice the yellow key to the left of the field column.



Name this table "Category."



In our next section, we will learn how to work with our WebForm. So let's continue with Section 2: WebForm: Table, Label, and Panel Controls.

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.

© Copyright 1997-2017 Shining Star Services LLC, Nannette Thacker. All Rights Reserved.