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 past four installments in this article series we have: created a SQL Server database for our web application; created an ASP.NET web page that enabled the visitor to upload an Excel spreadsheet and view its contents in a GridView; and created a Data Access Layer (DAL) using Typed DataSets for accessing our application database. All that remains is to import the uploaded Excel spreadsheet's data into the application database.
In a perfect world, a user would carefully check the data in the Excel spreadsheet and cross-reference it with the data already existing in the application database. But our users are humans and bound to err. Therefore, it behooves us to design the import functionality so that it can gracefully handle common types of mistakes. We'll examine two such cases: preventing duplicate entries and ensuring that the imported data is in the appropriate format.
Read on to learn more!
From the Internet.com eBook Library: Navigating Your IT Career
A career in information technology usually has its share of ups and downs. Download this Internet.com eBook to learn where the jobs are in IT, how to negotiate a salary, and helpful advice on job security and how to deal with a layoff. Join Internet.com now to download! http://www.devx.com/ebook/Link/34938
Interested in placing your TEXT AD HERE? Click Here
Importing the Uploaded Excel Spreadsheet In the Building the Importer Web Page and Uploading the Excel Spreadsheet article we created a web page with three Button Web controls for uploading, viewing, and importing the spreadsheet. Past installments have looked at handling the uploading and viewing buttons; we're now ready for the importing Button.
Start by creating an event handler for the ButtonImport Button's Click event. The first order of business is to hide the upload- and view-related Panels and show the import-related Panel (PanelImport). The PanelImport Panel includes a Label Web control named LabelImport that displays information about the import progress, such as whether certain Excel spreadsheet rows were not imported because they already exists in the database. In addition to showing PanelImport we also need to clear out this Label's Text property.
The following code shows the start of the ButtonImport's Click event handler.
Protected Sub ButtonImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonImport.Click
Next, we need to create an OleDbCommand and call its ExecuteReader method to retrieve the contents of the Excel spreadsheet. In Displaying the Uploaded Excel Spreadsheet we created an ExcelConnection() method that included code that connected to the uploaded Excel spreadsheet and returned an OleDbCommand whose CommandText was set to SELECT * FROM [Members$] (which returns all columns and rows from the Members worksheet).
To import the data we need to call the ExcelConnection() method to get back a configured OleDbCommand object and then utilize the OleDbCommand's ExecuteReader method to get an OleDbReader object so that we can step through the results one record at a time. The following code accomplishes this:
' retrieve the Select Command for the worksheet data Dim objCommand As New OleDbCommand() objCommand=ExcelConnection()
' create a DataReader Dim reader As OleDbDataReader reader=objCommand.ExecuteReader()
Now that we have an OleDbReader object we can step through the Excel spreadsheet's rows one at a time. For each row we read the values in for the various Excel spreadsheet columns using the syntax reader("columnName"). (For more information on retrieving data from a DataReader, see Retrieving Values from a DataReader.) This information is then used to add a new record to the Members table in the application database. The following code shows the general pattern with a few TODOs that we will return to later.
While reader.Read() ' set default values for loop Dim member_id As Integer = 0 Dim category_id As Integer = 0
Dim lastname As String = Convert.ToString(reader("lastname")) Dim firstname As String = Convert.ToString(reader("firstname")) Dim address1 As String = Convert.ToString(reader("address1")) Dim address2 As String = Convert.ToString(reader("address2"))
Dim city As String = Convert.ToString(reader("city")) Dim state As String = Convert.ToString(reader("state")) Dim zip As String = Convert.ToString(reader("zip")) Dim phone As String = Convert.ToString(reader("phone"))
Dim fax As String = Convert.ToString(reader("fax")) Dim email As String = Convert.ToString(reader("email"))
' Notice the space in the "web site" column from the Excel Spreadsheet Dim website As String = Convert.ToString(reader("web site")) Dim category As String = Convert.ToString(reader("category"))
'TODO: Insert any required validations here...
'TODO: Get (or create) the corresponding category_ID
'TODO: Insert a record into the Members table (if it's not a duplicate)
'Display information in LabelImport LabelImport.Text &= member_id & lastname & _ " " & firstname & " " & address1 & " " & address2 & _ " " & city & " " & state & " " & zip & " " & _ phone & " " & fax & " " & email & " " & website & _ " cat_id: " & category_id & " " & category & "<br>" End While reader.Close() End Sub
Let's look at each TODO.
Validating Data Because all the Excel spreadsheet columns are string values and because the Members table allows NULL values for all its columns, we don't need to perform any sort of validation. If, however, the Excel spreadsheet prompted users for numeric, Boolean, or date information (such as the member's birthdate), we would want to ensure that the value entered by the user in the Excel spreadsheet was in the appropriate format.
Furthermore, our application's business rules may indicate that certain fields are required or must be one of a certain set of legal values. For example, our application might require that a member always have an associated category or email address. Or it may be the case that all members must have a unique email address (that is, we cannot have two or more members with the same email address).
Such validation is a bit beyond the scope of this article, so I leave this as a topic for the reader to explore on her own. In a nutshell, you can add any required checks at the TODO line in the code above. For example, the following code snippet shows how you could require that a value was entered for the last name.
'Ensure the import data includes a last name for each member If lastname.Length = 0 Then LabelImport.Text &= "You must provide a last name!" Else 'TODO: Get (or create) the corresponding category_ID value
'TODO: Insert a record into the Members table (if it's not a duplicate) End If
Getting (or Creating) the Corresponding category_ID Value Presumably the Excel spreadsheet will be filled out by a human (not auto-generated by a computer program). Consequently, when prompted to specify the category for a member, a user will enter the name of the category (Development, Digital Imaging, etc.) rather than the corresponding category_id value (1, 2, etc.). However, the Members database links back to the Category database via its category_id column. Therefore, we need to take the category name entered by the user and determine its category_id. Moreover, if the category cannot be found then we need to create a new record in the Category table.
This work is handled by the GetCategoryID(categoryName) helper function. GetCategoryID(categoryName) checks to see if categoryName exists; if so, it returns the corresponding category_id value. If categoryName does not exist, GetCategoryID(categoryName) inserts the category into the table and returns the new category_id value. We'll create this GetCategoryID() function later in this article. For now, here is what the code will look like from the Button Click event handler:
'Get (or create) the corresponding category_ID category_id = GetCategoryID(category)
Inserting the Excel Row Into the Members Database Table After determining the appropriate category_id value we need to take the values from the current Excel row and use them in inserting a new record into Members. However, we don't want to perform the insert if the row already exists in the table. This duplicate check, along with the actual insert into the database table, is handled by a helper function named ImportIntoMembers.
ImportIntoMembers takes as input the various fields that describe a member (last name, first name, address, etc.). It then checks to see if a member with these field values already exists. If so, it displays an appropriate message in LabelImport; otherwise, it inserts the record and returns the value of the just-inserted record's member_id column.
We will create this helper method in a moment. For now, let's examine how it is called from the Button's Click event handler:
'Insert a record into the Members table (if it's not a duplicate) member_id = ImportIntoMembers(lastname, firstname, address1, address2, city, state, zip, phone, fax, email, website, category_id)
Communicating with the Application Database Using the Data Access Layer (DAL) At this point we still need to create the GetCategoryID(categoryName) and ImportIntoMembers helper functions. These two functions communicate with the application database to determine the category_id value (and create a new category, if needed) and to insert a record into the Members table (if it is not a duplicate). While we could interface with the application database directly through our ASP.NET page's base class, a better alternative is to use a Data Access Layer (DAL). We built our DAL in the preceding article, Creating a Data Access Layer. We are now ready to use it to perform the remaining logic.
Let's start by coding the GetCategoryID(categoryName) method. Create a method named GetCategoryID using the following code:
Protected Function GetCategoryID(ByVal categoryname As String) As Integer Dim category_id As Integer = 0
Try
Catch ex As Exception LabelImport.Text &= ex.ToString Return 0 End Try End Function
We need to now add code within the Try block to connect to the database via the DAL. Recall that the DAL's methods are accessible through its TableAdapter classes, which are available in the SSSMembersDataSetTableAdapters namespace. Therefore, type in Dim SSAdapter As New SSSMembersDataSetTableAdapters and then hit the period (.). As the following screen shot shows, Visual Web Developer's IntelliSense lists the available classes in the namespace. There are two: CategoryTableAdapter and MembersTableAdapter. Because we need to work with categories here, use the CategoryTableAdapter.
The CategoryTableAdapter's GetCategoryByName method returns information about a category by a specified name. This method, like all TableAdapter methods that return a set of rows, return data in the form of their corresponding strongly-typed DataTable. In other words, the GetCategoryByName method returns a CategoryDataTable object. If the category is found, then the returned CategoryDataTable contains a single row; if the category was not found, then it contains zero rows.
Protected Function GetCategoryID(ByVal categoryName As String) As Integer Dim category_id As Integer=0
Try Dim SSAdapter As New SSSMembersDataSetTableAdapters.CategoryTableAdapter Dim SSDataTable As SSSMembersDataSet.CategoryDataTable=Nothing SSDataTable=SSAdapter.GetCategoryByName(categoryName)
' see if the category already exists in the table, if not insert it If Not SSDataTable Is Nothing Then If SSDataTable.Rows.Count > 0 Then If Not SSDataTable(0).category_id=Nothing Then category_id=SSDataTable(0).category_id End If End If End If
If the passed in categoryName is found in the Category table then its category_id value is assigned to the local variable category_id. If, however, no matching record is found then category_id will have its initial value, 0. In this case we need to insert a new record into the Category table and return the just-inserted category_id value.
If category_id = 0 Then ' retrieve the identity key category_id from the insert category_id = Convert.ToInt32(SSAdapter.InsertCategoryQuery(categoryname)) End If
Return category_id
Catch ex As Exception LabelImport.Text &= ex.ToString Return 0 End Try End Function
Importing Members Into the Members Table With the GetCategoryID(categoryName) method complete, all that remains is to code the ImportIntoMembers function.
First we need to define the function and its input parameters:
Protected Function ImportIntoMembers(ByVal lastname As String, _ ByVal firstname As String, ByVal address1 As String, _ ByVal address2 As String, ByVal city As String, ByVal state As String, _ ByVal zip As String, ByVal phone As String, ByVal fax As String, _ ByVal email As String, ByVal website As String, _ ByVal category_id As Integer) As Integer
The columns in the Members database table have fixed widths. For example, lastname may not exceed 50 characters, while website may be between 0 and 200 characters. However, the Excel spreadsheet imposes no length limits. Therefore we need to truncate lengthy inputs to their maximum size. If you omit this truncation, SQL Server will throw an error if you attempt to import a column value whose length exceeds its defined bounds.
First we need to define the function and its input parameters:
Much like how we determined if a match category existed given a category name, we need to perform similar logic to determine whether there is a duplicate member already in the database. What constitutes a duplicate may differ by application. By that I mean that some applications might consider two records as duplicates if the members share the same last and first names; for others, two member records may only be considered duplicated if every single field has matching values. For our application, I've made it so that two members are considered duplicates if they have the same first and last names and the same address.
The following code calls the MembersTableAdapter's GetMemberByNameAddress method, passing in the first name, last name, and address values for the current Excel row. If there is a matching record, a message is displayed in the LabelImport Label control.
Dim member_id As Integer = 0
Try Dim SSAdapter As New SSSMembersDataSetTableAdapters.MembersTableAdapter Dim SSDataTable As SSSMembersDataSet.MembersDataTable=Nothing SSDataTable=SSAdapter.GetMemberByNameAddress(firstname, lastname, address1)
' see if the category already exists in the table, if not insert it If Not SSDataTable Is Nothing Then If SSDataTable.Rows.Count > 0 Then If Not SSDataTable(0).member_id=Nothing Then member_id=SSDataTable(0).member_id LabelImport.Text=LabelImport.Text & _ "<font color=blue>Member Found, Not Imported: " & _ " ID: " & member_id & " " & lastname & " " & firstname & _ " address: " & address1 & ".</font><br>" End If End If End If
Next, we check if member_id is 0. If so, then no duplicate was found so we insert a record into the Members table using the MembersTableAdapter's InsertMemberQuery method.
If member_id=0 Then ' retrieve the identity key member_id from the insert member_id = Convert.ToInt32(SSAdapter.InsertMemberQuery(lastname, firstname, _ address1, address2, city, state, zip, phone, fax, _ email, website, category_id))
Catch ex As Exception LabelImport.Text &= "<font color=red>" & ex.ToString & "</font><br />" Return 0 End Try End Function
Now when we use the Import Excel Data button, our function will check for duplicates and insert the data to our database tables and write the results to the screen.
Conclusion This article concludes this multi-part series on importing an Excel spreadsheet into a SQL Server database using Typed DataSets and TableAdapters. In this tutorial we've learned how to setup an ASP.NET website, created a SQL Server Database with two tables, use the Identity Specification property, and created a data access layer with TableAdapters. We've learned how to use various Web controls including the FileUpload, Panel, GridView, Button and Label. We've seen how to populate a GridView using a TableAdapter, how to read data from an Excel Spreadsheet and use an OleDbDataReader.
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/.
All newsletters are sent from the domain "internet.com." Please use this domain name (not the entire "from" address, which varies) when configuring e-mail or spam filter rules, if you use them.
No comments:
Post a Comment