|
|
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.
|
|