Using the ASP LinqDataSource Control

23 באפריל 2008

5 תגובות

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. 

Articles Database Schema


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.


LINQ to SQL Classes


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:


Using the Microsoft Visual Studio Server Explorer Data Connection


 


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.


 


ASP.NET LinqDataSource Web Server Control


 ASP.NET LinqDataSource Web Server Control ASP.NET LinqDataSource Web Server Control


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.

ASP.NET FormView Web Server Control

 


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

kick it on DotNetKicks.com

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

5 תגובות

  1. אוהד אסטון23 באפריל 2008 ב 10:55

    I think that the xxxDataSource way is wrong. It doesn't matter if its Sql, Object, Linq or whatever. That way you have no seperation, your DAL is located in your UI\View.

    להגיב
  2. egady24 באפריל 2008 ב 11:23

    Hi Ohad,
    Yes, it is not a perfect seperation, but there is different between the LinqDataSource to SqlDataSource and ObjectDataSource. The main benefit is that it bind to a LINQ enabled data model (free CRUD…).

    Gady.

    להגיב
  3. ecards28 באוקטובר 2008 ב 14:32

    Thanks for this blog – this is not obvious when trying to play around in the designer.

    Hopefully v.next will have designer support for queries across tables.

    להגיב
  4. grand-gambler.com1 באפריל 2010 ב 7:52

    In my opinion you stole this article and placed on another site. I've seen it now.

    להגיב