Using the ASP LinqDataSource Control
Using the ASP LinqDataSource Control
With ASP.NET LinqDataSource Control we can bind Web Pages to retrieves or modify data by using the advantages of the unified programming model that provided by LINQ.
My goal is to create Articles site using ASP.NET, on which all the articles are stored in SQL Server tables.
Articles Database Schema
Starting with the schema, for simplicity, the site will use two level of categories: main and secondary.
- Table Categories - Contains all the main categories and their descriptions.
- Table SubCategories - Contains all the secondary categories, linked to their parents.
- Table Authors - Contains the names of the authors.
- Table Articles - This is the main table containing all the information related to the articles, and connected to Authors and SubCategories.
Creating ASP.NET Web Site
The UI is a set of aspx pages, so for that we can create ASP.NET Web Site, which will contains all your aspx pages.
Lets Add New Item into our Articles Web Site, of type LINQ to SQL Classes, and call it Articles.dbml.
After we have a database schema, and Articles.dbml in our web site, we want to create Data Classes. For that, we open the Server Explorer, Connect to the database, and dragging items onto the design surface.
The following figure show the result we have so far:
Configure Linq Data Source
From the toolbox, drag into Default.aspx web page the LinqDataSource Control, founded at data category, and then press link to configure it.
Add Form View
Add new FormView Control from the Data Controls in the toolbox, and choose data source the the linq data source you have created above.
Override the Result
Now, you can view page Default.aspx, and look for the default results.
Lets say we want to display information not only from articles tables, but also from the Authors tables which connected to it with foreign key.
Double clicking on the LinqDataSource Control will create for us a new method in file _Default, so lets do some overrides:
public partial class _Default : System.Web.UI.Page
{
private Int32 m_id;
protected void Page_Load(object sender, EventArgs e)
{
String x = ClientQueryString;
if (ClientQueryString.ToUpper().StartsWith("ID="))
{
m_id = Int32.Parse(ClientQueryString.Substring(3));
}
}
protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
ArticlesDataContext dataContext = new ArticlesDataContext();
e.Result = from article in dataContext.Articles
where article.Id == m_id
select new
{
article.CreatedOn,
article.CreatedBy,
article.Title,
article.Text,
AuthorName = article.Author.Name,
AuthorDescription = article.Author.Description,
article.SubCategory.Category,
article.SubCategory.ParentCategory,
CategoryUrl = GetCategoryUrl(article.SubCategory.ParentCategory),
SubCategoryUrl = GetSubCategoryUrl(article.SubCategory.Category)
};
}
private String GetCategoryUrl(String category)
{
return "../" + GetSubCategoryUrl(category);
}
private String GetSubCategoryUrl(String category)
{
return category.ToLower().Replace(" ", "-") + ".aspx";
}
}
As I have changed the result set for the control, I will change in the Default.aspx code:
After removing DataKeyNames="Id" from the FormView, and update the fields as the result set,
<asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="ArticlesDataContext" onselecting="LinqDataSource1_Selecting"
TableName="Articles">
</asp:LinqDataSource>
<asp:FormView ID="FormView1" runat="server"
DataSourceID="LinqDataSource1">
<ItemTemplate>
Category:
<asp:Label ID="CategoryLabel" runat="server" Text='<%# Bind("Category") %>' />
<br />
CreatedOn:
<asp:Label ID="CreatedOnLabel" runat="server" Text='<%# Bind("CreatedOn") %>' />
<br />
CreatedBy:
<asp:Label ID="CreatedByLabel" runat="server" Text='<%# Bind("CreatedBy") %>' />
<br />
Title:
<asp:Label ID="TitleLabel" runat="server" Text='<%# Bind("Title") %>' />
<br />
Text:
<asp:Label ID="TextLabel" runat="server" Text='<%# Bind("Text") %>' />
<br />
Author Name:
<asp:Label ID="AuthorLabel" runat="server" Text='<%# Bind("AuthorName") %>' />
<br />
Author Description:
<asp:Label ID="AuthorDescriptionLabel" runat="server" Text='<%# Bind("AuthorDescription") %>' />
<br />
Sub Category Url:
<asp:Label ID="SubCategoryLabel" runat="server"
Text='<%# Bind("SubCategoryUrl") %>' />
<br />
Category Url:
<asp:Label ID="CategoryUrlLabel" runat="server"
Text='<%# Bind("CategoryUrl") %>' />
<br />
</ItemTemplate>
</asp:FormView>
Using the following URL: http://.../ArticlesWebSite/Default.aspx?id=3 you can access the article with id = 3.
Conclusion
With LinqDataSource Control and FormView or ListView you can bind web pages to the database in a very simple and powerfull way, using data retrieved from some tables related each other.
You can download the source code: LinqDataSource.zip
Download the script to create the database schema: ArticlesTables.zip
