Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Creating a Data Access Layer (DAL)By Nannette Thacker
Over the course of the past three tutorials we have created an ASP.NET page that allows the visitor to upload and display an Excel spreadsheet in a GridView. Our end goal is to allow the user to import the rows in the spreadsheet into the two tables of our application database, a task we will complete in this fourth installment.
In Displaying the Uploaded Excel Spreadsheet we saw how to use an
OleDbConnection to programmatically connect to the Excel spreadsheet and bind it to the GridView. Likewise, we could use this same style
of coding to insert data into our application database tables - connecting to the SQL Server database from code within the ASP.NET page
and issuing the
INSERT statements. A better approach, however, is to design a tiered architecture that separates out the different
responsibilities into different layers. Specifically, we will place the code for inserting data into our database into a separate Data Access
Layer (DAL), which we will create using Typed DataSets.
With the DAL created, we can then wrap up the page's core functionality: connecting to the Excel spreadsheet, enumerating its rows, and using the DAL to insert the rows into the database. Read on to learn more!
Creating our Data Access Layer TableAdapters
While it is possible to directly query and work with databases from an ASP.NET page's code-behind file, a better solution is to move such logic into a separate Data Access Layer (DAL). The main benefit of using a DAL versus placing the logic directly within the ASP.NET pages is that it decouples the data access logic from the presentation logic, thereby making the code more readable, updateable, and maintainable.
Typically the DAL returns strongly-typed objects that represent the data being worked with. For example, the DAL might include a method that
returns information about a particular Member from our
Members table. Rather than returning a SqlDataReader or loosely-typed DataSet,
ideally the DAL would return some object that had properties with names and types that mirrored the
There are a number of different techniques and tools for creating a Data Access Layer. One option is Typed DataSets. In a nutshell, Typed
DataSets generate a series of strongly-typed DataTables and TableAdapters. Each DataTable has properties that correspond to columns returned by a
specified query and serve as the business objects used to ferry information from the DAL to the ASP.NET pages. TableAdapters are the workhorses of the
DAL; they are composed of methods that communicate with the database and issue the
|Learn More About Using Typed DataSets|
|This article focuses on the step-by-step instructions for creating a DAL using Typed DataSets. It does not delve into the background of Typed DataSets or explore topics not directly related to the main task at hand. For a more in-depth background, refer to Scott Mitchell's 75-part Working with Data in ASP.NET tutorial series.|
Let's get started creating the Typed DataSet! The first order of business is to add an
App_Code folder to our website, which is where the
Typed DataSet file will be located. In your project's Solution Explorer, right click the project name and select
Add ASP.NET Folder. Choose the
App_Code folder option.
I like to keep my Typed DataSet files separate from my other classes. To follow along, add a new subfolder to the
DataSets. Now right click on the
DataSets folder and choose Add New Item.
Select the DataSet item, name it
SSSMembersDataSet.xsd, and click Add.
This creates a new Typed DataSet and may immediately add a new TableAdapter to the DataSet. (If a new TableAdapter isn't immediately added, you can drag one from the Toolbox onto the Typed DataSet's designer.) Whenever a new TableAdapter is added to the Typed DataSet, the TableAdapter Configuration Wizard is displayed, which prompts you to specify the database to work with and the SQL query (or stored procedure) to use to populate the corresponding strongly-typed DataTable.
The TableAdapter Configuration Wizard starts by prompting you to select a database to work with. The drop-down list shown in the screen shot below
should include our existing
SSMembers.mdf database file. Select it and then click Next.
The next screen gives you 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. This saves the connection string to the
section using the name provided. (For more information on how this value is saved, as well as programmatically retrieving it, please read
Working with Databases in ASP.NET and Visual Studio.)
Creating the TableAdapter's Queries
After specifying a connection string name, the subsequent wizard screen asks you how the TableAdapter should access the database: using SQL statements, by creating a new stored procedure, or by using existing stored procedures. For this application, let's use ad-hoc SQL statements. Therefore, select the first radio button and click Next.
The TableAdapter Configuration Wizard next asks for a SQL statement. The TableAdapter will use the data returned
by this SQL statement to define the strongly-typed DataTable's properties; furthermore, the TableAdapter will create a method
to populate the DataTable and will use this query to do so. You may type the
command or use the QueryBuilder. For this TableAdapter, use the query
from members, which returns all columns and all rows from the
By default, the TableAdapter automatically generates corresponding
DELETE statements based on the
SELECT query entered in this step. However, we do not need
DELETE capabilities for this application, just
INSERT. Also, we will be
creating our own TableAdapter
INSERT method later on in this article. Therefore, we do not need the TableAdapter
To disable the auto-generation of these SQL statements, click the Advanced Options button and uncheck the "Generate Insert, Update and Delete statements" checkbox and then click Next to progress to the subsequent wizard step.
You'll be asked to choose what methods to generate and to name these methods. The options you choose here dictate what
methods the TableAdapter will create for querying data. Uncheck the "Fill a DataTable" option, but leave the
"Return a DataTable" option checked. Change the method name from
click Next. (For a thorough explanation of these different options, read
Scott Mitchell's tutorial Creating a Data Access Layer.)
The wizard will now generate the
SELECT statement, table mappings, and Get method. Click the Finish button to
complete the TableAdapter creation process. After clicking Finish you'll see the new Members DataTable
with column names and types corresponding to the columns returned by the
SELECT query specified during the
wizard. In addition, there's a Members DataTable with one method,
Because our import involves two tables (
Category), we need to create another DataTable/TableAdapter
in our Typed DataSet for the
Category table. Right click anywhere within the Typed DataSet's designer and
choose to Add a new TableAdapter. This re-launches the TableAdapter Configuration Wizard.
As before, the TableAdapter Configuration Wizard prompts for the database to work with. This time, however, the drop-down list
includes the connection string name (
SSMembersConnectionString) - select this name. As we did earlier, opt
to specify the TableAdapter's query using ad-hoc SQL statements and then enter the following
SELECT statement and
The next wizard step asks you to select what methods to create (and their names). As before, uncheck the
"Fill a DataTable" option, but leave the "Return a DataTable" option checked. Change the method name from
GetCategoryData and click Next.
Click Next and then Finish to complete the TableAdapter Configuration Wizard. At this point your Typed DataSet should contain
two DataTable/TableAdapter pairs: one for the
Members table and one for
Creating Parameterized Select Queries
A common challenge when importing data is ensuring that duplicate records are not added to the target database. In the case of an Excel spreadsheet, if two different users each inadvertently import the same spreadsheet at different times of the day, the net effect will be that each row is duplicated in the application database. To prevent duplicates we need to check if the imported member's first name, last name and address already exist in the
Memberstable. If so, we won't insert a duplicate record for the same person.
To create such a safeguard we need to add a method to our Members DataTable that returns whether a specific first name, last name and address combination already exists in the database. In such a case, we do not want to import the information.
To add a select query to the TableAdapter, right click the
MembersTableAdapter title bar in the Typed DataSet designer and choose Add Query. (Updating
existing records when importing is beyond the scope of this tutorial, but may be added with an
Adding a new query launches the TableAdapter Query Configuration Wizard, which is very similar to the TableAdapter Configuration Wizard we used to create the Members and Category TableAdapters earlier in this article. Start by choosing to create a new query using SQL statements and then click Next.
The following wizard step asks what type of SQL query to use: one that returns rows, one that returns a single value, or
an update, delete, or insert. Because this query will return information about a particular
Member (one with
a specified last name, first name and address), choose the first option, SELECT which returns rows and click Next.
Like when creating a new TableAdapter, when creating a query we are prompted for a SQL statement. This is the statement the TableAdapter will send to the database when this method is called. Because we want to return information about a user with a specified first name, last name, and address, enter the following query:
The astute reader may have noticed that the above query does not query on the
address2 column (nor its
zip columns) to determine if
there is a duplicate import record. I encourage the interested reader to add this functionality.
|A Note About the Columns Returned by Select Queries|
When a TableAdapter's select query is executed, the TableAdapter connects to the database and issues the specified query.
It then takes the results and populates them into a corresponding strongly-typed DataTable object (MemberDataTable, in this
instance). Consequently, it is vital that the columns returned by the select query map to those defined in the DataTable.
Note that our
If, however, we had additional non-NULL properties in the DataTable we would need to return a value for these columns
Click Next. The subsequent step prompts us to name the method. Enter
GetMemberByNameAddress and then
click Next and then Finish.
At this point you are returned to the Typed DataSet's designer; the Member TableAdapter should now include two methods:
GetMemberByNameAddress(@firstname, @lastname, @address1).
Creating a Parameterized Insert Query
GetMemberByNameAddressselect query indicates that the Excel spreadsheet row does not exist in our table we need to then insert the record into the
Memberstable. To accomplish this we need to create an insert query for the Members TableAdapter. Right-click the MembersTableAdapter and choose Add Query. As before, specify that this insert query will come from a SQL statement, but this time instruct the TableAdapter Query Configuration Wizard that we will be creating an
The next screen prompts us to enter the
INSERT statment. Type in the following:
Recall that the
Members table's primary key (
member_id) is an
IDENTITY column, meaning
that its value is automatically assigned the next available number by the database system. At times it's helpful to retrieve
IDENTITY column value. This can be accomplished by using
shown in the above command text.
After entering the above
INSERT statement click Next and name the method
Then click Next and Finish to complete the wizard.
There is one last very important step to perform before we move on.
For the new insert query to properly return the value of the
SELECT SCOPE_IDENTITY() statement you
must set the method's
ExecuteMode property to
Scalar. To do this, right click on the
InsertMemberQuery in the MembersTableAdapter and select the
Properties menu item. In the left column of the Properties window you will see the
Change it from
Scalar. If you forget to change the
Scalar the method will return the number of rows affected (1)
rather than the value of the just-inserted
IDENTITY column. For further details on these steps
see Scott Mitchell's blog entry Returning the Just-Inserted
ID Value Using Typed DataSets.
Creating the Select and Insert Queries for the Category TableAdapter
In addition to the
Memberstable our application database includes a
Categorytable. Importing an Excel spreadsheet may add rows to both of these tables. Consequently, we need to add select and insert queries for the Category TableAdapter, just like we did for the Member TableAdapter.
Let's start by adding the insert query. Right click the CategoryTableAdapter title bar and choose Add Query.
Choose to specify the ad-hoc SQL statement for this query, and choose the
INSERT query type. Use the following
Finally, name the method
As we did with the Members TableAdapter's insert query, we need to set
ExecuteMode property to
Scalar so that the newly inserted record's
column value is properly returned. Right-click on the
InsertCategoryQuery method in the Category TableAdapter
and select the Properties menu item. Then change the
ExecuteMode property's value from
Finally, we need a method to determine if there already exists a category with the same name. Create a select query in the Category TableAdapter using the following ad-hoc SQL statement:
Name the method
Our TableAdapters are now complete. We are now ready to hookup the "Import Excel Data" button functionality in our ASP.NET page to the actual importing logic. We'll tackle this step in the final installment of this article series. Until then, may your dreams be in ASP.NET!
About the Author: