![[Home]](/wiki.gif)
Personal Website Starter Kit - Adding A Links Table - Part 1 - Table Driven LinksPersonal Website Starter Kit - Adding A Links Table - Part 1 - Table Driven Links
| |
| The Bush Survival Guide - 250 Ways to Make It Through The NExt Four Years Without Misunderestimating the Dangers Ahead, and Other Subliminable Stategeries |
|
Links:
| |
Subjects > Computers > Microsoft > Visual Studio 2005 Beta > Personal Website Starter Kit
Visual Studio 2005 comes with some nice tools for dealing with databases from within the development environment. Right now I am running Visual Studio 2005 within a Virtual PC session that is running Windows 2003. I have not installed Microsoft Office in that Virtual PC, and so I only have the Visual Studio tools to deal with databases. This is ok because it seems that Microsoft Access can not deal with the .MDF files that are supplied with this starter kit.
To open the databases that are supplied with this starter kit, go to the Solution Explorer window, and open the App_Data directory. Double click on the ASPNETDB.MDF file, or the Personal.mdf file. This will cause the Server Explorer window to open. You will then have a tree view to deal with databases so you can inspect the tables, stored procedures, and more. It is interesting to browse the table definitions of the ASPNETDB.MDF file.
Since this application has a Personal.mdf data file for Albums and Photos, this is probably also a good place to create the links table. Right click on the "Table" entry below Personal.mdf in the Server Explorer. Select "Add New Table" from the context menu. Go to the Properties view and change the Name for this table from Table1 to Links.
Enter these fields:
When done, go to the file menu and select "Save Links". This will actually create the table in the database.
Now create a second table called LinkCategories?Create:
Save this table also.
Insert the following sample data into these tables:
TO BE SHOWN: How to paste in a SQL data dump….
We will use one business object class to handle both our Links and Categories tables. The App_Code / PhotoManager?Create.cs file is the file that handles the business object class for the Albums and Photos.
Go to the App_Code directory in the solution explorer, and right click to select "Add New Item". Choose the Class template. For the Name type LinkManager?Create.cs. Be sure the language is Visual C#.
We also need to create the object types for our categories and links. Under App_Code, create a new file called LinkObjectTypes?Create.cs We need to add some additional components for our class to use, so add these usings at the start with this list:
using System.Collections; using System.Collections.Generic; using System.Data.SqlClient?Create; using System.IO;
Enter the following code for the categories object into LinkObjectTypes?Create.cs....
TODO
Now enter this code for the constructor of the LinkManager?Create in LinkManager?Create.cs....
TODO
Now lets create a procedure to return the list of categories. Enter this code for GetCategories?Create in the LinkManager?Create.cs file...
TODO
We also need to create a stored procedure for the ExecuteReader?Create.
CREATE PROCEDURE dbo.GetCategories?Create
@IsPublic?Create bitAS
SELECT * FROM LinkCategories?Create RETURN
Select Build Solution from the Build menu to make sure everything is ok. Correct any syntax errors.
Now that we have an ObjectDataSource?Create, lets drop a DataList?Create onto the page. Go to the Toolbox, go to the Data items, and drag and drop a DataList?Create onto the bottom of the page. In the tooltip dialog, choose the ObjectDataSourceCategories?Create.
Go to Links.aspx, insert a datalist and format the item template for the data:
TODO
Now you can test the work done so far. Run the links.aspx page and see the list of categories. Frequently stopping to check what is happening is a good practice, it helps you find errors as early as possible.
Also, running with debugging can be very helpful to find errors, but not for the reasons you might expect. Running with debugging causes errors to be flagged much earlier in the process of running the .NET code, eliminating spurious unhelpful errors at a later point. For example, set the Title to be an int. This will cause problems for the reader, because it wont be able to type case the Title to an int. Run the links.aspx without debugging, and then run with debuging.Check the following
TODO
First lets add the Link object to LinkObjectTypes?Create.cs:
TODO
Now we need to add a procedure to return the list of links from the LinksManager?Create:
TODO
Do a File -> SaveAll?Create to make sure that the system knows about the new procedure added to LinksManager?Create. Do a build solution, and correct any errors that appear. If you have errors in the LinksManager?Create or LinkObjectTypes?Create filoes, then the step below where you select a business object might not show all the business objects you would expect.
Now we'll add another DataSource?Create to the Links.aspx page:
Now we need to add a conditional section to the Links.aspx file. Click to source view.
In front of the current DataList1?Create object, add this code: <%
CatID?Create = this.Request.Params.Get("CatID?Create");
if (CatID?Create != null) // Null (not "") if there is no CatID?Create in query string (url)
{
%>
Request listing of category <%= CatID?Create %>.
}
else
{
%>
And immediately following the DataList1?Create object, add this: <%
}>
This causes the list of categories to be displayed when we have no CatID?Create in the URL. But if there is a CatID?Create, then the page displays the category ID selected. Go ahead do Debug -> Start Without Debugging to try the page. Try clicking on the links at the bottom to select different categories and see what hapepns.
TODO
Go ahead and run the webpage. Select Debug -> Start With Debugging
Now lets fix up the template for the item listings. We will want to make the title of each link be a link to the website, and also remove some of the fields.
The default ItemTemplate?Create looks something like this:
<ItemTemplate?Create>
<asp:Label ID="TitleLabel?Create" runat="server" Text='<%# Eval("Title") %>' ></asp:Label>
Description:
<asp:Label ID="DescriptionLabel?Create" runat="server" Text='<%# Eval("Description") %>'>
</asp:Label>
CatID?Create:
<asp:Label ID="CatIDLabel?Create" runat="server" Text='<%# Eval("CatID?Create") %>'></asp:Label>
Approved:
<asp:Label ID="ApprovedLabel?Create" runat="server" Text='<%# Eval("Approved") %>'></asp:Label>
Url:
<asp:Label ID="UrlLabel?Create" runat="server" Text='<%# Eval("Url") %>'></asp:Label>
Domain:
<asp:Label ID="DomainLabel?Create" runat="server" Text='<%# Eval("Domain") %>'></asp:Label>
</ItemTemplate?Create>
Replace it with the following code:
<ItemTemplate?Create>
<asp:Label ID="DescriptionLabel?Create" runat="server" Text='<%# Eval("Description") %>'>
</asp:Label>
Go to Links.aspx.cs and add the following function inside the definition of the class Links_aspx:
public string AddLineBreaks?Create(string AsciiText?Create, int MaxChars?Create)
{
if (AsciiText?Create.Length < MaxChars?Create)
{ return AsciiText?Create; }
string [] split = AsciiText?Create.Split(new Char[] { ' ' });
string Results="";
int accumulated = 0; // How much have we put together since the start or the last line break?
foreach (string s in split)
{
if (accumulated + s.Length > MaxChars?Create)
{
Results += "
";
accumulated = 0;
}
if (Results.Length>0)
{
Results += " ";
}
Results += s;
accumulated += s.Length;
}
return Results;
}
Yes, we probably could have used a regular expression to do the work above, but the above code is very clear and transparent.
And change the call for the TitleLabel?Create to the use this new function:
<asp:Label ID="TitleLabel?Create" runat="server"
Text='<%# AddLineBreaks?Create((string)Eval("Title"),22) %>' >
Try it out to make sure it works.
Cut these lines of text from Links.aspx:
Resources
Above the DataListLinks?Create add this:
<%= CatIDTitle?Create %>
<%= CatIDDescription?Create %>
And above the DataList1?Create add this:
Categories
Add a few variables to the Links.aspx.cs:
public LinkManager?Create linkmanager; public string CatIDText?Create; public string CatIDDescription?Create;
Now run to see the results.
It's looking pretty good now. Time to get rid of the dummy entries that originally came with the page.
Delete the following text from Links.aspx:
Top 5
Try it out... You've now got a very nice categorized listing of links, driven by database table.
This is the "Stage 1 Links Page".
What to do next?
Go on to the next part of this article
Go down to Stored Procedures, and right click to select Add each of these:
CREATE PROCEDURE dbo.GetLinks?Create
@IsApproved?Create bitAS
SELECT * FROM Links WHERE ([Links].[IsApproved?Create] = @IsApproved?Create) RETURN
CREATE PROCEDURE dbo.AddLink?Create
@Url nvarchar(128), @Domain nvarchar(70), @Title nvarchar(80), @Description nvarchar(255), @IsApproved?Create bitAS
INSERT INTO [Links] ([Url],[Domain],[Title],[Description],[IsApproved?Create]) VALUES (@Url,@Domain,@Title,@Description,@IsApproved?Create) RETURN
CREATE PROCEDURE dbo.GetCategories?Create
@IsPublic?Create bitAS
SELECT * FROM LinkCategories?Create RETURN
CREATE PROCEDURE dbo.AddCategory
@Title nvarchar(80), @Description nvarchar(255), @Identity int OUTAS
INSERT INTO [LinkCategories?Create] ([Title],[Description]) VALUES (@Title,@Description) SET @Identity = SCOPE_IDENTITY() RETURN
Now we have to create our links to these new database. Go to Links.aspx source view, and insert this text at the end:
