When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article
Jobs

ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
 
Print this Page!
Published: Wednesday, March 19, 2008

Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Creating a Data Access Layer (DAL)

By Nannette Thacker


Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters
This article is one in a series of articles on importing a Microsoft Excel spreadsheet into a database table.

  • Building the Database - provides an overview of the tutorial series and the first steps, including creating the website project and database tables.
  • Building the Importer Web Page and Uploading the Excel Spreadsheet - adds the interface for uploading an Excel spreadsheet from the user's computer and saving it on the web server's file system.
  • Displaying the Uploaded Excel Spreadsheet - shows how to query and display the contents of an Excel spreadsheet in a GridView control.
  • Creating a Data Access Layer (DAL) - creates the Data Access Layer (DAL) used to communicate with the application database during the import process.
  • Importing the Excel Spreadsheet - looks at importing the uploaded Excel spreadsheet into the application database tables using the DAL.
  • Introduction


    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!

    - continued -

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

    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 SELECT, INSERT, DELETE, and UPDATE statements.

    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.

    Add an App_Code folder.

    I like to keep my Typed DataSet files separate from my other classes. To follow along, add a new subfolder to the App_Code folder named DataSets. Now right click on the DataSets folder and choose Add New Item. Select the DataSet item, name it SSSMembersDataSet.xsd, and click Add.

    Add a DataSet to the project.

    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.

    Choose the database to connect to.

    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 Web.config file's <connectionStrings> 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.)

    Save the connection string to Web.config.

    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 Select * from members, which returns all columns and all rows from the Members table.

    Define the TableAdapter's main SELECT query.

    By default, the TableAdapter automatically generates corresponding INSERT, UPDATE, and DELETE statements based on the SELECT query entered in this step. However, we do not need UPDATE or 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 auto-generate INSERT, UPDATE, and DELETE statements.

    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.

    Uncheck the Generate Insert, Update and Delete statements checkbox.

    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 GetData to GetMembersData and 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, GetMembersData.

    Because our import involves two tables (Members and 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.

    The Members DataTable and TableAdapter have been added to the Typed DataSet.

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

    SELECT category_id, categoryname FROM Category

    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 GetData to GetCategoryData and click Next.

    Specify the details for the TableAdapter's methods.

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

    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 Members table. 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 Update method.)

    Add a new select query to the Members TableAdapter.

    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:

    Select member_id from members
    where firstname=@firstname
          and lastname=@lastname
          and address1=@address1

    The astute reader may have noticed that the above query does not query on the Members table's address2 column (nor its city, state, or 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 SELECT statement above only returns one column, member_id, but the MemberDataTable has a host of properties, including lastname, firstname, city, and others. This SELECT statement is valid, however, because all of the DataTable's properties (except for member_id) allow NULL values. The reason for this is because all of the columns in the Members table allow NULLs (except for member_id). So when this query is executed, the TableAdapter uses NULLs for all other columns in the DataTable.

    If, however, we had additional non-NULL properties in the DataTable we would need to return a value for these columns in the SELECT statement. As a general rule of thumb, it's wise to have the select query return the precise set of columns returned by the TableAdapter's main query (the one defined in the TableAdapter Configuration Wizard). This ensures that there will be no mismatch between the returned SELECT query data and the properties in the corresponding DataTable.

    Click Next. The subsequent step prompts us to name the method. Enter GetMemberByNameAddress and then click Next and then Finish.

    Name the new method GetMemberByNameAddress.

    At this point you are returned to the Typed DataSet's designer; the Member TableAdapter should now include two methods: GetMembersData() and GetMemberByNameAddress(@firstname, @lastname, @address1).

    Creating a Parameterized Insert Query


    If the GetMemberByNameAddress select query indicates that the Excel spreadsheet row does not exist in our table we need to then insert the record into the Members table. 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 INSERT statement.

    Add an INSERT method to the Members TableAdapter.

    The next screen prompts us to enter the INSERT statment. Type in the following:

    INSERT INTO Members(lastname, firstname, address1, address2, city, state, zip, phone, fax, email, website, category_id)
    VALUES (@lastname, @firstname, @address1, @address2, @city, @state, @zip, @phone, @fax, @email, @website, @category_id);

    -- return the member_id
    SELECT SCOPE_IDENTITY()

    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 the just-inserted IDENTITY column value. This can be accomplished by using SCOPE_IDENTITY(), as shown in the above command text.

    After entering the above INSERT statement click Next and name the method InsertMemberQuery. 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 ExecuteMode property. Change it from NonQuery to Scalar. If you forget to change the ExecuteMode property to 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 Members table our application database includes a Category table. 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.

    Add a new methods to the Category TableAdapter.

    Choose to specify the ad-hoc SQL statement for this query, and choose the INSERT query type. Use the following INSERT statement:

    INSERT INTO Category(categoryname)
    VALUES (@categoryname);

    -- return the category_id
    SELECT SCOPE_IDENTITY()

    Finally, name the method InsertCategoryQuery.

    As we did with the Members TableAdapter's insert query, we need to set the method's ExecuteMode property to Scalar so that the newly inserted record's IDENTITY 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 NonQuery to Scalar.

    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:

    SELECT category_id, categoryname
    FROM Category
    WHERE categoryname=@categoryname

    Name the method GetCategoryByName.

    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!

    By Nannette Thacker


    Further Readings:

  • Data Access Tutorials
  • Designing Data Tier Components and Passing Data Through Tiers
  • Returning the Just-Inserted ID Value Using Typed DataSets
  • SCOPE_IDENTITY() Technical Documentation
  • Attachments


  • Download the Application (in ZIP format)
  • 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. Her blog is online at http://weblogs.asp.net/nannettethacker/.

    Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters
    This article is one in a series of articles on importing a Microsoft Excel spreadsheet into a database table.

  • Building the Database - provides an overview of the tutorial series and the first steps, including creating the website project and database tables.
  • Building the Importer Web Page and Uploading the Excel Spreadsheet - adds the interface for uploading an Excel spreadsheet from the user's computer and saving it on the web server's file system.
  • Displaying the Uploaded Excel Spreadsheet - shows how to query and display the contents of an Excel spreadsheet in a GridView control.
  • Creating a Data Access Layer (DAL) - creates the Data Access Layer (DAL) used to communicate with the application database during the import process.
  • Importing the Excel Spreadsheet - looks at importing the uploaded Excel spreadsheet into the application database tables using the DAL.


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article