C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel
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:
Excel Spreadsheet File Upload
- Excel Spreadsheet File Upload.
- Retrieving and Viewing the Excel Spreadsheet Data.
- Importing the Excel Spreadsheet Data into a Database Table.
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
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!
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
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:
About the Author
Nannette Thacker is an ASP.NET web application developer and
SQL Server developer. She is owner of the ASP.NET consulting
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.
and more may be read at http://www.shiningstar.net
. You may
also view her http://weblogs.asp.net/nannettethacker/