C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import
Section 6: Data Access Layer DataSet TableAdapters
by Nannette Thacker

In this Tutorial, we have learned how to upload a file, specifically an Excel Spreadsheet. We have also learned how to bind our Excel Spreadsheet data to a GridView to display on the screen. Now let's look at the last and most important button, our "Import Excel Data" button, which will retrieve the data from the uploaded spreadsheet, and import it into our SQL Server database. Remember we already created our SQL Server database tables, Members and Categories, in Section 1: Creating Our Project, Database and Tables. Our goal is to hook up this button to now populate those tables.

Creating our Data Access Layer DataSet TableAdapters

Before we setup our click event for our "Import Excel Data" button, let's backup and create a Data Access Layer DataSet and some TableAdapters.

In our project's Solution Explorer, right click, and select to "Add ASP.NET Folder." Select the "App_Code" folder.



I like to keep my datasets separate from my classes, so right click and select to add a "New Folder."



Name this folder "DataSets."



Now right click on the folder and select to "Add New Item." Select the icon for the "DataSet" item and name it "SSSMembersDataSet.xsd." Select the language for your project, in our example, "Visual Basic" and select the "Add" button.



The DataSet screen will open and you may "Add" a "TableAdapter."



You will be asked to "Choose Your Data Connection." A drop down list will appear with our existing "Members.mdf" showing as a possible selection. Select it and then select the "Next" button.



You will be given the opportunity to "Save the Connection String to the Application Configuration File." Make sure the box is checked for "Yes, save the connection as:" and name your connection "SSMembersConnectionString." Select the "Next" button. Visual Web Developer has now added the connection string to the web.config file.



Web.config ConnectionString

Let's take a moment to look at our connectionString as generated in our web.config file by the wizard. You may check yours later after completing the Wizard. 

<configuration>
  <appSettings/>
  <connectionStrings>
    <add name="SSMembersConnectionString" 
         connectionString="Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|\SSMembers.mdf;
         Integrated Security=True;User Instance=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
Creating our TableAdapter Select Queries

The TableAdapter Configuration Wizard will ask you to Enter a SQL Statement. The TableAdapter will use the data returned by the SQL statement to fill the DataTable. You may type the command or use the QueryBuilder. We'll simply type "Select * from members" as our query string. Select "Next."



Click the "Advanced Options" button. Uncheck the box to "Generate Insert, Update and Delete statements." We'll be building our own Insert statements. Creating functionality for Updates and Deletes is beyond the scope of this tutorial. Select "OK."



Now you'll be asked to "Choose Methods to Generate." Uncheck the "Fill a DataTable" method. Leave the "Return a DataTable" method checked. Name it "GetMembersData" and select "Next."



The Wizard will now generate the SELECT statement, table mappings, and Get method. Click "Finish" to add the components to the dataset.



After clicking "Finish" you'll see your new Members Table Adapter with the column names, and new GetMembersData query.

For our import, each imported record will have one category assigned. So we want to create a table adapter for our Categories table. Right click anywhere on the blue screen and select to "Add" "TableAdapter."



The TableAdapter Configuration Wizard will ask you for a data connection. Select the SSMembersConnectionString as setup in the Web.config in the drop down menu, and then select the "Next" button.



You will now be asked to "Enter a SQL Statement." Type Select * from categories. Select the "Advanced Options" button and deselect the checkboxes as we did previously. Then select the "Next" button.



On the next screen, deselect the "Fill a DataTable" method and change the name for the "Return a DataTable" method to "GetCategoryData." Select the "Next" button.



If you select the "Query Builder" button, you may view the available columns for selection and Execute the Query before finalization. Select "OK" when done.



You may now see your newly generated query string and select the "Next" button.



The SELECT statement, table mappings, and GET method will be generated. You may select the "Finish" button.



Now that we have created our DataSet and TableAdapters, we need to create our custom Select and Insert queries with parameters. Section 7: TableAdapter Select and Insert Queries with Parameters .

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.