For example, if your project name is cs4750, your SQL instance ID is db-demo, and the database name is guestbook, host would be cs4750:us-east4:db-demo DSN would be mysql:unix_socket=/cloudsql/cs4750:us-east4:db-demo;dbname=guestbook" (where PDO driver is mysql).
In this tutorial we will answer this question by building a very rudimentary guestbook application. In doing so, we will look at different options for modeling user information in a database, and then see how to associate this data with the user accounts created by the Membership framework.
Cs Db Guestbook
The MembershipUser class does not include properties like PhoneNumber or DeliveryPreferences or PastOrders. So how do we track the user information needed by the application and have it integrate with the Membership framework? In this tutorial we will answer this question by building a very rudimentary guestbook application. In doing so, we will look at different options for modeling user information in a database, and then see how to associate this data with the user accounts created by the Membership framework. Let's get started!
Let's create a very simple guestbook application where an authenticated user can leave a comment. In addition to storing guestbook comments, let's allow each user to store his home town, homepage, and signature. If provided, the user's home town, homepage, and signature will appear on each message he has left in the guestbook.
In order to capture the guestbook comments, we need to create a database table named GuestbookComments that has columns like CommentId, Subject, Body, and CommentDate. We also need to have each record in the GuestbookComments table reference the user who left the comment.
Next, define the GuestbookComments's columns. Start by adding a column named CommentId of type uniqueidentifier. This column will uniquely identify each comment in the guestbook, so disallow NULL s and mark it as the table's primary key. Rather than providing a value for the CommentId field on each INSERT, we can indicate that a new uniqueidentifier value should be automatically generated for this field on INSERT by setting the column's default value to NEWID(). After adding this first field, marking it as the primary key, and settings its default value, your screen should look similar to the screen shot shown in Figure 2.
All that remains is to add a column that associates a user account with each guestbook comment. One option would be to add a column named UserName of type nvarchar(256). This is a suitable choice when using a Membership provider other than the SqlMembershipProvider. But when using the SqlMembershipProvider, as we are in this tutorial series, the UserName column in the aspnet_Users table is not guaranteed to be unique. The aspnet_Users table's primary key is UserId and is of type uniqueidentifier. Therefore, the GuestbookComments table needs a column named UserId of type uniqueidentifier (disallowing NULL values). Go ahead and add this column.
At this point the foreign key constraint has been established. The presence of this constraint ensures relational integrity between the two tables by guaranteeing that there will never be a guestbook entry referring to a non-existent user account. By default, a foreign key constraint will disallow a parent record to be deleted if there are corresponding child records. That is, if a user makes one or more guestbook comments, and then we attempt to delete that user account, the delete will fail unless his guestbook comments are deleted first.
Foreign key constraints can be configured to automatically delete the associated child records when a parent record is deleted. In other words, we can setup this foreign key constraint so that a user's guestbook entries are automatically deleted when her user account is deleted. To accomplish this, expand the "INSERT And UPDATE Specification" section and set the "Delete Rule" property to Cascade.
We now need to associate three columns with each user account to store the user's home town, homepage, and signature, which will appear in his guestbook comments. There are number of different ways to accomplish this:
Now that we have the data model created, we are ready to use it. In Steps 2 and 3 we will look at how the currently logged on user can view and edit their home town, homepage, and signature information. In Step 4 we will create the interface for authenticated users to submit new comments to the guestbook and view the existing ones.
Since each guestbook comment requires a subject and body, add a RequiredFieldValidator for each of the TextBoxes. Set the ValidationGroup property of these controls to "EnterComment"; likewise, set the PostCommentButton control's ValidationGroup property to "EnterComment". For more information on ASP.NET's validation controls, check out Form Validation in ASP.NET.
With the user interface complete, our next task is to insert a new record into the GuestbookComments table when the PostCommentButton is clicked. This can be accomplished in a number of ways: we can write ADO.NET code in the Button's Click event handler; we can add a SqlDataSource control to the page, configure its InsertCommand, and then call its Insert method from the Click event handler; or we could build a middle tier that was responsible for inserting new guestbook comments, and invoke this functionality from the Click event handler. Since we looked at using a SqlDataSource in Step 3, let's use ADO.NET code here.
After clicking the PostCommentButton button there is no visual feedback that the comment was added to the guestbook. We still need to update this page to display the existing guestbook comments, which we will do in Step 5. Once we accomplish that, the just-added comment will appear in the list of comments, providing adequate visual feedback. For now, confirm that your guestbook comment was saved by examining the contents of the GuestbookComments table.
In addition to leaving comments, a user visiting the Guestbook.aspx page should also be able to view the guestbook's existing comments. To accomplish this, add a ListView control named CommentList to the bottom of the page.
The LayoutTemplate defines the markup emitted by the control, while the ItemTemplate renders each item returned by the SqlDataSource. The ItemTemplate's resulting markup is placed in the LayoutTemplate's itemPlaceholder control. In addition to the itemPlaceholder, the LayoutTemplate includes a DataPager control, which limits the ListView to showing just 10 guestbook comments per page (the default) and renders a paging interface.
My ItemTemplate displays each guestbook comment's subject in an element with the body situated below the subject. Note that syntax used for displaying the body takes the data returned by the Eval("Body") databinding statement, converts it to a string, and replaces line breaks with the element. This conversion is needed in order to show the line breaks entered when submitting the comment since whitespace is ignored by HTML. The user's signature is displayed beneath the body in italics, followed by the user's home town, a link to his homepage, the date and time the comment was made, and the username of the person who left the comment.
Try adding a new comment to the guestbook. Upon clicking the PostCommentButton button the page posts back and the comment is added to the database, but the ListView control is not updated to show the new comment. This can be fixed by either:
Currently the AdditionalUserInfo.aspx page allows the user to view and edit their home town, homepage, and signature settings. It might be nice to update AdditionalUserInfo.aspx to display the logged in user's guestbook comments. That is, in addition to examining and modifying her information, a user can visit the AdditionalUserInfo.aspx page to see what guestbook comments she's made in the past. I leave this as an exercise for the interested reader.
The SELECT query used by the Guestbook.aspx page uses an INNER JOIN to combine the related records amongst the GuestbookComments, UserProfiles, and aspnet_Users tables. If a user that has no record in UserProfiles makes a guestbook comment, the comment won't be displayed in the ListView because the INNER JOIN only returns GuestbookComments records when there are matching records in UserProfiles and aspnet_Users. And as we saw in Step 3, if a user does not have a record in UserProfiles she cannot view or edit her settings in the AdditionalUserInfo.aspx page.
Let's take a non-trivial example to build a module. We're going to build a guestbook module. The module allows users to leave a message and these messages will be displayed in a list that is sorted by the date they were added in reverse order. There is a setting in the module specifying whether we wish to use moderation. If not then all messages are displayed as they come in. If we use moderation than anyone with edit permissions on the module will need to click on a button to OK any incoming message before it's shown. Finally: users can edit their own messages until they've been moderated and any user with edit permissions can edit any message. If a message has been edited it will be displayed with a small note who edited the message and when.
It's time we thought about what it is we're going to be storing. Foremost we'll be storing a text message left by visitors. We'll call the object a guestbook entry. The entry will have message as field as well as a date time stamp when it was created and a link to the users table in DNN to store who it was that left the message. We'll also need to bring in scope meaning we need to record the module id in which the entry was created. This avoids the message cropping up in another module if the user adds multiple guestbook modules to their site. Finally we'll need a flag to tell us whether the message has been approved. The SQL to create this is as follows:
Next, we create a new XMLDocument class object and load the guestbook.xml file. The required nodes are created with the function CreateElement, and the information entered by the user is retrieved and stored to an object of XmlText. Next, we store the created nodes without any values, using the function AppendChild in conjunction with the main XmlDocument object. 2ff7e9595c
Comments