C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import
Section 7: TableAdapter Select and Insert Queries with Parameters
by Nannette Thacker

As seen in previous sections of this Tutorial, our goal is to import data from an uploaded Excel Spreadsheet into our database tables. In our last section, we covered creating our DataSet and TableAdapters for our Members and Categories tables. But for our purposes, we need select and insert queries that allow passing of parameters.

Creating our Select Query with Parameters

For our import function, we want to check if the first name, last name and address already exist in our table. If so, we won't insert a duplicate record for the same person. This will allow future additions to the same spreadsheet and future imports, without inserting duplicate records. So we will now create another query for our table adapter that Gets Members by their Name and Address. We will name this GetMemberByNameAddress and pass in our Firstname, Lastname, and Address1. So right click the "MembersTableAdapter" bar and select to Add Query. (Updating existing records is beyond the scope of this tutorial, but may easily be added with an "Update" method.)



Our new query will be:

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


Name the method "GetMemberByNameAddress" and select "Next." Then select "Finish" to complete the creation fo the method.



Creating our Insert Query with Parameters

If our select query indicates that the record does not exist in our table, we'll need functionality to then insert the record into the table. So let's create our Insert query. Right click our MembersTableAdapter and select to "Add Query..." The TableAdapter Query Configuration Wizard will ask you to Choose a Command Type. Select to "Use SQL Statements." Then select the "Next" button.



You will be asked to "Choose a Query Type." Select an "Insert" type to add a new row to the table.



You will now be allowed to type in your INSERT statement. We are going to type in our statement as:

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()
Previously, in Section 1: Creating Our Project, Database and Tables, we created our Members table and setup our member_id as an Identity key using the "Identity Specification" property. In our insert query, we want to retrieve the value of the newly created member_id field. So we are now going to retrieve it by using SELECT SCOPE_IDENTITY() following our Insert query.



For more information on the IDENTITY field, see Scott Mitchell's article Retrieving Scalar Data from a Stored Procedure. For further details on the SCOPE_IDENTITY() function, see Microsoft's Transact-SQL Reference SCOPE_IDENTITY article.

Let's name our new function "InsertMemberQuery." Select the "Next" button.



And our INSERT statement is now generated by the TableAdapter Query Configuration Wizard. Select "Finish" to apply the settings to our query.



Set the Execute Mode Property to Scalar

This section is very important to remember. If you wish the tableadapter to return the value of the identity key, in our case the member_id field, after insertion, you must set the 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 your Properties window, you will see the "ExecuteMode" property. Select the drop down list and change it from NonQuery to Scalar.



If you forget this option, the return value will always be 1, indicating the number of rows that were affected, rather than the value of the identity key. For further details on the Scalar method see Scott Mitchell's blog Returning the Just-Inserted ID Value Using Typed DataSets

Creating our Category Select and Insert Queries

In our tutorial, our spreadsheet contains a column, "Category." For the purposes of this tutorial, rather than adding this text to our Members table, we use the Category table that we created earlier. We'll use a query statement to select the categoryname from the Category table and see if it already exists. If it exists, we'll retrieve the category_id and save it in the Members table. If it doesn't exist, we'll insert the new category into our Category table.

So to accomplish this, let's add two more queries to our CategoryTableAdapter. Right click the "CategoryTableAdapter" bar and select to "Add Query."




We're going to add our insert query as:

INSERT INTO Category(categoryname) VALUES (@categoryname);
-- return the category_id
SELECT SCOPE_IDENTITY()
Select the "Next" button.



Name our query "InsertCategoryQuery" and select "Next."



The wizard will generate the INSERT statement. Select "Finish."



As we did with the members insert query, we also want to set the execute mode property to Scalar for the category insert query. Right click on the InsertCategoryQuery in the CategoryTableAdapter and select the Properties menu item. In the left column of your Properties window, you will see the "ExecuteMode" property. Select the drop down list and change it from NonQuery to Scalar.

For our import method, when importing a row, we want to check and see if the category already exists, and if not insert it. So we need a select query to see if the category already exists in the table, then if not, we will insert the category. So let's right click the CategoryTableAdapter and "Add" a new Select query. 

SELECT     category_id, categoryname
FROM         Category where categoryname = @categoryname


We're going to select to "Return a DataTable" and name our method "GetCategoryByName." Select "Next."



Our SELECT statement and Get method are generated. Select "Finish."



And we may now see the new "GetCategoryByName" method added to our "CategoryTableAdapter."



Our TableAdapters are now complete and we are ready to hookup our "Import Excel Data" button functionality in our codebehind. The first thing we'll need to do is to retrieve each column from our Excel Spreadsheet so that it may be imported into our database tables. To do this, we'll create a DataReader using an OleDbDataReader. So let's continue with Section 8: Using an OleDbDataReader to Retrieve Our Data .

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.