I am sure that most of us before going for shopping on a certain occasion, we have to dig up our mind to draft a shopping list. However, after shopping, we unfortunately would say: "Oh, I forgot to buy this and that!".
This tutorial will explain how to build a shopping list using ASP.NET 2.0 with MS SQL database. Based on this article, some cool technical stuff in Visual Web Developer will be used to facilitate our work.
You can download Sample Project Shopping List related to this tutorial.
Functionalities
The functionalities that will de developed are:
- Create a new shopping list
- Searching and displaying the shopping list
- Get a printed copy of the shopping list
Database Design
There will be 2 tables for holding information of the shopping list and the items pertaining to a specific shopping list.
Figure 1. ListCategory table for holding shopping list title
Figure 2. Items_List_rel table for holding the items of the shopping list
Screen Design
Figure 3. Screen design for creating a new shopping list
Figure 4. Using a dropdown list for populating available shopping lists, and datalist for populating the list of items. The items will be in a tabular format.
Figure 5. Choosing a data source for listing the shopping list items
Figure 6. Set the data source to the new database created and save the connection string to the web.config file when asked to.
Creating a new shopping list
To create a new shopping list, the title of the shopping list, a title and the list of items should be supplied. We will assume that the items will be separated by linefeeds. So, each item in the list will be found on a separate line.
The next step is to insert the information in the tables; and 2 stored procedures will be used for that. They are simple stored procedures as illustrated below.
ALTER PROCEDURE dbo.NewList
@Title VARCHAR(500)
AS
-- First, find the next ID that should be assigned to the title
DECLARE @nextID INT,
@maxID INT
SET @nextID = 1 -- Assigns next id as if no title has been added
-- Gets the maximum list id from the table
SELECT @maxID = MAX(ListId) FROM ListCategory
IF @maxID IS NOT NULL
BEGIN
SET @nextID = @maxID + 1
END
/* ENDIF */
-- Finally, insert the title associating it with the maximum id
INSERT INTO ListCategory VALUES (@nextID, @Title)
RETURN
From the code above, the title associated with the shopping list is inserted in the database. An ID is associated with the description of the shopping list. The ID will be useful to group together the items belonging to a specific list.
ALTER PROCEDURE dbo.AddItem
@ItemDescription VARCHAR(500)
AS
-- Determine the item ID that should be assigned to the current item
DECLARE @itemId INT,
@titleId INT
-- Gets the latest title Id
SELECT @titleId = MAX(ListId) FROM ListCategory
-- Gets the maximum id of the item assigned to the current list
SELECT @itemID = MAX(ItemId) FROM Items_List_Rel WHERE ListId = @TitleId
IF (@itemID IS NULL)
SET @itemId = 1
ELSE
SET @itemId = @itemId + 1
/* END IF */
-- Finally inserts the record of the item
INSERT INTO Items_List_Rel VALUES (@itemId, @TitleId, @ItemDescription)
RETURN
Having the stored procedures at hand, they have to be called from the ASPX pages to complete the "Create a new shopping list" functionality.
Private Sub addShoppingListTitle()
Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
Dim sqlConn As New SqlConnection(strConnString)
sqlConn.Open()
Dim sqlCmd As New SqlCommand()
sqlCmd.CommandText = "NewList"
sqlCmd.CommandType = Data.CommandType.StoredProcedure
sqlCmd.Connection = sqlConn
' Creation parameters
Dim sqlParamQuestion As New SqlParameter("@Title", Data.SqlDbType.VarChar)
sqlParamQuestion.Value = txtTitle.Text
sqlCmd.Parameters.Add(sqlParamQuestion)
' Execute stored procedure
sqlCmd.ExecuteNonQuery()
' Close connection
sqlConn.Close()
End Sub
The above code snippet calls the stored procedure for inserting a new shopping list title.
' Add shopping list items
Dim strItems() As String = txtItems.Text.Split(vbLf)
For Each item As String In strItems
addItem(item)
Next
To get the list of items, separated by linefeeds, the above code snippet uses the very handy split (vblf) which returns an array of string with each element as the item.
Then, each item in the array is inserted in the database to build the shopping list as illustrated below.
Private Sub addItem(ByVal strItem As String)
Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
Dim sqlConn As New SqlConnection(strConnString)
sqlConn.Open()
Dim sqlCmd As New SqlCommand()
sqlCmd.CommandText = "AddItem"
sqlCmd.CommandType = Data.CommandType.StoredProcedure
sqlCmd.Connection = sqlConn
' Creation parameters
Dim sqlParamQuestion As New SqlParameter("@ItemDescription", Data.SqlDbType.VarChar)
sqlParamQuestion.Value = strItem
sqlCmd.Parameters.Add(sqlParamQuestion)
' Execute stored procedure
sqlCmd.ExecuteNonQuery()
' Close connection
sqlConn.Close()
End Sub
Retrieving the shopping list
被过滤广告
To retrieve the shopping, list, we will make use of the powerful DataList control gifted to us. It will be binded with the combolist for retrieving a specific shopping list.
First, we need to populate the ComboList with the available shopping list titles. Do not forget to set the AutoPostBack of the combolist to True.
We first create a new data source from the wizard.
Figure 7. Choosing a data source for populating combo with shopping titles
Figure 8. Choosing the connection of the datasource.
Figure 9. Chose the table named "List Description" select both columns and then click on Next. You can test the query in the next step to make sure everything is working perfectly.
Figure 10. Then, select the datasource to be assigned to the datalist and select the fields as specified in the figure
Figure 11. Selecting all the fields from the "Items_List_Rel" tables and click on the "WHERE" button.
Figure 12. The condition should be configured as in the picture above. Note how the value is obtained from the DropDownList and binded to the dataset returned. Then, click on the "Add" button.
You can test the query to make sure everything is working properly after the above step.
We will customize the template and make the information appear in tabular format.
Figure 13. Item template is formatted as above. The AlternatingItem Template is just a Copy Paste of the Item Template.
Printing
Printing the list is simple. We will make use of a simple Javascript code to get a printout.
A hyperlink is placed on the page and the following associated with it.
<a href="javascript:window.print();">Print List</a>
The important part is the "javascript:window.print()". Et voila, we are done with the application!
Some screen shots of shopping list web application
Figure 14. Building the shopping list
Figure 15. List selected and list of items populated
Figure 16. After clicking on the "Print" link, the above dialog is shown
Figure 17. Print preview of the shopping list
Summary
In this tutorial, some concepts to interact with stored procedures, javascript and the ASP.NET gifted wizard were illustrated. Of course, this application can be more robust. For instance, you use required field validators for validating entries or ignoring blank lines etc... Well, it is left to you to make the application better. Now, you are sure you have the shopping list stored and easily retrieved for future use, Happy coding!
discuss this topic to forum
